PL/SQL
Zajęcia nr IV
Procedura w PL/SQL
• Procedura PL/SQL jest nazwaną
jednostką programu, która realizuje akcję opisaną instrukcjami
• Procedura PL/SQL jest składowana to znaczy, że jest osadzana jako obiekt w schemacie użytkownika
Składnia tworzenia procedury PL/SQL
CREATE [ OR REPLACE ] PROCEDURE nazwa_procedury [
(
nazwa_parametru [rodzaj] typ_parametru [domyslność]
[, nazwa_parametru [rodzaj] typ_parametru [domyslność] ]
* )
] IS|AS
Blok anonimowy bez słowa kluczowego DECLARE
Pominięcie OR REPLACE spowoduje niemożność ponownego osadzenia kodu.
Po słowie kluczowym END może występować, oddzielona spacją, nazwa procedury.
Parametry procedury PL/SQL
Deklaracje typów parametrów nie zawierają rozmiaru.
Dopuszczone są typy wierszowe, rekordowe i samodzielnie definiowane
Rodzaje parametrów to:
• IN (domyślny) – dla parametrów wejściowych
• OUT - dla parametrów wyjściowych
• IN OUT – dla parametrów wejściowo- wyjściowych
Domyślność jest określona przez użycie słowa DEFAULT i podania wartości Zwalnia to programistę z obowiązku podania wartości zmiennej w wywołaniu
Przykład procedury PL/SQL
CREATE OR REPLACE PROCEDURE Zwiekszenie_Stawki (
n_ile NUMBER
, v_stanowisko VARCHAR2 DEFAULT 'PRESIDENT' ) IS
BEGIN
UPDATE NEW_EMP
SET sal = sal + n_ile
WHERE UPPER( job ) = UPPER( v_stanowisko );
END Zwiekszenie_Stawki;
Wywoływanie procedury PL/SQL
z SQL/Plus (wywołanie w całości musi być w jednej linii):
Execute nazwa_procedury;
lub
Begin
nazwa_procedury;
END;
Inicjowanie wartości parametrów BEGIN
zwiekszenie_stawki( 100, ‘CLERK’ );
END;
BEGIN
zwiekszenie_stawki( 100 );
END;
BEGIN
zwiekszenie_stawki(
n_ile => 100
, v_stanowisko => ‘CLERK’ );
END;
BEGIN
zwiekszenie_stawki(
v_stanowisko => ‘CLERK’
, n_ile => 100);
END;
Zadanie 1
Zbudować procedurę waloryzacja o parametrach
numer_departamentu (liczbowy), nazwa_stanowiska (znakowy), procent (liczbowy),
której zadaniem jest podnieść o odpowiedni procent wielkość pensji na danym stanowisku i w danym departamencie w tabeli new_emp
Procedura ma wypisywać komu podniosła pensję i o ile (kwotowo)
W przypadku niezmodyfikowania żadnej pensji – procedura powinna opisać ten fakt odpowiednim komunikatem
zawierającym również nazwy parametrów i ich wartości.
Funkcja w PL/SQL
• Funkcja PL/SQL jest nazwaną jednostką programu, która realizuje akcję opisaną instrukcjami i zwraca wynik typu
określonego w swojej deklaracji
Składnia tworzenia funkcji PL/SQL
CREATE [ OR REPLACE ] FUNCTION nazwa_funkcji [
(
nazwa_parametru [rodzaj] typ_parametru [domyslność]
[, nazwa_parametru [rodzaj] typ_parametru [domyslność] ]
* )
] RETURN określenie_typu IS|AS
Blok anonimowy bez słowa kluczowego DECLARE
Przykład funkcji PL/SQL
CREATE OR REPLACE FUNCTION zatrudnieni
( n_deptno NUMBER ) RETURN VARCHAR2 IS v VARCHAR2(4000);
CURSOR c IS SELECT ename FROM EMP
WHERE deptno = n_deptno;
BEGIN
FOR r IN c LOOP
v := v || ', ' || r.ename;
END LOOP;
RETURN LTRIM( v, ' ,' );
Zadanie 2
Utworzyć funkcję, która na podstawie numeru pracownika danego parametrem n_empno tworzy wiersz postaci:
Nazwisko zatrudniony w nazwa_departamentu zarabia kwota od daty zatrudnienia data_zatrudnienia
I zwraca go do programu wywołującego (gdzie następuje wypisanie wiersza)
Ewentualne nieznalezienie ma być obsługiwane komunikatem o braku znalezienia osoby danej parametrem o odpowiedniej wartości
Pakiety w PL/SQL
• Umożliwiają grupowanie kursorów, zmiennych, stałych, procedur, funkcji i wyjątków w grupy jednostek z uwagi na tematykę, osadzanie, używanie lub nadawanie uprawnień.
• Pakiety składają się z części publicznej (specyfikacji) i prywatnej (implementacja).
Udostępnione są do używania w środowiskach Oracle tylko te elementy, które są w części
publicznej.
Składnia pakietów
Deklaracja specyfikacji:
CREATE [OR REPLACE]
PACKAGE pkg_name IS|AS
deklaracje obiektów publicznych
specyfikacja nagłówków funkcji / procedur
END [pkg_name];
Składnia pakietów
Deklaracja ciała:
CREATE [OR REPLACE]
PACKAGE BODY pkg_name IS|AS
[deklaracje funkcji / procedur z implementacją poniżej]
definicje obiektów prywatnych
implementacja funkcji / procedur [BEGIN
instrukcje inicjalizacyjne pakietu ]
Przykład
CREATE OR REPLACE PACKAGE BODY Wdo AS dsp VARCHAR2(1) ;
PROCEDURE disable IS BEGIN
dsp := 'N';
END;
PROCEDURE put_line( v VARCHAR2 ) IS BEGIN
IF dsp = 'T' THEN
DBMS_OUTPUT.PUT_LINE( v );
END IF;
END;
PROCEDURE enable IS BEGIN
dsp := 'T';
DBMS_OUTPUT.ENABLE( 1000000 );
END;
BEGIN
dsp := 'N';
END Wdo;
CREATE OR REPLACE PACKAGE Wdo AS PROCEDURE put_line( v VARCHAR2 );
PROCEDURE enable;
PROCEDURE disable;
END Wdo;
Zadanie
Zbudować pakiet zawierający 2 jednostki programowe:
1. Funkcja jaki_prog o parametrze:
n_roczny_przychod – kwota rocznych przychodów, Zwracająca:
1: gdy mieści się w pierwszym progu <0,30000>
2: gdy jest wyższa
2. Procedura wylicz_podatek o parametrze
n_roczny_przychod – kwota rocznych przychodów, Wypisująca kwotę podatku z pierwszego progu (10%) i kwotę podatku z drugiego progu (30%) oraz łączną kwotę podatku w postaci:
próg 1: 3000 próg 2: 3000 ---
łącznie: 6000 z kwoty 40000
Użyć funkcji jaki_prog do określenia wszystkich progów pracowników z tabeli EMP w zapytaniu SQL (nazwisko -
