Kurs Oracle PL/SQL. Procedury i funkcje – informacje rozszerzone

Procedury i funkcje – informacje rozszerzone

Dla procedur oraz funkcji istnieją opcjonalne parametry. Mogą występować w trzech typach

IN – parametr tylko do odczytu, poprzez który dane zostają przekazane do podprogramu.

OUT – Służy do zwracania wartości z podprogramu. Ma wartość NULL do momentu kiedy zostanie zainicjalizowana.

IN OUT – Połączenie dwóch powyższych typów. Podczas wywoływania programu tym parametrem przekazywane są do niego wartości, a po zakończeniu wykonywania zwracane. Stosuje się go gdy dane wejściowe mają zostać zmienione podczas działania programu.

Podczas deklarowania parametrów dla procedury lub funkcji nie ma sztywnych ograniczeń co do ich ilości oraz kolejności. Parametry mogę zadeklarować na kilka różnych sposobów i poniżej omawiam to na przykładach:

wejsciowy1 – parametr wejścia typu varchar2, nie posiada wartości domyślnej i dlatego przy wywoływaniu podprogramu będę zmuszony ją podać.

wejsciowy2 oraz wejsciowy3 - dwa sposoby zadeklarowania wartości domyślnej dla parametru. Jeśli nie przypiszę żadnej wartości, przypisana zostanie automatycznie wartość występująca po DEFAULT lub znaku przypisania. Podczas wywoływania podprogramu nie muszę podawać wartości do tego parametru.

wejsciowy4 – ponieważ nie określiłem jaki ma to być typ parametru, Oracle domyślnie przyjmuje że jest ot parametr typu IN.

wejsciowy5 – parametr typu wejściowego numerycznego który będę musiał uzupełnić przy wywoływaniu procedury.

wyjsciowy – parametr wyjściowy typu number. Nie mogę określić dla niego wartości domyślnej, ani wartości wejściowej przy wywołaniu programu. Mogę to zrobić jedynie wewnątrz podprogramu.

dwustronny – parametr do którego nie mogę przypisać wartości domyślnej przy wywoływaniu podprogramu. Mogę podać do niego wartość podczas wywoływania podprogramu.

Mogę określać typ parametru, nie mogę natomiast długości. Zamiast więc stosować varchar2(243) muszę zastosować samo varchar2.

Przykład wzajemnego wywoływania procedur oraz praktycznego przekazywania parametrów.

Tworzę procedurę o nazwie „wypisywacz”, która po otrzymaniu danych w parametrach wejściowych (imię i nazwisko są domyślnie IN) ma wypisać na ekranie powitanie. Ponadto do parametru wyjściowego wzrost ma przypisać wartość 178.

2. Z bloku anonimowego wywołuję przed momentem stworzoną procedurę podając wartości dla parametrów wejściowych, oraz nazwę zmiennej do której ma zostać przypisana wartość wyjściowa.

Jak widać, po wywołaniu procedury „wypisywacz” wartość zmiennej do której została przypisana wartość wewnątrz procedury „wypisywacz” uległa zmianie.

Parametrów wcale nie muszę wypisywać w dokładnie takiej kolejności w jakiej są zdeklarowane w definicji funkcji/procedury. Jedynym warunkiem jest określenie podczas wywoływania podprogramu do jakiej zmiennej przypisuję jaką wartość.

Ćwiczenia

1. Stwórz blok anonimowy posiadający zdeklarowaną zmienną rekordową odpowiadającą konstrukcji tabeli employees;

2. Stwórz procedurę posiadającą jedną stałą typu tekstowego, jedną zmienną typu liczbowego o wartości domyślnej 0, oraz jedną niezainicjowaną zmienną tekstową;

3. Rozbuduj procedurę z ćwiczenia nr. 2 w taki sposób by wprowadzała wartości do tabeli regions ze zmiennych. Procedura ma wyświetlać pod jakim ID został zapisany nowy wiersz. Wykonaj tą procedurę.

4. Stwórz procedurę która wyświetli na ekranie imię, nazwisko oraz numer telefonu pracownika o id 104

5. Stwórz blok anonimowy który sprawdzi czy wybrany pracownik ( wybierz dowolnego) jest czyimkolwiek managerem. Jakie zapytanie SQL jest do tego potrzebne możesz zapytać instruktora, lub wymyślić je sam - podpowiedź: w tabeli employees istnieje pole manager_id, określa ono pracownika o takim employee_id jak manager_id innej osoby. Ten pracownik jest jego managerem. Ilość pracowników które podlegają pod tą osobę można określić funkcją count(). Jeśli ilość osób podległych wynosi 0 - to znaczy że nie jest niczyim managerem. Jeśli ta osoba jest managerem, wyświetl informację o jego imieniu i nazwisku oraz ilości osób podlegających. Jeśli nie jest wypisz na ekranie imię, nazwisko oraz informację że nie jest niczyim managerem.

6. Przerób blok z powyższego ćwiczenia w taki sposób:

- jeśli pracownik jest managerem 1 osoby procedura ma wyświetlać informację "kierownik"

- jeśli pracownik jest managerem 3 lub więcej osób procedura ma wyświetlać informację "manager"

- jeśli pracownik jest managerem 5 lub więcej osób procedura ma wyświetlać informację "dyrektor"

- jeśli pracownik nie jest niczyim managerem procedura ma wyświetlać informację "pracownik"

7. Wyświetl na ekranie tyle razy literę X ile jest wierszy w tabeli jobs . Przy każdym wypisywanym X napisz który raz wykonywane są instrukcje w pętli. Wykonaj to ćwiczenie z wykorzystaniem każdej znanej Ci pętli występującej w PL/SQL.

8. Stwórz funkcję która będzie zwracać średnią zarobków w dziale którego numer funkcja będzie przyjmować jako parametr.

9. Stwórz procedurę która przyjmie jako parametr wejściowy wynik funkcji z ćwiczenia 8 a następnie wypisze ją na ekranie.

10. Stwórz procedurę która jako parametr wejściowy przyjmie id managera, a parametrem wyjściowym zwróci średnią zarobków osób podległych pod tego managera.

11. Przerób procedurę z powyższego ćwiczenia w taki sposób, aby posiadała tylko jeden obustronny parametr.