DBMS_SQL mit Oracle 12c Release 2

http://www.oracle.com/technetwork/issue-archive/2017/17-mar/o27plsql-3616177.html

returning with insert .. select

http://www.oracle-developer.net/display.php?id=413

PL / SQL Arrays

http://www.tutorialspoint.com/plsql/plsql_arrays.htm

http://stackoverflow.com/questions/7012625/oracle-pl-sql-how-to-create-a-simple-array-variable

Recordsets mit VB

https://support.microsoft.com/de-de/kb/176086

SQL-Injection unterbinden

https://de.wikipedia.org/wiki/SQL-Injection

Ref Cursor

http://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

http://oracle-base.com/articles/8i/complex-recordsets.php

http://www.martinbulinski.de/plsql-tutorial/dynamisches-sql-mit-ref-cursor

https://msdn.microsoft.com/de-de/library/bw9eczdk%28v=vs.110%29.aspx

http://sql-plsql.blogspot.de/2007/05/oracle-plsql-ref-cursors.html    !!!

http://www.cmoser.de/blog-archiv.html?month=201307

http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Definerefcursorvariable.htm

http://www.asktheoracle.net/what-is-ref-cursor.html

http://www.mathworks.com/matlabcentral/newsreader/view_thread/260796

http://www.akadia.com/services/ora_return_result_set.html

http://www.toadworld.com/platforms/oracle/w/wiki/1856.ref-cursor-type.aspx

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.plsql.doc/doc/c0053892.html

http://www.morganslibrary.org/reference/plsql/ref_cursors.html

http://www.databasedesign-resource.com/ref-cursor.html

Example: Kopieren einer Tabelle per PL/SQL-Block

DECLARE

  l_sql           varchar2(32767);

  c_tab_comment   varchar2(32767);

  PROCEDURE run(p_sql varchar2) AS

  BEGIN

    execute immediate p_sql;

 

  END;

