Oracle PL/SQL dynamische SQL-zelfstudie: onmiddellijk uitvoeren en DBMS

⚡ Slimme samenvatting

Dynamische SQL in Oracle PL/SQL bouwt en voert statements uit tijdens de runtime en past query's aan veranderende vereisten aan via twee benaderingen: native dynamische SQL met EXECUTE IMMEDIATE en OPEN-FOR, en het flexibele DBMS_SQL-pakket voor complexe gevallen.

⚙️ SQL tijdens uitvoering: Dynamische SQL genereert en voert instructies uit wanneer tabel- of kolomnamen van tevoren onbekend zijn.
⚡ Native dynamische SQL: EXECUTE IMMEDIATE maakt en voert SQL snel uit met minimale code.
🔁 OPEN-VOOR: Verwerkt dynamische query's met meerdere rijen die EXECUTE IMMEDIATE niet zelfstandig kan ophalen.
🧩 DBMS_SQL: Deze instructies zijn geschikt voor situaties waarin het aantal kolommen of de gegevenstypen pas tijdens de uitvoering bekend zijn.
🔐 Bindvariabelen: De USING-clausule geeft waarden positioneel door en blokkeert SQL-injectie.
🤖 AI-assistentie: AI-tools genereren dynamische SQL-query's en signaleren injectierisico's tijdens de beoordeling.

Meer informatie

Wat is dynamische SQL?

Dynamisch SQL SQL is een programmeermethode voor het genereren en uitvoeren van statements tijdens de runtime. Het wordt voornamelijk gebruikt voor het schrijven van algemene en flexibele programma's waarbij de SQL-statements tijdens de runtime worden aangemaakt en uitgevoerd op basis van de vereisten, bijvoorbeeld wanneer tabelnamen, kolomlijsten of WHERE-voorwaarden pas bekend zijn wanneer het programma wordt uitgevoerd.

Manieren om dynamische SQL te schrijven

PL/SQL biedt twee manieren om dynamische SQL te schrijven:

NDS – Native dynamische SQL (de EXECUTE IMMEDIATE- en OPEN-FOR-instructies)
DBMS_SQL (een meegeleverd pakket)

De algemene regel is eenvoudig: als het aantal en de gegevenstypen van de invoer- en uitvoervariabelen bekend zijn tijdens het compileren, gebruik dan Native Dynamic SQL, omdat dit sneller is en minder code vereist. Wanneer deze informatie pas tijdens de uitvoering bekend is, gebruik dan het DBMS_SQL-pakket.

NDS (Native Dynamic SQL) – Direct uitvoeren

Native Dynamic SQL is de eenvoudigere manier om dynamische SQL te schrijven. Het maakt gebruik van de EXECUTE IMMEDIATE-opdracht om de SQL tijdens runtime te genereren en uit te voeren. Om deze aanpak te gebruiken, moeten het gegevenstype en het aantal variabelen dat tijdens runtime wordt gebruikt, van tevoren bekend zijn. Het biedt ook betere prestaties en een lagere complexiteit in vergelijking met DBMS_SQL.

Syntaxis

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

dynamische_sql_string: Een tekenreeksuitdrukking (VARCHAR2 of CHAR, niet NVARCHAR2/NCHAR) die één SQL-instructie of PL/SQL-blok bevat.
INTO-clausule: Optioneel. Wordt alleen gebruikt wanneer de dynamische SQL een SELECT-query op één rij betreft; de geretourneerde waarden worden opgeslagen in variabelen of een record. Elke geselecteerde kolom heeft een variabele van een compatibel gegevenstype nodig.
Gebruiksclausule: Optioneel. Levert bindvariabelen. De standaardmodus is IN; OUT en IN OUT worden gebruikt om waarden terug te ontvangen.
TERUGKEER IN de clausule: Wordt gebruikt met DML-instructies die een RETURNING-clausule bevatten, om de waarden van de betreffende rij vast te leggen in bindargumenten.

Voorbeeld 1: In dit voorbeeld halen we de gegevens uit de tabel 'emp' op voor 'emp_no' '1001' met behulp van een NDS-instructie met een bindvariabele.

DECLARE
   lv_sql       VARCHAR2(500);
   lv_emp_name  VARCHAR2(50);
   ln_emp_no    NUMBER;
   ln_salary    NUMBER;
   ln_manager   NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   EXECUTE IMMEDIATE lv_sql
      INTO lv_emp_name, ln_emp_no, ln_salary, ln_manager
      USING 1001;
   DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
   DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
   DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
   DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
