Wyk
ład
8
Wyk
ład
8
Język PL/SQL
Część II
Tabele PL/SQL
• Indeksy tabel PL/SQL nie muszą być kolejnymi
liczbami całkowitymi.
• Liczba wierszy jest nieograniczona.
• Podstawową
funkcją
tabel
PL/SQL
jest
umożliwienie
operowania
w
algorytmach
zbiorami
wartości,
które
mają
zmienny
(rozszerzalny) rozmiar i które mogą mieć, na
przykład, postać listy, stosu lub drzewa.
• Są w szczególności potrzebne do przekazywania
zbiorów wartości w aplikacjach WWW (z klienta
do serwera).
• Typ tabeli PL/SQL nazwisk
pracowników:
TYPE EnameTabTyp IS
TABLE OF Emp.Ename%TYPE
INDEX BY BINARY_INTEGER;
• Typ
tabeli
PL/SQL
przechowującej
wiersze
z
tabeli Emp:
TYPE EmpTabTyp IS
TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
• Deklaracja konkretnej tabeli
PL/SQL:
emp_tab EmpTabTyp;
Przykład
• Odwołania
do
wartości
w
tabelach są przez indeks np.:
emp_tab(i)
• Cała tabela SQL - wiersz po
wierszu - zostaje wpisana do
tabeli PL/SQL:
i:=0;
LOOP
i:=i+1;
FETCH c1 INTO
emp_tab(i);
EXIT WHEN c1%NOTFOUND;
END LOOP;
Przykład
DECLARE
TYPE EmpTabTyp IS
TABLE OF Emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
i integer;
CURSOR c1 IS
select * from emp;
BEGIN
open c1;
i:=0;
LOOP
i:=i+1;
FETCH c1 INTO emp_tab(i);
EXIT WHEN c1%NOTFOUND;
END LOOP
;
FOR
i
IN
1 .. 14
LOOP
dbms_output.put_line
(emp_tab(i).ename);
EXIT WHEN SQL%NOTFOUND;
END LOOP
;
close c1;
END
;
Przykład
Procedury, funkcje i pakiety
• Procedury, funkcje i pakiety - obiekty
zapisywane w bazie danych; mogą być używane
w każdej aplikacji bazy danych, przez każdego
użytkownika posiadającego odpowiednie
uprawnienia tak samo jak w przypadku innych
obiektów bazy danych.
• Jest także możliwość definiowania funkcji i
procedur w blokach PL/SQL; ich użycie jest
wtedy ograniczone do tego bloku.
Procedury, funkcje i pakiety –
typowe sytuacje ich użycia
1. Podstawowe, współdzielone funkcje używane
przez różne aplikacje np.
• utworzenie zamówienia,
• usunięcie klienta.
2. Zadania typu wsadowego np.
• tworzenie
tygodniowego
zestawienia
rachunków.
3. Transakcje np.
• zebranie i przetworzenie informacji z odległych
baz danych.
Definicja procedury
CREATE [OR REPLACE]
PROCEDURE
nazwa
(lista_parametrów_formalnych)
{AS|IS}
blok PL/SQL bez słowa kluczowego
DECLARE
Słowo REPLACE oznacza, że w sytuacji, gdy
procedura o tej samej nazwie już istnieje, to nie
należy podnosić z tego powodu błędu, a tylko
zastąpić istniejący obiekt przez nowy. Jest to
użyteczne przy testowaniu i uruchamianiu
procedur, gdy wielokrotnie trzeba zmieniać ich
treść.
Parametry
• IN
– wartość przychodzi z wywołującej jednostki
programu i nie ulega zmianie w procedurze lub
funkcji, parametr IN nie może wystąpić po lewej
stronie instrukcji przypisania;
• OUT
– wartość nie przychodzi z wywołującej
jednostki programu; przy zwykłym zakończeniu
(bez
błędu)
wartość
parametru
jest
przekazywana
do
wywołującej
jednostki
programu;
• IN OUT
(domyślny) – wartość przychodzi z
wywołującej jednostki programu i przy zwykłym
zakończeniu (bez błędu) wartość parametru jest
przekazywana
do
wywołującej
jednostki
programu;
• W specyfikacji typu danych paraemtru nie
podaje się rozmiaru np. NUMBER, VARCHAR2;
można też:
tabela.kolumna%TYPE
• Wpłata na konto:
CREATE OR REPLACE PROCEDURE
Credit (acc_no IN NUMBER, amount IN
NUMBER)
AS
BEGIN
UPDATE Account
SET Balance = Balance + amount
WHERE AccountNo = acc_no;
END
;
• Wywołanie procedury :
CALL Credit(1, 1000);
– w Oracle8i SQL
Credit(1, 1000);
– w kodzie PL/SQL
EXECUTE Credit(1, 1000);
– w
SQL*Plus
Przykład
• Sprawdzenie salda na koncie można
zrealizować za pomocą procedury
korzystając z parametru OUT:
CREATE OR REPLACE PROCEDURE
Saldo (acc_no IN NUMBER, bal OUT
NUMBER)
AS
BEGIN
SELECT Balance INTO bal FROM Account
WHERE AccountNo = acc_no;
END
;
Przykład
OUT
Przy
wywołaniu procedury Saldo drugim
argumentem powinna być nazwa zmiennej
liczbowej, na którą zostanie przypisany
aktualny stan konta.
DECLARE
s number(10);
BEGIN
saldo(1, s);
dbms_output.put_line(s);
END
;
Przykład
Wywołanie
Definicja funkcji
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;
• Funkcja zwracająca stan konta.
CREATE OR REPLACE FUNCTION
f_Saldo
(acc_no IN NUMBER)
RETURN
NUMBER
IS
v_balance NUMBER;
BEGIN
SELECT Balance INTO v_balance FROM
Account
WHERE AccountNo= acc_no;
RETURN v_balance;
END
;
Przykład
• W SQL:
SELECT f_Saldo(AccountNo) FROM
Account;
• W PL/SQL:
DECLARE x NUMBER(10);
BEGIN
x := f_Saldo(1);
dbms_output.put_line(x);
END;
• W SQL*Plus:
VARIABLE p NUMBER
EXECUTE :p := f_Saldo(1)
PRINT p
Przykład
Użycie
funkcji
Wartości domyślne parametrów
CREATE OR REPLACE PROCEDURE
Wpisz_studenta(
v_imie IN Studenci.Imie%TYPE,
v_nazwisko IN Studenci.Nazwisko
%TYPE,
v_rok IN Studenci.Rok%TYPE DEFAULT
1)
AS
v_indeks Studenci.Indeks%TYPE;
BEGIN
SELECT NVL(Max(Indeks)+1,1) INTO
v_indeks
FROM Studenci;
INSERT INTO Studenci
VALUES(v_indeks,v_imie,v_nazwisko,v_rok,
v_data);
END
;
Użycie
• Gdy wpisujemy studenta pierwszego roku:
Wpisz_studenta('Jaś', 'Fasola');
• Gdy student przenosi się od razu na rok 2:
Wpisz_studenta('Jaś', 'Fasola ',2);
• Gdy jest więcej parametrów o wartościach
domyślnych np. w procedurze Wpisz_studenta
mógłby być jeszcze parametr
v_data IN DATE DEFAULT Sysdate
wybór parametrów domyślnych, którym w chwili
wywołania procedury przypisuje się wartości,
dokonuje się przez wskazanie explicite nazwy
parametru np.
Wpisz_studenta('Jaś', 'Fasola',
v_data => '1-SEP-99');
Użycie
funkcji bazodanowych
w instrukcjach SQL
• W taki sam sposób jak funkcji standardowych –
pod warunkiem, że nie zmieniają stanu bazy
danych (nie mogą więc zawierać instrukcji
INSERT, DELETE i UPDATE) ani nie mają
parametrów wyjściowych.
• Nie powinny też korzystać ze zmiennych
nielokalnych zadeklarowanych w pakietach.
• Wszystkie parametry muszą zostać
wyspecyfikowane i nie wolno używać dla nich
notacji typu Data => '1-SEP-99'.
Informacja o parametrach
procedury lub funkcji
• DESCRIBE Aktualizuj_zarobki
• DESCRIBE Saldo
Informacja o tekście
procedury lub funkcji
COLUMN Line FORMAT 99999
COLUMN Text FORMAT A80
SET PAGESIZE 23
SET PAUSE ON
SELECT Line, Text
FROM User_Source
WHERE Name = ‘Nazwa_procedury’;
Nazwę procedury (ewentualnie
funkcji lub pakietu) należy wpisać
dużymi literami!
Wykrywanie i diagnostyka błędów
• Po każdej CREATE PROCEDURE/FUNCTION
należy wykonywać (ewentualnie umieszczać w
skrypcie, jeśli kompilowane procedury znajdują
się w skrypcie) instrukcję SQL*Plus,
show errors
która, gdy wystąpią błędy, wypisze je na ekran.
• Przy testowaniu i wykrywaniu błędów
semantycznych można używać procedury
Dbms_output.Put_line('Pracownik:
'||v_nazwisko||
' Zarobki: ' ||To_char(v_zarobki))
Status procedury (funkcji)
bazodanowej
• Gdy zmieniają się obiekty, do których odwołuje
się
procedura
lub
funkcja,
Oracle
automatycznie dokonuje ponownej kompilacji,
gdy ta procedura lub funkcja zostanie
wywołana. Konieczność ponownej kompilacji
można odczytać z perspektywy słownika
danych
User_objects
:
SELECT Status
FROM User_objects
WHERE Object_name = 'OBLICZ_ZAROBKI';
• Konieczność ponownej kompilacji wskazuje
wartość
Status =
'INVALID'
. Aby samemu spowodować
wykonanie ponownej kompilacji:
ALTER PROCEDURE Oblicz_zarobki
COMPILE;
Uprawnienie EXECUTE
Uprawnienia do użycia procedury nadaje się w
standardowy sposób np.:
GRANT EXECUTE
ON Oblicz_zarobki
TO Moja_księgowa;
Przeładowanie nazw procedur i
funkcji
• Użycie tej samej nazwy wielokrotnie jest
wygodne w sytuacji, gdy różne, specjalne
przypadki - z logicznego punktu widzenia tej
samej procedury lub funkcji, możemy zapisać
za pomocą zbioru procedur i funkcji używając
tej samej nazwy np. dodawanie z różną liczbą
argumentów i różnymi typami danych.
• Wersje z tą samą nazwą muszą się różnić albo
liczbą parametrów albo nazwą i typem
parametru (aby system był w stanie w chwili
wykonywania wybrać właściwą wersję).
PROCEDURE Zwolnij(Numer_prac NUMBER);
PROCEDURE Zwolnij(Nazwisko
VARCHAR2);
Pakiety
Ze
względu na dużą liczbę procedur i funkcji,
jakie zwykle powstają podczas tworzenia
aplikacji, konieczne jest grupowanie ich w
większe jednostki nazywane pakietami. W ramach
pakietu możemy globalnie dla niego zdefiniować:
• kursory,
• zmienne i stałe,
• wyjątki.
Każda sesja ma swoją własną wersję pakietu.
Zmienne i stałe zachowują swoje wartości na czas
trwania sesji.
Pakiety
• część publiczna (dostępna z zewnątrz przy
posiadaniu odpowiednich uprawnień), czyli
specyfikacja
CREATE OR REPLACE PACKAGE nazwa_pakietu AS
<deklaracje obiektów publicznych (w
przypadku procedur i
funkcji specyfikacje nagłówków)>
END nazwa_pakietu;
• część prywatna (dostępna tylko z wewnątrz),
czyli część implementacyjna.
CREATE OR REPLACE PACKAGE BODY nazwa_pakietu
AS
<definicje obiektów publicznych i
prywatnych>
END nazwa_pakietu;
Kod inicjalizacyjny pakietu
Można też na koniec części implementacyjnej
dołączyć kod inicjalizacyjny pakietu.
CREATE
[OR
REPLACE]
PACKAGE
BODY
nazwa_pakietu AS
<definicje obiektów publicznych i prywatnych>
BEGIN
<instrukcje inicjalizujące>
END nazwa_pakietu;
CREATE PACKAGE
Obsługa_prac AS
PROCEDURE
Zatrudnij
(v_numer_prac
NUMBER,
v_nazwisko VARCHAR2,
v_zarobki NUMBER,
v_numer_działu NUMBER);
PROCEDURE
Zwolnij
(v_numer_prac
NUMBER);
ile_przyjętych NUMBER;
ile_zwolnionych NUMBER;
END
Obsługa_prac
;
Przykład
Inicjalizacja
pakietu
CREATE PACKAGE BODY Obsługa_prac AS
PROCEDURE Zatrudnij
( v_numer_prac NUMBER, v_nazwisko
VARCHAR2,
v_zarobki
NUMBER, v_numer_działu NUMBER) IS
BEGIN
INSERT INTO Pracownicy VALUES
(v_numer_prac, v_nazwisko, v_zarobki, v_numer_działu);
COMMIT;
ile_przyjętych := ile_przyjętych +1;
END;
PROCEDURE zwolnij
(v_numer_prac NUMBER) IS
BEGIN
DELETE FROM Pracownicy
WHERE numer_prac = v_numer_prac;
COMMIT;
ile_zwolnionych := ile_zwolnionych +1;
END
;
BEGIN
ile_przyjętych := 0;
ile_zwolnionych := 0;
END Obsługa_prac
;
Przykład
Ciało pakietu
Użycie
Z zewnątrz dostęp do obiektów publicznych pakietu
odbywa się przez podanie nazwy pakietu, kropki i
nazwy obiektu np.
Obsługa_prac.Zatrudnij(1000, 'Kowalski ', 2000,
12);
RESTRICT_REFERENCES
W przypadku funkcji deklarowanych w specyfikacji
pakietu, aby móc je używać w instrukcjach SQL
należy zamieścić dyrektywę dla kompilatora:
PRAGMA RESTRICT_REFERENCES(nazwa-funkcji,
opcja, ...)
określającą ewentualne współdziałanie funkcji z
bazą danych i zmiennymi pakietu:
• WNDS - funkcja nie modyfikuje stanu bazy
danych;
• WNPS - funkcja nie modyfikuje wartości
zmiennych pakietu;
• RNDS - funkcja nie odczytuje zawartości bazy
danych;
• RNPS - funkcja nie odczytuje wartości
zmiennych pakietu.
Dla funkcji obliczającej maksymalne zarobki
pracownika w dziale:
....
FUNCTION Maks(Id_dz INTEGER) RETURN
Real;
PRAGMA RESTRICT_REFERENCES (Maks,
WNPS, WNDS, RNPS);
...
Brak RNDS oznacza: funkcja Maks może
odczytywać stan bazy danych.
Przykład
Usuwanie
DROP PROCEDURE procedura;
DROP FUNCTION funkcja;
DROP PACKAGE pakiet;