Oracle PL/SQL

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;