Oracle PL/SQL – Packages

Fach: Informationstechnologie – Datenbanksysteme
Schulstufe: 12. Schulstufe – HTL Informatik
Voraussetzungen: Prozeduren und Funktionen, Cursor, Exceptions
Autor: HTL Pinkafeld – IF/IT

1. Was ist ein Package?

1.1 Definition

Ein Package (Paket) ist eine benannte PL/SQL-Einheit, die logisch zusammengehörige Typen, Variablen, Konstanten, Cursor, Prozeduren und Funktionen bündelt. Es besteht aus zwei Teilen:

Package Specification (Spec)    Package Body
─────────────────────────────   ─────────────────────────────
Öffentliche Schnittstelle        Implementierung
  • Typen                          • Prozedur-/Funktions-Code
  • Variablen/Konstanten           • Private Elemente
  • Cursor-Deklarationen           • Initialisierungsabschnitt
  • Prozedur-/Funktions-Köpfe

1.2 Vorteile von Packages

2. Package Specification

2.1 Syntax

CREATE [OR REPLACE] PACKAGE paketname IS
    -- Öffentliche Typen, Konstanten, Variablen
    -- Cursor-Deklarationen (nur Header)
    -- Prozedur-/Funktions-Header (ohne Body)
END [paketname];
/

2.2 Beispiel: pkg_mitarbeiter

CREATE OR REPLACE PACKAGE pkg_mitarbeiter IS

    -- Konstanten
    c_min_gehalt CONSTANT NUMBER := 1700;
    c_max_gehalt CONSTANT NUMBER := 50000;

    -- Öffentlicher Typ
    TYPE t_emp_info IS RECORD (
        empno  emp.empno%TYPE,
        ename  emp.ename%TYPE,
        sal    emp.sal%TYPE,
        abt    dept.dname%TYPE
    );

    -- Prozedur-Köpfe (Schnittstelle)
    PROCEDURE gehaltserhöhung (p_empno emp.empno%TYPE, p_prozent NUMBER);
    PROCEDURE zeige_abteilung (p_deptno dept.deptno%TYPE);

    -- Funktions-Köpfe
    FUNCTION jahresgehalt (p_empno emp.empno%TYPE) RETURN NUMBER;
    FUNCTION ist_manager  (p_empno emp.empno%TYPE) RETURN BOOLEAN;

    -- Öffentlicher Cursor-Typ (REF CURSOR)
    TYPE t_emp_cursor IS REF CURSOR RETURN emp%ROWTYPE;

END pkg_mitarbeiter;
/

3. Package Body

3.1 Syntax

CREATE [OR REPLACE] PACKAGE BODY paketname IS
    -- Private Variablen, Typen (nicht in Spec sichtbar)
    -- Implementierung aller in der Spec deklarierten Subprogramme
    -- Zusätzliche private Subprogramme
    [BEGIN
        -- Initialisierungsabschnitt (einmalig bei erstem Aufruf)
    ]
END [paketname];
/

3.2 Implementierung von pkg_mitarbeiter

