March 5, 2008 – Zidar
MS SQL 2000 doneo je jednu lepu novinu - mogućnost pisanja korisničkih funkcija. Uz to je tiho došla još jedna novina, slabo dokumentovana, iako se može naći u Boks On Line. Korisničke funkcije mogu se pozvati iz CHECK constraints. Funkcije ko funkcije, mogu da pozivaju redove iz posmatrane tabele, ali i drugih tabela. Ovo nam omogućava da izbegnemo proceduralno programiranje (trigere) i da neka ograničenja za koje smo morali koristiti Stored Procedures ili čak front-end funkcije, sada možemo da postavimo na nivo tabele, tamo gde i treba.
Na jednostavnom primeru pokazaćemo kako to radi.
Dobrotvorna organizacija daje pomoc gradjanima. Svaki gradjanin ima svoj ‘racun’. Novac se isplacuje višekratno, u manjim iznosima. Nikome se ne sme isplatiti više od 100 dinara ukupno.
Krairana je tabela ‘Zaduzenja’ sa tri kolone - Transakcija, Racun, IznosZaduzenja.
IznosZaduzenja je ono sto se isplaćuje u jednoj transakciji. Ukupan zbir IznosZaduzenja po računu ne sme da predje 100.
Ovo su ograničenja koja želimo da postavimo:
Pretpostavljamo IznosZaduzenja mora biti pozitivan, ne mozž biti nula ili manji.
Ukupan zbir svih IznosZaduzenja po Racunu ne sme preći 100.
CREATE TABLE Zaduzenja – DROP TABLE Accounnt
(Transakcija int PRIMARY KEY
, Racun int NOT NULL
, IznosZaduzenja money NOT NULL CHECK (IznosZaduzenja > 0)
)
Ovako nasa tabela postuje prvo ograničenje. Kako da sprečimo da se nekome isplati više od 100 dinara?
MS SQL, od verzije 2000 pa naovamo ima jednu malo poznatu osobinu - u CHECK constraints mogu se koristiti user defined functions.
Iskoristimo to. Napišimo prvo funkciju koja izračunava ukupno zaduženje po računu. Funkcija bi mogla ovako da izgleda:
CREATE FUNCTION dbo.UkupnoZaduzenje (@Racun int)
RETURNS money
AS
BEGIN
DECLARE @Retval money
SET @Retval = (SELECT SUM(IznosZaduzenja)
FROM Zaduzenja WHERE Racun = @Racun
)
RETURN COALESCE(@Retval,0) – inace vraca NULL kad ne postoji ni jedan red u in line kveriju
END
Funkciju pozivamo ovako:
SELECT dbo.UkupnoZaduzenje(100)
Ovako pravimo CHECK constraint koji koristi nasu funkciju:
ALTER TABLE Zaduzenja
–DROP CONSTRAINT ck_UkupnoZaduzenjeManjeod100
ADD CONSTRAINT ck_UkupnoZaduzenjeManjeod100
CHECK
(
100 >= dbo.UkupnoZaduzenje (Racun)
)
Da vidimo da li radi:
INSERT INTO Zaduzenja VALUES (1,100,50)
(1 row(s) affected)
SELECT * FROM Zaduzenja
Transakcija Racun IznosZaduzenja
———– ———– ———————
1 100 50.0000
(1 row(s) affected)
Dodajmo jos 25 dinara na isti racun:
INSERT INTO Zaduzenja VALUES (2,100,25)
(1 row(s) affected)
Sada imamo u tabeli Zaduzenja:
Transakcija Racun IznosZaduzenja
———– ———– ———————
1 100 50.0000
2 100 25.0000
Ukupno azduzenje za Racun=100 jeste 75.
Pokušajmo da dodamo 30 dinara, što bi podiglo ukupno zaduženje na 105.
INSERT INTO Zaduzenja VALUES (3,100,30)
Rezultat:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint ‘ck_UkupnoZaduzenjeManjeod100′.
The conflict occurred in database ‘master’, table ‘Zaduzenja’, column ‘Racun’.
The statement has been terminated.
Još 25 moze da prodje:
INSERT INTO Zaduzenja VALUES (3,100,25)
(1 row(s) affected)
i ništa vise:
INSERT INTO Zaduzenja VALUES (4,100,0.001)
Ponovo dobijamo
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint ‘ck_UkupnoZaduzenjeManjeod100′.
The conflict occurred in database ‘Zadaci’, table ‘Zaduzenja’, column ‘Racun’.
The statement has been terminated.
SELECT * FROM Zaduzenja
Transakcija Racun IznosZaduzenja
———– ———– ———————
1 100 50.0000
2 100 25.0000
3 100 25.0000
(3 row(s) affected)
Ne moramo više da poštovanje ograničenja obezbeđujemo na front endu, nit SQL programiranjem (trigeri i stored procedure). Kod u funkciji nije proceduralni, ne maskiramo funkcijom nikakav kursor. Onako kako i treba da bude.
Objavljeno u: Uncategorized | Ukupno komentara: 2 »
