CREATE TYPE Typ_adresowy AS OBJECT(
	Ulica VARCHAR2(50),
	Miasto VARCHAR2(50),
	Kraj VARCHAR2(25),
	Kod VARCHAR2(10)
);

CREATE TYPE Typ_list AS VARRAY(10) OF VARCHAR2(20); 

CREATE TYPE Typ_klientow AS OBJECT(
	Id_klienta INTEGER,
	Nazwisko VARCHAR2(50),
	Imie VARCHAR2(50),
	Adres Typ_adresowy,
	Lista_telefonow Typ_list,
	ORDER MEMBER FUNCTION Kol_klientow (x IN Typ_klientow) RETURN INTEGER,
	PRAGMA RESTRICT_REFERENCES(Kol_klientow, WNDS, WNPS, RNPS, RNDS)
);

CREATE OR REPLACE TYPE BODY Typ_klientow AS
  ORDER MEMBER FUNCTION
  Kol_klientow (x IN Typ_klientow) RETURN INTEGER IS
  BEGIN
      RETURN Id_klienta - x.Id_klienta;
  END; 
END;

CREATE TYPE Typ_towarow AS OBJECT (
	Id_towaru INTEGER,
	Opis VARCHAR2(10), 
	Cena NUMBER,
	VAT NUMBER
);

CREATE TYPE Typ_pozycji AS OBJECT(
	Id_pozycji INTEGER,
	RefTowaru Ref Typ_Towarow,
	Ilosc NUMBER
);

CREATE OR REPLACE TYPE Lista_pozycji AS TABLE OF Typ_pozycji;

CREATE TYPE Typ_zamowien AS OBJECT(
	Id_zamowienia INTEGER,
	Klient REF Typ_klientow,
	Data_zam DATE,
	Pozycje_zam Lista_pozycji,
	MAP MEMBER FUNCTION Numer_zam RETURN NUMBER 

PRAGMA RESTRICT_REFERENCES(Numer_zam, WNDS, WNPS, RNPS, RNDS), 
MEMBER FUNCTION Suma RETURN NUMBER, 
PRAGMA RESTRICT_REFERENCES(Suma, WNDS, WNPS) 
);

CREATE OR REPLACE TYPE BODY Typ_zamowien  AS MEMBER FUNCTION Suma RETURN NUMBER IS
    i          INTEGER;
    towar      Typ_towarow;
    pozycja    Typ_pozycji;
    sumuj      NUMBER := 0;
    cena       NUMBER;
  BEGIN
     FOR i IN 1..SELF.Pozycje_zam.COUNT  LOOP
        pozycja := SELF.Pozycje_zam(i);
        SELECT DEREF(pozycja.RefTowaru) INTO towar FROM DUAL ;
        sumuj := sumuj + pozycja.Ilosc * towar.Cena ;
     END LOOP;
     RETURN sumuj;
  END;
	MAP MEMBER FUNCTION Numer_zam RETURN NUMBER IS
	  BEGIN
		   RETURN Id_zamowienia;
	  END;
END;


CREATE TABLE Klienci OF Typ_klientow
 (Id_klienta PRIMARY KEY);
 
CREATE TABLE Towary OF Typ_towarow
(Id_towaru PRIMARY KEY) ;


CREATE TABLE Zamowienia OF Typ_zamowien (
	Id_zamowienia PRIMARY KEY,
	SCOPE FOR (Klient) IS Klienci 
-- wartosci w kolumnie Klient ograniczone do obiektow w tabeli Klienci
)
NESTED TABLE Pozycje_zam STORE AS Pozycje;


ALTER TABLE Pozycje
ADD (SCOPE FOR (RefTowaru) IS Towary) ;


CREATE INDEX Ind_Pozycje
ON  Pozycje (NESTED_TABLE_ID) ;-- kolumna systemowa identyfikujaca tabele zagniezdzona 




CREATE UNIQUE INDEX Unique_Pozycje
ON Pozycje (NESTED_TABLE_ID, Id_pozycji) ;


INSERT INTO Towary VALUES(1004,'Cukier',3.00, 10);
INSERT INTO Towary VALUES(1011, 'Pomarancze', 5.50, 10);
INSERT INTO Towary VALUES(1534, 'Tablet', 450.00, 22);
INSERT INTO Towary VALUES(1535, 'Banan', 1.23, 22);


INSERT INTO Klienci VALUES (
    1, ‘Kowalski' , 'Jan’,
    Typ_adresowy('Kwiatowa 6', 'Milanowek', 'Polska', '12344'),
    Typ_list('765432345')
);


INSERT INTO Klienci VALUES (
    2, 'Sqlowy','Pankracy',
    Typ_adresowy('Biala 6', 'Brwinow', 'Polska', '12312'),
    Typ_list('13-13-1312', '12-12-1212')
);


INSERT INTO Zamowienia
  SELECT  1001, REF(k),
          SYSDATE,
          Lista_pozycji()
   FROM   Klienci k
   WHERE  k.Id_klienta= 1 ;


   
INSERT INTO THE (
  SELECT  z.Pozycje_zam
   FROM   Zamowienia z
   WHERE  z.Id_zamowienia = 1001
  )
