Bazy Danych, laboratorium, 2007.11.16

Plan zajęć:

  • zaawansowane konstrukcje SQL,
  • zaawansowane konstrukcje PL/SQL.

SQL

  • opcje kluczy obcych - domyślnie, baza uniemożliwia usuwanie wierszy na które wskazują klucze obce, możemy jednak wskazać inne zachowanie, tzn. on delete cascade powoduje usuwanie wierszy przy kasowaniu obiektów na które wskazują klucze obce, lub on delete set null powoduje zamienie wartości przez NULL przy kasowaniu obiektów na które wskazują klucze obce:
    <div class="code">k_id varchar(16) references tab1, 
    

    k_id2 varchar(16) references tab2 on delete cascade, k_id3 varchar(16) references tab2 on delete set null,

  • klucze obce składające się w wielu kolumn
    CREATE TABLE a ( id integer, a integer, b integer, CONSTRAINT fk_abc FOREIGN KEY (a,b) REFERENCES inna_tabela (id,kod) );
  • usuwanie tabeli wraz z wszystkimi tabelami zależnymi:
    <div class="code">drop table <i>nazwa</i> cascade constraints;</div>
    
  • zamiana definicji tabeli,
    alter table nazwa_tabeli add (nazwa_kolumny typ); alter table nazwa_tabeli modify (nazwa_kolumny typ); alter table nazwa_tabeli drop (nazwa_kolumny); alter table nazwa_tabeli add (constraint nazwa definicja); alter table nazwa_tabeli enable constraint nazwa; alter table nazwa_tabeli disable constraint nazwa; -- czasowe wyłączenie więzów spójności
  • tworzenie i usuwanie indeksów
    create [ unique ] index nazwa_indeksu on nazwa_tabeli (kol1 [ ASC|DESC ], kol2 ... ); drop index nazwa_indeksu; -- usuwanie
  • pseudokoluman ROWNUM
    select ROWNUM,id FROM tabela; SELECT * FROM tabela WHERE ROWNUM < 10; -- uwaga!! ale nie SELECT * FROM tabela WHERE ROWNUM > 10;
  • synonimy (skrócone nazwy tabeli i perspektyw)
    CREATE SYNONYM nazwa FOR nazwa_tabeli; DROP SYNONYM nazwa;
  • Typy danych: BLOB (binarne duże obiekty), CLOB (tekstowe duże obiekty), LOB (binarne lub testowe). Funkcje EMPTY_BLOB(), EMPTY_CLOB() zwracają puste obiekty.
  • PL/SQL

    Ćwiczenie. Zmień rozmiar pola imię w tabeli czytelnicy z 64 znaków na 32 znaki.
    Rozwiązanie:
    ALTER TABLE czytelnicy MODIFY (imie VARCHAR(32));
    Ćwiczenie. Zmień definicję kluczy obcych w bazie biblioteki, tak by były typu ON DELETE CASCADE. Sprawdź jak teraz działa usuwanie elementów tablicy czytelnicy.
    Rozwiązanie:
    -- SYS_C00412589 trzeba zastąpić odpowiednią wartością
    ALTER TABLE wypozyczenia DROP CONSTRAINT SYS_C00412589; 
    ALTER TABLE wypozyczenia ADD (
       CONSTRAINT fk_pesel FOREIGN KEY (pesel) 
       REFERENCES czytelnicy on delete cascade
    );
    Ćwiczenie. Dodaj indeksy: idx_data_w zawierający pole data_wypozyczenia, oraz idx_data_z zawierający pole data_zwrotu, do tabeli wypożyczenia .
    Rozwiązanie:
    CREATE INDEX idx_data_w ON wypozyczenia (data_wypozyczenia);
    CREATE INDEX idx_data_z ON wypozyczenia (data_zwrotu);
    Ćwiczenie. Wypisz książki których tytuły składają się z co najmniej dwóch wyrazów.
    Rozwiązanie:
    SELECT nr_ew,tytul FROM ksiazki WHERE REGEXP_LIKE(tytul,' ');
    Ćwiczenie. Wczytaj i wypisz zawartość strony stencel.mimuw.edu.pl.
    Rozwiązanie:
    SET serveroutput ON SIZE 100000 
    DECLARE
      req   utl_http.req;
      resp  utl_http.resp;
      value VARCHAR2(8192);
    BEGIN
      req := utl_http.begin_request('http://stencel.mimuw.edu.pl');
      resp := utl_http.get_response(req);
      LOOP
        utl_http.read_line(resp, value, TRUE);
        /* niestety funkcja put_line jest ograniczona do 255 znaków */ 
        loop
          exit when value is null;
          dbms_output.put_line( substr( value, 1, 250 ) );
          value := substr( value, 251 );
        end loop;
      END LOOP;
      utl_http.end_response(resp);
    EXCEPTION
      WHEN utl_http.end_of_body THEN
        utl_http.end_response(resp);
    END;
    /
    Ćwiczenie. Wypisz wszystkie odnośniki ze strony stencel.mimuw.edu.pl
    Rozwiązanie:
    SET serveroutput ON SIZE 100000 
    DECLARE
      req   utl_http.req;
      resp  utl_http.resp;
      value VARCHAR2(8192);
      s     varchar2(200);
      link  varchar2(200);
      title varchar2(200);
    BEGIN
      req := utl_http.begin_request('http://stencel.mimuw.edu.pl');
      resp := utl_http.get_response(req);
      LOOP
        utl_http.read_line(resp, value, TRUE);
        s:=regexp_substr(value,'&lt;a[^&gt;]+href="[^"]+"[^&gt;]*&gt;.*&lt;/a&gt;',1,1,'i');
        if s is not null then
          link:=regexp_replace(s,'^.*href="([^\"]+)".*$','\1',1,1,'i');
          title:=regexp_replace(s,'^.*&gt;(.*)&lt;/a&gt;$','\1',1,1,'i');
          if link is not null then
            dbms_output.put_line('href: '||link||' title: '||title);
          else 
            dbms_output.put_line('dziwny link: '||s);
          end if;
        end if;
      END LOOP;
      utl_http.end_response(resp);
    EXCEPTION
      WHEN utl_http.end_of_body THEN
        utl_http.end_response(resp);
    END;
    /
    Tomasz Waleń
    Tomasz Waleń
    Assistant Professor