Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate & DBMS_SQL

Vad är dynamisk SQL?

Dynamisk SQL är en programmeringsmetodik för att generera och köra satser vid körning. Den används huvudsakligen för att skriva generella och flexibla program där SQL-satser skapas och körs vid körning baserat på kravet, till exempel när tabellnamn, kolumnlistor eller WHERE-villkor inte är kända förrän programmet körs.

Sätt att skriva dynamisk SQL

PL/SQL erbjuder två sätt att skriva dynamisk SQL:

1. NDS – Native Dynamic SQL (satserna EXECUTE IMMEDIATE och OPEN-FOR)
2. DBMS_SQL (ett medföljande paket)

Den allmänna regeln är enkel: om antalet och datatyperna för in- och utdatavariablerna är kända vid kompileringstillfället, använd Native Dynamic SQL eftersom det är snabbare och kräver mindre kod. När den informationen bara är känd vid körning, använd DBMS_SQL-paketet.

NDS (Native Dynamic SQL) – Kör omedelbart

Native Dynamic SQL är det enklaste sättet att skriva dynamisk SQL. Det använder kommandot EXECUTE IMMEDIATE för att skapa och köra SQL:en vid körning. För att använda den här metoden måste datatypen och antalet variabler som används vid körning vara kända i förväg. Det ger också bättre prestanda och lägre komplexitet jämfört med DBMS_SQL.

syntax

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

• dynamisk_sql_sträng: Ett stränguttryck (VARCHAR2 eller CHAR, inte NVARCHAR2/NCHAR) som innehåller en enda SQL-sats eller ett PL/SQL-block.
• INTO-klausul: Valfritt. Används endast när den dynamiska SQL-en är en SELECT-kod med en rad; den samlar in de returnerade värdena i variabler eller en post. Varje vald kolumn behöver en typkompatibel variabel.
• ANVÄNDNINGSKLAUSUL: Valfritt. Tillhandahåller bindningsvariabler. Standardläget är IN; OUT och IN OUT används för att ta emot värden tillbaka.
• ÅTERVÄNDNINGSKLAUSUL: Används med DML-satser som innehåller en RETURNING-klausul för att fånga upp berörda radvärden i bindningsargument.

Exempel 1: I det här exemplet hämtar vi data från emp-tabellen för emp_no '1001' med hjälp av en NDS-sats med en bind-variabel.

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;
/

Produktion

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

Code Förklaring:

• Raderna 2–6: Deklarera variablerna.
• Linje 8: Ramar in SQL-koden vid körning. SQL-koden innehåller bindningsvariabeln ':empno' i WHERE-villkoret.
• Raderna 9–11: Kör den inramade SQL-kommandot med EXECUTE IMMEDIATE. INTO-klausulvariablerna innehåller de hämtade värdena, och USING-klausulen anger värdet för bindningsvariabeln :empno.
• Raderna 12–15: Visar de hämtade värdena.

Använda dynamisk SQL för DDL

Statisk PL/SQL kan inte köra DDL som CREATE, ALTER eller DROP direkt. EXECUTE IMMEDIATE löser detta genom att skapa kommandot som en sträng, vilket också är praktiskt när ett objektnamn anges vid körning:

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;
/

Objektnamn (tabell, kolumn, schema) kan inte skickas som bindningsvariabler, så de måste sammanfogas till strängen. Validera alltid sådan inmatning, till exempel med DBMS_ASSERT.SIMPLE_SQL_NAME, för att undvika SQL-injektion.

DBMS_SQL för dynamisk SQL

PL/SQL tillhandahåller DBMS_SQL-paketet för att arbeta med dynamisk SQL när satsens struktur inte är känd förrän vid körning. Processen att skapa och köra den dynamiska SQL-en innefattar följande steg:

• ÖPPNA MARKÖR: Den dynamiska SQL-funktionen körs som en markörenFör att köra SQL-satsen måste vi först öppna markören.
• PARSE SQL: Parsa den dynamiska SQL-filen. Detta kontrollerar syntaxen och håller frågan redo att köras.
• BINDVARIABEL Värden: Tilldela värdena för bindningsvariablerna, om några.
• DEFINERA KOLUMN: Definiera varje kolumn med hjälp av dess relativa position i select-satsen.
• UTFÖRA: Kör den analyserade frågan.
• HÄMTA VÄRDEN: Hämta de exekverade värdena.
• STÄNG MARKÖREN: När resultaten har hämtats, stäng markören.

Exempel 1: I det här exemplet hämtar vi data från emp-tabellen för emp_no '1001' med hjälp av en DBMS_SQL-sats. EXCEPTION-blocket stänger markören även om ett fel uppstår.

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;
/

Produktion

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

Code Förklaring:

• Raderna 1–8: Variabeldeklaration.
• Linje 10: Rama in SQL-satsen.
• Linje 11: Öppna markören med DBMS_SQL.OPEN_CURSOR, vilket returnerar ID:t för den öppnade markören.
• Linje 12: Efter att markören har öppnats analyseras SQL-koden.
• Linje 13: Bindningsvärdet '1001' tilldelas istället för ':empno'.
• Raderna 14–17: Definiera kolumnerna efter deras relativa position: (1) anställningsnamn, (2) anställningsnr, (3) lön, (4) chef.
• Linje 18: Kör frågan med DBMS_SQL.EXECUTE, vilket returnerar antalet bearbetade poster.
• Raderna 19–32: Hämtar posterna i en loop. FETCH_ROWS returnerar 0 när inga rader finns kvar, vilket avslutar loopen.
• UNDANTAGSblock: Säkerställer att markören är stängd så att öppna markörer inte läcker om ett fel uppstår.

NDS vs DBMS_SQL: När ska man använda vilket

Båda metoderna kör SQL vid körning, men de passar olika situationer:

• Använd nativ dynamisk SQL (EXEKTERA OMEDELBART / ÖPPNA FÖR) när antalet och datatyperna för in- och utdata är kända vid kompileringstillfället. Det är snabbare, lättare att läsa och kräver mindre kod.
• Använd DBMS_SQL när strukturen är okänd fram till körning, till exempel en fråga vars antalet valda kolumner eller bindningsvariabler varierar, så kallad dynamisk SQL för metod 4, eller ett uttalande som är för stort för att få plats i en enda VARCHAR2-variabel på 32K.

Vanliga frågor