Oracle PL/SQL: Difference between revisions
(No difference)
|
Latest revision as of 11:44, 13 November 2018
Minimalbeispiel
BEGIN FOR rec IN (SELECT ghi.id FROM geraetehistorien ghi WHERE ghi.id < 500) LOOP dbms_output.put_line(rec.id); pkg_ghi.prc_del_ghi_eintrag(p_ghi_id => rec.id); END LOOP; END; /
Sauberes Arbeiten mit Testmodus und Protokollierung
-- 2. Sauber arbeiten mit Testmodus und Protokollierung DECLARE l_test CONSTANT BOOLEAN := TRUE; -- TODO: Testmodus l_err VARCHAR2(1000) := NULL; l_commit BOOLEAN := TRUE; -- wird bei Problemen mit false überschrieben, nie mit true befüllt l_output VARCHAR2(1000); l_count NUMBER := 0; l_count_good NUMBER := 0; l_count_error NUMBER := 0; BEGIN -- dbms_output.enable; -- verhindert Pufferüberlauf wenn als Procedure gestartet. -- -- Eine Beispielschleife -- l_output := 'Nr' || ' ; ' || 'ID'; dbms_output.put_line(l_output); FOR rec IN (SELECT ghi.id FROM geraetehistorien ghi WHERE ghi.id < 500) LOOP BEGIN -- neuer BEGIN-Block um hier gesondert Exceptions zu fangen l_err := NULL; -- Viele Prozeduren geben Fehlermeldungen zurück, mit diesen ist dann l_err zu füllen l_count := l_count + 1; -- -- Hauptteil z.B.: erst Ausgabe, dann GHI Löschen -- l_output := l_count || ' ; ' || rec.id; -- hier eine Ausgabevariable für DBMS Put Line und Protokoll setzen dbms_output.put_line(l_output); -- -- Ende Hauptteil -- -- -- Fehlerbehandlung etc -- IF l_err IS NOT NULL THEN l_commit := FALSE; l_count_error := l_count_error + 1; ELSE l_count_good := l_count_good + 1; END IF; EXCEPTION WHEN OTHERS THEN l_commit := FALSE; l_count_error := l_count_error + 1; l_output := 'ERROR: ' || SQLERRM; dbms_output.put_line(l_output); END; -- block innerhalb for schleife END LOOP; -- commit erst am Ende, und nur wenn keine Fehler und kein Testmodus IF l_commit = TRUE THEN l_output := 'No Errors'; dbms_output.put_line(l_output); IF l_test THEN l_output := 'TEST -> ROLLBACK of all statements'; dbms_output.put_line(l_output); ROLLBACK; ELSE l_output := 'COMMIT'; dbms_output.put_line(l_output); COMMIT; END IF; ELSE -- l_commit ist nicht gesetzt, weil Fehler l_output := 'ERRORS -> ROLLBACK'; dbms_output.put_line(l_output); ROLLBACK; END IF; -- -- Zusammenfassung -- dbms_output.put_line(l_lpl_id, '-- '); dbms_output.put_line(l_lpl_id, '-- Zusammenfassung'); dbms_output.put_line(l_lpl_id, '-- bearbeitet: ' || l_count); dbms_output.put_line(l_lpl_id, '-- gut : ' || l_count_good); dbms_output.put_line(l_lpl_id, '-- fehlerhaft: ' || l_count_error); dbms_output.put_line(l_lpl_id, '-- '); -- -- Exeptions -- EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLCODE || ' - ' || SQLERRM); ROLLBACK; END; /
Ausgabe
-- SET SERVEROUTPUT ON -- dbms output aktivieren im SQL-Developer (PL/SQL-Dev macht das automatisch) DECLARE ln_num NUMBER; lv_text VARCHAR2(32); BEGIN ln_num := 12345; lv_text := 'qwertz'; dbms_output.put_line('ln_num = ' || ln_num || ', lv_text = ' || lv_text); END; /
Variablen
/* Variablennamenskonvention 1. Stelle: Lokale vs. Übergabevariable - Lokale (deklarierte) Variablen mit ‚l‘ beginnend - Prozedur-Übergabe Variablen mit ‚p‘ beginnend 2. Stelle: Typ - n für Number - V für Varchar2 (String/Zeichenkette) - B für Boolean (true/false) 3… Stelle: ‚_Bezeichnung‘ */ -- Minimalbeispiel DECLARE -- kann weggelassen werden wenn nichts zu deklarieren ln_num NUMBER := 0; ln_num2 CONSTANT NUMBER := 123; BEGIN ln_num := 12345; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLCODE || ' - ' || SQLERRM); END; / /* Zwischen BEGIN und END können weitere gekapselte DECLARE BEGIN EXCEPTION END -Blöcke eingefügt werden, Beispiel siehe Schleife mit Exception-Behandlung */ -- Variablen 1 DECLARE ln_num number; lv_text VARCHAR2(32); BEGIN ln_num := 12345; dbms_output.put_line('num = ' || ln_num); lv_text := 'qwertz'; dbms_output.put_line('text = ' || lv_text); END; / -- Variablen 2 - Via SQL befüllen DECLARE ln_lpl_id lineare_protokolle.id%type; -- %TYPE liest den Variablentyp aus der Tabellenspalte aus -> SEHR praktisch! lv_lpl_titel lineare_protokolle.beschreibung%TYPE; BEGIN SELECT t.id, t.beschreibung INTO ln_lpl_id, lv_lpl_titel FROM lineare_protokolle t WHERE t.id = (SELECT MAX(id) FROM lineare_protokolle); dbms_output.put_line('ID = ' || ln_lpl_id || ', Titel = ' || lv_lpl_titel); END; / -- Variablen 3 - Insert in DB DECLARE ln_num lineare_protokolle.id%TYPE; lv_lpl_titel lineare_protokolle.beschreibung%TYPE; BEGIN ln_num := 12345; lv_lpl_titel := 'asdfg'; insert into lineare_protokolle(id,beschreibung) values (ln_num,lv_lpl_titel); END; / -- Variablen 4 - Update in DB DECLARE ln_num tabelle.spalte1%TYPE := 0; BEGIN UPDATE tabelle SET (spalte1) = (ln_num) WHERE spalte2 = 123; --filter END; / -- Löschen mit RowCount declare ln_num tab.spalte1%TYPE := 0; ln_num_del number; BEGIN DELETE FROM employees WHERE employee_id = ln_num; ln_num_del := SQL%ROWCOUNT; DBMS_OUTPUT.PUT_LINE(ln_num_del || ' rows deleted.'); DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows deleted.'); END;
Schleifen
-- Prozedur ausführen BEGIN pkg_ghi.prc_del_ghi_eintrag(p_ghi_id => 123123123); END; / -- Besser Exceptions fangen und ausgeben BEGIN pkg_ghi.prc_del_ghi_eintrag(p_ghi_id => 123123123); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLCODE || ' - ' || SQLERRM); END; / -- For-Schleife - Minimalbeispiel BEGIN FOR rec IN (SELECT * FROM geraetehistorien ghi WHERE ghi.id < 500) LOOP dbms_output.put_line(rec.id); -- pkg_ghi.prc_del_ghi_eintrag(p_ghi_id => rec.id); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLCODE || ' - ' || SQLERRM); END; / -- For-Schleife mit If-Else BEGIN FOR rec IN (SELECT ghi.id FROM geraetehistorien ghi WHERE ghi.id < 500) LOOP IF (rec.id > 500) THEN dbms_output.put_line('lösche GHI-ID ' || rec.id); -- pkg_ghi.prc_del_ghi_eintrag(p_ghi_id => rec.id); ELSE dbms_output.put_line('lösche GHI-ID ' || rec.id || ' nicht'); END IF; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLCODE || ' - ' || SQLERRM); END; / -- For-Schleife mit EXCEPTION-Behandlung im Loop (Macht nach Fehler weiter) BEGIN FOR rec IN (SELECT ghi.id FROM geraetehistorien ghi WHERE ghi.id < 500) LOOP BEGIN -- separater Beginn Block, für Exceptions -- pkg_ghi.prc_del_ghi_eintrag(p_ghi_id => rec.id); dbms_output.put_line('lösche GHI-ID ' || rec.id); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLCODE || ' - ' || SQLERRM); END; -- innerer Begin Block END LOOP; END; / -- Schleife über Liste von Werten -- http://www.knaupes.net/oracle-plsql-for-schleife/ DECLARE TYPE person IS RECORD( LAST_NAME VARCHAR2(16), AGE NUMBER); TYPE personTab IS TABLE OF person; vPersons personTab := personTab(); BEGIN -- befüllen der Tabelle vPersons.extend; vPersons(1).LAST_NAME := 'KING'; vPersons(1).AGE := '32'; vPersons.extend; vPersons(2).LAST_NAME := 'QUEEN'; vPersons(2).AGE := '31'; FOR i IN vPersons.first .. vPersons.last LOOP dbms_output.put_line(vPersons(i).LAST_NAME || ' - ' || vPersons(i).AGE); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLCODE || ' - ' || SQLERRM); END;
Prozeduren
--erstellen und in DB speichern CREATE OR REPLACE PROCEDURE myproc(pn_card_id NUMBER, pv_loc varchar2) is ln_counter number; BEGIN ln_counter := ln_counter + 1; INSERT INTO tab1 VALUES (pn_card_id, pv_loc); end myproc; / --ausführen begin myproc(pn_card_id => 123, pv_loc => 'test'); end; / --löschen drop PROCEDURE myproc; -- Procedure in Anonymen Block verwenden DECLARE lv_text VARCHAR2(16) := 'asdfg'; PROCEDURE myproc(pn_card_id NUMBER, pv_loc NUMBER) IS ln_counter NUMBER := 0; BEGIN ln_counter := ln_counter + 1; INSERT INTO tab1 VALUES (pn_card_id, pv_loc); END myproc; BEGIN myproc(123, lv_text); END; / -- Exception Rückgabe aus Prozedur raus CREATE OR REPLACE PROCEDURE myproc (pn_card_id IN NUMBER, pv_loc IN NUMBER, pv_error OUT varchar2) is ln_counter number := 0; BEGIN pv_error := null; ln_counter := ln_counter + 1; INSERT INTO tab1 VALUES (pn_card_id, pv_loc); EXCEPTION WHEN OTHERS THEN pv_error := 'ERROR: ' || SQLCODE || ' - ' || SQLERRM; end myproc; /
Performance Tipps
-- fastest: plain SQL INSERT all INTO tab1 (owner, table_name) VALUES ('EC_ANF', 'ANFRAGEN') INTO tab1 (owner, table_name) VALUES ('EC_ANF', 'ANFRAGE_PARAMETER') INTO tab1 (owner, table_name) VALUES ('EC_ANF', 'ANFRAGEVERMERKE') INTO tab1 (owner, table_name) VALUES ('EC_ANF', 'KUNDEN') SELECT 1 FROM DUAL; -- select muss hier sein -- slow: Context switches SQL to PL/SQL FOR I IN 1..5600 LOOP SELECT product_id, warehouse_id INTO vn_p_id, vn_wh_id FROM big_inventories WHERE v_p_id = I; INSERT INTO inventories2 VALUES(vn_p_id, vn_wh_id); END LOOP -- better: via Cursor DECLARE CURSOR cv_raise IS SELECT deptno, increase FROM emp_raise; BEGIN FOR dept IN cv_raise LOOP UPDATE big_emp SET sal = sal * dept.increase WHERE deptno = dept.deptno; END LOOP; -- Bulk Collect CREATE PROCEDURE get_departments(p_loc NUMBER) IS TYPE dept_tab_type IS TABLE OF departments%ROWTYPE; v_depts dept_tab_type; BEGIN SELECT * BULK COLLECT INTO v_depts FROM departments WHERE location_id = p_loc; FOR i IN 1 .. v_depts.COUNT LOOP DBMS_OUTPUT.PUT_LINE(v_depts(i).department_id || ' ' || v_depts(i).department_name); END LOOP; END; -- Bulk Binding FORALL i IN v_id.FIRST .. v_id.LAST UPDATE employees SET salary = (1 + p_percent / 100) * salary WHERE employee_id = v_id(i); END;