Dynamiczny SQL w PL/SQL

Dynamiczny SQL

Szybki opis

Dynamiczny SQL jest technologią umożliwiającą m.in. stosowanie instrukcji DDL w PL/SQL, dynamiczne budowanie zapytań w trakcie wykonania programu, ograniczenia ilości błędów kompilacji spowodowanych niepoprawnymi odwołaniami do obiektów od których program napisany w PL/SQL jest zależny.

Korzyści wynikające ze stosowania dynamicznego SQL

• Możliwość wykorzystania instrukcji DDL w kodzie. Do tej pory w kodzie programów wykorzystywaliśmy z zakresu języka SQL jedynie instrukcje z grupy DML. Grupa instrukcji DML (Data Manipulation Language) to : INSERT, UPDATE, DELETE. Służą one do dodawania, kasowania, zmiany danych w istniejących już strukturach np. w stworzonych wcześniej tabelach. DDL to ta część SQL która jest odpowiedzialna za tworzenie, kasowanie lub zmianę stanu wyżej wymienionych struktur. Znajdują się w tej grupie polecenia takie jak np. : CREATE TABLE, DROP SEQUENCE. Do tej pory w kodzie PL/SQL nie mogliśmy wykorzystywać instrukcji z tej grupy. Dynamiczny SQL umożliwia nam te działania, jednak z pewnymi obwarowaniami - o tym nieco dalej.
  
• Dynamiczne budowanie zapytania. Bez wykorzystania dynamicznego SQL mogliśmy budować zapytania o z góry znanej konstrukcji. Modyfikowalnym elementem był np. parametr wyszukiwania ( np. select * from employees where employee_id=x ), nie mogliśmy natomiast zmieniać nazwy tabeli do której się odwołujemy. Z wykorzystaniem klauzuli EXECUTE IMMEDIATE staje się to możliwe, jednak naraża nas to na ataki typu SQL Injection. Zapytania możemy budować „w locie”, w trakcie wykonania programu – np. w zależności od warunków których zaistnienia lub nie nie jesteśmy w stanie przewidzieć zanim nie uruchomimy programu.
  
• Walidacja instrukcji SQL w momencie wykonania. Instrukcje SQL pisane w sposób „tradycyjny” w kodzie PL/SQL podlegają walidacji w momencie kompilacji. Jeżeli więc w kodzie SQL odnosimy się do obiektu którego jeszcze nie stworzyliśmy, lub obiekt ten ma status INVALID, nasz program nie skompiluje się poprawnie. Z użyciem dynamicznego SQL możemy pisać kod SQL „na zaś” przewidując istnienie obiektów do których się odwołujemy w przyszłości.
  
• Odseparowanie statusu programów od statusu obiektów do których program się odwołuje. Dotychczas jeśli tabela A została skasowana lub zmieniono jej budowę np. dodając kolumnę, wszystkie programy napisane w PL/SQL zależne od tej tabeli oraz programy zależne od nich zmieniały status na INVALID. Przez taką sytuację nie mogliśmy uruchomić wcześniej skompilowanych i działających programów zależnych od tych obiektów. Zmuszeni też byliśmy do ponownej kompilacji programów (ten problem został częściowo rozwiązany w wersji 11g Oracle poprzez automatyczną kaskadową rekompilację obiektów zależnych. ). Jeśli „tradycyjny” SQL odwołujący się takich obiektów zamienimy na SQL dynamiczny, nasz program pozostanie niewzruszony na ich zmiany. Pojawi nam się w takim wypadku odpowiedni błąd wykonania programu, jednak jego status (INVALID/VALID) pozostanie taki jaki był przed wykonaniem.
  

Klauzula EXECUTE IMMEDIATE

Jeśli chcemy zamienić zwykły kod SQL na SQL Dynamiczny, wystarczy że zamkniemy go w w apostrofach i poprzedzimy klauzulą EXECUTE IMMEDIATE.

Próba kompilacji programu zawierającego polecenie CREATE TABLE zakończyłaby się się niepowodzeniem, ponieważ jest to instrukcja z grupy DDL.

Instrukcje SQL możemy budować dynamicznie korzystając z konkatenacji:

Jest to o tyle wygodne, że możemy tworzyć programy generyczne. Poniżej kod który wykorzystuje dwie istotne zalety dynamicznego SQL – możliwości stosowania instrukcji takich jak CREATE USER, oraz konkatenacji. Dzięki temu mam jeden program który może tworzyć wielu różnych użytkowników o nazwie i haśle wskazywanych przez parametry wejściowe. 

Pamiętaj by nie nadużywać możliwości konkatenacji w dynamicznym SQL. Nigdy nie wykorzystuj tej możliwości w instrukcjach wykorzystywanych jakichkolwiek interfejsach do których dostęp mają użytkownicy. Ktoś mógłby to wykorzystać wstrzykując jakąś niepożądaną instrukcję w miejsce tekstu który ma np. trafić do zapytania jako parametr (truncate table, drop database). Takie ataki noszą nazwę SQL Injection i potrafią być bardzo niebezpieczne. W przypadku interfejsów dostępnych dla użytkowników, lepiej jest stosować pakiet dbms_sql w miejsce klauzuli EXECUTE IMMEDIATE, walidować dane pochodzące od użytkowników, lub zastosować inną metodę zabezpieczającą. O tym wszystkim piszę w dalszej części niniejszego rozdziału.

Parametry wejściowe i zwracanie wyników w instrukcji EXECUTE IMMEDIATE

 

Instrukcje SQL możemy budować korzystając z konkatenacji , jednak z powodów wyżej opisanych rozwiązanie to może być niebezpieczne, jak również w przypadku dłuższych instrukcji bardzo niewygodnie i mało czytelne. Aby dynamicznie podawać parametry, możesz skorzystać z z klauzuli USING. Poniżej przedstawiam przykład.

W tym przypadku cała instrukcja pozostaje czytelna przy czym nadal jest dynamicznie budowana. Zauważ że parametry zostały poprzedzone dwukropkami. Oznaczają one parametry dynamicznie podawane przy użyciu klauzuli USING. Wartości trafiające do tych parametrów pochodzą ze zmiennych wskazanych po „using” wg kolejności wymieniania. Plusem i jednocześnie minusem tego rozwiązania jest fakt, że w ten sposób możemy podawać np. wartości trafiające do tabeli, parametry stosowane w warunkach where (employee_id=x), jednak nie możemy w ten sposób podawać nazwy obiektów. Z jednej strony nie możemy w tym przypadku dynamicznie podać nazwy tabeli do której dodajemy dane, z drugiej spodziewane są dane konkretnego typu. W tym przypadku nie będzie już możliwe wstrzyknięcia dodatkowej, szkodliwej instrukcji przez użytkownika.

Z zapytania możemy również odbierać dane przy użyciu klauzuli into:

Zasada działania jest taka sama jak w przypadku klauzuli using in. Wartości są zwracane do zmiennych wg kolejności podawania.

Klauzula EXECUTE IMMEDIATE a kursory

Gdybyśmy chcieli wykorzystać dynamiczny SQL do stworzenia budowanego w locie zapytania zwracającego cały wiesz naraz lub wiele wierszy, musimy nieco obejść problem i wykorzystać tzw. ref_kursory. W skrócie przedstawiając ref_kursory służą do tworzenia kursorów na podstawie zapytania o nieznanej w momencie kompilacji konstrukcji. Szerzej na ten temat możesz poczytać w rozdziale poświęconym kursorom. Poniżej przykład dynamicznego budowania instrukcji i zwracania wyników do zmiennej typu wierszowego. W przypadku pobierania więcej niż jednego wiersza możemy posłużyć się zwykłą pętlą.

Klauzula EXECUTE IMMEDIATE oraz operacje masowe

Również w operacjach masowych możemy wykorzystać dynamiczny SQL. Rozwiązanie opiera się na metodzie z poprzedniego akapitu. Różnica polega na masowym FETCH'owaniu wierszy do tablicy.

 

 

EXECUTE IMMEDIATE a wywoływanie dynamicznie tworzonego kodu PL/SQL

Klauzulę EXECUTE IMMEDIATE możemy również wykorzystywać do dynamicznego uruchamiania kodu PL/SQL, nie tylko instrukcji SQL. Poniżej prosty przykład :

Pytanie które samo się nasuwa : W jaki sposób korzystać z parametrów IN oraz OUT procedur/funkcji w takim zagnieżdżonym kodzie?

Wykorzystujemy znaną nam już klauzulę USING, tym razem jednak doszedł nam dodatkowy element – OUT. Służy on do odbierania wartości z parametrów out. Parametrów możemy oczywiście odebrać więcej niż jeden. Wystarczy je wymienić po przecinku. Kolejność zmiennych do których ma trafić wartość z parametrów OUT powinna być taka jak parametrów OUT w blogu PL/SQL wykonywanym w ramach EXECUTE IMMEDIATE. Nie myl USING OUT z INTO. Pierwsze służy do odbierania wartości z parametrów typu OUT, drugie do odbierania wyników z zapytań SQL.