Kursory

Co to jest kursor?

Kursor jest mechanizmem obsługi poleceń SQL'a. Jest on przydzielany dla kasdego polecenia SQL'a. Zajmuje on pewien obszar w buforach bazy danych (w shared pool). Zawiera pierwotne oraz przetworzone polecenie SQL oraz pojedynczy wiersz wyniku.

Typy kursorów

Jawne (explicit cursors) - definiowane i obsługiwane przez programistę

Niejawne (implicit cursors) - generowane automatycznie dla kasdego zdania SQL, są obsługiwane przez jądro Oracle'a

Atrybuty kursorów

NOTFOUND - ma wartość TRUE, jeseli kursor nie zwrócił sadnego wiersza

FOUND - odwrotnie do poprzedniego atrybutu

ISOPEN - ma wartość TRUE jeseli kursor jest otwarty

ROWCOUNT - liczba wierszy wybranych przez kursor

Do atrybutów odwołujemy się podając:

nazwa_kursora%atrybut

Przykład

IF kursor1%ISOPEN

THEN

Kursory niejawne

Tworzone dla kasdego polecenia SQL wykonywanego w bloku PL/SQL'a

Mosna odwołać się tylko do ostatniego niejawnego kursora poprzez nazwę SQL

Kursorem niejawnym nie mosna sterować

Jedyny sposób odwołania się do kursora niejawnego to odwołanie się do jego atrubutów

Atrybuty kursora niejawnego

SQL%NOTFOUND

NULL - przed wykonaniem pierwszej komendy SQL

TRUE - jeseli SELECT, UPDATE lub DELETE nie wybierze sadnego wiersza lub INSERT nie powiedzie się

FALSE  - w przeciwnym przypadku

SQL%FOUND - przeciwieństwo SQL%NOTFOUND

SQL%ROWCOUNT

- liczba wierszy poprawionych zdaniem UPDATE

- liczba wierszy usuniętych poleceniem DELETE

- 0 jeseli SELECT INTO nie wybierze sadnego wiersza

- 1 jeseli SELECT INTO wybierze jeden wiersz

- 2 jeseli SELECT INTO wybierze więcej nis jeden wiersz

- NULL przed wykonaniem pierwszej komendy SQL

SQL%ISOPEN - dla kursora niejawnego będzie zawsze FALSE

Przykład

BEGIN

UPDATE emp SET sal = sal * 1.1

WHERE empno = :bl_pracownik.numer;

IF SQL%NOTFOUND

THEN

INSERT INTO uwaga

VALUES ('Nie istnieje pracownik o numerze '||

TO_CHAR(:blk_pracownik.numer) );

END IF

END;

Kursory jawne

Kursor jawny musi być zadeklarowany w sekcji deklaracji. Deklaracja polega na nadaniu nazwy odpowiedniemu zdaniu SELECT.

Składania deklaracji

CURSOR nazwa_kursora IS SELECT ;

Uwaga - jeseli w klauzuli SELECT występują wyrasenia, nalesy usyć aliasów.

Struktura oparta na kursorze

W oparciu o zadeklarowany wcześniej kursor mosna zdefiniowac zmienną ROWTYPE.

Przykład

DECLARE

CURSOR sprzedawca_ IS

SELECT empno,

ename,

sal,

comm

FROM emp

WHERE job = 'SALESMAN';

s_sprzedawca_ sprzedawca_%ROWTYPE;

Otwarcie kursora

Aby skorzystać z kursora jawnego nalesy go otworzyć instrukcją:

OPEN nazwa_kursora

Instrukcja OPEN powoduje:

zajęcie pamięci dla kursora (w obszarze shared pool)

przetworzenie zdania tworzącego kursor

przygotowaniu zestawu wierszy spełniających zapytanie

ustawienie kursora przed pierwszym wierszem z zestawu

Przykład

BEGIN

OPEN sprzedawca_;

Pobranie wiersza

Wiersz wyniku z obszaru kursora pobiera się komendą:

FETCH nazwa_kursora INTO [lista_zmiennych | struktura]

lista_zmiennych - musi zawierać tyle zmiennych, ile wartości zwraca polecenie SELECT tworzące kursor; typ zmiennych musi odpowiadać typom wyników

struktura - musi zawierać tyle pól, ile wartości zwraca zdanie SELECT tworzące kursor, typ pól musi odpowiadać typom wyników - najwygodniej jest zdefiniować strukturę opartą na kursorze

Komenda FETCH powoduje:

pobranie następnego wiersza wyniku (o ile istnieje) do obszaru kursora,

