PL/SQL(3)

M. Rakowski - WSISiZ

1

PL/SQL

Zajęcia nr III

PL/SQL(3)

M. Rakowski - WSISiZ

2

Instrukcje SQL w PL/SQL

• Instrukcje języka SQL są w PL/SQL są

analogiczne do oferowanych przez RDBMS
Instrukcje dzielą się na związane z kursorami
(SELECT + DML) oraz sterowaniem
transakcjami (COMMIT, ROLLBACK, SET
TRANSACTION, LOCK TABLE, SAVEPOINT)

• Kursory dzielą się na jawne (deklarowane

przez użytkownika) i niejawne – będące
wywołaniami poleceń SQL (w prawie
identycznej postaci)

PL/SQL(3)

M. Rakowski - WSISiZ

3

Kursor

Wykonaniu instrukcji SQL w RDBMS

towarzyszy zaalokowanie prywatnego

obszaru roboczego, który jest buforem

wykonania instrukcji i stanowi

udostępnienie danych pobieranych lub

informacji o realizacji instrukcji.

Kursor, deklarowany w PL/SQL, jest

zmienną, którą można kojarzyć ze

zdaniem SQL – instrukcją PL/SQL.

PL/SQL(3)

M. Rakowski - WSISiZ

4

Atrybuty kursora

Przetwarzaniu wierszy przez kursor (jawny lub nie) przez

towarzyszą atrybuty, którym nadawane są odpowiednie
wartości

•

kursor%FOUND - zawiera wartość BOOLEAN czy
zaczytano kolejny wiersz

•

kursor%NOTFOUND - zawiera wartość BOOLEAN czy nie
przechwycono wiersza

•

kursor%ISOPEN - zawiera wartość BOOLEAN czy kursor
jest otwarty

•

kursor%ROWCOUNT – zawiera wartość NUMBER - liczba
sprowadzonych wierszy

W przypadku kursora niejawnego nazwą kursora jest „SQL”

PL/SQL(3)

M. Rakowski - WSISiZ

5

Kursory niejawne -

wykorzystanie

Wybór pojedynczych danych (SELECT z klauzulą INTO)

Przykłady:

-- przykład 1
DECLARE
v_ename EMP.ename%TYPE;
n_sal EMP.SAL%TYPE := 5000;
BEGIN
SELECT ename
INTO v_ename

FROM EMP
WHERE sal = n_sal;

DBMS_OUTPUT.PUT_LINE( v_ename );
END;

-- przykład 2
DECLARE
r_dept DEPT%ROWTYPE;
n_deptno DEPT.deptno%TYPE := 10;
BEGIN
SELECT *
INTO r_dept

FROM DEPT
WHERE deptno = n_deptno;

DBMS_OUTPUT.PUT_LINE( r_dept.loc

||' - '
|| r_dept.dname );

END;

Zadanie:
1. Wykonać przykład 1 z parameterem n_sal = 5000, a następnie z parametrem
n_sal = 3000 – ewentualne znalezienie większej ilości wierszy obsłużyć komunikatem –
„Znaleziono więcej niż jeden wiersz”
2. Wykonać przykład 2 z parameterem n_deptno = 10, a następnie z parametrem
n_deptno = 50 – ewentualne nieznalezienie wierszy obsłużyć komunikatem –
„Nie znaleziono wiersza”

PL/SQL(3)

M. Rakowski - WSISiZ

6

Kursory niejawne –

wykorzystanie cd.

Polecenia DML

-- przykład 3
DECLARE
n_procent_podwyzki NUMBER := 10;
BEGIN
UPDATE NEW_EMP
SET sal = sal + n_procent_podwyzki/100 * sal;
DBMS_OUTPUT.PUT_LINE( 'Zmodyfikowano wierszy '

|| SQL%ROWCOUNT );

END;

Zadanie
1. Stworzyć tabelę NEW_EMP na podstawie EMP
2. Wykonać kod z przykładu 3
3. Zobaczyć zmiany
4. Wycofać zmiany
5. Zmodyfikować kod tak aby podnieść tylko osobom na stanowiskach ‘CLERK’
6. Zobaczyć zmiany
7. Usunąć tabelę NEW_EMP

