Laboratorium 5: SQL PLUS | Informatyka MIMUW

Podstawowa składnia

[
 DECLARE
    deklaracje (np. zmiennych, stałych)
]
BEGIN
  polecenia programu
[
 EXCEPTION
    obsługa błędów
]
END;

Zmienne

Dostępne typy zmiennych:

liczbowe: BINARY_INTEGER, DEC, DECIMAL, FLOAT, INT, INTGER, NATURAL, NUMBER, NUMERIC, PLS_INTEGER, POSITIVE, REAL, SMALLINT,
znakowe: CHAR, CHARACTER, LONG, NCHAR, NVARCHAR2, RAW, STRING, VARCHAR, VARCHAR2,
logiczne: BOOLEAN,
czasowe: DATE, TIMESTAMP, INTERVAL,
czasowe: DATE, TIMESTAMP, INTERVAL,
wskaźnikowe: REF CURSOR, REF object_type

Przykaady deklaracji:

DECLARE
 i NUMBER(10);
 j INT := 10;
 DATA DATE NOT NULL DEFAULT DATE '2007-01-01';
 ok BOOLEAN NOT NULL DEFAULT FALSE;
 s VARCHAR(100) := 'ala ma kota';

Zmienne złożone:

DECLARE
 TYPE t_punkt IS RECORD (
  x INT,
  y INT
 );
 p1 t_punkt;

Dostęp do poszczególnych pól jak w Pascalu/C (czyli np. p1.x).
Atrybuty %TYPE i %ROWTYPE służą do sprawdzania typu określonych pól/wierszy w tabelach.

DECLARE
 zmienna tabela.pole%TYPE;
 wiersz tabela%ROWTYPE;

Stałe

DECLARE
  nazwa CONSTANT typ := wartość;

Interakcja z użytkownikiem

Blok może zawierać zmienne podstawienia (czyli nazwy zmiennych poprzedzone znakiem '&'), przed uruchomieniem bloku użytkownik jest proszony o podanie wartości wszystkich zmiennych podstawieniowych.

s:=&abc;
i:=&identyfikator;

Do wypisywania komunikatów służy funkcja dbms_output.put_line()

dbms_output.put_line('i='||i||' s='||s);

Instrukcje warunkowe

Składnia polecania IF:

IF warunek THEN
  polecenia
(
  ELSIF warunek_x THEN
    polecenia
)*
[
  ELSE
    polecenia
]
END IF;

Składnia polecania CASE:

CASE wyrażenie
  WHEN wartość THEN polecenia1
  WHEN wartość_2 THEN polecenia2
[
  ELSE
    polecenia
]
END [ CASE ];
 
CASE
  WHEN warunek_1 THEN polecenia
  WHEN warunek_2 THEN polecenia2
[
  ELSE
    polecenia
]
END [ CASE ];

Jeśli CASE jest traktowane jako wyra»enie to można kończyć ENDem, jeśli jako
polecenie to trzeba je zakończyć END CASE.

Pętle

Składnia polecenia LOOP:

LOOP
  sekwencja
  [ IF warunek THEN EXIT; END IF; ]
  [ EXIT WHEN warunek ; ]
END LOOP;

Składnia polecania WHILE:

WHILE warunek LOOP
  polecenia
END LOOP;

Składnia polecenia FOR:

FOR licznik IN [ REVERSE ] start..koniec LOOP
  polecenia
END LOOP;

Nie jest konieczne definiowanie zmiennej licznik.

Różności

instrukcja NULL (instrukcja która nic nie robi, wypełniacz pustych bloków),

Zapytania SQL w PL/SQL

SELECT lista pól INTO lista zmiennych FROM ... ;
 
INSERT INTO tabela (lista pól) VALUES (lista warto±ci);
 
INSERT INTO tabela VALUES zmienna_wierszowa;
 
/* pseudokolumna ROW */
 
UPDATE tabela SET row = zmienna_wierszowa;

Kursory

DECLARE
  CURSOR nazwa [ (parametry) ] IS
    { zapytanie_sql | RETURN typ złożony }
    [ FOR UPDATE [ OF lista pól ] ]

Przykłady:

DECLARE
  CURSOR cur_osoby IS SELECT * FROM osoby;
  CURSOR cur_osoby2 RETURN osoby%ROWTYPE;
  CURSOR cur_wypozyczenia (p_pesel wypozyczenia%TYPE) IS
    SELECT * FROM wypozycznia WHERE pesel=p_pesel;

Operacje na kursorach:

OPEN nazwa_kursora [ ( warto±ci parametrów) ];
 
FETCH nazwa_kursora INTO { lista_zmiennych | zmienna wierszowa } ;
 
CLOSE nazwa_kursora;

Atrybuty kursora:

%ISOPEN
%FOUND
%NOTFOUND
%ROWCOUNT

