Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:plsql_code_pices

PL/SQL - Code Beispiele

PL/SQL Code Beispiele - Was immer wieder täglich gebraucht wird.

Mit langen Strings arbeiten

Mit dem q'[ ]' lassen sich lange Strings angeben, die dann auch wiederum einzelne „'“ enthalten können.

v_my_very_long_string := q'[ 
 
select ****  from xxxx where yyy in ('1','2','3')
]';

Collections

PL/SQL Table definieren

Associative Arrays (vor Oracle 10g "index-by tables")

Typ definieren:

TYPE myPlsqltab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

Variable deklarieren:

v_texte myPlsqltab;

Werte zuweisen:

v_texte(3):='Dies ist die erste Ziele';
v_texte(4):='Dies ist die zweite Ziele';
v_texte(8):='Dies ist die dritte Ziele';

Anzahl der Elemente mit count

dbms_output.put_line('-- Info :: Anzahl der Elemente :: '||v_texte.count);

Wert in einer Schleife auslesen:

-- loop over the table
FOR i IN v_texte.FIRST .. v_texte.LAST
LOOP
  -- ausgeben
  IF v_texte.EXISTS(i) 
   THEN
	DBMS_OUTPUT.put_line('element #' || i || ' = ' || v_texte(i));
  END IF; 
END LOOP;
 
-- loop over gaps	
v_count := v_texte.FIRST;
WHILE v_count IS NOT NULL
LOOP
   DBMS_OUTPUT.put_line('element #' || v_count || ' = ' || v_texte(v_count));
   v_count := v_texte.next(v_count);
END LOOP;
 

Komplettes Beispiel: PL/SQL Code Beispiel pür pl/sql Table

Ein „ORA-01426: numeric overflow“ zeigt uns dann an das der INDEX BY Type (pls_integer, binary_integer) auch mal zu klein sein kann, alternativ dann auf varchar2 setzen:

Beispiel um einen Lookup bei einem ETL Job für jeden Datensatz die Abfrage zu vermeiden, die ID's können 16 stellen enthalten:

DECLARE
  TYPE t_emp_lookup_tab IS TABLE OF emp_lookup.id%TYPE INDEX BY varchar2(16);
 
  v_emp_lookuptab   t_emp_lookup_tab;
 
  cursor c_emp_lookup
  IS
    SELECT id
        , MAX (deptno) AS deptno
      FROM emp_deptno_lookup
     GROUP BY id
     ORDER BY id;
BEGIN
  FOR rec IN c_emp_lookup
  loop
    v_emp_lookuptab (rec.id) := rec.deptno;
  END loop;
  dbms_output.put_line ('Fond for Smith wie the pers id 14857470850200 = ::' || v_emp_lookuptab (14857470850200));
END;
/

siehe auch http://www.williamrobertson.net/documents/collection-types.html

Nested Tables

Unterschied ⇒ auf „index by“ wird verzichtet!

Typ definieren:

TYPE varchar_t IS TABLE OF VARCHAR2(2000);

Vergleich Nested Tables Collection in PL/SQL mit dem von dem MULTISET UNION|INTERSECT|EXCEPT Operatoren

DECLARE
 
TYPE varchar2_t IS TABLE OF VARCHAR2(2000);
 
v_name_a    varchar2_t;
v_name_b    varchar2_t;
v_name_diff varchar2_t;
v_all       varchar2_t;  
v_sort       varchar2_t;
v_idx       PLS_INTEGER;
 
BEGIN
    v_name_a:=varchar2_t('C','B','A');
    v_name_b:=varchar2_t('A','D','E');
    -- Gesamtmenge inkl. dubletten
    v_name_diff :=  v_name_a MULTISET UNION v_name_b;
    DBMS_OUTPUT.put_line(v_name_diff.COUNT);
    -- 6 A A B C D E 
    -- Schnittmenge, Werte die in beiden Mengen sind
    v_name_diff :=  v_name_a MULTISET INTERSECT v_name_b;
    DBMS_OUTPUT.put_line(v_name_diff.COUNT);
    --1 - A
    -- Differenz
    v_name_diff :=  v_name_a MULTISET EXCEPT  v_name_b;
    DBMS_OUTPUT.put_line(v_name_diff.COUNT);
    --2 - D E
 
    ---------
    -- reduce the multiset again to a list with no duplicates
    v_all := SET(v_name_a MULTISET UNION v_name_b);
    DBMS_OUTPUT.put_line(v_all.COUNT);   
    -- 5  
 
    -- loop
   v_idx := v_all.FIRST;
 
   WHILE (v_idx IS NOT NULL)
   LOOP
      DBMS_OUTPUT.PUT_LINE  (v_all(v_idx)); 
      v_idx := v_all.NEXT(v_idx); 
   END LOOP;
