Laboratorium 7: klucze zewnętrzne, warunki spójności

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:

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:

drop table nazwa cascade constraints;

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

wyrażenia regularne
utl_http -- pakiet obsługujący połączenia HTTP,
PL/SQL Web Toolkit Reference, krótki opis htp -- pakiety obsługujące generowanie stron HTML,

Ć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;
/