-- Skript pro vytvoreni tabulek internetoveho obchodu
------------------------------------------------------
------------------------------------------------------

Drop table Pisen cascade constraints;
Table dropped.

Drop table Zamestnanec cascade constraints;
Table dropped.

Drop table Album cascade constraints;
Table dropped.

Drop table Interpret cascade constraints;
Table dropped.

Drop table Zakaznik cascade constraints;
Table dropped.

Drop table Objednavka cascade constraints;
Table dropped.

Drop table Faktura cascade constraints;
Table dropped.

Drop table CD cascade constraints;
Table dropped.

Drop table na cascade constraints;
Table dropped.

Drop table obsazena cascade constraints;
Table dropped.

Drop table za cascade constraints;
Table dropped.

Drop table Upominka cascade constraints;
Table dropped.

Drop table NovaUpominka cascade constraints;
Table dropped.

Drop view Inter;
View dropped.


Create table Pisen (
Nazev_P VarChar2(20) Not Null,
ID_I Integer Not Null,
Pozn VarChar2(20) Null,
Rok Integer Null,
Constraint PK_Pisen Primary Key (Nazev_P, ID_I)
);
Table created.

Create table Zamestnanec (
ID_Zam Integer Not Null,
Jmeno_Zam VarChar2(20) Not Null,
Constraint PK_Zamestnanec Primary Key (ID_Zam)
);
Table created.

Create table Album (
Nazev_A VarChar2(30) Not Null,
Rok Integer Not Null,
Cena Float Not Null,
Constraint PK_Album Primary Key (Nazev_A)
);
Table created.

Create table Interpret (
ID_I Integer Not Null,
Jmeno_I VarChar2(20) Not Null,
Pozn VarChar2(20) Null,
Constraint PK_Interpret Primary Key (ID_I)
);
Table created.

Create table Zakaznik (
Jmeno_Zak VarChar2(20) Not Null,
RC_Zak Integer Not Null,
Adresa VarChar2(100) Not Null,
Telefon Integer Null,
Constraint PK_Zakaznik Primary Key (RC_Zak)
);
Table created.

Create table Objednavka (
ID_Obj Integer Not Null,
Vyrizena Char(1) Not Null,
Pocet Integer Not Null,
RC_Zak Integer Not Null,
Nazev_A VarChar2(30) Not Null,
Constraint PK_Objednavka Primary Key (ID_Obj)
);
Table created.

Create table Faktura (
Cis_F Integer Not Null,
ID_Zam Integer Not Null,
dat_vystav Date Not Null,
Zaplaceno Char(1) Not Null,
dat_splatnosti Date Not Null,
Constraint PK_Faktura Primary Key (Cis_F)
);
Table created.

Create table CD (
ID_CD Integer Not Null,
Nazev_A VarChar2(30) Not Null,
Constraint PK_CD Primary Key (ID_CD)
);
Table created.

Create table na (
Cis_F Integer Not Null,
ID_CD Integer Not Null,
Constraint UNQ_na_1 Unique (ID_CD)
);
Table created.

Create table obsazena (
Nazev_P VarChar2(20) Not Null,
ID_I Integer Not Null,
Nazev_A VarChar2(30) Not Null,
Constraint UNQ_obsazena_1 Unique (Nazev_P,ID_I,Nazev_A)
);
Table created.

Create table za (
ID_Obj Integer Not Null,
Cis_F Integer Not Null,
Constraint UNQ_za_1 Unique (ID_Obj)
);
Table created.


Create table Upominka(
RC_ZAK Integer Not Null,
Pocet Integer Not Null,
Datum Date Not Null,
Constraint PK_Upominka Primary Key (RC_ZAK)
);
Table created.


Create table NovaUpominka(
RC_ZAK Integer Not Null,
Constraint PK_Nova_Upominka Primary Key (RC_ZAK)
);
Table created.


create view Inter
as select * from Obsazena natural join Interpret;
View created.


Alter table Pisen add (
Constraint FK_Pisen_1 Foreign Key (ID_I) References Interpret(ID_I)
);
Table altered.