PL/SQL(3)

M. Rakowski - WSISiZ

7

Kursory jawne

Służą do specyficznego przetwarzania większej ilości wierszy.
Deklaracja kursorów jawnych odbywa się w sekcji

DECLARE

Kursory mogą być otwierane instrukcją:

OPEN kursor[(parametry)];

Przechwytywanie kolejnych wierszy może odbywać się

instrukcją:

FETCH kursor INTO zmienna[, zmienna..]*

Kursory mogą być zamykane instrukcją:

CLOSE kursor;

W celu sprawdzania, czy przechwycenie kolenego wiersza

powidło się należy używać atrybutu kursora:

Kursor%FOUND lub Kursor%NOTFOUND

PL/SQL(3)

M. Rakowski - WSISiZ

8

Kursory jawne,

wykorzystanie

Postać kursorowa pętli FOR

-- przykład 4
DECLARE
CURSOR c_dept IS
SELECT *
FROM DEPT;
r_dept DEPT%ROWTYPE;
BEGIN
FOR r_dept IN c_dept
LOOP
DBMS_OUTPUT.PUT_LINE(

r_dept.deptno
|| ': '
|| r_dept.loc
|| ' - '
|| r_dept.dname );

END LOOP;
END;

-- przykład 5
BEGIN
FOR r_dept IN (

SELECT *

FROM DEPT

)

LOOP
DBMS_OUTPUT.PUT_LINE(

r_dept.deptno
|| ': '
|| r_dept.loc

|| ' - '
|| r_dept.dname );

END LOOP;
END;

Zadanie:
1. Za pomocą pętli FOR wyświetlić

nazwiska i zarobki wszystkich

pracowników i na koniec wyświetlić

„Najlepiej zarabia nazwisko bo kwotę”

PL/SQL(3)

M. Rakowski - WSISiZ

9

Jawna obsługa kursora

Zadanie
1.

Wykonać kod z przykładu 6.

2.

Zmienić inicjację n_sal na 6000.

3.

Czy obsługa nieznalezienia wiersza jest lepsza niż w SELECT?

4.

Wyświetlić nazwiska i pensje pracowników bez użycia pętli FOR.

-- przykład 6
DECLARE
v_ename EMP.ename%TYPE;
n_sal EMP.SAL%TYPE := 5000;
CURSOR c IS
SELECT ename
FROM EMP
WHERE sal = n_sal;
BEGIN
OPEN c;
FETCH c INTO v_ename;
CLOSE c;
DBMS_OUTPUT.PUT_LINE( v_ename );
END;

PL/SQL(3)

M. Rakowski - WSISiZ

10

Parametry kursorów

jawnych

Kursory, w celu ich wielokrotnego

użycia w bloku PL/SQL, są
parametryzowalne:

Format parametru kursora w

deklaracji:

CURSOR kursor[( zmienna TYP [DEFAULT wartość domyślna]

[, zmienna TYP [DEFAULT wartość domyślna] ]*

)] IS

SELECT ..

PL/SQL(3)

M. Rakowski - WSISiZ

11

Parametry kursorów

jawnych

-- przykład 7
DECLARE
v EMP.job%TYPE := 'CLERK';
CURSOR c( v_job EMP.job%TYPE DEFAULT v ) IS
SELECT MAX( sal)
FROM EMP
WHERE job = v_job;
n EMP.sal%TYPE;
v_tekst varchar2(100) := 'największe zarobki na stanowisku ' ;
BEGIN
OPEN c;
FETCH c INTO n;
CLOSE c;
DBMS_OUTPUT.PUT_LINE( v_tekst || v|| ' to ‘ || n );
v := 'MANAGER';
OPEN c( v ) ;
FETCH c INTO n;
CLOSE c;
DBMS_OUTPUT.PUT_LINE( v_tekst || v|| ' to ‘ || n

);

END;