Rozdzial 10: Procedury i funkcje skladowane

1
Rozdzial 10 Procedury i funkcje skladowane
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
2
Wprowadzenie do jezyka PL/SQL

• jezyk PL/SQL - rozszerzenie SQL o elementy
  programowania proceduralnego
• mozliwosc wykorzystywania
• zmiennych i stalych
• instrukcji sterujacych IF
• petli
• kursorów
• wyjatków

(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
3
Zmienne

• zmienne proste (np. numeryczne, znakowe)
• zmienne zlozone
• rekordowe
• tablicowe

deklarowanie zmiennych DECLARE nazwa_zmiennej
typ(dlugosc)
DECLARE licznik number(4) znak
char(1) flaga boolean osoba
pracownicy.nazwiskoTYPE pracownik_rekord
pracownicyROWTYPE
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
4
Nadanie wartosci zmiennym
licznik 10 znak 'A' flaga boolean
TRUE osoba 'BIALY' pracownik_rekord.placa_p
od 400
Stale
definiowanie stalych DECLARE nazwa_zmiennej
CONSTANT typ(dlugosc) wartosc
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
5
Instrukcja warunkowa IF
IF warunek1 THEN ... / wykonaj polecenia
/ ELSIF warunek2 THEN ... / wykonaj polecenia
/ . . . . . . . . . . . . . . . . . . . . . .
ELSIF warunekn THEN ... / wykonaj polecenia
/ ELSE ... / wykonaj polecenia / END IF
Petla podstawowa
LOOP / polecenia / EXIT WHEN warunek END
LOOP
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
6
Petla WHILE
WHILE warunek LOOP / polecenia / END LOOP
Petla numeryczna FOR
FOR zmienna_licznikowa IN x..y LOOP /
polecenia / END LOOP
Petla FOR z kursorem
FOR zmienna_rekord IN kursor LOOP / polecenia
/ END LOOP
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
7
Kursor

• Kazde zapytanie SQL umieszczone w programie
  PL/SQL jest wykonywane w tzw. obszarze roboczym.
  Serwer bazy danych wykorzystuje ten obszar do
  przechowywania danych otrzymanych w wyniku
  zapytania oraz do przechowywania dodatkowych
  informacji dotyczacych stanu wykonywanego
  zapytania, tzw. atrybutów. Kursor (ang. cursor)
  jest konstruktorem PL/SQL umozliwiajacym
• nadanie nazwy temu obszarowi,
• dostep do niego,
• pobranie z niego danych,
• kontrole procesu przetwarzania danych

(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
8
Kursor cd.
DECLARE CURSOR pracownik_kursor IS SELECT
nazwisko, placa_pod, id_zesp FROM
pracownicy osoba_nazwisko pracownicy.nazwiskoT
YPE osoba_placa pracownicy.placa_podTYPE oso
ba_id_zesp pracownicy.id_zespTYPE pracownik_da
ne pracownik_kursorROWTYPE
BEGIN OPEN pracownik_kursor FETCH
pracownik_kursor INTO osoba_nazwisko,
osoba_placa, osoba_id_zesp FETCH
pracownik_kursor INTO pracownik_dane CLOSE
pracownik_kursor END
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
9
Atrybuty kursora

• z kazdym kursorem sa zwiazane cztery atrybuty, w
  których jest przechowywana informacja o przebiegu
  operacji wykonywanych przez kursor
• NOTFOUND
• FOUND
• ROWCOUNT
• ISOPEN

LOOP FETCH pracownik_kursor INTO
pracownik_dane EXIT WHEN pracownik_kursorNOTFOUN
D ................... END LOOP
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
10
Procedury i funkcje skladowane

• cechy
• przechowywane w bazie danych
• postac skompilowana -gt zwiekszenie szybkosci
  dzialania
• wspóldzielone przez wielu uzytkowników

(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
11
Definiowanie procedury i funkcji
CREATE OR REPLACE PROCEDURE nazwa (parametr,
...) IS .................. BEGIN .............
..... END nazwa
specyfikacja
deklaracje stalych, zmiennych, kursorów
cialo - polecenia PL/SQL i SQL
CREATE OR REPLACE FUNCTION nazwa (parametr,
...) RETURN wyznaczany_typ IS ................
.. BEGIN .................. END nazwa
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
12
Predefiniowana procedura obslugi bledów

• zatrzymuje dzialanie podprogramu
• wycofuje wszelkie zmiany dokonane przez
  transakcje, w ramach której podprogram jest
  wykonywany
• wyswietla numer bledu i komunikat, podane jako
  parametry jej wywolania

raise_application_error (numer_bledu, tekst
bledu)
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
13
Procedura - przyklad 1
CREATE OR REPLACE PROCEDURE nowy_pracownik
(nazwisko_pracownika char, nazwisko_szefa
char, etat char, pensja number,
nazwa_zespolu char) IS nr_szefa
pracownicy.id_pracTYPE nr_zespolu
pracownicy.id_zespTYPE BEGIN SELECT id_prac
INTO nr_szefa FROM pracownicy WHERE
nazwiskonazwisko_szefa SELECT id_zesp INTO
nr_zespolu FROM zespoly WHERE
nazwanazwa_zespolu INSERT INTO pracownicy
VALUES (seq_pracownik.nextval,
nazwisko_pracownika, etat, nr_szefa, sysdate,
pensja, null, nr_zespolu) END
nowy_pracownik /
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
14
Zapisywanie procedury w bazie danych

• zapamietac procedure w pliku tekstowym
• wykonac polecenia zawarte w tym pliku wydajac
  polecenie

SQLgt _at_proc1
lub
SQLgt start proc1

• w przypadku bledu w procedurze, na ekranie pojawi
  sie komunikat
• aby odszukac miejsce, w którym wystapil blad
  nalezy wydac polecenie SHOW ERRORS
• po bezblednym zapisaniu procedury w bazie danych
  na ekranie pojawi sie komunikat

Warning Procedure Function created with errors.
Procedure created.
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
15
Wywolanie procedury
SQLgt EXECUTE nowy_pracownik(JANKIEWICZ,
BIALY, - gt ASYSTENT, 700, SYSTEMY
ROZPROSZONE) PL/SQL procedure successfully
completed.
Wyswietlanie informacji na ekranie

• predefiniowana procedura

dbms_output.put_line (informacja)

• odblokowanie mechanizmu wyprowadzania informacji
  na ekran

SQLgt set serveroutput on
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
16
Zadanie

• 5-1. Zmodyfikuj cialo procedury nowy_pracownik
  tak, aby nowemu pracownikowi nadawany byl numer
  (id_prac) wiekszy o 5 od najwiekszego numeru
  pracownika zapisanego w relacji pracownicy

CREATE OR REPLACE PROCEDURE nowy_pracownik
(nazwisko_pracownika char, nazwisko_szefa char,
etat char, pensja number, nazwa_zespolu
char) IS nr_szefa pracownicy.id_pracTYPE
nr_zespolu pracownicy.id_zespTYPE max_nr
number(5) BEGIN SELECT id_prac INTO nr_szefa
FROM pracownicy WHERE nazwiskonazwisko_szefa SEL
ECT id_zesp INTO nr_zespolu FROM zespoly WHERE
nazwanazwa_zespolu SELECT MAX(id_prac) INTO
max_nr FROM pracownicy INSERT INTO pracownicy
VALUES (max_nr5, nazwisko_pracownika, etat,
nr_szefa, sysdate, pensja, null,
nr_zespolu) END nowy_pracownik /
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
17
Procedura - przyklad 2
CREATE OR REPLACE PROCEDURE usun_pracownika
(identyfikator in pracownicy.id_pracTYPE)
IS nie_ma_pracownika EXCEPTION BEGIN DELETE
FROM pracownicy WHERE id_pracidentyfikator I
F SQLNOTFOUND THEN RAISE nie_ma_pracownika EN
D IF EXCEPTION WHEN nie_ma_pracownika
THEN raise_application_error(-20001, 'Nie
istnieje pracownik o podanym id') END
usun_pracownika /
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
18
Pakiet procedur i funkcji

• pakiet (ang. package) grupuje procedury i funkcje
• cechy
• ukrycie informacji - uzytkownikowi jest
  udostepniana tylko specyfikacja pakietu
  (interfejs), natomiast implementacja procedur i
  funkcji jest niewidoczna
• zwiekszenie funkcjonalnosci - zmienne
  zadeklarowane w pakietach istnieja przez cala
  sesje uzytkownika dzieki temu moga one sluzyc do
  wzajemnej komunikacji róznych procesów
• zwiekszenie szybkosci dzialania
• wspóldzielenie przez wielu uzytkowników

(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
19
Definiowanie pakietu
CREATE OR REPLACE PACKAGE nazwa_pakietu
IS END nazwa_pakietu
specyfikacja
deklaracje stalych, zmiennych, kursorów dostepnych
na zewnatrz pakietu
deklaracje procedur i funkcji
cialo
CREATE OR REPLACE PACKAGE BODY nazwa_pakietu
IS END nazwa_pakietu
deklaracje stalych, zmiennych, kursorów dostepnych
wewnatrz pakietu
definicje procedur i funkcji
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
20
Pakiet - przyklad
CREATE OR REPLACE PACKAGE kadry IS PROCEDURE
nowy_pracownik(...) PROCEDURE
usun_pracownika(...) END kadry /
CREATE OR REPLACE PACKAGE BODY kadry
IS PROCEDURE nowy_pracownik(...) IS BEGIN
... END nowy_pracownik PROCEDURE
usun_pracownika(...) IS BEGIN ... END usun END
kadry /
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej
21
Wywolanie procedury lub funkcji pakietu

• zdefiniowana w pakiecie procedure lub funkcje
  wywolujemy poprzedzajac ja nazwa pakietu, np.

SQLgt execute kadry.usun_pracownika(230)
Kompilowanie procedury, funkcji, pakietu
alter procedure function nazwa compile
alter package nazwa compile package body
Usuwanie procedury, funkcji, pakietu
drop procedure function package body
package
(c) 1999, Instytut Informatyki Politechniki
Poznanskiej