END;

Siehe ⇒ https://docs.oracle.com/database/121/SQLRF/operators006.htm#SQLRF0032

Sortieren mit Hilfe von SQL, dazu muss allerdings der Type als DB Object angelegt werden:

CREATE TYPE varchar2_t IS TABLE OF VARCHAR2(2000);
/
 
DECLARE
  v_unsort       varchar2_t;  
  v_sort        varchar2_t;  
  v_idx         PLS_INTEGER;
 
BEGIN
  v_unsort:=varchar2_t('Z','H','J','K','Q','A','C','B'); 
 
  -----
 
  FOR rec IN (SELECT column_value val  FROM TABLE(v_unsort)  ORDER BY column_value) 
  LOOP
    DBMS_OUTPUT.put_line(rec.val);
  END LOOP;
 
  -------
  DBMS_OUTPUT.put_line('-------');
  -----
 
   SELECT CAST (multiset( SELECT * FROM TABLE( v_unsort )  ORDER BY 1 DESC) AS varchar2_t) INTO v_sort FROM dual;
 
   v_idx := v_sort.FIRST;
   WHILE (v_idx IS NOT NULL)
   LOOP
     DBMS_OUTPUT.PUT_LINE  (v_sort(v_idx)); 
     v_idx := v_sort.NEXT(v_idx); 
   END LOOP;
 
END;
/

Kontroll Strukturen

Case Construkt

Mit dem Case Construkt kann viel Tip Arbeit erspart werden:

 <<Mein_CASE>> 
  CASE v_gehalt 
      WHEN 1000 THEN v_texte(1):='wenig';
      WHEN 2000 THEN v_texte(1):='geht so';
      WHEN 3000 THEN v_texte(1):='gut';
      ELSE v_texte(1):='Genug';
  END CASE Mein_CASE;

Siehe auch Der SQL CASE Ausdruck


Exception

Mit „ DBMS_OUTPUT.put_line('Prog ::'||$$plsql_unit||' Line::'||$$plsql_line || dbms_utility.FORMAT_ERROR_BACKTRACE);“ kann die Stelle im Code, an der die Exception abgefangen wird ermittelt werden.

Eine eigene Exception definieren und aufrufen:

SET serveroutput ON
DECLARE
   wrong_value EXCEPTION;
   PRAGMA exception_init(wrong_value, -20020);
   v_value PLS_INTEGER:=100;  
BEGIN
  IF v_value > 10 THEN
    raise_application_error( -20020, 'Value to small:: ' || v_value);
   ELSE
     RAISE wrong_value;
   END IF; 
