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

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

-- Naplneni tabulky ALBUM
----------------------------------------
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA ) 
 VALUES ('Album1', 2003, 200.0);
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA ) 
 VALUES ('Album2', 2000, 200.0);
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA ) 
 VALUES ('Album3', 2001, 200.0);
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA ) 
 VALUES ('Album4', 2001, 200.0);
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA ) 
 VALUES ('Album5', 1998, 200.0);
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA ) 
 VALUES ('Album6', 1999, 200.0);
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA ) 
 VALUES ('Album7', 2000, 200.0);
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA ) 
 VALUES ('Album8', 1990, 200.0);
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA ) 
 VALUES ('Album9', 1995, 200.0);
INSERT INTO ALBUM ( NAZEV_A, ROK, CENA ) 
 VALUES ('Album10', 2003, 200.0);

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

--Naplneni tabulky ZAMESTNANEC
--------------------------------------------
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM ) 
 VALUES ( 1, 'Novak' );
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM ) 
 VALUES ( 2, 'Hruska' );
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM ) 
 VALUES ( 3, 'Novotny' );
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM ) 
 VALUES ( 4, 'Svoboda' );
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM ) 
 VALUES ( 5, 'Bartos' );
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM ) 
 VALUES ( 6, 'Bohac' );
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM ) 
 VALUES ( 7, 'Brychta' );
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM ) 
 VALUES ( 8, 'Heger' );
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM ) 
 VALUES ( 9, 'Merta' );
INSERT INTO ZAMESTNANEC (ID_ZAM, JMENO_ZAM ) 
 VALUES ( 10, 'Vapenik' );

--Naplneni tabulky ZAKAZNIK
---------------------------------------------------------
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON ) 
 VALUES ( 'Novotny',0, 'Adresa1',5234254);
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON ) 
 VALUES ( 'Zakaznik1',1, 'Adresa1',5234254);
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON ) 
 VALUES ( 'Zakaznik2',2, 'Adresa1',5234254);
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON ) 
 VALUES ( 'Zakaznik3',3, 'Adresa1',5234254);
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON ) 
 VALUES ( 'Zakaznik4',4, 'Adresa1',5234254);
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON ) 
 VALUES ( 'Zakaznik5',5, 'Adresa1',5234254);
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON ) 
 VALUES ( 'Zakaznik6',6, 'Adresa1',5234254);
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON ) 
 VALUES ( 'Zakaznik7',7, 'Adresa1',5234254);
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON ) 
 VALUES ( 'Zakaznik8',8, 'Adresa1',5234254);
INSERT INTO ZAKAZNIK (JMENO_ZAK, RC_ZAK, ADRESA,TELEFON ) 
 VALUES ( 'Zakaznik9',9, 'Adresa1',5234254);

--Naplneni tabulky INTERPRET
---------------------------------------------
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 1, 'Interpret1','Pozn1' );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 2, 'Interpret2', 'Pozn2' );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 3, 'Interpret3', 'Pozn3'  );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 4, 'Interpret4', 'Pozn4'  );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 5, 'Interpret5', 'Pozn5'  );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 6, 'Interpret6', 'Pozn6'  );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 7, 'Interpret7', 'Pozn7'  );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 8, 'Interpret8', 'Pozn8'  );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 9, 'Interpret9', 'Pozn9'  );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 10, 'Interpret10', 'Pozn10'  );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 11, 'Interpret11', 'Pozn11'  );
INSERT INTO INTERPRET ( ID_I, JMENO_I, Pozn ) 
 VALUES ( 12, 'Interpret12', 'Pozn12'  );

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

-- 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;
/
  
--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' );
INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI ) 
 VALUES ( 2, 2, '01.02.2003','N','15.02.2003' );
INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI ) 
 VALUES ( 3, 1, '01.03.2003','A','15.03.2003' );
INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI ) 
 VALUES ( 4, 5, '01.04.2003','A','15.04.2003' );
INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI ) 
 VALUES ( 5, 2, '01.05.2003','N','15.05.2003' );
INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI ) 
 VALUES ( 6, 7, '01.06.2003','A','15.06.2003' );
INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI ) 
 VALUES ( 7, 3, '01.07.2003','N','15.07.2003' );
INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI ) 
 VALUES ( 8, 2, '01.08.2003','A','15.08.2003' );
INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI ) 
 VALUES ( 9, 1, '01.09.2003','N','15.09.2003' );
INSERT INTO FAKTURA ( CIS_F, ID_ZAM, DAT_VYSTAV, ZAPLACENO, DAT_SPLATNOSTI ) 
 VALUES ( 10, 1, '01.10.2003','N','15.10.2003' );
 
--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;
/

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

--Naplneni tabulky NA
-------------------------------
INSERT INTO NA (CIS_F, ID_CD ) 
 VALUES ( 1, 99);
INSERT INTO NA (CIS_F, ID_CD ) 
 VALUES ( 3, 97);
 
commit;