Analytische Funktionen - Beispiele

Ermitteln der Werte, die über einem Durchschnittswert liegen:

WITH 
   dept_costs AS (
      SELECT department_name, SUM(salary) dept_total
         FROM employees e, departments d
         WHERE e.department_id = d.department_id
      GROUP BY department_name),
   avg_cost AS (
      SELECT SUM(dept_total)/COUNT(*) avg
      FROM dept_costs)
SELECT * FROM dept_costs
   WHERE dept_total >
      (SELECT avg FROM avg_cost)

Ermitteln eines Maximalwertes:

SELECT deptno
     , ename
     , hiredate
     , max(hiredate) over (partition by deptno) last_date
  FROM emp
;

[siehe: http://sql-plsql-de.blogspot.de/2008/05/den-jngsten-datensatz-selektieren-mit.html]

WHERE-Bedingung mit Set-Values

SELECT *
  FROM emp e
 WHERE 1 = 1
   AND (e.deptno, e.job) IN
        (SELECT 20, UPPER('analyst')
           FROM dual)
;

Sub- und Instring - Beispiele

/**
  * instr('string', Pattern) liefert numerisch den Wert 
  * der Zeile, bei dem das Muster auftritt.
  * substr('string', Pattern_in_Zeile, Anzahl_der_Zeichen)
  */
select substr(t.block, instr(t.block, '<xs:simpleType>'), instr(t.block, '
simpleType>'))
  from xml_schema t
;
--
select instr(t.block, 'simpleType>')
  from xml_schema t
;
--
select instr(t.block, 'simpleType>')
  from xml_schema t
;
--
select substr(t.block, instr(t.block, 'simpleType>')+10, (instr(t.block, 'simpleType>') - instr(t.block, 'simpleType10))
  from xml_schema t
;
--
select instr(t.block, 'simpleType>') - instr(t.block, 'simpleType>')
  from xml_schema t
;

Update mit Rowid

update emp2 t2
   set t2.ename = 'Name'
 where 1 = 1
   and rowid = (select rowid
                  from emp2 t
                 where empno = 23);

Allerdings ist diese Art des Updates nicht sehr effizient.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53140678334596
http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/UnderstandingRowIdentifiersrowid.htm
http://psoug.org/definition/ROWID.htm
http://www.orafaq.com/wiki/ROWID

Variablen, Deklaration und Definition

-- *************************************************************
-- * allgemeine SELECT * INTO - Syntax
-- *************************************************************
select
  into
  from
 where ;
-- *************************************************************
-- * Deklaration des Typs
-- *************************************************************
declare
  v_empno Number;
  v_ename Varchar2(50);
  v_job   Varchar2(50);
begin
  select empno, ename, job
    into v_empno, v_ename, v_job
    from emp
   where empno = 1
   ;
  --
  dbms_output.put_line (v_empno || ' | ' ||
                        v_ename || ' | ' ||
                        v_job);
end;
-- *************************************************************
-- * mit dem Typ einer Tabellenspalte
-- *************************************************************
declare
  -- Definitionen
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
  v_job   emp.job%TYPE;
begin
  select empno, ename, job
    into v_empno, v_ename, v_job
    from emp
   where empno = 8001
   ;
  --
  dbms_output.put_line (v_empno || ' | ' ||
                        v_ename || ' | ' ||
                        v_job);
end;
-- *************************************************************
-- * Record-Deklaration
-- *************************************************************
declare
  -- Define a record
  TYPE t_EmpRecord is RECORD (
    v_empno emp.empno%TYPE,
    v_ename emp.ename%TYPE,
    v_job   emp.job%TYPE);
  -- Declare a variable of type record
  v_Employee t_EmpRecord;
begin
  select e.empno, e.ename, e.job
    into v_Employee.v_empno, v_Employee.v_ename, v_Employee.v_job
    from emp e
   where e.empno = 8001
   ;
  --
  dbms_output.put_line (v_Employee.v_empno || ' | ' ||
                        v_Employee.v_ename || ' | ' ||
                        v_Employee.v_job);
end;

Tabelle mittels For..Loop mit 100 Datensätzen befüllen

DECLARE
    p_EMPNO                  number(4)    := 0;
    p_ENAME                  varchar2(10) := 'Test';
    p_AENDERUNGSZEITSTEMPEL  timestamp(6) := sysdate;
    p_AENDERUNGSKENNZEICHEN  varchar2(2)  := 'I';
BEGIN
  FOR i IN 1 .. 100 LOOP
    p_EMPNO := p_EMPNO + 1;
    INSERT
      INTO CT_EMP1
      (
        EMPNO,
        ENAME,
        AENDERUNGSZEITSTEMPEL,
        AENDERUNGS_KENNZEICHEN
      )
      VALUES
      (
        p_EMPNO,
        p_ENAME,
        p_AENDERUNGSZEITSTEMPEL,
        p_AENDERUNGSKENNZEICHEN
      );
  END LOOP for_loop;
  COMMIT;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    raise_application_error (-20001,'Fehler beim Insert durch Schluesselverletzung.');
  WHEN OTHERS THEN
    dbms_output.put_line('Fehler beim Ausfuehren des Kommandos.');
    RAISE;
END;

Hier ein weiterer Link zur automatischen Testdatengenerierung:
https://www.besserdich.com/oracle/scripting/testdatengenerierung-zeilen-generator-row-generator-in-oracle/
 

Dynamisch mit dem DBMS_SQL-Package

DECLARE
--
  v_Cursor     NUMBER;
  v_CmdString  VARCHAR2(5000);
  v_Ret        INTEGER;
--
BEGIN
--
  v_Cursor    := DBMS_SQL.OPEN_CURSOR;
  v_CmdString := 'rename EMP to emp_tmp';
  DBMS_SQL.PARSE(v_Cursor, v_CmdString, DBMS_SQL.NATIVE);
  v_Ret := DBMS_SQL.EXECUTE(v_Cursor);
  DBMS_SQL.CLOSE_CURSOR(v_Cursor);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(v_Cursor);
    DBMS_OUTPUT.PUT_LINE('Fehler beim Ausfuehren des Kommandos');
    RAISE;
--
END;
/

Links

http://www.toadworld.com/products/toad-for-oracle/w/toad_for_oracle_wiki/231.dbms-sql-vs-execute-immediate.aspx
http://www.java2s.com/Tutorial/Oracle/0601__System-Packages/AnexampleofusingDBMSSQLOPENCURSOR.htm
http://www.java2s.com/Code/Oracle/System-Packages/FirstDBMSSQLExample.htm
http://docstore.mik.ua/orelly/oracle/bipack/ch02_05.htm
http://psoug.org/reference/dbms_sql.html

SQL mal wieder ein wenig dynamischer

declare
  procedure run(p_sql varchar2) as
  begin
    execute immediate p_sql;
  end;
begin       
 run('rename EMP to emp_tmp');
 run('rename emp_tmp to emp');
end;

Dazu noch ein paar nützliche Informationen:

http://use-the-index-luke.com/de/sql/mythen/dynamisches-sql-ist-langsam
http://www.insidesql.org/blogs/frankkalis/2004/07/16/dynamisches-sql-fluch-und-segen

Join über vier Tabellen

Dieses Beispiel beruht auf dem SQL - Wikipedia ER-Diagramm:
http://de.wikipedia.org/wiki/SQL#Sprachelemente_und_Beispiele

Um anzuzeigen, welche Vorlesungen die Studenten besuchen und deren zugeordnete Professoren, kann diese Abfrage verwendet werden:

select s.matrnr
     , s.name
     , v.vorlnr
     , v.titel
     , p.persnr
     , p.name
  from Student s, hoert h, Vorlesung v, Professor p
 where s.matrnr = h.matrnr
   and h.vorlnr = v.vorlnr
   and v.persnr = p.persnr
order by 2

MATRNR   NAME     VORLNR   TITEL   PERSNR   NAME
26120    Fichte   5001     ET      15       Tesla
26120    Fichte   5045     DB      12       Wirth
25403    Jonas    5001     ET      15       Tesla

Hier noch ein Beispiel zum Left Outer Join (wg. BURLESON Consulting):

http://www.dba-oracle.com/tips_oracle_left_outer_join.htm

Dynamisch Tabellen abfragen und Datensätze zählen

set serveroutput on
--
DECLARE
  CURSOR c_trig IS
    SELECT table_name
    FROM tabs
    WHERE 1=1
      AND table_name like 'T_%'
  ;
  v_zaehler number;
BEGIN
  FOR r IN c_trig
  LOOP
--    execute immediate 'TRUNCATE TABLE '||r.table_name;
    execute immediate 'select count(*) from ' || r.table_name into v_zaehler;
    DBMS_OUTPUT.PUT_LINE(r.table_name || ' : ' || v_zaehler);
  END LOOP;
END;
/

For-Cursor-Loop

DECLARE
  v_aenderungszeitstempel TIMESTAMP(6);
BEGIN
  FOR cur IN (select table_name
                from tabs
               where table_name     like 'CT_%'
                 and table_name not like 'CT_D_%'
                 and table_name not like 'CT_PROT_%') LOOP
    execute immediate 'SELECT max (aenderungszeitstempel) FROM ' || cur.table_name INTO v_aenderungszeitstempel;
    DBMS_OUTPUT.put_line('max Aenderungszeitstempel der Tabelle ' || cur.table_name || ':  ' || v_aenderungszeitstempel);
  END LOOP;
END;

einfache Queries

select table_name
  from tabs
 where table_name     like 'T_%'
   and table_name not like 'T_D_%'
   and table_name not like 'T_PRO_%'
 ;

Dynamisch SQL-Befehle generieren

select 'delete from ' || table_name || ';' from tabs;

select 'insert into ' || table_name || ' select * from ' || table_name || '@dwh;' from tabs;

select 'CREATE TABLE ' || table_name || '_S  AS (SELECT * from  ' || table_name || ');' from tabs;

Parameterübergabe eines Perl-Scripts an SQL*Plus

/*************************************************

*           Programm: start.pl

*************************************************/

sqlplus start.sql §Tabelle§

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

/*************************************************

*           Programm: start.sql

*************************************************/

PROMPT Aufruf eines SQL-Scripts mit Parameteruebergabe
exec sqlScript('&1');

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

/*************************************************

*           Programm: sqlScript.sql

*************************************************/

 select * from &1;

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

Funktionen

SELECT avg(sal) as Mittelwert FROM emp;
--> 2035 

--

-- Ermittle die Anzahl aller MA pro

-- Abteilung, die mehr als 1000,- 

-- erhalten.

--

SELECT e.deptno
     , count (e.ename)
  FROM emp e
 WHERE e.sal > 1000
GROUP BY (e.deptno)
;
--

-- Ermittle die Anzahl aller MA pro

-- Abteilung, die mehr als 1000,- 

-- erhalten und keine Provision 

-- bekommen.

--
SELECT e.deptno
     , count (e.ename)
  FROM emp e
 WHERE e.sal > 1000
   AND nvl(e.comm, 0) = 0
GROUP BY (e.deptno)
;

Correlated Subqueries

select *
  from part
 where part_id in (
   select distinct part_id
     from item
    where item_id in (
      select item_id
        from system_item_structure
       where status_deleted = 1
     )
  );
-------------- oder ------------------

SELECT DISTINCT LieferNr
  FROM Lieferprogramm lp1
 WHERE NOT EXITS (
   SELECT TeileNr
     FROM Teil t1
    WHERE NOT EXITS (
      SELECT LieferNr
        FROM Lieferprogramm lp2
       WHERE lp1.LieferNr = lp2.LieferNr
         AND t1.TeileNr = lp2.TeileNr 
    )
 );

Dies kann besser, effizienter durch eine PL/SQL-Lösung ersetzt werden:

DECLARE
 
  CURSOR Lieferanten IS
    SELECT DISTINCT LieferNr 
      FROM Lieferprogramm;
 
  CURSOR Teile IS
    SELECT DISTINCT TeileNr  
      FROM Lieferprogramm;
   AktLief Lieferprogramm.LieferNr%Type;  AktTeil Lieferprogramm.TeileNr%Type;  Treffer BOOLEAN;
 
BEGIN
  OPEN Lieferanten;
  LOOP
    FETCH Lieferanten INTO AktLief;
    EXIT WHEN Lieferanten%NOTFOUND;    Treffer := TRUE;
 
    OPEN Teile;
    WHILE Treffer LOOP
      FETCH Teile INTO AktLief;
      EXIT WHEN Teile%NOTFOUND;
      SELECT DISTINCT LieferNr  
        FROM Lieferprogramm;
       WHERE LieferNr = AktLief
         AND TeileNr = AktTeil;      Treffer := SQL%FOUND;
    END LOOP;
    IF Treffer THEN
      INSERT INTO Temp VALUES (AktLief);
    CLOSE Teile;
 
  END Loop;
  CLOSE Lieferanten;
 
END;

Select auf Select (Subquery) ersetzt View

Anforderung:

Ermittlung aller Reports und die Anzahl der Anwender je Report, die seit dem 01.01.2007 von min. 3 verschiedenen Anwendern verwendet wurden.

1.) Mit diesem Select erhalte ich die verschiedenen Anwender mit zugehöriger Report-Nummer:

select distinct rep_anwender, rep_nr

from report_list

where rep_aenddat >= TO_DATE('01.01.2007', 'dd.mm.yyyy')

group by rep_nr, rep_anwender

Anwender Report

1 100

2 100

3 100

Mit diesem Select kann ich eine View erstellen:

CREATE OR REPLACE VIEW V_View_01 AS

select distinct rep_anwender, rep_nr

from report_list

where rep_aenddat >= TO_DATE('01.01.2007', 'dd.mm.yyyy')

group by rep_nr, rep_anwender

2.) Mit dieser View ermittle ich nun zur Report-Nummer die maximale Anzahl der verschiedenen Anwender:

select rep_nr, count (all rep_nr) as maxAnzahl

from V_View_01

group by rep_nr

Als View formuliert:

CREATE OR REPLACE VIEW V_View_02 AS

select rep_nr, count (all rep_nr) as maxAnzahl

from V_View_01

group by rep_nr

3.) Mit dem dritten Select erhalte ich mein gewünschtes Ergebnis:

select rep_nr, maxAnzahl

from V_View02

where maxAnzahl >= 3

Report maxAnzahl

100 3

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

In Oracle kann ich das nun in folgendem Statement zusammenfassen:

select rep_nr,  
       count (rep_nr) maxAnzahl

  from (  
    select rep_user, 
           rep_nr

      from report_list

     where rep_aenddat >= TO_DATE('01.01.2007', 'dd.mm.yyyy')

    group by rep_nr, 
             rep_anwender

  )

group by rep_nr

having count (rep_nr) >= 3

oder

select rep_nr, maxAnzahl

  from ( 
    select rep_nr, 
           count (all rep_nr) as maxAnzahl

      from ( 
        select distinct rep_anwender, 
               rep_nr

          from report_list

         where rep_aenddat >= TO_DATE('01.01.2007', 'dd.mm.yyyy')

        group by rep_nr, 
                 rep_anwender

      )

    group by rep_nr

  )

where maxAnzahl >= 3

Select innerhalb Selektion, Subselect

SELECT ALL VSZ_FLV_NR,

       VSZ_JAHR,

       VSZ_VKZF,

       VSZ_NAMETG,

       vsz_flaecheinhektar,

       vsz_name,

       vsz_anzahleigentuemer,

       --

       (SELECT sum (AKF_AKTUELLER_BETRAG)

          FROM AKFINANZ

         where AKF_KTO_JAHR = vsz_jahr

           and akf_kto_bun_nr = vsz_bun_nr

           and AKF_KTO_FLV_NR = vsz_flv_nr

           and (AKF_FINANZIERUNGSART = 29 

                 and AKF_FINANZIER in (1, 2))

           and AKF_KTO_VKZF = VSZ_VKZF

        group by AKF_KTO_FLV_NR,

                 AKF_KTO_JAHR,

                 AKF_KTO_VKZF) as Eigenleistung,

       --

       (SELECT sum (AKF_AKTUELLER_BETRAG)

          FROM AKFINANZ

         where AKF_KTO_JAHR = vsz_jahr

           and akf_kto_bun_nr = vsz_bun_nr

           and AKF_KTO_FLV_NR = vsz_flv_nr

           and (AKF_FINANZIERUNGSART = 32 
                 and AKF_FINANZIER IN (16, 1, 11))

           and AKF_KTO_VKZF = VSZ_VKZF

        group by AKF_KTO_FLV_NR,

                 AKF_KTO_JAHR,

                 AKF_KTO_VKZF) as ZuschussEU,

       --

       (SELECT SUM(AKF_AKTUELLER_BETRAG)

          FROM AKFINANZ

         WHERE AKF_KTO_JAHR = vsz_jahr

           and akf_kto_bun_nr = vsz_bun_nr

           and AKF_KTO_FLV_NR = vsz_flv_nr

           AND (AKF_FINANZIERUNGSART = 32 
                 AND AKF_FINANZIER IN (26, 0))

           and AKF_KTO_VKZF = VSZ_VKZF

        GROUP BY AKF_KTO_FLV_NR,

                 AKF_KTO_JAHR,

                 AKF_KTO_VKZF) as ZuschussGA,

       --

       (SELECT sum (AKF_AKTUELLER_BETRAG)

          FROM AKFINANZ

         where AKF_KTO_JAHR = vsz_jahr

           and akf_kto_bun_nr = vsz_bun_nr

           and AKF_KTO_FLV_NR = vsz_flv_nr

           and (AKF_FINANZIERUNGSART = 32 
                 and AKF_FINANZIER IN (4, 5, 14))

           and AKF_KTO_VKZF = VSZ_VKZF

        group by AKF_KTO_FLV_NR,

                 AKF_KTO_JAHR,

                 AKF_KTO_VKZF) as ZuschussLM,

       --

       (SELECT sum (AKF_AKTUELLER_BETRAG)

          FROM AKFINANZ

         where AKF_KTO_JAHR = vsz_jahr

           and akf_kto_bun_nr = vsz_bun_nr

           and AKF_KTO_FLV_NR = vsz_flv_nr

           and (AKF_FINANZIERUNGSART = 26 
                 and AKF_FINANZIER IN (1, 2, 3, 4, 5, 6))

           and AKF_KTO_VKZF = VSZ_VKZF

        group by AKF_KTO_FLV_NR,

                 AKF_KTO_JAHR,

                 AKF_KTO_VKZF) as KostenbeteiligungDritter,

       --

       (SELECT VFA_DATUM

          FROM VSZFFAKTUM

         where vfa_vsz_jahr = vsz_jahr

           and vfa_bun_nr = vsz_bun_nr

           and vfa_vsz_flv_nr = vsz_flv_nr

           and vfa_vsz_vkzf = VSZ_VKZF

           and vfa_vpo_vee_nr = 41) as Anordnung,

       --

       (SELECT VFA_DATUM

          FROM VSZFFAKTUM

         where vfa_vsz_jahr = vsz_jahr

           and vfa_bun_nr = vsz_bun_nr

           and vfa_vsz_flv_nr = vsz_flv_nr

           and vfa_vsz_vkzf = VSZ_VKZF

           and vfa_vpo_vee_nr = 141) as Ausführungsanordnung,

       --

       (SELECT sum (KTO_BETRAG)

          FROM KONTO

         where kto_jahr = vsz_jahr

           and kto_bun_nr = vsz_bun_nr

           and kto_flv_nr = vsz_flv_nr

           and kto_vkzf = VSZ_VKZF

           and kto_massnahmeart = 182) as VLEBeitrag

       --

  FROM VSZFNEU

 where vsz_jahr = 2007

   and vsz_bun_nr = 9

   and vsz_flv_nr =5

   and vsz_vkzf in (
     select vfa_vsz_vkzf 
       from VSZFFAKTUM

      where vfa_vsz_jahr = vsz_jahr

        and vfa_bun_nr = vsz_bun_nr

        and vfa_vsz_flv_nr = vsz_flv_nr

        and vfa_vsz_vkzf = VSZ_VKZF

        and vfa_vpo_vee_nr = 191

        and (vfa_datum = null or vfa_datum > 
               to_date('31.12.1994','dd.mm.yyyy'))

   )

Subselect

SELECT ALL a.AKF_KTO_FLV_NR,

           a.AKF_KTO_JAHR,

           a.AKF_KTO_VKZF,

           sum (a.AKF_AKTUELLER_BETRAG) 

                 + (SELECT sum (b.AKF_AKTUELLER_BETRAG)

                      FROM AKFINANZ b

                     WHERE b.AKF_KTO_JAHR = 2007

                       AND (b.AKF_FINANZIERUNGSART = 29 

                               and b.AKF_FINANZIER = 2)

                       AND b.AKF_KTO_VKZF = a.AKF_KTO_VKZF

                    ) as Eigenleistung

  FROM AKFINANZ a

 WHERE a.AKF_KTO_JAHR = 2007

   AND (a.AKF_FINANZIERUNGSART = 29 AND a.AKF_FINANZIER = 1)

   AND a.AKF_KTO_VKZF = 586031

GROUP BY a.AKF_KTO_FLV_NR,

         a.AKF_KTO_JAHR,

         a.AKF_KTO_VKZF