Oracle PL/SQL – Prozeduren und Funktionen

Fach: Informationstechnologie – Datenbanksysteme
Schulstufe: 12. Schulstufe – HTL Informatik
Voraussetzungen: PL/SQL Einführung, Kontrollstrukturen, Cursor, Exceptions
Autor: HTL Pinkafeld – IF/IT

1. Gespeicherte Subprogramme – Überblick

1.1 Vorteile gegenüber anonymen Blöcken

1.2 Prozedur vs. Funktion

2. Stored Procedures erstellen

2.1 Syntax

CREATE [OR REPLACE] PROCEDURE prozedurname
    [(parameter1 [modus] datentyp, ...)]
IS  -- oder AS
    -- Deklarationsabschnitt (ohne DECLARE-Schlüsselwort!)
BEGIN
    -- Ausführungsabschnitt
EXCEPTION
    -- Fehlerbehandlung (optional)
END [prozedurname];
/

2.2 Einfache Prozedur ohne Parameter

CREATE OR REPLACE PROCEDURE zeige_abteilungen IS
BEGIN
    FOR r IN (SELECT deptno, dname, loc FROM dept ORDER BY deptno) LOOP
        DBMS_OUTPUT.PUT_LINE(
            LPAD(r.deptno, 3) || ' | ' ||
            RPAD(r.dname, 14) || ' | ' ||
            r.loc
        );
    END LOOP;
END zeige_abteilungen;
/

-- Aufrufen:
BEGIN
    zeige_abteilungen;
END;
/

2.3 OR REPLACE

Das Schlüsselwort OR REPLACE überschreibt eine bereits vorhandene Prozedur ohne Fehlermeldung. Ohne es würde Oracle einen Fehler werfen, wenn die Prozedur schon existiert.

-- Prozedur testen (enthält Syntaxfehler abfangen):
SHOW ERRORS PROCEDURE zeige_abteilungen;

-- Alternativ in SQL Developer: Kompiler-Status prüfen
SELECT object_name, status
FROM   user_objects
WHERE  object_type = 'PROCEDURE';

3. Parameter-Modi: IN, OUT, IN OUT

3.1 IN-Parameter (Standard)

IN ist der Standardmodus. Der Wert wird beim Aufruf übergeben und kann innerhalb der Prozedur nicht geändert werden:

CREATE OR REPLACE PROCEDURE gehaltserhöhung (
    p_empno  IN emp.empno%TYPE,
    p_prozent IN NUMBER
) IS
    v_alt_sal emp.sal%TYPE;
BEGIN
    SELECT sal INTO v_alt_sal FROM emp WHERE empno = p_empno;

    UPDATE emp
    SET    sal = sal * (1 + p_prozent / 100)
    WHERE  empno = p_empno;

    DBMS_OUTPUT.PUT_LINE(
        'Mitarbeiter ' || p_empno ||
        ': ' || v_alt_sal || ' → ' || ROUND(v_alt_sal * (1 + p_prozent/100), 2)
    );
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Mitarbeiter ' || p_empno || ' nicht gefunden.');
END gehaltserhöhung;
/

-- Aufruf:
BEGIN
    gehaltserhöhung(7369, 10);   -- 10 % Erhöhung für Mitarbeiter 7369
END;
/

3.2 OUT-Parameter

OUT-Parameter geben Werte an den Aufrufer zurück. Sie dürfen in der Prozedur nicht gelesen werden (nur beschrieben):

CREATE OR REPLACE PROCEDURE get_emp_info (
    p_empno   IN  emp.empno%TYPE,
    p_name    OUT emp.ename%TYPE,
    p_gehalt  OUT emp.sal%TYPE,
    p_gefunden OUT BOOLEAN
) IS
BEGIN
    SELECT ename, sal
    INTO   p_name, p_gehalt
    FROM   emp
    WHERE  empno = p_empno;

    p_gefunden := TRUE;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name     := NULL;
        p_gehalt   := NULL;
        p_gefunden := FALSE;
END get_emp_info;
/

-- Aufruf:
DECLARE
    v_name    VARCHAR2(10);
    v_sal     NUMBER;
    v_ok      BOOLEAN;