jeseli pobranie powiodło się, następuje przekazanie wyniku na zmienne lub strukturę.

Zamknięcie kursora

Kursor zamyka się instrukcją:

CLOSE nazwa_kursora

Zamknięcie kursora zwalnia obszar kursora. Po zamknięciu kursora nie mosna jus wykonać komendy FETCH.

Przykład

DECLARE

CURSOR sprzedawca_ IS

SELECT empno,

ename,

sal,

comm

FROM emp

WHERE job = 'SALESMAN';

s_sprzedawca_ sprzedawca_%ROWTYPE;

BEGIN

OPEN sprzedawca_;

FETCH sprzedawca_ INTO s_sprzedawca_;

CLOSE sprzedawca_;

END;

Atrybuty kursora jawnego

kursor%NOTFOUND

NULL - przed pierwszym wykonaniem FETCH

TRUE - jeseli ostatnia komenda FETCH nie powiodła się

FALSE - w przeciwnym przypadku

kursor%FOUND - przeciwieństwo kursor%NOTFOUND

kursor%ROWCOUNT

liczba wykonanych z powodzeniem poleceń FETCH

kursor%ISOPEN

TRUE - jeseli kursor jest otwarty

FALSE - jeseli kursor jest zamknięty

Usycie kursora jawnego

Deklaracja kursora

Deklaracja struktury lub zmiennych odpowiednich do wyniku kursora

Otwarcie kursora

Załosenie pętli zawierającej komendę FETCH; warunek zakończenia pętli powinien korzystać z atrybutów kursora

Zamknięcie kursora

Ćwiczenie 5.1

Nalesy dać podwyskę sprzedawcom, który osiągają odpowiednio wysoką prowizję. Propozycja:

set echo  off

set termout  on

set verify  off

set feedback off

set pause  off

DECLARE

CURSOR sprzedawca_ IS

SELECT empno,

ename,

sal,

comm

FROM emp

WHERE job = 'SALESMAN';

s_sprzedawca_ sprzedawca_%ROWTYPE;

BEGIN

OPEN sprzedawca_;

LOOP

FETCH sprzedawca_ INTO s_sprzedawca_;

EXIT WHEN sprzedawca_%NOTFOUND;

IF s_sprzedawca_.comm >= 0.25 * s_sprzedawca_.sal

THEN

s_sprzedawca_.sal := ROUND(s_sprzedawca_.sal * 1.1);

UPDATE EMP

SET sal = s_sprzedawca_.sal

WHERE empno = s_sprzedawca_.empno;

INSERT INTO uwaga ( komunikat )

VALUES (to_char(s_sprzedawca_.empno));

ELSE

NULL;

END IF;

END LOOP;

CLOSE sprzedawca_;

END;

prompt Numery sprzedawców, którzy dostali podwyskę:

select * from uwaga;

delete from uwaga;

set verify on

set feedback on

set echo on

Pętla FOR z usyciem zadeklarowanego kursora

Składnia

FOR struktura IN kursor

LOOP

zestaw_instrukcji;

END LOOP;

Usycie takiej pętli powoduje:

domyślną deklarację -  struktura kursor%ROWTYPE,

otwarcie kursora,

wykonanie pętli tyle razy ile jest wierszy wyniku,

zamknięcie kursora po zakończeniu pętli.

Przykład

DECLARE

CURSOR pracownik_ IS

SELECT empno,

ename,

job

FROM emp;

BEGIN

FOR s_pracownik_ IN pracownik_

LOOP

END LOOP;

END;

Kursor z parametrem

Składnia:

CURSOR kursor (par1 typ, par2 typ ) IS SELECT

Parametry mogą być usyte w SELECT jako stałe

Typ parametrów podaje się bez długości (np. tylko VARCHAR2)

Wartość parametrów nadaje się instrukcją:

OPEN kursor ( wartość1, wartość2 , )

lub

FOR struktura IN kursor ( wartość1, wartość2 , )

Przykład

DECLARE

CURSOR pracownik_ ( p_zawod_ VARCHAR2 ) IS

SELECT empno,

ename,

sal

FROM prac

WHERE job = p_zawod_;

BEGIN

FOR s_pracownik_ IN pracownik_(SPRZEDAWCA)

LOOP

END LOOP;

END;

Ćwiczenie 5.2

Przygotuj skrypt, który pozwalałby dawać dowolne podwyski, dowolnej grupie zawodowej Propozycja:

set echo  off

set termout  on

set verify  off

set feedback off

set pause  off

accept Zawod  prompt 'Podaj zawód (stanowisko): '

