PL/SQL-Tipps

EXEC dbms_output.put_line('Hello');

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; 
/

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; 
/

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; 
/

Ergebnis:
Zu viele Verkäufer!

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;
/

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;
/

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;
/

DECLARE
dept_rec dept%ROWTYPE; -- alle Datentypen der Tabelle dept übernehmen
BEGIN
dept_rec.deptno:=10;
dept_rec.dname:='MUNICH';
--dbms_output.put_line(dept_rec); geht nicht
dbms_output.put_line(dept_rec.deptno||' '||dept_rec.dname);
END;
/

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; 
/

In der Array Verarbeitung stehen beim zurückschreiben aus einem Array in eine Tabelle folgende Funktionen zur Verfügung:

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

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

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;
/

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; 
/

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;
/

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;
/

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 getptab; 
END plsql_tab; 
/ 

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; 
/

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')
*/

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;
/

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 geheim IS 
   BEGIN 
   NULL; 
   END berechnen; 
   END pack_test;
   /

CREATE OR REPLACE PACKAGE BODY p_body IS ... BEGIN
SELECT
   COUNT(*)
INTO g_var
FROM
   emp;
end;
/

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;

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

SELECT f(sysdate) FROM dual;

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

SELECT
   f(sysdate)
FROM
   dual;

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

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;
/

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;
/

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;
/

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

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;
/

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;
/

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

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

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

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;

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;
/

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;
/

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;
/

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;
/

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;
/

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;
/

Ergebnis:

7839
8000
8000

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;
/

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;

Erklärung: Man kann sich eine eigene Fehlerbehandlungsroutine schreiben, die als Autonome Transaktion läuft. Damit beeinflusst sie nicht die aufrufende Procedure/Function.

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.

SELECT * FROM dual;

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

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

LOOP 
<hier können Ihre Statements stehen>; 
END LOOP;

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;

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 
 

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;
/

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;

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;
/

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;
/

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

BEGIN
FOR i IN 1.. 10 LOOP -- Schleife von 1 bis 10 durchlaufen
  dbms_output.put_line('Schleife Nr. '||i);
END LOOP;
END;
/ 
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;
/

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;
/

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; 
/

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;
/

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;
/

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;

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;

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;
/

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;
/

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;

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;

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; 
/

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; 

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;
/

CREATE OR REPLACE FUNCTION f (
   v IN NUMBER
) RETURN NUMBER IS
BEGIN
   RETURN MOD(var, 2);
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 
   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;

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;
/

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;
/

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; 
/

CREATE OR REPLACE FUNCTION ora_func (
   p_eins IN INTEGER DEFAULT 42,
   p_zwei IN INTEGER DEFAULT 4711,
   p_drei IN VARCHAR2 DEFAULT 'test'
) RETURN VARCHAR2 AS
BEGIN
   RETURN 'p_eins='
          || p_eins
          || ', p_zwei='
          || p_zwei
          || ', p_drei='
          || p_drei;
END; 
/SELECT ora_func(p_eins=>42, p_zwei=>4711, p_drei=>test ) from dual;
SELECT ora_func(p_eins=>null) FROM dual; 

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;
/

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;
/

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.

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 ! ! ! !

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;
/

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;
/

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

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; 
/

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;
/

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; 
/

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;

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;

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

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;

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;
/
 

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

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

SELECT
   f(sysdate)
FROM
   dual;

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;
/

SELECT * FROM dual;

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

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);