Oracle PL/SQL Dynamisk SQL-opplæring: Utfør umiddelbart og DBMS_SQL
Fiona Brown
Hva er dynamisk SQL?
Dynamisk SQL er en programmeringsmetodikk for å generere og kjøre setninger under kjøring. Den brukes hovedsakelig til å skrive de generelle og fleksible programmene der SQL-setningene vil bli opprettet og utført under kjøring basert på kravet.
Måter å skrive dynamisk SQL
PL/SQL gir to måter å skrive dynamisk SQL på
- NDS – Native Dynamic SQL
- DBMS_SQL
NDS (Native Dynamic SQL) – Utfør umiddelbart
Native Dynamic SQL er den enklere måten å skrive dynamisk SQL på. Den bruker kommandoen 'EXECUTE IMMEDIATE' for å opprette og kjøre SQL-en under kjøring. Men for å bruke denne måten, må datatypen og antallet variabler som skal brukes på en kjøretid være kjent fra før. Det gir også bedre ytelse og mindre kompleksitet sammenlignet med DBMS_SQL.
syntax
EXECUTE IMMEDIATE(<SQL>) [INTO<variable>] [USING <bind_variable_value>]
- Syntaksen ovenfor viser kommandoen EXECUTE IMMEDIATE.
- Klausul INTO er valgfri og brukes bare hvis den dynamiske SQL-en inneholder en select-setning som henter verdier. Variabeltypen skal samsvare med variabeltypen til select-setningen.
- Klausul USING er valgfri og brukes bare hvis den dynamiske SQL-en inneholder en bindingsvariabel.
Eksempel 1: I dette eksemplet skal vi hente dataene fra emp-tabellen for emp_no '1001' ved å bruke NDS-setningen.
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;
/
Produksjon
Employee Name : XXX Employee Number: 1001 Salary: 15000 Manager ED: 1000
Kodeforklaring:
- Kodelinje 2-6: Deklarerer variabler.
- Kodelinje 8: Innramming av SQL ved kjøring. SQL inneholder bindingsvariabelen i where-betingelsen ':empno'.
- Kodelinje 9: Utføre den innrammede SQL-teksten (som gjøres i kodelinje 8) ved å bruke NDS-kommandoen 'EXECUTE IMMEDIATE'
- Variablene i 'INTO'-klausulen (lv_emp_name, ln_emp_no, ln_salary, ln_manager) brukes til å holde de hentede verdiene fra SQL-spørringen (emp_name, emp_no, salary, manager)
- 'USING'-leddet gir verdiene til bindingsvariabelen i SQL-spørringen (:emp_no).
- Kodelinje 10-13: Viser de hentede verdiene.
DBMS_SQL for dynamisk SQL
PL/SQL gir DBMS_SQL-pakken som lar deg jobbe med dynamisk SQL. Prosessen med å lage og utføre den dynamiske SQL-en inneholder følgende prosess.
- ÅPNE MARKØR: Den dynamiske SQL-en kjøres på samme måte som en markør. Så for å utføre SQL-setningen, må vi åpne markøren.
- PARSE SQL: Neste trinn er å analysere den dynamiske SQL-en. Denne prosessen vil bare sjekke syntaksen og holde spørringen klar til å utføres.
- BIND VARIABEL Verdier: Neste trinn er å tilordne verdiene for bindingsvariabler hvis noen.
- DEFINER KOLONNE: Neste trinn er å definere kolonnen ved å bruke deres relative posisjoner i select-setningen.
- HENRETTE: Neste trinn er å utføre den analyserte spørringen.
- HENT VERDIER: Neste trinn er å hente de utførte verdiene.
- LUKK MARKØR: Når resultatene er hentet, skal markøren lukkes.
Eksempel 1: I dette eksemplet skal vi hente dataene fra emp-tabellen for emp_no '1001' ved å bruke DBMS_SQL-setningen.
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:
/
Produksjon
Employee Name:XXX Employee Number:1001 Salary:15000 Manager ID:1000
Kodeforklaring:
- Kodelinje 1-9: Variabel erklæring.
- Kodelinje 10: Innramming av SQL-setningen.
- Kodelinje 11: Åpne markøren med DBMS_SQL.OPEN_CURSOR. Det vil returnere markør-ID-en som er åpnet.
- Kodelinje 12: Etter at markøren er åpnet, analyseres SQL-en.
- Kodelinje 13: Bindingsvariabel '1001' tilordner markør-ID-en i stedet ':empno'.
- Kodelinje 14-17: Definerer kolonnenavnet basert på deres relative plassering i SQL-setningen. I vårt tilfelle er den relative stillingen (1) emp_name, (2) emp_no (3) lønn (4) leder. Så basert på denne posisjonen definerer vi målvariabelen.
- Kodelinje 18: Utfører spørringen ved hjelp av DBMS_SQL.EXECUTE. Den returnerer antall behandlede poster.
- Kodelinje 19-33: Henter postene ved hjelp av en loop og viser de samme.
- Kodelinje 20: DBMS_SQL.FETCH_ROWS vil hente én post fra radene som behandles. Det kan kalles gjentatte ganger for å hente alle radene. Hvis den ikke kan hente rader, vil den returnere 0, og dermed gå ut av loopen.
Sammendrag
I denne delen har vi diskutert dynamisk SQL og måtene å utføre DYNAMISK SQL på. Vi har også sett de forskjellige trinnene i å utføre dynamisk SQL på begge måter. Vi har også sett eksemplene der samme scenario håndteres på både NDS- og DBMS_SQL-måter for å utføre kjøring under kjøring.