EXCEPTION
  WHEN wrong_value THEN
	DBMS_OUTPUT.put_line('Wrong Value exception :: '||SQLERRM);
        DBMS_OUTPUT.put_line('Prog ::'||$$plsql_unit||' Line::'||$$plsql_line||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  WHEN OTHERS THEN
	DBMS_OUTPUT.put_line('Other Exception :: '||SQLCODE|| '-'|| SQLERRM);   
END;
/

Benannte Exceptions abfangen:

CREATE OR REPLACE FUNCTION getuser(p_name varchar2)
RETURN varchar2
AS
 v_result emp.ename%TYPE;
BEGIN
 SELECT ename INTO v_result FROM emp WHERE ename LIKE UPPER(p_name);
 RETURN v_result;
exception 
   WHEN TOO_MANY_ROWS THEN
	dbms_output.put_line('To many Rows  Exception ::  '||sqlcode|| '-'|| sqlerrm);   
   WHEN   NO_DATA_FOUND THEN       
	dbms_output.put_line('No DATA Found Exception ::  '||sqlcode|| '-'|| sqlerrm);   
   WHEN OTHERS THEN
	dbms_output.put_line('OTHERS Exception ::  '||sqlcode|| '-'|| sqlerrm);    
END; 
/ 

Die wichtigen benannten Exceptions:

  • CURSOR_ALREADY_OPEN ORA-06511 Cursor bereits geöffnet
  • DUP_VAL_ON_INDEX ORA-00001 Schlüssel doppelt eingetragen
  • INVALID_CURSOR ORA-01001 Cursor nicht geöffnet
  • INVALID_NUMBER ORA-01722 bei impliziter Typkonvertierung
  • LOGIN_DENIED ORA-01017 Benutzername oder Paßwort falsch
  • NO_DATA_FOUND ORA-01403 SELECT liefert kein Ergebnis
  • NOT_LOGGED_ON ORA-01012 Keine Verbindung zur Datenbank
  • PROGRAM_ERROR ORA-06501 Interner PL/SQL - Fehler
  • ROWTYPE_MISMATCH ORA-06504 Strukturvariablen inkompatibel
  • STORAGE_ERROR ORA-06500 Speicherprobleme
  • TIMEOUT_ON_RESOURCE ORA-00051 Datenbank-Sperre
  • TOO_MANY_ROWS ORA-01422 SELECT liefert mehr als eine Zeile
  • VALUE_ERROR ORA-06502
  • ZERO_DIVIDE ORA-01476 durch 0 geteilt

SQL Abfragen

Einfaches Open

Die einfachste Variante einer Schleife über das Ergebniss einer SQL Abfrage:

SET serveroutput ON
DECLARE
  CURSOR c_emp IS SELECT * FROM emp;
  v_emp_rec c_emp%ROWTYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_emp_rec;
    EXIT WHEN  c_emp%notfound;
    DBMS_OUTPUT.put_line('Empno::'||v_emp_rec.empno);
  END LOOP;  
 
  DBMS_OUTPUT.put_line('Record Count::'||c_emp%rowcount);
 
  IF c_emp%isopen 
  THEN
   CLOSE c_emp; 
  END IF;
 
END;
/

For Loop

Mit for und einem impliziten Ergebniss Record läßt sich das ganze sehr kompakt schreiben:

SET serveroutput ON
DECLARE
  CURSOR c_emp IS SELECT * FROM emp;
BEGIN
  FOR rec IN c_emp
  LOOP
    DBMS_OUTPUT.put_line('Empno::'||rec.empno || 'at row:: '||c_emp%rowcount);
  END LOOP;    
END;
/

Dynamisches SQL mit dem Ref Cursor

Mit einem String ein SQL Statment definieren:

DECLARE  
   -- Weak Cursor
   TYPE rc_c1 IS REF CURSOR;                   
   -- cursor variable
   c_c1    rc_c1;   
   -- result type
   r_emp   emp%ROWTYPE;
   -- dynamic sql Text statments .-)
   v_sql  VARCHAR2(255):='SELECT * from emp';
BEGIN
   -- open Cursor for the text definition of the sql
   OPEN c_c1 FOR v_sql;
   -- query
   LOOP
     FETCH c_c1 INTO r_emp;
     EXIT WHEN c_c1%NOTFOUND;
     DBMS_OUTPUT.put_line('Empno::'||r_emp.empno || 'at row:: '||c_c1%rowcount);
   END LOOP;
   --close
   CLOSE c_c1;   
END;
/

Dynamisches SQL mit Execute immediate

Mit „execute Immediate“ können dynamische SQL's ausgeführt werden:

DECLARE
   -- dynamic sql text statments
   v_sql  VARCHAR2(255):='insert into emp (empno,ename) values (:1,:2) RETURNING empno into :3';
   -- 
   v_empno emp.empno%TYPE;  
   --
   v_count PLS_INTEGER;   
BEGIN
  -- call sql statement with bind variable and returning value
  -- Insert
  EXECUTE  IMMEDIATE v_sql using IN 6777,'tester' RETURN INTO v_empno;
  DBMS_OUTPUT.put_line('Insert Row Count::'||sql%rowcount);
  DBMS_OUTPUT.put_line('Return Empno    ::'||v_empno);
 
  -- Delete 
  EXECUTE IMMEDIATE 'delete from emp where empno=:1' using 6777;
  DBMS_OUTPUT.put_line('Delete Row Count::'||sql%rowcount);
 
  COMMIT;
 
  -- Select
  EXECUTE IMMEDIATE 'select count(*) from emp' INTO v_count;
  DBMS_OUTPUT.put_line('Emp Count::'||v_count);  
 
