CREATE TABLE Ordini(CodPizza int REFERENCES Pizze(CodPizza) ON DELETE CASCADE,
                    CodiceFiscale varchar(16) REFERENCES Clienti(CodiceFiscale),
		    data datetime,
		    quantita int check (quantita>0),
		    urgenza varchar(5) NOT NULL check( urgenza in ('bassa', 'media','alta'))default 'media',
                    PRIMARY KEY (codPizza,CodiceFiscale,data)
		    )


INSERT INTO Ordini VALUES('10','bdgcgdwer234hd89','03/05/2004',3,'media');
INSERT INTO Ordini VALUES('20','bdgcgdwer234hd89','03/05/2004',4,'media');
INSERT INTO Ordini VALUES('30','bdgcgdwer234hd89','04/10/2004',2,'alta');
INSERT INTO Ordini VALUES('40','ndjfyrieos9046hd','07/01/2003',3,'bassa');
INSERT INTO Ordini VALUES('50','mdldoeeer234hd89','08/11/2003',5,'bassa');
INSERT INTO Ordini VALUES('30','ndjf0wweos9046hd','03/03/2003',8,'bassa');



create procedure TotOrdini @codPizza int
as

DECLARE @data datetime
DECLARE @ricavato NUMERIC


if ((SELECT COUNT(*) FROM Ordini WHERE codPizza=@codPizza)<=
    (SELECT COUNT(*) FROM Ordini WHERE codPizza IN (SELECT codPizza FROM Pizze WHERE nome = 'Margherita')))
begin
      DECLARE c CURSOR FOR select  data, sum(CostoUnitario*quantita) 
	                   from Pizze,Ordini
	                   where Pizze.CodPizza=Ordini.CodPizza
                           group by data 
      OPEN c
      FETCH NEXT FROM c INTO @data, @ricavato
      WHILE (@@FETCH_STATUS = 0)
	BEGIN
	  PRINT 'In data: ' + CAST(@data AS VARCHAR(15)) + ' è stata ricavata la somma: ' + CAST(@ricavato AS VARCHAR(10)) 
	  FETCH NEXT FROM c INTO @data, @ricavato
	END
      CLOSE c
      DEALLOCATE c
  end
else 
  begin
    update Pizze set costoUnitario = CostoUnitario + .5 where nome='Margherita'
    insert into Pizze values (70,'Fantasia',6)
end
GO