sobota, 20 listopada 2010

Kurs Oracle PL/SQL. Kursory


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


Składa się on z podania nazwy kursora oraz zapytania na podstawie którego mają zostać do kursora „wciągnięte” dane.



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.
  1. Stwórz procedurę. Zadeklaruj tablicę obiektów o strukturze pól takiej jak rekord w tabeli departments. Stwórz kursor który pobierze wszystkie dane z tabeli departments, a następnie umieści je w tablicy.



 






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.

15 komentarzy:

  1. a może by tak wspomnieć, że w PL/SQL istnieją dwa typy kursorów i istnieje ku temu nawet jakiś powód?

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

      Usuń
  2. 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ń
  3. Kurs jest ok. Dzieki wielkie :)

    OdpowiedzUsuń
  4. 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:

    ==========================

    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

    OdpowiedzUsuń
  5. 1.
    declare
    typki employees%rowtype;

    srednia number;

    cursor tacybogaci
    is
    select * from employees where salary > (select avg(salary) from employees);


    begin

    select round(avg(salary),2) into srednia from employees;

    for typki in tacybogaci
    loop
    DBMS_OUTPUT.PUT_LINE(typki.last_name || ' ' || typki.salary ||' '|| srednia);
    end loop;

    end;

    2.
    declare

    typek employees%rowtype;

    cursor sparam(param number)
    is
    select * from employees where manager_id=param;


    begin

    for typek in sparam(100)
    loop
    dbms_output.put_line(typek.last_name);
    end loop;


    end;

    3. dodam, na razie mi nie wychodzi

    OdpowiedzUsuń
  6. 3.

    create or replace procedure zad3kursory
    is

    dzialy departments%rowtype;

    i number;

    type tablica is table of departments%rowtype index by binary_integer;


    tab tablica;

    cursor kursor is
    select * from departments;

    begin

    i:=0;

    open kursor;

    loop

    fetch kursor into tab(i);
    DBMS_OUTPUT.PUT_LINE(tab(i).department_name || ' juz w tablicy');
    i:=i+1;

    exit when kursor%notfound;
    end loop;

    close kursor;

    end;

    -- jakis blad mi wyskakuje, ale dziala

    OdpowiedzUsuń
    Odpowiedzi
    1. Prawdopodobnie dlatego, że warunek jest za daleko. Powinien pojawić się po fetch-u.

      exit when kursor%notfound;

      Usuń
  7. where job_id==160 - gdzie w tabeli jobs mamy taki job_id?

    OdpowiedzUsuń
  8. 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” '.

    NOTFOUND zwraca TRUE jesli ostatnia komenda FETCH nie zwrocila zadnego wiersza.

    OdpowiedzUsuń
  9. 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ń
  10. 1.
    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;

    OdpowiedzUsuń
  11. Przez zadanie 3 przeszedłem jak niżej:

    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;

    OdpowiedzUsuń
  12. 1.
    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;

    OdpowiedzUsuń
    Odpowiedzi
    1. Zmienna "licznik" jest niepotrzebna. Została z poprzedniej koncepcji.

      Usuń