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; /
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; /
CREATE OR REPLACE FUNCTION f ( v IN NUMBER ) RETURN NUMBER IS BEGIN RETURN MOD(var, 2); 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; /
EXEC dbms_output.put_line('Hello');
SELECT * FROM dual;
LOOP <hier können Ihre Statements stehen>; END LOOP;
DECLARE zahl INTEGER := 0; BEGIN zahl := zahl + 1; dbms_output.put_line('Zahl steht auf ' || zahl); END;
SELECT nvl(null,'X') FROM dual;
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;
CREATE OR REPLACE FUNCTION f ( v IN DATE ) RETURN DATE IS BEGIN RETURN ( v + 1 ); END; /
SELECT f(sysdate) FROM dual;
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; /
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;
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; /
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 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; /
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 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; /
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
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; /
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;/
CREATE OR REPLACE FUNCTION f ( v IN CLOB ) RETURN CLOB IS BEGIN RETURN ( v ); 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 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 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:
783980008000
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; /
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; /
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 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; /
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; /
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 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; /
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
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; /
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 :accountingort :new york
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; /
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;
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 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; /
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
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;/
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;/
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; /
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; /