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