Torben at 20:43, 30 October 2024

2024-10-30T20:43:44Z

New page

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

Oracle PL/SQL - Revision history

Torben at 20:43, 30 October 2024