Pętle FOR z użyciem kursorów:

FOR { zmienna rekordowa | zmienna wierszowa } IN nazwa_kursora LOOP
  polecenia
END LOOP;

Nie jest konieczne otwieranie lub zamykanie kursora. Nie jest równie» konieczne
definiowanie zmiennej licznikowej.

Pętle FOR z zapytaniem SQL:

FOR { zmienna rekordowa | zmienna wierszowa } IN ( zapytanie SQL ) LOOP
  polecenia
END LOOP;

Kursor niejawny ma nazwę SQL (ostatnie wykonywane polecenie SELECT INTO,
INSERT, UPDATE, DELETE).

Obsługa wyjątków

DECLARE
  wyj1 EXCEPTION;
  wyj2 EXCEPTION;
BEGIN
  polecenia ...
  RAISE wyjątek;
EXCEPTION
  WHEN wyj1 THEN
    polecenia1
  WHEN wyj2 THEN
    polecenia2
[ WHEN OTHERS THEN
    polecenia..      ]
END;

Predefiniowane wyjątki:

CASE_NOT_FOUND
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
NO_DATA_FOUND
TOO_MANY_ROWS
VALUE_ERROR
ZERO_DIVIDE

Przydatne informacje:

polecenie set serveroutput on aktywuje wypisywanie komunikatów przez funkcję put_line,

Ćwiczenie. Wypisz przy użyciu PL/SQLa bieżącą datę i godzinę

Rozwiązanie

BEGIN 
    dbms_output.put_line('Aktualna data: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')); 
END; 
/

Ćwiczenie. Oblicz 15-tą liczbę Fibonacciego.

Rozwiązanie

DECLARE
 i INT;
 j INT;
 tmp INT;
 n INT;
 BEGIN
 /* fib(0)=fib(1)=1 */
 i:=1;j:=1;
 FOR n IN 1..15 LOOP
    dbms_output.put_line('fib('||n||')='||j);
    tmp:=i+j;
    i:=j;
    j:=tmp;
  END LOOP;
END;
 /

Ćwiczenie. Zmodyfikuj poprzednie rozwiązanie, tak by użytkownik mógł wskazywać która liczbę fibonacciego należy obliczyć.

Rozwiązanie

DECLARE
 i INT;
 j INT;
 tmp INT;
 ii INT;
 n INT;
 BEGIN
 /* fib(0)=fib(1)=1 */
 i:=1;j:=1;n:=&podaj_n;
 FOR ii IN 2..n LOOP
    tmp:=i+j;
    i:=j;
    j:=tmp;
  END LOOP;
 dbms_output.put_line('fib('||n||')='||j);
END;
/

Ćwiczenie. Wypisz reprezentację słowną liczby podanej przez użytkownika (jeśli liczba nie będzie z zakresu 0..99, możesz wypisać komunikat o błędzie).

Rozwiązanie

DECLARE 
 i INT;
 s1 VARCHAR(100) :='';
 s2 VARCHAR(100) :='';
BEGIN
  i:=1;
  s1:=CASE FLOOR(i/10) 
    WHEN 9 THEN 'dziewięćdziesiąt'
    WHEN 8 THEN 'osiemdziesiąt'
    WHEN 7 THEN 'siedemdziesiąt'
    WHEN 6 THEN 'sześćdziesiąt'
    WHEN 5 THEN 'pięćdziesiąt'
    WHEN 4 THEN 'czterdzieści'
    WHEN 3 THEN 'trzydzieści'
    WHEN 2 THEN 'dwadzieścia'
    ELSE ''
 END; 
 
 IF i&gt;=10 AND i&lt;=20 THEN
   s2:=CASE i 
    WHEN 19 THEN 'dziewiętnaście'
    WHEN 18 THEN 'osiemnaście'
    WHEN 17 THEN 'siedemnaście'
    WHEN 16 THEN 'szesnaście'
    WHEN 15 THEN 'piętnaście'
    WHEN 14 THEN 'czternaście'
    WHEN 13 THEN 'trzynaście'
    WHEN 12 THEN 'dwanaście'
    WHEN 11 THEN 'jedenaście'
    WHEN 10 THEN 'dziesięć'
  END; 
 
 ELSIF i=0 THEN
   s2:='zero';
 ELSE
   s2:=CASE i MOD 10 
    WHEN 9 THEN 'dziewięć'
    WHEN 8 THEN 'osiem'
    WHEN 7 THEN 'siedem'
    WHEN 6 THEN 'sześć'
    WHEN 5 THEN 'pięć'
    WHEN 4 THEN 'cztert'
    WHEN 3 THEN 'trzy'
    WHEN 2 THEN 'dwa'
    WHEN 1 THEN 'jeden'
  END; 
 END IF;
 
 dbms_output.put_line(s1||' '||s2);
