Skip to Main Content

Search

Chiemsee

Chiemsee Bild

Unsere komplette Oracle PL/SQL Tipp Sammlung ...

Search Results

4207.10.201909.08.2024BULK-einlesen IN Record-Array mit LIMIT

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;
/
3107.10.201909.08.2024Bulk Cursor mit LIMIT Klausel
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;
/
101Returns, Numeric19.11.201906.07.2023Return Datentyp NUMBER
CREATE OR REPLACE FUNCTION f (
   v IN NUMBER
) RETURN NUMBER IS
BEGIN
   RETURN MOD(var, 2);
END;
/
93SQL%Rowcount18.11.201906.07.2023ROWCOUNT
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; 
/
90dbms_output.put_line08.11.201906.07.2023dbms_output
EXEC dbms_output.put_line('Hello');
86dual, dummy char(1)07.11.201906.07.2023Dual Tabelle
SELECT * FROM dual;
104dummy char(1)19.11.201907.07.2023Dual Tabelle
SELECT * FROM dual;
85loop07.11.201906.07.2023Schleifen (Loop)
LOOP 
<hier können Ihre Statements stehen>; 
END LOOP;
87my_block07.11.201906.07.2023Anonymer Block
DECLARE
   zahl INTEGER := 0;
BEGIN
   zahl := zahl + 1;
   dbms_output.put_line('Zahl steht auf ' || zahl);
END;
89nvl, coalesce08.11.201906.07.2023NVL Funktion
SELECT nvl(null,'X') FROM dual;
105nvl, coalesce19.11.201907.07.2023NVL Funktionen
SELECT nvl(null,'X') FROM dual;
83scott.emp_trig07.10.201909.08.2024Trigger stürzt ab, wenn Gehalt zu hoch
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;
102sysdate19.11.201906.07.2023Return Datentyp DATE
CREATE OR REPLACE FUNCTION f (
   v IN DATE
) RETURN DATE IS
BEGIN
   RETURN ( v + 1 );
END;
/
SELECT
   f(sysdate)
FROM
   dual;
9919.11.201906.07.2023BULK - einlesen IN Record - Array einer PL/SQL Tabelle
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;
/
 
7307.10.201909.08.2024Associative Array
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;
6207.10.201909.08.2024Bulk Cursor MIT SAVE EXCEPTIONS
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;
/
1207.10.201909.08.2024Bulk Cursor mit DELETE
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;
/
507.10.201909.08.2024Bulk Cursor mit Update
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;
/
4007.10.201909.08.2024CASE
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;
/
7407.10.201909.08.2024Collection mit ASCII Tabelle
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;
/
326.09.201909.08.2024Formatierte Ausgabe in PL/SQL
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;
/

Ergebnis:
Marco    #
Uli      #
     1,234
  1000,567

 
1007.10.201909.08.2024Oracle´s vordefinierte Exception
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;
/
3407.10.201909.08.2024PL/SQL Beispiele
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;
/
3507.10.201909.08.2024PL/SQL Beispiele, Returning Klausel

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

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

 

SELECT
   f(sysdate)
FROM
   dual;
5407.10.201909.08.2024Return Datentyp DATE
CREATE OR REPLACE FUNCTION f
  ( v IN DATE) RETURN DATE IS
BEGIN
    RETURN (v+1);
END;
/

SELECT f(sysdate) FROM dual;
5307.10.201909.08.2024Return Datentyp NUMBER
CREATE OR REPLACE FUNCTION f (
   v IN NUMBER
) RETURN NUMBER IS
BEGIN
   RETURN MOD(var, 2);
END;
/
5607.10.201909.08.2024Return Datentyp RECORD
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
426.09.201909.08.2024Returning Klausel
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

7507.10.201909.08.2024Rückgabe der Function ist ein Array !
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;
/
1107.10.201909.08.2024Sonstige Oracle Exceptions
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;
/
227.09.201909.08.2024PL/SQL Konvertierungen

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;
17:3
10928.11.201907.07.2023Collections bzw. Arrays
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;
/
8407.10.201909.08.2024Collection mit Basis ist ein RECORD
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; 
/
94Loop, Cursor for Loop18.11.201906.07.2023Cursor auf Tabelle
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;
71cursor, cur_emp07.10.201909.08.2024geschachtelte Cursor (Kurze Version)
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;
/
72cursor, cur_emp07.10.201909.08.2024geschachtelte Cursor (Lange Version)
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;
/
2407.10.201909.08.2024CURSOR ohne Deklartionsteil
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
1607.10.201909.08.2024Cursor
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;
/
2607.10.201909.08.2024Cursor auf Tabellen-Join
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 
 

1807.10.201909.08.2024Cursor im Deklarationsteil + FOR LOOP + SELECT mit Funktion
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
1907.10.201909.08.2024Cursor im Deklarationsteil + OPEN, FETCH, CLOSE
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;
/
2907.10.201909.08.2024Cursor im Package wird über die Procedure offen gelassen
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;
1707.10.201909.08.2024Cursor ohne Deklarationsteil direkt in FOR LOOP Schleife
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;
/
2807.10.201909.08.2024DELETE auf Cursor mit FOR UPDATE
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;
/
Ausgabe: Ein Stelle als Chef ist frei, denn der ist weg: KING
2207.10.201909.08.2024Einfacher CURSOR mit Parametern und NULL Werten
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

2307.10.201909.08.2024Einfacher CURSOR mit Parametern und einem Trick

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

2007.10.201909.08.2024OPEN FETCH CLOSE CURSOR mit Parametern

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

2107.10.201909.08.2024OPEN FETCH CLOSE CURSOR mit Parametern und Formatierung
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;
/
2707.10.201909.08.2024Update auf Cursor mit FOR UPDATE

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