END;
/

Bulk Collect - DML im Block abarbeiten

Mit dem Bulk Collect Feature können große Updates / Inserts hoch performant zusammen gefasst werden:

SET serveroutput ON
DECLARE
  -- declae table for the resuls
  TYPE t_emp_tab IS TABLE OF emp%ROWTYPE;
  -- define
  v_emptab t_emp_tab;
  v_count PLS_INTEGER;
  -- weak cursor
  c_cur  sys_refcursor;  
BEGIN
  -- Cursor example Select
  OPEN c_cur FOR 'select * from emp';
    FETCH c_cur BULK COLLECT INTO v_emptab;
  CLOSE c_cur; 
  DBMS_OUTPUT.put_line('Cursor fetch  : ' || v_emptab.COUNT);
 
  -- Execute immediate Select
  EXECUTE IMMEDIATE 'select * from emp'  BULK COLLECT INTO v_emptab;
  DBMS_OUTPUT.put_line('Execute immediate: ' || v_emptab.COUNT);
 
  -- Manipulate the data in the memory
  v_count := v_emptab.FIRST;
  WHILE v_count IS NOT NULL
  LOOP
    v_emptab(v_count).sal:=v_emptab(v_count).sal*2/v_count;
	v_count := v_emptab.next(v_count);
  END LOOP;
 
  --
  -- execute immedate update for all
  FORALL i IN 1 .. v_emptab.COUNT
   UPDATE emp SET sal=v_emptab(i).sal WHERE empno=v_emptab(i).empno;
   COMMIT;
 
  --
  -- commit all 10 rows between
   OPEN c_cur FOR 'select * from emp';
   LOOP
      FETCH c_cur  BULK COLLECT INTO v_emptab LIMIT 10;
 
      EXIT WHEN v_emptab.COUNT=0;
 
      FORALL i IN 1 .. v_emptab.COUNT
        INSERT INTO emp.fact VALUES v_emptab(i);
      COMMIT;
 
    END LOOP;
   CLOSE c_cur;
 
END;
/

siehe auch In Oracle PL/SQL das BULK COLLECT Feature einsetzen

Execute Immediate mit PLSQL Out Variable

Mit „execute immediate“ kann auch plsql aufgerufen werden, mit dem „using out“ Befehl kann auch eine Out Variable verarbeitet werden.

Beispiel:

var OS varchar2(5)
DECLARE
 v_oracle_home varchar2(512);
BEGIN
	:OS:='XX';    
	IF ( SYS_CONTEXT('USERENV', 'ISDBA') != 'FALSE' )
	THEN
	   EXECUTE immediate 'begin dbms_system.get_env(''ORACLE_HOME'',:1); end;' USING OUT v_oracle_home;
		IF instr(v_oracle_home,'/') > 0 THEN
			:OS:='LINUX';
		ELSE
			:OS:='WIN';
		END IF;
	END IF;
END;
/
print OS

DBMS_SQL - dynamisches SQL

Etwas umständlicher, dafür aber hoch flexiblel kann mit DBMS_SQL gearbeitet werden:

DECLARE
  TYPE curtype IS REF CURSOR;
  src_cur        curtype;
  v_cursor_id    NUMBER;
  v_colType_var  VARCHAR2(50);
  v_colType_num  NUMBER;
  v_colType_date DATE;
  v_tab_desc     DBMS_SQL.desc_tab;
  v_col_count    NUMBER;
  v_sql VARCHAR2(1000) := 'select * from emp';
