View source for Oracle PLSQL

[[Category:Coding]]
====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;