PL/SQL-Tipps

Cursor  Tipp 16

DECLARE
 CURSOR cur1 IS  SELECT empno,ename,sal
                 FROM emp
                 WHERE job = 'SALESMAN';
BEGIN
 FOR emp_satz IN cur1 LOOP
  dbms_output.put_line(
    emp_satz.empno||'  '||
    emp_satz.ename||'  '||
    emp_satz.sal);
 END LOOP;
END;
/

Cursor ohne Deklarationsteil direkt in FOR LOOP Schleife  Tipp 17

BEGIN
FOR emp_satz IN (SELECT empno,ename,sal
                 FROM emp
                 WHERE job = 'SALESMAN') LOOP
  dbms_output.put_line(
    emp_satz.empno||'  '||
    emp_satz.ename||'  '||
    emp_satz.sal      );
END LOOP;
END;
/

Cursor im Deklarationsteil + FOR LOOP + SELECT mit Funktion  Tipp 18

DECLARE
 CURSOR cur1 IS  SELECT empno,ename,
                (sal*12) as Jahresgehalt -- Beim Einsatz von Funktionen MUSS ein Aliasname verwendet werden
                 FROM emp
                 WHERE job = 'SALESMAN';
BEGIN
 -- Wir basteln eine schöne Ausgabe-Tabelle (bitte Fixed Size Font verwenden!)
 dbms_output.put_line(rpad('ID',10,' ')||rpad('NAME',10,' ')||rpad('J-Gehalt',10,' '));
 dbms_output.put_line(rpad('-',35,'-'));
 FOR emp_satz IN cur1 LOOP
  dbms_output.put_line( -- Formatierte Ausgabe der Spalten aus Cursor
  rpad(emp_satz.empno,10,' ')||
  rpad(emp_satz.ename,10,' ')||
  rpad(emp_satz.jahresgehalt,10,' '));
 END LOOP;
END;
/

REM Ausgabe:
ID        NAME      J-Gehalt  
-----------------------------------
7499      ALLEN     19200     
7521      WARD      15000     
7654      MARTIN    15000     
7844      TURNER    18000   

Cursor im Deklarationsteil + OPEN, FETCH, CLOSE  Tipp 19

DECLARE
 CURSOR curs IS  SELECT empno,ename,sal
                 FROM emp
                 WHERE job = 'SALESMAN';
 v_empno  emp.empno%TYPE;
 v_name   emp.ename%TYPE;
 v_sal    emp.sal%TYPE;
BEGIN
 OPEN curs;
 LOOP
  FETCH curs INTO v_empno,v_name,v_sal;
  EXIT WHEN curs%NOTFOUND OR curs%NOTFOUND IS NULL;
  dbms_output.put_line(v_empno||'  '||v_name||'  '||v_sal);
  dbms_output.put_line('Aktuelle Zeile :'||curs%rowcount);
 END LOOP;

 dbms_output.put_line('--- Eingelesene Zeilen :'||curs%rowcount);
 CLOSE curs;
 
END;
/

OPEN FETCH CLOSE CURSOR mit Parametern  Tipp 20

DECLARE
 CURSOR c1 (v_comm IN emp.comm%TYPE)
 IS SELECT ename,sal,job
    FROM emp
    WHERE comm=v_comm
    OR (comm IS null AND v_comm IS NULL);
 rec_c1  c1%rowtype; -- übernehme Spaltenstruktur des Cursor
BEGIN

 OPEN c1 (0); -- Parameter 0 in Cursor übergeben
 LOOP
  FETCH c1 INTO rec_c1;
  EXIT WHEN c1%notfound;
  dbms_output.put_line(rec_c1.ename||'  '||rec_c1.sal||'   '||rec_c1.job);
 END LOOP;
 CLOSE c1;
END;
/

 

OPEN FETCH CLOSE CURSOR mit Parametern und Formatierung  Tipp 21

DECLARE
 p_deptno emp.deptno%TYPE:=10; -- <==== Hier versteckt sich der Parameter
 CURSOR curs IS SELECT ename,sal,job FROM emp
 WHERE deptno=nvl(p_deptno,deptno ); -- und hier wird er eingesetzt
 rec_curs  curs%rowtype;
BEGIN
 OPEN curs;
 LOOP
  FETCH curs INTO rec_curs;
  EXIT WHEN curs%NOTFOUND OR curs%NOTFOUND IS NULL;
  dbms_output.put_line(
  rpad(rec_curs.ename,10,' ')||'  '||
  rpad(rec_curs.sal,6,' ')
  ||'   '||rec_curs.job);
 END LOOP;
 CLOSE curs;
END;
/

Einfacher CURSOR mit Parametern und NULL Werten  Tipp 22

SET SERVEROUTPUT ON
DECLARE
CURSOR emp_curs (p_comm IN NUMBER) IS
SELECT * FROM scott.emp
WHERE comm=p_comm OR ( comm is null and p_comm is null);

BEGIN
FOR rec_curs  IN emp_curs(0) LOOP
    dbms_output.put_line('Name='||rec_curs.ename);
END LOOP;
END;
/

REM Ausgabe:
Name=TURNER

Einfacher CURSOR mit Parametern und einem Trick  Tipp 23

SET SERVEROUTPUT ON
DECLARE
CURSOR emp_curs (p_comm IN NUMBER) IS
SELECT * FROM scott.emp
WHERE sal=coalesce(p_comm,sal); -- Wenn als Parameter NULL  übergeben wird, werden alle Zeilen zurückgegeben
BEGIN

FOR rec_curs  IN emp_curs(null) LOOP -- Aufruf emp_curs() ist nicht erlaubt!
    dbms_output.put_line('Name='||rec_curs.ename);
END LOOP;

END;
/

CURSOR ohne Deklartionsteil  Tipp 24

BEGIN
 FOR emp_satz IN (SELECT empno,ename,sal
                  FROM scott.emp
                  WHERE job = 'SALESMAN') LOOP
  dbms_output.put_line(emp_satz.empno||'  '||emp_satz.ename||'  '||emp_satz.sal);
 END LOOP;
END;
/

REM Ausgabe:
 7499 ALLEN 1600
 7521 WARD 1250
 7654 MARTIN 1250
 7844 TURNER 1500

dbms_output  Tipp 90

EXEC dbms_output.put_line('Hello');

RECORD  Tipp 37

DECLARE
 TYPE dept_type
 IS RECORD (deptno  dept.deptno%TYPE, --Datentyp der Deptno Spalte übernehmen
            dname   dept.dname%TYPE,
            loc     dept.loc%TYPE   );
 TYPE dept_type2 IS RECORD (deptno  dept.deptno%TYPE,
                           dname   dept.dname%TYPE,
                           loc     dept.loc%TYPE);
 abt1 dept_type; -- Variable vom Typ des Records anlegen
 abt2 dept_type;
 abt3 dept_type2;
BEGIN
 abt1.deptno := 10; -- Zuweisung von Werten in die Record-Feldelemente
 abt1.dname := 'Training';
 abt1.loc := 'München';
 abt2 := abt1;
 --abt3 := abt1; -- Records von verschiedenen Basistypen können nicht übertragen werden
 dbms_output.put_line(abt1.deptno||' '||abt1.dname||' '||abt1.loc);
END;
/

Schleife mit while loop  Tipp 106

DECLARE
i NUMBER(5,2):=0.00;

BEGIN
    WHILE i<5.00 LOOP -- Schleife bei i=5 verlassen
        dbms_output.put_line('i='||i);
        i:=i+0.50;
    END LOOP;
END;
/

Lokale benutzerdefinierte Exceptions  Tipp 7

DECLARE
anz        NUMBER(3);
e_toomany  EXCEPTION;

BEGIN
SELECT count(*)
INTO anz
FROM emp
WHERE job = 'SALESMAN';

 IF anz > 3 THEN
  RAISE e_toomany;
END IF;

EXCEPTION
WHEN e_toomany THEN
    dbms_output.put_line('Zu viele Verkäufer !');
END;
/

Sub Block Exceptions  Tipp 8

BEGIN
<<Block_1>> ------------------------------- Block 1 ---------
DECLARE
        
