Kursory są encją w której przechowywane są wiersze zwrócone przez zapytanie z bazy danych.
Przetwarzając kursory wykonujemy cztery operacje:
- zadeklarować kursor
- otworzyć go
- pobrać wiersze
- zamknąć kursor
Deklaracja kursora
Kursor deklarujemy w sekcji DECLARE bloku PL/SQL
Otwarcie kursora
Kursor otwieramy w sekcji wykonawczej bloku PL/SQL. W tym momencie do kursora pobierane są dane. W jednym bloku kursor możemy otworzyć tylko raz.
Pobranie wierszy z kursora
Mając dane załadowane do kursora (kursor otwarty), możemy pobrać je do zmiennych. Na poniższej ilustracji widać, że zdeklarowałem zmienne do których następnie pobieram dane z kursora. Istotne jest by pamiętać że dane zostaną załadowane do zmiennych w takiej kolejności w jakiej wypadają kolumny kursora. W tym wypadku zapytanie na podstawie którego stworzony został kursor zwraca dokładnie jeden wiersz. Gdybym z tego zapytania usunął warunek „where job_id==160” w kursorze znalazłoby się tyle wierszy ile znajduje się w tabeli jobs. W takiej sytuacji zastosowanie klauzuli FETCH zwróciłoby mi pierwszy wiersz ze zbioru danych w kursorze. Gdybym zechciał przetworzyć więcej niż jeden wiersz w wielowierszowym kursorze, musiałbym zastosować pętlę kursorową o której będzie mowa w następnych rozdziałach.
Zamknięcie kursora
Kursor zamykamy przy pomocy poniższej konstrukcji. Zamkniętego kursora nie możemy już przetwarzać.
Atrybuty kursora
Służą do określania własności kursora.
ISOPEN – Zwraca „true” jeśli kursor jest otwarty i „false” jeśli kursor jest zamknięty.
ROWCOUNT – Zwraca ilość wierszy które już zostały ściągnięte z kursora
NOTFOUND – Służy najczęściej do sprawdzania czy w kursorze są jeszcze jakieś dane. Zwraca „true” jeśli komenda FETCH nie może pobrać kolejnego wiersza. Jeśli FETCH może pobierać dane dalej, zwraca „false”
FOUND - Służy najczęściej do sprawdzania czy w kursorze są jeszcze jakieś dane. W przeciwieństwie do NOTFOUND zwraca „false” jeśli komenda FETCH nie może pobrać kolejnego wiersza. Jeśli FETCH może pobierać dane dalej, zwraca „true”
Poniżej prezentacja zastosowania atrybutów kursora. Kursor został zadeklarowany i otwarty, a następnie przetwarzany jest w pętli do momentu kiedy atrybut NOTFOUND zwróci wartość true.
Odbywa się to dzięki klauzuli EXIT WHEN. Zwróć uwagę że nie zastosowałem tutaj żadnego warunku typu „notfound=true”. Jeśli nie zdeklaruję takiego porównania, domyślnie dostawiane jest „=true” (my tego oczywiście nie widzimy – dzieje się to wewnątrzsystemowo). Na koniec korzystam z atrybutu ROWCOUNT aby określić ile ostatecznie wierszy zostało pobrane z kursora.
Sprawdzanie ilości wierszy wykonuję jeszcze na otwartym kursorze, przed jego zamknięciem, ponieważ w innym wypadku otrzymałbym komunikat o błędzie „invalid cursor” i program zostałby przerwany.
Pętla kursorowa
Stosowanie konstrukcji ujętej w poprzednim rozdziale nie jest najwygodniejszym rozwiązaniem w praktyce. Zaprezentowałem je by zapoznać czytelnika z mechanizmami związanymi z kursorami i pomóc zrozumieć zasady ich działania. Pętla kursorowa jest konstrukcją znacznie prostszą, nie musimy sprawdzać czy jeszcze są jakieś dane do pobrania ani otwierać i zamykać kursora.
Poniższa pętla wykona się tyle razy ile będzie wierszy w kursorze. Poniższa pętla pobierze po kolei wszystkie wiersze z kursora i wrzuci je do zmiennej rekordowej stworzonej na podstawie konstrukcji tabeli „employees” z której dane pobieram do rekordu.
Kursory sparametryzowane
Na pierwszy rzut oka kursory wydają się być przyjemne i wygodne w użyciu. Jest jeden problem …
Przy wiedzy którą dotychczas posiadamy nie możemy zmieniać zapytania na podstawie którego tworzony jest kursor. A co jeśli zechcemy zmienić parametr w warunku WHERE zapytania?
Kursory parametryzować mogę na dwa sposoby. Poprzez zmienną lub poprzez parametr. Oba zaprezentowałem na poniższym przykładzie. Pierwszy z kursorów pobiera wartość do warunku where ze zmiennej której wartość ustalam przed otwarciem kursora. Drugi otrzymuje tą wartość jako parametr uruchomieniowy przy otwieraniu kursora. Dlaczego to istotne? Dzięki temu możemy napisać jeden generyczny program narzędziowy który można wykorzystywać wielokrotnie, zamiast dublować ten sam kod ze zmienionym jednym parametrem.
Ćwiczenia
1. Stwórz kursor który pobierze dane o pracownikach zarabiających więcej niż średnia zarobków w firmie, a następnie wyświetli na konsoli ich imiona, nazwiska, wypłatę oraz średnia zarobków w firmie.
2. Stwórz kursor sparametryzowany, który wyświetli na ekranie wszystkich pracowników managera o id podanym jako parametr kursora. Parametr do kursora ma zostać przekazany z parametru procedury.
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.
a może by tak wspomnieć, że w PL/SQL istnieją dwa typy kursorów i istnieje ku temu nawet jakiś powód?
OdpowiedzUsuńTen komentarz został usunięty przez autora.
UsuńWszystko po trochu, jak bym miał tak uwzględniać wszystko, to ten kurs ukazałby się może za rok i byłby mega kobyłą do przerobienia.
OdpowiedzUsuńKurs jest ok. Dzieki wielkie :)
OdpowiedzUsuńWarto by było wspomnieć, że atrybuty FOUND i NOTFOUND zwracają NULL jeżeli nie pobraliśmy wcześniej danych z kursora. Dlatego zastosowanie pętli while w taki sposób:
OdpowiedzUsuń==========================
declare
kursor cursor is select * from employees;
begin
open kursor;
while kursor%found loop
--jakis kod
end loop;
end;
==========================
nie powiedzie się, tzn. nie zostanie pobrany ani jeden wiersz. Należałoby najpierw wykonać jeden fetch, a potem tą pętlą próbować czyli tak:
==========================
declare
kursor cursor is select * from employees;
begin
open kursor;
fetch kursor into zmienneKtorychTutajNiePisze;
while kursor%found loop
--jakis kod
end loop;
end;
==========================
Oczywiście najlepiej zastosować pętlę kursorową i po sprawie. Można też pętlę loop z exit when ale chcę tylko zaznaczyć tą rzecz, bo może komuś się to przyda.
Pozdrawiam
where job_id==160 - gdzie w tabeli jobs mamy taki job_id?
OdpowiedzUsuńco do notfound i found trzeba by bylo to sprecyzowac, bo troche to niejasne stwierdzenie przykladowo o notfound: 'Zwraca „true” jeśli komenda FETCH nie może pobrać kolejnego wiersza. Jeśli FETCH może pobierać dane dalej, zwraca „false” '.
OdpowiedzUsuńNOTFOUND zwraca TRUE jesli ostatnia komenda FETCH nie zwrocila zadnego wiersza.
i jeszcze to zdanie bo też razi: " ... i wrzuci je do zmiennej rekordowej stworzonej na podstawie konstrukcji tabeli „employees” z której dane pobieram do rekordu." Bzdura, zmienna indeksujaca jest zmienna rekordowa i jest to zmienna lokalna widziana tylko wewnatrz petli. Zatem nie trzeba nic deklarowac bo ta zmienna ma strukture odpowiadajaca strukturze rekordu kursora. Poza petlą zmienna emp ktora tam zostala zadeklarowana bedzie pusta... Nic do niej nie będzie wrzucane...
OdpowiedzUsuńJak dobrze, że to napisałeś. Przykład w prowadza w błąd. Nie działał u mnie i nie wiedziałem dlaczego. Dzięki Tobie już wiem.
UsuńPrawdopodobnie dlatego, że warunek jest za daleko. Powinien pojawić się po fetch-u.
OdpowiedzUsuńexit when kursor%notfound;
1.
OdpowiedzUsuńdeclare
srednia number;
abc employees%rowtype;
cursor first is
select * from employees where salary > (select avg(salary) from employees);
BEGIN
select avg(salary) into srednia from employees;
for abc in first loop
dbms_output.put_line(abc.name||' '||abc.last_name||' '||abc.salary);
end loop;
dbms_output.put_line('Średnia zarobków w firmie jest równa = '||srednia);
end;
2.
create or replace procedure nowa(a number) is
abc employees%rowtype;
cursor first is
select * from employees where manager_id = a;
begin
for abc in first loop
dbms_output.put_line(abc.name||' '||abc.last_name);
end loop;
end;
później:
declare
begin
nowa(12);
end;
3.
create or replace procedure kursor1 is
abc departments.department_name%type;
cursor kursor1 is select * from departments;
type tablica1 is table of departments.department_name%type index by binary_integer;
tab tablica1;
i number;
begin
i := 0;
for abc in kursor1 loop
tab(i):= abc.department_name;
i := i+1;
dbms_output.put_line('tab('||i||')= '||abc.department_name);
end loop;
end;
Przez zadanie 3 przeszedłem jak niżej:
OdpowiedzUsuńcreate or replace procedure kursor is
type tablica is table of departments%rowtype index by binary_integer;
tab tablica;
licznik number := 0;
cursor my_cur is select * from departments;
begin
for rec in my_cur loop
tab(licznik) := rec;
licznik := licznik + 1;
end loop;
for i in tab.first..tab.last loop
dbms_output.put_line(tab(i).department_name || ' , ' || tab(i).department_id);
end loop;
end;
/
begin
kursor;
end;
1.
OdpowiedzUsuńDECLARE
emp employees%ROWTYPE;
srednia number;
CURSOR cur IS
select * from employees where salary > (select avg(salary) from employees);
BEGIN
FOR emp IN cur LOOP
dbms_output.put_line('Imie: '||emp.first_name||', Nazwisko: '||emp.last_name||', Wyplata: '||emp.salary);
END LOOP;
SELECT ROUND(AVG(salary),2) INTO srednia FROM employees;
dbms_output.put_line('Średnia: '||srednia);
END;
2.
CREATE OR REPLACE PROCEDURE kursor_proc (id IN number) IS
pracownik employees%ROWTYPE;
CURSOR sparametryzowany IS
SELECT * FROM employees WHERE manager_id=id;
BEGIN
dbms_output.put_line('Manager id: '||id);
FOR pracownik IN sparametryzowany LOOP
dbms_output.put_line('Imie: '||pracownik.first_name||', Nazwisko: '||pracownik.last_name);
END LOOP;
END;
DECLARE
BEGIN
kursor_proc(100);
END;
3.
CREATE OR REPLACE PROCEDURE cur_3 IS
TYPE tablica IS TABLE OF departments%ROWTYPE INDEX BY binary_integer;
tab tablica;
licznik number;
CURSOR cur IS
SELECT * FROM departments;
BEGIN
licznik := 0;
FOR tab IN cur LOOP
dbms_output.put_line('Departament: '||tab.department_id||', Nazwa: '||tab.department_name||', Manager: '||tab.manager_id||', Lokacja: '||tab.location_id);
END LOOP;
END;
DECLARE
BEGIN
cur_3;
END;
Zmienna "licznik" jest niepotrzebna. Została z poprzedniej koncepcji.
UsuńTen komentarz został usunięty przez autora.
OdpowiedzUsuń