Malé povídání o databázích v Oracle

Aneb jak co nejrychlejc začít s Oraclem.

Proč tohle

Prostě asi protože jsem sám hledal nějaký příklad a nedařilo se mi nějaký najít, tak jsem se rozhodl, že dám svůj výtvor v plen světu :-) A jednak i sám pro sebe, jelikož co si nemapíšu,to jako bych něměl :-) Můžete si celou tu nádheru stáhnout jako zatatovaný balík, ale pak si přečtěte to co je na spodních řádkách téhle stránky.

Základní pojmy

Tak nějak už čekám, že ten kdo tohle bude číst tuší co to je tabulka, Integritní omezení a spol...

Foreign key (Cizí klíč)

Klíč v tabulce, na který se odkazuje položka z jiné tabulky. Porušení cizího klíče je v Oracle Databaze error 02291. Cizí klíč lze definovat také tak, aby se po porušení odstranily záznamy, které na daný klíč odkazovali (ON DELETE CASCADE), nebo aby se nastavily na NULL (ON DELETE SET NULL).

Unikátnost

Požadavek, aby každý řádek v daném sloupci byl unikátní. Porušení unikátnosti je v Oracle Databaze error 00001.

Trigger

Procedura která se volá před nebo po provedení nějaké akce na tabulce. Akce jsou Update, Delete,Insert. Používají se k hlídání toho, na co nestačí integritní omezení.

Sequence

Oběkt který vrací při každém dalším volání následující číslo. Spolu s triggery se dají použí na "nahrazení" typu "auto_increment".

Package

Balík procedur a funkcí v PL-SQL, většinou vztahující se k nějaké tabulce. Pokud by nekoha zajímalo víc, tak pěkné povídání o procedurách v plsql je tady Na stránce není copyright, takže jsem si ji dovolil ozrcadlit sem.

Statistika

Pomocný výpočet databázového stroje, který na základě statistiky posuzuje, jak si ukládat data.

Vyjimka

No to asi programator ví, co to je vyjimka, ale co to je v Oraclu? No pokud provede aplikace něco, co by provádět neměla. Mohou to být:

A možná ještě něco.Pokud si vyvoláváme vlastní vyjimky,tak můžou být v rozmezí -20000 a -20999. Podstatné je, že vyjimku si můžeme odchytit a ošetřit v EXCEPTION části FUNKCE /PROCEDURY. Vyjimky jsou buď už pojmenované "od přírody", nebo si je můžeme pojmenovat, tj. číslu přidělit nějaký lidsky přijatelný identifikátor, a to pomoci PRAGMA EXCEPTION_INIT, tj, např. 

PRAGMA EXCEPTION_INIT (EXC_kolidujici_rezervace,	-20042);
a pak si jí už pod tímhle názvem odchytit. Pokud by nekoha zajímalo víc, tak pěkné povídání o vyjímkách je tady Na stránce není copyright, takže jsem si ji dovolil ozrcadlit sem.

Příklad malé databáze

Za tohle fakt neručím prostě to berte tak,že dávám k nahlédnutí svoje zápisky.To schema je tu jen tak pro nahladnuti , s korektnim er to asi nema moc spolecneho.

Pseudo-ER schema

Jde o takovou jakoby malou databázi vybavení sportovního klubu. Na databázi si klademe následující požadavky:

Moje řešení

Tohle je moje řešení

Struktura, Omezeni, Indexy

Struktura bude tvořena sadou tabulek, tabulky budou mít jako primarní klíč integer, ikdyž by se třeba nabízel jiný kandidátní klíč, kvůli urychlení vzhledávání a indexování. Omezení můžeme definovat buď hned v době tvorby tabulek, nepo pomocí "ALTER TABLE xxx ADD CONSTRAINT ...", po vztvoření celého schématu. První y způsob je přehlednější, druhý má tu výhodu, že tabulky jsou již vztvořeny, a tak nezáleží na pořadí, ve kterém jsou jednotlivé příkazy volány (nejde se odvol8vat na ciz9 kl94 tabulky, kter8 neexistuje). Cizí klíče by měli uržovat integritu celé databáze. Indexy jsou nutné pokud to má celé nějak fungovat. Je víc než vhodné nadefinovat indexy pres cizí klíče, jinak databáze stráce spoustu času při každém "DELETE",zvláště Oracle se svoji specialitou tedy zamykáním referencovaných tabulek. To by Vás uživatelé hnali. 


--Zapoctova prace na predmet DATABAZOVE APLIKACE, ODLADENO PRO ORACLE 9
-------------------------------------------------------------------------------

---------------------------------------
--TABULKA CLEN (jsou zde ulozeni clenove oddilu)
---------------------------------------
CREATE TABLE clen (
id NUMBER NOT NULL,
login VARCHAR2(10) NOT NULL, -- je kandidatnim klicem - vytvari se dale unikatni 
email VARCHAR2(100) NOT NULL,
jmeno VARCHAR2(40) NOT NULL,
prijmeni VARCHAR2(40) NOT NULL,
--
CONSTRAINT PK_clen_id PRIMARY KEY (id),
CONSTRAINT UN_clen_login UNIQUE (login)
);

---------------------------------------
--TABULKA TYP (jsou zde ulozeny typy vybaveni, kterymi oddil disponuje)
---------------------------------------

-- typ vybaveni, muze jit bud o macky, cepin, helmu, hulky.
-- odkazuje se na ni tabulka vybavani.
CREATE TABLE typ (
id NUMBER NOT NULL, --primarni klic
nazev VARCHAR2(20) NOT NULL, -- macka, cepin... kandidatni klic pro snazzsi praci uzivatele
poznamka VARCHAR2(256), --neco jako "nevhodne pro tezzsi tury"
--
CONSTRAINT PK_typ_id PRIMARY KEY (id),
CONSTRAINT UN_typ_nazev UNIQUE  (nazev)
);



---------------------------------------
--TABULKA VYBAVENI (jsou zde ulozeny jednotlive kusy vybaveni)
---------------------------------------
--tabulka s oddilovym vybavenim
CREATE TABLE vybaveni (
id NUMBER NOT NULL, --primarni klic
id_typ NUMBER NOT NULL, -- id_typu
id_clena NUMBER NOT NULL, --id clena stanoveneho jako spravce
pocet_vydani NUMBER NOT NULL,--sledovani aby se nacini optrebovavalo stejnomerne 
poznamka VARCHAR2(256),
--
CONSTRAINT PK_vybaveni_id PRIMARY KEY (id),
CONSTRAINT FK_vybaveni_id_typ FOREIGN KEY (id_typ) REFERENCES typ (id),
CONSTRAINT FK_vybaveni_clen_id FOREIGN KEY (id_clena) REFERENCES clen (id)
);
--indexy pres cizi klice.
create index IDX_vybaveni_typ on vybaveni(id_typ);
create index IDX_vybaveni_spravce on vybaveni(id_clena);



---------------------------------------
--TABULKA VYPUJCKY (jsou zde informace o momentalne vypujcenem vybaveni)
---------------------------------------
--primarni klic je id_vybaveni
CREATE TABLE vypujcky (
id_clena NUMBER NOT NULL, --id clena, ktery si vypujcil
id_vybaveni NUMBER NOT NULL, -- uniktani, tedy bude primarnim klicem
datum_vypujceni DATE NOT NULL, -- kdy pujceno
datum_navraceni DATE NOT NULL, --dokdy vratit
--
CONSTRAINT PK_vypujcky_id_vyb PRIMARY KEY (id_vybaveni),
CONSTRAINT FK_vypujcky_id_vybaveni FOREIGN KEY (id_vybaveni) REFERENCES vybaveni (id),
CONSTRAINT FK_vypujcky_clen_id FOREIGN KEY (id_clena) REFERENCES clen (id)
);
--indexy pres cizi klice
--create index IDX_vypujcky_id_vybav on vypujcky(id_vybaveni); 
  --uz je primarni klic, takze si ho oracle inxuej sam.
