home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
ftp.disi.unige.it
/
2015-02-11.ftp.disi.unige.it.tar
/
ftp.disi.unige.it
/
pub
/
.person
/
MesitiM
/
teach
/
sol9A.txt
< prev
next >
Wrap
Text File
|
2003-05-26
|
2KB
|
71 lines
-- 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)