PL/SQL

PL/SQL(3) M. Rakowski - WSISiZ 1

PL/SQL

Zajęcia nr III


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)


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.


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”


Kursory niejawne - wykorzystanieWybór pojedynczych danych (SELECT z klauzulą INTO)Przykłady:-- przykład 1DECLARE v_ename EMP.ename%TYPE; n_sal EMP.SAL%TYPE := 5000;BEGIN SELECT ename INTO v_ename

FROM EMPWHERE sal = n_sal;

DBMS_OUTPUT.PUT_LINE( v_ename );END;

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

FROM DEPTWHERE 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”


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;

Zadanie1. Stworzyć tabelę NEW_EMP na podstawie EMP2. Wykonać kod z przykładu 33. Zobaczyć zmiany4. Wycofać zmiany5. Zmodyfikować kod tak aby podnieść tylko osobom na stanowiskach ‘CLERK’6. Zobaczyć zmiany7. Usunąć tabelę NEW_EMP


Kursory jawne

Służą do specyficznego przetwarzania większej ilości wierszy.

Deklaracja kursorów jawnych odbywa się w sekcji DECLAREKursory 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


Kursory jawne, wykorzystanie

Postać kursorowa pętli FOR-- przykład 4DECLARE 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 5BEGIN 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ę”


Jawna obsługa kursora

Zadanie1. 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 6DECLARE 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;


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 ..


Parametry kursorów jawnych-- przykład 7DECLARE 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;

PL/SQL

Documents

Transcript of PL/SQL