Alter table Objednavka add (
Constraint FK_Objednavka_1 Foreign Key (Nazev_A) References Album(Nazev_A),
Constraint FK_Objednavka_2 Foreign Key (RC_Zak) References Zakaznik(RC_Zak)
);
Table altered.

Alter table Faktura add (
Constraint FK_Faktura_1 Foreign Key (ID_Zam) References Zamestnanec(ID_Zam)
);
Table altered.

Alter table CD add (
Constraint FK_CD_1 Foreign Key (Nazev_A) References Album(Nazev_A)
);
Table altered.

Alter table na add (
Constraint FK_na_1 Foreign Key (Cis_F) References Faktura(Cis_F),
Constraint FK_na_2 Foreign Key (ID_CD) References CD(ID_CD)
);
Table altered.

Alter table obsazena add (
Constraint FK_obsazena_1 Foreign Key (Nazev_P, ID_I) References Pisen(Nazev_P, ID_I),
Constraint FK_obsazena_2 Foreign Key (Nazev_A) References Album(Nazev_A)
);
Table altered.

Alter table za add (
Constraint FK_za_1 Foreign Key (ID_Obj) References Objednavka(ID_Obj),
Constraint FK_za_2 Foreign Key (Cis_F) References Faktura(Cis_F)
);
Table altered.

Alter table Upominka add (
Constraint FK_Upominka_1 Foreign Key (RC_ZAK) References Zakaznik(RC_ZAK)
);
Table altered.


Alter table NovaUpominka add (
Constraint FK_Nova_Upominka_1 Foreign Key (RC_ZAK) References Zakaznik(RC_ZAK)
);
Table altered.

--Vytvoreni Package obsahujici funkce, ktere vyuzivaji triggery pro kontrolu vkladanych dat.
--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------

--Vytvoreni Package obchod_pkg
------------------------------
CREATE OR REPLACE PACKAGE obchod_pkg IS
FUNCTION POCET_CD_S_ALBEM_NA_FAKTURE (Cislo_CD in CD.ID_CD%type,Cislo_Faktury IN Faktura.CIS_F%type) RETURN NUMBER;
FUNCTION POCET_ALB_NA_FAKTURE (Jmeno_Alba IN Album.Nazev_A%type,Cislo_Faktury IN Faktura.Cis_F%type) RETURN NUMBER;
FUNCTION LZE_OBJEDNAT (IdCislo IN zakaznik.RC_Zak%type) RETURN BOOLEAN;
FUNCTION JE_NA_SKLADU (JmenoAlba IN Album.Nazev_A%type) RETURN BOOLEAN;
PROCEDURE KONTROLA_UPOMINEK;
END obchod_pkg;
/
Package created.


--Vytvoreni tela Package obchod_pkg
-----------------------------------
CREATE OR REPLACE PACKAGE BODY obchod_pkg IS

--Funkce vracejici pocet CD na fakture, ktere obsahuji stejne album, jako CD zadane jako vstupni parametr.
--------------------------------------------------------------------------------------------------------------------
FUNCTION POCET_CD_S_ALBEM_NA_FAKTURE (Cislo_CD in CD.ID_CD%type,Cislo_Faktury IN Faktura.CIS_F%type) RETURN NUMBER
IS
Jmeno CD.NAZEV_A%type;
Pocet NUMBER;
cursor SeznamCD is select * from na natural join cd where Cis_F=Cislo_faktury;
BEGIN
select Nazev_A into Jmeno from cd where ID_CD=Cislo_CD;
Pocet:=0;
for i in SeznamCd LOOP
if i.Nazev_A=Jmeno then
Pocet:=Pocet+1;
end if;
END LOOP;
RETURN(Pocet);
END;

--Funkce vracejici pocet objednavek daneho Alba na fakture.
-------------------------------------------------------------------------------------------------------------------
FUNCTION POCET_ALB_NA_FAKTURE (Jmeno_Alba IN Album.Nazev_A%type,Cislo_Faktury IN Faktura.Cis_F%type) RETURN NUMBER
IS
CURSOR ObjednanaAlba is select * from za natural join objednavka where (Nazev_A=Jmeno_Alba)and(Cislo_FAktury=Cis_F);
Pocet NUMBER;
BEGIN
Pocet:=0;
for i IN ObjednanaAlba LOOP
Pocet:=Pocet+i.Pocet;
END LOOP;
RETURN(Pocet);
END;

