Oracle Samouczek dotyczący dynamicznego języka SQL PL/SQL: wykonanie natychmiastowe i DBMS_SQL

Co to jest dynamiczny SQL?

Dynamiczny SQL to metodologia programowania służąca do generowania i uruchamiania instrukcji w czasie wykonywania. Służy głównie do pisania uniwersalnych i elastycznych programów, w których instrukcje SQL są tworzone i wykonywane w czasie wykonywania w zależności od wymagań, na przykład gdy nazwy tabel, listy kolumn lub warunki WHERE nie są znane do momentu uruchomienia programu.

Sposoby pisania dynamicznego SQL

PL/SQL udostępnia dwa sposoby zapisu dynamicznego SQL:

1. NDS – Natywny dynamiczny SQL (polecenia EXECUTE IMMEDIATE i OPEN-FOR)
2. DBMS_SQL (dołączony pakiet)

Ogólna zasada jest prosta: jeśli liczba i typy danych zmiennych wejściowych i wyjściowych są znane w czasie kompilacji, należy użyć natywnego dynamicznego SQL, ponieważ jest szybszy i wymaga mniej kodu. Jeśli te informacje są znane tylko w czasie wykonywania, należy użyć pakietu DBMS_SQL.

NDS (Native Dynamic SQL) – Wykonaj natychmiast

Natywny dynamiczny SQL to prostszy sposób pisania dynamicznego SQL. Wykorzystuje polecenie EXECUTE IMMEDIATE do tworzenia i wykonywania kodu SQL w czasie wykonywania. Aby skorzystać z tego podejścia, typ danych i liczba zmiennych używanych w czasie wykonywania muszą być znane wcześniej. Zapewnia to również lepszą wydajność i mniejszą złożoność w porównaniu z DBMS_SQL.

Składnia

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

• dynamiczny_ciąg_SQL: Wyrażenie ciągu (VARCHAR2 lub CHAR, nie NVARCHAR2/NCHAR) zawierające pojedyncze polecenie SQL lub blok PL/SQL.
• Klauzula INTO: Opcjonalny. Używany tylko wtedy, gdy dynamiczna instrukcja SQL to instrukcja SELECT dla jednego wiersza; przechwytuje zwrócone wartości do zmiennych lub rekordu. Każda wybrana kolumna wymaga zmiennej zgodnej z typem.
• Klauzula USING: Opcjonalnie. Dostarcza zmienne wiążące. Domyślnym trybem jest IN; OUT i IN OUT służą do odbierania wartości zwrotnych.
• Klauzula RETURNING INTO: Używane z instrukcjami DML zawierającymi klauzulę RETURNING w celu przechwycenia wartości wierszy objętych klauzulą ​​i umieszczenia ich w argumentach wiążących.

1 przykład: W tym przykładzie pobieramy dane z tabeli emp dla emp_no „1001” za pomocą polecenia NDS ze zmienną powiązania.

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

Wydajność

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

Code Wyjaśnienie:

• Linie 2-6: Deklarowanie zmiennych.
• Linia 8: Ramkowanie zapytania SQL w czasie wykonywania. Zapytanie SQL zawiera zmienną powiązania „:empno” w warunku WHERE.
• Linie 9-11: Wykonanie ramki SQL z instrukcją EXECUTE IMMEDIATE. Zmienne klauzuli INTO przechowują pobrane wartości, a klauzula USING dostarcza wartość zmiennej wiążącej :empno.
• Linie 12-15: Wyświetlanie pobranych wartości.

Korzystanie z Dynamic SQL dla DDL

Statyczny PL/SQL nie może bezpośrednio uruchamiać instrukcji DDL, takich jak CREATE, ALTER czy DROP. EXECUTE IMMEDIATE rozwiązuje ten problem, budując instrukcję jako ciąg znaków, co jest również przydatne, gdy nazwa obiektu jest podawana w czasie wykonywania:

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

Nazw obiektów (tabela, kolumna, schemat) nie można przekazać jako zmiennych wiążących, dlatego należy je połączyć w ciąg znaków. Zawsze sprawdzaj poprawność takich danych wejściowych, na przykład za pomocą DBMS_ASSERT.SIMPLE_SQL_NAME, aby uniknąć ataków typu SQL injection.

DBMS_SQL dla dynamicznego SQL

PL/SQL udostępnia pakiet DBMS_SQL do pracy z dynamicznym SQL, gdy struktura instrukcji nie jest znana aż do momentu uruchomienia. Proces tworzenia i wykonywania dynamicznego SQL obejmuje następujące kroki:

• OTWÓRZ KURSOR: Dynamiczny SQL wykonuje się jak kursorAby wykonać instrukcję SQL, musimy najpierw otworzyć kursor.
• PRZETWARZANIE SQL: Przeanalizuj dynamiczny kod SQL. Sprawdzi to składnię i przygotuje zapytanie do wykonania.
• ZMIENNA BIND Wartości: Przypisz wartości zmiennym wiążącym, jeśli takie istnieją.
• DEFINIUJ KOLUMNĘ: Zdefiniuj każdą kolumnę, używając jej względnej pozycji w poleceniu SELECT.
• WYKONAĆ: Wykonaj przeanalizowane zapytanie.
• POBIERZ WARTOŚCI: Pobierz wykonane wartości.
• ZAMKNIJ KURSOR: Po pobraniu wyników zamknij kursor.

1 przykład: W tym przykładzie pobieramy dane z tabeli emp dla numeru emp „1001” za pomocą instrukcji DBMS_SQL. Blok EXCEPTION zamyka kursor nawet w przypadku wystąpienia błędu.

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

Wydajność

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

Code Wyjaśnienie:

• Linie 1-8: Deklaracja zmiennej.
• Linia 10: Tworzenie instrukcji SQL.
• Linia 11: Otwieranie kursora za pomocą DBMS_SQL.OPEN_CURSOR, które zwraca identyfikator otwartego kursora.
• Linia 12: Po otwarciu kursora następuje analiza kodu SQL.
• Linia 13: W miejsce ':empno' przypisywana jest wartość powiązania '1001'.
• Linie 14-17: Definiowanie kolumn według ich względnej pozycji: (1) emp_name, (2) emp_no, (3) salary, (4) manager.
• Linia 18: Wykonanie zapytania za pomocą DBMS_SQL.EXECUTE, które zwraca liczbę przetworzonych rekordów.
• Linie 19-32: Pobieranie rekordów w pętli. FETCH_ROWS zwraca 0, gdy nie ma już żadnych wierszy, co powoduje wyjście z pętli.
• Blok WYJĄTKÓW: Zapewnia zamknięcie kursora, dzięki czemu otwarte kursory nie będą przeciekać w przypadku wystąpienia błędu.

NDS vs DBMS_SQL: Kiedy używać którego

Oba podejścia uruchamiają kod SQL w czasie wykonywania, ale sprawdzają się w różnych sytuacjach:

• Użyj natywnego dynamicznego SQL (WYKONAJ NATYCHMIAST / OTWÓRZ) gdy liczba i typy danych wejściowych i wyjściowych są znane w momencie kompilacji. Jest to szybsze, łatwiejsze do odczytania i wymaga mniej kodu.
• Użyj DBMS_SQL gdy struktura jest nieznana aż do momentu wykonania, na przykład w przypadku zapytania, którego liczba wybranych kolumn lub zmiennych wiążących jest zmienna, co jest znane jako metoda 4 dynamicznego SQL, lub gdy instrukcja jest zbyt długa, aby zmieścić się w pojedynczej zmiennej VARCHAR2 o rozmiarze 32 KB.

FAQ