CREATE OR REPLACE PACKAGE BODY pkg_mitarbeiter IS

    -- Private Variable (nur im Body sichtbar)
    v_aufrufe NUMBER := 0;

    -- ─── PROZEDUREN ───────────────────────────────────────────────────────────
    PROCEDURE gehaltserhöhung (p_empno emp.empno%TYPE, p_prozent NUMBER) IS
        v_alt emp.sal%TYPE;
        v_neu emp.sal%TYPE;
    BEGIN
        IF p_prozent <= 0 OR p_prozent > 50 THEN
            RAISE_APPLICATION_ERROR(-20010, 'Ungültiger Prozentsatz: ' || p_prozent);
        END IF;

        SELECT sal INTO v_alt FROM emp WHERE empno = p_empno;
        v_neu := ROUND(v_alt * (1 + p_prozent / 100), 2);

        IF v_neu > c_max_gehalt THEN
            RAISE_APPLICATION_ERROR(-20011, 'Gehalt würde Maximum überschreiten.');
        END IF;

        UPDATE emp SET sal = v_neu WHERE empno = p_empno;
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Gehalt: ' || v_alt || ' → ' || v_neu);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20012, 'Mitarbeiter ' || p_empno || ' nicht gefunden.');
    END gehaltserhöhung;

    PROCEDURE zeige_abteilung (p_deptno dept.deptno%TYPE) IS
    BEGIN
        v_aufrufe := v_aufrufe + 1;  -- Private Variable zählen
        DBMS_OUTPUT.PUT_LINE('--- Abteilung ' || p_deptno || ' ---');
        FOR r IN (
            SELECT e.ename, e.job, e.sal
            FROM   emp e
            WHERE  e.deptno = p_deptno
            ORDER  BY e.sal DESC
        ) LOOP
            DBMS_OUTPUT.PUT_LINE(
                RPAD(r.ename, 12) || RPAD(r.job, 10) || r.sal
            );
        END LOOP;
    END zeige_abteilung;

    -- ─── FUNKTIONEN ───────────────────────────────────────────────────────────
    FUNCTION jahresgehalt (p_empno emp.empno%TYPE) RETURN NUMBER IS
        v_sal  emp.sal%TYPE;
        v_comm emp.comm%TYPE;
    BEGIN
        SELECT sal, NVL(comm, 0)
        INTO   v_sal, v_comm
        FROM   emp
        WHERE  empno = p_empno;

        RETURN (v_sal + v_comm) * 12;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN RETURN NULL;
    END jahresgehalt;

    FUNCTION ist_manager (p_empno emp.empno%TYPE) RETURN BOOLEAN IS
        v_cnt NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_cnt
        FROM   emp
        WHERE  mgr = p_empno;
        RETURN v_cnt > 0;
    END ist_manager;

END pkg_mitarbeiter;
/

3.3 Aufrufen des Packages

BEGIN
    -- Package-Elemente über Punkt-Notation aufrufen:
    pkg_mitarbeiter.gehaltserhöhung(7369, 5);
    pkg_mitarbeiter.zeige_abteilung(20);

    IF pkg_mitarbeiter.ist_manager(7839) THEN
        DBMS_OUTPUT.PUT_LINE('King ist Manager.');
    END IF;

    DBMS_OUTPUT.PUT_LINE(
        'Jahresgehalt King: ' || pkg_mitarbeiter.jahresgehalt(7839)
    );
END;
/

4. Package-Variablen und Sitzungszustand

4.1 Lebensdauer

Package-Variablen leben für die gesamte Datenbankverbindung (Session). Jede Session hat ihre eigene Kopie:

CREATE OR REPLACE PACKAGE pkg_zaehler IS
    -- Öffentliche Variable – bleibt die ganze Session erhalten
    v_gesamt_aufrufe  NUMBER := 0;

    PROCEDURE zähle_aufruf (p_aktion VARCHAR2);
    FUNCTION  hole_zähler   RETURN NUMBER;
END pkg_zaehler;
/

CREATE OR REPLACE PACKAGE BODY pkg_zaehler IS
    PROCEDURE zähle_aufruf (p_aktion VARCHAR2) IS
    BEGIN
        v_gesamt_aufrufe := v_gesamt_aufrufe + 1;
        DBMS_OUTPUT.PUT_LINE('[' || v_gesamt_aufrufe || '] ' || p_aktion);
    END;

    FUNCTION hole_zähler RETURN NUMBER IS
    BEGIN
        RETURN v_gesamt_aufrufe;
    END;
END pkg_zaehler;
/

-- Test:
BEGIN
    pkg_zaehler.zähle_aufruf('Login');
    pkg_zaehler.zähle_aufruf('Abfrage');
    pkg_zaehler.zähle_aufruf('Update');
    DBMS_OUTPUT.PUT_LINE('Gesamt: ' || pkg_zaehler.hole_zähler());
END;
/
-- Ausgabe: [1] Login, [2] Abfrage, [3] Update, Gesamt: 3

