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

Vad är dynamisk SQL?

Dynamisk SQL är en programmeringsmetod för att generera och köra satser under körning. Det används främst för att skriva de allmänna och flexibla programmen där SQL-satserna kommer att skapas och exekveras under körning baserat på kravet.

Sätt att skriva dynamisk SQL

PL/SQL ger två sätt att skriva dynamisk SQL

1. NDS – Native Dynamic SQL
2. DBMS_SQL

NDS (Native Dynamic SQL) – Kör omedelbart

Native Dynamic SQL är det enklare sättet att skriva dynamisk SQL. Den använder kommandot 'EXECUTE IMMEDIATE' för att skapa och köra SQL under körning. Men för att använda det här sättet måste datatypen och antalet variabler som ska användas vid en körning vara kända tidigare. Det ger också bättre prestanda och mindre komplexitet jämfört med DBMS_SQL.

syntax

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]

• Ovanstående syntax visar kommandot EXECUTE IMMEDIATE.
• Klausul INTO är valfri och används endast om den dynamiska SQL-koden innehåller en select-sats som hämtar värden. Variabeltypen ska matcha variabeltypen för select-satsen.
• Klausul USING är valfri och används endast om den dynamiska SQL-koden innehåller någon bindningsvariabel.

Exempelvis 1: I det här exemplet kommer vi att hämta data från emp-tabellen för emp_no '1001' med hjälp av NDS-satsen.

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
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_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

Produktion

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

Kodförklaring:

• Kodrad 2-6: Deklarerar variabler.
• Kodrad 8: Ramar in SQL vid körning. SQL innehåller bindningsvariabeln i where-villkoret ':empno'.
• Kodrad 9: Exekvera den inramade SQL-texten (vilket görs i kodrad 8) med NDS-kommandot 'EXECUTE IMMEDIATE'
• Variablerna i 'INTO'-satsen (lv_emp_name, ln_emp_no, ln_salary, ln_manager) används för att hålla de hämtade värdena från SQL-frågan (emp_name, emp_no, salary, manager)
• 'USING'-satsen ger värdena till bindningsvariabeln i SQL-frågan (:emp_no).
• Kodrad 10-13: Visar de hämtade värdena.

DBMS_SQL för dynamisk SQL

PL/SQL tillhandahåller DBMS_SQL-paketet som låter dig arbeta med dynamisk SQL. Processen att skapa och exekvera den dynamiska SQL-koden innehåller följande process.

• ÖPPNA MARKören: Dynamisk SQL kommer att köras på samma sätt som en markören. Så för att kunna köra SQL-satsen måste vi öppna markören.
• PARSE SQL: Nästa steg är att analysera dynamisk SQL. Denna process kommer bara att kontrollera syntaxen och hålla frågan redo att köras.
• BIND VARIABEL Värden: Nästa steg är att tilldela värdena för bindningsvariabler om några.
• DEFINIERA KOLUMN: Nästa steg är att definiera kolumnen med hjälp av deras relativa positioner i select-satsen.
• Utförande: Nästa steg är att köra den analyserade frågan.
• HÄMTA VÄRDEN: Nästa steg är att hämta de exekverade värdena.
• STÄNG MARKÖR: När resultaten har hämtats ska markören stängas.

Exempelvis 1: I det här exemplet kommer vi att hämta data från emp-tabellen för emp_no '1001' med hjälp av DBMS_SQL-satsen.

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;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
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,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||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_ClIRSOR(ln_cursor_id);

END:
/

Produktion

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

Kodförklaring:

• Kodrad 1-9: Variabel deklaration.
• Kodrad 10: Ramar in SQL-satsen.
• Kodrad 11: Öppna markören med DBMS_SQL.OPEN_CURSOR. Det kommer att returnera markör-id som öppnas.
• Kodrad 12: Efter att markören har öppnats analyseras SQL.
• Kodrad 13: Bindvariabel '1001' tilldelar markör-id istället ':empno'.
• Kodrad 14-17: Definiera kolumnnamnet baserat på deras relativa position i SQL-satsen. I vårt fall är den relativa positionen (1) emp_name, (2) emp_no (3) lön (4) chef. Så baserat på denna position definierar vi målvariabeln.
• Kodrad 18: Kör frågan med DBMS_SQL.EXECUTE. Det returnerar antalet bearbetade poster.
• Kodrad 19-33: Hämta posterna med en loop och visa densamma.
• Kodrad 20: DBMS_SQL.FETCH_ROWS kommer att hämta en post från de rader som behandlas. Den kan anropas upprepade gånger för att hämta alla rader. Om den inte kan hämta rader kommer den att returnera 0, och därmed lämna slingan.

Sammanfattning

I det här avsnittet har vi diskuterat dynamisk SQL och sätten att exekvera DYNAMISK SQL. Vi har också sett de olika stegen i exekvering av dynamisk SQL på båda sätten. Vi har också sett exemplen där samma scenario hanteras på både NDS- och DBMS_SQL-sätt för att utföra exekvering vid körning.