Pawel Gorecki
Wprowadzenie do PL/SQL i jezyk PL/SQL
Aplikacje bazodanowe w Internecie
1. Deklaratywne wiezy spojnosci.
Mozna je okreslac za pomoca instrukcji SQL-a: CREATE TABLE, ALTER TABLE lub wyzwalaczy.
Skladnia
CREATE TABLE name (
column datatype [DEFAULT expr] [column_constraint] ...
| table_constraint
[, column datatype [DEFAULT expr] [column_constraint] ...
| table_constraint ] ...
)
column_constraint:
[CONSTRAINT constraint_name]
{ [NOT] NULL | UNIQUE | PRIMARY KEY |
REFERENCES table [(column)] [ON DELETE CASCADE] |
CHECK (condition)
}
table_constraint:
[CONSTRAINT constraint]
{ {UNIQUE | PRIMARY KEY} (column [,column] ...)
| FOREIGN KEY (column [,column] ...)
REFERENCES table [(column [,column] ...)] [ON DELETE CASCADE]
| CHECK (condition) }
Uwaga: niektore klauzule sa pominiete - wiecej informacji w pomocach Oracla.
Klauzula CONSTRAINT nadaje nazwe wiezom. Mozemy ja wykorzystac np.: do ich wylaczenia lub wlaczenia.
Wiezy:
tabeli
Mozemy uzyc tej instrukcji m.in do:
ALTER TABLE table
ADD (CONSTRAINT constraint_name CHECK(...))
ALTER TABLE table
ENABLE CONSTRAINT constraint_name
ALTER TABLE table
DISABLE CONSTRAINT constraint_name
ALTER TABLE table
DROP CONSTRAINT constraint_name
Jest to interakcyjny system umo¿liwiaj¹cy wprowadzanie i wykonywanie:
Mozna go wykorzystac do tworzenia bardzo prostych aplikacji.
Polecenia:
Jest to podstawowy jezyk pisania aplikacji w Oraclu. Jest dostepny w wielu programach narzedziowych Oracla.
Sa to podstawowe jednostki PL/SQL
Skladnia:
[DECLARE
-- sekcja dekalracji ]
BEGIN
-- instrukcje
[EXCEPTION
-- obsluga wyjatkow ]
END;
Bloki moga byc zagniezdzone. W sekcji instrukcji oprocz konstrukcji
PL/SQL moga wystapic instrukcje SQL: DELETE, INSERT, UPDATE, SELECT,
COMMIT, ROLLBACK
3.2. Zmienne i stale.
Skladnia deklaracji:
ident datatype [CONSTANT] [NOT NULL] [:=expr];
Typy dostepne z SQL-a, ponadto:
Uwaga: nazwy zmiennych musza byc rozne od nazw kolumn.
Mozna uzywac innych zmiennych:
Umieszczona w bloku PL/SQL nie powoduje wypisywania wynikow na ekran.
Ma tez szczegolna skladnie:
SELECT expr1,expr2,... INTO var1,var2,... FROM table1,table2... [WHERE...] [GROUP BY...] [HAVING...] [FOR UPDATE OF...]
Taka instrukcja musi zawsze zwrocic jeden wiersz. Wpp zostana podniesione odpowiednie wyjatki: too_many_rows lub no_data_found.
Klazula INTO powoduje umieszczenie wyniku zapytania na zmiennych: var1,....
Znaczenie FOR UPDATE OF...
Atrybuty zapytan (dotyczy ostatnio wykonywanego zapytania):
Zmienne systemowe zwiazane z obsluga bledow:
Wypisywanie na ekranie:
dbms_output.Put_line(txt_expr)
Np.:
dbms_output.Put_line('abc'||zmienna_txt||to_char(1023));
Ale przedtem w SQL*Plusie ustawiamy:
SET ServerOutput ON
Standardowe konstrukcje:
IF warunek THEN instrukcje;
[ELSIF warunek THEN instrukcje; ]
[ELSIF ...]
[ELSE instrukcje; ]
END IF;
LOOP
instrukcje z
EXIT lub EXIT WHEN warunek;
END LOOP;
WHILE warunek LOOP
instrukcje;
END LOOP;
FOR licznik IN [REVERSE] dolna_granica..gorna_granica LOOP
instrukcje;
END LOOP;
Ponadto instrukcja GOTO:
...
GOTO gdzies; -- skocz do instrukcji za etykieta
...
<gdzies> -- etykieta
...
Instrukcja pusta:
NULL;
W deklaracji zmiennych typu wierszowego piszemy na okreslenie typu:
table_name%ROWTYPE
Nie mozna odwolywac sie do calego rekordu - uzywamy notacji kropkowej.
Np.:
DECLARE
Rekosoba OsobaTbl%ROWTYPE;
BEGIN
SELECT *
INTO Rekosoba
FROM OsobaTbl
WHERE Name='Ziutek';
dbms_output.putline(Rekosoba.street||'-'||to_char(Rekosoba.id));
END;
Kursor jest specjalnym buforem umozliwiajacym dostep do wynikow zapytan.
Deklaracja:
CURSOR nazwa IS instrukcja_select; -- bez INTO
Korzystanie:
OPEN nazwa; -- otworz kursor
W petli wykonujemy:
FETCH nazwa INTO zmienna,...; -- przechwyc rekord(y)
EXIT WHEN nazwa%NOTFOUND; -- sprawdz czy jestesmy na koncu
Na koniec:
CLOSE(nazwa); -- zamknij kursor
Atrybuty kursora:
Chcac wykonywac modyfikakcje na wierszach (usuwanie, zmiany) nalezy zalozyc na nie blokady. Sluzy do tego klauzula w instrukcji SELECT:
FOR UPDATE OF column, column...,
ktora nalezy umiescic w deklaracji kursora.
Nastepnie w instrukcji INSERT lub DELETE zamiescic klauzule:
WHERE CURRENT OF kursor
Np.:
DECLARE
Rekosoba OsobaTbl%ROWTYPE;
CURSOR kursor IS
SELECT name,id FROM OsobaTbl
FOR UPDATE OF id;
BEGIN
OPEN kursor;
LOOP
FETCH kursor INTO Rekosoba;
EXIT WHEN kursor%NOTFOUND;
IF Rekosoba.id=0
THEN UPDATE Osoba SET id=id+1
WHERE CURRENT OF kuror;
END IF;
CLOSE kursor;
END;
Wyjatki definiowane przez programiste:
name EXCEPTION;
Podnoszenie (sekcja instrukcji):
RAISE name;
Obsluga (sekcja EXCEPTION):
WHEN name THEN instrukcje; -- obsluz wyjatek name WHEN others THEN instrukcje; -- obsluz wszystkie nie obsluzone
Podnoszenie wyjatku powoduje przerwanie wykonywania instrukcji i przekazanie sterowania do sekcji wyjatkow. Jesli tam nie nastapi jego obsluga to przekazanie nastapi do sekcji nadrzednej itd. Jesli nikt nie obsluzy wyjatku to zostanie przekazany do aplikacji wolajacej bloki PL/SQL-a.
Rekordy PL/SQL sa rozszerzeniem typu wierszowego. Ma podobne do niego wlasnosci:
DECLARE:
TYPE typ_rec_name IS RECORD
(
... deklaracje kolumn ...
);
rec_name typ_rec_name;
Sa to dwuwymiarowe i dwukolumnowe tabele. Pierwsza kolumna jest indeks typu BINARY_INTEGER. Druga kolumna jest wartoscia i moze byc dowolnego typu z typow wyzej wymienionych. Liczba wierszy jest nieograniczona. Umozliwiaja przesylanie duzych ilosci danych.
Deklaracje:
TYPE type_name IS TABLE OF typ_danych [NOT NULL] INDEX BY BINARY_INTEGER;
Np.: typ_danych moze byc postaci:
table.column%TYPE table%ROWTYPE jakis_typ_rekordowy jakis_typ_standardowy
Deklaracja zmiennej typu tabelowego:
tab type_name;
Funkcje i procedury moga przyjmowac i zwracac wartosci typu tabelowego.
Do zmiennych typu tabelowego odwolujemy sie przez indeks:
tab(i);
Atrybuty tabel PL/SQL:
tab.EXISTS(i) -- czy i-ty jest okreslony tab.COUNT -- liczba eltow tab.FIRST -- pierwszy element tabeli tab.LAST -- ostatni element tabeli tab.PRIOR(i) -- poprzednik i-tego tab.NEXT(i) -- nastepnik i-tego tab.DELETE -- usun zawartosc tabeli tab.DELETE(i) -- usun i-ty element tab.DELETE(i,j) -- usun elementy od i do j
Przyklad uzycia tabeli PL/SQL:
LOOP
i:=i+1; -- zmienna typu BINARY_INTEGER
FETCH c1 INTO tab(i); -- c1 - kursor
EXIT WHEN c1%NOTFOUND;
END LOOP;
FOR rec IN (SELECT name FROM anySQLtable)
LOOP
n:=n+1; -- zmienna typu BINARY_INTEGER
tab(n):=rec.name;
END LOOP;
Definicja procedury:
CREATE [OR REPLACE] PROCEDURE nazwa (lista_parametrow_formalnych) [AS|IS] blok PL/SQL bez slowa DECLARE
Klauzula OR REPLACE pozwala na zmiane istniejacych procedur (i rowniez innych obiektow). Lista parametrow formalnych ma postac:
var1 [IN|OUT|IN OUT] type [, ...]
Typy parametrow:
Definicja funkcji:
CREATE [OR REPLACE] FUNCTION nazwa (lista_parametrow_formalnych) RETURN typ [AS|IS] blok PL/SQL bez slowa DECLARE
Procedura lub funkcja moze podniesc swoj wlasny blad:
Raise_application_error(numer_bledu,tresc_komunikatu)
Nr bledu powinien byc z przedzialu: -20000..-20999
Informacje o procedurach i funkcjach:
DESCRIBE PROCEDURE name DESCRIBE FUNCTION name
Jesli zmieniaja sie obiekty zwiazane z funkcja lub procedura, to Oracle automatycznie dokonuje ich kompilacji. Dane o kompilacji mozna odczytac w nastepujacy sposob:
SELECT status FROM user_objects WHERE object_name = 'nazwa_procedury'
Jesli status jest rowny INVALID to procedura wymaga kompilacji, ktora mozemy przeprowadzic:
ALTER PROCEDURE nazwa_procedury COMPILE;
Uprawnienia do uzycia procedury mozna nadac tak:
GRANT EXECUTE ON nazwa_procedury TO jakas_osoba;
Funkcje i procedury mozna przeladowywac.
Umozliwiaja grupowanie kursorow, zmiennych, stalych, procedur, funkcji i wyjatkow w wieksze jednostki.
Pakiety skladaja sie z czesci publicznej (specyfikacja) i prywatnej (implementacja).
Deklaracja specyfikacji:
CREATE [OR REPLACE]
PACKAGE name
AS
deklaracje obiektow publicznych
specyfikacja naglowkow fcji/procedur
END name;
Czesc implementacyjna:
CREATE [OR REPLACE]
PACKAGE BODY name
AS
Definicje obiektow publicznych i prywatnych
END name;
Sa to specjalne procedury uruchamiane podczas zajscia operacji na bazie danych. Sluza przede wszystkim do testowania warunkow spojnosci i wykonywanie pewnych stalych czynnosci na b.d.
Wyrozniamy wyzwalacze wierszowe (wykonywane dla kazdego wiersza zwiazanego z instrukcja modyfikujaca - FOR EACH ROW) lub wyzwalacze dla instrukcji. Zaleza od tego czy ma byc wykonywany przed (BEFORE) czy po (AFTER) operacji. Wyzwalacze dotycza instrukcji: INSERT, DELETE, UPDATE.
Skladnia:
CREATE [OR REPLACE] TRIGGER name [BEFORE|AFTER] [specyfikacja instrukcji] ON tabela [FOR EACH ROW [WHEN cond]] blok PL/SQL bez DECLARE
'specyfikacja instrukcji' jest ciagiem nazw: INSERT -- wyzwalacz dla instrukcji INSERT UPDATE [OF kol,...] -- wyzwalacz dla instrukcji UPDATE [kolumn kol,...] DELETE -- wyzwalacz dla instrukcji DELETE polaczonych slowem OR.
Klauzula WHEN z warunkiem (wystepujaca z FOR EACH ROW) powoduje uruchomienie wyzwalacza dla wiersza jesli jest spelniony warunek.
Wykonanie Raise_application_error(nr_bledu,tekst) powoduje anulowanie wykonania instrukcji, z ktora jest zwiazany.
Kolejnosc wykonywania wyzwalaczy:
W wyzwalaczu mozna odwolywac sie do starych i nowych wartosci w wierszu:
Specjalne zmienne systemowe informuja o typie operacji:
W wyzwalaczach nie wolno uzywac operacji zwiazanych z transakcjami: COMMIT, ROLLBACK.
Ograniczenia w uzyciu wyzwalaczy:
W przypadku uzycia niedozwolonego wyzwalacza wystapi blad.
Operacja na wyzwalaczach:
ALTER TRIGGER name [ENABLE|DISABLE] -- wlacz/wylacz wyzwalacz DROP TRIGGER name -- usun
Dane o wyzwalaczach sa przechowywane w perspektywie User_triggers.