Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:plsql_code_pices

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
prog:plsql_code_pices [2019/02/12 11:14] – [PL/SQL - Code Beispiele] gpipperrprog:plsql_code_pices [2019/02/12 11:14] (aktuell) – [Mit langen Strings arbeiten] gpipperr
Zeile 1: Zeile 1:
 +===== 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
  
prog/plsql_code_pices.txt · Zuletzt geändert: 2019/02/12 11:14 von gpipperr