--Funkce vracejici true v pripade, ze zadany zakaznik si muze vytvorit novou objednavku.
----------------------------------------------------------------------------------------
FUNCTION LZE_OBJEDNAT (IdCislo IN zakaznik.RC_Zak%type) RETURN BOOLEAN IS
Cislo NUMBER;
BEGIN
select CIS_F into Cislo from faktura natural join za natural join objednavka
where (RC_Zak=IdCislo)and(zaplaceno='N')and(Dat_splatnosti < Sysdate );
RETURN(false);
EXCEPTION
WHEN NO_DATA_FOUND then RETURN(true);
WHEN TOO_MANY_ROWS then RETURN(false);
END;

--Funkce vracejici true, jestlize zadane Album se nachazi na nekterem CD na sklade.
-----------------------------------------------------------------------------------
FUNCTION JE_NA_SKLADU (JmenoAlba IN Album.Nazev_A%type) RETURN BOOLEAN IS
POmCislo NUMBER;
BEGIN
select ID_CD INTO PomCislo from CD
where Nazev_A=JmenoAlba and not ID_CD in (select ID_CD from CD natural join na where Nazev_A=JmenoAlba);
RETURN(true);
EXCEPTION
when NO_DATA_FOUND then RETURN(false);
when TOO_MANY_ROWS then RETURN(true);
END;

--Procedura pro kontrolu zasilanych upominek.
--Jestlize zakaznik neni v tabulce upominek a ma nezaplacenou fakturu tak ho tam vlozi.
--Jestlize zakaznik v tabulce je a datum zaslani upominky je starsi nez 14 dni aktualizuje udaje v tabulce.
-----------------------------------------------------------------------------------------------------------
PROCEDURE Kontrola_Upominek IS
CURSOR Neplatici is select * from Zakaznik;
rc Zakaznik.RC_ZAK%type;
DatumPom Upominka.DAtum%type;
BEGIN
for I in Neplatici LOOP
BEGIN
if (not obchod_pkg.lze_objednat(i.RC_ZAK)) then
select RC_ZAK,Datum into rc, DatumPom from Upominka where RC_ZAK=I.RC_ZAK;
if (DatumPom<(sysdate-14)) then
UPDATE Upominka SET Pocet=Pocet+1,
Datum=sysdate
where RC_ZAK=I.RC_ZAK;
end if;
end if;
EXCEPTION
when NO_DATA_FOUND then INSERT INTO Upominka(RC_ZAK,Pocet,Datum)
Values(I.RC_ZAK,1,sysdate);
END;
end LOOP;
END;

END obchod_pkg;
/
Package body created.

show errors;
No errors. 

--Vytvoreni triggeru pro kontrolu vkladanych dat.
-------------------------------------------------
-------------------------------------------------

--Trigger provadi kontrolu pri vytvareni nebo uprave tabulky OBJEDNAVKA.
--Kontroluje zda muze byt zakaznikovi vytvorena nova objednavka.
------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER tr_nezaplacena_faktura
BEFORE INSERT OR UPDATE ON Objednavka
FOR EACH ROW
BEGIN
if not obchod_pkg.lze_objednat(:NEW.RC_ZAK) then raise_application_error(-20001,'Zakaznik si nemuze vytvorit novou objednavku');
end if;
END tr_nezaplacena_faktura;
/
Trigger created.


--Trigger provadi kontrolu pri vytvareni nebo uprave tabulky OBJEDNAVKA.
--Kontroluje jestli objednavane Album je na skladu na nekterem CD.
------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER tr_CD_na_skladu
BEFORE INSERT OR UPDATE ON Objednavka
FOR EACH ROW
BEGIN
if not obchod_pkg.je_na_skladu(:NEW.Nazev_A) then raise_application_error(-20002,'CD neni dostupne skladem');
end if;
END TR_CD_na_skladu;
/
Trigger created.


