Zmienne bindowane w SQL, PL/SQL

Aby zrozumieć zmienne bindowane, wyobraź sobie sytuację, w której posiadasz tabelę słownikową, do której sięgasz dość często. Takie rozwiązanie w SQLu będzie mogło wyglądać następująco:

SELECT * FROM finanse.income WHERE inc_id = 1;
SELECT * FROM finanse.income WHERE inc_id = 2;
SELECT * FROM finanse.income WHERE inc_id = 3;
SELECT * FROM finanse.income WHERE inc_id = 4;

Rozwiązanie, choć wydaje się poprawne i zwraca oczekiwane wyniki, to dla bazy jest nieoptymalne i męczące. Dla każdego pojedynczego zapytania, choć wyglądają całkiem podobnie, rezerwowana jest oddzielna przestrzeń współdzielona (shared pool) i wyliczany jest nowy plan wykonania. To wszystko zabiera zasoby i cenny czas.

Na poniższym screenie pokazane są zapytania z tabeli v$sql, które poleciały wykonując powyższe selecty. Jak widać, dla każdego zapytania odłożono oddzielny rekord i przydzielono SQL_ID.

Zapytania z v$sql
Zapytania bez bindowania

Oracle zdaje sobie sprawę z takiego marnotrawienia zasobów i dlatego udostępnia mechanizm bindowania zmiennych. Rozwiązanie to polega na zastąpieniu tych elementów w zapytaniach SQL, które warunkują zwrócenie wyników, na znacznik bindowania.

Wykorzystując ten mechanizm powyższe rozwiązanie może wyglądać następująco:

VARIABLE v_inc_id NUMBER; --deklaracja zmiennej

BEGIN
:v_inc_id := 1; --przypisanie wartości
END;

SELECT * FROM finanse.income WHERE inc_id = :v_inc_id; --wykorzystanie zmiennej bindowanej

BEGIN
:v_inc_id := 2; --przypisanie wartości
END;

SELECT * FROM finanse.income WHERE inc_id = :v_inc_id; --wykorzystanie zmiennej bindowanej

BEGIN
:v_inc_id := 3; --przypisanie wartości
END;

SELECT * FROM finanse.income WHERE inc_id = :v_inc_id; --wykorzystanie zmiennej bindowanej

BEGIN
:v_inc_id := 4; --przypisanie wartości
END;

SELECT * FROM finanse.income WHERE inc_id = :v_inc_id; --wykorzystanie zmiennej bindowanej

Teraz po wykonaniu powyższego, rekordy w tabeli v$sql będą wyglądać następująco. Jak widać na cztery wykonane zapytania, w tabeli v$sql powstał tylko jeden rekord, w którym jako warunek pojawia się zmienna bindowana :v_inc_id. Rozwiązanie takie jest tym, które jest optymalne i oczekiwane zarówno przez SZBD (System Zarządzania Bazą Danych), jak i przez kierownika projektu, który na pewno będzie się czepiał takich szczegółów 😛

Zapytania z bindowaniem

No dobra, ale czy bindowanie zmiennych jest dostępne również w PL/SQL? Jak najbardziej i jest wykorzystywane zawsze wtedy, kiedy w zapytaniach korzysta się ze zmiennych. Przykład tego samego rozwiązania tylko umieszczony w bloku anonimowym.

DECLARE
v_income finanse.income%ROWTYPE;
v_id NUMBER;
BEGIN
v_id := 1;
SELECT * INTO v_income FROM finanse.income WHERE inc_id = v_id;
v_id := 2;
SELECT * INTO v_income FROM finanse.income WHERE inc_id = v_id;
v_id := 3;
SELECT * INTO v_income FROM finanse.income WHERE inc_id = v_id;
v_id := 4;
SELECT * INTO v_income FROM finanse.income WHERE inc_id = v_id;
END;
/

A od strony v$sql jego wykonanie będzie wyglądać właśnie tak

Zapytanie wykorzystujące zmienne bindowane

Jak widać różnica pomiędzy jawnym wykorzystaniem zmiennej bindowanej, a tej nadanej przez bazę jest tylko taka, że w jawnym sami nazywamy zmienne bindowane, podczas gdy w niejawnym nazewnictwo jest przydzielane przez system.

//