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 - 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 );
Zadanie: END;
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;
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
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
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
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;
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 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;