BEGIN

  run('create table EMP_TMP as select * from EMP where ' || 11 ||

      ' = 1');

  select comments

    into c_tab_comment

    from sys.all_TAB_comments

   where owner = 'TEST'

     and table_name = 'EMP'

     and comments is not null;

  run('comment on table TEST.EMP is ' || '''' || c_tab_comment || '''');

  for tc in (select column_name

               from sys.all_tab_cols

              where owner = 'TEST'

                and table_name = 'EMP') loop

    for c in (select comments

                from sys.all_col_comments

               where owner = 'TEST'

                 and table_name = 'EMP'

                 and column_name = tc.column_name) loop

      run('comment on column TEST.EMP.' || tc.column_name ||

          ' is ' || '''' || c.comments || '''');

    end loop;

  end loop;

EXCEPTION

  WHEN OTHERS THEN

    NULL;

END;

########################################################

CREATE TABLE new_table
  AS (SELECT *
      FROM old_table WHERE 1=2);

aus:
[http://www.techonthenet.com/sql/tables/create_table2.php]

APEX als Rapid Application Development Tool

http://www.heise.de/developer/artikel/Oracle-APEX-Rapid-Application-Development-Werkzeug-fuer-Webanwendungen-227214.html

https://blogs.oracle.com/apexcommunity_deutsch/de/

Error Handling / Exceptions

Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2)

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS007

Uni Potsdam - Wiki (Stefan Uhlmann)

http://fara.cs.uni-potsdam.de/~uhlmann/19/ch09.html

For Cursor Loop - Example

  set serveroutput on;
  --
  DECLARE
    v_anzahlDS NUMBER(20);
  BEGIN
    FOR n IN (SELECT owner, table_name
                FROM all_tables
               WHERE owner = 'SCOTT' order by 1)
    LOOP
      execute immediate 'select count(*) from '|| n.owner ||'.'|| n.table_name INTO v_anzahlDS;
      dbms_output.put_line(n.table_name || '   ' || v_anzahlDS);
    END LOOP;
  END;

Top Down mit PL/SQL

CREATE OR REPLACE PROCEDURE get_EmpData
IS
  -- Cursor deklarieren
  CURSOR c_emp
  IS
    SELECT ename
         , job
         , sal
      FROM emp
    ORDER BY job;
  -- Variablen deklarieren
  v_erg VARCHAR2(3000) := NULL;
BEGIN
  FOR i IN c_emp
  LOOP
    v_erg := i.ename ||',' || i.job ||',' || i.sal;
    dbms_output.put_line(v_erg);
  END LOOP;
END get_EmpData;

SWE mit PL/SQL

-- ---------------------------------------------

-- Grundstruktur eines PL/SQL-Blockes, um eine

-- Liste (Array, Collection) abzuarbeiten.

--

-- Eine FOR-Schleife gibt alle DB-User aus,

-- die das SELECT-Statement zurückgibt.

-- ---------------------------------------------

set serveroutput on;
--
DECLARE

BEGIN
   FOR i IN (SELECT username FROM all_users)
   LOOP
     dbms_output.put_line(i.username);
   END LOOP;
EXCEPTION
   WHEN others THEN
     dbms_output.put_line('sqlerrm : '||sqlerrm);
END;

-- ---------------------------------------------

-- Die Grundstruktur eines PL/SQL-Blockes, um 

-- eine Matrix (2-dimensionales Array), zu be-

-- arbeiten.

--

-- Die Main - FOR-Schleife gibt alle DB-User an

-- die Sub - FOR-Schleife. Diese 2. Schleife gibt 

-- dann den Usernamen und die Tabellen, die dem 

-- DB-User gehören aus.

-- ---------------------------------------------

DECLARE
BEGIN
  FOR i IN (SELECT username FROM all_users)
  LOOP
    FOR j IN (SELECT table_name FROM all_tables
               WHERE owner = i.username
                 AND table_name LIKE 'XML%'
              ORDER BY table_name)
    LOOP
      dbms_output.put_line(i.username || '   ' || j.table_name);
    END LOOP;
  END LOOP;
EXCEPTION
  WHEN others THEN
    dbms_output.put_line('sqlerrm : '||sqlerrm);
END;

-- ---------------------------------------------

-- Das geht natürlich einfacher und viel schneller, 

-- weil der Kontext-Switch zwischen PL/SQL und SQL-

-- Engine wegfällt ...

-- ---------------------------------------------

SELECT au.username
     , allt.table_name
  FROM all_users au
     , all_tables allt
 WHERE allt.owner = au.username
   and table_name like 'XML%'
order by table_name
;

-- ---------------------------------------------

-- oder evtl. auf diesem Weg ...

-- ---------------------------------------------

SELECT table_name
  FROM all_tables
 WHERE owner NOT IN ('SYSTEM'
                   , 'SYS'
                   , 'SYSAUX'
                   , 'ORDSYS'
                   , 'WMSYS'
                   , 'MDSYS'
                   , 'EXFSYS'
                   , 'TSMSYS'
                   , 'XDB'
                   , 'DBSNMP'
                   , 'OUTLN'
                   , 'CTXSYS'
                   , 'APPQOSSYS')
   AND table_name LIKE 'VOLL%'
    OR table_name LIKE '%VOLL%'
ORDER BY owner
;

Anonymer PL/SQL-Block, um eine ID aus einer Sequenz zu füllen

Allgemein 12. März 2014 Hinterlasse einen Kommentar

declare

  --

  v_seq_nr NUMBER(18) := 0;

  --

  CURSOR c_xd_id

  IS

    SELECT xds.xd_id

      FROM xml_dokument_satz xds

     WHERE xds.id = 1;

  --

begin

  --

  FOR i IN c_xd_id 

  LOOP

    select seq_xds.nextval 

      into v_seq_nr 

      from dual;

    --

    UPDATE XML_DOKUMENT_SATZ

       SET ID = v_seq_nr

     WHERE xd_id = i.xd_id;

  END LOOP;

  --

  commit;

  --

exception

  when others then

    dbms_output.put_line('sqlerrm : '||sqlerrm);

end;

PL/SQL - Blockstruktur

Anonymer PL/SQL-Block

DECLARE

…

BEGIN

…

EXCEPTION

…

END;

Stored Program Units

--------Prozedur --------

CREATE PROCEDURE <procedure_name>

(p_parameter1  IN <typ>, p_parameter2  OUT <typ>, p_parameter2  IN OUT <typ>)

IS/AS

<Deklarationen>

BEGIN

…

EXCEPTION

…

END <procedure_name>;

--------Funktion --------

CREATE FUNCTION <function_name> (p_parameter1  IN <typ>, p_parameter2  OUT <typ>, p_parameter2  IN OUT <typ>)

RETURN <typ>

IS/AS

<Deklarationen>

BEGIN

…

RETURN <ausdruck>

EXCEPTION

…

END <function_name>;

--------Aufrufe --------

VARIABLE   v_<name>   <TYP>

EXECUTE     :v_<name>   :=  <function_name>(<value>)

DECLARE  v_<name>   <TYP>;

BEGIN

v_<name>   :=  <function_name>(<value>)

END;

EXECUTE dbms_output.put_line(<function_name>(<value>));

SELECT <function_name>(<value>)

  FROM <tabelle>;

Beispiel:

select mytools.get_Value(5) from dual;

SQL dynamisch ausführen mit Bind Variablen

##################### Wert in Variable ##########################
 

PROCEDURE s_leseLetzteLfdNr ( p_i_tabelle IN VARCHAR2 )

/****************************************************************
 *  BESCHREIBUNG: Die ID des letzten Transfervorganges wird

 *  aus der Steuertabelle abgefragt.
 *
 *  PARAMETER:    p_i_tabelle IN          Tabelle, deren Daten

 *                                        transferiert werden.
 *
 *  FEHLER:        
 *                 
 *
 *  CHANGE HISTORY:
 *  Datum       Name            Beschreibung
 *  ----------  -------------- ----------------------------------
 *  ...
 **************************************************************/
IS
 

  sqlStr VARCHAR2(1000);

  g_offset NUMBER(30) := 1;

BEGIN
  -- Die letzte laufende Nummer aus der Remote-DB lesen
  -- und in der Konfigurationstabelle ablegen.
 
  sqlStr := 'SELECT max_id
               FROM remote_tabelle@remoteUser
              WHERE tabname = :get_table
                AND attribut = ''SPALTE''';
   
  DBMS_OUTPUT.PUT_LINE(sqlStr);
   
  EXECUTE immediate sqlStr INTO g_offset USING p_i_tabelle;
   
END s_leseLetzteLfdNr;


##################### Update in Tabelle ####################### 

PROCEDURE s_schreibeLetzteLfdNr ( p_i_tabelle IN VARCHAR2 )

/***************************************************************
 *  BESCHREIBUNG: Die ID des letzten Transfervorganges wird

 *  aus der Steuertabelle (remote_tabelle) abgefragt und in 
 *  der Konfigurationstabelle (config_table) festgehalten.
 *
 *  PARAMETER:    p_i_tabelle IN          Tabelle, deren Daten
 *                                        transferiert werden.
 *
 *  FEHLER:        
 *                 
 *
 *  CHANGE HISTORY:
 *  Datum       Name            Beschreibung
 *  ----------  -------------- ------------------------------
 *  ...
 ************************************************************/
IS
 

  sqlStr VARCHAR2(1000);

BEGIN
  -- Die letzte laufende Nummer aus der Remote-DB lesen
  -- und in der Konfigurationstabelle ablegen.
 
    sqlStr := 'UPDATE config_table
                  SET max_id = (
                    SELECT max_id
                      FROM remote_tabelle@remoteUser
                     WHERE tabname = :get_table
                        AND attribut = ''SPALTE'')
                WHERE t_name = :get_table';
   
    DBMS_OUTPUT.PUT_LINE(sqlStr);

    EXECUTE immediate sqlStr USING p_i_tabelle, p_i_tabelle;
   
END s_schreibeLetzteLfdNr;

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm
http://stackoverflow.com/questions/7816402/using-bind-variables-with-dynamic-select-into-clause-in-pl-sql  
http://www.oracle-base.com/articles/9i/dynamic-binds-using-contexts.php
 

Trigger-Beispiel für eine fortlaufende Änderungsnummer

1. Erzeugen einer Sequenz

CREATE SEQUENCE SEQ_AENDNR_CT_DEPT
       MINVALUE 1
       MAXVALUE 999999
   INCREMENT BY 1
     START WITH 1
         CACHE 20
       NOORDER 
       NOCYCLE
;

2. Der Trigger, der einen neuen Wert aus der Sequenz erzeugt und einfügt

CREATE OR REPLACE

TRIGGER T_SEQ_AendNr_CT_Dept

BEFORE INSERT 

ON CT_Dept  

FOR EACH ROW

DECLARE

  -- Lokale Variablen

  next_id NUMBER;

BEGIN

  SELECT SEQ_AENDNR_CT_DEPT.NEXTVAL INTO next_id

    FROM dual;

  :NEW.AENDERUNGSNUMMER := next_id;

END T_SEQ_AendNr_Fachlsperre;

/

[s.a. SQL-kurz&gut, J. Gennick, 2. Auflage 2007, S. 139, O'REILLY]

PL/SQL-Cursor

Cursor-Konzept

Um die Daten einer Tabelle zu verarbeiten, um sie in eine Ablauflogik einzubinden, gibt es bei Oracle die Programmiersprache PL/SQL und die Cursor-Technologie. Dabei enthält der Cursor die selektierten Daten, ist er eine Entsprechung des Speicherbereiches.

Cursor-Attribute

Bei Oracle können vier Cursor-Attribute abgefragt werden, um Informationen über die Datenmenge zu erhalten:

• %NOTFOUND
• %FOUND
• %ROWCOUNT
• %ISOPEN

%NOTFOUND liefert TRUE, wenn der letzte Datensatz erreicht wurde; es sind keine weiteren Datensätze vorhanden.
%FOUND liefert dagegegen TRUE, wenn nach einem Cursor-Fetch noch ein weiterer Datensatz existiert.
%ROWCOUNT zählt die gefundenen bzw. geholten Datensätze.
%ISOPEN Solange der Cursor offen ist, liefert dieses Attribut den Wert TRUE andernfalls FALSE.