4.2 SERIALLY_REUSABLE

Mit dem Pragma SERIALLY_REUSABLE werden Package-Variablen nach jedem Aufruf zurückgesetzt – nützlich, wenn kein Sitzungszustand erwünscht ist:

CREATE OR REPLACE PACKAGE pkg_einmalig IS
    PRAGMA SERIALLY_REUSABLE;
    v_wert NUMBER := 0;
    PROCEDURE setze (p_wert NUMBER);
END;
/

5. Overloading im Package

5.1 Gleicher Name, verschiedene Signaturen

CREATE OR REPLACE PACKAGE pkg_format IS
    -- Drei Versionen von "formatiere":
    FUNCTION formatiere (p_zahl NUMBER,     p_maske VARCHAR2 DEFAULT 'FM999,990.00') RETURN VARCHAR2;
    FUNCTION formatiere (p_datum DATE,      p_maske VARCHAR2 DEFAULT 'DD.MM.YYYY')   RETURN VARCHAR2;
    FUNCTION formatiere (p_text  VARCHAR2)  RETURN VARCHAR2;
END pkg_format;
/

CREATE OR REPLACE PACKAGE BODY pkg_format IS
    FUNCTION formatiere (p_zahl NUMBER, p_maske VARCHAR2 DEFAULT 'FM999,990.00') RETURN VARCHAR2 IS
    BEGIN
        RETURN TO_CHAR(p_zahl, p_maske);
    END;

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

    FUNCTION formatiere (p_text VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
        RETURN '"' || TRIM(p_text) || '"';
    END;
END pkg_format;
/

BEGIN
    DBMS_OUTPUT.PUT_LINE(pkg_format.formatiere(1234567.89));
    DBMS_OUTPUT.PUT_LINE(pkg_format.formatiere(SYSDATE));
    DBMS_OUTPUT.PUT_LINE(pkg_format.formatiere('  Hallo Welt  '));
END;
/

6. Private und öffentliche Elemente

6.1 Sichtbarkeit

CREATE OR REPLACE PACKAGE pkg_demo IS
    -- ÖFFENTLICH:
    FUNCTION öffentliche_funktion (p_x NUMBER) RETURN NUMBER;
END pkg_demo;
/

CREATE OR REPLACE PACKAGE BODY pkg_demo IS
    -- PRIVAT (nur intern verwendbar):
    FUNCTION private_hilfsfunktion (p_x NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN p_x * p_x;
    END;

    -- Öffentliche Funktion nutzt private:
    FUNCTION öffentliche_funktion (p_x NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN private_hilfsfunktion(p_x) + 1;
    END;
END pkg_demo;
/

7. Initialisierungsabschnitt

7.1 Einmalige Initialisierung

Der BEGIN-Abschnitt am Ende des Package Body wird genau einmal pro Session ausgeführt – beim ersten Aufruf eines Package-Elements:

CREATE OR REPLACE PACKAGE pkg_config IS
    v_server_name  VARCHAR2(50);
    v_db_version   VARCHAR2(20);
    v_start_zeit   DATE;
END pkg_config;
/

CREATE OR REPLACE PACKAGE BODY pkg_config IS
BEGIN
    -- Einmalige Initialisierung:
    SELECT host_name   INTO v_server_name FROM v$instance;
    SELECT version     INTO v_db_version  FROM v$instance;
    v_start_zeit := SYSDATE;

    DBMS_OUTPUT.PUT_LINE('Package initialisiert: ' || TO_CHAR(v_start_zeit, 'HH24:MI:SS'));
EXCEPTION
    WHEN OTHERS THEN
        v_server_name := 'Unbekannt';
        v_db_version  := 'Unbekannt';
END pkg_config;
/

8. Wichtige Oracle Standard-Packages

8.1 DBMS_OUTPUT

BEGIN
    DBMS_OUTPUT.ENABLE(1000000);      -- Puffer aktivieren (1 MB)
    DBMS_OUTPUT.PUT_LINE('Zeile 1');
    DBMS_OUTPUT.PUT('Teiltext ');
    DBMS_OUTPUT.NEW_LINE;
END;
/

8.2 DBMS_UTILITY

DECLARE
    v_start  NUMBER;
    v_ende   NUMBER;
BEGIN
    v_start := DBMS_UTILITY.GET_TIME();  -- Centisekunden

    -- Langsame Operation simulieren:
    FOR i IN 1..100000 LOOP NULL; END LOOP;

    v_ende := DBMS_UTILITY.GET_TIME();
    DBMS_OUTPUT.PUT_LINE('Dauer: ' || (v_ende - v_start) || ' cs');
END;
/

8.3 UTL_FILE – Dateizugriff

DECLARE
    v_file UTL_FILE.FILE_TYPE;
BEGIN
    -- Datei schreiben (Verzeichnis muss in DBA_DIRECTORIES definiert sein):
    v_file := UTL_FILE.FOPEN('MY_DIR', 'ausgabe.txt', 'W');
    UTL_FILE.PUT_LINE(v_file, 'Erste Zeile');
    UTL_FILE.PUT_LINE(v_file, 'Zweite Zeile: ' || TO_CHAR(SYSDATE));
    UTL_FILE.FCLOSE(v_file);
    DBMS_OUTPUT.PUT_LINE('Datei geschrieben.');
END;
/

8.4 DBMS_SCHEDULER – Jobs planen

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_DAILY_CLEANUP',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN cleanup_alte_logs(30); END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
        enabled         => TRUE,
        comments        => 'Tägliche Bereinigung nach 30 Tagen'
    );
END;
/

8.5 Übersicht häufig verwendeter Packages

9. Packages verwalten

9.1 Kompilieren und Status prüfen

-- Package Spec und Body kompilieren:
ALTER PACKAGE pkg_mitarbeiter COMPILE;
ALTER PACKAGE pkg_mitarbeiter COMPILE BODY;

-- Status aller Packages prüfen:
SELECT object_name, object_type, status, last_ddl_time
FROM   user_objects
WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
ORDER  BY object_name, object_type;

-- Fehler nach Kompilierung:
SELECT line, position, text
FROM   user_errors
WHERE  name = 'PKG_MITARBEITER'
ORDER  BY type, sequence;

9.2 Quellcode anzeigen

-- Spec anzeigen:
SELECT text FROM user_source
WHERE  name = 'PKG_MITARBEITER' AND type = 'PACKAGE'
ORDER  BY line;

-- Body anzeigen:
SELECT text FROM user_source
WHERE  name = 'PKG_MITARBEITER' AND type = 'PACKAGE BODY'
ORDER  BY line;

9.3 Package löschen

-- Nur Body löschen (Spec bleibt):
DROP PACKAGE BODY pkg_mitarbeiter;

-- Spec und Body löschen:
DROP PACKAGE pkg_mitarbeiter;

10. Zusammenfassung und Ausblick

10.1 Package-Aufbau auf einen Blick

pkg_mitarbeiter (Package)
├── Specification (öffentliche Schnittstelle)
│   ├── Konstanten: c_min_gehalt, c_max_gehalt
│   ├── Typen: t_emp_info, t_emp_cursor
│   ├── Prozeduren: gehaltserhöhung, zeige_abteilung
│   └── Funktionen: jahresgehalt, ist_manager
└── Body (Implementierung)
    ├── Private Variable: v_aufrufe
    ├── Implementierung aller Spec-Subprogramme
    └── Private Hilfsprozeduren/-funktionen

10.2 Wann ein Package?

10.3 Ausblick

Das letzte Kapitel behandelt Trigger – PL/SQL-Code, der automatisch beim Auftreten bestimmter Datenbankereignisse ausgeführt wird:

• DML-Trigger (BEFORE/AFTER INSERT, UPDATE, DELETE)
• Zeilenorientierte vs. statement-orientierte Trigger
• :NEW und :OLD Pseudorecords
• INSTEAD OF Trigger auf Views
• DDL- und System-Trigger