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

Innehållsförteckning:

Anonim

Vad är Dynamic SQL?

Dynamic SQL är en programmeringsmetod för att generera och köra uttalanden vid körning. Det används huvudsakligen för att skriva allmänna och flexibla program där SQL-uttalanden skapas och körs vid körning baserat på kravet.

I den här handledningen lär du dig-

  • Sätt att skriva dynamisk SQL
  • NDS (Native Dynamic SQL) - Kör omedelbart
  • DBMS_SQL för dynamisk SQL

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 enklaste sättet att skriva dynamisk SQL. Den använder kommandot 'EXECUTE IMMEDIATE' för att skapa och köra SQL vid körning. Men för att använda detta sätt måste datatypen och antalet variabler som ska användas vid körning vara kända innan. Det ger också bättre prestanda och mindre komplexitet jämfört med DBMS_SQL.

Syntax 

EXECUTE IMMEDIATE()[INTO][USING ]
  • Ovanstående syntax visar kommandot EXECUTE IMMEDIATE.
  • Klausul INTO är valfri och används endast om den dynamiska SQL 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 innehåller någon bindningsvariabel.

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

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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 : XXXEmployee Number: 1001Salary: 15000Manager ED: 1000

Kodförklaring:

  • Kodrad 2-6 : Deklarerande variabler.
  • Kodrad 8 : Inramning av SQL vid körning. SQL innehåller bindningsvariabeln där villkoret ': empno'.
  • Kodrad 9 : Exekvera den inramade SQL-texten (som 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, lön, manager)
  • 'USING' -satsen ger värdena till bindningsvariabeln i SQL-frågan (: emp_no).
  • Kodrad 10-13 : Visar hämtade värden.

DBMS_SQL för dynamisk SQL

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

  • ÖPPEN PIL : Den dynamiska SQL körs på samma sätt som en markör. Så för att köra SQL-uttalandet 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 VARIABLE Värden : Nästa steg är att tilldela värden för bindningsvariabler om sådana finns.
  • DEFINIERA KOLONN : Nästa steg är att definiera kolumnen med hjälp av deras relativa positioner i valsatsen.
  • KÖR : Nästa steg är att köra den analyserade frågan.
  • Hämta värden : Nästa steg är att hämta de körda värdena.
  • STÄNG PILAR : När resultaten har hämtats bör markören stängas.

Exempel 1 : I det här exemplet ska vi hämta data från emp-tabellen för emp_no '1001' med DBMS_SQL-uttalande.

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:XXXEmployee Number:1001Salary:15000Manager ID:1000

Kodförklaring:

  • Kodrad 1-9 : Variabel deklaration.
  • Kodrad 10 : Inrama SQL-satsen.
  • Kodrad 11 : Öppna markören med DBMS_SQL.OPEN_CURSOR. Det returnerar markör-id som öppnas.
  • Kodrad 12 : När markören har öppnats analyseras SQL.
  • Kodrad 13 : Bindningsvariabeln '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) manager. Så baserat på denna position definierar vi målvariabeln.
  • Kodrad 18 : Utföra frågan med DBMS_SQL.EXECUTE. Antalet behandlade poster returneras.
  • Kodrad 19-33 : Hämta posterna med en slinga och visa samma.
  • Kodrad 20: DBMS_SQL.FETCH_ROWS hämtar en post från de bearbetade raderna. Det kan kallas 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 avslutas slingan.

Sammanfattning

I det här avsnittet har vi diskuterat dynamisk SQL och sätten att köra DYNAMIC SQL. Vi har också sett de olika stegen i att köra 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 körning vid körning.