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;