Arvestame olukorda, kus salvestatud protseduurid võivad päringu jõudlust halvendada.
Salvestatud protseduuride kompileerimisel MS SQL Server 2000-s paigutatakse salvestatud protseduurid protseduuride vahemällu, mis võib aidata nende täitmisel jõudlust suurendada, välistades vajaduse salvestatud protseduurikoodi sõelumise, optimeerimise ja kompileerimise järele.
Teisest küljest on salvestatud protseduuri kompileeritud koodi salvestamisel lõkse, millel võib olla vastupidine mõju.
Fakt on see, et salvestatud protseduuri koostamisel koostatakse vastavalt protseduuri koodi moodustavate avalduste täitmisplaan, kui kompileeritud salvestatud protseduur on vahemällu salvestatud, siis salvestatakse ka selle täitmisplaan ja seetõttu salvestatud protseduur ei tööta. olema optimeeritud konkreetse olukorra ja päringu parameetrite jaoks.
Teeme selle demonstreerimiseks väikese katse.
SAMM 1. Andmebaasi loomine.
Katse jaoks loome eraldi andmebaasi.
LOO ANDMEBAAS test_sp_perf
SEES (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
LOGI SISSE (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
2. SAMM. Tabeli koostamine.
CREATE TABLE sp_perf_test(veerg1 int, veerg2 tähemärki(5000))
3. SAMM. Tabeli täitmine teststringidega. Korduvad read lisatakse tabelisse tahtlikult. 10 000 rida numbritega 1 kuni 10 000 ja 10 000 rida numbritega 50 000.
DEKLARERI @i int
MÄÄRA @i=1
Kuni ma<10000)
ALUSTA
INSERT INTO sp_perf_test(veerg1, veerg2) VALUES(@i,"Teststring #"+CAST(@i as char(8)))
INSERT INTO sp_perf_test(veerg1, veerg2) VALUES(50000,"Teststring #"+CAST(@i as char(8)))
SEADISTA @i= @i+1
LÕPP
SELECT COUNT(*) FROM sp_perf_test
MINNA
4. SAMM. Looge rühmitamata indeks. Kuna täitmisplaan on koos protseduuriga vahemällu salvestatud, kasutatakse indeksit kõigi kõnede puhul ühtemoodi.
LOO MITTEKLUSTEERITUD INDEX CL_perf_test ON sp_perf_test(veerg1)
MINNA
5. SAMM. Looge salvestatud protseduur. Protseduur lihtsalt täidab SELECT-lause koos tingimusega.
LOO PROC proc1 (@paramint)
AS
SELECT veerg1, veerg2 FROM sp_perf_test WHERE [e-postiga kaitstud]
MINNA
6. SAMM. Käivitage salvestatud protseduur. Haavatava protseduuri käivitamisel kasutatakse spetsiaalselt selektiivset parameetrit. Protseduuri tulemusena saame 1 rea. Täitmisplaan näitab rühmitamata indeksi kasutamist, nagu päring on valikuline ja see on parim viis stringi eraldamiseks. Ühe rea toomiseks optimeeritud protseduur salvestatakse protseduuri vahemällu.
EXEC proc1 1234
MINNA
7. SAMM. Salvestatud protseduuri käivitamine mitteselektiivse parameetriga. Parameetrina kasutatakse väärtust 50000. Sellised read, mille esimese veeru väärtus on vastavalt umbes 10000, kasutades rühmitamata indeksit ja järjehoidjate otsinguoperatsiooni, on ebaefektiivsed, kuid kuna kompileeritud kood koos täitmisplaaniga salvestatakse protseduuri vahemälu, seda kasutatakse. Täitmisplaan näitab seda ja ka seda, et järjehoidjate otsimise toiming viidi läbi 9999 rea jaoks.
EXEC proc1 50000
MINNA
8. SAMM. Ridade valik, mille esimene väli on võrdne 50 000-ga. Eraldi päringu täitmisel päring optimeeritakse ja kompileeritakse esimese veeru kindla väärtusega. Selle tulemusel tuvastab päringu optimeerija, et väli dubleeritakse mitu korda, ja otsustab kasutada tabeli kontrollimise toimingut, mis on antud juhul palju tõhusam kui rühmitamata indeksi kasutamine.
SELECT veerg1, veerg2 FROM sp_perf_test WHERE veerg1=50000
MINNA
Seega võime järeldada, et salvestatud protseduuride kasutamine ei pruugi alati päringu jõudlust parandada. Peaksite olema väga ettevaatlik nende salvestatud protseduuride suhtes, mis töötavad muutuva ridade arvuga tulemustega ja kasutavad erinevaid täitmisplaane.
Skripti abil saate katset oma MS SQL serveris korrata.
Salvestatud protseduuride tüübid
SQL Serveris on mitut tüüpi salvestatud protseduurid.
- Süsteemne salvestatud protseduurid mõeldud erinevate haldustoimingute tegemiseks. Peaaegu kõik serveri haldustoimingud tehakse nende abiga. Võime öelda, et süsteem salvestatud protseduurid on liides, mis pakub tööd süsteemitabelitega, mis lõppkokkuvõttes taandub andmete muutmisele, lisamisele, kustutamisele ja nii kasutaja- kui ka süsteemiandmebaaside süsteemitabelitest. Süsteemne salvestatud protseduurid eesliitega sp_ , need salvestatakse süsteemi andmebaasi ja neid saab kutsuda mis tahes muu andmebaasi kontekstis.
- Kohandatud salvestatud protseduurid teatud toiminguid rakendada. Salvestatud protseduurid- täielik andmebaasiobjekt. Selle tulemusena on iga salvestatud protseduur asub konkreetses andmebaasis, kus see käivitatakse.
- Ajutine salvestatud protseduurid eksisteerivad vaid lühikest aega, pärast mida server need automaatselt hävitab. Need jagunevad kohalikeks ja globaalseteks. Kohalik ajutine salvestatud protseduurid saab helistada ainult ühendusest, milles need on loodud. Sellise protseduuri loomisel tuleb sellele anda nimi, mis algab ühe # märgiga. Nagu kõik ajutised objektid, salvestatud protseduurid seda tüüpi kustutatakse automaatselt, kui kasutaja serveriga ühenduse katkestab, taaskäivitab või peatab. Globaalne ajutine salvestatud protseduurid saadaval kõigi sama protseduuriga serveriühenduste jaoks. Selle määratlemiseks piisab, kui anda sellele nimi, mis algab tähemärkidega ## . Need protseduurid kustutatakse, kui server taaskäivitatakse või peatatakse või kui ühendus, mille kontekstis need loodi, suletakse.
Salvestatud protseduuride loomine, muutmine ja kustutamine
Loomine salvestatud protseduur hõlmab järgmiste ülesannete lahendamist:
- tüübi määratlemine salvestatud protseduur: ajutine või kohandatud. Lisaks saate luua oma süsteemi salvestatud protseduur, andes sellele nime koos eesliitega sp_ ja asetades selle süsteemi andmebaasi. Selline protseduur on saadaval kohaliku serveri mis tahes andmebaasi kontekstis;
- juurdepääsu planeerimine. Loomise ajal salvestatud protseduur pidage meeles, et sellel on andmebaasiobjektidele samad juurdepääsuõigused kui selle loonud kasutajal;
- määratlus salvestatud protseduuri parameetrid. Nagu enamikes programmeerimiskeeltes sisalduvad protseduurid, salvestatud protseduurid võivad olla sisend- ja väljundparameetrid;
- koodi arendamine salvestatud protseduur. Protseduurikood võib sisaldada mis tahes SQL-käskude jada, sealhulgas teiste kutsumist. salvestatud protseduurid.
Uue loomine ja olemasoleva muutmine salvestatud protseduur tehakse järgmise käsuga:
<определение_процедуры>::= (CREATE | ALTER ) protseduuri_nimi [;number] [(@parameetri_nimi andmetüüp ) [=vaikeseade] ][,...n] AS sql_lause [...n]
Mõelge selle käsu parameetritele.
Kasutades eesliiteid sp_ , # , ## , saab loodud protseduuri määratleda süsteemi või ajutise protseduurina. Nagu käsu süntaksist näha, ei ole lubatud määrata omaniku nime, kellele loodud protseduur kuuluma hakkab, samuti andmebaasi nime, kuhu see paigutada. Seega selleks, et mahutada loodud salvestatud protseduur konkreetses andmebaasis peate selle andmebaasi kontekstis käivitama käsu CREATE PROCEDURE. Kui käsitseda kehast salvestatud protseduur Lühendatud nimesid saab kasutada samas andmebaasis olevate objektide puhul, st ilma andmebaasi nime määramata. Kui soovite viidata teistes andmebaasides asuvatele objektidele, on vaja anda andmebaasi nimi.
Nimes olev number on identifitseerimisnumber salvestatud protseduur, mis määrab selle kordumatult protseduuride rühmas. Protseduuride haldamise mugavuse huvides loogiliselt sama tüüpi salvestatud protseduurid saab rühmitada, andes neile sama nime, kuid erinevad identifitseerimisnumbrid.
Sisend- ja väljundandmete edastamiseks loodud salvestatud protseduur saab kasutada parameetreid, mille nimed, nagu ka kohalike muutujate nimed, peavad algama sümboliga @. Üks salvestatud protseduur Saate määrata mitu valikut, eraldades need komadega. Protseduuri kehas ei tohi kasutada kohalikke muutujaid, mille nimed on samad, mis protseduuri parameetrite nimed.
Andmetüübi määramiseks, millele vastav salvestatud protseduuri parameeter, sobivad kõik SQL-i andmetüübid, sealhulgas kasutaja määratud andmetüübid. Andmetüüpi CURSOR saab aga kasutada ainult kui väljundparameeter salvestatud protseduur, st. märksõnaga VÄLJUND .
Märksõna OUTPUT olemasolu tähendab, et vastav parameeter on mõeldud andmete tagastamiseks salvestatud protseduur. See aga ei tähenda sugugi, et parameeter ei sobi väärtuste edastamiseks salvestatud protseduur. Märksõna OUTPUT määramine annab serverile käsu väljuda salvestatud protseduur määrake parameetri praegune väärtus kohalikule muutujale, mis määrati protseduuri kutsumisel parameetri väärtuseks. Pange tähele, et võtmesõna OUTPUT määramisel saab protseduuri kutsumisel vastava parameetri väärtust määrata ainult kohaliku muutuja abil. Tavaliste parameetrite jaoks lubatud avaldised või konstandid ei ole lubatud.
Märksõna VARYING kasutatakse koos
Salvestatud protseduur salvestatud protseduur) on nimega andmebaasiprogrammi objekt. SQL Serveril on mitut tüüpi salvestatud protseduure.
Süsteemi salvestatud protseduurid süsteemi salvestatud protseduur) tarnivad DBMS-i arendajad ja neid kasutatakse süsteemikataloogis toimingute tegemiseks või süsteemiteabe hankimiseks. Nende nimed algavad tavaliselt eesliitega "sp_". Igat tüüpi salvestatud protseduure käivitatakse käsuga EXECUTE, mida saab lühendada EXEC-iks. Näiteks salvestatud protseduur sp_helplogins, mis töötab ilma parameetriteta, genereerib kaks kontonimede aruannet (Inglise) sisselogimised) ja nende vastavad kasutajad igas andmebaasis (Inglise) kasutajad).
EXEC sp_helplogins;
Anda ettekujutus süsteemi salvestatud protseduuride abil tehtud toimingutest tabelis. 10.6 näitab mõningaid näiteid. Kokku on SQL Serveris üle tuhande süsteemi salvestatud protseduuri.
Tabel 10.6
SQL Serveri süsteemi salvestatud protseduuride näited
Kasutaja saab luua salvestatud protseduure kasutajate andmebaasides ja ajutiste objektide andmebaasis. Viimasel juhul oleks salvestatud protseduur ajaline. Nagu ajutiste tabelite puhul, peab ajutise salvestatud protseduuri nimi algama eesliitega "#", kui see on kohalik ajutine salvestatud protseduur, või "##", kui see on globaalne. Kohalikku ajutist protseduuri saab kasutada ainult ühenduse sees, milles see loodi, globaalset saab kasutada ka muude ühenduste sees.
Programmeeritavaid SQL Serveri objekte saab luua kas Transact-SQL tööriistade või koostude abil (Inglise) assembly) Microsoft .Net Frameworki CRL (Common Language Runtime) keskkonnas. Selles õpetuses käsitletakse ainult esimest meetodit.
Salvestatud protseduuride loomiseks kasutage käsku CREATE PROCEDURE (võib lühendada PROC), mille vorming on toodud allpool:
CREATE (PROC I PROCEDURE) protsessi_nimi [ ; number]
[(gparameter data_type )
["vaikimisi] |
[KOOS
[ REPLIKSEERIMISEKS ]
AS ([ BEGIN ] sql_lause [;] [ ...n ] [ END ] )
Kui salvestatud protseduur (või triger, funktsioon, vaade) luuakse valikuga Krüpteerimine, teisendatakse selle kood selliselt, et tekst muutub loetamatuks. Samal ajal, nagu on märgitud, on kasutatud algoritm üle võetud SQL Serveri varasematest versioonidest ja seda ei saa pidada usaldusväärseks kaitsealgoritmiks – on olemas utiliidid, mis võimaldavad kiiresti pöördkonversiooni sooritada.
Valik RECOMPILE määrab, et iga kord, kui protseduur kutsutakse, kompileerib süsteem teksti uuesti. Tavalisel juhul salvestatakse esimesel käivitamisel koostatud protseduur vahemällu, mis võimaldab jõudlust suurendada.
EXECUTE AS määrab turbekonteksti, milles protseduur viiakse läbi. Järgmisena üks väärtustest f CALLER | ISE | OMANIK | "kasutaja_nimi"). CALLER on vaikeväärtus ja tähendab, et kood käivitatakse seda moodulit kutsuva kasutaja turbekontekstis. Vastavalt sellele peavad kasutajal olema õigused mitte ainult programmeeritava objekti enda, vaid ka muude andmebaasiobjektide jaoks, mida see mõjutab. TEOSTAMINE ISE tähendab kasutaja konteksti kasutamist, mis loob või muudab programmeeritavat objekti. OWNER määrab, et kood käivitatakse protseduuri praeguse omaniku kontekstis. Kui sellele pole omanikku määratletud, siis eeldatakse selle skeemi omanikku, kuhu see kuulub. EXECUTE AS "kasutaja_nimi" võimaldab teil täpselt määrata kasutajanime (üksikute jutumärkidega).
Protseduuri jaoks saab määrata parameetreid. Need on kohalikud muutujad, mida kasutatakse väärtuste edastamiseks protseduurile. Kui parameeter on deklareeritud märksõnaga OUTPUT (või lühidalt OUT), on tegemist väljundiga: sellele protseduuris antud väärtust saab pärast protseduuri lõppu kasutada protseduuri kutsunud programm. Märksõna READONLY tähendab, et parameetri väärtust ei saa salvestatud protseduuri sees muuta.
Parameetritele saab määrata vaikeväärtused, mida kasutatakse juhul, kui parameetri väärtus ei ole protseduuri kutsumisel selgesõnaliselt määratud. Kaaluge näidet:
CREATE PROC surma (@a int, @b int=0,
©tulemus int OUTPUT) AS
SET @result=0a+0b
Oleme loonud kolme parameetriga protseduuri ja parameetri @b vaikeväärtus on =0 ja @result parameeter on väljundparameeter: selle kaudu tagastatakse väärtus kutsuvale programmile. Tehtud toimingud on üsna lihtsad – väljundparameeter saab kahe sisendi summa väärtuse.
SQL Server Management Studios töötades leiate loodud salvestatud protseduuri programmeeritavate andmebaasiobjektide jaotisest (Inglise) Programmeeritavus) salvestatud protseduuride jaotises (joonis 10.2).
Protseduuri kutsumisel saab sisendparameetritena kasutada nii muutujaid kui konstante. Vaatleme kahte näidet. Esimeses on protseduuri sisendparameetrid selgelt määratud konstantidega, kõnes on väljundparameetri jaoks määratud märksõna OUTPUT. Teises valikus kasutatakse muutuja väärtust esimese sisendparameetrina ja teine parameeter määratakse võtmesõna DEFAULT abil, mille jaoks tuleks kasutada vaikeväärtust:
Riis. 10.2.
DECLARE @koos int;
EXEC summa 10,5,@c VÄLJUND;
PRINT0c; - Kuvatakse 15
KUULUTAGE Gi int = 5;
- helistades kasutage vaikeväärtust
EXEC summa Gi,DEFAULT, 0s VÄLJUND;
PRINT0c; - Kuvatakse 5
Vaatleme nüüd näidet selle tagastuskoodi analüüsiga, millega protseduur lõpeb. Olgu vaja välja arvutada, mitu raamatut Bookli tabelis antud aastate vahemikus ilmus. Sel juhul, kui algusaasta on suurem kui lõpuaasta, tagastab protseduur "1" ja ei lähe arvesse, vastasel juhul loendame raamatute arvu ja tagastame 0:
CREATE PROC dbo.rownum(0FirsYear int, GLastYear int, 0result int OUTPUT) AS
KUI 0eelmine aasta>0eelmine aasta TAGASTUS 1
SET @result= (SELECT COUNT(*) FROM dbo.Bookl
KUS 0esimese aasta ja 0eelmise aasta vahel) ;
Mõelge selle protseduuri kutsumise variandile, kus tagastuskood salvestatakse täisarvulises muutujas 0ret, mille järel analüüsitakse selle väärtust (antud juhul on see 1). PRINT-lauses kasutatud funktsiooni CAST kasutatakse Gresi täisarvu muutuja väärtuse teisendamiseks stringitüübiks:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
KUI 0ret=l PRINT "Algusaasta on suurem kui lõppaasta"
PRINT "Raamatute arv" + CAST(Gres as varchar(20))
Salvestatud protseduurid ei saa mitte ainult lugeda andmeid tabelist, vaid ka muuta andmeid ja isegi luua tabeleid ja mitmeid muid andmebaasiobjekte.
Siiski ei saa salvestatud protseduurist luua skeeme, funktsioone, käivitajaid, protseduure ja vaateid.
Järgnev näide illustreerib nii neid võimalusi kui ka ajutiste objektide ulatusega seotud probleeme. Järgmine salvestatud protseduur kontrollib ajutise tabeli #Tab2 olemasolu; kui seda tabelit pole, loob see selle. Pärast seda sisestatakse tabelisse #Tab2 kahe veeru väärtused ja tabeli sisu kuvatakse SELECT-lausega:
CREATE PROC My_Procl (@id int, @name varchar(30))
KUI OBJECT_ID("tempdb.dbo.#Tab21) ON NULL
SISESTAGE dbo.#Tab2 (id, nimi)VÄÄRTUSED (0id,0nimi)
SELECT * FROM dbo. #Tab2 -#1
Enne salvestatud protseduuri esimest kõnet loome selles kasutatav ajutine tabel #Tab2. Pöörake tähelepanu EXEC-operaatorile. Eelmistes näidetes edastati parameetrid protseduurile "positsiooni järgi", kuid sel juhul kasutatakse parameetrite edastamiseks teistsugust vormingut - "nime järgi", parameetri nimi ja selle väärtus on selgelt näidatud:
CREATE TABLE dbo.#Tab2 (id int, nimi varchar(30));
EXEC My_Procl 0name="lvan", 0id=2;
SELECT * FROM dbo.#Tab2; -#2
Ülaltoodud näites täidetakse SELECT-lause kaks korda: esimest korda - protseduuri sees, teist korda - kutsuva koodi fragmendist (tähistatud kommentaariga "nr 2").
Enne teist protseduurikutset kustutame ajutise tabeli #Tab2. Seejärel luuakse salvestatud protseduurist samanimeline ajutine tabel:
TABEL DBO.#TAB2;
EXEC My_Procl 0name="Ivan", 0id=2;
SELECT * FROM dbo.#Tab2; -#2
Sel juhul kuvab andmeid ainult protseduuri sees olev SELECT-lause (koos "Xa 1" kommentaariga). SELECT "#2" täitmine toob kaasa vea, sest salvestatud protseduuris loodud ajutine tabel kustutatakse juba protseduuri naasmisel tempdb andmebaasist.
Saate loobuda salvestatud protseduurist, kasutades käsku DROP PROCEDURE. Selle vorming on näidatud allpool. Ühe lausega saate kustutada mitu salvestatud protseduuri, loetledes need komadega eraldatuna:
VÄLJASTA (PROC I MENETLUS) ( protseduur ) [
Näiteks eemaldame eelnevalt loodud summaprotseduuri:
DROP PROC summa;
Saate teha muudatusi olemasolevas protseduuris (tegelikult alistada), kasutades käsku ALTER PROCEDURE (lubada
lühend PROC). Välja arvatud märksõna ALTER, on avalduse vorming peaaegu sama, mis käsul CREATE PROCEDURE. Näiteks muudame protseduuri dbo. rownum, määrates selle käivituma omaniku turbekontekstis:
ALTER PROC dbo.rownum(SFirsYear int,
SLastYear int, Sresult int OUTPUT)
WITH EXECUTE AS Omanik - määrake valik
KUI 0eelmine aasta>0eelmine aasta TAGASI 1 MUUD ALGAS
SET 0result= (SELECT COUNT(*) FROM dbo.Bookl
KUS SFirsYear JA SLastYear vahel);
Mõnel juhul võib osutuda vajalikuks käsk dünaamiliselt genereerida ja see andmebaasiserveris käivitada. Seda ülesannet saab lahendada ka operaatori EXEC abil. Järgmises näites valitakse kirjed tabelist Bookl tingimusel, et atribuut Aasta on võrdne muutuja määratud väärtusega:
DEKLARERI 0a int = 2000;
EXEC("SELECT * FROM dbo.Bookl WHERE = " [e-postiga kaitstud]) ;
Dünaamiliselt genereeritud käskude täitmine loob eeldused arvutirünnakute, näiteks "SQL-i süsti" rakendamiseks. (Inglise) SQL-i süstimine). Rünnaku olemus seisneb selles, et kurjategija sisestab dünaamiliselt genereeritud päringusse omaenda SQL-koodi. Tavaliselt juhtub see siis, kui kasutaja sisestatud tulemustest võetakse tekstisisesed parameetrid.
Muudame veidi eelmist näidet:
DECLARE 0y varchar(100);
SET 0y="2OOO"; - selle saime kasutajalt
Kui eeldame, et saime kasutajalt SET-lauses määratud stringi väärtuse (ükskõik kuidas näiteks veebirakenduse kaudu), siis näide illustreerib meie koodi "tavalist" käitumist.
DECLARE 0y varchar(100);
SET 0y="2000; DELETE FROM dbo.Book2"; - süstimine
EXEC("SELECT * FROM dbo.Book2 WHERE="+0y);
Võimaluse korral on sellistel juhtudel soovitatav kasutada sp_executcsql süsteemi salvestatud protseduuri, mis võimaldab teil kontrollida parameetrite tüüpi, mis on üks SQL-i sisestamise takistusi. Selle vormingut üksikasjalikult kaalumata analüüsime näidet, mis sarnaneb varem esitatud näitega:
KÄIVITA sp_executesql
N"SELECT * FROM dbo.Bookl WHERE =0y",
See määrab täpselt päringus kasutatava parameetri tüübi ja SQL Server juhib seda täitmise ajal. Täht "N" enne jutumärke näitab, et see on Unicode'i literaalne konstant, nagu protseduur nõuab. Parameetrile saab määrata mitte ainult konstantse väärtuse, vaid ka mõne teise muutuja väärtuse.
Salvestatud protseduur on Transact-SQL-i avalduse eritüüp, mis on loodud SQL-keele ja protseduurilaiendite abil. Peamine erinevus paketi ja salvestatud protseduuri vahel on see, et viimane salvestatakse andmebaasiobjektina. Teisisõnu, salvestatud protseduurid salvestatakse serveri poolele, et parandada korduvate ülesannete jõudlust ja järjepidevust.
Andmebaasimootor toetab salvestatud protseduure ja süsteemiprotseduure. Salvestatud protseduurid luuakse samamoodi nagu kõik teised andmebaasiobjektid, s.t. kasutades DDL keelt. Süsteemi protseduurid neid pakub andmebaasimootor ja neid saab kasutada süsteemikataloogi teabele juurdepääsuks ja selle muutmiseks.
Salvestatud protseduuri loomisel saate määratleda valikulise parameetrite loendi. Seega võtab protseduur asjakohased argumendid iga kord, kui seda kutsutakse. Salvestatud protseduurid võivad tagastada väärtuse, mis sisaldab kasutaja määratud teavet või tõrke korral sobiva veateate.
Salvestatud protseduur kompileeritakse enne selle objektina andmebaasi salvestamist. Protseduuri eelkompileeritud vorm salvestatakse andmebaasi ja seda kasutatakse iga kord, kui see välja kutsutakse. See salvestatud protseduuride omadus annab olulise eelise (peaaegu kõikidel juhtudel) protseduuride uuesti kompileerimise kõrvaldamise ja vastava jõudluse parandamise. Sellel salvestatud protseduuride omadusel on positiivne mõju ka andmebaasisüsteemi ja rakenduste vahelisele andmevahetusele. Eelkõige võib mitme tuhande baidise salvestatud protseduuri kutsumine nõuda vähem kui 50 baiti. Kui mitu kasutajat täidavad korduvaid toiminguid, kasutades salvestatud protseduure, võib nende säästude kumulatiivne mõju olla märkimisväärne.
Salvestatud protseduure saab kasutada ka järgmistel eesmärkidel:
andmebaasi tabelitega toimingute logilogi loomiseks.
Salvestatud protseduuride kasutamine tagab turbekontrolli taseme, mis ületab turvalisuse, mida pakub GRANT ja REVOKE avaldused, mis annavad kasutajatele erinevad juurdepääsuõigused. See on võimalik, kuna volitus salvestatud protseduuri täitmiseks ei sõltu volitusest muuta salvestatud protseduuris sisalduvaid objekte, nagu on kirjeldatud järgmises jaotises.
Salvestatud protseduurid, mis logivad tabelitesse kirjutamist ja/või lugemist, pakuvad andmebaasile täiendavat turvalisust. Selliseid protseduure kasutades saab andmebaasi administraator jälgida kasutajate või rakenduste poolt andmebaasis tehtud muudatusi.
Salvestatud protseduuride loomine ja teostamine
Salvestatud protseduurid luuakse lause abil LOO PROTSEDUURI, millel on järgmine süntaks:
CREATE PROC proc_name [((@param1) type1 [ VARYING] [= vaike1] )] (, …) AS partii | EXTERNAL NAME meetodi_nimi Süntaksi kokkulepped
Parameeter schema_name määrab skeemi nime, mille on määranud loodud salvestatud protseduuri omanik. Parameeter proc_name määrab salvestatud protseduuri nime. Parameeter @param1 on protseduuri parameeter (formaalne argument), mille andmetüüp on määratud parameetriga type1. Protseduuri parameetrid on protseduuri sees kohalikud, nii nagu kohalikud muutujad on lokaalsed paketi sees. Protseduuri parameetrid on väärtused, mille helistaja edastab protseduurile selles kasutamiseks. Parameeter default1 määrab vastava protseduuriparameetri vaikeväärtuse. (Vaikeväärtus võib olla ka NULL.)
VÄLJUNDI valik määrab, et protseduuri parameeter on tagastatav ja seda saab kasutada väärtuse tagastamiseks salvestatud protseduurist kutsuvasse protseduuri või süsteemi.
Nagu varem mainitud, salvestatakse protseduuri eelkompileeritud vorm andmebaasi ja seda kasutatakse iga kord, kui seda kutsutakse. Kui mingil põhjusel on vaja salvestatud protseduur kompileerida iga kord, kui see välja kutsutakse, kasutatakse protseduuri deklaratsioonis valik KOMPILISEERIMISEGA. Suvandi WITH RECOMPILE kasutamine tühistab salvestatud protseduuride ühe olulisema eelise: jõudluse paranemise tänu ühele kompileerimisele. Seetõttu tuleks suvandit WITH RECOMPILE kasutada ainult siis, kui salvestatud protseduuri kasutatavates andmebaasiobjektides tehakse sageli muudatusi.
TEOSTA AS pakkumine määratleb turbekonteksti, milles salvestatud protseduur tuleb pärast selle väljakutsumist käivitada. Selle konteksti määramisega saab andmebaasimootor juhtida kasutajakontode valikut selle salvestatud protseduuriga viidatud objektide juurdepääsuõiguste kontrollimiseks.
Vaikimisi saavad lauset CREATE PROCEDURE kasutada ainult sysadmini fikseeritud serverirolli ja db_owner või db_ddladmin fikseeritud andmebaasirolli liikmed. Nende rollide liikmed saavad aga selle õiguse teistele kasutajatele antud juhiseid kasutades määrata TOETUSE LOOMISE PROTSESS.
Allolev näide näitab, kuidas luua projektitabeliga töötamiseks lihtne salvestatud protseduur:
KASUTAGE SampleDb; MINGE PROTSEDUURI LOOMINE Suurenda eelarvet (@percent INT=5) AS VÄRSKENDUS Projekti SET Eelarve = Eelarve + Eelarve * @protsent/100;
Nagu varem mainitud, kasutatakse kahe paketi eraldamiseks GO juhendamine. CREATE PROCEDURE lauset ei saa kombineerida sama partii teiste Transact-SQL-i lausetega. Salvestatud protseduur IncreaseBudget suurendab kõigi projektide eelarveid teatud protsendi võrra, mis on määratud parameetriga @percent. Protseduur määratleb ka protsendi vaikeväärtuse (5), mida rakendatakse, kui seda argumenti protseduuri täitmise ajal ei esine.
Salvestatud protseduurid pääsevad juurde tabelitele, mida pole olemas. See atribuut võimaldab teil protseduuri koodi siluda ilma vastavaid tabeleid loomata ja isegi sihtserveriga ühendust loomata.
Erinevalt põhilistest salvestatud protseduuridest, mis on alati salvestatud praeguses andmebaasis, on võimalik luua ajutisi salvestatud protseduure, mis paigutatakse alati ajutisse süsteemi andmebaasi tempdb. Ajutiste salvestatud protseduuride loomise üheks põhjuseks võib olla teatud lausete rühma korduva täitmise vältimine andmebaasiga ühenduse loomisel. Saate luua kohalikke või globaalseid ajutisi protseduure. Selleks määratakse kohaliku protseduuri nimi ühe # märgiga (#proc_name) ja globaalse protseduuri nimi topeltmärgiga (##proc_name).
Kohalikku ajutiselt salvestatud protseduuri saab käivitada ainult selle loonud kasutaja ja ainult ühenduse loomise ajal andmebaasiga, milles see loodi. Globaalset ajutist protseduuri saavad teostada kõik kasutajad, kuid ainult seni, kuni viimane ühendus, millel see töötab (tavaliselt protseduuri looja ühendus), on lõpetatud.
Salvestatud protseduuri elutsükkel koosneb kahest faasist: selle loomine ja teostamine. Iga protseduur luuakse üks kord ja viiakse läbi mitu korda. Salvestatud protseduuri teostab TEOSTA avaldused kasutaja, kes omab protseduuri või omab protseduurile juurdepääsuõigust. Avaldusel EXECUTE on järgmine süntaks:
[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] väärtus | [@parameter1=] @muutuja ] | VAKEED).. Süntaksi kokkulepped
Välja arvatud parameeter return_status, on kõigil lause EXECUTE parameetritel sama tõeväärtus kui samanimelise lause CREATE PROCEDURE parameetritel. Parameeter return_status määrab täisarvulise muutuja, mis salvestab protseduuri tagastusoleku. Väärtuse saab määrata parameetrile, kasutades kas konstanti (väärtus) või kohalikku muutujat (@muutuja). Nimega parameetrite väärtuste järjekord ei ole oluline, kuid nimetamata parameetrite väärtused tuleb esitada selles järjekorras, milles need on määratletud lauses CREATE PROCEDURE.
DEFAULT klausel pakub protseduuri definitsioonis määratud protseduuriparameetri vaikeväärtusi. Kui protseduur ootab väärtust parameetrile, mille jaoks pole vaikeväärtust määratletud ja parameeter puudub, või on määratud DEFAULT märksõna, ilmneb tõrge.
Kui lause EXECUTE on paketi esimene lause, võib märksõna EXECUTE välja jätta. Siiski on kindlam lisada see sõna igasse pakendisse. Lause EXECUTE kasutamine on näidatud allolevas näites:
KASUTAGE SampleDb; TÄITDA Suurenda eelarvet 10;
Selle näite lause EXECUTE käivitab IncreaseBudget salvestatud protseduuri, mis suurendab kõigi projektide eelarvet 10%.
Järgmine näide näitab, kuidas luua salvestatud protseduur andmete töötlemiseks tabelites Employee ja Works_on.
Näites olev protseduur ModifyEmpId illustreerib salvestatud protseduuride kasutamist viiteterviklikkuse protsessi osana (antud juhul tabelite Employee ja Works_on vahel). Sellist salvestatud protseduuri saab kasutada päästikdefinitsiooni sees, mis tegelikult tagab viiteterviklikkuse.
Järgmine näide näitab OUTPUT-klausli kasutamist salvestatud protseduuris:
Seda salvestatud protseduuri saab käivitada järgmiste avalduste abil:
DEKLARERI @quantityDeleteEmployee INT; KÄIVITA Kustuta Töötaja @empId=18316, @ [e-postiga kaitstud] VÄLJUND; PRINT N"Töötajad kustutatud: " + convert(nvarchar(30), @quantityDeleteEmployee);
See protseduur loendab projektide arvu, millega töötaja personalinumbriga @empId töötab, ja määrab saadud väärtuse parameetrile ©counter. Pärast kõigi antud personalinumbri ridade kustutamist tabelitest Töötaja ja Töötatavad, määratakse arvutatud väärtus muutujale @quantityDeleteEmployee.
Parameetri väärtus tagastatakse kutsumisprotseduurile ainult siis, kui on määratud suvand OUTPUT. Ülaltoodud näites edastab protseduur DeleteEmployee kutsumisprotseduurile parameetri @counter, nii et salvestatud protseduur tagastab väärtuse süsteemile. Seetõttu tuleb @counter parameeter määrata nii protseduuri deklareerimisel suvandis OUTPUT kui ka selle väljakutsumise lauses EXECUTE.
WITH RESULTS SETS lause EXECUTE
SQL Server 2012-s trükitakse käsk EXECUTE WITH RESULTS SETS klausel A, mis teatud tingimustel võib muuta salvestatud protseduuri tulemuskomplekti kuju.
Seda lauset aitavad selgitada järgmised kaks näidet. Esimene näide on sissejuhatav näide, mis näitab, milline võib tulemus välja näha, kui klausel WITH RESULTS SETS on välja jäetud:
Protseduur EmployeesInDept on lihtne protseduur, mis kuvab kõigi konkreetses osakonnas töötavate töötajate personalinumbrid ja perekonnanimed. Osakonna number on protseduuri parameeter ja see tuleb protseduurile helistades täpsustada. Selle protseduuri teostamisel väljastatakse kahe veeruga tabel, mille pealkirjad ühtivad andmebaasi tabeli vastavate veergude nimetustega, st. id ja perekonnanimi. Tulemuste veeru päiste (ja ka nende andmetüübi) muutmiseks kasutab SQL Server 2012 uut klauslit WITH RESULTS SETS. Selle klausli rakendamine on näidatud allolevas näites:
KASUTAGE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS (( INT NOT NULL, [Perenimi] CHAR(20) NOT NULL));
Sel viisil kutsutud salvestatud protseduuri täitmise tulemus on järgmine:
Nagu näete, võimaldab salvestatud protseduuri käitamine, kasutades EXECUTE-lause WITH RESULT SETS klauslit, muuta protseduuri poolt loodud tulemikomplekti veergude nimesid ja andmetüüpe. Seega pakub see uus funktsioon suuremat paindlikkust salvestatud protseduuride täitmisel ja nende tulemuste uude tabelisse paigutamisel.
Salvestatud protseduuride struktuuri muutmine
Seda väidet toetab ka andmebaasimootor MUUDA PROTSEDUURI salvestatud protseduuride struktuuri muutmiseks. Lauset ALTER PROCEDURE kasutatakse tavaliselt protseduuris Transact-SQL-i lausete muutmiseks. Kõik lause ALTER PROCEDURE parameetrid omavad sama tähendust kui samanimelise lause CREATE PROCEDURE parameetrid. Selle avalduse kasutamise peamine eesmärk on vältida olemasolevate salvestatud protseduuride õiguste alistamist.
Andmebaasimootor toetab CURSOR andmetüüp. Seda andmetüüpi kasutatakse kursorite deklareerimiseks salvestatud protseduurides. Kursor on programmeerimiskonstruktsioon, mida kasutatakse päringu tulemuste (tavaliselt ridade komplekti) salvestamiseks ja kasutajatel seda tulemust ridade kaupa kuvamiseks.
Ühe või rühma salvestatud protseduuride eemaldamiseks kasutage LOPPEMISE PROTSEDUURI avaldus. Ainult salvestatud protseduuri omanik või fikseeritud rollide db_owner ja sysadmin liikmed saavad salvestatud protseduuri kustutada.
Salvestatud protseduurid ja levinud keele käitusaeg
SQL Server toetab Common Language Runtime (CLR), mis võimaldab arendada erinevaid andmebaasiobjekte (salvestatud protseduurid, kasutaja määratud funktsioonid, päästikud, kasutaja määratud agregaadid ja kasutaja määratud andmetüübid), kasutades C# ja Visual Basic. Ühise keele käitusaeg võimaldab ka neid objekte käivitada ühise käitusaja süsteemi abil.
Tavakeele käitusaeg on valiku kaudu lubatud ja keelatud clr_enabled süsteemi protseduur sp_configure, mis käivitatakse täitmiseks käsuga REKONFIGURERI. Järgmine näide näitab, kuidas saate sp_configure süsteemiprotseduuri abil lubada ühise keele käitusaja:
KASUTAGE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE
Protseduuri loomine, kompileerimine ja salvestamine CLR-i abil nõuab järgmist toimingute jada loetletud järjekorras.
Looge C#-s või Visual Basicus salvestatud protseduur ja seejärel kompileerige see sobiva kompilaatori abil.
Kasutades juhiseid LOO KOOSTAMINE, looge sobiv käivitatav fail.
Käivitage protseduur, kasutades käsku EXECUTE.
Alloleval joonisel on kujutatud eelnevalt kirjeldatud sammude graafiline diagramm. Allpool on selle protsessi üksikasjalikum kirjeldus.
Kõigepealt loo soovitud programm arenduskeskkonnas, näiteks Visual Studio. Kompileerige valmis programm C# või Visual Basic kompilaatori abil objektikoodiks. See kood on salvestatud dünaamilise lingi teegi (.dll) faili, mis toimib CREATE ASSEMBLY lause allikana, mis loob vahepealse käivitatava koodi. Järgmisena väljastage käivitatava koodi andmebaasiobjektina salvestamiseks käsk CREATE PROCEDURE. Lõpuks käivitage protseduur tuttava EXECUTE-lause abil.
Allolev näide näitab salvestatud protseduuri lähtekoodi C#-s:
System.Data.SqlClient kasutamine; kasutades Microsoft.SqlServer.Server; public partial class StoredProcedures ( public static int CountEmployees() ( int read; SqlConnection ühendus = new SqlConnection("Context Connection=true"); ühendus.Open(); SqlCommand cmd = ühendus.CreateCommand(); cmd.CommandText = "select count(*) kui "Töötajate arv" " + "Töötajalt"; read = (int)cmd.ExecuteScalar(); connection.Close(); tagasta read; ) )
See protseduur rakendab päringu tabelis Töötaja ridade loendamiseks. Programmi alguses olevate direktiivide kasutamine määrake selle täitmiseks vajalikud nimeruumid. Nende direktiivide kasutamine võimaldab määrata klassinimesid lähtekoodis ilma vastavaid nimeruume selgesõnaliselt määramata. Järgmisena määratletakse klass StoredProcedures, mille jaoks SqlProcedure atribuut, mis teavitab kompilaatorit, et see klass on salvestatud protseduur. Klassi koodi sees on defineeritud meetod CountEmployees(). Ühendus andmebaasisüsteemiga luuakse klassi eksemplari kaudu SqlConnection. Ühenduse avamiseks kasutatakse selle eksemplari meetodit Open(). A CreateCommand() meetod võimaldab teil pääseda juurde klassi eksemplarile SqlCommnd, millele edastatakse soovitud SQL-käsk.
Järgmises koodilõigul:
Cmd.CommandText = "valige arv(*) kui "Töötajate arv" " + "Töötajalt";
kasutab SELECT-lauset tabeli Töötaja ridade loendamiseks ja tulemuse kuvamiseks. Käsu tekst määratakse, määrates cmd-muutuja atribuudi CommandText meetodi CreateCommand() poolt tagastatud eksemplarile. Järgmine on nn ExecuteScalar() meetod SqlCommandi eksemplar. See meetod tagastab skalaarväärtuse, mis teisendatakse täisarvuliseks andmetüübiks int ja määratakse ridade muutujale.
Nüüd saate selle koodi koostada Visual Studio abil. Lisasin selle klassi projekti nimega CLRStoredProcedures, nii et Visual Studio kompileerib samanimelise koostu laiendiga *.dll. Allolev näide näitab järgmist sammu salvestatud protseduuri loomisel: käivitatava koodi loomine. Enne selles näites oleva koodi käivitamist peate teadma kompileeritud .dll-faili asukohta (tavaliselt asub see projekti silumiskaustas).
KASUTAGE SampleDb; MINGE KOOSTAMISE CLRStoredProcedures LOOMINE ALAST "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" KOOS PERMISSION_SET = OHUTU
Lause CREATE ASSEMBLY võtab sisendiks hallatud koodi ja loob sobiva objekti, mille jaoks saate luua ühise keele käitusaja (CLR) salvestatud protseduure, kasutaja määratletud funktsioone ja käivitajaid. Sellel juhisel on järgmine süntaks:
CREATE ASSEMBLY koostu_nimi [ AUTORISEERIMISE omaniku_nimi ] FROM (dll_fail) Süntaksi kokkulepped
Parameeter assembly_name määrab koostu nime. Valikuline klausel AUTORISEERIMINE määrab rolli nime selle koostu omanikuna. Klausel FROM määrab tee, kus laaditav koost asub.
PERMISSION_SET klausliga on CREATE ASSEMBLY lause väga oluline klausel ja seda tuleks alati täpsustada. See määratleb komplekteerimiskoodile antud juurdepääsuõiguste kogumi. SAFE õiguste komplekt on kõige piiravam. Nende õigustega koostukood ei pääse juurde välistele süsteemiressurssidele, näiteks failidele. Õiguste komplekt EXTERNAL_ACCESS võimaldab komplekteerimiskoodil juurdepääsu teatud välistele süsteemiressurssidele, samas kui UNSAFE õiguste komplekt pakub piiramatut juurdepääsu ressurssidele nii andmebaasisüsteemi sees kui ka väljaspool.
Koostekoodi teabe salvestamiseks peab kasutaja saama väljastada avalduse CREATE ASSEMBLY. Koost kuulub kasutajale (või rollile), kes käsku täidab. Koostu omanikku saate muuta, kasutades avalduse CREATE SCHEMA klauslit AUTORISEERIMINE.
Andmebaasimootor toetab ka avaldusi ALTER ASSEMBLY ja DROP ASSEMBLY. ALTER Assembly avaldus kasutatakse koostu värskendamiseks uusimale versioonile. See juhend lisab või eemaldab ka vastava koostuga seotud failid. Drop Assembly avaldus eemaldab praegusest andmebaasist määratud koostu ja kõik sellega seotud failid.
Järgmine näide näitab, kuidas luua varem juurutatud hallatud koodi põhjal salvestatud protseduur:
KASUTAGE SampleDb; MINGE PROTSEDUURI LOOMINE CountEmployees VÄLISnimena CLRStoredProcedures.StoredProcedures.CountTemployees
Näites olev lause CREATE PROCEDURE erineb varasemate näidete samast lausest selle poolest, et see sisaldab EXTERNAL NAME parameeter. See suvand määrab, et koodi genereerib CLR. Selles lauses olev nimi koosneb kolmest osast:
koostu_nimi.klassi_nimi.meetodi_nimi
koostu_nimi - määrab koostu nime;
klassi_nimi - määrab üldklassi nime;
meetodi_nimi – valikuline osa, määrab klassi sees määratud meetodi nime.
Protseduuri CountEmployees täitmine on näidatud allolevas näites:
KASUTAGE SampleDb; DECLARE @count INT EXECUTE @count = Töötajate arv PRINT @count -- Tagastab 7
PRINT-lause tagastab tabelis Töötaja praeguse ridade arvu.
Microsoft SQL Serveris oma algoritmide juurutamiseks ja automatiseerimiseks ( arvutused) saate kasutada salvestatud protseduure, nii et täna räägime nende loomisest, muutmisest ja kustutamisest.
Kuid kõigepealt natuke teooriat, et saaksite aru, mis on salvestatud protseduurid ja milleks need T-SQL-is on.
Märge! Algajatele programmeerijatele soovitan T-SQL-i teemal järgmisi kasulikke materjale:
- T-SQL keele täpsemaks uurimiseks soovitan lugeda ka raamatut - The Way of the T-SQL Programmer. Transact-SQL-i õpetus.
Mis on T-SQL-is salvestatud protseduurid?
Salvestatud protseduurid- Need on andmebaasiobjektid, millesse algoritm on manustatud SQL-i käskude komplektina. Teisisõnu võime öelda, et salvestatud protseduurid on andmebaasis olevad programmid. Salvestatud protseduure kasutatakse korduvkasutatava koodi salvestamiseks serverisse, näiteks kirjutasite algoritmi, järjestikuse arvutuse või mitmeastmelise SQL-lause ning selleks, et mitte iga kord kõiki selles algoritmis sisalduvaid juhiseid täita, saate korraldada seda salvestatud protseduurina. Sel juhul kompileerib server SQL-protseduuri loomisel koodi ja iga kord, kui seda protseduuri käivitate, ei kompileeri SQL-server seda enam uuesti.
Salvestatud protseduuri käivitamiseks SQL Serveris on vaja selle nime ette kirjutada käsk EXECUTE, samuti on võimalik see EXEC käsk kirjutada stenogrammi. Salvestatud protseduuri kutsumine näiteks SELECT-lauses, kuna funktsioon enam ei tööta, s.t. protseduurid viiakse läbi eraldi.
Salvestatud protseduurides on erinevalt funktsioonidest juba võimalik teha andmete muutmise toiminguid nagu: UNSERT, UPDATE, DELETE. Samuti saab protseduurides kasutada peaaegu igat tüüpi SQL-lauset, näiteks tabelite loomiseks CREATE TABLE või EXECUTE, s.t. muude protseduuride kutsumine. Erandiks on mitut tüüpi käsud, näiteks: funktsioonide, vaadete, trigerite loomine või muutmine, skeemide loomine ja mõned muud sarnased juhised, näiteks pole ka salvestatud protseduuris võimalik vahetada andmebaasiühenduse konteksti (USE).
Salvestatud protseduuril võivad olla sisend- ja väljundparameetrid, see võib tagastada tabeliandmeid, ei pruugi midagi tagastada, vaid täidab ainult selles sisalduvaid juhiseid.
Väga kasulikud on salvestatud protseduurid, mis aitavad meil paljusid toiminguid automatiseerida või lihtsustada, näiteks on pidevalt vaja pivot tabelite abil genereerida erinevaid keerulisi analüütilisi aruandeid, s.t. PIVOT-operaator. Selle operaatoriga päringute moodustamise lihtsustamiseks ( nagu teate, on PIVOT-i süntaks üsna keeruline), saate kirjutada protseduuri, mis genereerib teie jaoks dünaamiliselt kokkuvõtlikke aruandeid, näiteks materjalis "Dynamic PIVOT in T-SQL" on toodud näide selle funktsiooni rakendamisest salvestatud protseduuri kujul.
Näited Microsoft SQL Serveris salvestatud protseduuridega töötamise kohta
Algandmed näideteks
Kõik alltoodud näited käivitatakse rakenduses Microsoft SQL Server 2016 Express. Selleks, et näidata, kuidas salvestatud protseduurid reaalsete andmetega töötavad, vajame neid andmeid, loome need. Näiteks loome testtabeli ja lisame sellele mõned kirjed, oletame, et see on tabel, mis sisaldab toodete loendit nende hindadega.
Tabeli loomise avaldus CREATE TABLE TestTabel( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Andmelause lisamine INSERT INTO TestTable(CategoryId, Product Name, Price) VÄÄRTUSED (1 , "Hiir", 100), (1, "Klaviatuur", 200), (2, "Telefon", 400) GO -- SELECT * FROM testtabeli päring
Andmed on olemas, nüüd liigume salvestatud protseduuride loomise juurde.
Salvestatud protseduuri loomine T-SQL-is – CREATE PROCEDURE avaldus
Salvestatud protseduurid luuakse lause abil LOO PROTSEDUURI, pärast seda juhist tuleb kirjutada oma protseduuri nimi, seejärel määrata vajadusel sisend- ja väljundparameetrid sulgudes. Peale seda kirjutad märksõna AS ja avad juhiste ploki märksõnaga BEGIN, sulged selle ploki sõnaga END. Selle ploki sisse kirjutate kõik juhised, mis rakendavad teie algoritmi või mingit järjestikust arvutust, teisisõnu programmeerite T-SQL-is.
Näiteks kirjutame salvestatud protseduuri, mis lisab uue kirje, st. uus üksus meie testgraafikus. Selleks defineerime kolm sisendparameetrit: @CategoryId - tootekategooria identifikaator, @ProductName - toote nimi ja @Price - toote hind, see parameeter on meie jaoks valikuline, s.t. seda ei saa protseduurile edasi anda ( näiteks hinda me veel ei tea), määrame selle definitsioonis vaikeväärtuse. Need parameetrid on protseduuri kehas, st. plokis BEGIN…END saab kasutada samamoodi nagu tavalisi muutujaid ( nagu teate, tähistatakse muutujaid @-märgiga). Kui teil on vaja määrata väljundparameetrid, siis pärast parameetri nime määrake märksõna OUTPUT ( või lühendatult OUT).
Plokis BEGIN…END kirjutame andmete lisamise juhise ja ka protseduuri lõpus SELECT-lause, et salvestatud protseduur tagastaks tabelina toodud andmed määratud kategooriasse kuuluvate toodete kohta, võttes arvesse uut, äsja lisatud toode. Ka selles salvestatud protseduuris lisasin sissetuleva parameetri töötlemise ehk lisatühikute eemaldamise tekstistringi algusest ja lõpust, et vältida olukordi, kus kogemata sisestati mitu tühikut.
Siin on selle protseduuri kood Kommenteerisin ka seda).
Loo protseduur CREATE PROCEDURE TestProcedure (--sissetulevad parameetrid @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN -Juhised, mis rakendavad teie algoritmi -Sissetulevate parameetrite töötlemine -Liigsete tühikute eemaldamine algusest ja tekstistringi lõpus SET @Tootenimi = LTRIM(RTRIM(@Tootenimi)); --Lisa uus kirje INSERT INTO TestTable(CategoryId, Product Name, Price) VÄÄRTUSED (@CategoryId, @ProductName, @Price) --Tagasta andmed SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Salvestatud protseduuri käivitamine T-SQL-is – EXECUTE käsk
Nagu ma juba märkisin, saate käivitada salvestatud protseduuri, kasutades käsku EXECUTE või EXEC. Sissetulevad parameetrid edastatakse protseduuridele, lihtsalt loetledes need üles ja sisestades protseduuri nime järele vastavad väärtused ( väljundparameetrite jaoks tuleb määrata ka käsk OUTPUT). Parameetrite nimetus ei pruugi aga olla määratud, kuid sel juhul tuleb järgida väärtuste täpsustamise järjekorda, s.t. määrake väärtused sisendparameetrite määratlemise järjekorras ( see kehtib ka väljundparameetrite kohta).
Vaikeväärtustega parameetreid ei pruugita määrata, need on nn valikulised parameetrid.
Siin on mõned erinevad, kuid samaväärsed viisid salvestatud protseduuride, eriti meie testprotseduuri, käitamiseks.
1. Kutsuge protseduur välja hinda määramata EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Testi toode 1" --2. Nimetame määratud hinnaga protseduuri EXEC TestProcedure @CategoryId = 1, @ProductName = "Testitoode 2", @Price = 300 --3. Kutsume protseduuri välja ilma EXEC parameetrite nime määramata TestProcedure 1, "Test item 3", 400
Salvestatud protseduuri muutmine T-SQL-iks – ALTER PROCEDURE avaldus
Protseduuri algoritmi saate teha juhiste järgi MUUDA PROTSEDUURI. Teisisõnu, juba olemasoleva protseduuri muutmiseks tuleb CREATE PROCEDURE asemel kirjutada ALTER PROCEDURE ja muuta kõike muud vastavalt vajadusele.
Oletame, et peame oma testiprotseduuris muudatusi tegema, ütleme näiteks @Price parameetrit, st. hind, muudame selle kohustuslikuks, selleks eemaldame vaikeväärtuse ja kujutame ette, et me ei pea enam saadud andmekogumit hankima, selleks eemaldame salvestatud protseduurist lihtsalt SELECT-lause.
Muutke protseduuri ALTER PROCEDURE TestProcedure (--sissetulevad parameetrid @CategoryId INT, @Tootenimi VARCHAR(100), @Price MONEY) AS BEGIN - Juhised, mis rakendavad teie algoritmi - Sissetulevate parameetrite töötlemine tekstiridadest SET @Tootenimi = LTRIM(RTRIM(@Tootenimi)); -- Lisa uus kirje INSERT INTO TestTable(CategoryId, Product Name, Price) VÄÄRTUSED (@CategoryId, @ProductName, @Price) END GO
T-SQL-is salvestatud protseduuri kustutamine – DROP PROCEDURE avaldus
Vajadusel saate salvestatud protseduuri kustutada, seda tehakse avalduse abil KUKKUMISE MENETLUS.
Näiteks kustutame enda loodud testiprotseduuri.
KUKKUMISE PROTSEDUUR Katseprotseduur
Salvestatud protseduuride kustutamisel tasub meeles pidada, et kui protseduurile viitavad teised protseduurid või SQL-laused, siis pärast selle kustutamist ebaõnnestuvad need veaga, kuna protseduuri, millele need viitavad, enam ei eksisteeri.
Mul on kõik olemas, loodan, et materjal oli teile huvitav ja kasulik, hüvasti!
