8 kodowanie dynamicznych poleceń SQL

dynamiczny SQL to technika programowania, która umożliwia dynamiczne tworzenie poleceń SQL w czasie wykonywania. Możesz tworzyć bardziej uniwersalne, elastyczne aplikacje za pomocą dynamicznego SQL, ponieważ pełny tekst instrukcji SQL może być Nieznany podczas kompilacji. Na przykład dynamic SQL pozwala utworzyć procedurę, która działa na tabeli, której nazwa nie jest znana do czasu uruchomienia.,

Oracle zawiera dwa sposoby implementacji dynamicznego SQL w aplikacji PL/SQL:

• Native dynamic SQL, gdzie dynamiczne polecenia SQL umieszczane są bezpośrednio w blokach PL / SQL.
• wywołanie procedur w pakiecieDBMS_SQL.

Ten rozdział obejmuje następujące tematy:

• „czym jest dynamiczny SQL?”
• ” dlaczego warto używać dynamicznego SQL?,”
• „dynamiczny Scenariusz SQL wykorzystujący natywny dynamiczny SQL”
• „wybór pomiędzy natywnym dynamicznym SQL a pakietem DBMS_SQL”
• „używanie dynamicznego SQL w językach innych niż PL/SQL”
• „używanie rekordów PL/SQL w poleceniach SQL INSERT i UPDATE”

szczegóły dotyczące DBMS_SQL można znaleźć w pliku DBMS_SQL Oracle9i dostarczył Pakiety pl / sql i types Reference.

czym jest dynamiczny SQL?

Dynamic SQL umożliwia pisanie programów odwołujących się do poleceń SQL, których pełny tekst nie jest znany do czasu uruchomienia., Zanim szczegółowo omówimy dynamiczny SQL, jasna definicja statycznego SQL może stanowić dobry punkt wyjścia do zrozumienia dynamicznego SQL. Statyczne polecenia SQL nie zmieniają się z wykonania na wykonanie. Pełny tekst statycznych poleceń SQL jest znany podczas kompilacji, co zapewnia następujące korzyści:

• pomyślna kompilacja weryfikuje, czy polecenia SQL odwołują się do ważnych obiektów bazy danych.
• pomyślna kompilacja weryfikuje, czy istnieją niezbędne uprawnienia dostępu do obiektów bazy danych.,
• wydajność statycznego SQL jest generalnie lepsza niż dynamicznego SQL.

ze względu na te zalety, powinieneś używać dynamicznego SQL tylko wtedy, gdy nie możesz używać statycznego SQL do osiągnięcia swoich celów, lub jeśli używanie statycznego SQL jest uciążliwe w porównaniu do dynamicznego SQL. Jednak statyczny SQL ma ograniczenia, które można przezwyciężyć za pomocą dynamicznego SQL. Nie zawsze możesz znać pełny tekst poleceń SQL, które muszą być wykonane w procedurze PL / SQL., Twój program może akceptować dane wejściowe użytkownika, które definiują instrukcje SQL do wykonania, lub twój program może wymagać zakończenia pewnych prac przetwarzania, aby określić prawidłowy przebieg działania. W takich przypadkach należy użyć dynamicznego SQL.

na przykład aplikacja do raportowania w środowisku hurtowni danych może nie znać dokładnej nazwy tabeli przed uruchomieniem., Tabele te mogą być nazwane zgodnie z początkiem miesiąca i roku kwartału, na przykład INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998 I tak dalej. W aplikacji do raportowania można użyć dynamicznego SQL, aby określić nazwę tabeli w czasie wykonywania.

Możesz również uruchomić złożone zapytanie z wybraną przez użytkownika kolejnością sortowania., Zamiast kodowania zapytania dwa razy, z różnymi klauzulami ORDER BY , można skonstruować zapytanie dynamicznie, aby zawierało określoną klauzulę ORDER BY.

dynamiczne programy SQL mogą obsługiwać zmiany w definicjach danych, bez konieczności rekompilacji. To sprawia, że dynamiczny SQL jest znacznie bardziej elastyczny niż statyczny SQL. Dynamiczny SQL pozwala pisać kod wielokrotnego użytku, ponieważ SQL można łatwo dostosować do różnych środowisk..,