create index IDX_vypujcky_id_clena on vypujcky(id_clena);
--index, pres ktery hledaji casto poheldy
create index IDX_vypujcky_vratit_do on vypujcky(datum_navraceni);
---------------------------------------
--TABULKA REZERVACE (jsou zde ulozeny informace o rezervacich vybaveni na urcitou dobu urcitymi cleny)
---------------------------------------
-- rezervace, klic je kombinace id_clena, id_vybaveni
CREATE TABLE rezervace (
id_clena NUMBER NOT NULL, 
id_vybaveni NUMBER NOT NULL, -- zabiraji se konkretni macky ne nejake, 
                             -- kazdy clen muze mit svoje oblibene, velikost...
datum DATE NOT NULL, -- poznamka o tom, kdy doslo k rezervaci
od_datum DATE NOT NULL, -- rezervace na dobu od
do_datum DATE NOT NULL, --rezervace na dobu do
--
CONSTRAINT PK_rezervace_id_cl_id_vyb PRIMARY KEY (id_vybaveni,id_clena),
CONSTRAINT FK_rezervace_id_vybaveni FOREIGN KEY (id_vybaveni) REFERENCES vybaveni (id)
	ON DELETE CASCADE,
CONSTRAINT FK_rezervace_id_clena FOREIGN KEY (id_clena) REFERENCES clen (id)
  	ON DELETE CASCADE
);
--indexy pres cizi klice
create index IDX_rezervace_id_clena on rezervace(id_clena);
create index IDX_rezervace_id_vybaveni on rezervace(id_vybaveni);

---------------------------------------
--TABULKA VYPUJCKY_LOG (jsou zde informace o minulosti vypujcek a rezervaci)
---------------------------------------
--primarni klic je id
CREATE TABLE vypujcky_log (
id NUMBER NOT  NULL, --primarni klic
datum DATE NOT NULL, --datum udalosti
id_clena NUMBER NOT NULL,
id_vybaveni NUMBER NOT NULL,
status VARCHAR2(1) NOT NULL, -- P pujceno, V vraceno R rezervovano
poznamka VARCHAR2(64), -- poznamka, napr vratil se zlomenym hrotem, vratil pozde, pokuta 20kc, a pod.
CONSTRAINT PK_vypujcky_log_id PRIMARY KEY (id),
CONSTRAINT FK_vypujcky_log_id_vybaveni FOREIGN KEY (id_vybaveni) REFERENCES vybaveni (id)
 	ON DELETE CASCADE,
CONSTRAINT FK_vypujcky_log_id_clena FOREIGN KEY (id_clena) REFERENCES clen (id)
  	ON DELETE CASCADE
);
--indexy pres cizi klice
create index IDX_vypujcky_log_id_vyb on vypujcky_log (id_vybaveni);
create index IDX_vypujcky_log_id_clena on vypujcky_log (id_clena);
--tohle se casto hleda v pohladech.
create bitmap index IDX_vypujcky_log_status on vypujcky_log (status);

Triggery

Potom je potřeba dodělat triggery a sekvence na to, na co integritní omezení nestačila. 

  --tabulka vybaveni
CREATE  SEQUENCE SEQ_vybaveni_id 
; 

CREATE OR REPLACE TRIGGER TRG_bef_ins_row_vybaveni_id
  BEFORE INSERT
  ON vybaveni
  FOR EACH ROW
  BEGIN
    SELECT SEQ_vybaveni_id.NEXTVAL
    INTO :NEW.id
    FROM DUAL;
  END;
/

--tabulka clen
CREATE  SEQUENCE SEQ_clen_id;

CREATE OR REPLACE TRIGGER TRG_bef_ins_row_clen_id
  BEFORE INSERT
  ON clen
  FOR EACH ROW
  BEGIN
    SELECT SEQ_clen_id.NEXTVAL
    INTO :NEW.id
    FROM DUAL;
  END;
/


--tabulka vypujcky_log
CREATE SEQUENCE SEQ_vypujcky_log_id;

CREATE OR REPLACE TRIGGER TRG_bef_ins_row_vyp_lg_id
  BEFORE INSERT
  ON vypujcky_log
  FOR EACH ROW
  BEGIN
    SELECT SEQ_vypujcky_log_id.NEXTVAL
    INTO :NEW.id
    FROM DUAL;
  END;
/

--typ
CREATE SEQUENCE SEQ_typ_id;

CREATE OR REPLACE TRIGGER TRG_bef_ins_row_typ_id
  BEFORE INSERT
  ON typ
  FOR EACH ROW
  BEGIN
    SELECT SEQ_typ_id.NEXTVAL
    INTO :NEW.id
    FROM DUAL;
  END;
/

-----------------------------------------------------------------------------
--trigery zajistujici integritu dat
-----------------------------------------------------------------------------

-- triggery zajistujici integritu dat tabulky vybaveni
--na insert, zajisti aby pocet_vydani byl roven 0
CREATE OR REPLACE TRIGGER TRG_bef_ins_row_vybaveni
  BEFORE INSERT
  ON vybaveni FOR EACH ROW
  BEGIN
    select 0 into :new.pocet_vydani from dual;
  END;
/




CREATE OR REPLACE TRIGGER TRG_bef_upd_row_vybaveni
  BEFORE UPDATE OR DELETE
  ON vybaveni FOR EACH ROW
  declare
    xpocet_rezervaci number;
    xpocet_vypujcek  number;
  BEGIN
  IF updating then
   IF (:new.pocet_vydani < :old.pocet_vydani) THEN
    RAISE_APPLICATION_ERROR(-20070,'Nejde snizit optotrebeni materialu!!');
   END IF;
  END IF; --updating
-- tohle jsou dost zavazne dva radky, pokud tu tohle nebude, tak se to diky constraints proste smaze a konec.
--  select count(*) into xpocet_rezervaci from rezervace where id_vybaveni=:old.id;
--  select count(*) into xpocet_vypujcek  from vypujcky  where id_vybaveni=:old.id;
--  IF xpocet_rezervaci<>0 or xpocet_vypujcek<>0 then
--    RAISE_APPLICATION_ERROR (-20071,'vybaveni nejde menit, pokud je prave vypujceno nebo rezervovano');
-- END IF;
  END
  ;
/


--kontrola sprvanych vlozeni udaju a pripadna oprava
CREATE OR REPLACE TRIGGER TRG_bef_ins_upd_row_clen
  BEFORE INSERT OR UPDATE 
  ON clen FOR EACH ROW
  declare
    kontrolni_login varchar2(10);
  BEGIN
    kontrolni_login := trim(:new.login);
    IF lower(kontrolni_login)<> kontrolni_login THEN
      RAISE_APPLICATION_ERROR (-20050,'Login se musi skladat pouze z malych pismen');
    end if;
    --pozmenit vsem prvni pismeno na velke, aby se pak nejakej zakaznik neurazil :-) 
    :new.jmeno := trim(initcap(:new.jmeno));
    :new.prijmeni := trim(initcap(:new.prijmeni));

    :new.email := trim(lower(:new.email)) ;
    --assert!!! kontrola emailu??? to bylo pracny, zavinac, kontrolovat,ze je neco pred nim, za nim alespon jedna tecka... to bude lepsi udelat nejakym reqexpem v nejakym ovladacim prvku k databazi

  END;
/

---- triggery zajistujici integritu dat tabulky rezervace
--kontrola, jestli si nekdo nedava rezervacku na neco, co neni vypujcene

create or replace TRIGGER TRG_bef_ins_row_rezervace
  BEFORE INSERT
  ON rezervace
  FOR EACH ROW
  declare
    kdo_to_ma_pujcene clen.id%type;
    pocet_neslucitelnych_rezervaci number;
    xpocet_clen number;
    xpocet_vybaveni number;
  BEGIN
   IF :new.do_datum < sysdate or :new.od_datum < sysdate then
    RAISE_APPLICATION_ERROR(-20043, 'Nejde rezervovat na dobu v minulosti');
   END IF;
   IF (:new.do_datum - :new.od_datum) > 30 or (:new.do_datum - :new.od_datum) < 1 then
    RAISE_APPLICATION_ERROR(-20041, 'Doba na kterou jde vybaveni rezervovat musi byt mezi 1 a 30 dny');
   END IF; 
   -- osetrit, aby si dva lidi nemohli rezervovat 
   SELECT COUNT(*) into pocet_neslucitelnych_rezervaci from rezervace where 
  ((
    (od_datum <= :new.do_datum and do_datum >= :new.do_datum) or
    (od_datum <= :new.do_datum and do_datum >= :new.do_datum)
   ) and id_vybaveni = :new.id_vybaveni 
  );
   IF pocet_neslucitelnych_rezervaci<> 0 then 
    RAISE_APPLICATION_ERROR(-20042, 'Tato rezervaci koliduje s jiz zadanymi rezervacemi');
   END IF;
  END;
