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

Hvad er dynamisk SQL?

Dynamisk SQL er en programmeringsmetode til generering og kørsel af sætninger under kørsel. Den bruges primært til at skrive generelle og fleksible programmer, hvor SQL-sætningerne oprettes og udføres under kørsel baseret på kravet, for eksempel når tabelnavne, kolonnelister eller WHERE-betingelser ikke er kendte, før programmet kører.

Måder at skrive dynamisk SQL på

PL/SQL tilbyder to måder at skrive dynamisk SQL på:

1. NDS – Native Dynamic SQL (UDFØR IMMEDIATE- og OPEN-FOR-sætningerne)
2. DBMS_SQL (en medfølgende pakke)

Den generelle regel er enkel: Hvis antallet og datatyperne for input- og outputvariablerne er kendte på kompileringstidspunktet, skal du bruge Native Dynamic SQL, fordi det er hurtigere og kræver mindre kode. Når disse oplysninger kun er kendte under kørsel, skal du bruge DBMS_SQL-pakken.

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

Native Dynamic SQL er den nemmeste måde at skrive dynamisk SQL på. Den bruger kommandoen EXECUTE IMMEDIATE til at oprette og udføre SQL'en under kørsel. For at bruge denne tilgang skal datatypen og antallet af variabler, der bruges under kørsel, være kendt på forhånd. Den giver også bedre ydeevne og lavere kompleksitet sammenlignet med DBMS_SQL.

Syntaks

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];

• dynamisk_sql_streng: Et strengudtryk (VARCHAR2 eller CHAR, ikke NVARCHAR2/NCHAR), der indeholder en enkelt SQL-sætning eller PL/SQL-blok.
• INTO-klausul: Valgfrit. Bruges kun, når den dynamiske SQL er en SELECT-værdi med én række; den registrerer de returnerede værdier i variabler eller en post. Hver valgt kolonne skal have en typekompatibel variabel.
• BRUGER af klausul: Valgfrit. Forsyner binder variabler. Standardtilstanden er IN; OUT og IN OUT bruges til at modtage værdier tilbage.
• RETURNING INTO-klausul: Bruges med DML-sætninger, der indeholder en RETURNING-klausul, til at indfange berørte rækkeværdier i bindingsargumenter.

Eksempel 1: I dette eksempel henter vi dataene fra emp-tabellen for emp_no '1001' ved hjælp af en NDS-sætning 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;
/

Produktion

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

Code Forklaring:

• Linje 2-6: Deklarering af variablerne.
• Linje 8: Indramning af SQL'en under kørsel. SQL'en indeholder bindvariablen ':empno' i WHERE-betingelsen.
• Linje 9-11: Udfører den indrammede SQL med EXECUTE IMMEDIATE. INTO-klausulvariablerne indeholder de hentede værdier, og USING-klausulen angiver værdien for bindevariablen :empno.
• Linje 12-15: Viser de hentede værdier.

Brug af dynamisk SQL til DDL

Statisk PL/SQL kan ikke køre DDL såsom CREATE, ALTER eller DROP direkte. EXECUTE IMMEDIATE løser dette ved at bygge sætningen som en streng, hvilket også er praktisk, når et objektnavn angives under kørsel:

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

Objektnavne (tabel, kolonne, skema) kan ikke overføres som bindvariabler, så de skal sammenkædes med strengen. Valider altid sådant input, for eksempel med DBMS_ASSERT.SIMPLE_SQL_NAME, for at undgå SQL-injektion.

DBMS_SQL til dynamisk SQL

PL/SQL leverer DBMS_SQL-pakken til at arbejde med dynamisk SQL, når strukturen af ​​​​sætningen ikke er kendt før kørselstid. Processen med at oprette og udføre den dynamiske SQL involverer følgende trin:

• ÅBN MARKØR: Den dynamiske SQL udføres som en markørenFor at udføre SQL-sætningen skal vi først åbne markøren.
• PARSE SQL: Parser den dynamiske SQL. Dette kontrollerer syntaksen og holder forespørgslen klar til udførelse.
• BIND VARIABEL Værdier: Tildel værdierne for bindingsvariablerne, hvis der er nogen.
• DEFINER KOLONNE: Definer hver kolonne ved hjælp af dens relative position i SELECT-sætningen.
• UDFØR: Udfør den parsede forespørgsel.
• HENT VÆRDIER: Hent de udførte værdier.
• LUK MARKØR: Når resultaterne er hentet, skal du lukke markøren.

Eksempel 1: I dette eksempel henter vi dataene fra emp-tabellen for emp_no '1001' ved hjælp af en DBMS_SQL-sætning. EXCEPTION-blokken lukker markøren, selvom der opstår en fejl.

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

Produktion

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

Code Forklaring:

• Linje 1-8: Variabeldeklaration.
• Linje 10: Indramning af SQL-sætningen.
• Linje 11: Åbning af markøren ved hjælp af DBMS_SQL.OPEN_CURSOR, som returnerer id'et for den åbnede markør.
• Linje 12: Når markøren er åbnet, parses SQL'en.
• Linje 13: Bindingsværdien '1001' tildeles i stedet for ':empno'.
• Linje 14-17: Definer kolonnerne efter deres relative position: (1) medarbejdernavn, (2) medarbejdernr., (3) løn, (4) leder.
• Linje 18: Udfører forespørgslen med DBMS_SQL.EXECUTE, som returnerer antallet af behandlede poster.
• Linje 19-32: Henter posterne i en løkke. FETCH_ROWS returnerer 0, når der ikke er nogen rækker tilbage, hvilket afslutter løkken.
• UNDTAGELSESblok: Sørger for, at markøren er lukket, så åbne markører ikke lækker, hvis der opstår en fejl.

NDS vs. DBMS_SQL: Hvornår skal man bruge hvilken

Begge tilgange kører SQL under kørsel, men de passer til forskellige situationer:

• Brug Native Dynamic SQL (Udfør øjeblikkeligt / Åbn for) når antallet og datatyperne for input og output er kendte på kompileringstidspunktet. Det er hurtigere, lettere at læse og kræver mindre kode.
• Brug DBMS_SQL når strukturen er ukendt indtil kørselstidspunktet, for eksempel en forespørgsel hvis antallet af valgte kolonner eller bindevariabler varierer, kendt som method-4 dynamisk SQL, eller en sætning, der er for stor til at passe i en enkelt 32K VARCHAR2-variabel.

Ofte Stillede Spørgsmål