END;
/

Ćwiczenie. Wypisz liczbę wierszy w tabeli ksiazki.

Rozwiązanie

DECLARE
  ile INT;
BEGIN
  SELECT COUNT(*) INTO ile FROM ksiazki;
  dbms_output.put_line('liczb wierszy w tabeli ksiazki: '||ile);
END;
/

Ćwiczenie. Dla każdej tabeli zdefiniowanej w bazie danych, wpisz jej nazwę i liczbę wierszy (wskazówka: użyj widoku systemowego USER_TABLES lub tabeli ALL_TABLES)

Rozwiązanie

DECLARE 
 t USER_TABLES%ROWTYPE;
 q VARCHAR(200);
 c INT;
BEGIN
FOR t IN (SELECT ut.TABLE_NAME,ut.NUM_ROWS FROM USER_TABLES ut) LOOP
  dbms_output.put_line(t.table_name||' liczba wierszy: '||t.num_rows);
END LOOP;
/* można też wykorzystać dynamiczne zapytania SQL */
FOR t IN (SELECT * FROM USER_TABLES) LOOP
  q:='SELECT COUNT(*) FROM '||t.table_name;
  BEGIN
    EXECUTE IMMEDIATE q INTO c;
    dbms_output.put_line(t.table_name||' liczba wierszy: '||c);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('blad przy sprawdzaniu rozmiaru tabeli '||t.table_name);
  END;
END LOOP;
END;
/

Ćwiczenie. Wypisz dla każdej książki jej najdłuższy czas wypożyczenia.

Rozwiązanie

DECLARE 
 CURSOR cur_k IS SELECT * FROM ksiazki;
 k ksiazki%ROWTYPE;
 c FLOAT;
BEGIN
 OPEN cur_k;
 LOOP
   FETCH cur_k INTO k;
   EXIT WHEN cur_k%NOTFOUND;
   SELECT MAX(NVL(data_zwrotu,sysdate)-data_wypozyczenia) 
     INTO c 
     FROM wypozyczenia 
    WHERE nr_ew=k.nr_ew;
   dbms_output.put_line(k.nr_ew||', '||k.tytul||', max : '||TO_CHAR(c,'9999999D999'));
 END LOOP;
 CLOSE cur_k;
END;
/

Ćwiczenie. Sprawdź czy w bazie istnieje czytelnik o numerze pesel 999 i jeśli brak takiej osoby dodaj wiersz z jej opisem.

Rozwiązanie

DECLARE
 CURSOR cur_c(p_pesel czytelnicy.pesel%TYPE) IS SELECT * FROM czytelnicy WHERE pesel=p_pesel;
 o czytelnicy%ROWTYPE;
BEGIN
 OPEN cur_c('999');
 FETCH cur_c INTO o;
 IF cur_c%FOUND THEN
   dbms_output.put_line('osoba 999 już istnieje');
 ELSE
   dbms_output.put_line('osoba 999 jeszcze nie istnieje');
   o.pesel:='999'; o.imie:='Gall'; o.nazwisko:='Anonim';
   INSERT INTO czytelnicy VALUES o;
   COMMIT;
 END IF;
END;
/

Ćwiczenie. Wyświetl trzy najczęściej wypożyczne książki (jeśli jest wiele książek o tej samej liczbie wypożyczeń, posortuj wyniki wg. tytułu). Wskazówka: uwzględnij atrybut %ROWCOUNT.

Rozwiązanie

DECLARE
 CURSOR cur_k IS 
    SELECT k.nr_ew,k.tytul,COUNT(*) AS liczba_wypozyczen 
     FROM ksiazki k LEFT JOIN wypozyczenia w ON (k.nr_ew=w.nr_ew)
    GROUP BY k.nr_ew,k.tytul
    ORDER BY liczba_wypozyczen DESC,k.tytul ASC;
 r cur_k%ROWTYPE; 
BEGIN
 FOR r IN cur_k LOOP
   EXIT WHEN cur_k%ROWCOUNT&gt;3;
   dbms_output.put_line('wiersz '||cur_k%ROWCOUNT||': '||r.nr_ew||', '||r.tytul||', liczba wypożyczeń: '||r.liczba_wypozyczen);
 END LOOP;
END;
/

Ćwiczenie. Sprawdź czy istnieje książka o numerze ewidecyjnym 15. Użyj obsługi wyjątków.

Rozwiązanie

DECLARE
 r ksiazki%ROWTYPE; 
 nr INT := 15;
BEGIN
 SELECT * INTO r FROM ksiazki WHERE nr_ew=nr;
 dbms_output.put_line('książka o numerze '||nr||' istnieje ('||r.tytul||')');
EXCEPTION
 WHEN NO_DATA_FOUND THEN
   dbms_output.put_line('brak książki o numerze '||nr);
END;
/