-- creazione tabella CONTOCORRENTE

CREATE TABLE ContoCorrente(
       NumCC int CHECK (NumCC % 2 != 0),
       titolare varchar(16) REFERENCES Persone(CodiceFiscale)
                            ON DELETE CASCADE 
		            ON UPDATE CASCADE,
       saldo decimal(10,2) NOT NULL,
       filiale  int         REFERENCES Filiali(Codice) 
		            ON DELETE NO ACTION 
		            ON UPDATE CASCADE,
       PRIMARY KEY (filiale,NumCC) 
)

-- esempi di tuple

insert into ContoCorrente values (1,'bdgcgdwer234hd89', 1000, 1);
insert into ContoCorrente values (3,'bdgcgdwer234hd89', 3000, 1);
insert into ContoCorrente values (1,'bdgcgdwer234hd89', 1000, 2);
insert into ContoCorrente values (5,'mdld345er234hd89', 1000, 1);
insert into ContoCorrente values (7,'ndjf0wweos9046hd', 1000, 1);
insert into ContoCorrente values (3,'ndjfyrieos9046hd', 1000, 2);


-- stored procedure

CREATE PROCEDURE Budjet @importo decimal(10,2), @citta varchar(30)
AS 
DECLARE @saldoMedio decimal(10,2)

SELECT @saldoMedio =AVG(saldo) 
FROM ContoCorrente 
WHERE filiale IN 
      (SELECT codice 
       FROM Filiali
       WHERE citta = @citta) 

IF (@importo < @saldoMedio)
 BEGIN 
   DECLARE @cognome varchar(30)
   DECLARE @nome varchar(30)
   DECLARE @saldo decimal(10,2)

   DECLARE ImpCursor CURSOR FOR  
           SELECT Cognome,Nome,saldo
           FROM Persone, ContoCorrente
           WHERE CodiceFiscale = titolare
                  and saldo <= @importo and filiale IN 
                       (SELECT codice 
                        FROM Filiali
                        WHERE citta = @citta)
   OPEN ImpCursor
   
   FETCH NEXT FROM ImpCursor INTO @Cognome, @nome, @saldo
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      PRINT 'Cognome: ' + @Cognome + ' Nome: ' + @nome + ' saldo: '
           + CAST(@saldo,VARCHAR(20))
      FETCH NEXT FROM ImpCursor INTO @Cognome, @nome, @saldo
   END
   CLOSE ImpCursor
   DEALLOCATE ImpCursor
 END 
ELSE
   UPDATE ContoCorrente
   SET saldo = saldo + 50
   WHERE filiale IN 
         (SELECT codice 
          FROM Filiali
          WHERE citta = @citta)