Samouczek dotyczący dynamicznego SQL Oracle PL / SQL: wykonaj natychmiastowe & DBMS_SQL

Spisie treści:

Anonim

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 będą tworzone i wykonywane w czasie wykonywania na podstawie wymagań.

W tym samouczku nauczysz się:

  • Sposoby pisania dynamicznego SQL
  • NDS (Native Dynamic SQL) - Wykonaj natychmiast
  • DBMS_SQL dla dynamicznego SQL

Sposoby pisania dynamicznego SQL

PL / SQL zapewnia dwa sposoby pisania dynamicznego SQL

  1. NDS - natywny dynamiczny SQL
  2. DBMS_SQL

NDS (Native Dynamic SQL) - Wykonaj natychmiast

Natywny dynamiczny SQL to łatwiejszy sposób pisania dynamicznego SQL. Używa polecenia „EXECUTE IMMEDIATE” do tworzenia i wykonywania kodu SQL w czasie wykonywania. Aby jednak 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 mniej złożoności w porównaniu do DBMS_SQL.

Składnia 

EXECUTE IMMEDIATE()[INTO][USING ]
  • Powyższa składnia przedstawia polecenie EXECUTE IMMEDIATE.
  • Klauzula INTO jest opcjonalna i używana tylko wtedy, gdy dynamiczny SQL zawiera instrukcję select, która pobiera wartości. Typ zmiennej powinien pasować do typu zmiennej instrukcji select.
  • Klauzula USING jest opcjonalna i używana tylko wtedy, gdy dynamiczny SQL zawiera jakąkolwiek zmienną powiązaną.

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

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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;/

Wynik

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

Objaśnienie kodu:

  • Linia kodu 2-6 : Deklarowanie zmiennych.
  • Linia kodu 8 : ramkowanie kodu SQL w czasie wykonywania. SQL zawiera zmienną bind w miejscu gdzie warunek ': empno'.
  • Linia kodu 9 : Wykonywanie tekstu SQL w ramce (co jest wykonywane w linii kodu 8) za pomocą polecenia NDS „EXECUTE IMMEDIATE”
  • Zmienne w klauzuli 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) są używane do przechowywania pobranych wartości z zapytania SQL (emp_name, emp_no, salary, manager)
  • Klauzula „USING” podaje wartości zmiennej bind 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 kodu SQL obejmuje następujący proces.

  • OPEN CURSOR : Dynamiczny SQL będzie wykonywany w taki sam sposób jak kursor. Aby więc wykonać instrukcję SQL, musimy otworzyć kursor.
  • PARSE SQL : Następnym krokiem jest przeanalizowanie dynamicznego SQL. Ten proces po prostu sprawdzi składnię i sprawi, że zapytanie będzie gotowe do wykonania.
  • BIND VARIABLE Wartości : Następnym krokiem jest przypisanie wartości zmiennym powiązanym, jeśli takie istnieją.
  • DEFINE COLUMN : Następnym krokiem jest zdefiniowanie kolumny przy użyciu ich względnych pozycji w instrukcji select.
  • WYKONAJ : Następnym krokiem jest wykonanie przeanalizowanego zapytania.
  • FETCH VALUES : Następnym krokiem jest pobranie wykonanych wartości.
  • CLOSE CURSOR : Po pobraniu wyników kursor powinien zostać zamknięty.

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

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:/

Wynik

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

Objaśnienie kodu:

  • Linia kodu 1-9 : Deklaracja zmiennej.
  • Linia kodu 10 : Tworzenie ramek dla instrukcji SQL.
  • Linia kodu 11 : Otwieranie kursora za pomocą DBMS_SQL.OPEN_CURSOR. Zwróci identyfikator otwartego kursora.
  • Linia kodu 12 : Po otwarciu kursora SQL jest analizowany.
  • Linia kodu 13 : Zmienna wiązania „1001” jest przypisywana do id kursora zamiast „: empno”.
  • Wiersz kodu 14-17 : Definiowanie nazwy kolumny na podstawie ich względnej pozycji w instrukcji SQL. W naszym przypadku pozycja względna to (1) emp_name, (2) emp_no (3) wynagrodzenie (4) menedżer. Na podstawie tej pozycji definiujemy zmienną docelową.
  • Linia kodu 18 : Wykonywanie 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 tego samego.
  • Linia kodu 20: DBMS_SQL.FETCH_ROWS pobierze jeden rekord z przetworzonych wierszy. Można go wywołać wielokrotnie, aby pobrać wszystkie wiersze. Jeśli nie może pobrać wierszy, zwróci 0, a tym samym zakończy pętlę.

Podsumowanie

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