SELECT  2001, REF(t), 12
   FROM   Towary t
   WHERE  t.Id_towaru = 1534

   
   INSERT INTO Zamowienia
  SELECT  2001, REF(k),
          SYSDATE,
          Lista_pozycji()
   FROM   Klienci k
   WHERE  k.Id_klienta= 2 ;
   
   
   INSERT INTO THE (
  SELECT  z.Pozycje_zam
   FROM   Zamowienia z
   WHERE  z.Id_zamowienia = 2001
  )
  SELECT  02, REF(t), 12
   FROM   Towary t
   WHERE  t.Id_towaru = 1004;

INSERT INTO THE (
  SELECT  z.Pozycje_zam
   FROM   Zamowienia z
   WHERE  z.Id_zamowienia = 2001
  )
  VALUES( Typ_pozycji(11, NULL, 2) ) ;

  UPDATE THE (
  SELECT  z.Pozycje_zam
   FROM   Zamowienia z
   WHERE  z.Id_zamowienia = 2001
  ) tab  -- Uzycie aliasu do wyniku spłaszczonego zapytania
  SET tab.RefTowaru =
   (SELECT REF(t)
     FROM  Towary t
     WHERE t.Id_towaru = 1011
     )
  WHERE tab.Id_pozycji = 11 ;
  
  INSERT INTO Zamowienia
SELECT 1, REF(k), Sysdate, Lista_pozycji()
FROM Klienci k
WHERE Id_klienta = 1;


INSERT INTO THE(SELECT Pozycje_zam FROM Zamowienia z 
                                   WHERE z.Id_zamowienia = 1)
SELECT 1, REF(t), 2
FROM Towary t
WHERE Id_towaru = 1011;

INSERT INTO Klienci
  VALUES (
    3, 'Koza' , 'Jan',
    Typ_adresowy('Jelcza 5', 'Warszawa', 'Polska', '12345'),
    Typ_list('22-333-332', '22-333-333')
    ) ;


	
	DECLARE
	Refklienta REF Typ_klientow;
	Reftowaru REF Typ_towarow;
BEGIN
	SELECT REF(k) INTO Refklienta FROM Klienci k
	WHERE k.Nazwisko = 'Banachowski';
	SELECT REF(t) INTO Reftowaru FROM Towary t
	WHERE t.Opis = 'Tablet';
	INSERT INTO Zamowienia
	VALUES (Typ_zamowien(3, Refklienta, Sysdate,  
                   Lista_pozycji(Typ_pozycji(1,Reftowaru,20))));
END;


SELECT z.Id_zamowienia, z.Klient.Nazwisko, z.Klient.Imie, z.Klient.Adres.Miasto
FROM Zamowienia z;


SELECT  VALUE(z) FROM   Zamowienia z;


SELECT  DEREF(z.Klient),   -- cała informacja o kliencie
       z.Data_zam, z.Id_zamowienia, -- informacja o zamowieniu
        z.Pozycje_zam -- informacja o pozycjach zamowienia
 FROM   Zamowienia z
 WHERE  z.Id_zamowienia = 1001 ;


 SELECT  z.Id_zamowienia, z.Suma() 
 FROM    Zamowienia z;

 SELECT z.Klient.Nazwisko, z.Id_zamowienia, tab.Id_pozycji, tab.RefTowaru.Opis, tab.Ilosc
FROM THE (
  SELECT  z.Pozycje_zam
   FROM   Zamowienia z
   WHERE  z.Id_zamowienia = 2001
  ) tab, Zamowienia z
WHERE z.Id_zamowienia= 2001
UNION
SELECT z.Klient.Nazwisko, z.Id_zamowienia, tab.Id_pozycji, tab.RefTowaru.Opis, tab.Ilosc
FROM THE (
  SELECT  z.Pozycje_zam
   FROM   Zamowienia z
   WHERE  z.Id_zamowienia = 1001
  ) tab, Zamowienia z
WHERE z.Id_zamowienia= 1001;


CREATE TABLE Pomo(Nazwisko VARCHAR2(50),
Id_zamowienia INTEGER, Id_pozycji INTEGER, Opis VARCHAR2(10));

CREATE OR REPLACE PROCEDURE Oblicz AS
z Zamowienia%ROWTYPE;       k Typ_klientow;
Tab Lista_pozycji;   pozycja Typ_pozycji;
towar Typ_towarow;
BEGIN
   DELETE Pomo;
   FOR z IN (SELECT * FROM Zamowienia) LOOP
           SELECT DEREF(z.Klient) INTO k FROM DUAL;
           Tab := z.Pozycje_zam;
           FOR i IN 1..Tab.COUNT LOOP
                   pozycja :=tab(i);
                   SELECT DEREF(pozycja.RefTowaru) INTO towar FROM DUAL;
                   INSERT INTO Pomo 
                   VALUES (k.Nazwisko,z.Id_zamowienia, tab(i).Id_pozycji,towar.Opis);
           END LOOP;
   END LOOP;
END;

CREATE OR REPLACE TYPE Typ_zamowien_klientow AS OBJECT(
	Licznik_zam INTEGER,
	Kraj VARCHAR2(50)
);

CREATE VIEW Zamowienia_klientow OF Typ_zamowien_klientow 
WITH OBJECT OID (Kraj) AS 
SELECT COUNT(z.Id_zamowienia), z.Klient.Adres.Kraj
FROM Zamowienia z
GROUP BY z.Klient.Adres.Kraj;





SELECT  z.Id_zamowienia, z.Suma() 
 FROM    Zamowienia z;