Oracle SQL: Tabellen benennen ohne namespace-pollution

UNAUSGEGORENE GEDANKEN im folgenden Artikel

Es geht in diesem Artikel um verschiedene Ansätze, einem mühsam zusammengedaddelten Resultset einen Namen zu geben, um PL/SQL-Skripte lesbarer zu machen, jedoch ohne die Tabelle permanent zu speichern und damit den Namespace mit immer mehr Hilfstabellen zu verschmutzen.

Sprechende Namen hinter AS

… (SELECT * FROM ABC LEFT JOIN XYZ ON ABC.myID = XYZ.myID) AS mySpecialItems

Named Resultsets beliebiger Struktur

… in DDL

Temporäre Tabellen, die also (im Gegensatz zu globale temporary tables) komplett wieder verschwinden gibt es scheinbar erst ab Oracle 18c.

Alternativ kann man Tabellen erzeugen und am Ende des Skriptes auch immer gleich wieder löschen.

… in reinem SQL

WITH magic_numbers AS (
    SELECT '42, 4711' AS str 
    FROM DUAL
)
SELECT substr(str, 0, 2) AS The_Answer
FROM magic_numbers;

Allgemeine, leicht verständliche Erläuterung zur WITH-clause: https://modern-sql.com/de/feature/with

WITH-clause in Oracle: https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#i2066378

… in PL/SQL

DECLARE
  the_question VARCHAR2(100);
  the_answer VARCHAR2(100);
  resultset_with_the_question VARCHAR2(100);
  resultset_with_the_answer VARCHAR2(100);
BEGIN
  the_question := 'Life, the Universe, and Everything';
  the_answer := '42';
  resultset_with_the_question := 'SELECT ''' || the_question || ''' FROM DUAL';
  resultset_with_the_answer := 'SELECT ''' || the_answer || ''' FROM DUAL';
  execute immediate resultset_with_the_question || ' UNION ' || resultset_with_the_answer;
END;

Named Resultsets gleicher Struktur

Einfaches Beispiel mit %TYPE

Im folgenden Snippet findet sich zwar kein Beispiel für ein Resultset, aber es hilft das nachfolgende Beispiel besser zu verstehen:

DECLARE   
   unternehmensname partner.firma%TYPE;
BEGIN
    SELECT firma INTO unternehmensname 
    FROM partner
    WHERE partnerid = 1;

    DBMS_OUTPUT.PUT_LINE('Unternehmensname: ' || unternehmensname);
END;
/

Quelle: selbst erstellt

Beispiel mit %TYPE, %ROWTYPE, Collection-type und (BULK COLLECT) INTO

DECLARE
  deptid        employees.department_id%TYPE;
  jobid         employees.job_id%TYPE;
  emp_rec       employees%ROWTYPE;
  TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  all_emps      emp_tab;
BEGIN
  SELECT department_id, job_id INTO deptid, jobid 
     FROM employees WHERE employee_id = 140;
  IF SQL%FOUND THEN 
    DBMS_OUTPUT.PUT_LINE('Dept Id: ' || deptid || ', Job Id: ' || jobid);
  END IF;
  SELECT * INTO emp_rec FROM employees WHERE employee_id = 105;
  SELECT * BULK COLLECT INTO all_emps FROM employees;
  DBMS_OUTPUT.PUT_LINE('Number of rows: ' || SQL%ROWCOUNT);
END;
/

Quelle