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;