/

-- hlida, ze vypujceni neni rezervovane, ze si nikdo neprodluzuje zapujcni dobu na vice nez sto dni
 CREATE OR REPLACE TRIGGER TRG_bef_ins_upd_row_vypujcky
  BEFORE INSERT OR UPDATE 
  ON vypujcky FOR EACH ROW
  declare
    xpocet  number;
    xdatum  rezervace.datum%type;
    xid_clen vypujcky.id_clena%type;
  BEGIN
    begin
      --jsou nejake rezervace?? 
      --pokud jsou nejake rezervace ...
      --tak kdy si to muzeme pujcit??
       SELECT COUNT(*) into xpocet from rezervace where 
        (
          (
            (od_datum >= :new.datum_vypujceni and do_datum  <= :new.datum_navraceni) or
            (od_datum >= :new.datum_vypujceni and  do_datum <= :new.datum_navraceni)
          )
         and id_vybaveni = :new.id_vybaveni
        );        
       if xpocet<> 0 then
       --DBMS_OUTPUT.PUT_LINE ('Nalezena nejaka rezervace na dane vybaveni v danou dobu...');
        if inserting then -- nekdo si neco pujcuje
          begin
            -- kdy to je mozne??
            -- ...a kdyz na dane vybaveni mam nejcasnejsi rezervaci
            select datum into xdatum from rezervace where id_vybaveni = 
              :new.id_vybaveni and id_clena = :new.id_clena and
              datum = ( select min(datum) from rezervace where id_vybaveni = :new.id_vybaveni); 
            --a jeste to z vybaveni smazat  
            delete from rezervace where id_vybaveni = :new.id_vybaveni and id_clena = :new.id_clena;
            DBMS_OUTPUT.PUT_LINE ('Existuje rezervace clena, ktery pozaduje vypujceni...');
            exception
            when no_data_found then
              raise_application_error(-20065, 'Dane vybaveni jiz bylo rezervovano jinym clenem');
          end;
        elsif updating then -- nekdo si neco prodluzuje
          raise_application_error(-20066, 'Vybaveni je rezervovane, vypujcni doba nelze prodlouzit.');
        end if;
      else -- zbozi neni rezervovano
     -- DBMS_OUTPUT.PUT_LINE ('Vybaveni neni rezervovano pro danou dubu...');
          if updating then
           if :old.datum_vypujceni + 100 < :new.datum_navraceni then
             raise_application_error(-20067, 'Celkova doba vypujcky nesmi presahnout 100 dni. ');     
           end if; 
           if :old.datum_navraceni < sysdate then
             raise_application_error(-20068, 'Vypujcni doba prekrocena. V takovem pripade neni mozno obnovit vypujcku.');     
           end if;
          end if; 
      end if; --tohle je ale skarede a neprehledne :-)
    end;


  END;
/

create or replace TRIGGER TRG_aft_del_row_vypujcky  
  after delete
  ON vypujcky
  FOR EACH ROW
  declare
    xstav_vraceni vypujcky_log.poznamka%type := 'Vraceno v casovem limitu';  
  BEGIN
    if :old.datum_navraceni < sysdate then
      xstav_vraceni := 'Vraceno se zpozdenim';
      DBMS_OUTPUT.PUT_LINE ('POZOR VYBAVENI S ID: '||:old.id_vybaveni ||' VRACENO SE ZPOZDENIM. MELO BYT VRACENO: ' || :old.datum_navraceni);
    end if;
    insert into vypujcky_log (datum,id_clena,id_vybaveni,status, poznamka) 
    values (sysdate, :old.id_clena, :old.id_vybaveni,'V', xstav_vraceni);
    -- V je vraceno
  END;
/



create or replace TRIGGER TRG_aft_ins_row_rezervace  
  after INSERT
  ON rezervace
  FOR EACH ROW
  BEGIN
    insert into vypujcky_log (datum,id_clena,id_vybaveni,status,poznamka) 
    values (:new.datum, :new.id_clena, :new.id_vybaveni, 
    'R','Rezervovano od/do :'||:new.od_datum ||'/'||:new.do_datum );
  END;
/



create or replace TRIGGER TRG_aft_ins_row_vypujcky  
  after INSERT
  ON vypujcky
  FOR EACH ROW
  BEGIN
    insert into vypujcky_log (datum,id_clena,id_vybaveni,status,poznamka) 
    values (:new.datum_vypujceni, :new.id_clena, :new.id_vybaveni, 
    'P', 'Vypujceno od/do :'||:new.datum_vypujceni ||'/'||:new.datum_navraceni);
  --zvysit miru opotrebovani vybaveni.
  update vybaveni set pocet_vydani = pocet_vydani + 1 where id = :new.id_vybaveni;
  END;
/


CREATE OR REPLACE TRIGGER TRG_bef_del_upd_row_typ
  BEFORE UPDATE OR DELETE 
  ON typ FOR EACH ROW
  declare
    xpocet_vypujcek  number;
    xpocet_rezervaci  number;
  BEGIN
   select count(*) into xpocet_vypujcek from vypujcky where id_vybaveni in 
    (select id from vybaveni where id_typ = :old.id);
      select count(*) into xpocet_rezervaci from rezervace where id_vybaveni in 
    (select id from vybaveni where id_typ = :old.id);
   IF (xpocet_vypujcek<> 0) or (xpocet_rezervaci<> 0) then
    raise_application_error(-20090, 'Typ nejde menit ani mazat pokud je rezervovan nebo vypujcen');
   END IF;
  END;
/

Packages

Pro aplikaci je ideální napsat si balíky procedur a funkcí, protože v nich můžeme korektně reagovat na chyby vygenerované v triggerech a jinak generovaných databází. Krom toho usnadňují život uživatelům a o to tady přeci jde :-) 

  CREATE OR REPLACE PACKAGE dbp_typ AS
  PROCEDURE pridej(
		xnazev    	typ.nazev%type,
		xpoznamka	typ.poznamka%type
  );


  procedure odeber(xid typ.id%type);
  procedure odeber_podle_nazvu(xnazev typ.nazev%type);

  procedure zmen(
		xid		typ.id%type default null,
		xnazev    	typ.nazev%type default null,
		xpoznamka	typ.poznamka%type default null
	);

  function id_podle_nazvu(xnazev typ.nazev%type) return typ.id%type;
  END;
/