BEGIN
 
  DBMS_OUTPUT.put_line(RPAD('=', 40, '='));
  DBMS_OUTPUT.put_line(RPAD(RPAD('-', 10, '-') || ' &&tab_name ', 40, '-'));
  DBMS_OUTPUT.put_line(RPAD('=', 40, '='));
 
  -- open cursor for the sql statement
  OPEN src_cur FOR v_sql;
 
  -- switch from native dynamic sql to dbms_sql package.
  v_cursor_id := DBMS_SQL.to_cursor_number(src_cur);
  DBMS_SQL.describe_columns(v_cursor_id, v_col_count, v_tab_desc);
 
  -- define columns.
  FOR i IN 1 .. v_col_count
  LOOP
    IF v_tab_desc(i).col_type = 2 THEN
      DBMS_SQL.define_column(v_cursor_id, i, v_colType_num);
    ELSIF v_tab_desc(i).col_type = 12 THEN
      DBMS_SQL.define_column(v_cursor_id, i, v_colType_date);
    ELSE
      DBMS_SQL.define_column(v_cursor_id, i, v_colType_var, 50);
    END IF;
  END LOOP;
 
  -- fetch rows with dbms_sql package.
  WHILE DBMS_SQL.fetch_rows(v_cursor_id) > 0
  LOOP
    FOR i IN 1 .. v_col_count
    LOOP
       DBMS_OUTPUT.put(RPAD(v_tab_desc(i).col_name, 30) || ' => ');
 
      IF (v_tab_desc(i).col_type = 1) THEN
        DBMS_SQL.column_value(v_cursor_id, i, v_colType_var);
        DBMS_OUTPUT.put_line(v_colType_var);
      ELSIF (v_tab_desc(i).col_type = 2) THEN
        DBMS_SQL.column_value(v_cursor_id, i, v_colType_num);
        DBMS_OUTPUT.put_line(v_colType_num);
      ELSIF (v_tab_desc(i).col_type IN ( 12 ) ) THEN --, 178, 179, 180, 181, 231
        DBMS_SQL.column_value(v_cursor_id, i, v_colType_date);
        DBMS_OUTPUT.put_line(v_colType_date);
      ELSE 
         DBMS_OUTPUT.put_line( 	'unsupported Datatype ::'||v_tab_desc(i).col_type);  
      END IF;    
    END LOOP;
 
    DBMS_OUTPUT.put_line(RPAD('=', 40, '='));
 
  END LOOP;
 
  -- Close the cursor
  DBMS_SQL.close_cursor(v_cursor_id);
 
END;
/

In diesen Beispiel wird eine Tabelle in Spalten Ansicht ausgegeben, siehe auch aus der Script Library: select.sql.

Weiteres Beispiel Oracle PL/SQL - Generisch aus einer Tabelle eine CSV Datei erstellen - UTL_FILE und DBMS_SQL einsetzen


PL/SQL conditional compilation

Seit 10.1.0.4

Mit Conditional Compilation kann mit PreCompiler Statements noch zusätzliche Logik in PL/SQL hinterlegt werden.

In der Umgebung von PL/SQL können Compiler Flags gesetzt werden, diese Flags können abgefragt werden.

Je nach Laufzeitumgebung wird der Code dann anders übersetzt, sehr praktisch um Features für verschiedene Relases im selben Code zuverwenden, oder um unterschiedliche Test Szenarien umzusetzen.

Beispiel:

...
 CURSOR c_organisation
	IS
	SELECT * FROM dept org
        ---debug run               
        $if $$USE_TST_SETS=1 $then 
           WHERE ROWNUM < 5
        $end
      ORDER BY DEPTNAME DESC ;
...
 
 
--------------
-- Setzen mit PLSQL_CCFlags  beim Übersetzen
--
ALTER PACKAGE export compile PLSQL_CCFlags = 'USE_TST_SETS:1' reuse settings;
 
 
-- teste mit
 
SET SERVEROUTPUT ON SIZE UNLIMITED
 
BEGIN
  DBMS_PREPROCESSOR.print_post_processed_source (
    object_type => 'PACKAGE BODY',
    schema_name => 'SCOTT',
    object_name => 'EXPORT');
END;
/
 
 

siehe dazu:

Sehr praktisch sind dabei diese Direktiven um Debug Messages mit mehr Informationen auszustatten:

  • $$PLSQL_LINE : Zeilennummer, an der die Direktive geladen wurden
  • $$PLSQL_UNIT : Name der aktuellen PL/SQL Unit

Mit 12c:

  • $$PLSQL_UNIT_OWNER : Eigentümer PL/SQL Unit
  • $$PLSQL_UNIT_TYPE : Der Typ (procedure, function, package etc.) der aktuellen PL/SQL Unit

Weitere Quellen über PL/SQL

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
prog/plsql_code_pices.txt · Zuletzt geändert: 2019/02/12 11:14 von Gunther Pippèrr