BEGIN    
    DBMS_OUTPUT.ENABLE(10000);                
    <<Block_2>> ---------------------------- Block 2 ---------
    DECLARE
        
    v_sal    emp.sal%TYPE;
    BEGIN            
        SELECT sal INTO v_sal FROM emp WHERE empno=10000;
            
    EXCEPTION
        WHEN ZERO_DIVIDE THEN
            DBMS_OUTPUT.PUT_LINE('Fehler in Block_2');
            DBMS_OUTPUT.PUT_LINE('Fehlertext :'||sqlerrm);
    END Block_2;
            
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Fehler in Block_1');
        DBMS_OUTPUT.PUT_LINE('Fehlertext :'||sqlerrm);
END Block_1;
END;
/

Ergebnis: Fehler in Block_1 Fehlertext :ORA-01403: Keine Daten gefunden

Anonymer Block  Tipp 87

DECLARE
  zahl integer := 0;
BEGIN
   zahl := zahl + 1;
   dbms_output.put_line('Zahl steht auf '|| zahl);
END;

Fehlervariablen SQLCODE + SQLERRM  Tipp 9

DECLARE
v_sqlcode NUMBER;
v_sqlerrm VARCHAR2(512);
v_errpos  VARCHAR2(4000);
BEGIN
NULL;
EXCEPTION WHEN OTHERS THEN

 v_sqlcode := sqlcode; -- Fehlernummer
 v_sqlerrm := sqlerrm; -- Fehlernummer und Fehlertext
 v_errpos :=DBMS_UTILITY.FORMAT_ERROR_BACKTRACE; -- Informationen über Zeilennummer
END;

Oracle´s vordefinierte Exception  Tipp 10

BEGIN
NULL;
EXCEPTION
 WHEN NO_DATA_FOUND           THEN dbms_output.put_line('Keinen Datensatz gefunden, oder beim Dateilesen keine Zeile gefunden!');
 WHEN DUP_VAL_ON_INDEX        THEN dbms_output.put_line('Doppelter Datensatz in Primärschlüsselspalte! ORA-00001');
 WHEN TIMEOUT_ON_RESOURCE     THEN dbms_output.put_line('Timeout bei Warten auf Resource ORA-00051');
 WHEN INVALID_CURSOR          THEN dbms_output.put_line('Zugriff auf einen nicht geöffneten Cursor ORA-01001');
 WHEN NOT_LOGGED_ON           THEN dbms_output.put_line('Befehel wurde vor der Anmeldung durchgeführt ORA-01012');
 WHEN LOGIN_DENIED            THEN dbms_output.put_line('Anmeldung mit falschen Passwort ORA-01017');
 WHEN TOO_MANY_ROWS           THEN dbms_output.put_line('SELECT INTO lieferte mehr als eine Zeile zuück ORA-01422');
 WHEN ZERO_DIVIDE             THEN dbms_output.put_line('Division durch 0 ORA-01476');
 WHEN INVALID_NUMBER          THEN dbms_output.put_line('Umwandlung von Text in Zahl nicht erfolgreich ORA-01722');
 WHEN STORAGE_ERROR           THEN dbms_output.put_line('Hauptspeicher ausgegangen ORA-06500');
 WHEN PROGRAM_ERROR           THEN dbms_output.put_line('Internes Problem ORA-06501');
 WHEN VALUE_ERROR             THEN dbms_output.put_line('Fehler bei Umwandlung von Zahl oder String ORA-06502');
 WHEN CURSOR_ALREADY_OPEN     THEN dbms_output.put_line('Geöffneter Cursor wurde versucht nochmals zu öffnen ORA-06511');
END;
/

Sonstige Oracle Exceptions  Tipp 11

DECLARE
 e_nullcol  EXCEPTION;
 PRAGMA EXCEPTION_INIT (e_nullcol,-1400); -- Fehlernummer bereits von Oracle vergeben

BEGIN
 INSERT INTO dept
 VALUES (NULL,'TRAINING','FRANKFURT');

EXCEPTION
 WHEN e_nullcol THEN  
    dbms_output.put_line('NOT-NULL-Column is missing !');
END;
/

Bulk Cursor mit DELETE  Tipp 12

DECLARE
TYPE big_tab_type IS TABLE OF
    scott.big_tab.object_id%TYPE;
mybig_tab     big_tab_type;
summe        NUMBER:=0;
BEGIN
SELECT object_id BULK COLLECT INTO mybig_tab
FROM scott.big_tab;
FORALL i IN mybig_tab.first .. mybig_tab.last
    DELETE FROM scott.big_tab
    WHERE object_id=mybig_tab(i);
END;
/

Schleife mit while loop  Tipp 13

DECLARE
i NUMBER(5,2):=0.00;

BEGIN
    WHILE i<5.00 LOOP -- Schleife bei i=5 verlassen
        dbms_output.put_line('i='||i);
        i:=i+0.50;
    END LOOP;
END;
/

Formatierte Ausgabe in PL/SQL  Tipp 3

BEGIN
-- Für Texte String auf 10 Zeichen mit Leerzeichen rechts auffüllen
dbms_output.put_line(rpad('Marco',10,' ')||'#');
dbms_output.put_line(rpad('Uli',10,' ')||'#');

-- Für Zahlen auf 10 Zeichen mit Leerzeichen links auffüllen
dbms_output.put_line(lpad('1,234',10,' '));
dbms_output.put_line(lpad('1000,567',10,' '));
END;
/

Returning Klausel  Tipp 4

DECLARE
v_empno NUMBER;
BEGIN
UPDATE scott.emp SET sal=sal
WHERE ename='KING'
RETURNING empno INTO v_empno; -- Empno des King zurückgeben
dbms_output.put_line(v_empno);

INSERT INTO SCOTT.emp (empno,ename) VALUES (8000,'MARCO')
RETURNING empno INTO v_empno; -- 8000 wird wieder zurückgegeben
dbms_output.put_line(v_empno);

DELETE FROM scott.emp WHERE empno=8000
RETURNING empno INTO v_empno; -- 8000 wird wieder zurückgegeben
dbms_output.put_line(v_empno);
END;
/

 

Bulk Cursor mit Update  Tipp 5

DECLARE
TYPE big_tab_type IS TABLE OF
    scott.big_tab.object_id%TYPE;
mybig_tab     big_tab_type;
summe        NUMBER:=0;
BEGIN
SELECT object_id BULK COLLECT INTO mybig_tab
FROM scott.big_tab;
FORALL i IN mybig_tab.first .. mybig_tab.last
    UPDATE emp2 set object_name=object_name
    WHERE object_id=mybig_tab(i);
END;
/
REM Zweite Variante (mit zwei Arrays!)
DECLARE
TYPE emp_tab_type IS TABLE OF scott.emp%ROWTYPE;
TYPE pk_tab_type IS TABLE OF scott.emp.empno%TYPE; -- INDEX BY BINARY_INTEGER;
emp_tab     emp_tab_type;
emp_pk         pk_tab_type;
BEGIN
SELECT e.* BULK COLLECT INTO emp_tab FROM scott.emp e;
emp_pk:=pk_tab_type();
FOR i IN emp_tab.first .. emp_tab.last LOOP
    emp_pk.extEND;
    emp_pk(i):=emp_tab(i).empno;
END LOOP;
FORALL i IN  emp_pk.first .. emp_pk.last
     UPDATE emp
     SET   row=emp_tab(i)
     WHERE empno = emp_pk(i);
END;
/

Exception Handling a la MARCO :-)  Tipp 6

DROP TABLE err_tab;
CREATE TABLE err_tab (
c_sqlcode     NUMBER,
c_sqlerrm     VARCHAR2(4000),
c_errpos      VARCHAR2(100),
c_modulname   VARCHAR2(4000),
c_user        VARCHAR2(128),
c_time        DATE,
c_comment     VARCHAR2(4000),
c_vars        CLOB);

CREATE OR REPLACE PROCEDURE error_handling (
p_sqlcode         IN NUMBER,
p_sqlerrm         IN VARCHAR2,
p_errpos          IN VARCHAR2,
p_user            IN VARCHAR2,
p_time              IN DATE DEFAULT SYSDATE,
p_comment          IN VARCHAR2 DEFAULT '',
p_vars              IN VARCHAR2,
p_mode        IN VARCHAR2 DEFAULT 'T'
) IS
owner_name    VARCHAR2 (128);
caller_name   VARCHAR2 (128);
line_number   NUMBER;
caller_type   VARCHAR2 (128);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
OWA_UTIL.WHO_CALLED_ME (owner_name,caller_name,line_number,caller_type);
IF instr(p_mode,'T')>0 THEN -- Ausgabe in Tabelle
  INSERT INTO err_tab (c_sqlcode,c_sqlerrm,c_errpos,c_modulname,c_user,c_time,c_comment,c_vars )
  VALUES (p_sqlcode,p_sqlerrm,p_errpos,caller_type||' '||caller_name,p_user,p_time,p_comment,p_vars);
  COMMIT;
