Oracle PL/SQL Dynamisk SQL-opplæring: Utfør umiddelbart og DBMS_SQL

Hva er dynamisk SQL?

Dynamisk SQL er en programmeringsmetode for å generere og kjøre setninger under kjøretid. Den brukes hovedsakelig til å skrive generelle og fleksible programmer der SQL-setningene opprettes og kjøres under kjøretid basert på kravet, for eksempel når tabellnavn, kolonnelister eller WHERE-betingelser ikke er kjent før programmet kjører.

Måter å skrive dynamisk SQL på

PL/SQL tilbyr to måter å skrive dynamisk SQL på:

1. NDS – Native Dynamic SQL (EXECUTE IMMEDIATE- og OPEN-FOR-setningene)
2. DBMS_SQL (en medfølgende pakke)

Den generelle regelen er enkel: hvis antallet og datatypene for input- og outputvariablene er kjent ved kompileringstid, bruk Native Dynamic SQL fordi det er raskere og krever mindre kode. Når denne informasjonen bare er kjent ved kjøretid, bruk DBMS_SQL-pakken.

NDS (Native Dynamic SQL) – Utfør umiddelbart

Native Dynamic SQL er den enkleste måten å skrive dynamisk SQL på. Den bruker EXECUTE IMMEDIATE-kommandoen til å opprette og kjøre SQL-en under kjøretid. For å bruke denne tilnærmingen må datatypen og antallet variabler som brukes under kjøretid være kjent på forhånd. Den gir også bedre ytelse og lavere kompleksitet sammenlignet 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_streng: Et strenguttrykk (VARCHAR2 eller CHAR, ikke NVARCHAR2/NCHAR) som inneholder en enkelt SQL-setning eller PL/SQL-blokk.
• INTO-klausul: Valgfritt. Brukes kun når den dynamiske SQL-en er en SELECT-verdi med én rad. Den registrerer de returnerte verdiene i variabler eller en post. Hver valgte kolonne trenger en typekompatibel variabel.
• BRUKER klausulen: Valgfritt. Forsyner binder variabler. Standardmodusen er IN; OUT og IN OUT brukes til å motta verdier tilbake.
• RETURNING INTO-klausul: Brukes med DML-setninger som har en RETURNING-klausul, for å fange opp berørte radverdier i bindingsargumenter.

Eksempel 1: I dette eksemplet henter vi dataene fra emp-tabellen for emp_no '1001' ved hjelp av en NDS-setning 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;
/

Produksjon

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

Code Forklaring:

• Linje 2–6: Deklarering av variablene.
• Linje 8: Setter SQL-en i en ramme under kjøretid. SQL-en inneholder bindingsvariabelen ':empno' i WHERE-betingelsen.
• Linje 9–11: Utfører den innrammede SQL-en med EXECUTE IMMEDIATE. INTO-klausulvariablene inneholder de hentede verdiene, og USING-klausulen angir verdien for bindingsvariabelen :empno.
• Linje 12–15: Viser de hentede verdiene.

Bruk av dynamisk SQL for DDL

Statisk PL/SQL kan ikke kjøre DDL som CREATE, ALTER eller DROP direkte. EXECUTE IMMEDIATE løser dette ved å bygge setningen som en streng, noe som også er nyttig når et objektnavn oppgis under kjøretid:

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

Objektnavn (tabell, kolonne, skjema) kan ikke sendes som bindingsvariabler, så de må sammenkobles i strengen. Valider alltid slike inndata, for eksempel med DBMS_ASSERT.SIMPLE_SQL_NAME, for å unngå SQL-injeksjon.

DBMS_SQL for dynamisk SQL

PL/SQL tilbyr DBMS_SQL-pakken for arbeid med dynamisk SQL når strukturen til setningen ikke er kjent før kjøretid. Prosessen med å opprette og kjøre den dynamiske SQL-en involverer følgende trinn:

• ÅPNE MARKØR: Den dynamiske SQL-en kjøres som en markørFor å kjøre SQL-setningen må vi først åpne markøren.
• PARSE SQL: Parser den dynamiske SQL-en. Dette kontrollerer syntaksen og holder spørringen klar til utførelse.
• BIND VARIABLE Verdier: Tilordne verdiene for bindingsvariablene, hvis noen.
• DEFINÉR KOLONNE: Definer hver kolonne ved å bruke dens relative posisjon i SELECT-setningen.
• HENRETTE: Kjør den analyserte spørringen.
• HENT VERDIER: Hent de utførte verdiene.
• LUKK MARKØR: Når resultatene er hentet, lukk markøren.

Eksempel 1: I dette eksemplet henter vi dataene fra emp-tabellen for emp_no '1001' ved hjelp av en DBMS_SQL-setning. EXCEPTION-blokken lukker markøren selv om det oppstår en feil.

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

Produksjon

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

Code Forklaring:

• Linje 1–8: Variabeldeklarasjon.
• Linje 10: Innramming av SQL-setningen.
• Linje 11: Åpne markøren ved hjelp av DBMS_SQL.OPEN_CURSOR, som returnerer ID-en til den åpnede markøren.
• Linje 12: Etter at markøren er åpnet, blir SQL-en analysert.
• Linje 13: Bindingsverdien '1001' tildeles i stedet for ':empno'.
• Linje 14–17: Definer kolonnene etter deres relative posisjon: (1) ansattnavn, (2) ansattnr., (3) lønn, (4) leder.
• Linje 18: Utfører spørringen med DBMS_SQL.EXECUTE, som returnerer antall behandlede poster.
• Linje 19–32: Henter postene i en løkke. FETCH_ROWS returnerer 0 når det ikke er noen rader igjen, noe som avslutter løkken.
• UNNTAKSBLOKK: Sørger for at markøren er lukket, slik at åpne markører ikke lekker hvis det oppstår en feil.

NDS vs DBMS_SQL: Når skal man bruke hvilken

Begge tilnærmingene kjører SQL under kjøring, men de passer til forskjellige situasjoner:

• Bruk Native Dynamic SQL (UTFØR ØYEBLIKKELIG / ÅPNE FOR) når antallet og datatypene for input og output er kjent ved kompileringstidspunktet. Det er raskere, enklere å lese og krever mindre kode.
• Bruk DBMS_SQL når strukturen er ukjent frem til kjøretid, for eksempel en spørring der antallet valgte kolonner eller bindingsvariabler varierer, kjent som dynamisk SQL fra metode 4, eller en setning som er for stor til å passe i en enkelt VARCHAR2-variabel på 32K.

Spørsmål og svar