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.
- 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.
Świetny kurs, ćwiczenia są idealnym uzupełnieniem.
OdpowiedzUsuńPrzepraszam, że się czepiam, ale w zadaniu 6 pomijany jest przypadek, gdy osoba zarządza dokładnie 2-ma osobami.
OdpowiedzUsuńOczywiście kurs znakomity :)
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ńW 11 wystarczy coś takiego:
OdpowiedzUsuńCREATE OR REPLACE PROCEDURE proc_11 (wartosc IN OUT number)
IS
BEGIN
SELECT AVG(salary) INTO wartosc FROM employees WHERE manager_id = wartosc;
END;
Ten komentarz został usunięty przez autora.
OdpowiedzUsuńCREATE OR REPLACE procedure procedura_02
OdpowiedzUsuńis
zmienna1 number default 0;
zmienna2 varchar2(20);
stala constant varchar(20) := 'TEST'; -- constant;
begin
null;
end;
/
Ten komentarz został usunięty przez autora.
OdpowiedzUsuńw 6 powinna być procedura a więc:
OdpowiedzUsuń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;
Ten komentarz został usunięty przez autora.
OdpowiedzUsuń7. petla for
OdpowiedzUsuń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;
7. petla while
OdpowiedzUsuń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;
7. petla (zwykla) loop
OdpowiedzUsuń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;
Twoja pętla WHILE wykonuje się 107 razy, a powinna 108. Tak wygląda prawidłowe rozwiązanie:
OdpowiedzUsuń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.
zadanie 8
OdpowiedzUsuń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;
zadanie 8
OdpowiedzUsuń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;
wywołaj procedurę z bloku
Usuńbegin
zadanie9manager(110);
end;
-------------------------------
bo proste rozwiązania są najlepsze