Oracle PL/SQL dinamikus SQL oktatóanyag: Azonnali és DBMS_SQL végrehajtása

By: Fiona Brown Fiona Brown
Hours korszerűsített

⚡ Okos összefoglaló

Dinamikus SQL Oracle A PL/SQL futási időben épít és futtat utasításokat, a lekérdezéseket két megközelítéssel igazítva a változó követelményekhez: a natív dinamikus SQL EXECUTE IMMEDIATE és OPEN-FOR funkciókkal, valamint a rugalmas DBMS_SQL csomaggal összetett esetekre.

  • 🇧🇷 Futásidejű SQL: A dinamikus SQL utasításokat generál és hajt végre, ha a tábla- vagy oszlopnevek előre ismeretlenek.
  • ⚡ Natív dinamikus SQL: Az EXECUTE IMMEDIATE parancs a lehető legkevesebb kóddal gyorsan létrehozza és futtatja az SQL-t.
  • 🔁 NYITVA: Többsoros dinamikus lekérdezéseket kezel, amelyeket az EXECUTE IMMEDIATE önmagában nem tud lekérdezni.
  • 🧩 DBMS_SQL: Olyan utasításokhoz illik, amelyek oszlopszáma vagy típusa futásidőig ismeretlen.
  • 🔐 Kötési változók: A USING záradék pozicionálisan adja át az értékeket, és blokkolja az SQL injektálást.
  • 🤖 AI segítség: A mesterséges intelligencia eszközei dinamikus SQL-kódokat rajzolnak, és a felülvizsgálat során jelzik az injektálási kockázatokat.
További információk

Oracle PL/SQL dinamikus SQL oktatóanyag

Mi az a dinamikus SQL?

Dinamikus SQL egy programozási módszertan utasítások futásidejű létrehozására és futtatására. Főként általános célú és rugalmas programok írására használják, ahol az SQL utasítások futásidejű létrehozására és végrehajtására kerül sor a követelmények alapján, például amikor a táblanevek, oszloplisták vagy WHERE feltételek nem ismertek a program futása előtt.

Dinamikus SQL írásának módjai

A PL/SQL kétféleképpen írhat dinamikus SQL-t:

  1. NDS – Natív dinamikus SQL (az EXECUTE IMMEDIATE és az OPEN-FOR utasítások)
  2. DBMS_SQL (a mellékelt csomag)

Az általános szabály egyszerű: ha a bemeneti és kimeneti változók száma és adattípusa fordítási időben ismert, akkor használd a Native Dynamic SQL-t, mert gyorsabb és kevesebb kódot igényel. Ha ez az információ csak futási időben ismert, akkor használd a DBMS_SQL csomagot.

NDS (Native Dynamic SQL) – Azonnali végrehajtás

A natív dinamikus SQL a dinamikus SQL írásának egyszerűbb módja. Az EXECUTE IMMEDIATE parancsot használja az SQL futásidejű létrehozásához és végrehajtásához. Ennek a megközelítésnek a használatához előre ismerni kell a futásidejű változók adattípusát és számát. Emellett jobb teljesítményt és alacsonyabb komplexitást is biztosít a DBMS_SQL-hez képest.

Szintaxis

