Pakiety wbudowane i dynamiczny SQL

Wstęp

Oracle dostarcza całego zestawu specjalnych narzędzi do stosowania w PL/SQL w postaci pakietów wbudowanych. Każdy taki pakiet zawiera pewną zamkniętą funkcjonalność w postaci procedur, funkcji, typów, wyjątków itp.

Właścicielem pakietów wbudowanych (podobnie jak innych obiektów systemowych) jest użytkownik SYS. Inni użytkownicy korzystają z synonimów publicznych, aby odwoływać się do zawartości pakietów wbudowanych. Zdecydowana większość pakietów tworzy się automatycznie podczas tworzenia słownika systemowego w trakcie procesu zakładania bazy danych, część jednak, w razie takiej potrzeby należy utworzyć „ręcznie” uruchamiając odpowiedni skrypt na użytkowniku SYS. Ilość wszystkich pakietów wbudowanych jest ogromna (przeszło 100).

Rozdział poniższy ma na celu zapoznanie z najbardziej popularnymi z nich, przy czym nie zostanie w nim przedstawiona pełna funkcjonalność (tej należy szukać w dokumentacji), ale zostaną tylko wzmiankowane problemy, do rozwiązania których mogą być przydatne pakiety wbudowane.

Dynamiczny SQL

Dynamiczny SQL pozwala na następujące rzeczy, niedostępne w standartowym PL/SQL:

• Tworzenie i wykonywanie instrukcji DDL ( Data Definition Language ) takich jak np. tworzenie tabel, tworzenie indeksów. Instrukcje te normalnie nie są dozwolone w PL/SQL'u.
• Modyfikowanie i tworzenie poleceń SQL, np. przez parametry użytkownik może wykonując jedną procedurę w dynamicznym PL/SQL'u uruchamiać polecenie na kilka sposobów przekazując warunki dla klauzuli WHERE lub określając różne sposoby sortowania danych.
• Odwoływanie się do obiektów, które jeszcze nie istnieją w momencie kompilacji podprogramu.

Dynamiczny SQL można realizować na dwa sposoby:

• Przy pomocy pakietu wbudowanego dbms_sql. Dostarcza on pełną funkcjonalność pozwalającą na dynamiczne tworzenie i wykonywanie instrukcji SQL oraz PL/SQL wewnątrz programów napisanych w standardowym PL/SQL'u. Pakiet oferuje procedury i funkcje pozwalające na konstruowanie kodu w czasie wykonywania programu, dynamiczne tworzenie i uruchamianie instrukcji SQL. Pakiet DBMS_SQL jest generowany automatycznie w momencie tworzenia bazy danych. Pakiet można także wygenerować przez uruchomienie skryptu dbmssql.sql
• Przy pomocy „Native Dynamic SQL”. „Native Dynamic SQL” jest dostępny dopiero w Oracle 8i i dostarcza komendę PL/SQL EXECUTE IMEDIATE komenda; która powoduje wykonanie się komendy dynamicznie umieszczonej w zmiennej znakowej komenda.

Przykład

-- Wykonanie najprostszego typu polecenia:
-- Zmodyfikowanie zarobków wszystkim pracownikom na stanowisku
-- AGENT korzystając z dbms_sql.
CREATE OR REPLACE PROCEDURE dynamic_dbms 
IS
  cursor_handle number;
  rows_updated number;
BEGIN
  -- otwarcie kursora
  cursor_handle := dbms_sql.open_cursor;
  -- Parsowanie polecenia
  dbms_sql.parse(cursor_handle, 
       ‘UPDATE emp ‘ ||
       ‘   SET sal = sal + 1000 ‘ ||
       ‘ WHERE job = ‘’AGENT’’’, 
       dbms_sql.native);
  -- wykonanie polecenia
    rows_updated := dbms_sql.execute(cursor_handle);
  -- zamknięcie kursora
  dbms_sql.close_cursor(cursor_handle);
END;
                        

Tylko w Oracle 8i

-- Podobnie jak powyżej, ale korzystając z Native Dynamic SQL
CREATE OR REPLACE PROCEDURE dynamic_native 
IS
BEGIN
  EXECUTE IMEDIATE ‘UPDATE emp ‘ ||
                   ‘   SET sal = sal + 1000 ‘ ||
                   ‘ WHERE job = ‘’AGENT’’’;
END;
                        

Warto pamiętać, że pakiet dbms_sql posiada również procedury służące do pobierania z bazy danych wartości typu LONG, oraz w przeciwieństwie do native dynamic sql może realizować przetwarzanie tablicowe. W przypadku stosowania dynamicznego SQL’a prawa do wykonywanych dynamicznie poleceń powinny być przyznane nie poprzez rolę, ale bezpośrednio poleceniem grant.

Procedury są wykonywane w kontekście uprawnień właściciela procedury. Skoro właścicielem pakietu dbms_sql jest SYS, mogłoby to rodzić poważne niebezpieczeństwo, dlatego pakiet ten jest wykonywany w kontekście użytkownika wołającego podprogram (AUTHID CURRENT_USER).