Oracle PL/SQL Dynamic SQL Tutorial: Udfør øjeblikkelig & DBMS_SQL

Hvad er dynamisk SQL?

Dynamisk SQL er en programmeringsmetode til at generere og køre sætninger under kørsel. Det bruges hovedsageligt til at skrive de generelle og fleksible programmer, hvor SQL-sætningerne vil blive oprettet og eksekveret ved kørsel baseret på kravet.

Måder at skrive dynamisk SQL

PL/SQL giver to måder at skrive dynamisk SQL på

1. NDS – Native Dynamic SQL
2. DBMS_SQL

NDS (Native Dynamic SQL) – Udfør øjeblikkeligt

Native Dynamic SQL er den nemmere måde at skrive dynamisk SQL på. Den bruger kommandoen 'EXECUTE IMMEDIATE' til at oprette og udføre SQL'en under kørslen. Men for at bruge denne måde, skal datatypen og antallet af variable, der skal bruges på et kørselstidspunkt, være kendt før. Det giver også bedre ydeevne og mindre kompleksitet sammenlignet med DBMS_SQL.

Syntaks

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

• Ovenstående syntaks viser kommandoen EXECUTE IMMEDIATE.
• Klausul INTO er valgfri og bruges kun, hvis den dynamiske SQL indeholder en select-sætning, der henter værdier. Variabeltypen skal matche variabeltypen for select-sætningen.
• Klausul USING er valgfri og bruges kun, hvis den dynamiske SQL indeholder en bindevariabel.

Eksempel 1: I dette eksempel skal vi hente data fra emp-tabellen for emp_no '1001' ved hjælp af NDS-sætning.

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

Kodeforklaring:

• Kodelinje 2-6: Erklærer variable.
• Kodelinje 8: Framing af SQL'en under kørsel. SQL indeholder bindevariablen i where-betingelsen ':empno'.
• Kodelinje 9: Udførelse af den indrammede SQL-tekst (som udføres i kodelinje 8) ved hjælp af NDS-kommandoen 'EXECUTE IMMEDIATE'
• Variablerne i 'INTO'-klausulen (lv_emp_name, ln_emp_no, ln_salary, ln_manager) bruges til at holde de hentede værdier fra SQL-forespørgslen (emp_name, emp_no, salary, manager)
• 'USING'-udtrykket giver værdierne til bind-variablen i SQL-forespørgslen (:emp_no).
• Kodelinje 10-13: Viser de hentede værdier.

DBMS_SQL til dynamisk SQL

PL/SQL leverer DBMS_SQL-pakken, der giver dig mulighed for at arbejde med dynamisk SQL. Processen med at skabe og udføre den dynamiske SQL indeholder følgende proces.

• ÅBN MARKØR: Den dynamiske SQL vil køre på samme måde som en markøren. Så for at udføre SQL-sætningen skal vi åbne markøren.
• PARSE SQL: Næste trin er at parse den dynamiske SQL. Denne proces vil blot kontrollere syntaksen og holde forespørgslen klar til at udføres.
• BIND VARIABEL Værdier: Det næste trin er at tildele værdierne for eventuelle bindevariabler.
• DEFINER KOLONNE: Næste trin er at definere kolonnen ved hjælp af deres relative positioner i select-sætningen.
• EXECUTE: Næste trin er at udføre den parsede forespørgsel.
• HENT VÆRDIER: Næste trin er at hente de udførte værdier.
• LUK MARKØR: Når resultaterne er hentet, skal markøren lukkes.

Eksempel 1: I dette eksempel skal vi hente data fra emp-tabellen for emp_no '1001' ved hjælp af DBMS_SQL-sætning.

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

Kodeforklaring:

• Kodelinje 1-9: Variabel erklæring.
• Kodelinje 10: Indramning af SQL-sætningen.
• Kodelinje 11: Åbning af markøren ved hjælp af DBMS_SQL.OPEN_CURSOR. Det vil returnere det markør-id, som er åbnet.
• Kodelinje 12: Efter at markøren er åbnet, analyseres SQL.
• Kodelinje 13: Bind-variablen '1001' tildeler markør-id'et i stedet ':empno'.
• Kodelinje 14-17: Definition af kolonnenavnet baseret på deres relative position i SQL-sætningen. I vores tilfælde er den relative stilling (1) emp_name, (2) emp_no (3) løn (4) leder. Så baseret på denne position definerer vi målvariablen.
• Kodelinje 18: Udførelse af forespørgslen ved hjælp af DBMS_SQL.EXECUTE. Det returnerer antallet af behandlede poster.
• Kodelinje 19-33: Henter posterne ved hjælp af en loop og viser det samme.
• Kodelinje 20: DBMS_SQL.FETCH_ROWS vil hente én post fra de behandlede rækker. Det kan kaldes gentagne gange for at hente alle rækkerne. Hvis den ikke kan hente rækker, vil den returnere 0 og dermed forlade sløjfen.

Resumé

I dette afsnit har vi diskuteret dynamisk SQL og måderne til at udføre DYNAMISK SQL. Vi har også set de forskellige trin i eksekvering af den dynamiske SQL på begge måder. Vi har også set eksemplerne, hvor det samme scenarie håndteres på både NDS- og DBMS_SQL-måder for at udføre eksekvering under kørsel.