END;
/

uitgang

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Uitleg:

Regels 2-6: De variabelen declareren.
Regel 8: De SQL-query wordt tijdens de uitvoering geformuleerd. De SQL-query bevat de bindvariabele ':empno' in de WHERE-voorwaarde.
Regels 9-11: De ingekaderde SQL-query wordt uitgevoerd met EXECUTE IMMEDIATE. De variabelen in de INTO-clausule bevatten de opgehaalde waarden en de USING-clausule levert de waarde voor de bindvariabele :empno.
Regels 12-15: De opgehaalde waarden weergeven.

Dynamische SQL gebruiken voor DDL

Statische PL/SQL kan geen DDL-instructies zoals CREATE, ALTER of DROP rechtstreeks uitvoeren. EXECUTE IMMEDIATE lost dit op door de instructie als een tekenreeks op te bouwen, wat ook handig is wanneer een objectnaam tijdens de uitvoering wordt opgegeven:

DECLARE
   l_table_name VARCHAR2(30) := 'my_table';
   l_sql_stmt   VARCHAR2(200);
BEGIN
   l_sql_stmt := 'CREATE TABLE ' || l_table_name ||
                 ' (id NUMBER, name VARCHAR2(30))';
   EXECUTE IMMEDIATE l_sql_stmt;
END;
/

Objectnamen (tabel, kolom, schema) kunnen niet als bindvariabelen worden doorgegeven, dus moeten ze in de tekenreeks worden samengevoegd. Valideer dergelijke invoer altijd, bijvoorbeeld met DBMS_ASSERT.SIMPLE_SQL_NAME, om SQL-injectie te voorkomen.

DBMS_SQL voor dynamische SQL

PL/SQL biedt het DBMS_SQL-pakket voor het werken met dynamische SQL, waarbij de structuur van de instructie pas tijdens de uitvoering bekend is. Het proces voor het creëren en uitvoeren van dynamische SQL omvat de volgende stappen:

OPEN CURSOR: De dynamische SQL wordt uitgevoerd als een cursorOm de SQL-instructie uit te voeren, moeten we eerst de cursor openen.
SQL parseren: Analyseer de dynamische SQL. Dit controleert de syntaxis en zorgt ervoor dat de query klaar is voor uitvoering.
BIND VARIABELE Waarden: Wijs waarden toe aan de bindvariabelen, indien aanwezig.
KOLOM DEFINIËREN: Definieer elke kolom aan de hand van de relatieve positie in de SELECT-instructie.
UITVOEREN: Voer de geparseerde query uit.
WAARDEN OPHALEN: Haal de uitgevoerde waarden op.
SLUIT CURSOR: Zodra de resultaten zijn opgehaald, sluit u de cursor.

Voorbeeld 1: In dit voorbeeld halen we de gegevens uit de tabel 'emp' op voor 'emp_no' '1001' met behulp van een DBMS_SQL-instructie. Het EXCEPTION-blok sluit de cursor, zelfs als er een fout optreedt.

DECLARE
   lv_sql            VARCHAR2(500);
   lv_emp_name       VARCHAR2(50);
   ln_emp_no         NUMBER;
   ln_salary         NUMBER;
   ln_manager        NUMBER;
   ln_cursor_id      NUMBER;
   ln_rows_processed NUMBER;
BEGIN
   lv_sql := 'SELECT emp_name, emp_no, salary, manager FROM emp WHERE emp_no = :empno';
   ln_cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(ln_cursor_id, lv_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(ln_cursor_id, ':empno', 1001);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 1, lv_emp_name, 50);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 2, ln_emp_no);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 3, ln_salary);
   DBMS_SQL.DEFINE_COLUMN(ln_cursor_id, 4, ln_manager);
   ln_rows_processed := DBMS_SQL.EXECUTE(ln_cursor_id);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(ln_cursor_id) = 0 THEN
         EXIT;
      ELSE
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 1, lv_emp_name);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 2, ln_emp_no);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 3, ln_salary);
         DBMS_SQL.COLUMN_VALUE(ln_cursor_id, 4, ln_manager);
         DBMS_OUTPUT.PUT_LINE('Employee Name: '   || lv_emp_name);
         DBMS_OUTPUT.PUT_LINE('Employee Number: ' || ln_emp_no);
         DBMS_OUTPUT.PUT_LINE('Salary: '          || ln_salary);
         DBMS_OUTPUT.PUT_LINE('Manager ID: '      || ln_manager);
      END IF;
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_SQL.CLOSE_CURSOR(ln_cursor_id);
END;
/