CREATE OR REPLACE PACKAGE BODY dbp_typ AS

 EXC_prave_prop_or_rezer EXCEPTION;
 EXC_prilis_dlouha_hodnota EXCEPTION;
 EXC_cizi_klic EXCEPTION;

 --provazani s chybami vyvolavanymi triggery
 PRAGMA EXCEPTION_INIT (EXC_prave_prop_or_rezer, -20060);
 --provazani se standardnimi chybami
 PRAGMA EXCEPTION_INIT (EXC_prilis_dlouha_hodnota, -01401);
 PRAGMA EXCEPTION_INIT (EXC_cizi_klic, -02291);

 PROCEDURE pridej(
		xnazev    	typ.nazev%type,
		xpoznamka	typ.poznamka%type
  )
    AS
    BEGIN
      insert into typ (nazev , poznamka) values (xnazev ,xpoznamka);
      DBMS_OUTPUT.PUT_LINE ('Typ vybaveni'''||xnazev||''' byl pridan.');
      EXCEPTION
      	when EXC_prilis_dlouha_hodnota then
          RAISE_APPLICATION_ERROR (-20101,'Vkladana hodnota je pro sloupec prilis dlouha.');
    END;


 procedure odeber(xid typ.id%type)
 as
 begin
 --kontrola,ze tento typ neni vypujcen nebo rezervovan provadi triggery
  delete from typ where id = xid;
 exception
  when EXC_cizi_klic then
  RAISE_APPLICATION_ERROR (-20111,'Existuje vybaveni daneho typu,typ nejde odstranit');
 end;

 procedure odeber_podle_nazvu(xnazev typ.nazev%type)
 as
 xid typ.id%type;
 begin
  xid := dbp_typ.id_podle_nazvu(xnazev);
  dbp_typ.odeber(xid);
 end; 

 procedure zmen(
--kontrola,ze tento typ neni vypujcen nebo rezervovan provadi triggery
		xid		typ.id%type default null,
		xnazev    	typ.nazev%type default null,
		xpoznamka	typ.poznamka%type default null
	)as
 
 begin  
   update typ set
  --pokud se nenajde, pouzit defaultni hodnoty
      nazev = 	 decode(xnazev,null,nazev,'null',null, xnazev),
      poznamka = decode(xpoznamka,null,poznamka,'null',null, xpoznamka)
    where id=xid;
 end;

 function id_podle_nazvu(xnazev typ.nazev%type) return typ.id%type as
     xid typ.id%type;
 begin
  select id into xid from typ where nazev = xnazev;
  return xid;
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR (-20110,'Typ nazvu '''||xnazev||''' neexistuje.');
  return null;
 end;

 END; --dbp_typ
/





CREATE OR REPLACE PACKAGE dbp_clen AS
  PROCEDURE pridej(
		xlogin    clen.login%type,
		xemail    clen.email%type,
		xjmeno    clen.jmeno%type,
		xprijmeni clen.prijmeni%type
  );

  procedure odeber(login clen.login%type);
  procedure odeber_id(xid clen.id%type);

  procedure zmen(
   	        xlogin clen.login%type, -- ten podle ktereho se vyhledava, menit nejde
		xemail clen.email%type default null, 
		xjmeno clen.jmeno%type default null,
		xprijmeni clen.prijmeni%type default null
	);
  procedure zmen_id(
                xid clen.id%type, -- ten podle ktereho se vyhledava, menit nejde
		xlogin clen.login%type default null,
		xemail clen.email%type default null, 
		xjmeno clen.jmeno%type default null,
		xprijmeni clen.prijmeni%type default null
	);

  function id_podle_loginu(xlogin clen.login%type) return clen.id%type;
  END;
/
CREATE OR REPLACE PACKAGE BODY dbp_clen AS
EXC_porusena_unikatnost   EXCEPTION;
EXC_prilis_dlouha_hodnota EXCEPTION;
EXC_neexistujici_zaznam   EXCEPTION;
EXC_existuji_vypuj_or_rez EXCEPTION;



PRAGMA EXCEPTION_INIT (EXC_porusena_unikatnost,   -00001);
PRAGMA EXCEPTION_INIT (EXC_prilis_dlouha_hodnota, -01401);
--pragma exception_init (EXC_existuji_vypuj_or_rez, -20071);
pragma exception_init (EXC_existuji_vypuj_or_rez, -02292);

-- pridani noveho clen
  PROCEDURE pridej(
		xlogin clen.login%type,
		xemail clen.email%type,
		xjmeno clen.jmeno%type,
		xprijmeni clen.prijmeni%type
    )
    AS
    BEGIN
      insert into clen (login,email,jmeno,prijmeni) values (xlogin,xemail,xjmeno,xprijmeni);
      DBMS_OUTPUT.PUT_LINE ('Uzivatel'''||xlogin||''' byl pridan.');
      EXCEPTION
        when EXC_porusena_unikatnost then -- unikatni klic je pouze na loginu
          RAISE_APPLICATION_ERROR (-20121,'Clen s loginem '''||xlogin||''' jiz existuje');
      	when EXC_prilis_dlouha_hodnota then
          RAISE_APPLICATION_ERROR (-20101,'Vkladana hodnota je pro sloupec prilis dlouha.');
    END;

-- odebrani clen, podle klice id
 procedure odeber_id(xid clen.id%type) as
    begin
      delete from clen where id = xid;    
      exception   
        when EXC_neexistujici_zaznam then -- odpropagovano funkci uzivatel_id(login)
          raise_application_error(-20122,'Clen  s takovym id neexistuje, nejde smazat');
        when  EXC_existuji_vypuj_or_rez then  
          raise_application_error(-20123, 'Nelze vymazat clena, ktery ma neco pujceno/rezervovano/je spravce!');
    end;


-- pokud zname login
  procedure zmen(
    		xlogin clen.login%type, -- ten podle ktereho se vyhledava, menit nejde
		xemail clen.email%type default null,
		xjmeno clen.jmeno%type default null,
		xprijmeni clen.prijmeni%type default null
	) as
	    xid clen.id%type;
	  begin

      update clen set 
	
        email = 	decode(xemail,null,email,'null',null, xemail),
        jmeno = 	decode(xjmeno,null, jmeno, 'null', null, xjmeno),
        prijmeni = 	decode(xprijmeni, null, prijmeni, 'null',null, xprijmeni)
        where login = xlogin;
      exception
        when EXC_neexistujici_zaznam then -- odpropagovano funkci uzivatel_id(login)
          raise_application_error(-20124,'Uzivatel s loginem '''||xlogin||''' neexistuje.');
        when EXC_prilis_dlouha_hodnota then
          RAISE_APPLICATION_ERROR (-20125,'Vkladana hodnota je pro sloupec prilis dlouha.');
	  end;


-- pokud zname id. je tak mozno i menit login!!!
  procedure zmen_id(
                xid clen.id%type, -- ten podle ktereho se vyhledava, menit nejde
		xlogin clen.login%type default null,
		xemail clen.email%type default null, 
		xjmeno clen.jmeno%type default null,
		xprijmeni clen.prijmeni%type default null
	) as
    begin
      update clen set 
        email = 	decode(xemail,null,email,'null',null, xemail),
        jmeno = 	decode(xjmeno,null, jmeno, 'null', null, xjmeno),
        prijmeni = 	decode(xprijmeni, null, prijmeni, 'null',null, xprijmeni),
	login = 	decode (xlogin, null, login,'null',null,xlogin)
        where id = xid;
      exception
        when EXC_neexistujici_zaznam then 
          raise_application_error(-20122,'Uzivatel s danym id neexistuje.');
        when EXC_prilis_dlouha_hodnota then
          RAISE_APPLICATION_ERROR (-20101,'Vkladana hodnota je pro sloupec prilis dlouha.');
    end;
    
-- dotaz na id clena podle daneho loginu
  function id_podle_loginu(xlogin clen.login%type) return clen.id%type as
    xid clen.id%type;
    begin
      select id into xid from clen where login=xlogin;
      RETURN xid;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
-- toto je chyba, predpokladam pouzivani funkce original_id pri pridavani do tabulky vybaveni
          RAISE_APPLICATION_ERROR (-20124,'Uzivatel '''||xlogin||''' neexistuje.');
          return null;
    END;

 --odebirani podle loginu clena
  procedure odeber(login clen.login%type) as
    xid clen.id%type;
    begin
      xid := dbp_clen.id_podle_loginu(login);
      dbp_clen.odeber_id(xid);
    end;
  END;
/  




CREATE OR REPLACE PACKAGE dbp_vybaveni AS
  PROCEDURE pridej(
    	xid_typ vybaveni.id_typ%type,
  	xid_clena vybaveni.id_clena%type,
	xpoznamka vybaveni.poznamka%type default null
  );
  procedure vloz_vybaveni_hromadne (
	kolik number,
	login_spravce clen.login%type, 
	ceho typ.nazev%type
  );
  procedure odeber(xid vybaveni.id%type);
--pocet pouziti jde menit akorat na vyssi cislo
  procedure zmen(
	xid vybaveni.id%type,
        xid_typ vybaveni.id_typ%type default null,
  	xid_clena vybaveni.id_clena%type default null,
  	xpocet_vydani vybaveni.pocet_vydani%type default null
	);
 -- vybere nejmene opotrebovane vybaveni
function nejvhodnejsi_podle_opotrebeni(
	xtyp_nazev typ.nazev%type,
	xod_datum DATE,
	xdo_datum DATE
  )
  return vybaveni.id%type;
  procedure nejvhodnejsi(
	xtyp_nazev typ.nazev%type,
	xod_datum DATE,
	xdo_datum DATE
 );


  --vybere nejmene opotrebovane vybaveni, ktere spravuje dany spravce
  return vybaveni.id%type;
  
  END;
/





 
create OR REPLACE PACKAGE BODY dbp_vybaveni AS
EXC_prilis_dlouha_hodnota      		EXCEPTION;
EXC_zaznam_vybaveni_neexistuje     	exception;
EXC_nulova_hodnota             		exception;
EXC_existuji_vyp_rez          		exception;  
  

PRAGMA EXCEPTION_INIT (EXC_prilis_dlouha_hodnota, 	-01401);
--PRAGMA EXCEPTION_INIT (EXC_zaznam_vybaveni_neexistuje   -20022);
PRAGMA EXCEPTION_INIT (EXC_nulova_hodnota, 		-01407);
--pragma exception_init (e_existuji_vypujcky, 		-02292);
PRAGMA EXCEPTION_INIT (EXC_existuji_vyp_rez, 		-20071);

-- interni, neni soucasti baliku... 
  procedure id_exists(xid vybaveni.id%type) 
  as
    xxid vybaveni.id%type;
  begin
    select id into xxid from vybaveni where id = xid; -- kontrola, zda tam id je, pokud ne, vyhodi se chyba, ktera se propaguje vys...
    exception
      when NO_DATA_FOUND then
	--propagovat chybu...
        raise_application_error(-20130,'Vybaveni stakovym id neexistuje',TRUE);--propagovat dal  
  end;


-- prida novy kus vybaveni
  PROCEDURE pridej(
    	xid_typ vybaveni.id_typ%type,
  	xid_clena vybaveni.id_clena%type,
        xpoznamka vybaveni.poznamka%type default null
  ) as
  begin
    insert into vybaveni (
		id_typ,
		id_clena,
		poznamka
		) 
           values (
		xid_typ,
		xid_clena,
		xpoznamka
		);	
  end;

PROCEDURE vloz_vybaveni_hromadne (
		kolik number,
		login_spravce clen.login%type, 
		ceho typ.nazev%type) AS
    i NUMBER := 1;
    xid_typ vybaveni.id_typ%type;
    xid_spravce clen.id%type;
BEGIN
 select typ.id into xid_typ from typ where nazev = ceho;
 select clen.id into xid_spravce from clen where login = login_spravce;
 if kolik < 1  then
  raise_application_error(-20195,'Pocet vkladaneho vybaveni je prilis maly');
 end if;
 LOOP
        INSERT INTO VYBAVENI (id_typ,id_clena,pocet_vydani) VALUES(xid_typ,xid_spravce,0);
        i := i+1;
        EXIT WHEN i = kolik + 1;
 END LOOP;
END;

--meni vsecko ve vybaveni, vcetne mnozstvi vypujcek, na vyssi, na nizssi dat nejde (trigger)...
  procedure zmen(
	xid vybaveni.id%type,
        xid_typ vybaveni.id_typ%type default null,
  	xid_clena vybaveni.id_clena%type default null,
  	xpocet_vydani vybaveni.pocet_vydani%type default null
	)
  as
  begin
    dbp_vybaveni.id_exists(xid);
    --pokud neexistuje, tak hnedka tady vyhodime vyjimku a zbytek se neprovede
    update vybaveni set
      id_clena = decode(xid_clena,null,id_clena,'null',null, xid_clena), 
      id_typ =   decode(xid_typ,null,id_typ,'null',null, xid_typ),
      pocet_vydani = decode(xpocet_vydani,null,pocet_vydani,'null',null, xpocet_vydani)
    where id=xid;
   end;


  
  procedure odeber(xid vybaveni.id%type) as
    tid vybaveni.id%type;
  begin
    select id into tid from vybaveni where id = xid; -- kontrola, zda tam id je
    delete from vybaveni where id = xid;
    -- tohle jde jen pokud nejsou nejake rezervace a nebo vypujcky.
    -- inak by se rezervace smazali samy zrusenim ciziho klice
    DBMS_OUTPUT.PUT_LINE ('vybaveni s id: '''||xid||''' bylo odebrano.');  
    exception 
      when NO_DATA_FOUND then
        raise_application_error(-20130,'vybaveni s id '''||xid||''' neexistuje.');
      when  EXC_existuji_vyp_rez then
        raise_application_error(-20131, 'Nelze vymazat vybaveni, nekdo jej mam pujceny. Mel by jej nejdriv vratit.');
  end;

  --vybere nejmene opotrebovany kus vybavni daneho typu
 function nejvhodnejsi_podle_opotrebeni(
	xtyp_nazev typ.nazev%type,
	xod_datum DATE,
	xdo_datum DATE
  ) 
  return vybaveni.id%type
        as
  retval vybaveni.id%type;
  xid_typ typ.id%type;
  begin
  xid_typ := dbp_typ.id_podle_nazvu(xtyp_nazev);
  DBMS_OUTPUT.PUT_LINE('ID TYPU VYBAVENI:' ||xid_typ);
  
  select  id into retval from vybaveni where id_typ = xid_typ 
	and pocet_vydani = (select min(pocet_vydani) from vybaveni where 
				id_typ = xid_typ and 
				not exists (select id_vybaveni from vypujcky where id_vybaveni = id) and
				not exists (select id_vybaveni from rezervace 
					    where ((od_datum <= xod_datum and do_datum >= xod_datum) or
		      			     	  (od_datum <= xdo_datum and do_datum >= xdo_datum)
						  ) and id_vybaveni = id
		   			   )
			   )
 	and id not in 	(select id_vybaveni from rezervace 
					    where ((od_datum <= xod_datum and do_datum >= xod_datum) or
		      			     	  (od_datum <= xdo_datum and do_datum >= xdo_datum)
						  )
		   			   ) 
        and ROWNUM < 2; --limitace na 1 navraceny radek
  DBMS_OUTPUT.PUT_LINE('Vybrane vybaveni:' ||retval);
  return retval;
  exception 
   when NO_DATA_FOUND then
        raise_application_error(-20151,'Neni vhodne vybaveni vybaveni');
   
  end;

  procedure nejvhodnejsi(
	xtyp_nazev typ.nazev%type,
	xod_datum DATE,
	xdo_datum DATE
 ) as
 begin
  DBMS_OUTPUT.PUT_LINE('ID VYBRANEHO VYBAVENI:' ||dbp_vybaveni.nejvhodnejsi_podle_opotrebeni(xtyp_nazev, xod_datum, xdo_datum));

 end;
  --vybere nejmene opotrebovane vybaveni, ktere spravuje dany spravce
  END;
/  

---- balik pro praci s rezervacemi    
create or replace package dbp_rezervace as
-- rezervuj vybaveni xid_vybaveni pro clen
 procedure rezervuj(xid_vybaveni vybaveni.id%type, 
            xlogin clen.login%type, 
	    xod_datum rezervace.od_datum%type,
	    xdo_datum rezervace.do_datum%type
	    );

-- zrusi rezervaci na vybaveni pro clen xlogin  
-- je-li zadano pro nektery parametr null, jsou vymazany vsechny rezervace pro druhy parametr
  procedure zrus_rezervaci(xid_vybaveni vybaveni.id%type default null, 
           xlogin clen.login%type default null);
  
  end;
/


create or replace package body dbp_rezervace as
  
  EXC_kolidujici_rezervace exception;
  EXC_vybaveni_neexistuje  exception;
  EXC_uzivatel_neexistuje  EXCEPTION;
  EXC_neplatne_datum       EXCEPTION;

  PRAGMA EXCEPTION_INIT (EXC_kolidujici_rezervace,	-20042);
  PRAGMA EXCEPTION_INIT (EXC_uzivatel_neexistuje,       -20124);
  PRAGMA EXCEPTION_INIT (EXC_neplatne_datum,            -20043);
  PRAGMA EXCEPTION_INIT (EXC_vybaveni_neexistuje,       -02291);


  procedure rezervuj(xid_vybaveni vybaveni.id%type, 
            xlogin clen.login%type, 
	    xod_datum rezervace.od_datum%type,
	    xdo_datum rezervace.do_datum%type
	    ) 
  as
    xid_clena clen.id%type;
    xtemp vybaveni.id%type;
  begin
     xid_clena := dbp_clen.id_podle_loginu(xlogin);
    select id into xtemp from vybaveni where id = xid_vybaveni;
    insert into rezervace (id_vybaveni, id_clena, od_datum, do_datum, datum) 
	values (xid_vybaveni,xid_clena, xod_datum, xdo_datum, sysdate);
    exception
      when no_data_found then
        raise_application_error(-20141,'Vybaveni s id '''||xid_vybaveni||''' neexistuje.');  
      when EXC_uzivatel_neexistuje then
        raise_application_error(-20142,'Uzivatel s loginem '''||xlogin||''' neexistuje.');  
      when EXC_kolidujici_rezervace then
        raise_application_error(-20143,'Vybaveni je jiz rezervovano na tuto dobu.');
      when EXC_neplatne_datum then
        raise_application_error(-20144,'Vybaveni nelze rezervovat na dobu v minulosti');
  end;

-- pro parametr, ktery je nezadany, se vymazou veskere vyskyty - pouziti napr. pro zruseni vsech rezervaci nejakeho clen  
  procedure zrus_rezervaci(xid_vybaveni vybaveni.id%type default null, 
           xlogin clen.login%type default null)
  as
    xdatum rezervace.datum%type;
    xid_clen clen.id%type;
  begin
    if xid_vybaveni is null and xlogin is null then --jsou-li oba null, zrusim vse
      delete from rezervace;
    elsif xid_vybaveni is null then
      begin
        xid_clen := dbp_clen.id_podle_loginu(xlogin);
        delete from rezervace where id_clena = xid_clen;
      end;
    elsif xlogin is null then
       begin
        delete from rezervace where id_vybaveni = xid_vybaveni;
      end;
    else
      begin
        xid_clen := dbp_clen.id_podle_loginu(xlogin);
        select datum into xdatum from rezervace -- pro zjisteni, zda tato dvojce v rezervacich existuje
          where id_vybaveni = xid_vybaveni and id_clena = xid_clen;
        delete from rezervace where id_vybaveni = xid_vybaveni and id_clena = xid_clen;
      end;
    end if;
      exception
        when no_data_found then
          raise_application_error(-20040,'Zadna rezervace nebyla smazana ( login: '''||xlogin||''', id'''||xid_vybaveni||''').');  
  end;
             
end;
/  



create or replace package dbp_vypujcky as
-- vypujci vybaveni xid_vybaveni pro clena defaultne na dva tydny
  procedure vypujcit(xid_vybaveni vybaveni.id%type,
		     xdo_datum vypujcky.datum_navraceni%type default null,
              	     xlogin clen.login%type);

-- vrati vypujcku
-- je-li zadano pro vybaveni null, vrati vsechny vypujcky zadaneho clen
  procedure vratit(xid_vybaveni vybaveni.id%type default null, 
               xlogin clen.login%type);

  procedure prodlouzit(xid_vybaveni vybaveni.id%type, 
	       xdo_datum vypujcky.datum_navraceni%type default null, 
               xlogin clen.login%type);
  end;
/


-- telo baliku pro praci s vypujckami
create or replace package body dbp_vypujcky as
  EXC_vypujceno			exception;
  EXC_rezervovano		exception;
  EXC_vybaveni_neexistuje 	exception;
  EXC_uzivatel_neexistuje 	EXCEPTION;
  
  -- vyjimka osetrujici absenci ciziho klice - znamena nepritomnost vybaveni, nepritomnost loginu se detekuje v prevodu loginu na id

  PRAGMA EXCEPTION_INIT (EXC_rezervovano,         -20065);
  PRAGMA EXCEPTION_INIT (EXC_vypujceno,           -00001); --porusi se unikatni klic
  PRAGMA EXCEPTION_INIT (EXC_uzivatel_neexistuje, -20124);

-- rezervuje vybaveni s id xid_vybaveni pro clen s loginem xlogin  
 procedure vypujcit(xid_vybaveni vybaveni.id%type,
		     xdo_datum vypujcky.datum_navraceni%type default null,
              	     xlogin clen.login%type) 
  as
    xid_clen clen.id%type;
    xpocet number;
  begin
      xid_clen := dbp_clen.id_podle_loginu(xlogin);
      select count(*) into xpocet from vypujcky where id_clena = xid_clen;
      if xpocet >= 5 then
        raise_application_error(-20150, 'Kazdy muze mit pujcene max 5 veci najednou.');
      end if;
    -- datum vypujcky a doba vraceni se nastavuje automaticky v triggeru
      -- kontrola max. tri pujcenych vybaveniu daneho clen
    insert into vypujcky (id_vybaveni, id_clena, datum_vypujceni, datum_navraceni) values (
      xid_vybaveni,
      xid_clen,
      sysdate,
      -- defaultne se pujcuje na 14 dni
      decode(xdo_datum,'null',sysdate + 14,null,sysdate + 14,xdo_datum)); 
    exception
      when EXC_vybaveni_neexistuje then
        raise_application_error(-20151,'vybaveni s id '''||xid_vybaveni||''' neexistuje.');  
      when EXC_uzivatel_neexistuje then
        raise_application_error(-20152,'Clen s loginem '''||xlogin||''' neexistuje.');  
      when EXC_vypujceno then 
        raise_application_error(-20153,'vybaveni s id '''||xid_vybaveni||''' je an danou dobu vypujceno.');  
      when EXC_rezervovano then 
        raise_application_error(-20154,'vybaveni s id '''||xid_vybaveni||''' je na danou dobu rezervovano.');  
  end;

-- jde s nim zrusit napr vsechny rezervace
  procedure vratit(xid_vybaveni vybaveni.id%type default null,
           xlogin clen.login%type )
  as
    xdatum vypujcky.datum_vypujceni%type;
    xid_clen clen.id%type;
  begin
    if xid_vybaveni is null then
      begin
        xid_clen := dbp_clen.id_podle_loginu(xlogin);
        delete from vypujcky where id_clena = xid_clen;
      end;
    else
      begin
        xid_clen := dbp_clen.id_podle_loginu(xlogin);
        select datum_vypujceni into xdatum from vypujcky -- jen se koukenem jestli je
          where id_vybaveni = xid_vybaveni and id_clena = xid_clen;
        delete from vypujcky where id_vybaveni = xid_vybaveni and id_clena = xid_clen;
      end;
    end if;
      exception
        when no_data_found then
          raise_application_error(-20160,'Neexistuje vypujcka ( login: '''||xlogin||''', id'''||xid_vybaveni||''').');  
  end;

-- prodlouzeni vypujcky
  procedure prodlouzit(xid_vybaveni vybaveni.id%type, 
	       xdo_datum vypujcky.datum_navraceni%type default null, 
               xlogin clen.login%type)
  as
    xid vypujcky.id_vybaveni%type;
    xid_clen vypujcky.id_clena%type ;
  -- prodlouzeni - nelze prodlouzit vypujcku, je-li na ni rezervace, jinak se prodluzuje max. na dvojnasobek vypujcni doby, tedy      
  begin
    xid_clen := dbp_clen.id_podle_loginu(xlogin);
    select id_vybaveni into xid from vypujcky where id_vybaveni = xid_vybaveni and id_clena = xid_clen;
      update vypujcky set datum_navraceni = xdo_datum where id_vybaveni = xid_vybaveni and id_clena = xid_clen;     
    exception
     when no_data_found then
         raise_application_error(-20160,'Neexistuje vypujcka ( login: '''||xlogin||''', id'''||xid_vybaveni||''').');   
               when EXC_uzivatel_neexistuje then
        raise_application_error(-20142,'Uzivatel s loginem '''||xlogin||''' neexistuje.');  
  end;
end;
/

Statistiky

Oracle nám umožňuje spočítat si statistikz kolik, čeho a kam bylo umístěno, vzhledáváno a podle toho si sám uspořádat všecko tak jak potřebuje, aby se mu pro naši konkrítní tabulku s našimi konkrétními nároky co nejlépe počítalo. 

  analyze TABLE typ compute statistics;
analyze TABLE vybaveni compute statistics;
analyze TABLE clen compute statistics;
analyze TABLE vypujcky compute statistics;
analyze TABLE rezervace compute statistics;
analyze TABLE vypujcky_log compute statistics;

Data

Pokud si někdo budete chtít zkusit tetovací soubor, tak tady jsou data. Emaily jsou trochu namršený, strkat je jinak na web bych si netroufl. 

  -------------------------------------------------------------------------------
-- clenove
insert into clen (jmeno, prijmeni, login, email) values ('Tomas', 'Pop', 'tom','tomas.kop@selanam.cz');
insert into clen (jmeno, prijmeni, login, email) values ('Marcel', 'Tlustos', 'macino','tlusto@erra.cz');
insert into clen (jmeno, prijmeni, login, email) values ('Marketa', 'Florianova', 'makula','makula@sfcentrum.cz');
insert into clen (jmeno, prijmeni, login, email) values ('Jan', 'Marecek', 'pinda','honzasd@studentt.cvut.cz');
insert into clen (jmeno, prijmeni, login, email) values ('Alena', 'Sedlackova', 'aja','alasedla@pimail.cz');
insert into clen (jmeno, prijmeni, login, email) values ('Dusan', 'Micica', 'dusan','moncicakd@sezlnam.cz');
-- typy vybaveni
insert into typ (nazev, poznamka) values ('Macka_kovana_88', 'Kovana macka, nehhodna na lezeni v ledech');
insert into typ (nazev, poznamka) values ('Macka_ramova_94', 'Ramova macka, pouzitelne pouze na skelet!!');
insert into typ (nazev, poznamka) values ('Cepin_stubaj_71', 'Historicke cepiny, turistaky');
insert into typ (nazev, poznamka) values ('Cepin_do_ledu_96','Cepiny pro narocnejsi lezeni');
insert into typ (nazev, poznamka) values ('Helma_kasida_88', 'Tezka a stara, lepsi mit vlastni...');
insert into typ (nazev, poznamka) values ('Teleskopky_99',   'Teleskopky cobry, odpruzene');

--vlozime desatero od kazdeho typu vybaveni
exec dbp_vybaveni.vloz_vybaveni_hromadne(10,'macino','Macka_ramova_94');
exec dbp_vybaveni.vloz_vybaveni_hromadne(10,'tom','Macka_kovana_88');
exec dbp_vybaveni.vloz_vybaveni_hromadne(10,'tom','Cepin_stubaj_71');
exec dbp_vybaveni.vloz_vybaveni_hromadne(10,'tom','Teleskopky_99');
exec dbp_vybaveni.vloz_vybaveni_hromadne(10,'macino','Helma_kasida_88');

insert into vypujcky_log(datum, id_clena, id_vybaveni, status, poznamka) values ('01-JAN-06', 1,2,'P','');
insert into vypujcky_log(datum, id_clena, id_vybaveni, status, poznamka) values ('11-JAN-06', 1,2,'V','');
insert into vypujcky_log(datum, id_clena, id_vybaveni, status, poznamka) values ('15-JAN-06', 2,5,'R','');
insert into vypujcky_log(datum, id_clena, id_vybaveni, status, poznamka) values ('18-JAN-06', 2,5,'P','');
insert into vypujcky_log(datum, id_clena, id_vybaveni, status, poznamka) values ('27-JAN-06', 2,5,'V','');
-- aktualni vypujcka
insert into vypujcky (id_clena, id_vybaveni, datum_vypujceni, datum_navraceni) values(1, 16, sysdate -7, sysdate +7 );
-- vypujcka, ktera nabyla vracena v limitu
insert into vypujcky (id_clena, id_vybaveni, datum_vypujceni, datum_navraceni) values(2, 18, sysdate -20, sysdate -5);

Views

Pohledy jsou takovou náhradou za neexistující uživatelské rozhraní. Jsou to vlastně takové nějaké složitějsí dotazi zakuklené tak aby se tvářili jednoduše. 

-------------------------------------------------------------------------------
create or replace view VW_seznam_spravcu_vybaveni as
  select distinct clen.jmeno, clen.prijmeni, clen.email
         from vybaveni inner join clen on vybaveni.id_clena = clen.id
;

create or replace view VW_seznam_clenu as
  select distinct clen.jmeno, clen.prijmeni, clen.email, clen.login
         from clen
;

create or replace view VW_vybaveni_po_limitu as
 select distinct vypujcky.datum_navraceni,typ.nazev, clen.jmeno, clen.prijmeni,clen.email
 from (((vybaveni join typ on vybaveni.id_typ = typ.id) join vypujcky on vybaveni.id = vypujcky.id_vybaveni) 
 join clen on vypujcky.id_clena = clen.id) where vypujcky.datum_navraceni < sysdate
;  

--seznam vybaveni + nazev + login spravce
create or replace view VW_seznam_vybaveni as
 select distinct vybaveni.id, typ.nazev,clen.login from
 (vybaveni join typ on vybaveni.id_typ = typ.id) join clen on vybaveni.id_clena = clen.id
; 

--seznam vybaveni ktere prave neni zapujcene + nazev + login spravce
create or replace view VW_seznam_dostupneho_vybaveni as
 select distinct vybaveni.id, typ.nazev,clen.login as login_spravce,clen.email as email_spravce from
 (vybaveni join typ on vybaveni.id_typ = typ.id) join clen on vybaveni.id_clena = clen.id 
 where not exists (select * from vypujcky where vypujcky.id_vybaveni = vybaveni.id)
;

--seznam deseti kusu nejpouzivanejsiho vybaveni + spravce  
create or replace view VW_seznam_nejpouzivanejsiho as
 select distinct vybaveni.pocet_vydani, vybaveni.id, typ.nazev,clen.login from
 ((vybaveni join typ on vybaveni.id_typ = typ.id) join vypujcky on vypujcky.id_vybaveni = vybaveni.id)
 join clen on vybaveni.id_clena = clen.id 
 where rownum < 11 order by vybaveni.pocet_vydani desc
;

--seznam prave vypujceneho vybaveni  
create or replace view VW_prave_vypujcene as
 select distinct vypujcky.datum_navraceni, vybaveni.id, typ.nazev,clen.login from
 ((vybaveni join typ on vybaveni.id_typ = typ.id) join vypujcky on vypujcky.id_vybaveni = vybaveni.id)
 join clen on vypujcky.id_clena = clen.id 
 order by vypujcky.datum_navraceni desc
;

create or replace view VW_rezervace as
select typ.nazev,vybaveni.id, clen.jmeno, clen.prijmeni from
((typ join vybaveni on typ.id = vybaveni.id_typ) 
 join rezervace on vybaveni.id = rezervace.id_vybaveni)
 join clen on rezervace.id_clena = clen.id
;

create or replace view VW_log as
select clen.jmeno as jmeno, clen.prijmeni as prijmeni, 
clen.login as login_clena, vypujcky_log.status as akce, vypujcky_log.poznamka as poznamka
from vypujcky_log join clen on vypujcky_log.id_clena = clen.id
;

create or replace view VW_kdo_jak_vyuzival as
select count(clen.login) as pocet_akci,
       clen.login as login_clena from
(vypujcky_log join clen on vypujcky_log.id_clena = clen.id) group by clen.login order by pocet_akci desc
;

Testovací skript

Takovou slušností je jako součást řešení dát i testovací skript, který tak nějak ukáže co to umí. 

  --nastaveni rozumnejsiho chovani prostredi
set linesize 200;
set pagesize 80;
set serverout on;

--seznam spravcu vybaveni
exec DBMS_OUTPUT.PUT_LINE('-------SPRAVCI VYBAVENI-----------');
select * from VW_seznam_spravcu_vybaveni;

-- seznam dostupneho vybaveni (toho, ktere aktualne neni vypujcene)
exec DBMS_OUTPUT.PUT_LINE('-------DOSTUPNE VYBAVENI-----------');
select * from VW_seznam_dostupneho_vybaveni;

exec DBMS_OUTPUT.PUT_LINE('-------SEZNAM CLENU-----------');
select * from VW_seznam_clenu;

exec DBMS_OUTPUT.PUT_LINE('-------VYPIS LOGU VYPUJCEK-----------');
select * from VW_log;

exec DBMS_OUTPUT.PUT_LINE('-------VYPIS AKTUALNICH VYPUJCEK-----------');
select * from VW_prave_vypujcene;

exec DBMS_OUTPUT.PUT_LINE('-------VYPIS VYABVENI PO LIMITU-----------');
select * from VW_vybaveni_po_limitu;


--rezervujeme si macky
/* 
pokus o rezervaci v neplatnem datu */
exec dbp_rezervace.rezervuj(25,'aja','12-JAN-06','18-JAN-06');
/* 
tak si ho rezervujem na platnou dobu */
exec dbp_rezervace.rezervuj(25,'aja','12-AUG-06','18-AUG-06');
/*
podivame se, ze rezervace je skutecne v rezervacich */
select * from VW_rezervace;
/*
tak a zkusim si ho rezervovat na jineho clena, coz nepujde */
exec dbp_rezervace.rezervuj(25,'pinda','14-AUG-06','15-AUG-06');
/*
pro uplnost rezervaci zrusim */
exec dbp_rezervace.zrus_rezervaci(25, 'aja');
/*
podivame se, ze rezervace skutecne zmizela */
select * from VW_rezervace;


/*
 pak se podivam po nejvhodnejsich mackach na pristi tyden (tj volnych, nejmenekrat pouzitych */
exec dbp_vybaveni.nejvhodnejsi('Macka_ramova_94', sysdate +7, sysdate + 14);
/*
rezervuju  ho na popristi tyden */
exec dbp_rezervace.rezervuj(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14),'pinda',sysdate +7,sysdate+14);
/*
 ale kdyz to zkusi provest jiny uzivatel, tak mu system vybere jine vybaveni */
exec dbp_rezervace.rezervuj(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14),'aja',sysdate +7,sysdate+14);
/*
podivame se, kdo si tedy co rezervoval */
select * from VW_rezervace;
/*
 a ted si to zkusime pujcit, jako nekdo, kdo to nema rezervovane, tak abychom porusili rezervaci...*/
exec dbp_vypujcky. vypujcit(2, null, 'tom');
/*
 ale ten, kdo to ma rezervovane si to pujcit muze...*/
exec dbp_vypujcky. vypujcit(2, null, 'aja');
/*
podivame se, ze zmizela z rezervaci */
select * from VW_rezervace;
/*
 a ted si to zkusime vypujcit jeste jednou...*/
exec dbp_vypujcky. vypujcit(2, null, 'aja');
/*
 a kouknem se kdo ma co prave pujcene */
select * from VW_prave_vypujcene;
/*
vidime ze aja, tak ji zkusime odstranit z clenu */
exec dbp_clen.odeber('aja');
/*
to nejde, takze vypujcku vratime vsecko co ma aja pujceno */
exec dbp_vypujcky.vratit(null,'aja');
/*
podivame se do logu  kdo jak vyuzival aplikaci*/
select * from VW_kdo_jak_vyuzival;
/*pak smazneme tu aju */
exec dbp_clen.odeber('aja');
/*
podivame se do logu  kdo jak vyuzival aplikaci, tentokrat uz udaje o aje nejsou, nesmime skladovat data o nikom, kdou uz neni clen*/
select * from VW_kdo_jak_vyuzival;
/*
 chudaka aju zas pridame, at o neco neprijde  a to hned dvakret, podruhe bude chyba*/
exec dbp_clen.pridej('aja', 'alased@seznam.cz', 'Alena', 'Sedlackova');
exec dbp_clen.pridej('aja', 'alased@seznam.cz', 'Alena', 'Sedlackova');
/*
 a zkusime si napujcovat hodne vybaveni na jednoho clena */
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'pinda');
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'pinda');
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'pinda');
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'pinda');
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'pinda');
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'pinda');
/*
 tak dopujcujeme zbytek vybaveni na jineho clena, aby uz nebylo zadne vhodne */
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'dusan');
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'dusan');
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'dusan');
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'dusan');
exec dbp_vypujcky. vypujcit(dbp_vybaveni.nejvhodnejsi_podle_opotrebeni('Macka_ramova_94', sysdate +7, sysdate + 14), null, 'dusan');

exec dbp_vypujcky.vratit(null,'pinda');
exec dbp_vypujcky.vratit(null,'dusan');
/*
pokus vratit nekoho, kdo neexistuje */
exec dbp_vypujcky.vratit(null,'dus');

/*
Zkusime vratit marcelovu propadlou vypujcku*/
exec dbp_vypujcky.vratit(null,'macino');
/*
podivame se na aktualni vypujckly */
select * from VW_prave_vypujcene;
/*
a kdo jak vyuzival aplikaci */

select * from VW_kdo_jak_vyuzival;

--tohle je konec testovaciho souboru--

Úklid

No a jelikož asi nikdo na tuhle aplikaci nebude dlouho zvedavej, tohle je skript na úklid. 

--zase vsechno pekne smazat, pospatku v opacnem poradi nez to bylo tvoreno...

--odstraneni statistik
analyze TABLE typ delete statistics;
analyze TABLE vybaveni delete statistics;
analyze TABLE clen delete statistics;
analyze TABLE vypujcky delete statistics;
analyze TABLE rezervace delete statistics;
analyze TABLE vypujcky_log delete statistics;

--odstraneni pohledu
drop view VW_seznam_spravcu_vybaveni;
drop view VW_seznam_clenu;
drop view VW_vybaveni_po_limitu;
drop view VW_seznam_vybaveni;
drop view VW_seznam_dostupneho_vybaveni;
drop view VW_seznam_nejpouzivanejsiho;
drop view VW_prave_vypujcene;
drop view VW_rezervace;
drop view VW_log;
drop view VW_kdo_jak_vyuzival;

--odstraneni balicku
drop package dbp_typ;
drop package dbp_rezervace;
drop package dbp_vybaveni;
drop package dbp_clen;
drop package dbp_vypujcky;

--odstraneni sekvenci
drop SEQUENCE SEQ_vybaveni_id;
drop SEQUENCE SEQ_vypujcky_log_id;
drop SEQUENCE SEQ_clen_id;
drop SEQUENCE SEQ_typ_id;

--odstraneni triggeru 
drop TRIGGER TRG_bef_ins_row_vybaveni_id;
drop TRIGGER TRG_bef_ins_row_clen_id;
drop TRIGGER TRG_bef_ins_row_typ_id;
drop TRIGGER TRG_bef_ins_row_vyp_lg_id;
drop TRIGGER TRG_bef_ins_row_vybaveni;
drop TRIGGER TRG_bef_upd_row_vybaveni;
drop TRIGGER TRG_bef_ins_upd_row_clen;
drop TRIGGER TRG_bef_ins_row_rezervace;
drop TRIGGER TRG_bef_ins_upd_row_vypujcky;
drop TRIGGER TRG_aft_del_row_vypujcky;
drop TRIGGER TRG_aft_ins_row_rezervace;
drop TRIGGER TRG_aft_ins_row_vypujcky;


--odstraneni indexu

drop index IDX_vybaveni_typ;
drop index IDX_vybaveni_spravce;
drop index IDX_vypujcky_id_clena;
drop index IDX_vypujcky_vratit_do;
drop index IDX_rezervace_id_clena;
drop index IDX_rezervace_id_vybaveni;
drop index IDX_vypujcky_log_status;
drop index IDX_vypujcky_log_id_vyb;
drop index IDX_vypujcky_log_id_clena;


--vymazani tabulek a zavislosti
DROP TABLE vypujcky_log CASCADE CONSTRAINTS;
DROP TABLE rezervace CASCADE CONSTRAINTS;
DROP TABLE vypujcky CASCADE CONSTRAINTS;
DROP TABLE clen CASCADE CONSTRAINTS;
DROP TABLE vybaveni CASCADE CONSTRAINTS;
DROP TABLE typ CASCADE CONSTRAINTS;

Závěr

Co dodat, hodně toho tady není, nejsou tu kursory, a spousta dalsího zajímavého co nám Oracle a jiné databáze poskztují, Ale je to docela mzslím pěkný úvod, pro člověka,který o tom už někdy slyšel. Tohle je pro studijní účely, tedy pro čtení a pokusy, nikoli jako možnost co odevzdat. Pokud to někdo budete chtít použít jako svůj zápočťák nebo nějak jinak, tak bych ocenil, kdybyste se přemohli a konzultovali to se mnou. © T.P. 2006