ELSIF instr(p_mode,'F')>0 THEN -- Ausgabe in Datei
  NULL; /* To be defined */
END IF;
END;
/

CREATE OR REPLACE PROCEDURE ptest IS
v_sqlcode NUMBER;
v_sqlerrm VARCHAR2(512);
v_errpos  VARCHAR2(512);
i         NUMBER;
BEGIN
NULL; -- Hier könnte Ihr Code stehen
i:=1/0;
EXCEPTION
    WHEN OTHERS THEN
        v_sqlcode:=sqlcode;
        v_sqlerrm:=sqlerrm;
        v_errpos:=DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
        error_handling(v_sqlcode,v_sqlerrm,v_errpos,user,sysdate,null,'i=1/0','T');
END;
/

exec ptest
SELECT * from err_tab;

Deklaration von Datentypen  Tipp 1

DECLARE
var_a NUMBER:=1;
var_b VARCHAR2(2001):='ABCDEF';
var_c CHAR(100):='ABCDEF';
var_d DATE:=sysdate;
var_e CLOB;
var_f BLOB;
var_g BOOLEAN:=TRUE;
var_h BINARY_FLOAT;
var_i BINARY_DOUBLE;
var_j ROWID;
BEGIN
NULL;
END;
/

Erklärung:
Variablen müssen im Deklarationsteil erwähnt werden
Sie sollten jedoch mit einer Zuweisung von Werten auf die Variablen vorsichtig sein, weil ein evtl. Fehler nicht vom lokalen Fehlerbehandlungsteil abgefangen wird.

Dual Tabelle  Tipp 86

SELECT * FROM dual;

INSTR  Tipp 88

instr (<col|string>,<suchstring>,<anf_pos>)

NVL Funktion  Tipp 89

SELECT nvl(null,'X') FROM dual;

PL/SQL Konvertierungen  Tipp 2

REM Text in Datum:

SELECT to_date('01.01.2005') FROM dual;
SELECT to_date('01.01.2005','DD.MM.YYYY') FROM dual;
SELECT to_date('01.Jan.2005','DD.MON.YYYY','nls_date_language=german')
FROM dual;

 

