PL/SQL
Przydatne polecenia wydawane z konsoli
Wypisanie nazw tabel dostępnych dla użytkownika
SELECT table_name FROM user_tables;
Wypisanie struktury tabeli lub widoku
Desc nazwa_tabeli
Włączenie / wyłączenie wyświetlania komunikatów:
SET ServerOutput ON /OF
Blok PL/SQL:
Blok (zwany wówczas anonimowym) instrukcji w PL/SQL może zostać podany z konsoli jako zestaw deklaracji i poleceń i użyty w czasie sesji. Nie jest zapisywany trwale jako obiekt.
Struktura bloku
DECLARE
deklaracje
BEGIN
instrukcje
EXCEPTION
obsługa wyjątków
END;
Deklaracje zmiennych:
NazwaZmiennej TypZmiennej [NOT NULL] [:=wyrażenie];
np.: nazwisko VARCHAR2(30) := `Kowalski';
Instrukcja SELECT w PL/SQL:
SELECT wyrażenie1, wyrażenie2, ...
INTO zmienna1, zmienna2, ...
FROM tabela1, tabela2....
Zapytanie musi zwracać 1 wiersz.
Instrukcja warunkowa:
IF warunek THEN
instrukcje
[ELSIF warunek THEN]
instrukcje
[ELSE]
instrukcje
END IF;
Wyświetlenie komunikatu:
DBMS_OUTPUT.PUT_LINE(komunikat);
Wywołanie błędu:
raise_application_error(-20500, `komunikat');
Zmienne systemowe:
SQL%ROWCOUNT - liczba zwróconych wierszy
SQL%FOUND - czy zapytanie zwróciło wiersz
SQLERRM - komunikat o błędzie
Uruchomienie procedury:
EXECUTE procedura (parametry)
EXEC procedura (parametry)
Deklaracja typu zmiennej przez odwołanie do typu (dziedziny) wiersza lub kolumny:
zmienna emp.ename%type - zmienna tego samego typu co dziedzina kolumny ename w tabeli emp
zmienna emp%rowtype - zmienna typu wierszowego; może na nią zostać podstawiony cały rekord z tabeli emp
Deklaracja i użycie kursora:
Kursor jest to bufor pamięci, do którego zapisywany jest wynik zapytania SQL (SELECT...) zdefiniowanego w deklaracji kursora. Kursor jest deklarowany i definiowany w sekcji DECLARE bloku lub procedury:
CURSOR nazwa_kursora IS SELECT ...;
Atrybuty kursora:
nazwa_kursora %FOUND - ostatnia instrukcja FETCH zwróciła wiersz
nazwa_kursora %NOTFOUND - ostatnia instrukcja FETCH nie zwróciła wiersza
nazwa_kursora %ROWCOUNT - ilość zwróconych dotychczas wierszy
nazwa_kursora %ISOPEN - określa, czy kursor jest otwarty
Otwarcie kursora i wczytanie wartości pobranych z bazy jest wykonywane po deklaracji BEGIN
otwarcie kursora:
OPEN nazwa_kursora;
pobranie kolejnego rekordu z bazy danych
FETCH nazwa_kursora INTO zmienna;
zamknięcie kursora
CLOSE nazwa
Kursorów używamy zwykle w pętli np.:
LOOP
FETCH nazwa INTO zmienna;
EXIT WHEN nazwa%NOTFOUND;
...
END LOOP
Procedury:
Struktura procedury:
CREATE [OR REPLACE] PROCEDURE nazwa_procedury (nazwa_parametru [IN/OUT/INOUT] TYP, ...) AS
deklaracje
BEGIN
kod procedury
END;
Funkcje:
CREATE [OR REPLACE] FUNCTION nazwa_funkcji (nazwa_par ametru[IN/OUT/INOUT] TYP, ...) RETURN TYP AS
deklaracje
BEGIN
kod funkcji
RETURN wyrażenie;
END;
Wypisanie tekstu procedury lub funkcji
COLUMN Line FORMAT 99999
COLUMN Text FORMAT A80
SET Pagesize 50
SET PAUSE On
SELECT u.Line, u.Text
FROM User_Source u
WHERE u.name = 'Nazwa_procedury';
Wyzwalacze:
Struktura wyzwalacza
CREATE [OR REPLACE] TRIGGER nazwa
{BEFORE / AFTER} specyfikacja instrukcji
ON tabela
[FOR EACH ROW]
blok PL/SQL
Specyfikacje instrukcji:
INSERT, DELETE, UPDATE
Można je łączyć np. BEFORE INSERT OR UPDATE
Dla UPDATE można określić pola, których modyfikowanie spowoduje uruchomienie triggera np. UPDATE OF pole1, pole2
Odwołanie do starej/nowej wartości w wyzwalaczu:
:OLD.pole
:NEW.pole
Zmienne logiczne określające, co spowodowało uruchomienie wyzwalacza:
UPDATING, INSERTING, DELETING
Wypisanie tekstu wyzwalacza
SELECT Trigger_Body
FROM User_Triggers
WHERE Trigger_Name = 'Nazwa_wyzwalacza';
albo
SELECT Trigger_Type, Table_Name, Triggering_event
FROM User_Triggers
WHERE Trigger_Name = 'Nazwa_wyzwalacza';
Tworzenie sekwensji:
CREATE SEQUENCE nazwa_sekwencji
[INCREMENT BY k]
[START WITH n];
Bieżąca i kolejna wartości sekwencji:
nazwa_sekwencji.currval
nazwa_sekwencji.nextval
Przykładowe procedury
Create or replace procedure Rezerwuj_VIP (ident number, pocz date, kon date, kat number)
as
b integer;
numo integer;
czy number;
wolny pokoje.id_pokoju%type;
Cursor wolne_pokoje is
SELECT id_pokoju
FROM pokoje
WHERE id_kategorii = kat AND NOT EXISTS
(SELECT * FROM rezerwacje,r_przydzielona
WHERE rezerwacje.id_rezerwacji = r_przydzielona.id_rezerwacji
AND r_przydzielona.id_pokoju = pokoje.id_pokoju
AND (kon BETWEEN rezerwacje.rez_od AND rezerwacje.rez_do
OR pocz BETWEEN rezerwacje.rez_od AND rezerwacje.rez_do));
Cursor znajdz is
SELECT goscie.id_goscia, r_przydzielona.id_pokoju, r_przydzielona.id_rezerwacji
FROM pokoje, rezerwacje, goscie, r_przydzielona
WHERE pokoje.id_kategorii = kat
AND r_przydzielona.id_pokoju = pokoje.id_pokoju
AND rezerwacje.id_rezerwacji = r_przydzielona.id_rezerwacji
AND rezerwacje.id_goscia = goscie.id_goscia
AND VIP = 0
AND pocz BETWEEN rezerwacje.rez_od AND rezerwacje.rez_do
ORDER BY rezerwacje.rez_od DESC;
zmiana znajdz%rowtype;
BEGIN
SELECT Count (1) INTO b
FROM goscie
WHERE id_goscia = ident;
If b > 0 Then
Open wolne_pokoje;
Fetch wolne_pokoje INTO wolny;
If wolne_pokoje%found Then
dbms_output.put_line ('Wolny jest pokoj nr '|| wolny);
SELECT nvl(Max(id_rezerwacji) + 1 , 1) INTO numo FROM rezerwacje;
INSERT INTO rezerwacje VALUES (numo, ident, pocz, kon);
INSERT INTO r_przydzielona VALUES(numo, wolny);
dbms_output.put_line ('Zarezerwowano wolny pokoj nr '|| wolny);
Else
dbms_output.put_line ('Nie ma wolnego pokoju tej kategorii');
SELECT VIP INTO czy FROM goscie WHERE id_goscia = ident;
If czy = 0 Then
dbms_output.put_line ('Wstawiamy do zamowien');
SELECT nvl(Max(id_rezerwacji) + 1, 1) INTO numo FROM rezerwacje;
INSERT INTO rezerwacje VALUES (numo, ident, pocz, kon);
INSERT INTO r_zamowiona VALUES (numo, kat);
Else -- to prawdziwy VIP
dbms_output.put_line ('To jest VIP!');
Open znajdz;
Fetch znajdz INTO zmiana;
If znajdz%found Then
SELECT id_rezerwacji INTO numo FROM rezerwacje
WHERE zmiana.id_rezerwacji= rezerwacje.id_rezerwacji;
INSERT INTO r_zamowiona VALUES( numo, kat);
DELETE FROM r_przydzielona WHERE id_rezerwacji = numo;
dbms_output.put_line('Wstawiamy do rezerwacji zamowionych goscia nr '
||zmiana.id_goscia);
SELECT nvl(Max(id_rezerwacji) + 1, 1) INTO b FROM rezerwacje;
INSERT INTO rezerwacje VALUES (b, ident, pocz, kon);
INSERT INTO r_przydzielona VALUES (b, zmiana.id_pokoju);
dbms_output.put_line('Wstawiamy do rezerwacji przydzielonych goscia nr '
||ident);
Else
dbms_output.put_line('Rezerwacja nie jest mozliwa');
SELECT nvl(Max(id_rezerwacji) + 1, 1) INTO b FROM rezerwacje;
INSERT INTO rezerwacje VALUES (b, ident, pocz, kon);
INSERT INTO r_zamowiona VALUES (b, kat);
End If;
End If;
End If;
Close wolne_pokoje;
Else
dbms_output.put_line('Nie ma takiego goscia');
End If;
End;
procedure Dodaj_Goscia (p_imie varchar2, p_nazwisko varchar2, p_dataur date,
p_adres varchar2, p_dowos varchar2, P_vip number)
as
NumerId number(5);
Ilu number(5);
Taki_juz_jest exception;
Begin
SELECT COUNT(1) INTO Ilu FROM goscie
WHERE Nazwisko = p_nazwisko AND Imie = p_imie AND dowos = p_dowos;
If ilu > 0 Then
Raise Taki_juz_jest;
Else
SELECT NVL(MAX(Id_goscia) + 1, 1) INTO NumerId FROM goscie;
SELECT Count(1) INTO ilu FROM Goscie;
dbms_output.put_line ('Mamy w bazie aktualnie dane '|| Ilu||
' gosci i dopisujemy kolejnego.');
INSERT INTO Goscie VALUES (NumerId, p_imie,p_nazwisko ,p_dataur,p_adres,p_dowos,
P_vip);
commit;
End If;
Exception
When Taki_juz_jest Then
SELECT Id_Goscia INTO NumerId FROM Goscie WHERE
nazwisko = p_nazwisko AND imie = P_imie AND dowos = p_dowos;
dbms_output.put_line ('Gosc ' ||p_nazwisko||' '||p_imie||' jest juz zapisany w
bazie z numerem identyfikacyjnym '||NumerId);
end;