sobota, 20 listopada 2010

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


i funkcje z parametrami



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.

  1. 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.
 




Ten temat omawiam na poniższych szkoleniach:
• Programowanie w PL/SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.

16 komentarzy:

  1. Świetny kurs, ćwiczenia są idealnym uzupełnieniem.

    OdpowiedzUsuń
  2. Przepraszam, że się czepiam, ale w zadaniu 6 pomijany jest przypadek, gdy osoba zarządza dokładnie 2-ma osobami.

    Oczywiście kurs znakomity :)

    OdpowiedzUsuń
  3. Dzięki za odpowiedzi, były bardzo pomocne. Wydaje mi się że w 11 nie potrzebna jest zmienna "wyj". Można wynik zapytania przekazać od razu do "param". Teraz procedura działa jakby miała parametr tylko wejściowy, bo nie jest zmieniany "param"

    OdpowiedzUsuń
  4. W 11 wystarczy coś takiego:

    CREATE OR REPLACE PROCEDURE proc_11 (wartosc IN OUT number)
    IS
    BEGIN
    SELECT AVG(salary) INTO wartosc FROM employees WHERE manager_id = wartosc;
    END;

    OdpowiedzUsuń
  5. Ten komentarz został usunięty przez autora.

    OdpowiedzUsuń
  6. CREATE OR REPLACE procedure procedura_02
    is

    zmienna1 number default 0;
    zmienna2 varchar2(20);
    stala constant varchar(20) := 'TEST'; -- constant;

    begin
    null;
    end;
    /

    OdpowiedzUsuń
  7. Ten komentarz został usunięty przez autora.

    OdpowiedzUsuń
  8. w 6 powinna być procedura a więc:
    create or replace procedure sprawdzacz
    (
    id_pracownika IN number
    )
    IS
    ilu number;
    typek employees%rowtype ;
    begin
    select * into typek from employees where employee_id=id_pracownika;
    select count(*) into ilu from employees where manager_id=id_pracownika;
    if ilu=1 then
    DBMS_OUTPUT.PUT_LINE(typek.last_name||' jest kierownikiem');
    elsif ilu>1 and ilu<4 then
    DBMS_OUTPUT.PUT_LINE(typek.last_name||' jest managerem');
    elsif ilu>4 then
    DBMS_OUTPUT.PUT_LINE(typek.last_name||' jest dyrektorem');
    else
    DBMS_OUTPUT.PUT_LINE(typek.last_name ||' nie jest niczyim menadżerem');
    end if;
    end;


    begin
    sprawdzacz(111);
    end;

    OdpowiedzUsuń
  9. Ten komentarz został usunięty przez autora.

    OdpowiedzUsuń
  10. 7. petla for

    declare
    zmienna number;
    iterator number;
    begin
    select count(*) into zmienna from employees;
    for iterator in 1..zmienna loop
    dbms_output.put_line('X' ||iterator);
    end loop;
    end;

    OdpowiedzUsuń
  11. 7. petla while

    declare
    zmienna number;
    iterator number:=1;
    begin
    select count(*) into zmienna from employees;
    while iterator<zmienna loop
    dbms_output.put_line('X' ||iterator);
    iterator:=iterator+1;
    end loop;
    end;

    OdpowiedzUsuń
  12. 7. petla (zwykla) loop

    declare
    zmienna number;
    iterator number:=1;
    begin
    select count(*) into zmienna from employees;
    loop
    dbms_output.put_line('X' ||iterator);
    iterator:=iterator+1;
    exit when iterator>zmienna ;
    end loop;
    end;

    OdpowiedzUsuń
  13. Twoja pętla WHILE wykonuje się 107 razy, a powinna 108. Tak wygląda prawidłowe rozwiązanie:
    declare
    zmienna number;
    iterator number:=0;
    begin
    select count(*) into zmienna from employees;
    while iterator<zmienna loop
    iterator:=iterator+1;
    dbms_output.put_line('X' ||iterator);
    end loop;
    end;

    Natomiast w pętli FOR nie potrzebujesz deklaracji iteratora.

    OdpowiedzUsuń
  14. zadanie 8
    procedura działa, jednak nie potrafię zrobić, żeby po jej wywołaniu wyswietliła sie ta średnia. Jak wywołuje prodedure to dostaje tylko info,że wywołała sie poprawnie i nic więcej

    set serveroutput on;
    create or replace procedure zadanie9manager(id_managera in number) AS
    ile number;
    BEGIN
    select avg(salary) into ile from employees where manager_id = id_managera;
    DBMS_OUTPUT.PUT_LINE(ile);
    END;

    SET SERVEROUTPUT on;

    OdpowiedzUsuń
  15. zadanie 8
    procedura działa, jednak nie potrafię zrobić, żeby po jej wywołaniu wyswietliła sie ta średnia. Jak wywołuje prodedure to dostaje tylko info,że wywołała sie poprawnie i nic więcej

    set serveroutput on;
    create or replace procedure zadanie9manager(id_managera in number) AS
    ile number;
    BEGIN
    select avg(salary) into ile from employees where manager_id = id_managera;
    DBMS_OUTPUT.PUT_LINE(ile);
    END;

    SET SERVEROUTPUT on;

    OdpowiedzUsuń
    Odpowiedzi
    1. wywołaj procedurę z bloku
      begin
      zadanie9manager(110);
      end;

      -------------------------------
      bo proste rozwiązania są najlepsze

      Usuń