Jeder Oracle-Datenbank-Entwickler wird vermutlich im Laufe der Jahre das ein oder andere Helfer-Skript gebaut haben, das ihm die tägliche Arbeit etwas erleichtert. Dies können PL/SQL-Funktionen oder Prozeduren sein, die universell und damit in verschiedenen Projekten einsetzbar sind, Skripte, die bei der Analyse von Problemen helfen, oder Skripte, die einem eher stupide Tipp-Tätigkeiten abnehmen.
Genau für letzteren Anwendungsfall habe ich schon vor Jahren die kleine PL/SQL-Funktion GEN_SQL geschrieben, die zu einer gegebenen Datenbank-Tabelle verschiedene Arten von SQL-Statements generiert.
Die PL/SQL-Funktion GEN_SQL
Der Funktion GEN_SQL übergibt man als ersten Parameter den Namen einer Tabelle aus dem gleichen Datenbankschema und als zweiten Parameter die Art des zu generierenden SQL-Statements.
Folgende Werte sind für diesen zweiten Parameter möglich:
| Parameterwert | Beschreibung |
| S | Funktion generiert ein Select-Statement zu einer Tabelle |
| U | Funktion generiert ein Update-Statement zu einer Tabelle, inkl. Where-Klausel, bei der auf einen Primary Key (falls vorhanden) eingeschränkt wird |
| I | Funktion generiert ein Insert-Statement zu einer Tabelle |
| M | Funktion generiert ein Merge-Statement zu einer Tabelle, inkl. ON-Klausel, bei der der Primary Key (falls vorhanden) verwendet wird |
| C | Funktion generiert kein SQL-Statement sondern eine simple kommaseparierte Liste der Spaltennamen einer Tabelle. Diese ist der Default-Wert |
Die Funktion GEN_SQL sieht wie folgt aus:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 | CREATE OR REPLACE FUNCTION HR.GEN_SQL (p_table IN VARCHAR2, p_type IN VARCHAR2 DEFAULT 'C') RETURN VARCHAR2 IS /* Helper function to generate SQL statements for a table in the same schema. Parameter p_type can be: C = Function generates a simple Column-List of the specified table. This is the default. S = Function generates a select statement for the specified table. U = Function generates an update statement for the specified table. I = Function generates an insert statement for the specified table. M = Function generates a merge statement for the specified table. Author: Frank Winter */ v_line VARCHAR2 (4000); v_line2 VARCHAR2 (4000); v_result VARCHAR2 (4000); v_newline VARCHAR2 (5) := CHR (13) || CHR (10); v_table VARCHAR2 (40) := TRIM(UPPER(p_table)); v_type CHAR (1) := TRIM(UPPER(p_type)); v_pk_part VARCHAR2 (4000); v_update VARCHAR2 (4000); -- select all field name of a specified table: CURSOR c1 IS SELECT column_name, column_id, count(*) over (partition by table_name) rc FROM user_tab_columns WHERE table_name = v_table ORDER BY column_id; -- sub function that returns the part of the update oder merge statement where a primary key is used: function create_pk_part(p_table_name IN varchar2) return varchar2 is cursor c2 is select position, column_name from user_cons_columns where constraint_name = (select constraint_name from user_constraints uc where table_name = p_table_name and constraint_type = 'P') order by position; l_pk_part varchar2(4000); begin for r in c2 loop l_pk_part := l_pk_part|| ' t.'||r.COLUMN_NAME||' = r.'||r.COLUMN_NAME|| ' AND' ; end loop; if l_pk_part is null then l_pk_part := '<TODO: no PK-constraint available>'; else l_pk_part := substr(l_pk_part, 1, length(l_pk_part) - 4); -- erase the last ' AND' -- end if; return trim(l_pk_part); end; BEGIN -- different actions depending from the second parameter: IF v_type IN ('U', 'M') THEN v_pk_part := create_pk_part(v_table); END IF; CASE WHEN v_type IN ('C', 'S') THEN FOR r_c1 IN c1 LOOP v_line := v_line || r_c1.column_name || ', '; END LOOP; v_line := TRIM (v_line); v_line := SUBSTR (v_line, 1, LENGTH (v_line) - 1); IF v_type = 'S' THEN v_result := 'SELECT '|| v_line || ' FROM '|| v_table; ELSIF v_type = 'C' THEN v_result := v_line; END IF; WHEN v_type = 'I' THEN v_line := gen_sql(v_table); v_line2 := 'r.'||replace(v_line, ', ',', r.'); v_result := 'INSERT INTO '|| v_table || ' ' ; v_result := v_result || '(' || v_line || ')' || ' ' ||v_newline; v_result := v_result || 'VALUES '; v_result := v_result || '(' || v_line2 || ')' || ' ' ||v_newline; WHEN v_type = 'U' THEN v_result := 'UPDATE ' || v_table || ' t SET ' ||v_newline; FOR r_c1 IN c1 LOOP v_line := v_line||'t.'||r_c1.column_name || ' = r.'|| r_c1.column_name ; if r_c1.column_id < r_c1.rc then v_line := v_line|| ', ' ||v_newline; end if; END LOOP; v_result := v_result||v_line||v_newline||'WHERE '||v_pk_part; WHEN v_type = 'M' THEN -- changing the update part of the merge statement: -- v_update := replace(gen_sql(v_table,'U'), v_table, NULL); v_update := replace(v_update, ' t SET', ' SET'); v_update := substr(v_update, 1, instr(v_update, 'WHERE ') -1); v_result := 'MERGE INTO ' || v_table || ' t '||v_newline ||'USING ('||gen_sql(v_table, 's')||') r'||v_newline ||'ON ('||v_pk_part||')'||v_newline ||'WHEN MATCHED THEN '||v_newline ||v_update ||'WHEN NOT MATCHED THEN '||v_newline ||replace(gen_sql(v_table,'I'), 'INTO '||v_table, NULL)||v_newline; END CASE; RETURN v_result; EXCEPTION WHEN OTHERS THEN raise_application_error (-20001, 'FEHLER!!!!: ' || SQLERRM); END; / |
Anwendungsbeispiele für GEN_SQL
Nachfolgend ein paar Anwendungsbeispiele für den Einsatz der Funktion GEN_SQL.
Um einfach nur die Spalten-Liste der in den folgenden Beispielen verwendeten Tabelle EMPLOYEES zu erhalten, ruft man die Funktion wie folgt auf:
1 2 3 | select GEN_SQL('EMPLOYEES') from dual; |
Als Ergebnis erhält man schlicht eine kommaseparierte Liste der Tabellenspalten, sortiert nach ihrer Position in der Tabelle. Das Ergebnis der obigen Abfrage sieht somit folgendermaßen aus:
1 2 3 | EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID |
Um ein einfaches Select-Statement für eine Tabelle zu generieren, ruft man die Funktion mit dem Parameter „S“ auf:
1 2 3 | select GEN_SQL('EMPLOYEES','S') from dual; |
Der Rückgabewert der Funktion ist in diesem Beispiel folgendes Select-Statement:
1 2 3 | SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID FROM EMPLOYEES |
Soll ein Update-Statement für eine Tabelle erzeugt werden, wird die Funktion mit dem Parameter „U“ aufgerufen:
1 2 3 | select GEN_SQL('EMPLOYEES','U') from dual; |
Der Ergebniswert besteht aus folgendem Update-Statement:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | UPDATE EMPLOYEES t SET t.EMPLOYEE_ID = r.EMPLOYEE_ID, t.FIRST_NAME = r.FIRST_NAME, t.LAST_NAME = r.LAST_NAME, t.EMAIL = r.EMAIL, t.PHONE_NUMBER = r.PHONE_NUMBER, t.HIRE_DATE = r.HIRE_DATE, t.JOB_ID = r.JOB_ID, t.SALARY = r.SALARY, t.COMMISSION_PCT = r.COMMISSION_PCT, t.MANAGER_ID = r.MANAGER_ID, t.DEPARTMENT_ID = r.DEPARTMENT_ID WHERE t.EMPLOYEE_ID = r.EMPLOYEE_ID |
Generierung eines einfachen Insert-Statements
Dieses generierte Update-Statement arbeitet mit einem Alias namens „r“ als Namen für einen beliebigen PL/SQL-Record. Ich hatte dies in der Vergangenheit benötigt, da ich die betroffenen Tabellen oft mit Werten aus einem Record upgedatet hatte. Wenn man dies nicht wünscht, lässt sich das Skript an dieser Stelle natürlich leicht anpassen.
Um ein einfaches Insert-Statement zu generieren, ruft man die Funktion mit dem Parameter „I“ auf:
1 2 3 | select GEN_SQL('EMPLOYEES','I') from dual; |
Der Rückgabewert der Funktion ist folgendes Insert-Statement:
1 2 3 4 | INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID) VALUES (r.EMPLOYEE_ID, r.FIRST_NAME, r.LAST_NAME, r.EMAIL, r.PHONE_NUMBER, r.HIRE_DATE, r.JOB_ID, r.SALARY, r.COMMISSION_PCT, r.MANAGER_ID, r.DEPARTMENT_ID) |
Auch dieses generierte Statement arbeitet mit einem Alias namens „r“ (s. o.).
Generierung eines Merge-Statements
Die meiste lästige Tipparbeit hat man im Allgemeinen bei dem Erstellen eines Merge-Befehls. Hier daher der beispielhafte Aufruf für die Generierung eines Merge-Befehls:
1 2 3 | select GEN_SQL('EMPLOYEES','M') from dual; |
Der Ergebniswert besteht in dem Beispiel aus folgendem Statement:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | MERGE INTO EMPLOYEES t USING (SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID FROM EMPLOYEES) r ON (t.EMPLOYEE_ID = r.EMPLOYEE_ID) WHEN MATCHED THEN UPDATE SET t.EMPLOYEE_ID = r.EMPLOYEE_ID, t.FIRST_NAME = r.FIRST_NAME, t.LAST_NAME = r.LAST_NAME, t.EMAIL = r.EMAIL, t.PHONE_NUMBER = r.PHONE_NUMBER, t.HIRE_DATE = r.HIRE_DATE, t.JOB_ID = r.JOB_ID, t.SALARY = r.SALARY, t.COMMISSION_PCT = r.COMMISSION_PCT, t.MANAGER_ID = r.MANAGER_ID, t.DEPARTMENT_ID = r.DEPARTMENT_ID WHEN NOT MATCHED THEN INSERT (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID) VALUES (r.EMPLOYEE_ID, r.FIRST_NAME, r.LAST_NAME, r.EMAIL, r.PHONE_NUMBER, r.HIRE_DATE, r.JOB_ID, r.SALARY, r.COMMISSION_PCT, r.MANAGER_ID, r.DEPARTMENT_ID) |
Das generierte Merge-Statement ist jedenfalls noch anzupassen. Zum einen darf der Update-Teil nicht den Key updaten, der in der ON-Klausel verwendet wird. Dieser Teil des Updates ist zu entfernen (im obigen Beispiel also der Teil „t.EMPLOYEE_ID = r.EMPLOYEE_ID, „).
Zum anderen kann man davon ausgehen, dass das Select-Statement in der USING-Klausel so nur bedingt sinnvoll ist, da auf diese Weise in die Tabelle Werte aus sich selbst gemerget werden. Funktionieren wird es, dürfte in der Praxis aber selten vorkommen. Welches Select-Statement in der USING-Klausel enthalten sein soll, ist automatisch aber nicht ermittelbar. Daher ist dieser Teil für eine nachträgliche Änderung vorgesehen.
Fazit
Letztlich gilt für alle obigen Anwendungsbeispiele, dass man mit der Verwendung der GEN_SQL-Funktion ein brauchbares Grundgerüst für ein SQL-Statement erhält, das aber im Einzelfall an die eigenen Wünsche anzupassen ist. Ob dies manuell oder im Rahmen eigenen PL/SQL-Codes geschieht, in den diese Funktion integriert wird, bleibt jedem Entwickler selber überlassen.
