SQL W JĘZYKU PL/SQL
Strukturalny język zapytań SQL określa sposób manipulowania danymi w bazie danych. Konstrukcje proceduralne języka PL/SQL stają się bardziej użyteczne w połączeniu z mocą przetwarzania języka SQL, ponieważ pozwalają wtedy programom PL/SQL na manipulowanie danymi w bazie danych Oracle.
Instrukcje SQL
Instrukcje SQL można podzielić na pięć kategorii, które zostały przedstawione w poniższej tabeli.
Tabela. Kategorie instrukcji SQL
Kategoria Przykładowe instrukcje SQL
Instrukcje języka manipulowania danymi DML (Data Manipulation Language)
SELECT, INSERT, UPDATE, DELETE, SET TRANSACTION, EXPLAIN PLAN
Instrukcje języka definicji danych DDL (Data Definition Language)
DROP, CREATE, ALTER, GRANT, REVOKE
Instrukcje sterowania transakcją COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION ALTER SESSION, SET ROLE
Instrukcje sterowania sesji ALTER SESSION, SET ROLE
Instrukcje sterowania systemu ALTER SYSTEM
Instrukcje języka manipulowania danymi DML (Data Manipulation Language) – służą do zmieniania danych w tabelach lub danych zapytań w tabeli bazy danych, ale nie umożliwiają zmiany struktury tabeli lub innych obiektów.
Instrukcje języka definicji danych DDL (Data Definition Language) – służą do tworzenia, usuwania lub zmieniania struktury obiektu schematu. Polecenia, które zmieniają uprawnienia do obiektów schematu, są również instrukcjami DDL.
Instrukcje sterowania transakcją – gwarantują zachowanie spójności danych dzięki zorganizowaniu instrukcji SQL w logiczne transakcje, których wykonanie jako jednostki kończy się powodzeniem lub niepowodzeniem.
Instrukcje sterowania sesji – służą do zmieniania ustawień dla pojedynczego połączenia z bazą danych, np. do aktywacji śledzenia sesji SQL.
Instrukcje sterowania systemu – służą do zmieniania ustawień dla całej bazy danych, np. do aktywowania lub dezaktywowania procesu archiwizacji.
Wykorzystanie instrukcji SQL w języku PL/SQL
Jedynymi instrukcjami SQL, które są dozwolone w programie PL/SQL, są:
instrukcje DML
instrukcje sterowania transakcjami.
Niedozwolone są:
przede wszystkim instrukcje DDL,
ale także i niektóre instrukcje DML nie są dozwolone (np. instrukcja EXPLAIN PLAN).
Aby wyjaśnić dlaczego te, a nie inne instrukcje SQL są dozwolone i niedozwolone w języku PL/SQL, konieczna jest znajomość założeń projektowych przyjętych dla języka PL/SQL. Ogólnie język
programowania może wiązać zmienne na dwa sposoby: przez wiązanie wczesne lub wiązanie późne.
Wiązanie zmiennej jest procesem identyfikowania lokacji pamięci skojarzonej z identyfikatorem programu.
W języku PL/SQL wiązanie uwzględnia również sprawdzenie istnienia zezwolenia na uzyskanie dostępu do odwoływanego obiektu schematu w bazie danych. W przypadku języka, w którym stosuje się wiązanie wczesne, wiązanie zmiennej następuje podczas etapu kompilacji, natomiast w przypadku języka, w którym stosuje się wiązanie późne, proces wiązania zmiennej jest odkładany aż do czasu uruchomienia programu.
Uwzględnienie procesu wiązania wczesnego oznacza, że etap kompilacji będzie trwać dłużej (ponieważ musi być wykonane wiązanie zmiennych), ale sam program będzie wykonywany szybciej, ponieważ wiązanie będzie już zakończone. Wiązanie późne skraca czas kompilacji, ale wydłuża czas wykonywania programu.
Język PL/SQL celowo zaprojektowano w taki sposób, aby zastosować wiązanie wczesne. Decyzję tę podjęto w celu zapewnienia jak najszybszego wykonywania bloku, ponieważ wszystkie obiekty bazy danych są sprawdzane przez kompilator. Jest to sensowne rozwiązanie, ponieważ bloki PL/SQL są składowane w bazie danych za pomocą procedur, funkcji, pakietów i wyzwalaczy. Obiekty te są składowane w skompilowanej formie, a zatem w razie potrzeby mogą być ładowane bezpośrednio z bazy danych do pamięci i uruchamiane.
Konsekwencją takiej decyzji projektowej jest zakaz stosowania instrukcji DDL. Instrukcje DDL modyfikują obiekt bazy danych, więc zachodzi konieczność ponownego sprawdzania zezwoleń, które wymagałyby ponownego wiązania identyfikatorów, a ten proces jest przeprowadzany podczas kompilacji.
Aby lepiej to zilustrować, przeanalizujmy następujący, hipotetyczny blok PL/SQL:
BEGIN
CREATE TABLE tabela_tymcz
(wart_num NUMBER, wart_znak CHAR(10));
INSERT INTO tabela_tymcz(wart_num, wart_znak) VALUES(10, ‘Tekst!’);
END;
W celu kompilacji tego bloku identyfikator tabela_tymcz wymaga dowiązania. Podczas tego procesu następuje sprawdzenie faktu istnienia tabeli. Jednak tabela nie może istnieć przed uruchomieniem bloku. Z powyższego przykładu wynika, że omawiany blok nie może zostać skompilowany, a zatem nie ma sposobu na jego uruchomienie.
Instrukcje sterowania transakcji są jedynymi instrukcjami SQL, które nie mają możliwości modyfikowania obiektów schematu lub uprawnień do obiektów schematu i w ten sposób są jedynymi poprawnymi
instrukcjami SQL stosowanymi w języku PL/SQL.
Stosowanie instrukcji DML w języku PL/SQL
Dozwolonymi instrukcjami DML w języku PL/SQL są instrukcje: SELECT, INSERT, UPDATE, DELETE.
Składnia instrukcji SELECT w języku PL/SQL jest następująca:
SELECT *
element_listy_wyboru
INTO rekord_PL/SQL FROM
zmienna
odnośnik_do_tabeli
; x
WHERE klauzula_where
Poniższa tabela zawiera opis wszystkich elementów składni instrukcji SELECT.
Klauzula instrukcji SELECT
Opis
element_listy_wyboru Kolumna (lub wyrażenie) do wybrania. Każdy element listy wyboru jest oddzielony przecinkiem i może być opcjonalnie identyfikowany przez alias (zmiennik). Cały zbiór elementów listy w instrukcji SELECT nazywa się listą wyboru. Znak gwiazdki (*) w składni jest skrótem zastępującym cały wiersz. W ten sposób są zwracane poszczególne pola w tabeli w kolejności, w jakiej pola zdefiniowano.
zmienna Zmienna PL/SQL, do której będzie przekazany element listy wyboru. Każda zmienna powinna być zgodna ze swoim skojarzonym elementem wyboru. Dlatego elementy listy oraz zmienne wyjściowe powinny istnieć w tej samej liczbie.
rekord_PL/SQL Może być stosowany zamiast listy zmiennych. Rekord powinien zawierać pola, które odpowiadają elementom z listy wyboru, ale również pozwalają na łatwiejszą manipulację zwracanymi danymi. Rekordy łączą powiązane pola w jednej jednostce składniowej. W ten
sposób można manipulować tymi polami zarówno jako grupą, jak również indywidualnie.
Jeżeli lista wyboru jest znak gwiazdki (*) oraz źródłem danych jest pojedyncza tabela, to ten rekord może być definiowany jako odnośnik_do_tabeli%ROWTYPE.
odnośnik_do_tabeli Identyfikuje tabelę, z której mają być pobrane dane. jednym zapytaniu można określić więcej niż jeden odnośnik_do_tabeli. W takim przypadku argumenty należy oddzielić przecinkami.
Zapytanie pobierające dane z kilku tabel nazywa się powiązaniem. Argument
odnośnik_do_tabeli nie musi być nazwą tabeli bazy danych. Może być synonimem, tabelą w odległej bazie danych, określonej przez powiązanie z bazą danych, perspektywą, tabelą zagnieżdżoną (od systemu Oracle9i)
klauzula_where Kryterium tego zapytania. Klauzula ta identyfikuje wiersz, który będzie zwrócony przez zapytanie. Kryterium składa się z warunków logicznych (boolowskich) łączonych operatorami logicznymi. Klauzula ta definiuje, które instrukcje tworzą tzw. zestaw aktywny – zestaw wierszy zwracanych przez zapytanie SELECT, lub na których są wykonywane instrukcje UPDATE or DELETE.
Istnieje znacznie więcej klauzul dla instrukcji SELECT, np. klauzule ORDER BY oraz GROUP BY.
Instrukcje SELECT można także łączyć z operatorami, takimi jak: UNION, MINUS.
Instrukcja SELECT według podanej powyżej składni powinna zwracać najwyżej jeden wiersz. Klauzula WHERE jest sprawdzana dla każdego wiersza w tabeli. Jeżeli odpowiada ona więcej niż jednemu wierszowi, PL/SQL zwraca następujący komunikat o błędzie:
ORA-1427: Zapytanie o jeden wiersz zwraca więcej niż jeden wiersz
W takim przypadku do osobnego pobrania każdego wiersza konieczne jest zastosowanie kursora.
Poniższy przykład ilustruje sposób zastosowania dwóch różnych instrukcji SELECT:
DECLARE
z_RekordTowar towary%ROWTYPE;
z_KlientNazwa klienci.nazwa%TYPE;
z_KlientKraj klienci.kraj%TYPE;
BEGIN
/* Pobranie jednego rekordu z tabeli Produkty i zapisanie w rekordzie z_RekordProdukt. Zauważmy, że klauzula WHERE odpowiada tylko jednemu wierszowi tabeli, a zapytanie zwraca wszystkie pola w tabeli Produkty (ponieważ stosowany jest znak (*). W ten sposób ładowany rekord jest definiowany jako
produkty%ROWTYPE. */
SELECT *
INTO z_RekordTowar FROM towary
WHERE nr_t = 15;
/* Pobranie dwóch pól z tabeli Klienci i zapisanie ich w zmiennych z_KlientNazwa i z_KlientKraj. Zatem ponownie klauzula WHERE odpowiada tylko jednemu wierszowi tabeli.*/
SELECT nazwa, kraj
INTO z_KlientNazwa, z_KlientKraj FROM Klienci
WHERE nr = 10;
END;
Instrukcja INSERT
Składnia instrukcji INSERT jest następująca:
INSERT INTO odnośnik_do_tabeli
(nazwa kolumny)
VALUES ( wyrażenie ) ; x
instrukcja_select
Klauzula odnośnik_do_tabeli odwołuje się do tabeli Oracle (albo do określonych perspektyw),
nazwa_kolumny odwołuje się do kolumny w tabeli, a wyrazenie jest wyrazeniem SQL lub PL/SQL. Jeżeli instrukcja INSERT zawiera część instrukcja_select, to elementy listy instrukcji SELECT powinny
odpowiadać kolumnom, do których mają być wstawiane dane. Zapytanie takiego typu określa się jako podzapytanie, ponieważ umieszczono je wewnątrz innej instrukcji SQL.
Podobnie jak w przypadku instrukcji SELECT, instrukcja INSERT może również przyjmować nieco inne formy, np. zastosować podzapytanie jako część klauzuli VALUES.
Poniższe przykłady ilustrują prawidłowy sposób użycia instrukcji INSERT:
DECLARE
z_IdTowar towary%TYPE;
BEGIN
-- Pobranie nowego identyfikatora produktu SELECT towary_sekwencja.NEXTVAL
INTO z_IdTowar FROM dual;
-- Dodanie wiersza do tabeli towary
INSERT INTO towary(nr_t, nazwa_t, cena_jedn) VALUES(z_IdTowar, ‘Napój porzeczkowy’, 3,10);
/* Dodanie drugiego wiersza do tabeli Produkty,
ale z zastosowaniem numeru sekwencji bezpośrednio w instrukcji INSERT */
INSERT INTO towary(nr_t, nazwa_t, cena_jedn)
VALUES(towary_sekwencja.NEXTVAL, ‘Napój porzeczkowy’, 3,10);
W następnym przykładzie przedstawiono nieprawidłowy sposób wykonania polecenia INSERT. Elementy listy instrukcji SELECT podzapytania nie odpowiadają kolumnom, które mają być wstawiane. Taka instrukcja powoduje zwrócenie błędu Oracle ORA-00947: niewystarczająca liczba wartości.
INSERT INTO towary SELECT * from klienci;
Kolejny przykład przedstawia prawidłowo wydaną instrukcję INSERT. Przez wstawienie kopii każdego wiersza następuje podwojenie wielkości tabeli towary (w przypadku, gdy nie zostałyby naruszone więzy integralności, np. związane z kluczem tabeli towary):
INSERT INTO towary SELECT * from towary;
Instrukcja UPDATE
Składnia instrukcji UPDATE jest następująca:
UPDATE odnośnik_do_tabeli SET
(nazwa kolumny) = wyrazenie
( nazwa kolumny ) = (instrukcja_select)
WHERE klauzula_where ; x
CURRENT OF kursor
klauzula odnośnik_do_tabeli odwołuje się do tabeli bazy danych Oracle (albo do odpowiednio okreslonych perspektyw),
nazwa_kolumny jest nazwą kolumny, której wartość ma być zmieniona, wyrazenie jest wyrażeniem SQL,
jeśli instrukcja UPDATE zawiera część instrukcja_select, to elementy listy wyboru powinny odpowiadać kolumnom w klazuli SET,
specjalną klazulę CURRENT OF kursor stosuje się wraz z definicją kursora.
W poniższym przykładzie zilustrowano zastosowanie instrukcji UPDATE:
DECLARE
z_IdTowar towary.nr_t%TYPE:=25;
z_DodajMagazyn NUMBER:=100;
BEGIN
-- Ta instrukcja UPDATE doda 100 do pola Stan_magazyn dla towaru o nr_t = 20 UPDATE towary
SET stan_magazyn:= Stan_magazyn + z_DodajMagazyn WHERE nr_t = z_IdTowar;
/* Ta instrukcja UPDATE spowoduje zmodyfikowanie obu kolumn tabeli tabela_tym dla wszystkich wierszy. */
UPDATE tabela_tym
SET kol_num = 1, kol_znak = ‘Nowy tekst’;
END;
Instrukcja DELETE
Instrukcja DELETE usuwa wiersze z tabeli bazy danych. Klauzula WHERE wskazuje, które wiersze mają być usunięte. Składnia instrukcji DELETE jest następująca:
DELETE odnośnik_do_tabeli
FROM alias
; x
WHERE klauzula_where
CURRENT OF cursor
klauzula odnośnik_do_tabeli odwołuje się do tabeli bazy danych Oracle (albo do odpowiednio okreslonych perspektyw),
klazula_where definiuje zbiór wiersz, które mają być usunięte,
specjalną klauzulę CURRENT OF kursor stosuje się wraz z definicją kursora.
Poniższy przykład ilustruje kilka przypadków poprawnego użycia instrukcji DELETE.
DECLARE
z_TowarWycofany towary.wycofany%TYPE;
BEGIN
-- Usunięcie produktów wycofanych z tabeli towary z_TowarWycofany:= FALSE;
DELETE FROM towary
WHERE Wycofany= z_TowarWycofany;
END;
UWAGA: Instrukcja DELETE w powyższym bloku powoduje powstanie błędów związanych z naruszeniem ograniczenia nałożonych na tabelę produkty, co wiąże się z naruszeniem więzów integralności.
Klauzula WHERE
Wraz z instrukcjami SELECT, UPDATE oraz DELETE stosuje się klauzulę WHERE będącą integralną częścią działań wykonywanych przez te instrukcje. Klauzula ta definiuje, które instrukcje tworzą zestaw aktywny – zestaw wierszy zwracanych przez zapytanie (SELECT) lub na których są wykonywane instrukcje UPADTE oraz DELETE.
Klauzula WHERE składa się z warunków połączonych operatorami logicznymi: AND, OR oraz NOT.
Warunki zwykle przyjmują formę porównań, tak jak w poniższym przykładzie:
DECLARE
z_Wycofany towary.wycofany %TYPE:=’TAK’;
BEGIN
-- Usunięcie wszystkich towarów wycofanych DELETE FROM towary
WHERE wycofany=z_Wycofany;
END;
Powyższy, przykładowy blok usuwa wszystkie te wiersze z tabeli towary, dla których warunek został oceniony na wartość TRUE (tzn. dla których kolumna wycofany = ‘TAK’). Przy tego typu porównaniach należy zwracać uwagę na właściwe stosowanie nazw zmiennych oraz na sposób porównywania znaków.
Nazwy zmiennych
Na potrzeby kolejnego przykładu przyjęto, że w poprzednim bloku programu zmieniono nazwę zmiennej z_Wycofany na Wycofany:
DECLARE
Wycofany towary.wycofany%TYPE:=’TAK’;
BEGIN
-- Usunięcie wszystkich towarów (nie tylko tych wycofanych) DELETE FROM towary
WHERE wycofany=Wycofany;
END;
Ta prosta zmiana radykalnie wpływa na otrzymane wyniki wykonania tej instrukcji – tak zmodyfikowany blok usunie wszystkie wiersze z tabeli produkty, a nie tylko te, dla których zachodzi równość Wycofany =
‘TAK’. Wynika to ze sposobu parsowania identyfikatorów w instrukcji SQL. Kiedy mechanizm PL/SQL napotka na warunek, taki jak:
1wyrazenie = 2wyrazenie,
najpierw następuje sprawdzenie identyfikatorów 1wyrazenie oraz 2wyrazenie w celu ustalenia, czy odpowiadają one kolumnom tabeli, na których jest wykonana dana operacja. Następnie identyfikatory sprawdza się, czy są zmiennymi w bloku PL/SQL. Język PL/SQL nie rozróżnia małych i dużych liter, zatem w poprzednim bloku obydwa identyfikatory wycofany oraz Wycofany są kojarzone z kolumną w tabeli produkty, a nie ze zmienną. Wynikiem sprawdzenia tego warunku dla każdego wiersza tabeli będzie wartość TRUE i dlatego wszystkie wiersze zostaną usunięte.
Jeżeli blok posiada etykietę, można w dalszym ciągu użyć tej samej nazwy dla zmiennej jak dla kolumny tabeli – dzięki nadanej etykiecie do odwołania do zmiennej. Przedstawiony poniżej blok daje pożądany efekt – usuwa tylko te wiersze, dla których Wycofany = ‘TAK’:
<<l_BlokDelete>>
DECLARE
Wycofany towary.wycofany%TYPE:=’TAK’;
BEGIN
-- Usunięcie wszystkich towarów wycofanych DELETE FROM towary
WHERE wycofany= l_BlokDelete.Wycofany;
END;
Mimo, ze ten sposób prowadzi do pożądanego wyniku, jednak stosowanie tej samej nazwy dla zmiennej PL/SQL i dla kolumny tabeli nie jest cechą dobrego stylu programowania.
Porównania znaków
W systemie Oracle mogą być zastosowane dwa różne rodzaje porównań wartości dwóch znaków: z
dopełnieniem odstepu (blank-padded) lub bez dopełnienia odstępu (non-blank-padded). Typy te różnią się w sposobie porównywania ciągów znaków o różnych długościach. Załóżmy, iż porównujemy dwa ciągi
znaków: ciagznakow1 oraz ciagznakow2. Do porównania z dopełnieniem odstępu stosuje się następujący algorytm:
1. Jeżeli ciąg znaków ciagznakow1 oraz ciagznakow2 są różnej długości, krótszy ciąg należy dopełnić znakami odstępu (spacjami), tak aby obydwa miały te samą długość.
2. Następnie porównuje się każdy ciąg znaków, znak po znaku, zaczynając od lewej strony. Przykładowo, w ciągu znaków ciagznakow1 znakiem jest znak1, a w ciągu znaków ciagznakow2 – znak2.
3. Jeżeli ASCII(znak1)<ASCII(znak2), to ciagznakow1 < ciagznakow2. Jeżeli ASCII(znak1)>ASCII(znak2), to ciagznakow1 > ciagznakow2. Gdy ASCII(znak1)=ASCII(znak2), wówczas odpowiednio w ciągu znaków ciagznakow1 oraz ciagznakow2 przechodzi się do następnego znaku.
4. Jeżeli jest możliwe osiągnięcie końców znaków ciagznakow1 oraz ciagznakow2, to ciągi te są równe.
Przy zastosowaniu algorytmu porównania z dopełnieniem odstępu wszystkie poniższe warunki zwrócą wartość TRUE:
‘abc = ‘abc’
‘abc ‘ = ‘abc‘ -- Należy zauważyć, iż w pierwszym ciągu znaków występują na końcu spacje
‘ab’ < ‘abc’
‘abcd’ > ‘abcc’
Algorytm porównania bez dopełnieniem odstępu jest nieco inny:
1. Należy porównać każdy ciąg znaków, znak po znaku, zaczynając od lewej strony. Przykładowo, w ciągu znaków ciagznakow1 znakiem jest znak1, a w ciągu znaków ciagznakow2 – znak2.
2. Jeżeli ASCII(znak1)<ASCII(znak2), to ciagznakow1 < ciagznakow2. Jeżeli ASCII(znak1)>ASCII(znak2), to ciagznakow1 > ciagznakow2. Gdy ASCII(znak1)=ASCII(znak2), wówczas należy przejść do
następnego znaku odpowiednio w ciągach znaków ciagznakow1 oraz ciagznakow2.
3. Jeżeli ciąg znaków ciagznakow1 kończy się przed ciągiem znaków ciagznakow2, to ciagznakow1 <
ciagznakow2. Gdy ciagznakow2 kończy się przed ciągiem znaków ciagznakow1, to ciagznakow1 >
ciagznakow2.
Przy zastosowaniu algorytmu porównania bez dopełnieniem odstępu wszystkie poniższe warunki zwrócą wartość TRUE:
‘abc = ‘abc’
‘ab’ < ‘abc’
‘abcd’ > ‘abcc’
Jednak poniższe porównanie bez dopełnieniem odstępu zwróci wartość FALSE, ponieważ ciągi znaków są różnej długości. Jest to podstawowa różnica pomiędzy powyższymi dwoma metodami porównań:
‘abc ‘ = ‘abc‘ -- Należy zauważyć, iż w pierwszym ciągu znaków występują na końcu spacje
Po zdefiniowaniu tych dwóch różnych metod porównań warto zastanowić się, kiedy należy stosować każdą z nich. Język PL/SQL wykorzystuje metody porównania z dopełnieniem odstępu tylko wtedy, gdy obydwa
porównywane ciągi znaków są stałej długości. Jeżeli dane ciągi znaków są różnej długości, stosuje się metodę porównywania bez dopełnienia odstępu. Typ danych CHAR określa ciągi znaków o stałej długości, a typ VARCHAR2 określa ciągi znaków o zmiennej długości. Stałe znakowe (objęte znakami apostrofu) są zawsze uważane za ciągi znaków o stałej długości.
Jeżeli dana instrukcja nie jest wykonywana na poprawnych wierszach, należy sprawdzić typy danych użyte w klauzuli WHERE. Wykonanie poniższego bloku nie spowoduje usunięcia jakichkolwiek wierszy, ponieważ zmienna z_Wycofany jest typu CHAR, a nie typu VARCHAR2:
DECLARE
z_Wycofany VARCHAR2(4);
BEGIN
z_Wycofany:=’Tak ’; -- Zauważmy, iż na końcu łańcucha występuje spacja -- Usuniecie wszystkich produktów wycofanych
DELETE FROM towary
WHERE wycofany=z_Wycofany;
END;
Kolumna Wycofany tabeli towary jest typu CHAR. Wszystkie produkty wycofane posiadają wartość ‘TAK’
dla kolumny wycofany. Ponieważ zmienna z_Wycofany = ‘Tak ‘ zawiera końcowy znak odstępu (spację) i zdefiniowano dla niej typ danych o zmiennej długości, to instrukcja DELETE nie będzie miała żadnego wpływu na wiersze.
Aby klauzula WHERE dała pożądany efekt, dla zmiennych w bloku PL/SQL należy zdefiniować ten sam typ danych jak dla porównywanych kolumn bazy danych. Taki efekt gwarantuje zastosowanie atrybutu %TYPE.
Klauzula RETURNING
Często potrzebne są informacje na temat wierszy modyfikowanych przez instrukcję DML już po wydaniu instrukcji, np. identyfikator wiersza wprowadzonego do bazy danych. Jednym ze sposobów na uzyskanie tej informacji jest zastosowanie instrukcji SELECT po wykonaniu instrukcji. Jednak w takim przypadku
potrzebna jest druga instrukcja SQL oraz kolejne odwołanie do jądra systemu RDBMS. Od systemu Oracle8 istnieje krótszy sposób – klauzula RETURNING. Klauzula ta jest poprawna na końcu dowolnej instrukcji DML i jest wykorzystywana w celu uzyskania informacji na temat wiersza lub wierszy, których dotyczyło działanie instrukcji DML.
Składnia klauzuli RETURNING jest następująca:
RETURNING wyrazenie INTO zmienna x
gdzie wyrazenie jest poprawnym wyrażeniem języka PL/SQL lub SQL, które może obejmować kolumny lub pseudokolumny bieżacej tabeli, natomiast zmienna jest zmienną języka PL/SQL, do której zostaną zapisane wyniki. Odpowiadające sobie zmienne i wyrażenia muszą być danymi zgodnych typów.
Poniższe przykłady prezentują wykorzystanie klauzuli RETURNING:
DECLARE
z_NowyRowid ROWID;
z_IdTowar towary.Id_prod%TYPE;
z_NazwaTowar towary.nazwa_t%TYPE;
z_CenaTowar towary.cena_jedn%TYPE;
BEGIN
/* Wprowadzenie nowego wiersza do tabeli towary i jednoczesne pobranie identyfikatora rowid nowego wiersza. */
INSERT INTO towary(nr_t, nazwa_t, cena_jedn)
VALUES(towary_sekwencja.NEXTVAL, ‘Mleko’, 1,45) RETURNING rowid INTO z_NowyRowid;
DBMS_OUTPUT.PUT_LINE(‘Identyfikator rowid nowego wiersza to: ‘||z_NowyRowid);
/* Uaktualnienie tego nowego wiersza – zwiększenie wartości pola stan_magazyn i jednocześnie uzyskanie nazwy towaru i jego ceny jednostkowej. */
UPDATE towary
SET stan_magazyn = stan_magazyn + 12 WHERE rowid = z_NowyRowid
RETURNING nt_t, nazwa_t, cena_jedn INTO z_IdTowar, z_NazwaTowar, z_CenaJedn;
DBMS_OUTPUT.PUT_LINE(‘Nazwa towaru:‘||z_NazwaTowar||’ i jego cena: ‘||z_CenaTowar||’zł’);
/* Usunięcie wiersza i uzyskanie identyfikatora Id usuniętego towaru. */
DELETE FROM towary
WHERE rowid = z_NowyRowid;
DBMS_OUTPUT.PUT_LINE(‘Identyfikator Id usuniętego wiersza wynosi: ‘||z_IdTowar);
END;
/
Identyfikator rowid nowego wiersza: AAAF4MFAFAAAAATAFM Nazwa produktu: ‘Mleko’ i jego cena: 1,45 zł
Identyfikator Id usuniętego wiersza wynosi: 30021 PL/SQL procedure successfully completed
