Bazy danych, 3. ¢wiczenia
2007-10-16
1 Plan zaj¦¢
Wst¦p do PL/SQL:
• wprowadzanie,
• podstawowa skªadnia,
• de niowanie zmiennych prostych, zªo»onych,
• interakcja z u»ytkownikiem,
• instrukcje warunkowe,
• p¦tle,
• kursory,
• wyj¡tki.
2 Podstawowa skªadnia
[ DECLARE
deklaracje (np. zmiennych, staªych) ]BEGIN
polecenia programu [ EXCEPTION
obsªuga bª¦dów ]END;
3 Zmienne
Dost¦pne typy zmiennych:
• liczbowe: BINARY_INTEGER, DEC, DECIMAL, FLOAT, INT, INT- GER, NATURAL, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, REAL, SMALLINT,
• znakowe: CHAR, CHARACTER, LONG, NCHAR, NVARCHAR2, RAW, STRING, VARCHAR, VARCHAR2,
• logiczne: BOOLEAN,
• czasowe: DATE, TIMESTAMP, INTERVAL,
• zªo»one: RECORD, TABLE, VARRAY,
• wska¹nikowe: REF CURSOR, REF object_type Przykªady deklaracji:
DECLARE
i NUMBER(10);
j INT := 10;
data DATE NOT NULL DEFAULT DATE '2007-01-01';
ok BOOLEAN NOT NULL DEFAULT FALSE;
s VARCHAR(100) := 'ala ma kota';
Zmienne zªo»one:
DECLARE
TYPE t_punkt IS RECORD ( x INT,
y INT );p1 t_punkt;
Dost¦p do poszczególnych pól jak w Pascalu/C (czyli np. p1.x).
Atrybuty %TYPE i %ROWTYPE sªu»¡ do sprawdzania typu okre±lonych pól/wierszy w tabelach.
DECLARE
zmienna tabela.pole%TYPE;
wiersz tabela%ROWTYPE;
Staªe:
DECLARE
nazwa CONSTANT typ := warto±¢;
4 Interakcja z u»ytkownikiem
Blok mo»e zawiera¢ zmienne podstawienia (czyli nazwy zmiennych poprzedzone znakiem '&'), przed uruchomieniem bloku u»ytkownik jest proszony o podanie warto±ci wszystkich zmiennych podstawieniowych.
s:=&abc;
i:=&identyfikator;
Do wypisywania komunikatów sªu»y funkcja dbms_output.put_line() dbms_output.put_line('i='||i||' s='||s);
5 Instrukcje warunkowe
Skªadnia polecania IF:
IF warunek THEN polecenia
( ELSIF warunek_x THEN polecenia
)*[
ELSEpolecenia ]END IF;
Skªadnia polecania CASE:
CASE wyra»enie
WHEN warto±¢ THEN polecenia1 WHEN warto±¢_2 THEN polecenia2 [ ELSE
polecenia ]END [ CASE ];
CASEWHEN warunek_1 THEN polecenia WHEN warunek_2 THEN polecenia2 [ ELSE
polecenia ]END [ CASE ];
Je±li CASE jest traktowane jako wyra»enie to mo»na ko«czy¢ ENDem, je±li jako polecenie to trzeba je zako«czy¢ END CASE.
6 P¦tle
Skªadnia polecenia LOOP:
LOOPsekwencja
[ IF warunek THEN EXIT; END IF; ] [ EXIT WHEN warunek ; ]
END LOOP;
Skªadnia polecania WHILE:
WHILE warunek LOOP polecenia
END LOOP;
Skªadnia polecenia FOR:
FOR licznik IN [ REVERSE ] start..koniec LOOP polecenia
END LOOP;
Nie jest konieczne de niowanie zmiennej licznik.
7 Ró»no±ci
• instrukcja NULL (instrukcja która nic nie robi, wypeªniacz pustych blo- ków),
8 Zapytania SQL w PL/SQL
SELECT lista pól INTO lista zmiennych FROM ... ;
INSERT INTO tabela (lista pól) VALUES (lista warto±ci);
INSERT INTO tabela VALUES zmienna_wierszowa;
/* pseudokolumna ROW */
UPDATE tabela SET row = zmienna_wierszowa;
9 Kursory
DECLARE
CURSOR nazwa [ (parametry) ] IS
{ zapytanie_sql | RETURN typ zªo»ony } [ FOR UPDATE [ OF lista pól ] ]
Przykªady:
DECLARE
CURSOR cur_osoby IS SELECT * FROM osoby;
CURSOR cur_osoby2 RETURN osoby%ROWTYPE;
CURSOR cur_wypozyczenia (p_pesel wypozyczenia%TYPE) IS SELECT * FROM wypozycznia WHERE pesel=p_pesel;
Operacje na kursorach:
OPEN nazwa_kursora [ ( warto±ci parametrów) ];
FETCH nazwa_kursora INTO { lista_zmiennych | zmienna wierszowa } ; CLOSE nazwa_kursora;
Atrybuty kursora:
%ISOPEN
%FOUND
%NOTFOUND
%ROWCOUNT
P¦tle FOR z u»yciem kursorów:
FOR { zmienna rekordowa | zmienna wierszowa } IN nazwa_kursora LOOP polecenia
END LOOP;
Nie jest konieczne otwieranie lub zamykanie kursora. Nie jest równie» konieczne de niowanie zmiennej licznikowej.
P¦tle FOR z zapytaniem SQL:
FOR { zmienna rekordowa | zmienna wierszowa } IN ( zapytanie SQL ) LOOP polecenia
END LOOP;
Kursor niejawny ma nazw¦ SQL (ostatnie wykonywane polecenie SELECT INTO, INSERT, UPDATE, DELETE).
10 Obsªuga wyj¡tków
DECLARE
wyj1 EXCEPTION;
wyj2 EXCEPTION;
BEGIN
polecenia ...
RAISE wyj¡tek;
EXCEPTION
WHEN wyj1 THEN polecenia1 WHEN wyj2 THEN
polecenia2 [ WHEN OTHERS THEN
polecenia.. ] END;
Prede niowane wyj¡tki:
CASE_NOT_FOUND CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX INVALID_CURSOR NO_DATA_FOUND TOO_MANY_ROWS VALUE_ERROR ZERO_DIVIDE
