martinbulinski.de

Wir wollen in dieser Lektion den Unterschied zwischen einer Funktion und Prozedur ausarbeiten.

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.

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.

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>

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.

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.