Bazy Danych, laboratorium, 2010.11.22

0001-01-01

Plan zajęć:

przypomnienie składni PL/SQL notatki,

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>=10 AND i<=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>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;
/

Bazy Danych, laboratorium, 2010.11.22

Tomasz Waleń

Assistant Professor