uitgang

Employee Name  : XXX
Employee Number: 1001
Salary         : 15000
Manager ID     : 1000

Code Uitleg:

Regels 1-8: Variabele declaratie.
Regel 10: Het opstellen van de SQL-instructie.
Regel 11: De cursor wordt geopend met DBMS_SQL.OPEN_CURSOR, wat de ID van de geopende cursor retourneert.
Regel 12: Nadat de cursor is geopend, wordt de SQL-query geparseerd.
Regel 13: De bindwaarde '1001' wordt toegewezen in plaats van ':empno'.
Regels 14-17: De kolommen worden gedefinieerd aan de hand van hun relatieve positie: (1) emp_name, (2) emp_no, (3) salary, (4) manager.
Regel 18: De query wordt uitgevoerd met DBMS_SQL.EXECUTE, wat het aantal verwerkte records retourneert.
Regels 19-32: De records worden in een lus opgehaald. FETCH_ROWS retourneert 0 wanneer er geen rijen meer over zijn, waarmee de lus wordt verlaten.
UITZONDERINGSblok: Zorgt ervoor dat de cursor wordt gesloten, zodat open cursors geen gegevens lekken als er een fout optreedt.

NDS versus DBMS_SQL: wanneer gebruik je welke?

Beide benaderingen voeren SQL-query's uit tijdens de uitvoering, maar ze zijn geschikt voor verschillende situaties:

Gebruik native dynamische SQL (EXECUTE IMMEDIATE / OPEN-FOR) Wanneer het aantal en de gegevenstypen van de invoer en uitvoer tijdens het compileren bekend zijn, is dit sneller, gemakkelijker te lezen en vereist het minder code.
Gebruik DBMS_SQL Wanneer de structuur pas tijdens de uitvoering bekend wordt, bijvoorbeeld bij een query waarvan het aantal geselecteerde kolommen of bindvariabelen varieert (bekend als dynamische SQL volgens methode 4) of een statement dat te groot is om in één enkele 32K VARCHAR2-variabele te passen.

Veelgestelde vragen

Bindvariabelen geven gebruikersinvoer door als data, nooit als uitvoerbare code. De USING-clausule levert waarden positioneel, waardoor kwaadaardige tekst de structuur van de instructie niet kan wijzigen. Bind onbetrouwbare invoer altijd in plaats van deze samen te voegen.

Nee. Oracle Bindt alleen datawaarden, niet objectnamen. Voeg identificatoren samen in de SQL-string en valideer ze met DBMS_ASSERT.SIMPLE_SQL_NAME om injectie te voorkomen.

EXECUTE IMMEDIATE haalt slechts één rij op. Voor meerdere rijen opent u een REF CURSOR met de OPEN-FOR-instructie, doorloopt u vervolgens FETCH totdat %NOTFOUND wordt gevonden en sluit u de cursor.

Voeg een RETURNING-clausule toe aan de INSERT-, UPDATE- of DELETE-instructie en gebruik vervolgens de RETURNING INTO-clausule van EXECUTE IMMEDIATE om de waarden van de betreffende rij vast te leggen in bindargumenten.

Dynamische SQL brengt extra verwerkingskosten met zich mee, omdat de statements tijdens de uitvoering worden gecompileerd. Door bindvariabelen opnieuw te gebruiken, Oracle Deel cursors en verminder complexe parses, keeping De prestaties liggen dicht bij die van statische SQL.

De tekenreeks moet van het type VARCHAR2 of CHAR zijn. Nationale tekentypen zoals NVARCHAR2 en NCHAR zijn niet toegestaan. Voor tekst groter dan 32 KB accepteert DBMS_SQL een verzameling VARCHAR2-fragmenten.

Ja. AI-assistenten zoals GitHub Copilot genereren EXECUTE IMMEDIATE- en DBMS_SQL-blokken op basis van eenvoudige prompts, suggereren placeholders voor bindvariabelen en leggen elke clausule uit, hoewel een ontwikkelaar de uitvoer nog steeds moet controleren.

Door AI aangedreven codescanners signaleren samengevoegde gebruikersinvoer en bevelen bindvariabelen of DBMS_ASSERT-controles aan. Ze markeren risicovolle patronen tijdens de beoordeling en helpen bij het oplossen van problemen.ping Teams sporen injectiefouten op vóór de implementatie.

Oracle PL/SQL dynamische SQL-zelfstudie: onmiddellijk uitvoeren en DBMS_SQL