===== 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.


<code plsql>

v_my_very_long_string := q'[ 

select ****  from xxxx where yyy in ('1','2','3')
]';

</code>
==== Collections ====

=== PL/SQL Table definieren===

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

Typ definieren:
<code plsql>
TYPE myPlsqltab is TABLE OF varchar2(2000) index by binary_integer;
</code>

Variable deklarieren:
<code plsql>
v_texte myPlsqltab;
</code>

Werte zuweisen:
<code plsql>
v_texte(3):='Dies ist die erste Ziele';
v_texte(4):='Dies ist die zweite Ziele';
v_texte(8):='Dies ist die dritte Ziele';

</code>

Anzahl der Elemente mit count
<code>
dbms_output.put_line('-- Info :: Anzahl der Elemente :: '||v_texte.count);
</code>

Wert in einer Schleife auslesen:
<code plsql>

-- 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;
   
</code>

Komplettes Beispiel: {{:prog:plsql:plsql_table_example_v1_gpi.zip| 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:
<code sql>
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;
/
</code> 

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

==Nested Tables==

Unterschied => auf "index by" wird verzichtet!

Typ definieren:
<code plsql>
TYPE varchar_t is TABLE OF varchar2(2000);
</code>


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

<code plsql>
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;

</code>

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:

<code plsql>
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;
/
</code>


----

==== Kontroll Strukturen====

=== Case Construkt ===

Mit dem Case Construkt kann viel Tip Arbeit erspart werden:

<code plsql>

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

</code>

Siehe auch [[prog:sql_case_when|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:
<code plsql>
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;
/
</code>

Benannte Exceptions abfangen:

<code sql>

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; 
/ 
</code>
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:
<code plsql>
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;
/
</code>

==== For Loop ====

Mit for und einem impliziten Ergebniss Record läßt sich das ganze sehr kompakt schreiben:
<code plsql>
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;
/
</code>


==== Dynamisches SQL mit dem Ref Cursor ====

Mit einem String ein SQL Statment definieren:
<code plsql>

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

</code>


==== Dynamisches SQL mit Execute immediate ====
Mit "execute Immediate" können dynamische SQL's ausgeführt werden:
<code plsql>
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;
/
</code>

==== Bulk Collect  - DML im Block abarbeiten ====

Mit dem Bulk Collect Feature können große Updates / Inserts hoch performant zusammen gefasst werden:
<code plsql>
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;
/
</code>

siehe auch [[prog:plsql_bulk_collect|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:
<code sql>
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

</code>

==== DBMS_SQL - dynamisches SQL ====

Etwas umständlicher, dafür aber hoch flexiblel kann mit DBMS_SQL gearbeitet werden:
<code plsql>
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;
/
</code>
In diesen Beispiel wird eine Tabelle in Spalten Ansicht ausgegeben, siehe auch aus der Script Library:
[[https://orapowershell.codeplex.com/SourceControl/latest#sql/select.sql|select.sql]].

Weiteres Beispiel [[prog:plsql_spool_utf_8_file|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:
<code plsql>
...
 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;
/

              
</code>

siehe dazu:
  * http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-conditional-compilation-133587.pdf
  * https://oracle-base.com/articles/10g/conditional-compilation-10gr2


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 =====


Der Guru - Steven Feuerstein:
  * http://stevenfeuersteinonplsql.blogspot.de/
  * http://www.oracle.com/technetwork/issue-archive/index-087690.html


Im Web:
  * http://www.tutorialspoint.com/plsql/plsql_records.htm