--Trigger provadi kontrolu pri vytvareni nebo uprave tabulky NA.
--Kontroluje jestli CD pridavane na fakturu odpovida pozadavkum nektere objednavky, na ketre byla faktura vystavena.
--Jestli na fakture je jeste potreba CD s danym albem.
--------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER tr_kontrola_faktury
BEFORE INSERT OR UPDATE ON NA
FOR EACH ROW
DECLARE
jmeno Album.Nazev_A%type;
BEGIN
select Nazev_A into jmeno from CD where ID_CD = :new.ID_CD;
if obchod_pkg.pocet_alb_na_fakture(jmeno,:new.Cis_F) <= obchod_pkg.pocet_cd_s_albem_na_fakture(:new.ID_CD,:new.cis_f)
then raise_application_error(-20003,'Objednavky na fakture neobsahuji pozadavek na toto album.');
end if;
END TR_CD_na_skladu;
/
Trigger created.


--Trigger provadi kontrolu pri vytvareni nebo uprave tabulky UPOMINKA.
--Triger pri kazde zmene tabulky upominek vytvori novou tabulku s upominkami, ktere maji byt dnes odeslany.
--Odvozeny udaj z tabulky UPOMINKY
------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER tr_dnesni_upominka
AFTER INSERT OR UPDATE OR DELETE ON Upominka
DECLARE
CURSOR Upominky is select RC_ZAK, Datum from Upominka;
BEGIN
DELETE from NovaUpominka;
for I in Upominky LOOP
if I.Datum=sysdate then insert into NovaUpominka(RC_ZAK)
Values(I.RC_ZAK);
end if;
end LOOP;
END tr_dnesni_upominka;
/
Trigger created.

show errors;
No errors.

--Skript pro naplneni tabulke testovacimi daty
-----------------------------------------------
-----------------------------------------------

alter session set nls_date_format='DD.MM.YYYY';
Session altered.


-- Naplneni tabulky ALBUM
----------------------------------------
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album1', 2003, 200.0);
1 row created.

INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album2', 2000, 200.0);
1 row created.

INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album3', 2001, 200.0);
1 row created.

INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album4', 2001, 200.0);
1 row created.

INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album5', 1998, 200.0);
1 row created.

INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album6', 1999, 200.0);
1 row created.

INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album7', 2000, 200.0);
1 row created.

INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album8', 1990, 200.0);
1 row created.

INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album9', 1995, 200.0);
1 row created.

INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album10', 2003, 200.0);
1 row created.


-- Naplneni tabulky CD
------------------------------------------
declare
CURSOR Alba is select Nazev_A from Album;
pocet NUMBER;
begin
pocet := 1;
for i in Alba loop
INSERT INTO CD(ID_CD,Nazev_A) VALUES (pocet,i.Nazev_A);
INSERT INTO CD(ID_CD,Nazev_A) VALUES (100-pocet,i.Nazev_A);
pocet := pocet+1;
end loop;
end;
/
PL/SQL procedure successfully completed.


--Naplneni tabulky ZAMESTNANEC
--------------------------------------------
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM )
VALUES ( 1, 'Novak' );
1 row created.

INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM )
VALUES ( 2, 'Hruska' );
1 row created.

INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM )
VALUES ( 3, 'Novotny' );
1 row created.

INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM )
VALUES ( 4, 'Svoboda' );
1 row created.

INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM )
VALUES ( 5, 'Bartos' );
1 row created.

INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM )
VALUES ( 6, 'Bohac' );
1 row created.

INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM )
VALUES ( 7, 'Brychta' );
1 row created.

INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM )
VALUES ( 8, 'Heger' );
1 row created.

INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM )
VALUES ( 9, 'Merta' );
1 row created.

INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM )
VALUES ( 10, 'Vapenik' );
1 row created.


--Naplneni tabulky ZAKAZNIK
---------------------------------------------------------
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON )
VALUES ( 'Novotny',0, 'Adresa1',5234254);
1 row created.

INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON )
VALUES ( 'Zakaznik1',1, 'Adresa1',5234254);
1 row created.

INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON )
VALUES ( 'Zakaznik2',2, 'Adresa1',5234254);
1 row created.

INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON )
VALUES ( 'Zakaznik3',3, 'Adresa1',5234254);
1 row created.

INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON )
VALUES ( 'Zakaznik4',4, 'Adresa1',5234254);
1 row created.

INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON )
VALUES ( 'Zakaznik5',5, 'Adresa1',5234254);
1 row created.

INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON )
VALUES ( 'Zakaznik6',6, 'Adresa1',5234254);
1 row created.

INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON )
VALUES ( 'Zakaznik7',7, 'Adresa1',5234254);
1 row created.

INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON )
VALUES ( 'Zakaznik8',8, 'Adresa1',5234254);
1 row created.

INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON )
VALUES ( 'Zakaznik9',9, 'Adresa1',5234254);
1 row created.


--Naplneni tabulky INTERPRET
---------------------------------------------
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 1, 'Interpret1','Pozn1' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 2, 'Interpret2', 'Pozn2' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 3, 'Interpret3', 'Pozn3' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 4, 'Interpret4', 'Pozn4' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 5, 'Interpret5', 'Pozn5' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 6, 'Interpret6', 'Pozn6' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 7, 'Interpret7', 'Pozn7' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 8, 'Interpret8', 'Pozn8' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 9, 'Interpret9', 'Pozn9' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 10, 'Interpret10', 'Pozn10' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 11, 'Interpret11', 'Pozn11' );
1 row created.

INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn )
VALUES ( 12, 'Interpret12', 'Pozn12' );
1 row created.


-- Naplneni tabulky PISEN
--------------------------------------------------
DECLARE
CURSOR Interpreti is SELECT ID_I FROM Interpret;
pocet NUMBER;
BEGIN
pocet:=1;
for i in Interpreti LOOP
INSERT INTO Pisen ( NAZEV_P, ID_I, POZN, ROK )
VALUES ('Pisen'||pocet,i.ID_I,'Text'||pocet,1980+pocet);
INSERT INTO Pisen ( NAZEV_P, ID_I, POZN, ROK )
VALUES ('Pisnicka'||pocet,i.ID_I,'Text'||pocet,1980+pocet);
pocet:=pocet+1;
END LOOP;
END;
/
PL/SQL procedure successfully completed.


-- Naplneni tabulky OBJEDNAVKA
----------------------------------------------------
DECLARE
CURSOR RCisla is SELECT RC_ZAK FROM Zakaznik;
CURSOR Alba is SELECT Nazev_A FROM Album;
pocet NUMBER;
rc NUMBER;
BEGIN
pocet:=1;
OPEN RCisla;
FOR i IN Alba LOOP
FETCH RCisla INTO rc;
if RCisla%NOTFOUND then EXIT;
end if;
INSERT INTO Objednavka (ID_OBJ, VYRIZENA, POCET,RC_ZAK, NAZEV_A )
VALUES ( pocet, 'A', 2*pocet, rc, i.Nazev_A );
pocet:=pocet+1;
END LOOP;
CLOSE RCisla;
END;
/
PL/SQL procedure successfully completed.


--Naplneni tabulky FAKTURA
----------------------------------------------------------------------------
INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI )
VALUES ( 1, 1, '01.01.2003','A','15.01.2003' );
1 row created.

INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI )
VALUES ( 2, 2, '01.02.2003','N','15.02.2003' );
1 row created.

INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI )
VALUES ( 3, 1, '01.03.2003','A','15.03.2003' );
1 row created.

INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI )
VALUES ( 4, 5, '01.04.2003','A','15.04.2003' );
1 row created.

INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI )
VALUES ( 5, 2, '01.05.2003','N','15.05.2003' );
1 row created.

INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI )
VALUES ( 6, 7, '01.06.2003','A','15.06.2003' );
1 row created.

INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI )
VALUES ( 7, 3, '01.07.2003','N','15.07.2003' );
1 row created.

INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI )
VALUES ( 8, 2, '01.08.2003','A','15.08.2003' );
1 row created.

INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI )
VALUES ( 9, 1, '01.09.2003','N','15.09.2003' );
1 row created.

INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI )
VALUES ( 10, 1, '01.10.2003','N','15.10.2003' );
1 row created.


--Napleni tabulky OBSAZENA
---------------------------------------------------
DECLARE
CURSOR Pisnicky is select Nazev_P, ID_I from Pisen;
cursor Alba is select Nazev_A from Album;
nazev Album.Nazev_A%type;
BEGIN
OPEN ALba;
FOR i IN Pisnicky LOOP
FETCH Alba INTO nazev;
if alba%NOTFOUND then Exit;
end if;
INSERT INTO OBSAZENA ( NAZEV_P, ID_I, NAZEV_A )
VALUES ( i.Nazev_P,i.ID_I,nazev);
END LOOP;
CLOSE Alba;
END;
/
PL/SQL procedure successfully completed.


--Napleni tabulky ZA
---------------------------------------------------
DECLARE
CURSOR Objednavky is Select ID_OBJ from Objednavka;
CURSOR Faktury is select CIS_F from Faktura;
f Faktura.Cis_F%type;
BEGIN
OPEN Faktury;
FOR i IN Objednavky LOOP
FETCH Faktury INTO f;
if Faktury%NOTFOUND then Exit;
end if;
INSERT INTO ZA ( ID_OBJ, CIS_F )
VALUES ( i.ID_OBJ, f);
END LOOP;
END;
/
PL/SQL procedure successfully completed.


--Naplneni tabulky NA
-------------------------------
INSERT INTO NA (CIS_F, ID_CD )
VALUES ( 1, 99);
1 row created.

INSERT INTO NA (CIS_F, ID_CD )
VALUES ( 3, 97);
1 row created.


commit;
Commit complete.

--Skript pro otestovani jednotlivych triggeru a procedur.
---------------------------------------------------------
---------------------------------------------------------

--Pridani objednavky zakaznikem, ktery ma zaplacene vsechny faktury a na sklade je CD s pozadovanym albem.
----------------------------------------------------------------------------------------------------------
select * from Objednavka;

ID_OBJ 	V 	POCET 	RC_ZAK 	NAZEV_A
1 	A 	2 	0 	Album1
2 	A 	4 	1 	Album2
3 	A 	6 	2 	Album3
4 	A 	8 	3 	Album4
5 	A 	10 	4 	Album5
6 	A 	12 	5 	Album6
7 	A 	14 	6 	Album7
8 	A 	16 	7 	Album8
9 	A 	18 	8 	Album9
10 	A 	20 	9 	Album10

10 rows selected.

INSERT INTO Objednavka (ID_OBJ, VYRIZENA, POCET,RC_ZAK, NAZEV_A )
VALUES ( 50, 'A', 1, 2,'Album1' );
1 row created.

--Kontrola, ze byl zaznam pridan
select * from Objednavka;

ID_OBJ 	V 	POCET 	RC_ZAK 	NAZEV_A
1 	A 	2 	0 	Album1
2 	A 	4 	1 	Album2
3 	A 	6 	2 	Album3
4 	A 	8 	3 	Album4
5 	A 	10 	4 	Album5
6 	A 	12 	5 	Album6
7 	A 	14 	6 	Album7
8 	A 	16 	7 	Album8
9 	A 	18 	8 	Album9
10 	A 	20 	9 	Album10
50 	A 	1 	2 	Album1

11 rows selected.


--Pokus o vytvoreni objednavky zakaznikem, ktery ma nejakou nezaplacenou fakturu
INSERT INTO Objednavka (ID_OBJ, VYRIZENA, POCET,RC_ZAK, NAZEV_A )
VALUES ( 51, 'A', 1, 1,'Album1' );

INSERT INTO Objednavka (ID_OBJ, VYRIZENA, POCET,RC_ZAK, NAZEV_A )
            *