EXECUTE IMMEDIATE dynamic_sql_string
[INTO {variable[, variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, ...]]
[RETURNING INTO bind_argument[, ...]];
  • dinamikus_sql_karakterlánc: Egyetlen SQL utasítást vagy PL/SQL blokkot tartalmazó karakterlánc-kifejezés (VARCHAR2 vagy CHAR, nem NVARCHAR2/NCHAR).
  • INTO záradék: Opcionális. Csak akkor használatos, ha a dinamikus SQL egysoros SELECT; a visszaadott értékeket változókba vagy egy rekordba rögzíti. Minden kiválasztott oszlophoz típuskompatibilis változó szükséges.
  • USING záradék: Opcionális. Kötőváltozókat biztosít. Az alapértelmezett mód az IN; az OUT és az IN OUT módok szolgálnak az értékek visszaküldésére.
  • VISSZATÉRÉS BE záradék: RETURNING záradékot tartalmazó DML utasításokkal használatos az érintett sorok értékeinek kötési argumentumokba való rögzítéséhez.

Példa 1: Ebben a példában az emp_no '1001' emp táblából egy NDS utasítás és egy bind változó használatával kérdezzük le az adatokat.

NDS – Azonnali végrehajtás

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

teljesítmény

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

Code Magyarázat:

  • 2–6. sorok: A változók deklarálása.
  • 8 vonal: Az SQL keretezése futásidőben. Az SQL tartalmazza a ':empno' kötési változót a WHERE feltételben.
  • 9–11. sorok: A keretezett SQL végrehajtása az EXECUTE IMMEDIATE paranccsal. Az INTO záradék változói a beolvasott értékeket tartalmazzák, az USING záradék pedig a kötési változó (:empno) értékét adja meg.
  • 12–15. sorok: A beolvasott értékek megjelenítése.

KAPCSOLÓDÓ CIKKEK

Dinamikus SQL használata DDL-hez

A statikus PL/SQL nem tud közvetlenül DDL-t futtatni, mint például a CREATE, ALTER vagy DROP. Az EXECUTE IMMEDIATE ezt úgy oldja meg, hogy az utasítást karakterláncként építi fel, ami akkor is hasznos, ha futásidőben megadunk egy objektumnevet:

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

Az objektumnevek (tábla, oszlop, séma) nem adhatók át kötési változóként, ezért azokat össze kell fűzni a karakterlánccá. Az ilyen bemeneteket mindig érvényesíteni kell, például a DBMS_ASSERT.SIMPLE_SQL_NAME paraméterrel, az SQL injektálás elkerülése érdekében.

DBMS_SQL dinamikus SQL-hez

A PL/SQL biztosítja a DBMS_SQL csomagot a dinamikus SQL használatához, amikor az utasítás szerkezete futásidőig nem ismert. A dinamikus SQL létrehozásának és végrehajtásának folyamata a következő lépésekből áll:

  • KURZOR MEGNYITÁSA: A dinamikus SQL úgy fut, mint egy kurzorAz SQL utasítás végrehajtásához először meg kell nyitnunk a kurzort.
  • SQL-elemzés: Elemzi a dinamikus SQL-t. Ez ellenőrzi a szintaxist, és készenlétben tartja a lekérdezést a végrehajtásra.
  • BIND VÁLTOZÓ Értékek: Adja meg a bind változók értékeit, ha vannak ilyenek.
  • OSZLOP MEGHATÁROZÁSA: Definiálja az egyes oszlopokat a SELECT utasításban elfoglalt relatív pozíciójukkal.
  • VÉGREHAJTÁS: Hajtsa végre az elemzett lekérdezést.
  • ÉRTÉKEK LEKÉRÉSE: A végrehajtott értékek lekérése.
  • KURZOR BEZÁRÁSA: Miután a program lekérte az eredményeket, zárja be a kurzort.

Példa 1: Ebben a példában az emp_no '1001' emp táblából egy DBMS_SQL utasítással kérdezzük le az adatokat. Az EXCEPTION blokk bezárja a kurzort, még akkor is, ha hiba történik.

DBMS_SQL dinamikus SQL-hez

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

teljesítmény

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

Code Magyarázat:

  • 1–8. sorok: Változó deklaráció.
  • 10 vonal: Az SQL utasítás keretezése.
  • 11 vonal: A kurzor megnyitása a DBMS_SQL.OPEN_CURSOR használatával, amely visszaadja a megnyitott kurzor azonosítóját.
  • 12 vonal: A kurzor megnyitása után az SQL elemzésre kerül.
  • 13 vonal: A ':empno' helyett az '1001' kötési érték kerül hozzárendelésre.
  • 14–17. sorok: Az oszlopok definiálása relatív pozíciójuk alapján: (1) alkalmazott_neve, (2) alkalmazott_száma, (3) fizetés, (4) vezető.
  • 18 vonal: A lekérdezés végrehajtása a DBMS_SQL.EXECUTE függvénnyel, amely visszaadja a feldolgozott rekordok számát.
  • 19–32. sorok: Rekordok lekérése ciklusban. A FETCH_ROWS 0 értéket ad vissza, ha már nincsenek sorok, ezzel kilépve a ciklusból.
  • KIVÉTEL blokk: Biztosítja, hogy a kurzor zárva legyen, így a nyitott kurzorok nem szivárognak, ha hiba keletkezik.

NDS vs. DBMS_SQL: Mikor melyiket használjuk?

Mindkét megközelítés futásidejű SQL-t futtat, de különböző helyzetekben alkalmazhatók:

  • Natív dinamikus SQL használata (AZONNALI VÉGREHAJTÁS / MEGNYITÁS) amikor a bemenetek és kimenetek száma és adattípusa fordítási időben ismert. Gyorsabb, könnyebben olvasható és kevesebb kódot igényel.
  • DBMS_SQL használata amikor a struktúra futásidőig ismeretlen, például egy olyan lekérdezés esetén, amelynek a kiválasztott oszlopainak vagy kötési változóinak száma változik, ezt 4. módszerű dinamikus SQL-nek nevezik, vagy ha egy utasítás túl nagy ahhoz, hogy egyetlen 32K-os VARCHAR2 változóba férjen el.

GYIK

A bind változók adatként adják át a felhasználói bemenetet, soha nem futtatható kódként. Az USING záradék pozicionálisan adja meg az értékeket, így a rosszindulatú szöveg nem módosíthatja az utasítás szerkezetét. A nem megbízható bemenetet mindig köti, ahelyett, hogy összefűzné.

Nem. Oracle csak az adatértékeket köti, az objektumneveket nem. Az azonosítókat fűzze össze az SQL-karakterlánccá, és validálja őket a DBMS_ASSERT.SIMPLE_SQL_NAME segítségével az injektálás elleni védelem érdekében.

Az EXECUTE IMMEDIATE csak egy sort kér le. Sok sor esetén nyisson meg egy REF CURSOR-t az OPEN-FOR utasítással, majd ismételje meg a FETCH-et a %NOTFOUND utasításig, majd CLOSE-olja a kurzort.

Adjon hozzá egy RETURNING záradékot az INSERT, UPDATE vagy DELETE utasításhoz, majd az EXECUTE IMMEDIATE utasítás RETURNING INTO záradékával rögzítse az érintett sorok értékeit kötési argumentumokba.

A dinamikus SQL elemzési többletterhelést okoz, mivel az utasítások futásidejű fordulóban fordulnak elő. A kötési változók újrafelhasználása lehetővé teszi Oracle kurzorok megosztása és a nehéz elemzések csökkentése, keeping a statikus SQL-hez közeli teljesítmény.

A karakterláncnak VARCHAR2 vagy CHAR karakterláncnak kell lennie. Nemzeti karaktertípusok, mint például az NVARCHAR2 és az NCHAR, nem engedélyezettek. 32 kB feletti szöveg esetén a DBMS_SQL elfogadja a VARCHAR2 darabok gyűjteményét.

Igen. Az olyan mesterséges intelligencia asszisztensek, mint a GitHub Copilot, egyszerű promptokból készítenek EXECUTE IMMEDIATE és DBMS_SQL blokkokat, javasolnak bind-variable helyőrzőket, és elmagyarázzák az egyes záradékokat, bár a fejlesztőnek továbbra is át kell tekintenie a kimenetet.

A mesterséges intelligencia által vezérelt kódolvasók megjelölik az összefűzött felhasználói bemenetet, és kötési változókat vagy DBMS_ASSERT ellenőrzéseket javasolnak. Az áttekintés során kiemelik a kockázatos mintákat, segítve a...ping A csapatok a bevetés előtt észreveszik a befecskendezési hibákat.

Foglald össze ezt a bejegyzést a következőképpen: