środa, 29 czerwca 2011

PL/SQL Podtypy (Subtypes)


Deklaracja podtypów

Podtypy (subtypes) to typy danych zdefiniowane przez programistę na podstawie istniejących już wcześniej typów danych. Przykładem może być typ danych służący do przechowywania cen produktów – tj. z dokładnością do 2 miejsca po przecinku:












W powyższym przykładzie „cena” jest nazwą podtypu. „cena_netto” to zmienna typu cena.
Podtypy możemy też budować w oparciu o typy złożone. Nasz podtyp opiera się o tym prostszy, tymczasem możemy też budować podtypy w oparciu o np. typ wierszowy, typy tablicowe.











Zasięg podtypów

W poprzednich przykładach nowo zdeklarowany typ jest „widoczny” tylko w blogu w którym został zdeklarowany. Jeśli chcecie mieć typ dostępny dla większej ilości procedur, możecie zdeklarować go w ramach pakietu:






Tracing kodu PL/SQL

Przy prostych programach, jesteśmy w stanie sami prześledzić proces wzajemnego wywoływania się różnych procedur. Jeśli jednak projektujemy spory system, lub przejmujemy po kimś innym projekt warto byłoby móc zautomatyzować ten proces.
Jeśli chcemy obserwować wywołania procedur w projekcie (np. kiedy zostały wywołane i przez jaką inną procedurę) możemy skorzystać z dobrodziejstw pakietu dbms_trace.
Sama obserwacja wymaga kilku czynności, natomiast przed przystąpieniem do pracy z tym pakietem musimy powziąć przygotowania.
Pakiet dbms_trace jest już w bazie skompilowany i gotowy do użycia. Do pracy będą nam też potrzebne odpowiednie tabelki do których ładowane będą dane z trace'owania naszej aplikacji. Tych tabelek jeszcze nie mamy. Skrypt  tworzący te tabelki oraz niezbędne widoki możemy znaleźć w $ORACLE_HOME/rdbms/admin i nazywa się tracetab.sql

tracetab.sql na pewno znajdziecie w wersji Enterprise 11g, sprawdzałem to również na Express Edition 10g ale tego pliku tam nie znalazłem. Być może trzeba by go było skądś ściągnąć. Pakiet dbms_trace był w obu wersjach. Jeśli podczas wykonywania tracetab.sql wypluje nam kilka błędów to nie ma co się przejmować, wszystko i tak działa.


Generalnie dalsza część opowieści po utworzeniu odpowiedniej struktury tabel i widoków przedstawia się tak:

  • Trzeba umożliwić tracing dla pakietów i procedur/funkcji które chcemy obserwować.
ALTER PROCEDURE NAZWA_PROCEDURY COMPILE DEBUG;
lub
ALTER PACKAGE NAZWA_PAKIETU COMPILE DEBUG BODY;
lub
wykonanie następnego punktu i ponowną kompilację kodu.
  • Włączyć tracowanie dla sesji (tylko w tym przypadku będziemy mogli obserwować bloki anonimowe):
ALTER SESSION SET PLSQL_DEBUG=TRUE;

  • Ustawić poziom trace'owania:
EXECUTE DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ALL_CALLS);


Poziomów trace jest kilka, na pewno uncle google je zna :) Powyższe (trace_all_calls) śledzi wszystkie wywołania procedur/funkcji/pakietów).


  • Wykonanie procedury/funkcji dla której włączyliśmy tracing;
  • Wyłączenie trace'owania  ( po co mają nam się zbierać dane i baza puchnąć jak już mamy to co chcemy?)
 EXECUTE DBMS_TRACE.CLEAR_PLSQL_TRACE;

Po wszystkim możemy zajrzeć do widoków:

plsql_trace_events
plsql_trace_runs

zawierających informacje na temat obserwowanych procedur i funkcji, czasu ich wywołania oraz kilku innych ciekawych rzeczy.




Uprzedzając pytanie: Nie. Nie możemy tej metody wykorzystać do obserwacji odwołań do tabeli/widoków lub poczynań użyszkodników. Do tego służy audyt (również opisany na tym blogu)
Uprzedzając kolejne pytanie: Tak. Inni niż SYS użytkownicy również mogą korzystać z tej metody, jednak najpierw należy im nadać uprawnienia do pakietu dbms_trace oraz w.w. widoków (patrz artykuł o uprawnieniach w kursie SQL na tym blogu) :)

sobota, 25 czerwca 2011

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.