ERROR at line 1:
ORA-20001: Zakaznik si nemuze vytvorit novou objednavku
ORA-06512: at "MLEJNJ1.TR_NEZAPLACENA_FAKTURA", line 2
ORA-04088: error during execution of trigger 'MLEJNJ1.TR_NEZAPLACENA_FAKTURA'
--Kontrola, ze nebyl zaznam pridan
select * from Objednavka;

ID_OBJ 	V 	POCET 	RC_ZAK 	NAZEV_A
1 	A 	2 	0 	Album1
2 	A 	4 	1 	Album2
3 	A 	6 	2 	Album3
4 	A 	8 	3 	Album4
5 	A 	10 	4 	Album5
6 	A 	12 	5 	Album6
7 	A 	14 	6 	Album7
8 	A 	16 	7 	Album8
9 	A 	18 	8 	Album9
10 	A 	20 	9 	Album10
50 	A 	1 	2 	Album1

11 rows selected.



--Pridani objednavky na album, ktere neni na sklade.
----------------------------------------------------
--Pridani noveho alba, ktere neni na sklade
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA )
VALUES ('Album50', 2003, 200.0);
1 row created.

--Pokus o pridani tothoto alba na objednavku
INSERT INTO Objednavka (ID_OBJ, VYRIZENA, POCET,RC_ZAK, NAZEV_A )
VALUES ( 52, 'A', 1, 2,'Album50' );

INSERT INTO Objednavka (ID_OBJ, VYRIZENA, POCET,RC_ZAK, NAZEV_A )
            *

ERROR at line 1:
ORA-20002: CD neni dostupne skladem
ORA-06512: at "MLEJNJ1.TR_CD_NA_SKLADU", line 2
ORA-04088: error during execution of trigger 'MLEJNJ1.TR_CD_NA_SKLADU'
--Kontrola, ze nebyl zaznam pridan
select * from Objednavka;

ID_OBJ 	V 	POCET 	RC_ZAK 	NAZEV_A
1 	A 	2 	0 	Album1
2 	A 	4 	1 	Album2
3 	A 	6 	2 	Album3
4 	A 	8 	3 	Album4
5 	A 	10 	4 	Album5
6 	A 	12 	5 	Album6
7 	A 	14 	6 	Album7
8 	A 	16 	7 	Album8
9 	A 	18 	8 	Album9
10 	A 	20 	9 	Album10
50 	A 	1 	2 	Album1

11 rows selected.



--Pridani CD na fakturu, ktera obsahuje objednavku na dane album.
-----------------------------------------------------------------
select * from NA;

CIS_F 	ID_CD
1 	99
3 	97

INSERT INTO NA (CIS_F, ID_CD )
VALUES ( 2, 98);
1 row created.

--Kontrola, ze byl zaznam pridan
select * from NA;

CIS_F 	ID_CD
1 	99
3 	97
2 	98



--Pridani CD na fakturu, ktera neobsahuje objednavku na dane album.
-------------------------------------------------------------------
INSERT INTO NA (CIS_F, ID_CD )
VALUES ( 1, 7);

INSERT INTO NA (CIS_F, ID_CD )
            *

ERROR at line 1:
ORA-20003: Objednavky na fakture neobsahuji pozadavek na toto album.
ORA-06512: at "MLEJNJ1.TR_KONTROLA_FAKTURY", line 6
ORA-04088: error during execution of trigger 'MLEJNJ1.TR_KONTROLA_FAKTURY'
--Kontrola, ze nebyl zaznam pridan
select * from NA;

CIS_F 	ID_CD
1 	99
3 	97
2 	98


--Napleni tabulky Upominka a odvozene tabulky NovaUpominka
----------------------------------------------------------
delete from Upominka;
0 rows deleted.

select * from Upominka;

no rows selected
select * from NovaUpominka;

no rows selected
execute obchod_pkg.kontrola_upominek;
PL/SQL procedure successfully completed.

--Kontrola, ze byly tyto tabulky naplneny daty
select * from Upominka;

RC_ZAK 	POCET 	DATUM
1 	1 	17.05.2005
4 	1 	17.05.2005
6 	1 	17.05.2005
8 	1 	17.05.2005
9 	1 	17.05.2005

select * from NovaUpominka;

RC_ZAK
1
4
6
8
9