Datenbanken SoSe2015

Blatt 9

Cursor

CREATE OR REPLACE PROCEDURE Blatt6d (kontinent CONTINENT.NAME%TYPE) IS

CURSOR countries_on_continent IS
  SELECT * FROM Country, encompasses WHERE encompasses.continent = kontinent and country.code = encompasses.country; 
current_country countries_on_continent%ROWTYPE;

--Datentyp der Variablen "groesse_europa" könnte man aus der Tabellenspalte übernehmen.
--groesse_kontinent CONTINENT.AREA%TYPE;
groesse_kontinent NUMBER;
Begin
  
--Zuerst Fläche von Europe ermitteln:
select continent.area into groesse_kontinent from continent where name = kontinent;
DBMS_OUTPUT.PUT_LINE('Größe von ' || kontinent || ': ' || groesse_kontinent);

OPEN countries_on_continent;

FETCH countries_on_continent INTO current_country;
WHILE countries_on_continent%FOUND LOOP
  DBMS_OUTPUT.PUT_LINE('Country: '|| current_country.code || ' - ' || current_country.name || 
     ', Anteil an ' || kontinent || ': ' || (current_country.Area*(current_country.Percentage/100)/ groesse_kontinent) );
  FETCH countries_on_continent INTO current_country;
END LOOP;
CLOSE countries_on_continent; 

end;

execute Blatt6d ('Asia');

show errors PROCEDURE Blatt6d;

WHILE cursorname%FOUND LOOP
   ..
END LOOP;

Trigger

• BEFORE DELETE: wird vor dem Löschen ausgelöst. In der automatisch vorhandenen Variable "NEW" findet sich der zur löschende Datensatz. Gemäß Doku dürfte man diese Werte sogar manipulieren ;-). In der Variablen "OLD" befinden sich die originalen Werte des zu löschenden Satzes.
• BEFORE INSERT: wird vor dem Einfügen ausgelöst. In der automatisch vorhandenen Variable "NEW" findet sich der einzufügende Datensatz. Man kann Werte manipulieren, die im automatisch folgenden INSERT verwendet werden.
• BEFORE UPDATE: wird vor dem Aktualisieren ausgelöst. In der automatisch vorhandenen Variable "NEW" finden sich die neuen Werte des zu aktualisierenden Datensatz. In der Variable "OLD" befinden sich die vorherigen Werte. Man kann Werte in "NEW" manipulieren, die im automatisch folgenden UPDATE verwendet werden.
• AFTER DELETE: wird nach dem Löschen ausgelöst. Zu den Variablen siehe "BEFORE DELETE"
• AFTER INSERT: wird nach dem Einfügen ausgelöst. In der automatisch vorhandenen Variable "NEW" findet sich der einzufügende Datensatz. Man kann diese Werte nicht mehr manipulieren.
• AFTER UPDATE: wird nach dem Aktualisieren ausgelöst. In der automatisch vorhandenen Variable "NEW" finden sich die aktualisierten Werte des Datensatz. Man kann diese Werte nicht mehr manipulieren. In der Variablen "OLD" befinden sich vermutlich die vorherigen Werte.

drop table Bestellung;
drop table Pizza;
create table Pizza
( 
  ID  number not null,
  PizzaName varchar(20) not null,
  Erzeugt Date not null,
  constraint Pizza_PK primary key (ID)
);

create table Bestellung
(
   PizzaName varchar(20) not null,
   Besteller varchar(20) not null, 
   Bestelldatum date not null
);
insert into Pizza (ID, PizzaName, Erzeugt)  values (1, 'Hawaii', SYSDATE );
insert into Pizza (ID, PizzaName, Erzeugt)  values (2, 'Salami', SYSDATE);
insert into Pizza (ID, PizzaName, Erzeugt)  values (3, 'Tonno', SYSDATE);

insert into Bestellung (PizzaName, Besteller, Bestelldatum) values ('Hawaii', 'Wolfgang Knauf', SYSDATE);
insert into Bestellung (PizzaName, Besteller, Bestelldatum) values ('Salami', 'Hans Wurst', SYSDATE-2);
insert into Bestellung (PizzaName, Besteller, Bestelldatum) values ('Salami', 'Karl Otto', SYSDATE-14);

CREATE OR REPLACE TRIGGER Pizza_TRA
  AFTER UPDATE 
  ON Pizza
  FOR EACH ROW

BEGIN
  update Bestellung set PizzaName = :NEW.PizzaName where PizzaName = :OLD.PizzaName;
END;

--Hier wird jetzt auch die Bestellung geändert:
update Pizza set Pizzaname = 'Hawai' where Pizzaname = 'Hawaii';

select * from pizza;
select * from bestellung;

Blatt 8

Prozeduren

CREATE OR REPLACE PROCEDURE MeineProzedur (Parameterdeklaration) IS
Variablendeklarationen
BEGIN
  Anweisungen;
END;

execute MeineProzedur (Parameters);

CREATE OR REPLACE PROCEDURE HelloWorldProc IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World');
END;

EXECUTE HelloWorldProc();

drop PROCEDURE HelloWorldProc;

