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

Przez: Fiona Brown Fiona Brown
Hours Zaktualizowano

Co to jest dynamiczny SQL?

Dynamiczny SQL to metodologia programowania służąca do generowania i uruchamiania instrukcji w czasie wykonywania. Używa się go głównie do pisania uniwersalnych i elastycznych programów, w których instrukcje SQL będą tworzone i wykonywane w czasie wykonywania w oparciu o wymagania.

Sposoby pisania dynamicznego SQL

PL/SQL udostępnia dwa sposoby pisania dynamicznego SQL

  1. NDS – Natywny dynamiczny SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) – Wykonaj natychmiast

Native Dynamic SQL to łatwiejszy sposób pisania dynamicznego SQL. Używa polecenia „EXECUTE IMMEDIATE”, aby utworzyć i wykonać SQL w czasie wykonywania. Jednak aby użyć tego sposobu, typ danych i liczba zmiennych, które mają być używane w czasie wykonywania, muszą być znane wcześniej. Zapewnia również lepszą wydajność i mniejszą złożoność w porównaniu do DBMS_SQL.

Składnia 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • Powyższa składnia przedstawia polecenie EXECUTE IMMEDIATE.
  • Klauzula INTO jest opcjonalna i używana tylko wtedy, gdy dynamiczny SQL zawiera instrukcję wyboru, która pobiera wartości. Typ zmiennej powinien odpowiadać typowi zmiennej instrukcji Select.
  • Klauzula USING jest opcjonalna i używana tylko wtedy, gdy dynamiczny SQL zawiera jakąkolwiek zmienną wiążącą.

1 przykład: W tym przykładzie pobierzemy dane z tabeli emp dla emp_no '1001' za pomocą instrukcji NDS.

NDS – Wykonaj natychmiast

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

Wydajność

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

Wyjaśnienie kodu:

  • Linia kodu 2-6: Deklarowanie zmiennych.
  • Linia kodu 8: Ramkowanie kodu SQL w czasie wykonywania. SQL zawiera zmienną wiązania w warunku „:empno”.
  • Linia kodu 9: Wykonanie tekstu SQL w ramce (co odbywa się w 8. linii kodu) przy użyciu polecenia NDS „EXECUTE IMMEDIATE”
  • Zmienne w klauzuli „INTO” (lv_emp_name, ln_emp_no, ln_salary, ln_manager) służą do przechowywania wartości pobranych z zapytania SQL (emp_name, emp_no, pay, manager)
  • Klauzula „USING” podaje wartości zmiennej wiążącej w zapytaniu SQL (:emp_no).
  • Linia kodu 10-13: Wyświetlanie pobranych wartości.

DBMS_SQL dla dynamicznego SQL

PL/SQL dostarcza pakiet DBMS_SQL, który umożliwia pracę z dynamicznym SQL. Proces tworzenia i wykonywania dynamicznego SQL obejmuje następujący proces.

  • OTWÓRZ KURSORA: Dynamiczny SQL będzie wykonywany w taki sam sposób jak a kursor. Aby więc wykonać instrukcję SQL, musimy otworzyć kursor.
  • PARSE SQL: Następnym krokiem jest przeanalizowanie dynamicznego kodu SQL. Ten proces po prostu sprawdzi składnię i sprawi, że zapytanie będzie gotowe do wykonania.
  • POWIĄZANIE ZMIENNYCH Wartości: Następnym krokiem jest przypisanie wartości dla zmiennych wiążących, jeśli takie istnieją.
  • DEFINIUJ KOLUMNĘ: Następnym krokiem jest zdefiniowanie kolumny przy użyciu ich względnych pozycji w instrukcji Select.
  • WYKONAĆ: Następnym krokiem jest wykonanie przeanalizowanego zapytania.
  • POBIERZ WARTOŚCI: Następnym krokiem jest pobranie wykonanych wartości.
  • ZAMKNIJ KURSORA: Po pobraniu wyników kursor powinien zostać zamknięty.

1 przykład: W tym przykładzie pobierzemy dane z tabeli emp dla emp_no '1001' za pomocą instrukcji DBMS_SQL.

DBMS_SQL dla dynamicznego SQL

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

DBMS_SQL dla dynamicznego SQL

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

Wydajność

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

Wyjaśnienie kodu:

  • Linia kodu 1-9: Deklaracja zmiennej.
  • Linia kodu 10: Tworzenie ramek instrukcji SQL.
  • Linia kodu 11: Otwarcie kursora za pomocą DBMS_SQL.OPEN_CURSOR. Zwróci identyfikator kursora, który jest otwarty.
  • Linia kodu 12: Po otwarciu kursora następuje analiza kodu SQL.
  • Linia kodu 13: Zmienna powiązania „1001” przypisuje identyfikator kursora zamiast „:empno”.
  • Linia kodu 14-17: Definiowanie nazwy kolumny na podstawie jej względnej pozycji w instrukcji SQL. W naszym przypadku względną pozycją jest (1) nazwa_emp, (2) nr_emp, (3) wynagrodzenie (4) menadżer. Zatem na podstawie tej pozycji definiujemy zmienną docelową.
  • Linia kodu 18: Wykonanie zapytania przy użyciu DBMS_SQL.EXECUTE. Zwraca liczbę przetworzonych rekordów.
  • Linia kodu 19-33: Pobieranie rekordów za pomocą pętli i wyświetlanie ich.
  • Linia kodu 20: DBMS_SQL.FETCH_ROWS pobierze jeden rekord z przetworzonych wierszy. Można go wywoływać wielokrotnie w celu pobrania wszystkich wierszy. Jeśli nie może pobrać wierszy, zwróci 0, opuszczając w ten sposób pętlę.

Podsumowanie

W tej sekcji omówiliśmy dynamiczny SQL i sposoby wykonywania DYNAMICZNEGO SQL. Widzieliśmy także różne kroki wykonywania dynamicznego SQL na oba sposoby. Widzieliśmy także przykłady, w których ten sam scenariusz jest obsługiwany zarówno w sposób NDS, jak i DBMS_SQL, aby wykonać wykonanie w czasie wykonywania.

Możesz lubić: