Wir werden eine Funktion erzeugen, die es erlaubt, Mitarbeiterdatensätze zu verändern. Diese dürfen aber nur während der Geschäftszeiten verändert werden, denn wir wollen nicht, dass die Reinigungskräfte des Nachts ihre Gehälter erhöhen.
Grundsätzlich kann eine Funktion all das tun, was eine Prozedur auch kann, z.B. andere Funktionen oder Prozeduren aufrufen. Zusätzlich kann und muß eine Funktion auch einen Wert als Ergebnis zurückliefern.
Rumpf
Wieder erzeugen wir erst einmal den “Rahmen” unserer Funktion:
create or replace function plsql19 return boolean is begin null; end; /
Beachten Sie die return boolean Klausel. Die Funktion liefert einen boolschen Wert, also entweder Wahr oder Falsch zurück, abhängig davon, ob die Veränderung des Datensatzes erfolgreich war.
Implementierung
Nun erzeugen wir Funktionalität:
create or replace function plsql19
(emp_id IN employees.id%type,
new_sal IN employees.salary%type)
return boolean is
begin
update employees
set salary = new_sal
where id = emp_id
and to_char(sysdate,'hh24:mi') between '09:00' and '17:30';
-- Bitte beachten, dass hier ein Stringvergleich und kein Datumsvergleich stattfindet!
if sql%rowcount > 0 then
return true;
else
return false;
end if;
end;
/
Die Funktion kennt zwei Eingabe-Argumente, über die gesteuert wird, welcher Mitarbeiter welches neue Gehalt erhält. Wegen der verankerten Deklaration der Datentypen ist Voraussetzung, dass eine Tabelle employees mit den referenzierten Spalten tatsächlich existiert.
Das UPDATE-Statement kann nur durchgeführt werden, wenn es zwischen 9 Uhr und 17.30 Uhr ist.
Nach Ausführung des UPDATE prüfen wir, ob das vorangegangene SQL-Statement mindestens einen Datensatz verändert hat. Ist dies nicht der Fall, liefert die Funktion FALSE zurück.
Wenn wir die Funktion über DESCRIBE betrachten, erhalten wir
FUNCTION plsql19 RETURNS BOOLEAN Argument Name Type In/Out Default? ------------- ------------ ------ -------- EMP_ID VARCHAR2(10) IN NEW_SAL NUMBER(10,2) IN
Hier sehen wir also, welche Eingabe-Argumente zulässig sind und welcher Datentyp zurückgeliefert wird.
Hier ist die Struktur der EMPLOYEES-Tabelle
SQL>desc employees Name Null? Type ------------------------------- ----- ------------ ID VARCHAR2(10) NAME VARCHAR2(30) AGE VARCHAR2(3) SEX VARCHAR2(1) COMM_DATE DATE SALARY NUMBER(10,2)
Und hier der Inhalt
SQL>select * from employees; ID NAME AGE S COMM_DATE SALARY -- --------- --- - --------- ------ 1 Smith 45 F 01-FEB-97 70000 2 Slate 52 M 01-FEB-97 80000 3 Rubble 22 M 10-FEB-98 22000 4 Flintstone 18 M 01-FEB-98 70000 5 OOI 21 F 01-FEB-99 99000 6 Vajhaal 21 M 01-MAR-99 2000 7 Mostert 25 M 20-AUG-98 50000 8 Jeffcoat 45 M 01-FEB-99 35000 SQL>
Test
Jetzt testen wir die Funktion:
Um eine Funktion zu testen, können wir nicht einfach ihren Namen in SQL*Plus eintippen, da sie ja einen Wert zurückliefert und wir einen “Container” brauchen, der den zurückgelieferten Wert aufnimmt. Wir müssen also die Funktion aus PL/SQL heraus aufrufen.
Hier ist unser anonymer Block, um die Funktion zu testen:
begin
if plsql19('7',1) then
dbms_output.put_line('Salary updated.');
else
dbms_output.put_line('Try again tomorrow morning.');
end if;
end;
/
Denken Sie daran, dass SERVEROUTPUT ON gesetzt sein muß, damit Sie ein Ergebnis sehen.
Wenn Sie so sind wie ich und abends arbeiten, sehen Sie
Try again tomorrow morning.
Das Data Dictionary
Sollten Sie Ihren Source-Code nicht gespeichert haben, gibt es ja noch das Data Dictionary, in dem unsere Funktion im Source-Code vorliegt.
SQL> select text from all_source where name = 'PLSQL19' order by line;
TEXT
-----------------------------------------------
function plsql19
(emp_id IN employees.id%type,
new_sal IN employees.salary%type)
return boolean is
begin
update employees
set salary = new_sal
where id = emp_id
and to_char(sysdate,'hh24:mi') between '09:00' and '17:30';
-- Bitte beachten, dass hier ein Stringvergleich und kein Datumsvergleich stattfindet!
if sql%rowcount > 0 then
return true;
else
return false;
end if;
end;
Um die Funktion zu verändern, folgen Sie den Schritten
- Spoolen Sie das Ergebnis des SELECTs in eine Datei
- Entfernen Sie die Überschrift TEXT (oder SQL*Plus erledigt das durch set heading off)
- Stellen Sie der ersten Zeile ein CREATE OR REPLACE voran.
- Verändern Sie den Rumpf der Funktion entsprechend Ihren Anforderungen
- Speichern Sie die Datei und führen Sie sie aus
Voila, die Funktion ist verändert!
Funktionen in SQL-Anweisungen
Das beste ist, dass Funktionen auch in SQL-Anweisungen genutzt werden können.
Dabei gilt allerdings, dass sie weder den Datenbestand verändern dürfen noch Transaktionssteuerung übernehmen können. Ausserdem muß die Funktion einen SQL-bekannten Datentypen zurückliefern und es können nur IN-Argumente verwendet werden.
(bevor sich jemand beschwert: es gelten noch weitere Einschränkungen z.B. bei Verwendung von Packages. Früher musste man die Reinheit der Funktionen durch PRAGMA-Anweisungen zusichern (sog. Assertions) (z.B. WNDS=Writes No Database State oder WNPS=Writes No Package State)).
Wir können uns also eine Funktion brutto vorstellen, die auf einen übergeben Nettowert die Mehrwertsteuer addiert.
create or replace function brutto (nettowert IN number) return number is begin return (nettowert * 1.19); end; /
Solche Funktionen können sowohl in einem SELECT wie auch in jeder anderen DML Anweisung genutzt werden, also z.B.
SQL>select artikel_netto, brutto(artikel_netto) from artikel;
oder in
SQL>update rechnung set brutto_position = brutto(netto_pos);
Ein Kenner der Materie würde an dieser Stelle auch auf das Pragma AUTONOMOUS_TRANSACTION hinweisen, mit dem einige Einschränkungen der Verwendung von Funktionen innerhalb SQL aufgehoben werden.