Dynamic SQL umożliwia również wykonywanie poleceń DDL (data definition language) i innych poleceń SQL, które nie są obsługiwane w czysto statycznych programach SQL.

po co używać dynamicznego SQL?

powinieneś używać dynamicznego SQL w przypadkach, gdy statyczny SQL nie obsługuje operacji, którą chcesz wykonać, lub w przypadkach, gdy nie znasz dokładnych instrukcji SQL, które muszą być wykonane przez procedurę PL / SQL. Te polecenia SQL mogą zależeć od danych wejściowych użytkownika lub mogą zależeć od pracy przetwarzania wykonanej przez program.,DL and SCL Statements in PL/SQL

In PL/SQL, you can only execute the following types of statements using dynamic SQL, rather than static SQL:

• Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE
• Session control language (SCL) statements, such as ALTER SESSION and SET ROLE
  
  See Also:

  Oracle9i SQL Reference for information about DDL and SCL statements.,

ponadto możesz używać tylko klauzuliTABLEw instrukcjiSELECT za pomocą dynamicznego SQL. Na przykład następujący blok PL / SQL zawieraSELECT oświadczenie, które używa klauzuliTABLE I natywnego dynamicznego SQL:

wykonywanie zapytań dynamicznych

możesz użyć dynamicznego SQL do tworzenia aplikacji, które wykonują zapytania dynamiczne, których pełny tekst nie jest znany do czasu uruchomienia., Wiele typów aplikacji musi używać zapytań dynamicznych, w tym:

• aplikacje, które pozwalają użytkownikom na wprowadzanie lub wybieranie kryteriów wyszukiwania lub sortowania zapytań w czasie wykonywania
• aplikacje, które pozwalają użytkownikom na wprowadzanie lub wybieranie podpowiedzi optymalizatora w czasie wykonywania
• aplikacje, które odpytują bazę danych, w której definicje danych tabel stale się zmieniają
• aplikacje, które odpytują bazę danych, w której często tworzone są nowe tabele

przykłady można znaleźć w sekcji „Querying Using Dynamic SQL: Example” i zobacz przykłady zapytań w „dynamiczny scenariusz SQL przy użyciu natywnego dynamicznego SQL”.,

odwoływanie się do obiektów bazy danych, które nie istnieją podczas kompilacji

wiele typów aplikacji musi wchodzić w interakcje z danymi, które są generowane okresowo. Na przykład możesz znać definicje tabel podczas kompilacji, ale nie nazwy tabel.

dynamiczny SQL może rozwiązać ten problem, ponieważ pozwala poczekać do czasu uruchomienia, aby określić nazwy tabel. Na przykład w przykładowej aplikacji hurtowni danych omówionej w „czym jest dynamiczny SQL?”, nowe tabele są generowane co kwartał, a te tabele zawsze mają tę samą definicję., Możesz pozwolić użytkownikowi określić nazwę tabeli w czasie wykonywania za pomocą dynamicznego zapytania SQL, podobnego do następującego:

dynamiczna Optymalizacja wykonywania

dynamiczny SQL pozwala zbudować polecenie SQL w sposób, który optymalizuje wykonanie poprzez dynamiczne połączenie podpowiedzi w polecenie SQL. Umożliwia to zmianę podpowiedzi na podstawie bieżących statystyk bazy danych, bez konieczności rekompilacji.,

na przykład poniższa procedura używa zmiennej o nazwie a_hint, aby umożliwić użytkownikom przekazanie opcji podpowiedzi do polecenia SELECT:

w tym przykładzie użytkownik może przekazać dowolną z następujących wartości dla a_hint:

a_hint = '/*+ ALL_ROWS */'a_hint = '/*+ FIRST_ROWS */'a_hint = '/*+ CHOOSE */'lub inna ważna opcja podpowiedzi.

wykonywanie dynamicznych bloków PL/SQL

możesz użyćEXECUTEIMMEDIATE do wykonywania anonimowych bloków PL / SQL. Możesz dodać elastyczność, konstruując Zawartość bloku w czasie wykonywania.

na przykład, załóżmy, że chcesz napisać aplikację, która pobiera numer zdarzenia i wysyła go do obsługi zdarzenia. Nazwa programu obsługi ma postać EVENT_HANDLER_event_num, gdzie event_num jest numerem zdarzenia., Jedną z metod jest zaimplementowanie dispatcher jako instrukcji switch, gdzie kod obsługuje każde zdarzenie poprzez statyczne wywołanie odpowiedniej procedury obsługi. Ten kod nie jest zbyt rozszerzalny, ponieważ kod dyspozytora musi być aktualizowany za każdym razem, gdy zostanie dodany moduł obsługi nowego zdarzenia.,

korzystając z natywnego dynamicznego SQL, możesz napisać mniejszy, bardziej elastyczny dyspozytor zdarzeń podobny do następującego:

wykonywanie dynamicznych operacji przy użyciu praw Invokera

korzystając z funkcji invoker-rights z dynamicznym SQL, możesz tworzyć aplikacje, które wydają dynamiczne instrukcje SQL zgodnie z uprawnieniami i schematem wywoływacza. Te dwie funkcje, invoker-rights i dynamic SQL, umożliwiają tworzenie podkomponentów aplikacji wielokrotnego użytku, które mogą działać i uzyskać dostęp do danych i modułów wywoływacza.,

dynamiczny Scenariusz SQL przy użyciu natywnego dynamicznego SQL

Ten scenariusz pokazuje, jak wykonać następujące operacje przy użyciu natywnego dynamicznego SQL:

• wykonywanie operacji DDL i DML
• wykonywanie zapytań jednorzędowych i wielorzędowych

baza danych w tym scenariuszu jest bazą danych zasobów ludzkich firmy (o nazwiehr) z następującym modelem danych:

tabela główna o nazwieoffices zawiera listę wszystkich lokalizacji firmy., Tabelaoffices ma następującą definicję:

Column Name Null? Type LOCATION NOT_NULL VARCHAR2(200)

wieleemp_tabele lokalizacji zawierają informacje o pracownikach, gdzie lokalizacja jest nazwą miasta, w którym znajduje się biuro. Na przykład tabela o nazwie emp_houston zawiera informacje o pracownikach dla Biura firmy w Houston, podczas gdy tabela o nazwie emp_boston zawiera informacje o pracownikach dla Biura firmy w Bostonie.,

każdaemp_tabela lokalizacji ma następującą definicję:

poniższe sekcje opisują różne natywne dynamiczne operacje SQL, które mogą być wykonywane na danych w bazie danychhr.

przykładowa operacja DML przy użyciu natywnego dynamicznego SQL

następująca natywna dynamiczna procedura SQL daje podniesienie do wszystkich pracowników o określonym stanowisku:

przykładowa operacja DDL przy użyciu natywnego dynamicznego SQL

EXECUTE IMMEDIATE może wykonywać operacje DDL., Na przykład, następująca procedura dodaje lokalizację biura:

następująca procedura usuwa lokalizację biura:

przykładowe zapytanie Jednorzędowe przy użyciu natywnego dynamicznego SQL

EXECUTEIMMEDIATE może wykonywać dynamiczne zapytania jednorzędowe. Zmienne bind można określić w klauzuli USING I pobrać wynikowy wiersz do celu określonego w klauzuliINTO instrukcji.,

następująca funkcja pobiera liczbę pracowników w danej lokalizacji wykonujących określone zadanie:

przykładowe zapytanie Wielowierszowe przy użyciu natywnego dynamicznego SQL

OPEN-FOR, FETCH I CLOSE mogą wykonywać dynamiczne zapytania Wielowierszowe zapytania wiersza., Na przykład, poniższa procedura wyświetla listę wszystkich pracowników z określonym zadaniem w określonej lokalizacji:

wybór pomiędzy natywnym dynamicznym SQL a pakietem DBMS_SQL

Oracle udostępnia dwie metody używania dynamicznego SQL w PL/SQL: natywny dynamiczny SQL oraz pakietDBMS_SQL. Natywny dynamiczny SQL umożliwia umieszczanie dynamicznych poleceń SQL bezpośrednio w kodzie PL/SQL. Te dynamiczne instrukcje obejmują instrukcje DML( w tym zapytania), anonimowe bloki PL/SQL, instrukcje DDL, instrukcje kontroli transakcji i instrukcje kontroli sesji.,

To process most native dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use OPEN-FOR, FETCH, and CLOSE statements.

pakietDBMS_SQL jest biblioteką PL / SQL, która oferuje API do dynamicznego wykonywania poleceń SQL. PakietDBMS_SQL zawiera procedury otwierania kursora, parsowania kursora, dostarczania wiązań i tak dalej. Programy używające pakietu DBMS_SQL wykonują wywołania do tego pakietu w celu wykonywania dynamicznych operacji SQL.

poniższe sekcje zawierają szczegółowe informacje o zaletach obu metod.,

zalety natywnego dynamicznego SQL

natywny dynamiczny SQL zapewnia następujące zalety w porównaniu z pakietemDBMS_SQL:

natywny dynamiczny SQL jest łatwy w użyciu

ponieważ natywny dynamiczny SQL jest zintegrowany z SQL, możesz go używać w taki sam sposób, w jaki używasz statycznego SQL w kodzie PL / SQL. Natywny dynamiczny kod SQL jest zwykle bardziej zwarty i czytelny niż odpowiedni kod, który używa pakietu DBMS_SQL.,

z pakietem DBMS_SQL musisz wywoływać wiele procedur i funkcji w ścisłej kolejności, dzięki czemu nawet proste operacje wymagają dużej ilości kodu. Możesz uniknąć tej złożoności, używając zamiast tego natywnego dynamicznego SQL.

tabela 8-1 ilustruje różnicę w ilości kodu wymaganego do wykonania tej samej operacji za pomocą pakietuDBMS_SQL I natywnego dynamicznego SQL.,

tabela 8-1 porównanie kodu pakietu DBMS_SQL i natywnego dynamicznego SQL

natywny dynamiczny SQL jest szybszy niż DBMS_SQL

natywny dynamiczny SQL w PL/SQL działa porównywalnie do wydajności statycznego SQL, ponieważ interpreter PL / SQL ma wbudowaną obsługę. Programy używające natywnego dynamicznego SQL są znacznie szybsze niż programy używające pakietu DBMS_SQL. Zazwyczaj natywne dynamiczne polecenia SQL działają od 1,5 do 3 razy lepiej niż równoważne DBMS_SQL. (Wzrost wydajności może się różnić w zależności od aplikacji.,)

natywny dynamiczny SQL łączy kroki przygotowania, wiązania i wykonywania instrukcji w jedną operację, co minimalizuje kopiowanie danych i narzut wywołania procedury i poprawia wydajność.

pakietDBMS_SQL jest oparty na proceduralnym API i wywołuje wysokie wywołania procedur i kopie danych. Za każdym razem, gdy bindujesz zmienną, pakietDBMS_SQL kopiuje zmienną PL / SQL bind do jej przestrzeni do użycia podczas wykonywania., Za każdym razem, gdy wykonujesz pobieranie, dane są kopiowane do przestrzeni zarządzanej przez pakiet DBMS_SQL, a następnie pobrane dane są kopiowane, po jednej kolumnie na raz, do odpowiednich zmiennych PL / SQL, co powoduje znaczne obciążenie.

Wskazówka dotycząca wydajności: używanie zmiennych Bind

podczas korzystania z natywnego dynamicznego pakietu SQL lub pakietuDBMS_SQL można poprawić wydajność za pomocą zmiennych bind, ponieważ zmienne bind umożliwiają Oracle współdzielenie jednego kursora dla wielu poleceń SQL.,

na przykład następujący natywny dynamiczny kod SQL nie używa zmiennych bind:

dla każdej odrębnej zmiennej my_deptno tworzony jest nowy kursor, co powoduje niezgodność zasobów i słabą wydajność. Zamiast tego bind my_deptno jako zmienna bind:

tutaj ten sam kursor jest ponownie używany dla różnych wartości bindmy_deptno, poprawiając wydajność i skalowalność.,

natywny dynamiczny SQL obsługuje typy zdefiniowane przez użytkownika

natywny dynamiczny SQL obsługuje wszystkie typy obsługiwane przez statyczny SQL w PL/SQL, w tym typy zdefiniowane przez użytkownika, takie jak zdefiniowane przez użytkownika Obiekty, kolekcje iREFs. Pakiet DBMS_SQL nie obsługuje tych typów zdefiniowanych przez użytkownika.

natywny dynamiczny SQL obsługuje pobieranie do rekordów

natywny dynamiczny SQL i statyczny SQL obsługują pobieranie do rekordów, ale pakietDBMS_SQL nie. W natywnym dynamicznym SQL wiersze wynikające z zapytania mogą być bezpośrednio pobierane do rekordów PL/SQL.,

w poniższym przykładzie wiersze z zapytania są pobierane do rekordu emp_rec:

zalety pakietu DBMS_SQL

pakiet DBMS_SQL zapewnia następujące zalety w stosunku do natywnego dynamicznego SQL:

DBMS_SQL jest obsługiwany w kliencie-programy po stronie klienta

pakiet DBMS_SQL jest obsługiwany w programach po stronie klienta, ale natywny dynamiczny SQL nie jest., Każde wywołanie pakietu DBMS_SQL z programu po stronie klienta tłumaczy się na zdalne wywołanie procedury PL/SQL (RPC); te wywołania występują, gdy trzeba powiązać zmienną, zdefiniować zmienną lub wykonać instrukcję.

DBMS_SQL obsługuje description

DESCRIBE_COLUMNSprocedura w pakiecieDBMS_SQLmoże być używana do opisu kolumn kursora otwartego i przetwarzanego przezDBMS_SQL. Ta funkcja jest podobna do polecenia DESCRIBE w SQL*Plus., Natywny dynamiczny SQL nie posiada obiektu DESCRIBE.

DBMS_SQL obsługuje aktualizacje wielu wierszy i usuwa z klauzulą zwracającą

pakietDBMS_SQL obsługuje instrukcje z klauzuląRETURNING, które aktualizują lub usuwają wiele wierszy. Natywny dynamiczny SQL obsługuje tylko klauzulę RETURNING, jeśli zwracany jest pojedynczy wiersz.,

DBMS_SQL Supports SQL Statements Larger than 32KB

The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.

DBMS_SQL Lets You Reuse SQL Statements

The PARSE procedure in the DBMS_SQL package parses a SQL statement once., Po pierwszym przetworzeniu instrukcji można użyć wielokrotnie z różnymi zestawami argumentów bind.

Native dynamic SQL przygotowuje polecenie SQL za każdym razem, gdy jest ono używane, co zwykle obejmuje parsowanie, optymalizację i generowanie planów. Chociaż dodatkowe operacje prepare wiążą się z niewielką karą za wydajność, spowolnienie jest zwykle przeważane przez korzyści wydajnościowe natywnego dynamicznego SQL.,

przykłady kodu pakietu DBMS_SQL i natywnego dynamicznego kodu SQL

poniższe przykłady ilustrują różnice w kodzie niezbędne do wykonania operacji za pomocą DBMS_SQL oraz natywnego dynamicznego kodu SQL. W szczególności przedstawiono następujące przykłady:

• zapytanie
• operacja DML
• operacja zwracająca DML

ogólnie rzecz biorąc, natywny dynamiczny kod SQL jest bardziej czytelny i zwarty, co może poprawić wydajność programistów.,

zapytania przy użyciu Dynamicznego SQL: przykład

poniższy przykład zawiera dynamiczną instrukcję zapytania z jedną zmienną bind (:jobname) I dwiema wybranymi kolumnami (ename I sal):

stmt_str := 'SELECT ename, sal FROM emp WHERE job = :jobname';

Ten przykład zapytania dla pracowników z opisem stanowiska SALESMAN w job kolumna emp tabeli. Tabela 8-2 pokazuje przykładowy kod, który wykonuje to zapytanie za pomocą pakietu DBMS_SQL I natywnego dynamicznego SQL.,

