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.

17 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. 1. declare costam employees%rowtype; begin; null; end;

    2.
    create or replace procedure zadaniedomowe
    is

    tekst1 varchar(20) constant:='to tekst1';
    numerek number default 0;
    tekst2 varchar2(20);


    begin
    null;
    end;

    3.
    create or replace procedure zadaniedomowe
    is

    tekst1 constant varchar(20):='to tekst1';
    numerek number default 0;
    tekst2 varchar2(20);


    begin
    insert into regions values(numerek, tekst1) returning region_id into tekst2;
    DBMS_OUTPUT.PUT_LINE('nowy region id to '||tekst2);

    end;

    4.
    create or replace procedure wyswietlastocztery
    is

    naszpracownik employees%rowtype;

    begin
    select * into naszpracownik from employees where employee_id=104;

    DBMS_OUTPUT.put_line(naszpracownik.first_name || naszpracownik.last_name ||naszpracownik.phone_number);
    end;

    5.
    declare

    ilu number;
    typek employees%rowtype;

    begin

    select * into typek from employees where employee_id=100;
    select count(*) into ilu from employees where manager_id=100;

    if ilu>0
    then
    DBMS_OUTPUT.PUT_LINE(typek.last_name || ilu || ' osob podlegajacych');
    else
    DBMS_OUTPUT.PUT_LINE(typek.last_name ||' nie jest niczyim menadżerem');
    end if;

    end;

    6.
    declare
    -- tak, wiem mozna bylo lepiej to zrobic inaczej

    ilu number;
    typek employees%rowtype;

    begin

    select * into typek from employees where employee_id=100;
    select count(*) into ilu from employees where manager_id=100;

    if ilu=0
    then
    DBMS_OUTPUT.PUT_LINE('pracownik');
    end if;


    if ilu=1 or ilu=2
    then
    DBMS_OUTPUT.PUT_LINE('kierownik');
    end if;

    if ilu=3 or ilu=4
    then
    DBMS_OUTPUT.PUT_LINE('menadżer');
    end if;

    if ilu>4
    then
    DBMS_OUTPUT.PUT_LINE('menadżer');
    end if;

    end;

    7.
    declare
    ile number;

    begin
    select count(*) into ile from jobs;

    for iterator in 0..ile
    loop
    DBMS_OUTPUT.PUT_LINE(iterator || ' X');
    end loop;

    -- nie chce mi sie wiecej petli robic, to proste

    end;

    8.

    create or replace function sr_zarobkow_z_departments(dep_id number)
    return number
    is
    wynik number;

    begin
    select avg(salary) into wynik from employees where department_id=dep_id;

    return wynik;
    end;

    9.
    create or replace procedure wypisuje_sr_z_departamentu(wynikfunkcji in number)
    is

    begin
    dbms_output.put_line(wynikfunkcji);

    end;

    -- a następnie
    declare
    aa number;

    begin
    aa:=SR_ZAROBKOW_Z_DEPARTMENTS(10);
    WYPISUJE_SR_Z_DEPARTAMENTU(aa);

    end;

    10.
    create or replace procedure srednia_zarobkow_podleglych_managera(id_menagera in number, srednia out number)
    is

    begin
    select avg(salary) into srednia from employees where manager_id=id_menagera;

    end;

    11.
    create or replace procedure srednia_zarobkow_podleglych(param in out number)
    is
    wej number;
    wyj number;

    begin
    wej:=param;

    select avg(salary) into wyj from employees where manager_id=wej;

    end;

    -- moje rozwiązania
    -- może komuś pomogą
    -- komentarze, zwłaszcza krytyczne, pożądane

    OdpowiedzUsuń
    Odpowiedzi
    1. 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"

      Usuń
    2. Ten komentarz został usunięty przez autora.

      Usuń
    3. CREATE OR REPLACE procedure procedura_02
      is

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

      begin
      null;
      end;
      /

      Usuń
    4. Ten komentarz został usunięty przez autora.

      Usuń
    5. 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;

      Usuń
    6. Ten komentarz został usunięty przez autora.

      Usuń
    7. 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;

      Usuń
    8. 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;

      Usuń
    9. 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;

      Usuń
    10. 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.

      Usuń
  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. 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ń
  6. 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ń