Pakiet DBMS_SQL

Pakiet DBMS_SQL możemy wykorzystywać alternatywnie do klauzuli EXECUTE IMMEDIATE. Każdą z czynności : otwarcie kursora, parsowanie zapytania, wykonanie zapytania i zamknięcie kursora wykonujemy tutaj ręcznie i osobno. To ma swoje zady i walety. Z jednej strony jest więcej pisania i ilość kodu gwałtownie wzrasta. Z drugiej strony mamy większą kontrolę nad wszystkim co się dzieje, kod jest nieco bardziej przejrzysty. Przeanalizujmy teraz porównianie obu tych technik.
Użycie EXECUTE IMMEDIATE :

To samo z użyciem pakietu DBMS_SQL:

W obu przypadkach musiałem konkatenować zapytanie, z tym że w pierwszym wykonanie całości sprowadza się do polecenia EXECUTE IMMEDIATE. W drugim mamy kilka innych poleceń, wywołań procedur i funkcji z pakietu DBMS_SQL. Przeanalizujmy je po kolei.



dbms_sql.open_cursor
To funkcja alokująca przestrzeń na przetwarzanie kursora. Zwraca numeryczą referencję do kursora (taki identyfikator, dzięki któremu wiadomo o który kursor chodzi).



Dbms_sql.parse
Procedura ta sprawdza poprawność semantyczną zapytania (sprawdza czy wpisałeś zapytanie czy np. „Suchą szosą szedł sobie Sasza” ). W przypadku operacji DDL procedura ta wykonuje polecenie! W takim przypadku dzieje się to już tutaj i polecenie execute nie jest obowiązkowe.
Pierwszy parametr to identyfikator kursora (uzyskany przy wywołaniu funkcji open_cursor), drugi to zapytanie które będzie wykonywane, trzeci to wersja SQL. Można w trzecim parametrze ustawić zgodność SQL z wersją np. 6.












dbms_sql.execute
Funkcja wykonuje zapytanie. Jest zbędna w przypadku operacji DDL. W przypadku operacji DML zwraca ilość wierszy które uległy zmianie. W przypadku klauzuli EXECUTE IMMEDIATE również moglibyśmy się dowiedzieć ilu wierszy dotyczyła zmiana, ale musielibysmy dodatkowo użyć klauzuli SQL%ROWCOUNT.



dbms_sql.close
Dealokuje przestrzeń w pamięci operacyjnej wykorzystywaną na potrzeby przetwarzania kursora.



Możemy też wykorzystać zmienne bindowane w obu przypadkach. Wersja z EXECUTE IMMEDIATE:

Wersja z DBMS_SQL:



declare
dep integer:=90;
mana integer:=100;
podwyzka number:=400;
cu_id integer;
ile integer;
begin
cu_id:=dbms_sql.open_cursor;
dbms_sql.parse(cu_id, 'update employees set salary=salary+:p where department_id=:d and manager_id=:m',dbms_sql.native);
dbms_sql.bind_variable(cu_id,':p',podwyzka);
dbms_sql.bind_variable(cu_id,':d',dep);
dbms_sql.bind_variable(cu_id,':m',mana);
ile:=dbms_sql.execute(cu_id);
dbms_sql.close_cursor(cu_id);

end;

To co się tutaj pojawiło nowego, to wywołanie procedury bind_variable. Służy ona do podawania wartości jakie mają trafić do zmiennych bindowanych. Pierwszy parametr to identyfikator kursora, drugi zmienna bindowana użyta w zapytaniu, trzeci wartość do wstawienia do tej zmiennej. W zasadzie w obu przypadkach zamiast zmiennych dep,mana i podwyżka mógłbym użyć równie dobrze wartości bezpośrednio.