Table 8-2 Querying Using the DBMS_SQL Package and Native Dynamic SQL

Performing DML Using Dynamic SQL: Example

poniższy przykład zawiera dynamiczną INSERT instrukcję dla tabeli z trzema kolumnami:

stmt_str := 'INSERT INTO dept_new VALUES (:deptno, :dname, :loc)';

Ten przykład wstawia nowy wiersz, dla którego wartości kolumn są w zmiennych PL/SQL deptnumber, deptname I location. Tabela 8-3 pokazuje przykładowy kod, który wykonuje tę operację DML przy użyciu pakietu DBMS_SQL I natywnego dynamicznego SQL.,

tabela 8-3 operacja DML przy użyciu pakietu DBMS_SQL i natywnego dynamicznego SQL

Wykonywanie DML z klauzulą zwracającą przy użyciu Dynamicznego SQL: przykład

poniższy przykład używa dynamicznego UPDATE do aktualizacji lokalizacji działu, a następnie zwraca nazwę działu:

stmt_str := 'UPDATE dept_new SET loc = :newloc WHERE deptno = :deptno RETURNING dname INTO :dname';

tabela 8-4 pokazuje przykładowy kod, który wykonuje tę operację, używając zarówno pakietu DBMS_SQL, jak i natywnego dynamicznego SQL.,

tabela 8-4 DML zwracanie operacji przy użyciu pakietu DBMS_SQL i natywnego dynamicznego SQL

używanie dynamicznego SQL w językach innych niż PL/SQL

chociaż ten rozdział omawia obsługę dynamicznego SQL PL/SQL, możesz wywołać dynamiczny SQL z innych języków:

• Jeśli używasz C/C++, możesz wywołać dynamiczny SQL za pomocą Oracle Call Interface (OCI), lub możesz użyć precompilera Pro*C/C++ aby dodać dynamiczne rozszerzenia SQL do kodu C.
• Jeśli używasz COBOL, możesz użyć prekompilatora Pro * COBOL, aby dodać dynamiczne rozszerzenia SQL do kodu COBOL.,
• Jeśli używasz Javy, możesz tworzyć aplikacje, które używają dynamicznego SQL z JDBC.

Jeśli masz aplikację, która używa OCI, Pro * C / C++ lub pro*COBOL do uruchamiania dynamicznego SQL, powinieneś rozważyć przejście na natywny dynamiczny SQL wewnątrz procedur i funkcji przechowywanych w PL / SQL. Round-trips sieci wymagane do wykonywania dynamicznych operacji SQL z aplikacji po stronie klienta może zaszkodzić wydajności. Procedury przechowywane mogą znajdować się na serwerze, eliminując obciążenie sieci., Procedury i funkcje przechowywane w PL/SQL można wywoływać z aplikacji OCI, Pro*C/C++ lub pro*COBOL.,br>Zobacz także:

aby uzyskać informacje na temat wywoływania procedur składowanych Oracle i funkcji składowanych z różnych języków, zobacz:

• Oracle Call Interface Programmer 's Guide
• Pro*C/C++ Precompiler Programmer' s Guide
• Pro*COBOL Precompiler Programmer 's Guide
• Oracle9i Java Stored Procedures Developer' s Guide

Korzystanie z rekordów PL/SQL w SQL INSERT i polecenia update

chociaż można wyliczyć każde pole rekordu PL/SQL podczas wstawiania lub aktualizowania wierszy w tabeli, wynikowy kod nie jest szczególnie czytelny ani możliwy do utrzymania., Zamiast tego możesz używać rekordów PL / SQL bezpośrednio w tych instrukcjach. Najwygodniejszą techniką jest zadeklarowanie rekordu przy użyciu atrybutu % ROWTYPE, tak aby miał dokładnie te same pola Co tabela SQL.

chociaż ta technika pomaga zintegrować zmienne PL / SQL i typy ściślej z instrukcjami SQL DML, nie można używać rekordów PL/SQL jako zmiennych bind w dynamicznych instrukcjach SQL.