SELECT TO_TIMESTAMP ('19-Sep-15 17:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
FROM DUAL;

=>19.09.15 17:10:10,123000000

Datum in Text

SELECT TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS') FROM dual;

=>07.11.2006 10:05:35

 

SELECT TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF') FROM dual;

=>10:09:23.343000

SELECT TO_CHAR(SYSTIMESTAMP,'FF4') FROM dual;
=>3280

SELECT TO_CHAR(100.00,'L9G999D99',
   ' NLS_NUMERIC_CHARACTERS = '',.''
     NLS_CURRENCY            = ''€''
   ') "Gehalt"
     FROM DUAL;
=>€100,00

Text in Zahl

SELECT TO_NUMBER('8.778') FROM dual;
SELECT TO_NUMBER('-Euro100','L9G999D99',
' NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''Euro'' ') "Gehalt"
FROM DUAL;
=>-100

 

SELECT TO_NUMBER('-100,123','999D999',
' NLS_NUMERIC_CHARACTERS = '',.'' ') "Gehalt"
FROM DUAL;
=>-100,123

     
Ersetzte NULL in etwas anderes:

SELECT NVL(col,0) FROM mytab;    -- Wenn Spalte col eine Zahl
SELECT NVL(col,'NA') FROM mytab; -- Wenn Spalte col ein Text
SELECT NVL(NULL,SYSDATE) FROM dual; -- Wenn Spalte col ein Datum ist

Text In Clob (ab 9i):

SELECT TO_CLOB('Text') FROM dual;
SELECT TO_LOB(long_column) FROM dual;

Buchstabe IN ASCII:

SELECT ASCII('A') FROM dual;
=>65

ASCII IN Buchstabe:

SELECT CHR(65) FROM dual;
=>A

RAW IN HEX:

SELECT rawtohex('AA') from dual;
=>4141

Syntax: TO_BINARY_DOUBLE(expr [,fmt [, 'nlsparam' ] ])
Syntax: TO_BINARY_FLOAT(expr [,fmt [, 'nlsparam' ] ])

SELECT 1234.56, TO_BINARY_FLOAT(1234.56) FROM dual;

Cursor auf Tabellen-Join  Tipp 26

DECLARE 

CURSOR cur_emp_dept IS SELECT e.ename,e.sal,d.dname,d.loc
                FROM scott.emp e, scott.dept d
                WHERE e.deptno=d.deptno
                AND e.deptno=10;                           
BEGIN
    FOR emp_dept_satz IN cur_emp_dept LOOP
        dbms_output.put(' Mitarbeiter: '||emp_dept_satz.ename);
        dbms_output.put(' Gehalt: '        ||emp_dept_satz.sal);
        dbms_output.put(' Abteilung: '    ||emp_dept_satz.dname);
        dbms_output.put(' Ort: '        ||emp_dept_satz.loc);
        dbms_output.new_line;
    END LOOP;
END;
/

REM Ausgabe:
Mitarbeiter: CLARK Gehalt: 2450 Abteilung: ACCOUNTING Ort: NEW YORK
Mitarbeiter: MILLER Gehalt: 1300 Abteilung: ACCOUNTING Ort: NEW YORK

 

Update auf Cursor mit FOR UPDATE  Tipp 27

 DECLARE
 CURSOR cur1 IS  SELECT empno,ename,sal
                 FROM scott.emp
                 FOR UPDATE;
BEGIN
 FOR emp_satz IN cur1 LOOP
  IF emp_satz.ename='KING' then -- der bekommt eine Gehaltskürzung
    UPDATE scott.emp SET sal=sal-1
    WHERE CURRENT OF cur1;
    dbms_output.put_line('Gehaltskürzung für: '||emp_satz.ename);
  END IF;
 END LOOP;

END;
/

DELETE auf Cursor mit FOR UPDATE  Tipp 28

DECLARE
 CURSOR cur1 IS  SELECT empno,ename,sal
                 FROM scott.emp
                 FOR UPDATE;
BEGIN
 FOR emp_satz IN cur1 LOOP
  IF emp_satz.ename='KING' then -- der wird aus der Firma geworfen
    DELETE FROM  scott.emp
    WHERE CURRENT OF cur1;
    dbms_output.put_line('Ein Stelle als Chef ist frei, denn der ist weg: '||emp_satz.ename);
  END IF;
 END LOOP;

END;
/

Cursor im Package wird über die Procedure offen gelassen  Tipp 29

CREATE OR REPLACE PACKAGE cur_pack AS
procedure cursoroeffne;
procedure ausgabe;
procedure cursorzu;
END;
/

CREATE OR REPLACE PACKAGE BODY cur_pack AS
CURSOR c_emp IS SELECT * from scott.emp;
rec_emp c_emp%ROWTYPE;

procedure cursoroeffne
is
BEGIN
 open c_emp;
END;

procedure ausgabe
is
BEGIN
 for i in 1 .. 3 loop
     if not c_emp%isopen then
         open c_emp;
     END if;
     fetch c_emp into rec_emp;
     dbms_output.put_line('Name:'||rec_emp.ename);
     dbms_output.new_line;
 END loop;
END;

procedure cursorzu
is
BEGIN    
 close c_emp;
END;

END;
/

EXEC cur_pack.cursoroeffne;
EXEC cur_pack.ausgabe;
EXEC cur_pack.cursorzu;

BULK-einlesen einer Spalte in PL/SQL Tabelle  Tipp 30

ALTER SYSTEM FLUSH buffer_cache;

DECLARE
TYPE big_tab_type IS TABLE OF
    scott.big_tab.object_id%TYPE;
mybig_tab     big_tab_type;
summe        NUMBER:=0;
BEGIN
SELECT object_id BULK COLLECT INTO mybig_tab
FROM scott.big_tab;
FOR i IN mybig_tab.first .. mybig_tab.last LOOP
    summe:=summe+nvl(mybig_tab(i),0);
END LOOP;
dbms_output.put_line('Summe='||summe);
END;
/

Bulk Cursor mit LIMIT Klausel  Tipp 31

DECLARE CURSOR
big_cur IS
SELECT object_id FROM scott.big_tab;
TYPE big_tab_type IS TABLE OF scott.big_tab.object_id%TYPE;
mybig_tab big_tab_type; summe NUMBER:=0;
teil_summe NUMBER:=0;
BEGIN OPEN big_cur;
LOOP FETCH big_cur BULK COLLECT INTO mybig_tab LIMIT 100;
FOR i IN 1.. mybig_tab.count LOOP
summe:=summe+mybig_tab(i);
END LOOP;
EXIT WHEN big_cur%notfound;
END LOOP;
CLOSE big_cur;
END;
/

Schleife mit LOOP ... END LOOP  Tipp 32

Schleife mit FOR LOOP ... END LOOP  Tipp 33

BEGIN
FOR i IN REVERSE 1.. 10 LOOP -- Schleife rückwärts von 10 bis 1 durchlaufen
  dbms_output.put_line('Schleife Nr. '||i);
END LOOP;
END;
/

PL/SQL Beispiele  Tipp 34

BEGIN
-- Für Texte String auf 10 Zeichen mit Leerzeichen rechts auffüllen
dbms_output.put_line(rpad('Marco',10,' '));
dbms_output.put_line(rpad('Uli',10,' '));

-- Für Zahlen auf 10 Zeichen mit Leerzeichen links auffüllen
dbms_output.put_line(lpad('1,234',10,' '));
dbms_output.put_line(lpad('1000,567',10,' '));
END;
/

PL/SQL Beispiele, Returning Klausel  Tipp 35

DECLARE
v_empno NUMBER;
BEGIN
UPDATE scott.emp SET sal=sal
WHERE ename='KING'
RETURNING empno INTO v_empno; -- Empno des King zurückgeben
dbms_output.put_line(v_empno);

INSERT INTO SCOTT.emp (empno,ename) VALUES (8000,'MARCO')
RETURNING empno INTO v_empno; -- 8000 wird wieder zurückgegeben
dbms_output.put_line(v_empno);

DELETE FROM scott.emp WHERE empno=8000
RETURNING empno INTO v_empno; -- 8000 wird wieder zurückgegeben
dbms_output.put_line(v_empno);
END;
/

ROWTYPE  Tipp 38

Subblöcke  Tipp 39

BEGIN

    <<Block_1>> ------------------------------- Block 1 ---------
    DECLARE
    var_i    number:=1;
    var_j    number:=1;

    BEGIN
        DBMS_OUTPUT.PUT_LINE(' Block 1,Variable var_i:='||var_i);
        DBMS_OUTPUT.PUT_LINE(' Variable var_j:='||var_j);
       
        <<Block_2>> ---------------------------- Block 2 (Subblock)--
        DECLARE
        var_i    number:=2;
        BEGIN
            DBMS_OUTPUT.PUT_LINE(' Block 2, Variable var_i:='||var_i);
            DBMS_OUTPUT.PUT_LINE(' Variable var_j:='||var_j);                       
        END Block_1;       
    END;
END;

/

CASE  Tipp 40

DECLARE
v_job scott.emp.job%TYPE;
BEGIN
SELECT max(job) INTO v_job FROM emp WHERE empno=7938;
CASE
WHEN v_job='CLERK'    THEN dbms_output.put_line('CLERK');
WHEN v_job='SALESMAN' THEN dbms_output.put_line('SALESMAN');
ELSE dbms_output.put_line('??');
END CASE;
END;
/

Collection mit Datentyp Basis ist eine Tabelle  Tipp 41

DECLARE
TYPE emp_type IS TABLE OF scott.emp%ROWTYPE
INDEX BY BINARY_INTEGER;
l_data  emp_type;
BEGIN
    l_data(1).empno:=8000;
    l_data(1).ename:='Patzwahl';
    l_data(5).empno:=8001;
    l_data(5).ename:='Huberl';
END;
/

BULK-einlesen IN Record-Array mit LIMIT  Tipp 42

DECLARE
    CURSOR c IS
        SELECT * FROM scott.emp;
    TYPE emp_tab_type IS TABLE OF c%ROWTYPE;
    emp_tab emp_tab_type;
BEGIN
OPEN c;
LOOP
 FETCH c BULK COLLECT
 INTO emp_tab LIMIT 5;
 FOR i IN 1 .. emp_tab.count LOOP
   dbms_output.put_line('Zeile='||i||' Name='||emp_tab(i).ename);
 END LOOP;
 EXIT WHEN c%notfound;
END LOOP;
CLOSE c;
END;
/

SAVE Exceptions  Tipp 44

SQL%BULK_EXCEPTIONS.COUNT
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
SQL%BULK_EXCEPTIONS(i).ERROR_CODE
DECLARE
  TYPE  emp_rec IS RECORD(empno number, ename varchar2(100));
  TYPE emp_tab_type IS TABLE OF emp_rec
  INDEX BY BINARY_INTEGER;
  t_emp emp_tab_type;
BEGIN
  --Array füllen
  t_emp(1).empno:=8010;
  t_emp(1).ename:='MARCOOOOOOOOOOOO';
  t_emp(2).empno:=8011;
  t_emp(2).ename:='MARCO';
  t_emp(3).empno:=8012;
  t_emp(3).ename:='MARCOsafsafsdfsdfsdfsd';
  FORALL i IN 1 .. t_emp.count SAVE EXCEPTIONS
  INSERT INTO emp (empno,ename) values (t_emp(i).empno,t_emp(i).ename);

  EXCEPTION WHEN OTHERS THEN
  for i in 1 .. sql%bulk_exceptions.count loop
    dbms_output.put_line('Fehler ' || i || ' aufgetreten an Stelle:'||
    SQL%BULK_EXCEPTIONS(i).ERROR_INDEX||' Wert:'||t_emp(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX).ename);
    dbms_output.put_line('Oracle error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
  END loop;
END;
/

• SQL%BULK_EXCEPTIONS.COUNT
• SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
• SQL%BULK_EXCEPTIONS(i).ERROR_CODE

BULK-einlesen IN Record-Array einer PL/SQL Tabelle  Tipp 45

DECLARE
    CURSOR emp_cur IS
        SELECT * FROM scott.emp;
    TYPE emp_tab_type IS TABLE OF emp_cur%ROWTYPE;
    emp_tab emp_tab_type;
BEGIN
    OPEN emp_cur;
    FETCH emp_cur BULK COLLECT
    INTO emp_tab;    
       CLOSE emp_cur;
END;
/

Rückgabe der Function ist Cursor  Tipp 46

CREATE OR REPLACE PACKAGE plsql_tab IS
TYPE tab_type IS TABLE OF scott.emp%ROWTYPE
INDEX BY BINARY_INTEGER;
p_tab tab_type;
FUNCTION getptab RETURN tab_type;
END;
/
show errors
   
CREATE OR REPLACE PACKAGE BODY plsql_tab IS
FUNCTION getptab RETURN tab_type IS
BEGIN
   SELECT * BULK COLLECT INTO p_tab FROM scott.emp;
   --dbms_output.put_line(p_tab(p_tab.first).ename);
   RETURN p_tab;
END;
END;
/
show errors

REM IN SQL:Geht nicht weil Rückgabetype nur in PL/SQL definiert!
SELECT * FROM TABLE(cast(plsql_tab.gettab as ref_type));
SELECT plsql_tab.gettab FROM dual;

REM IN PL/SQL: Geht
DECLARE
my_tab plsql_tab.tab_type;
BEGIN
my_tab:=plsql_tab.getptab;
END;
/

Rückgabe der Function in einen Typ  Tipp 47

CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(2001);
/

CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAY
AS
  l_data EmpArray := EmpArray();
  CURSOR c_emp IS SELECT ename FROM EMP;
  BEGIN
    FOR emp_rec IN c_emp LOOP
      l_data.extend;
      l_data(l_data.count) := emp_rec.ename;
    END LOOP;
    RETURN l_data;
END;
/

SELECT getemparray FROM dual;

/*GETEMPARRAY
--------------------------------------------------------------------------------
EMPARRAY('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT',
 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')
*/

Package Overloading  Tipp 48

CREATE OR REPLACE PACKAGE overload IS
 FUNCTION double (var1 number) RETURN NUMBER;
 --pragma restrict_references (double, WNDS,WNPS,RNPS,RNDS);
 FUNCTION double (var1 varchar2) RETURN VARCHAR2;
 --pragma restrict_references (double, WNDS,WNPS,RNPS,RNDS);
END;
/

CREATE OR REPLACE PACKAGE BODY overload IS
 FUNCTION double (var1 NUMBER) RETURN NUMBER
 IS
 BEGIN
  RETURN var1*2;
 END;
 FUNCTION double (var1 VARCHAR2) RETURN VARCHAR2
 IS
 BEGIN
  RETURN var1||'-'||var1;
 END;
END;
/

Package Body  Tipp 50

CREATE OR REPLACE PACKAGE BODY pack_test IS
  PROCEDURE mehr_gehalt(mit_nr in number) IS
  BEGIN
   NULL;
  END;
  FUNCTION berechnen (x in number) return number
  IS
  BEGIN
   RETURN -1;
  END;
  -- Private Procedure
  PROCEDURE geheim IS
  BEGIN
   NULL;
  END;
END;

Package mit Initialisierungsteil  Tipp 51

CREATE OR REPLACE PACKAGE BODY p_body
IS
...
BEGIN
  SELECT count(*) into g_var from emp;
END;
/

Cursor bleibt im Package offen  Tipp 52

CREATE OR REPLACE PACKAGE my_curs AS
procedure cursorauf;
procedure ausgabe (zeilenanzahl NUMBER);
procedure cursorzu;
END;
/

CREATE OR REPLACE PACKAGE BODY my_curs AS
CURSOR c_emp IS SELECT * FROM emp;
rec_emp c_emp%ROWTYPE;

PROCEDURE cursorauf
IS
BEGIN
 OPEN c_emp;
 FETCH c_emp INTO rec_emp;
 dbms_output.put_line('Name:'||rec_emp.ename);
 dbms_output.new_line;
END cursorauf;

PROCEDURE ausgabe(zeilenanzahl NUMBER)
IS
BEGIN
    IF NOT c_emp%isopen THEN
         OPEN c_emp;
     END IF;
    FOR I IN 1 .. zeilenanzahl LOOP
        FETCH c_emp INTO rec_emp;
        EXIT WHEN c_emp%NOTFOUND OR c_emp%NOTFOUND IS NULL;
        dbms_output.put_line('Name:'||rec_emp.ename);
 END LOOP;
END ausgabe;

PROCEDURE cursorzu is
BEGIN    
 CLOSE c_emp;
END cursorzu;
END my_curs;
/

exec my_curs.cursorauf;
exec my_curs.ausgabe(5);

exec my_curs.cursorzu;

Return Datentyp NUMBER  Tipp 53

CREATE OR REPLACE FUNCTION f
  ( v IN NUMBER) RETURN NUMBER IS
BEGIN
    RETURN mod(var,2) ;
END;
/

Return Datentyp DATE  Tipp 54

CREATE OR REPLACE FUNCTION f
  ( v IN DATE) RETURN DATE IS
BEGIN
    RETURN (v+1);
END;
/

SELECT f(sysdate) FROM dual;

Return Datentyp CLOB  Tipp 55

CREATE OR REPLACE FUNCTION f
  ( v IN CLOB) RETURN CLOB IS
BEGIN
    RETURN (v);
END;
/

SELECT f(sysdate) FROM dual;

Return Datentyp RECORD  Tipp 56

CREATE OR REPLACE TYPE rec_emp AS OBJECT(
empno  NUMBER, -- scott.emp.empno%TYPE GEHT NICHT !
ename  VARCHAR2(10),
sal    NUMBER,
deptno NUMBER);
/

CREATE OR REPLACE FUNCTION f
  ( v IN NUMBER) RETURN rec_emp IS
r rec_emp;
BEGIN
    SELECT empno,ename,sal,deptno
    INTO r.empno,r.ename,r.sal,r.deptno -- v_rec_emp geht nicht
    FROM scott.emp WHERE empno=v;
    RETURN r;
END;
/
show errors

Oder Record wird intern definert  Tipp 57

DECLARE
  TYPE t IS RECORD (empno NUMBER, ename VARCHAR2(100));
  my_t   t;
 
  FUNCTION f (
  p_empno IN NUMBER,
  p_ename IN VARCHAR2)
  RETURN t IS
    v_res t;
  BEGIN
    v_res.empno := p_empno;
    v_res.ename := p_ename;
    RETURN v_res;
  END f;
BEGIN
  my_t := f(7839,'KING');
  DBMS_OUTPUT.PUT_LINE('ID='||my_t.empno||' Name= '||my_t.ename);
END;
/

Collections bzw. Arrays  Tipp 109

DECLARE

TYPE array_t IS VARRAY(3) OF VARCHAR2(10);

array_v array_t := array_t('Matt', 'Joanne', 'Robert');

BEGIN

FOR i IN 1..array_v.count LOOP

dbms_output.put_line(array_v(i));

END LOOP;

END;

/

 

Audit Trigger: Verbesserte Version  Tipp 82

CREATE OR REPLACE TRIGGER scott.emp_trig_idu
BEFORE UPDATE OR INSERT OR DELETE
ON scott.emp_trig
FOR EACH ROW
BEGIN
  INSERT INTO scott.emp_audit
  (empno,old_sal,new_sal,user_name,change_date) values
  (coalesce(:old.empno,:new.empno),:old.sal,:new.sal,user,sysdate);
END;
/

Test-Befehle:
UPDATE scott.emp_trig SET sal=4999 WHERE empno=7934;
DELETE FROM scott.emp_trig WHERE empno=7839;
INSERT INTO scott.emp_trig (empno,ename,deptno,sal) values (817,'Marco',20,5000); SELECT event,empno,old_sal,new_sal,change_date,user_name FROM scott.emp_audit;

Trigger stürzt ab, wenn Gehalt zu hoch  Tipp 83

CREATE OR REPLACE TRIGGER scott.emp_trig_idu
BEFORE UPDATE
ON scott.emp_trig
FOR EACH ROW
BEGIN
  IF :new.sal>:old.sal*1.1 THEN
        RAISE_APPLICATION_ERROR(-20001,'Gehaltssprung zu hoch (mehr als 10% sind verboten)!');
  END IF;
END;
/

UPDATE scott.emp_trig set sal=9000;

Collection mit Basis ist ein RECORD  Tipp 84

DECLARE
TYPE mytyp IS RECORD (
vorname  VARCHAR2(100),
nachname VARCHAR2(100));
TYPE numarray IS TABLE OF mytyp
INDEX BY BINARY_INTEGER;
l_data  numarray;
BEGIN
    l_data(1).vorname:='Marco';
    l_data(1).nachname:='Patzwahl';
    l_data(0).vorname:='Hans';
    l_data(0).nachname:='Wurst';
  for i in 0 .. l_data.count-1 LOOP
    dbms_output.put_line('Name='||l_data(i).vorname||' '||l_data(i).nachname);
  END LOOP;
END;
/
DECLARE
TYPE mytyp IS RECORD (
vorname  VARCHAR2(100),
nachname VARCHAR2(100));
TYPE numarray IS TABLE OF mytyp
INDEX BY BINARY_INTEGER;
l_data  numarray;
l_rtyp  mytyp;
BEGIN
    l_rtyp.vorname:='Peter';
    l_rtyp.nachname:='Kraus';
    l_data(2):=l_rtyp;
  for i in 0 .. l_data.count-1 LOOP
    dbms_output.put_line('Name='||l_data(i).vorname||' '||l_data(i).nachname);
  END LOOP;
END;
/
DECLARE
TYPE mytyp IS RECORD (
owner  VARCHAR2(30),
name  VARCHAR2(30),
text  VARCHAR2(4000)
);
TYPE numarray IS TABLE OF mytyp
INDEX BY BINARY_INTEGER;
l_data  numarray;
l_rtyp  mytyp;
BEGIN
   dbms_output.put_line('Zeit:'||systimestamp);
   FOR r in (SELECT owner,name,text,rownum as rn from all_source) LOOP
    l_rtyp.owner:=r.owner;
    l_rtyp.name:=r.name;
    l_rtyp.text:=r.text;
    l_data(r.rn):=l_rtyp;
   END LOOP;
    dbms_output.put_line('Anzahl:'||l_data.count);
    dbms_output.put_line('Zeit:'||systimestamp);
END;
/

Procedure Identifizierung der Rechte vom aktuellen Benutzer für Kompilierung  Tipp 66

CREATE OR REPLACE PROCEDURE ptest (
name     varchar2,
beruf     varchar2)
AUTHID CURRENT_USER --<=== Rechte des startenden Benutzers verwenden
IS
BEGIN
dbms_output.put_line('Hr. '||name||' ist von Beruf '||beruf);
END;
/

Autonome Transation  Tipp 67

CREATE OR REPLACE PROCEDURE ptest (
name     varchar2,
beruf    varchar2)
IS
PRAGMA AUTONOMOUS_TRANSACTION; --<== Läuft quasi in einer eigenen Session ab
BEGIN
dbms_output.put_line('Hr. '||name||' ist von Beruf '||beruf);
update emp set ename=name, job=beruf
where empno=8000;
COMMIT; -- Commit oder Rollback ist bei einer Autonomen Transaktion Pflicht
END;
/

Package Header  Tipp 68

CREATE OR REPLACE PACKAGE pack_test
is
 -- Globale Variablen
 g_var  number(5);
 
 -- Globale Proceduren
 procedure mehr_gehalt(mit_nr in number);

 --Globale Funktionen
 function berechnen (x in number) return number;
 
 --Globale Cursor
 cursor C_PACK is SELECT ename,sal from emp;
 
 -- Globale Exceptions
  value_to_long_fehler  exception;
  pragma exception_init (value_to_long_fehler,-1438);
 
END pack_test;
/

Zwei Cursor  Tipp 70

DECLARE
 CURSOR cur1 (p_comm number DEFAULT NULL) is
            SELECT empno,ename
             FROM scott.emp WHERE comm = p_comm;
 CURSOR cur2  IS
            SELECT empno,ename
             FROM scott.emp WHERE COMM IS NULL;            
 emp_satz  cur1%ROWTYPE;
 p_curs emp.comm%type :=NULL;
BEGIN
 if p_curs is not null then
     OPEN cur1(p_curs);
     LOOP
          FETCH cur1 INTO emp_satz;
          EXIT WHEN cur1%NOTFOUND;
          dbms_output.put_line(emp_satz.empno||'   '||emp_satz.ename);
     END LOOP;

     dbms_output.put_line(' Eingelesene Zeilen :'||cur1%rowcount);
     CLOSE cur1;
 ELSE
    OPEN cur2;
     LOOP
          FETCH cur2 INTO emp_satz;
          EXIT WHEN cur2%NOTFOUND;
          dbms_output.put_line(emp_satz.empno||'   '||emp_satz.ename);
     END LOOP;
     dbms_output.put_line(' Eingelesene Zeilen :'||cur2%rowcount);
     CLOSE cur2;
 END IF;
END;
/

geschachtelte Cursor (Kurze Version)  Tipp 71

DECLARE
 CURSOR cur_emp (p_deptno IN NUMBER) IS SELECT *
                FROM scott.emp
                WHERE deptno=p_deptno;
 CURSOR cur_dept IS SELECT * FROM scott.dept;                               
BEGIN
 FOR dept_satz IN cur_dept LOOP
    FOR emp_satz IN cur_emp(dept_satz.deptno) LOOP -- Hier wird die Abt-Nr eingesetzt (10,20,30,40)
        dbms_output.put_line('Abt: '||dept_satz.dname||' Mitarbeiter: '||emp_satz.ename);
    END LOOP;
 END LOOP;
END;
/

 

geschachtelte Cursor (Lange Version)  Tipp 72

DECLARE
 CURSOR cur_emp (p_deptno IN NUMBER) IS SELECT *
                FROM scott.emp
                WHERE deptno=p_deptno;
 CURSOR cur_dept IS SELECT *
                FROM scott.dept;                               
BEGIN
 FOR dept_satz IN cur_dept LOOP
      dbms_output.put_line('Mitarbeiter in Abteilung:'||dept_satz.dname||' Nr.:'||dept_satz.deptno);
    FOR emp_satz IN cur_emp(dept_satz.deptno) LOOP -- Hier wird die Abt-Nr eingesetzt (10,20,30,40)
        dbms_output.put_line(' Mitarbeiter: '||emp_satz.ename);
    END LOOP;
 END LOOP;
END;
/

Associative Array  Tipp 73

DECLARE
-- TYPE typ_name IS TABLE OF datentyp [NOT NULL]
-- INDEX BY {BINARY_INTEGER| VARCHAR(x)};
TYPE numarray IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
l_data  numarray;
BEGIN
    l_data(1) := 'Marco';
    l_data(2) := 'Hans';
    l_data(3) := 'Andrea';
    --l_data(4) := 'Andrea'; =>ORA-22160: Element bei Index [3] nicht vorhanden
    for i in 1 .. l_data.count LOOP
     dbms_output.put_line(l_data(i));
    END loop;
END;
/


DECLARE
-- TYPE typ_name IS TABLE OF datentyp [NOT NULL]
-- INDEX BY {BINARY_INTEGER| VARCHAR(x)};
TYPE numarray IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
l_data  numarray;
BEGIN
    l_data(1) := 'Marco';
    l_data(2) := 'Hans';
    l_data(3) := 'Andrea';
    --l_data(4) := 'Andrea'; =>ORA-22160: Element bei Index [3] nicht vorhanden
    forall i in 1 .. l_data.count
    insert into t SELECT l_data(i) from dual
    WHERE l_data(i) is NOT null;
END;
/
SELECT * FROM t;

Collection mit ASCII Tabelle  Tipp 74

DECLARE
TYPE numarray IS TABLE OF VARCHAR2(2)
INDEX BY BINARY_INTEGER;
l_data  numarray;
BEGIN
    FOR i IN 1 .. 255 LOOP
       l_data(i) := chr(i);
    END LOOP;
    FOR i in 1 .. l_data.count LOOP
       dbms_output.put_line(rpad(i,3,' ')||' '||l_data(i));
    END LOOP;
END;
/

Rückgabe der Function ist ein Array !  Tipp 75

DECLARE
 TYPE T IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
 saltab T;
 avg10  emp.sal%type;

 FUNCTION avg_sal (maximum NUMBER DEFAULT 5000)  RETURN T
 IS
  avgtab T;
 BEGIN
 avgtab(10):=0; -- Defaultwert falls kein Wert zurückkommt
  FOR rec_avg IN (SELECT deptno,round(avg(sal),0) avgsal from emp
                  WHERE sal >= maximum group by deptno) loop
          avgtab(rec_avg.deptno) := rec_avg.avgsal; -- Werte für Abt. 10, 20 und 30
  END LOOP;
  RETURN avgtab;
 END;

BEGIN
 saltab := avg_sal(1000);
 saltab := avg_sal(6000); -- Kein Defaultwert
 dbms_output.put_line(saltab(10));
 avg10 := avg_sal(1000) (10); -- Average an der Stelle 10
 --avg10 := avg_sal (10);  --> Fehler falscher Typ!
END;
/

Verschlüsselung und Hash Packages in Postgres  Tipp 91

Oracle Hashing:

SELECT sys.dbms_crypto.hash(
utl_raw.cast_to_raw('hundkatzemaus'), 3 
/*sys.dbms_crypto.hash_sh1*/)
from dual;

Oracle Verschlüsselung und Entschlüsselungsfunktion:

CREATE OR REPLACE FUNCTION crypt (
text         IN     VARCHAR2,
cryptmode    IN     VARCHAR2 DEFAULT 'E',
key        IN    VARCHAR2 DEFAULT 'MuniQSoft_Key') 
RETURN VARCHAR2 IS
p_typ PLS_INTEGER:=4360; 
--ENCRYPT_AES256+chain_cbc+ pad_pkcs5;
p_key VARCHAR2(512);
BEGIN
p_key := sys.UTL_I18N.STRING_TO_RAW(lpad(key,32,'-+'));
  IF substr(upper(cryptmode),1,1)='E' THEN -- Verschlüsselung
      RETURN (sys.dbms_crypto.encrypt(
        src => sys.UTL_I18N.STRING_TO_RAW(text,'AL32UTF8'),    typ => p_typ, key => p_key));
  ELSE -- Entschlüsselung
      RETURN sys.UTL_I18N.RAW_TO_CHAR(
        sys.dbms_crypto.decrypt(
        src => text, typ => p_typ, key => p_key));
  END IF;
END;
/

Vergleich der Funktionen  Tipp 112

CREATE OR REPLACE FUNCTION ora_func(p_eins IN integer default 42, p_zwei integer default 4711, p_drei varchar2 default 'test') RETURN VARCHAR2 AS BEGIN RETURN 'p_eins='|| p_eins ||', p_zwei='|| p_zwei ||', p_drei=' || p_drei; END; 

Mögliche Aufrufe:



SELECT ora_func FROM dual; =>p_eins=42, p_zwei=4711, p_drei=test SELECT ora_func(p_eins=>null) FROM dual; p_eins=, p_zwei=4711, p_drei=test 

Hinweise:

Bei Oracle muss in einem SQL Kontext für den Funktionaufruf auch eine Tabelle verwendet werden (meist die DUAL Tabelle), bei Postgres ist das nicht notwendig

Sowohl Oracle als auch Postgres unterstützen auch OUT und INOUT Parameter, nur schreibt Oracle IN OUT und Postgres INOUT.

Procedure unterstützen nur IN und INOUT in Postgres

Event Trigger bei Postgres  Tipp 113

Nicht nur Oracle besitzt DDL Trigger, diese gibt es auch in ähnlicher Form in der Postgres Datenbank

Folgende Events stehen derzeit zur Verfügung:

• ddl_command_start (zündet vor Ausführung von ( CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT oder REVOKE Befehlen)
• ddl_command_end (zündet nach Ausführung von ( CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT oder REVOKE Befehlen)
• sql_drop (zündet bei allen Befehlen, die Objekte aus der Datenbank löschen)
• table_rewrite (zündet vor rewrite Operationen wie ALTER TABLE oder ALTER TYPE)

Beispiel für einen ddl_command_end Trigger:

CREATE FUNCTION notice_event() RETURNS event_trigger AS $$
DECLARE r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE NOTICE 'caught % event on %', r.command_tag, r.object_identity;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE EVENT TRIGGER tr_notice_alter_table
  ON ddl_command_end WHEN TAG IN ('ALTER TABLE')
  EXECUTE PROCEDURE notice_event();

Beispiel für einen sql_drop Trigger in Postgres:

CREATE EVENT TRIGGER tr_notice_drop_table ON SQL_DROP
    WHEN TAG IN ('DROP TABLE')
    EXECUTE PROCEDURE public.notice_drop_event();

CREATE OR REPLACE FUNCTION notice_drop_event() RETURNS event_trigger AS $$
DECLARE r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
        RAISE NOTICE 'Schema:% Objekt:% Typ:% Original:%',  
        r.schema_name,r.object_name,r.object_type,r.original;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

 

create table t (id int);
drop table t;

HINWEIS: Schema:public Objekt:t Typ:table Original:t
HINWEIS: Schema:public Objekt:t Typ:type Original:f
HINWEIS: Schema:public Objekt:_t Typ:type Original:f
DROP TABLE

Mögliche Return-Spalten der Funktion pg_event_trigger_dropped_objects():

• classid (oid)
• objid (oid)
• objsubid (oid)
• original (boolean) War es das Originalziel des Drop
• normal (boolean)
• is_temporary (boolean) War es ein temporäres Objekt
• object_type (text) Objekttyp wie z.B. Tabelle, Index, Schema
• schema_name (text) Schemaname
• object_name (text) Objektname
• object_identity (text)
• address_names (text[])
• address_args (text[])

Event Matrix für Postgres 12
Weitere spannende Postgres Beispiele erfahren Sie in unseren PG/SQL Kurs.

TRIGGER  Tipp 80

DROP TABLE scott.emp_trig;
CREATE TABLE scott.emp_trig AS SELECT * FROM scott.emp;
col cmode format A6
col user_name format a10
col sal format 9999

DROP TABLE scott.emp_audit;
CREATE TABLE scott.emp_audit AS SELECT * FROM scott.emp WHERE 1=2;

ALTER TABLE scott.emp_audit ADD
    ( old_sal         NUMBER(7,2),
      new_sal         NUMBER(7,2),
      user_name     VARCHAR2(30),
      change_date     DATE,
      event            CHAR(1));

CREATE OR REPLACE TRIGGER scott.emp_trig_idu
BEFORE INSERT OR UPDATE OR DELETE ON scott.emp_trig
FOR EACH ROW
DECLARE
v_event CHAR(1):='X';
BEGIN
  IF INSERTING THEN
      v_event:='I';
  ELSIF UPDATING THEN
      v_event:='U';
  ELSE --DELETING
      v_event:='D';
  END IF;
 
  INSERT INTO scott.emp_audit
  (empno,old_sal,new_sal,user_name,change_date,event)
  VALUES
  (:OLD.empno,:OLD.sal,:NEW.sal,user,sysdate,v_event);
END;
/

Funktionen mit Rekusion  Tipp 58

CREATE OR REPLACE FUNCTION fac (n integer)
RETURN NUMBER
IS
BEGIN
     IF n=1 THEN
        RETURN  1;
     ELSE
        RETURN n*fac(n-1);
     END IF;
END;
/

 

Funktionen die Fehlertext anhand Fehlernummer zurückgeben  Tipp 59

CREATE OR REPLACE FUNCTION system.get_error
(error_code in number)
RETURN varchar2
IS
BEGIN
RETURN sqlerrm(error_code);
END;
/
show errors

SQL> SELECT system.get_error(-1017) FROM dual;

SYSTEM.GET_ERROR(-1017)
----------------------------------------------------------------
ORA-01017: Benutzername/Kennwort ungültig; Anmeldung abgewiesen.

Funktionen mit Commit  Tipp 60

CREATE OR REPLACE FUNCTION f
RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO scott.emp (empno,ename)
    VALUES (8000,'MARCO');
    COMMIT;
    RETURN 0;
END;
/
show errors

SELECT f,e.* FROM scott.emp e
WHERE rownum=1;
REM Zeile wird eingefügt !!!!

Associative Array mit VARCHAR2 als Referenz  Tipp 61

SET SERVEROUTPUT ON
DECLARE
  TYPE country_tab_type IS TABLE OF VARCHAR2(50)
  INDEX BY VARCHAR2(2);
  t_country country_tab_type;
BEGIN
  --Array füllen, Index ist hier eindeutig durch Länder ISO
  t_country('AT') := 'Austria';
  t_country('FR') := 'France';
  t_country('DE') := 'Germany';
 
  -- Welches Land verbirgt sich hinter dem ISO code "DE"
  DBMS_OUTPUT.PUT_LINE('ISO code "DE" = ' || t_country('DE'));

END;
/

Bulk Cursor MIT SAVE EXCEPTIONS  Tipp 62

DROP TABLE scott.objects;
DROP TABLE scott.tables;
CREATE TABLE scott.objects AS
SELECT object_name,owner FROM all_objects
WHERE owner='SCOTT' and object_type in ('TABLE','INDEX');
CREATE TABLE scott.tables AS
SELECT table_name,owner FROM all_tables WHERE owner='SCOTT';
ALTER TABLE scott.objects ADD PRIMARY KEY (owner,object_name);


SET SERVEROUTPUT ON
DECLARE
TYPE object_tab_type IS TABLE OF scott.objects.object_name%TYPE;
TYPE table_tab_type IS TABLE OF scott.tables.table_name%TYPE;
myobjects_tab     object_tab_type;
mytables_tab    table_tab_type;

error_count    NUMBER;
BEGIN
SELECT object_name BULK COLLECT INTO myobjects_tab
FROM scott.objects;

SELECT table_name BULK COLLECT INTO mytables_tab
FROM scott.tables;

FORALL i IN mytables_tab.first .. mytables_tab.last SAVE EXCEPTIONS
    INSERT INTO scott.objects (object_name,owner)
    VALUES (mytables_tab(i),'SCOTT');

EXCEPTION
when others then
    error_count := SQL%BULK_EXCEPTIONS.COUNT;
    for i in 1.. error_count loop
        dbms_output.put_line('** Oracle Fehler:' ||
        sqlerrm(-SQL%BULK_EXCEPTIONS(I).ERROR_CODE)||':'||mytables_tab(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)||
    '  #  Position:'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);  
    END loop;
    COMMIT;
END;
/

Prozedur mit zwei Parametern  Tipp 63

CREATE OR REPLACE PROCEDURE ptest (
name     VARCHAR2,
beruf     VARCHAR2)
IS
BEGIN
dbms_output.put_line('Hr. '||name||' ist von Beruf '||beruf);
END;
/

Prozedur eingebettet im Deklarationsbereich  Tipp 64

CREATE OR REPLACE PROCEDURE ptest (
name     VARCHAR2,
beruf    VARCHAR2)
IS
 PROCEDURE p (out_text) IS
 BEGIN
    dbms_output.put_line(out_text);
 END;
BEGIN
p('Hr. '||name||' ist von Beruf '||beruf);
END;
/

IN, OUT und IN OUT Parameter in Proceduren  Tipp 65

CREATE OR REPLACE PROCEDURE in_out (
p_in         IN NUMBER, -- Parameter geht in die Proc rein
p_out        OUT NUMBER, -- Paramter wird zurückgegeben
p_in_out     IN OUT NUMBER)  -- Parameter geht rein und wieder raus
IS
var1 number :=10;
BEGIN
 --p_in :=20; -- geht nicht
 var1 :=p_in;

 p_out :=20;
 var1 := p_out;
 p_out := p_out +1;
 
 p_in_out := 20;
 var1 := p_in_out;
 p_in_out := p_in_out +1;
EXCEPTION
 WHEN OTHERS THEN
  dbms_output.put_line(sqlerrm);
END;
/

REM Aufruf
DECLARE
v_out NUMBER;
v_in_out NUMBER:=2;
BEGIN
in_out(1,v_out,v_in_out);
END;
/

ROWCOUNT  Tipp 93

CREATE OR REPLACE PROCEDURE oracle_test
as
BEGIN
DELETE FROM t WHERE id = 1;
dbms_output.put_line('DELETES:'||SQL%ROWCOUNT);

INSERT INTO t SELECT * FROM tt;
dbms_output.put_line('INSERTS:'||SQL%ROWCOUNT);

UPDATE t SET c=1;
dbms_output.put_line('UPDATES:'||SQL%ROWCOUNT);

END;
/

Cursor auf Tabelle  Tipp 94

BEGIN
FOR emp_tab IN 
(select * from scott.emp) LOOP
 dbms_output.put_line(
  emp_tab.ename||' '||
  emp_tab.job||' '||
  emp_tab.sal||' '||
  emp_tab.deptno);
END LOOP;
END;

Datentypen in PG/SQL und PL/SQL im Deklarationsteil  Tipp 95

DECLARE
var_a NUMBER:=1;
var_b VARCHAR2(2001):='ABCDEF';
var_c CHAR(100):='ABCDEF';
var_d DATE:=sysdate;
var_e CLOB;
var_f BLOB;
var_g BOOLEAN:=TRUE;
var_h BINARY_FLOAT;
var_i BINARY_DOUBLE;
var_j ROWID;
BEGIN
NULL;
END;

Schleifen mit LOOP...END LOOP  Tipp 97

DECLARE
i NUMBER:=0;
BEGIN
LOOP
    i:=i+1;
    EXIT WHEN i>10; -- Schleife bei i=10 verlassen
END LOOP;
END;
/

Basis ist eine Tabelle  Tipp 98

DECLARE
TYPE emp_type IS TABLE OF scott.emp%ROWTYPE
INDEX BY BINARY_INTEGER;
l_data  emp_type;
BEGIN
    l_data(1).empno:=8000;
    l_data(1).ename:='Patzwahl';
    l_data(5).empno:=8001;
    l_data(5).ename:='Huberl';
END;

BULK - einlesen IN Record - Array einer PL/SQL Tabelle  Tipp 99

DECLARE
    CURSOR emp_cur IS
        SELECT * FROM scott.emp;
    TYPE emp_tab_type IS TABLE OF emp_cur%ROWTYPE;
    emp_tab emp_tab_type;
BEGIN
    OPEN emp_cur;
    FETCH emp_cur BULK COLLECT
    INTO emp_tab;    
       CLOSE emp_cur;
END;
/

 

Return Datentyp NUMBER  Tipp 101

CREATE OR REPLACE FUNCTION f
  ( v IN NUMBER) RETURN NUMBER IS
BEGIN
    RETURN mod(var,2) ;
END;
/

Return Datentyp DATE  Tipp 102

CREATE OR REPLACE FUNCTION f
  ( v IN DATE) RETURN DATE IS
BEGIN
    RETURN (v+1);
END;
/

SELECT f(sysdate) FROM dual;

Funktionen mit Rekursion  Tipp 103

CREATE OR REPLACE FUNCTION fac (n integer)

RETURN NUMBER IS BEGIN IF n=1 THEN RETURN 1; ELSE RETURN n*fac(n-1); END IF; END; /

Dual Tabelle  Tipp 104

SELECT * FROM dual;

NVL Funktionen  Tipp 105

SELECT nvl(null,'X') FROM dual;

Pipelined Table Function  Tipp 110

CREATE OR REPLACE TYPE emp_type
AS OBJECT (EMPNO NUMBER(4,0),
    ENAME       VARCHAR2(10 BYTE),
    JOB         VARCHAR2(9 BYTE),
    HIREDATE    DATE,
    SAL         NUMBER(7,2),
    DEPTNO      NUMBER(2,0));
/

CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type;

CREATE OR REPLACE FUNCTION  get_emp_tab
RETURN  emp_tab_type PIPELINED IS
BEGIN
    FOR rec IN (SELECT * FROM emp) LOOP
        PIPE ROW (emp_type(
      rec.empno,
      rec.ename,
      rec.job,
      rec.hiredate,
      rec.sal,
      rec.deptno)) ;
    END LOOP;   
END;
/

SELECT * FROM table(get_emp_tab);

Trigger  Tipp 107

CREATE OR REPLACE TRIGGER trg_ins
BEFORE INSERT
   ON t
   FOR EACH ROW
BEGIN

IF :NEW.ID IS NULL THEN   
  :NEW.ID:= ADRESS_SEQ.NEXTVAL;
END IF;
IF :NEW.c_time IS NULL THEN
  :NEW.c_time := SYSDATE;
  :NEW.l_update := SYSDATE;
END IF;
END;

Datentypen CAST  Tipp 111

Oracle hat leider (noch ) keinen Datentyp Boolean.
SELECT CAST('123,45' AS BINARY_DOUBLE) FROM dual;
SELECT CAST('123,45' AS BINARY_FLOAT) FROM dual;
SELECT CAST('123,45' AS NUMBER ) FROM dual;
SELECT CAST('123' AS INTEGER) FROM dual;
SELECT CAST ('31.12.2019' as DATE) FROM dual;
SELECT CAST ('31.12.2019' AS TIMESTAMP) FROM dual;
SELECT CAST(123.45 AS VARCHAR(8)) FROM dual;
SELECT CAST(123.45 AS VARCHAR2(8)) FROM dual;
SELECT CAST(123.45 AS CHAR(7)) FROM dual;
SELECT CAST('ABC' AS raw(8)) from dual;

Exceptionhandling  Tipp 108

BEGIN
    EXECUTE IMMEDIATE(
    'CREATE TABLE yyy (a INT)');
    EXECUTE IMMEDIATE(
   'CREATE TABLE yyy (a INT)');
EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line(
   'SQLCode: '||
    SQLCODE|| ' // SQLErrm: ' ||
    SQLERRM);
END;

DBMS_STATS  Tipp 92

Wer bei Oracle schon mal das Package dbms_stats verwendet hat, weiß um dessen Mächtigkeit.
Leider hat Postgres nur einen Bruchteil der Möglichkeiten bei der Statistikerfassung, aber wir versuchen uns mal eine eigene Procedure zu bauen.

Oracle Package dbms_stats.gather_table_stats:

BEGIN dbms_stats.gather_table_stats(
'SCOTT','EMP');
END;

und Oracle Package dbms_stats.gather_schema_stats:

BEGIN 
dbms_stats.gather_schema_stats(
'SCOTT');
END;