BEGIN
    get_emp_info(7839, v_name, v_sal, v_ok);

    IF v_ok THEN
        DBMS_OUTPUT.PUT_LINE(v_name || ' verdient ' || v_sal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Nicht gefunden.');
    END IF;
END;
/

3.3 IN OUT-Parameter

IN OUT-Parameter können sowohl gelesen als auch beschrieben werden:

CREATE OR REPLACE PROCEDURE normalisiere_name (
    p_name IN OUT VARCHAR2
) IS
BEGIN
    -- Ersten Buchstaben groß, Rest klein
    p_name := INITCAP(TRIM(p_name));
END normalisiere_name;
/

DECLARE
    v_n VARCHAR2(50) := '  MARIA HUBER  ';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Vorher: [' || v_n || ']');
    normalisiere_name(v_n);
    DBMS_OUTPUT.PUT_LINE('Nachher: [' || v_n || ']');
END;
/

3.4 NOCOPY-Hint

Bei großen Datenstrukturen (Collections, CLOBs) kann NOCOPY die Performance verbessern – übergabe als Referenz statt Kopie:

CREATE OR REPLACE PROCEDURE verarbeite_liste (
    p_daten IN OUT NOCOPY DBMS_SQL.VARCHAR2A
) IS
BEGIN
    -- p_daten wird per Referenz übergeben – kein Kopieren
    NULL;
END;
/

4. Stored Functions erstellen

4.1 Syntax

CREATE [OR REPLACE] FUNCTION funktionsname
    [(parameter [IN] datentyp, ...)]
RETURN rückgabetyp
IS  -- oder AS
    -- Deklarationen
BEGIN
    -- Logik
    RETURN ausdruck;  -- Rückgabewert
EXCEPTION
    ...
END [funktionsname];
/

4.2 Einfache Funktion

CREATE OR REPLACE FUNCTION jahresgehalt (
    p_sal  emp.sal%TYPE,
    p_comm emp.comm%TYPE DEFAULT 0
) RETURN NUMBER
IS
    v_comm NUMBER := NVL(p_comm, 0);
BEGIN
    RETURN (p_sal + v_comm) * 12;
END jahresgehalt;
/

-- Aufruf in PL/SQL:
DECLARE
    v_jg NUMBER;
BEGIN
    v_jg := jahresgehalt(3000, 500);
    DBMS_OUTPUT.PUT_LINE('Jahresgehalt: ' || v_jg);
END;
/

4.3 Funktion mit Fehlerbehandlung

CREATE OR REPLACE FUNCTION get_abteilungsname (
    p_deptno dept.deptno%TYPE
) RETURN VARCHAR2
IS
    v_name dept.dname%TYPE;
BEGIN
    SELECT dname INTO v_name
    FROM   dept
    WHERE  deptno = p_deptno;

    RETURN v_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'Unbekannte Abteilung';
    WHEN OTHERS THEN
        RETURN 'Fehler: ' || SQLERRM;
END get_abteilungsname;
/

5. Funktionen in SQL-Abfragen verwenden

5.1 Funktion im SELECT

Eigene Funktionen können direkt in SQL-Abfragen verwendet werden – wie built-in-Funktionen:

SELECT empno,
       ename,
       sal,
       NVL(comm, 0)           AS provision,
       jahresgehalt(sal, comm) AS jahreslohn,
       get_abteilungsname(deptno) AS abteilung
FROM   emp
ORDER  BY jahreslohn DESC;

5.2 Funktionen in WHERE und ORDER BY

-- Funktion im WHERE:
SELECT ename, sal
FROM   emp
WHERE  jahresgehalt(sal, comm) > 40000;

-- Funktion im ORDER BY:
SELECT ename, deptno
FROM   emp
ORDER  BY get_abteilungsname(deptno), ename;

5.3 Einschränkungen für SQL-Funktionen

Damit eine Funktion in SQL verwendet werden kann, muss sie folgende Regeln einhalten:

• Keine DML (kein INSERT/UPDATE/DELETE in der DB-Funktion), außer mit PRAGMA AUTONOMOUS_TRANSACTION
• Kein COMMIT/ROLLBACK innerhalb der Funktion
• Keine OUT-Parameter für Funktionen in SQL
• Deterministisch: Gleiche Eingabe → gleiche Ausgabe (für Optimizer-Nutzung: DETERMINISTIC)

6. Lokale Subprogramme

6.1 Definition

Prozeduren und Funktionen können auch lokal innerhalb eines Blocks definiert werden. Sie sind nur in diesem Block sichtbar:

DECLARE
    -- Lokale Funktion (muss NACH allen Variablendeklarationen stehen)
    FUNCTION steuer (p_gehalt NUMBER) RETURN NUMBER IS
    BEGIN
        CASE
            WHEN p_gehalt <= 1800 THEN RETURN 0;
            WHEN p_gehalt <= 3000 THEN RETURN p_gehalt * 0.20;
            WHEN p_gehalt <= 5000 THEN RETURN p_gehalt * 0.32;
            ELSE                       RETURN p_gehalt * 0.42;
        END CASE;
    END steuer;

    -- Lokale Prozedur
    PROCEDURE zeige_steuer (p_name VARCHAR2, p_sal NUMBER) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(
            RPAD(p_name, 12) || 'Gehalt: ' || LPAD(p_sal, 6) ||
            ' | Steuer: ' || LPAD(ROUND(steuer(p_sal)), 6)
        );
    END zeige_steuer;

BEGIN
    FOR r IN (SELECT ename, sal FROM emp WHERE deptno = 10) LOOP
        zeige_steuer(r.ename, r.sal);
    END LOOP;
END;
/

7. Overloading – Überladen

7.1 Konzept

Innerhalb eines Packages (oder lokalen Deklarationsabschnitts) können mehrere Subprogramme mit demselben Namen, aber unterschiedlichen Parametern existieren:

DECLARE
    -- Gleicher Name, unterschiedliche Parameterlisten:
    FUNCTION formatiere (p_wert NUMBER) RETURN VARCHAR2 IS
    BEGIN
        RETURN TO_CHAR(p_wert, 'FM999,990.00') || ' €';
    END;

    FUNCTION formatiere (p_wert DATE) RETURN VARCHAR2 IS
    BEGIN
        RETURN TO_CHAR(p_wert, 'DD.MM.YYYY');
    END;

    FUNCTION formatiere (p_wert VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
        RETURN '"' || TRIM(p_wert) || '"';
    END;

BEGIN
    DBMS_OUTPUT.PUT_LINE(formatiere(1234.5));       -- 1,234.50 €
    DBMS_OUTPUT.PUT_LINE(formatiere(SYSDATE));      -- 15.06.2025
    DBMS_OUTPUT.PUT_LINE(formatiere('  Hallo  ')); -- "Hallo"
END;
/

8. Subprogramme verwalten

8.1 Data Dictionary

-- Alle eigenen Stored Procedures/Functions anzeigen:
SELECT object_name, object_type, status, last_ddl_time
FROM   user_objects
WHERE  object_type IN ('PROCEDURE', 'FUNCTION')
ORDER  BY object_type, object_name;

-- Quellcode anzeigen:
SELECT line, text
FROM   user_source
WHERE  name = 'JAHRESGEHALT'
AND    type = 'FUNCTION'
ORDER  BY line;

-- Kompilierfehler anzeigen:
SELECT line, position, text
FROM   user_errors
WHERE  name = 'GEHALTSERHÖHUNG'
ORDER  BY sequence;

8.2 DROP und Abhängigkeiten

-- Prozedur löschen:
DROP PROCEDURE gehaltserhöhung;

-- Funktion löschen:
DROP FUNCTION jahresgehalt;

-- Abhängige Objekte prüfen:
SELECT name, type, referenced_name, referenced_type
FROM   user_dependencies
WHERE  referenced_name = 'JAHRESGEHALT';

8.3 Rechte vergeben

-- Ausführungsrecht für anderen Benutzer:
GRANT EXECUTE ON jahresgehalt TO scott;
GRANT EXECUTE ON gehaltserhöhung TO hr_app;

-- Recht entziehen:
REVOKE EXECUTE ON jahresgehalt FROM scott;

9. Autonome Transaktionen

9.1 PRAGMA AUTONOMOUS_TRANSACTION

Eine autonome Transaktion läuft unabhängig von der aufrufenden Transaktion. Nützlich z.B. für Logging (muss auch bei ROLLBACK erhalten bleiben):

CREATE OR REPLACE PROCEDURE log_aktion (
    p_aktion   VARCHAR2,
    p_benutzer VARCHAR2 DEFAULT USER,
    p_info     VARCHAR2 DEFAULT NULL
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;  -- Eigene Transaktion!
BEGIN
    INSERT INTO audit_log (aktion, benutzer, zeitpunkt, info)
    VALUES (p_aktion, p_benutzer, SYSTIMESTAMP, p_info);

    COMMIT;  -- Commit nur für diese Transaktion – Haupt-TX unberührt
END log_aktion;
/

-- Verwendung:
BEGIN
    log_aktion('DELETE', USER, 'Versuch, Abteilung 10 zu löschen');

    DELETE FROM dept WHERE deptno = 10;  -- Schlägt fehl (FK-Verletzung)
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- Haupttransaktion wird zurückgerollt
        -- Aber: log_aktion wurde COMMITTED und bleibt erhalten!
        DBMS_OUTPUT.PUT_LINE('Fehler: ' || SQLERRM);
END;
/

10. Zusammenfassung und Ausblick

10.1 Prozedur vs. Funktion – Entscheidungshilfe

10.2 Best Practices

• ✅ OR REPLACE bei Definitionen verwenden
• ✅ Subprogrammnamen wiederholen bei END
• ✅ %TYPE und %ROWTYPE für Parameter verwenden
• ✅ Exception-Handling in jedem Subprogramm
• ✅ Funktionen in SQL: deterministisch und ohne DML
• ✅ Fehler mit RAISE_APPLICATION_ERROR kommunizieren

10.3 Ausblick

Das nächste Kapitel behandelt Packages – die Möglichkeit, verwandte Prozeduren, Funktionen, Typen und Variablen in einer logischen Einheit zu bündeln:

• Package Specification vs. Package Body
• Package-Variablen (Sitzungszustand)
• Overloading im Package
• Standard-Packages von Oracle (DBMS_OUTPUT, DBMS_UTILITY, UTL_FILE, …)