PL/SQL

1
PL/SQL

• Zajecia nr III

2
Instrukcje SQL w PL/SQL

• Instrukcje jezyka SQL sa w PL/SQL sa analogiczne
  do oferowanych przez RDBMS Instrukcje dziela sie
  na zwiazane z kursorami (SELECT DML) oraz
  sterowaniem transakcjami (COMMIT, ROLLBACK, SET
  TRANSACTION, LOCK TABLE, SAVEPOINT)
• Kursory dziela sie na jawne (deklarowane przez
  uzytkownika) i niejawne bedace wywolaniami
  polecen SQL (w prawie identycznej postaci)

3
Kursor

• Wykonaniu instrukcji SQL w RDBMS towarzyszy
  zaalokowanie prywatnego obszaru roboczego, który
  jest buforem wykonania instrukcji i stanowi
  udostepnienie danych pobieranych lub informacji o
  realizacji instrukcji.
• Kursor, deklarowany w PL/SQL, jest zmienna, która
  mozna kojarzyc ze zdaniem SQL instrukcja PL/SQL.

4
Atrybuty kursora

• Przetwarzaniu wierszy przez kursor (jawny lub
  nie) przez towarzysza atrybuty, którym nadawane
  sa odpowiednie wartosci
• kursorFOUND - zawiera wartosc BOOLEAN czy
  zaczytano kolejny wiersz
• kursorNOTFOUND - zawiera wartosc BOOLEAN czy
  nie przechwycono wiersza
• kursorISOPEN - zawiera wartosc BOOLEAN czy
  kursor jest otwarty
• kursorROWCOUNT zawiera wartosc NUMBER -
  liczba sprowadzonych wierszy
• W przypadku kursora niejawnego nazwa kursora jest
  SQL

5
Kursory niejawne - wykorzystanie

• Wybór pojedynczych danych (SELECT z klauzula
  INTO)
• Przyklady

-- przyklad 2 DECLARE r_dept DEPTROWTYPE
n_deptno DEPT.deptnoTYPE 10 BEGIN SELECT
INTO r_dept FROM DEPT WHERE deptno
n_deptno DBMS_OUTPUT.PUT_LINE( r_dept.loc
' - ' r_dept.dname ) END
-- przyklad 1 DECLARE v_ename EMP.enameTYPE
n_sal EMP.SALTYPE 5000 BEGIN SELECT ename
INTO v_ename FROM EMP WHERE sal n_sal
DBMS_OUTPUT.PUT_LINE( v_ename ) END

• Zadanie
• Wykonac przyklad 1 z parameterem n_sal 5000, a
  nastepnie z parametrem
• n_sal 3000 ewentualne znalezienie wiekszej
  ilosci wierszy obsluzyc komunikatem
• Znaleziono wiecej niz jeden wiersz
• 2. Wykonac przyklad 2 z parameterem n_deptno
  10, a nastepnie z parametrem
• n_deptno 50 ewentualne nieznalezienie wierszy
  obsluzyc komunikatem
• Nie znaleziono wiersza

6
Kursory niejawne wykorzystanie cd.

• Polecenia DML

-- przyklad 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 '
SQLROWCOUNT ) END

• Zadanie
• Stworzyc tabele NEW_EMP na podstawie EMP
• Wykonac kod z przykladu 3
• Zobaczyc zmiany
• Wycofac zmiany
• Zmodyfikowac kod tak aby podniesc tylko osobom na
  stanowiskach CLERK
• Zobaczyc zmiany
• Usunac tabele NEW_EMP

7
Kursory jawne

• Sluza do specyficznego przetwarzania wiekszej
  ilosci wierszy.
• Deklaracja kursorów jawnych odbywa sie w sekcji
  DECLARE
• Kursory moga byc otwierane instrukcja
• OPEN kursor(parametry)
• Przechwytywanie kolejnych wierszy moze odbywac
  sie instrukcja
• FETCH kursor INTO zmienna, zmienna..
• Kursory moga byc zamykane instrukcja
• CLOSE kursor
• W celu sprawdzania, czy przechwycenie kolenego
  wiersza powidlo sie nalezy uzywac atrybutu
  kursora
• KursorFOUND lub KursorNOTFOUND

8
Kursory jawne, wykorzystanie

• Postac kursorowa petli FOR

-- przyklad 4 DECLARE CURSOR c_dept IS
SELECT FROM DEPT r_dept
DEPTROWTYPE BEGIN FOR r_dept IN c_dept
LOOP DBMS_OUTPUT.PUT_LINE(
r_dept.deptno ' ' r_dept.loc
' - ' r_dept.dname ) END LOOP END
-- przyklad 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 pomoca petli FOR wyswietlic
nazwiska i zarobki wszystkich pracowników i na
koniec wyswietlic Najlepiej zarabia nazwisko bo
kwote
9
Jawna obsluga kursora
-- przyklad 6 DECLARE v_ename EMP.enameTYPE
n_sal EMP.SALTYPE 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

• Zadanie
• Wykonac kod z przykladu 6.
• Zmienic inicjacje n_sal na 6000.
• Czy obsluga nieznalezienia wiersza jest lepsza
  niz w SELECT?
• Wyswietlic nazwiska i pensje pracowników bez
  uzycia petli FOR.

10
Parametry kursorów jawnych

• Kursory, w celu ich wielokrotnego uzycia w bloku
  PL/SQL, sa parametryzowalne
• Format parametru kursora w deklaracji
• CURSOR kursor( zmienna TYP DEFAULT wartosc
  domyslna
• , zmienna TYP DEFAULT wartosc domyslna
  
• ) IS
• SELECT ..

11
Parametry kursorów jawnych
-- przyklad 7 DECLARE v EMP.jobTYPE
'CLERK' CURSOR c( v_job EMP.jobTYPE DEFAULT v
) IS SELECT MAX( sal) FROM EMP WHERE
job v_job n EMP.salTYPE v_tekst
varchar2(100) 'najwieksze 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