Stored Procedure
-
Upload
ifeoma-fulton -
Category
Documents
-
view
36 -
download
0
description
Transcript of Stored Procedure
Stored ProcedureStored Procedure er programstumper, der gemmes i databasen og afvikles op databaseserveren på samme måde som forespørgsler.
Med Stored Procedures kan man på den måde flytte basal behandlig op på serveren og opnå en både mere optimal og sikker afvikling.
Fra C# programmer kan man aktivere Stored Proceures med Command objektets meroder:
ExecuteNonQuery: Hvis der ikke ønskes brugt noget resultat fra den afviklede Stored Procedure.
ExecuteScalar: Hvis der ønskes et enkelt resulat fra den afviklede Stored Procedure
ExecuteReader: Hvis en Stored Procedure aflevere en forespørgsel som resultat og man ønsker at anvende denne.
For at afvilke en Stored Procedure anvendes SQL udtrykket EXEC efterfulgt af parametrene, enten i som værdier i den declarerede rækkefølge med komma imellem eller ved at angive parameterens navn = tildelt værdi. Dette vises i de efterfølgende eksempler
Eksempler på Stored ProcedurePrimitiv beregnings procedure, der illustrere simple parametre og returværdierDer kan returneres én værdi.
CREATE PROCEDURE BroekVaerdi (@taeller int = 1,@naevner int = 1)
ASBEGIN DECLARE @result int
IF @naevner = 0BEGIN
RETURN 0.0ENDELSE BEGIN
set @result = @taeller / @naevner;RETURN @result
ENDEND
Eksempler på Stored ProcedureProceduren kaldes enten med parametrene i rækkefølge, som de er erklæret i proceduren, eller med navngivning.
Resultater kan bruges lokalt i declarede variable.Hvis værdier skal bruges uden for SQL, kan de returneres i en forespørgsel.Denne forespørgsel læses fra C# med DataReader.
DECLARE @tal1 int;DECLARE @tal2 int;
EXEC @tal1 = BroekVaerdi 10, 2
EXEC @tal2 = BroekVaerdi @naevner=2, @taeller=10
SELECT @tal1, @tal2 /* resultat i en forespørgsel*/
Eksempler på Stored ProcedureCREATE PROCEDURE FindPersonMedEfternavn(
@efternavn varchar(10) = '%')ASBEGIN
select * from person where efternavn like @efternavn;END
Eksempler på brug af proceduren
a) EXEC FindPersonMedEfternavn “Larsen%”
b) EXEC FindPersonMedEfternavn
Eksempler på Stored ProcedureCREATE PROCEDURE OpretAfdeling /* med muligt autonr*/(
@afdnr int,@afdnavn varchar(30)
)ASBEGIN
insert into afdeling (Afdnr, AfdNavn) Values (@afdnr,@afdnavn);END
Eksempler på brug af proceduren
a) EXEC OpretAfdeling 10, ‘Afd ti’
b) EXEC OpretAfdeling @afdnr=10 @afdnavn=‘Afd ti’
c) EXEC OpretAfdeling @afdnavn=‘Afd ti’ @afdnr=10
Eksempler på Stored ProcedureCREATE PROCEDURE OpretAfdeling2 /* med muligt autonr*/(
@afdnr int = -1,@afdnavn varchar(30)
)ASBEGIN
BEGIN TRANSACTIONIF @afdnr < 0BEGIN
select @afdnr = max(afdnr) from afdeling;set @afdnr = @afdnr +1;
ENDinsert into afdeling (Afdnr, AfdNavn) Values (@afdnr,@afdnavn);select * from afdeling WHERE AfdNr=@afdnr;COMMIT TRASACTION
END
Eksempler på brug af proceduren
a) EXEC OpretAfdeling2 @afdnr=10 @afdnavn=‘Afd ti’
b) EXEC OpretAfdeling2 @afdnavn=‘Afd auto’
Eksempler på Stored ProcedureCREATE PROCEDURE OpretPeson /* I tabel hvor key er def. som identity autonummerering*/(
@navn varchar(30))ASBEGIN
insert into Person (Navn) Values (@navn);select * from Person WHERE Id=@@identity;
END
Eksempler på brug af proceduren
a) EXEC OpretPerson ‘Bjørk’
b) EXEC OpretPerson @navn=‘Busch’
Eksempler på Stored ProcedureCREATE PROCEDURE OpretKontoPostering (
@KontoID int,@Beloeb int,@Tekst varchar(30)
)ASBEGIN
DECLARE @Saldo intBEGIN TRANSACTIONINSERT INTO Transakion (kontoID, Beloeb, Tekst)
Values (@KontoID@Beloeb,@Tekst);UPDATE Konto SET Saldo=Saldo+@Beloeb WHERE KontoID=@KontoID;SELECT @Saldo = Saldo FROM konto WHERE KontoID=@KontoID; IF @Saldo < 0
ROLLBACK TRANSACTIONELSE
COMMIT TRANSACTIONEND
Eksempel på brug af proceduren
EXEC OpretKontoPostering @KontoID=1002 @Beloeb=100 @Tekst=‘kontantudbetaling‘
Eksempler på Stored ProcedureCREATE PROCEDURE RetPersonNavn2 (
@PersonNr int,@Fornavn varchar(30),@Efternavn varchar(30),@OldFornavn varchar(30),@OldEfternavn varchar(30)
)ASBEGIN
DECLARE @XFornavn varchar (30)DECLARE @XEfternavn varchar (30)DECLARE @outtable as table /* temporær tabel til rettede poster – før ændring */
( Fornavn varchar(30) , Efternavn varchar(30))
BEGIN TRANSACTIONUPDATE Person SET Fornavn=@Fornavn , Efternavn=@Efternavn
OUTPUT deleted.Fornavn, deleted.EfternavnINTO @outtable /* her fås gamle poster*/WHERE PersonNr=@PersonNr;
SELECT @XFornavn = Fornavn, @XEfternavn= Efternavn FROM @outtable;IF @XFornavn = @OldFornavn AND @XEfternavn = @OldEfternavnBEGIN
COMMIT TRANSACTION RETURN 1
ENDELSEBEGIN
print 'Roolback da data var ændret før opdatering'ROLLBACK TRANSACTIONRETURN 0
ENDEND
Eksempel på brug af proceduren
EXEC RetPersonFornavn 5, 'NyFornavn', ‘NyEfternavn‘,‘GlFornavn’, ‘GlEfternavn
Denne løsning sikre at præcis de felter vi ændrer ikke er rettet af andre undervejs.Dette er en “stærkere” løsning end “bare” at sikre at rækken ikke er ændret.