SQL*Plus PL/SQL
SQL*Plus
Polecenia
START nazwa_pliku, lub @ nazwa_pliku
ED nazwa_pliku
SPOOL nazwa_pliku
SPOOL OFF
SET PAUSE ON
SET AUTOCOMMIT ON
SET PAGESIZE n
HOST polecenie
EXIT
DESCRIBE nazwa
EXECUTE nazwa_procedury
COLUMN nazwa FORMAT An
COLUMN nazwa FORMAT 9999
VARIABLE X NUMBER (lub CHAR(n), VARCHAR(n) )
EXECUTE :X:= wyrazenie
PRINT X
ACCEPT Zmienna PROMPT 'Podaj wartosc: '
---------------------------------------------
PL/SQL
Blok anonimowy
DECLARE
obiekty PL/SQL zmienne, stale funkcje, procedury, wyjatki
BEGIN
ciag instrukcji
EXCEPTION
(obsluga wyjatków)
END;
Uwaga: wpisywanie konczymy kropka `.'
Przyklad: sprzedaż samochodu FIAT
Założenie tabeli Magazyn
CREATE TABLE MAGAZYN
(MAGNO NUMBER(4) NOT NULL,
PRODUKT VARCHAR2(10),
STAN NUMBER(4));
Założenie tabeli Zakupy
CREATE TABLE ZAKUPY
(PRODUKT VARCHAR2(10),
DATA DATE);
SELECT TABLE_NAME FROM USER_TABLES
INSERT INTO MAGAZYN VALUES (1, `Fiat',5);
INSERT INTO MAGAZYN VALUES (2, `Polonez',3);
select * from magazyn select * from magazyn
******************************************
DECLARE liczba NUMBER(5);
BEGIN
SELECT STAN INTO liczba FROM Magazyn
WHERE PRODUKT = 'Fiat';
/*
Gdy w kolumnie Produkt tabeli MAgazyn nie ma wartosci 'Fiat'
podnoszony jest wyjatek o nazwie no_data_found
*/
IF liczba > 0 THEN
UPDATE Magazyn SET STAN = STAN-1
WHERE PRODUKT = 'Fiat';
INSERT INTO Zakupy VALUES ('Kupiono Fiata', Sysdate)
ELSE
INSERT INTO Zakupy VALUES ('Brak Fiatów', Sysdate);
END IF;
COMMIT;
EXCEPTION -- Poczatek obslugi wyjatków
WHEN no_data_found THEN
INSERT INTO dziennik_bledow VALUES ('Nie znaleziono produktu Fiat');
END;
Deklaracja stałych i zmiennych
Przykłady deklaracji:
zarobki NUMBER(7,2);
pi CONSTANT NUMBER(7,5) := 3.14159;
nazwisko VARCHAR2(25) := 'Kowalski'
data DATE := Sysdate;
zonaty BOOLEAN := False;
liczba_dzieci BINARY_INTEGER :=0;
Wypisywanie wyników na ekran
SET SERVEROUTPUT ON
DBMS_OUTPUT.Put_line(wyrażenie tekstowe)
Przykład:
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('Going to sleep for 10 seconds...');
DBMS_OUTPUT.PUT_LINE('Woke up after 10 seconds.');
END;
/
SET ServerOutput ON
ACCEPT rocz_zarobki PROMPT 'Podaj roczne zarobki: '
DECLARE
mies NUMBER(9,2) := &rocz_zarobki;
BEGIN
mies := mies/12;
DBMS_OUTPUT.Put_line ('Miesięczne zarobki = ' || mies);
END;
Zmienne systemowe PL/SQL
SQL%ROWCOUNT -
SQL%FOUND = TRUE
SQL%NOTFOUND = TRUE
Tylko w bloku EXCEPTION
SQLERRM - info o błędzie
SQLCODE - kod błędu
Przykład:
CREATE TABLE dziennik_bledow(dane varchar(9), usuniete number, data date);
DECLARE usuniete NUMBER;
BEGIN
DELETE FROM DEPT WHERE Deptno = 50;
usuniete := SQL%ROWCOUNT;
INSERT INTO dziennik_bledow VALUES ('Dział ', usuniete, Sysdate);
END;
Instrukcje sterujące
IF warunek THEN instrukcje END IF;
IF warunek THEN instrukcje
ELSE instrukcje
END IF;
LOOP
instrukcje (w tym EXIT lub EXIT WHEN warunek)
END LOOP;
FOR zmiena IN wartosc1..wartosc2
LOOP ---- END LOOP;
WHILE warunek LOOP ---- END LOOP;
Rekordy ←→ wiersze tabeli
DECLARE
rek_osoby Emp%ROWTYPE; /* Typ wierszowy */
BEGIN
SELECT * INTO rek_osoby FROM Emp WHERE Ename = 'SCOTT';
rek_osoby.Sal := 1.1 * rek_osoby.Sal;
INSERT INTO DZIENNIK VALUES (rek_osoby.Ename, rek_osoby.Job, rek_osoby.Sal, Sysdate);
END;
_________________________________________
Kursory
CURSOR nazwa_kursora IS instrukcja_SELECT (!!! bez INTO !!!)
OPEN nazwa_kursora;
w pętli
FETCH nazwa_kursora INTO zmienna;
EXIT WHEN nazwa_kursora%NOTFOUND;
CLOSE nazwa_kursora;
Przykład:
SET ServerOutput ON
DECLARE
zarobki REAL:=0;
CURSOR kursor_osoba IS SELECT * FROM EMP;
rek_osoby kursor_osoba%ROWTYPE;
BEGIN
OPEN kursor_osoba;
LOOP
FETCH kursor_osoba INTO rek_osoby;
EXIT WHEN kursor_osoba%NOTFOUND;
zarobki:= zarobki + NVL(rek_osoby.Sal*12,0) +NVL(rek_osoby.comm,0);
END LOOP;
DBMS_OUTPUT.Put_line('W sumie zarobki = ' || zarobki);
CLOSE kursor_osoba;
END;
Możliwe jest też:
FOR rek_osoby IN kursor_osoba LOOP ... END LOOP;
CURSOR nazwa_kursora(parametr typ_danych, ...) IS instrukcja_SELECT
Przykład:
DECLARE
CURSOR emp_cursor
(v_deptno NUMBER, v_job VARCHAR2) IS
SELECT empno, ename
FROM Emp
WHERE deptno = v_deptno
AND job = v_job;
BEGIN
OPEN emp_cursor(10, 'CLERK');
.......
Atrybuty kursora
kursor%FOUND
kursor%NOTFOUND
kursor%ROWCOUNT
kursor%ISOPEN
Aktualizacja danych
FOR UPDATE OF kolumna, kolumna...
WHERE CURRENT OF kursor
Przykład:
DECLARE
CURSOR kursor_osoba IS
SELECT Ename, Sal FROM EMP FOR UPDATE OF Sal;
rek_osoby kursor_osoba%ROWTYPE;
BEGIN
OPEN kursor_osoba;
LOOP
FETCH kursor_osoba INTO rek_osoby;
EXIT WHEN kursor_osoba%NOTFOUND;
IF rek_osoby.Sal < 10000 THEN
UPDATE Emp SET Sal = Sal*1.1
WHERE CURRENT OF kursor_osoba;
ELSIF rek_osoby.Sal > 10000 THEN
UPDATE Emp SET Sal = Sal*0.9
WHERE CURRENT OF kursor_osoba;
END IF;
END LOOP;
CLOSE kursor_osoba;
COMMIT;
END;
Typy kursorów
DECLARE
TYPE Typ_produktow IS
REF CURSOR RETURN Produkty%ROWTYPE; /* silny typ */
kursor1 Typ_produktow;
TYPE Typ_kursorowy IS REF CURSOR; /* słaby typ */
kursor2 Typ_kursorowy;
Wyjątki programistyczne
nazwa_wyjatku EXCEPTION;
RAISE nazwa_wyjatku;
WHEN nazwa_wyjatku THEN ...
Przykład:
DECLARE
brak_w_magazynie EXCEPTION;
stan_magazynu NUMBER(5);
BEGIN
SELECT Liczba INTO stan_magazynu
FROM Magazyn WHERE Marka = 'Fiat';
IF stan_magazynu < 1 THEN
RAISE brak_w_magazynie;
END IF;
EXCEPTION
WHEN brak_w_magazynie Then
INSERT INTO Zamowienia VALUES ('Fiaty');
END;
Procedury
CREATE [OR REPLACE] PROCEDURE nazwa (lista_parametrów_formalnych)
(AS|IS)
blok PL/SQL {bez słowa kluczowego DECLARE}
Typy przekazywania parametrów:
IN
OUT
IN OUT
Przykład:
CREATE OR REPLACE PROCEDURE Kredyt (acc_no IN Number, amount IN NUMBER) AS
BEGIN
UPDATE Accounts a SET a.Balance = a.Balance + amount
WHERE a.Acocount_id = acc_no;
END;
EXECUTE Kredyt(123321,1000)
Sprawdzenie stanu konta:
CREATE OR REPLACE PROCEDURE Saldo (acc_no IN Number, bal OUT NUMBER) AS
BEGIN
SELECT a.Balance INTO bal FROM Accounts a
WHERE a.Accounts_id = acc_no;
END;
EXECUTE Saldo (123321,stan_konta)
Funkcje
CREATE [OR REPLACE] FUNCTION nazwa (lista_parametrów_formalnych)
RETURN typ {AS|IS}
blok PL/SQL bez słowa kluczowego DECLARE z instrukcją RETURN
wyrażenie
Przykład:
CREATE OR REPLACE FUNCTION saldo (acc_no IN NUMBER)
RETURN NUMBER IS
value NUMBER;
BEGIN
SELECT a.Balance INTO value FROM Accounts a
WHERE a.Account_id = acc_no;
RETURN value;
END;
KOd SQL lub PL/SQL
SELECT Saldo (a.Account_id) FROM Accounts a;
lub
x := Saldo(123321);
Kod SQL*Plus
VARIABLE p NUMBER
EXECUTE :p:= Saldo(123321)
PRINT p
Inny przykład:
CREATE OR REPLACE PROCEDURE Wpisz_studenta(
imie IN studenci.Imie%TYPE
nazwisko IN Studenci.Nazwisko%TYPE
rok IN STUDENCI.Rok%TYPE DEFAULT 1)
AS
indeks Studenci.Indeks%TYPE;
BEGIN
SELECT NVL(Max (s.Indeks)+1,1) INTO indeks
FROM STudenci s;
INSERT INTO Studenci
VALUES (indeks, imie, nazwisko, rok)
END;
Wpisz_studenta('Jasio', 'Fasola');
Wpisz_studenta('Jan', 'Fasolski',2);
Oglądanie procedury
COLUMN Line FORMAT 99999
COLUMN TEXT FORMAT A80
SET PAGESIZE 23
SET PAUSE ON
SELECT u.Line, u. Text
FROM User_Source u
WHERE u.Name = 'Nazwa_procedury';
Błędy
SHOW ERRORS
lub
DBMS_OUTPUT.Put_line(tekst)
np.:
DBMS_OUTPUT.Put_line('Pracownik: '|| Nazwisko || 'Zarobki: ' || TO_CHAR(Zarobki));
Ponowna kompilacja potrzebna, gdy Status = 'INVALID'
Samodzielna kompilacja:
ALTER PROCEDURE Oblicz_Zarobki COMPILE;
Nadanie uprawnień do użycia procedury:
GRANT EXECUTE ON Oblicz_Zarobki TO Moja_Ksiegowa;
Pakiety
Grupują procedury i funkcje
Możemy w pakiecie zdefiniować globalnie:
1. kursory
2. zmienne i stałe
3. wyjątki
Pakiet składa się z:
1. części publicznej (specyfikacji)
2. części prywatnej (implementacji)
Składnia:
CREATE [OR REPLACE] PACKAGE nazwa_pakietu AS
Deklaracja obiektów publicznych
Dla funkcji i procedur specyfikacja nagłówków
END nazwa_pakietu;
Składnia części implementacyjnej:
CREATE [OR REPLACE] PACKAGE BODY nazwa_pakietu AS
Definicje obiektów publicznych i prywatnych
BEGIN
Instrukcje inicjujące
END nazwa_pakietu;
Przykład: zatrudnianie i zwalnianie pracowników
Specyfikacja pakietu:
CREATE PACKAGE Obsluga_prac AS
PROCEDURE Zatrudnij(
numer_prac NUMBER,
nazwisko VARCHAR2,
zarobki NUMBER,
numer_dzialu NUMBER);
PROCEDURE Zwolnij(
numer_prac NUMBER);
ile_przyjetych NUMBER;
ile_zwolnionych NUMBER;
END Obsluga_prac;
Część implementacjna pakietu:
CREATE PACKAGE BODY Obsluga_prac AS
/* Procedura zatrudniania pracownika */
PROCEDURE Zatrudnij(numer_prac NUMBER,nazwisko VARCHAR2,
zarobki NUMBER,numer_dzialu NUMBER) IS
BEGIN
INSERT INTO Pracownicy VALUES (numer_prac,nazwisko,zarobki,numer_dzialu);
COMMIT;
ile_przyjetych := ile_przyjetych + 1;
END Zatrudnij;
/* Procedura zwalniania pracownika */
PROCEDURE Zwolnij(numer_prac NUMBER) IS
BEGIN
DELETE FROM Pracownicy where Id_prac = numer_prac;
COMMIT;
ile_zwolnionych := ile_zwolnionych + 1;
END Zwolnij;
BEGIN
ile_przyjetych := 0;
ile_zwolnionych := 0;
END Obsluga_prac;
Dostęp do obiektów publicznych pakietu odbywa się techniką kropkową:
Obsluga_prac.Zatrudnij(1000, 'Kowalski', 2000, 12);
Przykład: użycie globalnego kursora
CREATE OR REPLACE PACKAGE Pak AS
CURSOR c1 IS SELECT Id_pracownika
FROM Pracownicy
ORDER BY Id_pracownika DESC;
PROCEDURE Pierwsze_3;
PROCEDURE Nastepne_3;
END Pak;
CREATE OR REPLACE PACKAGE BODY Pak AS
v_id NUMBER;
PROCEDURE Pierwsze_3 IS
BEGIN
OPEN c1
LOOP
FETCH c1 INTO v_id;
DBMS_OUTPUT.PUT_LINE('Id: ' || v_id);
EXIT WHEN c1%ROWCOUNT >= 3;
END LOOP;
END Pierwsze_3;
PROCEDURE Nastepne_3 IS
BEGIN
LOOP
FETCH c1 INTO v_id;
DBMS_OUTPUT.PUT_LINE('Id: ' || v_id);
EXIT WHEN c1%ROWCOUNT >= 6;
END LOOP;
CLOSE c1;
END Nastepne_3;
END Pak;
UWAGA!!!
Nie można przekazywać kursora przez parametr procedury,
nie można też współdzielić zmiennych kursorowych!
Można przekazywać zmienne kusorowe przez parametry procedury w pakiecie.
Przykład:
VARIABLE krs REFCURSOR
CREATE OR REPLACE PACKAGE Emp_data AS
TYPE Emp_cur_typ IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE Use_emp_cv(emp_cv IN OUT Emp_cur_typ);
PROCEDURE Write(emp_cv IN OUT Emp_cur_typ);
END Emp_data;
CREATE OR REPLACE PACKAGE BODY Emp_data AS
PROCEDURE Use_emp_cv(emp_cv IN OUT Emp_cur_typ) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM emp;
END use_emp_cv;
PROCEDURE WRITE(emp_cv IN OUT Emp_curtyp) IS
rek_ emp_cv%ROWTYPE;
BEGIN
Use_emp_cv(emp_cv);
DBMS_OUTPUT.Put_line('Pracownicy firmy: ');
LOOP
FETCH emp_cv INTO rek;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.Put_line('(rek.ename);
END LOOP;
CLOSE emp_cv;
END Write;
END Emp_data;
/
execute Emp_data.Write(:krs)
Wyzwalacze bazy danych (Triggers)
Wyzwalacze to procedury. Dotyczą tabel, perspektyw, schematów (konta użytkowników), całej bazy danych. Są odpalane przy zajściu odpowiedniego zdarzenia, np: INSERT, DELETE lub UPDATE. Służą głównie do programowania więzów spójności.
Najczęściej używane są wyzwalacze tabelowe: jest ich 12 typów.
Składnia:
CREATE [OR REPLACE] TRIGGER nazwa_wyzwalacza
{BEFORE|AFTER}
specyfikacja instrukcji <- {INSERT|DELETE|UPDATE}
ON tabela
[FOR EACH ROW]
blok_PL/SQL
Kolejność odpalania wyzwalaczy:
1. przed instrukcją
2. przed pierwszym wierszem, na którym operuje instrukcja
3. po pierwszym wierszu, na którym operuje instrukcja
4. przed/po drugim, trzecim... wierszu, na którym operuje instrukcja
5. przed ostatnim wierszem
6. po ostatnim wierszu
7. po instrukcji
Odróżniamy wiersze stare i nowe:
1. :OLD - wiersz przed zmianą
2. :NEW - wiersz po zmianie
Sytuacje, w jakich używamy wyzwalaczy
1. Do sprawdzenia więzów spójności, wyzwalacz typu BEFORE - gdy błąd, wykonanie jest
anulowane
Przykład: utrzymywanie zarobków pracowników w przedziale zależnym od stanowiska
CREATE TRIGGER Sprawdzenie_zarobków
BEFORE INSERT OR UPDATE OF Zarobki, Stanowisko
ON Pracownicy
FOR EACH ROW
DECLARE
minimum NUMBER;
maksimum NUMBER;
BEGIN
/* Poszukiwanie przedziału zarobków */
SELECT w.Min_zar, w.Mak_zar INTO minimum, maksimum
FROM Widelki w
WHERE w.Stanowisko = :NEW.Stanowisko;
/* Błąd, jeśli zarobki spoza przedziału */
IF :NEW.Zarobki < minimum OR :NEW.Zarobki > maksimum THEN
Raise_application_error(-20500,'Zarobki= " || :NEW.Zarobki || 'spoza przedziału');
END IF;
END; /* Koniec wyzwalacza*/
/
2. Do realizacji stałych czynności: AFTER INSERTING|DELETING|UPDATING
Przykład: aktualizacja budżetu działu
CREATE TRIGGER Budzet_dzialu
AFTER DELETE OR INSERT OR UPDATE OF Zarobki, Num_dzialu
ON Pracownicy
FOR EACH ROW
BEGIN
/* Zakładamy, że Zarobki, Num_dzialu NOT NULL */
IF DELETING OR (UPDATING AND :OLD.Num_dzialu <> :NEW.Num_dzialu) THEN
UPDATE Budzet
SET Fund_plac = Fund_plac - :OLD.Zarobki
WHERE Id_dzialu = :OLD.Num_dzialu;
END IF;
IF INSERTING OR (UPDATING AND :OLD.Num_dzialu <> :NEW.Num_dzialu) THEN
UPDATE Budzet
SET Fund_plac = Fund_plac - :NEW.Zarobki
WHERE Id_dzialu = :NEW.Num_dzialu;
END IF;
IF UPDATING AND (:OLD.Num_dzialu = :NEW.Num_dzialu)
AND (:OLD.Zarobki <> :NEW.Zarobki) THEN
UPDATE Budzet
SET Fund_plac = Fund_plac - :OLD.Zarobki + :NEW.Zarobki
WHERE Id_dzialu = :OLD.Num_dzialu;
END IF;
END;
3. Do wykonywania obliczeń (np. wstawianie jednoznacznych identyfikatorów) (BEFORE)
CREATE TRIGGER GenerujIdPrac
BEFORE INSERT ON Pracownicy
FOR EACH ROW
BEGIN
SELECT NVL(Max(p.Id_prac)+1,1)
INTO :NEW.Id_prac
FROM Pracownicy p;
END;
Zamiast powyższego (jeśli nie jest zabronione przez SZBD) można użyć
SELECT Prac_seq.nextval
INTO :NEW.Id_prac
FROM Dual;
4. Utrzymywanie reguł odmiennych od przyjętych w deklaratywnych więzach spójności (AFTER)
Przykład: ustaw NULL
CREATE TRIGGER Ustaw_null_dept
AFTER DELETE OR UPDATE OF Deptno ON Dept
FOR EACH ROW
BEGIN
IF UPDATING AND :OLD.Deptno <> :NEW.Deptno OR DELETING THEN
UPDATE Emp
SET Emp.Deptno = NULL
WHERE Emp.Deptno = :OLD.Deptno;
END IF;
END;
W wyzwalaczach nie wolno używać ani COMMIT ani ROLLBACK
nie wolno odczytywać ani zmieniać wartości w zmienianej tabeli za wyjątkiem użycia instrukcji
INSERT INTO ... VALUES ...
Zmienne pomocnicze muszą być zadeklarowane w pakiecie - to jedyna droga przekazywania
wartości między wyzwalaczami (oprócz wykorzystania pomocniczej tabeli w BD)
Wyzwalacze typu INSTEAD OF - zwiazane z perspektywą
CREATE [OR REPLACE] TRIGGER nazwa_wyzwalacza
INSTEAD OF specyfikacja_instrukcji <- (INSERT OR DELETE OR UPDATE)
ON perspektywa
blok PL/SQL
pozwalają na zmiany w bazie danych z poziomu użytkowego, czyli perspektyw
Przykład: złączenie trzech tabel
CREATE VIEW Manager_info AS
SELECT e.Ename, e.Empno, d.Dname, d.Deptno, p.Level, p. Projno
FROM Emp e, Dept d, Project p
WHERE e.Empno = d.Mgr_no AND d.Deptno = p.Resp_dept;
Teraz wstawimy wiersze za pomocą wyzwalacza, co w efekcie powoduje wstawienie informacji do trzech tabel
CREATE TRIGGER Manager_info_insert
INSTEAD OF INSERT ON Manager_info
FOR EACH ROW
DECLARE
p NUMBER;
BEGIN
SELECT Count(*) INTO p FROM Emp
WHERE Emp.empno = :NEW.Empno;
IF p=0 THEN
INSERT INTO Emp VALUES (:NEW.Empno, :NEW.Ename);
ELSE
UPDATE Emp
SET Emp.Ename = :NEW.Ename
WHERE Emp.Empno = :NEW.Empno;
END IF;
SELECT Count(*) INTO p FROM Dept
WHERE Dept.Deptno = :NEW.Deptno;
IF p=0 THEN
INSERT INTO Dept VALUES (:NEW.Deptno, :NEW.Dname);
ELSE
...
Wyzwalacze systemowe
Składnia:
CREATE [OR REPLACE] TRIGGER nazwa_wyzwalacza
[BEFORE|AFTER|INSTEAD OF][zdarzenie bazodanowe|zdarzenie DDL]
ON [DATABASE|SCHEMA]
blok PL/SQL
przy czym
1. zdarzenie bazodanowe: SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN
2. zdarzenie DDL: CREATE, ALTER, DROP, GRANT, REVOKE
Można je łączyć operatorem OR
Przykład: wyzwalacz rejestrujący wszystkie błędy serwera BD, a dla błędu 1017 (niepotrzebne logowanie) ma obsługę specjalną
CREATE TRIGGER Log_errors AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR (1017)) THEN
<wykonaj specjalną obsługę>
ELSE
<zapisz w dzienniku informację o błędzie>
END IF;
END;
Przykład: notowanie tworzenia nowego obiektu w BD
CREATE TRIGGER Audit_db_object AFTER CREATE ON SCHEMA
<zapisz w dzienniku infomację o utworzeniu obiektu>
Operacje na wyzwalaczach
ALTER TRIGGER nazwa {ENABLE|DISABLE}; ← włącza/wyłącza
DROP TRIGGER nazwa; ← usuwanie
Informacje o wyzwalaczach znajdują się w perspektywie User_triggers
SELECT u.Trigger_type, u.Table_name, u.Triggering_event
FROM User_triggers u
WHERE u.Trigger_name = 'NAZWA' <- duże litery!
KONIEC
PLSQL.doc 14/18