accept Podwyzka prompt 'Podaj wysokość podwyski (w %): '

prompt

prompt Dochody pracowników na stanowisku &Zawod przed podwyską

select ename Nazwisko,

sal Pensja,

comm Prowizja

from emp

where job = upper('&Zawod');

DECLARE

CURSOR pracownik_ (p_zawod_ VARCHAR2) IS

SELECT empno,

sal,

comm

FROM emp

WHERE job = upper(p_zawod_);

BEGIN

FOR s_pracownik_ IN pracownik_ ('&Zawod')

LOOP

UPDATE emp

SET sal = ROUND(sal * (1 + &Podwyzka/100))

WHERE empno = s_pracownik_.empno;

END LOOP;

END;

prompt

prompt Dochody pracowników na stanowisku &Zawod po podwysce

select ename Nazwisko,

sal Pensja,

comm Prowizja

from emp

where job = upper('&Zawod');

set verify on

set feedback on

set echo on

Pętla FOR z usyciem nie zadeklarowanego kursora

Składnia:

FOR struktura IN ( SELECT )

LOOP

zestaw_instrukcji;

END LOOP;

Usycie takiej pętli powoduje:

domyślną deklarację kursora opartego na poleceniu SELECT,

domyślną deklarację -  struktura kursor_domyślny%ROWTYPE,

otwarcie kursora domyślnego,

wykonanie pętli tyle razy ile jest wierszy wyniku,

zamknięcie kursora domyślnego po zakończeniu pętli.

Ćwiczenie 5.3

Przygotuj skrypt, który pozwalałby dawać dowolne podwyski, dowolnej grupie zawodowej, tym razem przy usyciu kursora domyślnego w pętli FOR.

Propozycja:

set echo  off

set termout  on

set verify  off

set feedback off

set pause  off

accept Zawod  prompt 'Podaj zawód (stanowisko): '

accept Podwyzka prompt 'Podaj wysokość podwyski (w %): '

prompt

prompt Dochody pracowników na stanowisku &Zawod przed podwyską

select ename Nazwisko,

sal Pensja,

comm Prowizja

from emp

where job = upper('&Zawod');

BEGIN

FOR s_pracownik_ IN

(

SELECT empno,

sal,

comm

FROM emp

WHERE job = upper('&Zawod')

)

LOOP

UPDATE emp

SET sal = ROUND(sal * (1 + &Podwyzka/100))

WHERE empno = s_pracownik_.empno;

END LOOP;

END;

prompt

prompt Dochody pracowników na stanowisku &Zawod po podwysce

select ename Nazwisko,

sal Pensja,

comm Prowizja

from emp

where job = upper('&Zawod');

set verify on

set feedback on

set echo on

Klauzula CURRENT OF

Składnia:

UPDATE | DELETE

WHERE CURRENT OF kursor

Klauzulę mosna usywać tylko w bloku PL/SQL'a

Kursor usywany w klauzuli musi być zbudowany na

SELECT FOR UPDATE OF kol1, kol2,

Klauzula powoduje, se UPDATE lub DELETE operuje na wierszu znajdującym się w obszarze kursora

Ćwiczenie 5.4

Przygotuj skrypt, który pozwalałby dawać dowolne podwyski, dowolnej grupie zawodowej, tym razem przy usyciu kursora zadeklarowanego tak, aby następnie usyć klauzuli CURRENT OFF. Propozycja:

set echo  off

set termout  on

set verify  off

set feedback off

set pause  off

accept Zawod  prompt 'Podaj zawód (stanowisko): '

accept Podwyzka prompt 'Podaj wysokość podwyski (w %): '

prompt

prompt Dochody pracowników na stanowisku &Zawod przed podwyską

select ename Nazwisko,

sal Pensja,

comm Prowizja

from emp

where job = upper('&Zawod');

DECLARE

CURSOR pracownik_ (p_zawod_ VARCHAR2) IS

SELECT empno,

sal,

comm

FROM emp

WHERE job = upper(p_zawod_)

FOR UPDATE OF sal;

BEGIN

FOR s_pracownik_ IN pracownik_ ('&Zawod')

LOOP

UPDATE emp

SET sal = ROUND(sal * (1 + &Podwyzka/100))

WHERE CURRENT OF pracownik_;

END LOOP;

END;

prompt

prompt Dochody pracowników na stanowisku &Zawod po podwysce

select ename Nazwisko,

sal Pensja,

comm Prowizja

from emp

where job = upper('&Zawod');

set verify on

set feedback on

set echo on