Funktionen

CREATE OR REPLACE FUNCTION MeineFunktion (Parameterdeklaration) 
RETURN Rückgabedatentyp
IS
Variablendeklarationen
BEGIN
  Anweisungen;
  RETURN Rückgabewert;
END;

select Spalte1, Spalte2, MeineFunktion(Parameters), Spalte 3 from MeineTabelle where ...;

select MeineFunktion(Parameters) from dual;

CREATE OR REPLACE FUNCTION HelloWorldFunc 
RETURN varchar
IS
BEGIN
  return 'Hello World';
END;

select HelloWorldFunc() from dual;

drop FUNCTION HelloWorld;

Arbeiten mit Variablen

CREATE OR REPLACE FUNCTION Quadrat(zahl number) 
RETURN number 
IS
quadrat number;
BEGIN
  quadrat := zahl * zahl;
  return quadrat;
END;

select Quadrat(17) from dual;

IF/THEN/ELSE

IF condition THEN
   {...statements to execute when condition is TRUE...}

ELSE
   {...statements to execute when condition is FALSE...}

END IF;

CREATE OR REPLACE FUNCTION IstPositiv(zahl number) 
RETURN number 
IS
BEGIN
  if (Zahl >= 0) then
    return 1;
  else 
    return 0;
  end if;
END;

Anwendungsfehler auslösen

Raise_Application_Error (-20343, 'The balance is too low.');

SQL in Funktionen

drop table Bestellung;
drop table Pizza;
create table Pizza
( 
  ID  number not null,
  PizzaName varchar(20) not null,
  Erzeugt Date not null,
  constraint Pizza_PK primary key (ID)
);

create table Bestellung
(
   PizzaID int not null,
   Besteller varchar(20) not null, 
   Bestelldatum date not null,
   constraint Bestellung_FKA foreign key (PizzaID) references Pizza(ID)
);
insert into Pizza (ID, PizzaName, Erzeugt)  values (1, 'Hawaii', SYSDATE);
insert into Pizza (ID, PizzaName, Erzeugt)  values (2, 'Salami', SYSDATE);
insert into Pizza (ID, PizzaName, Erzeugt)  values (3, 'Tonno', SYSDATE);

create or replace Procedure BestellePizza (pPizzaname Pizza.PizzaName%TYPE, pBesteller Bestellung.Besteller%TYPE) is
  myPizzaID number;
BEGIN
  /*Ohne "min(ID)" liefert das SELECT keine Treffer zurück, wenn nix gefunden wurden. Dadurch wirft die Funktion den 
    Fehler "01403. 00000 -  "no data found". Deshalb als doofen Workaround: min(ID) holen - das liefert ein gültiges NULL, wenn nix da ist!
    Folgendes klappt also nicht:
    select ID into myPizzaID from Pizza where Pizza.PizzaName = pPizzaname; */
  select min(ID) into myPizzaID from Pizza where Pizza.PizzaName = pPizzaname;
  if (myPizzaID is null) then
    Raise_Application_Error (-20001, 'Keine Pizza namens ' || pPizzaname || ' gefunden');
  end if;

  insert into Bestellung (PizzaID, Besteller, Bestelldatum) values (myPizzaID,  pBesteller, SYSDATE);
END;

--Korrektes Insert:
execute BestellePizza ('Hawaii', 'Hans Wurst');
--Wird Fehler auslösen:
execute BestellePizza ('Hawai', 'Hans Wurst');

select * from bestellung;

Datumsmagie

select TO_DATE ('18.06.2015', 'DD.MM.YYYY') from dual;

select TO_DATE ('2015-06-18', 'YYYY-MM-DD') from dual;

select TO_DATE ('2015-18-06', 'YYYY-DD-MM') from dual;

SQLDeveloper

Zugriff auf FH-Oracle

Linux

ssh xyz@login2.cs.hs-rm.de -L 1521:oracle2:1521

Windows

• "putty.exe" herunterladen von http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
  
  
• "putty.exe" starten.
  
  
• In "Connection" -> "SSH" -> "Tunnels" gehen. Im Feld "Source port" wird "127.0.0.1:1521" eingetragen, im Feld "Destination" wird "oracle2:1521" eingetragen - die Adresse der FH-Oracle:
  
  Danach auf "Add" klicken.
  
  Jetzt taucht die Verbindung unter "Forwarded ports" auf:
  
  
  Auf "Session" wechseln und als Hostname "login1.cs.hs-rm.de" eingeben. Ich empfehle, das als Session zu speichern, damit man den Tunnel nicht jedesmal neu einrichten muss:
  
  
  Jetzt auf "Open" klicken.
  Es kommt ein Anmeldefenster. Dort mit dem FH-Login anmelden.
  
  Nach erfolgreichem Login belassen wir das Fenster in diesem Zustand und ...
  
  
• ...melden uns im SQLDeveloper an. Hier gelten die gleichen Einstellungen wie im obigem Linux-Beispiel: Hostname = "localhost", SID = "p001".
  

Übungsblätter

Blatt 4, Aufgabe 1.5

Allgemeine Hinweise

Tools für ER-Diagramme: