Ne konsiderojmë një situatë ku procedurat e ruajtura mund të degradojnë performancën e pyetjes.
Gjatë përpilimit të procedurave të ruajtura në MS SQL Server 2000, procedurat e ruajtura vendosen në cache-in e procedurës, gjë që mund të ndihmojë në rritjen e performancës kur ato ekzekutohen duke eliminuar nevojën për analizë, optimizim dhe përpilim të kodit të procedurës së ruajtur.
Nga ana tjetër, ka kurthe në ruajtjen e kodit të përpiluar të një procedure të ruajtur që mund të ketë efektin e kundërt.
Fakti është se gjatë përpilimit të një procedure të ruajtur, përpilohet plani i ekzekutimit të atyre deklaratave që përbëjnë kodin e procedurës, përkatësisht, nëse procedura e ruajtur e përpiluar është memorizuar, atëherë plani i ekzekutimit të saj gjithashtu ruhet në memorie, dhe për këtë arsye procedura e ruajtur nuk do të të jetë i optimizuar për një situatë specifike dhe parametra të pyetjes.
Do të bëjë një eksperiment të vogël për ta demonstruar këtë.
HAPI 1. Krijimi i bazës së të dhënave.
Për eksperimentin, le të krijojmë një bazë të dhënash të veçantë.
KRIJO BAZA TË TË DHËNAVE test_sp_perf
AKTIV (NAME="Test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
LOG ON (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
HAPI 2. Krijimi i një tabele.
KRIJO TABELA sp_perf_test(kolona 1 int, kolona 2 karakter (5000))
HAPI 3. Plotësimi i tabelës me vargje provë. Rreshtat dublikatë janë shtuar qëllimisht në tabelë. 10000 rreshta me numra nga 1 deri në 10000 dhe 10000 rreshta me numra 50000.
DEKLARONI @i int
SET @i=1
Nderkohe une<10000)
FILLOJ
INSERT INTO sp_perf_test(kolona 1, kolona 2) VALUES(@i,"Test string #"+CAST(@i si char(8)))
INSERT INTO sp_perf_test(kolona 1, kolona 2) VALUES(50000,"Teststring #"+CAST(@i si char(8)))
SET @i= @i+1
FUND
ZGJIDH COUNT(*) NGA sp_perf_test
SHKO
HAPI 4. Krijo një indeks jo të grupuar. Meqenëse plani i ekzekutimit është memorizuar me procedurën, indeksi do të përdoret në të njëjtën mënyrë për të gjitha thirrjet.
KRIJO INDEKS TË JO GJENDUR CL_perf_test ON sp_perf_test(kolona1)
SHKO
HAPI 5. Krijo një procedurë të ruajtur. Procedura thjesht ekzekuton deklaratën SELECT me kushtin.
KRIJO PROC proc1 (@paramint)
AS
ZGJIDH kolonën1, kolonën 2 NGA sp_perf_test WHERE [email i mbrojtur]
SHKO
HAPI 6. Drejtoni një procedurë të ruajtur. Kur fillon një procedurë vulnerabël, përdoret në mënyrë specifike një parametër selektiv. Si rezultat i procedurës, marrim 1 rresht. Plani i ekzekutimit tregon përdorimin e një indeksi jo të grupuar, si pyetja është selektive dhe kjo është mënyra më e mirë për të nxjerrë vargun. Një procedurë e optimizuar për marrjen e një rreshti të vetëm ruhet në cache-in e procedurës.
EXEC proc1 1234
SHKO
HAPI 7. Ekzekutimi i një procedure të ruajtur me një parametër jo selektiv. Si parametër përdoret vlera 50000. Rreshtat me një vlerë të tillë të kolonës së parë prej rreth 10000, përkatësisht, duke përdorur një indeks jo të grupuar dhe operacionin e kërkimit të faqerojtësve janë joefikas, por meqenëse kodi i përpiluar me planin e ekzekutimit ruhet në procedurën procedurale. cache, do të përdoret. Plani i ekzekutimit e tregon këtë, si dhe faktin që një operacion kërkimi i faqerojtësve është kryer për 9999 rreshta.
EXEC proc1 50000
SHKO
HAPI 8. Kryerja e një përzgjedhjeje rreshtash me fushën e parë të barabartë me 50000. Kur ekzekutohet një pyetje e veçantë, pyetja optimizohet dhe përpilohet me një vlerë specifike të kolonës së parë. Si rezultat, optimizuesi i pyetjeve përcakton që fusha është dublikuar shumë herë dhe vendos të përdorë operacionin e skanimit të tabelës, i cili në këtë rast është shumë më efikas sesa përdorimi i një indeksi jo të grupuar.
ZGJIDH kolonën1, kolonën 2 FROM sp_perf_test WHERE kolona1=50000
SHKO
Kështu, mund të konkludojmë se përdorimi i procedurave të ruajtura mund të mos përmirësojë gjithmonë performancën e pyetjes. Duhet të jeni shumë të kujdesshëm për ato procedura të ruajtura që funksionojnë me rezultate me një numër të ndryshueshëm rreshtash dhe përdorin plane të ndryshme ekzekutimi.
Ju mund të përdorni skriptin për të përsëritur eksperimentin në serverin tuaj MS SQL.
Përfshini në procedurat tuaja linjën - SET NOCOUNT ON:
Me çdo deklaratë DML, serveri SQL na kthen me kujdes një mesazh që përmban numrin e regjistrimeve të përpunuara. Ky informacion mund të jetë i dobishëm për ne gjatë korrigjimit të kodit, por pas kësaj do të jetë plotësisht i padobishëm. Duke shkruar SET NOCOUNT ON, ne e çaktivizojmë këtë veçori. Për procedurat e ruajtura që përmbajnë shprehje të shumëfishta ose sythe, ky veprim mund të japë një rritje të konsiderueshme të performancës, sepse sasia e trafikut do të reduktohet ndjeshëm.
Transaksion SQL
Përdorni emrin e skemës me emrin e objektit:
Epo, mendoj se është e qartë. Ky operacion i tregon serverit se ku të kërkojë objekte dhe në vend që të gërmojë rastësisht nëpër kazanët e tij, ai do të dijë menjëherë se ku duhet të shkojë dhe çfarë të marrë. Me një numër të madh të bazave të të dhënave, tabelave dhe procedurave të ruajtura, ai mund të kursejë ndjeshëm kohën dhe nervat tona.
Transaksion SQL
SELECT * FROM dbo.MyTable --Kjo është një gjë e mirë për të bërë -- Në vend të SELECT * FROM MyTable --Kjo është një gjë e keqe për të bërë --Thirrni procedurën EXEC dbo.MyProc --Sërish mirë --Në vend të EXEC MyProc --Keq!
Mos përdorni prefiksin "sp_" në emrat e procedurave tuaja të ruajtura:
Nëse emri i procedurës sonë fillon me "sp_", SQL Server së pari do të shikojë në bazën e të dhënave kryesore. Fakti është se ky prefiks përdoret për procedurat personale të ruajtura të brendshme të serverit. Prandaj, përdorimi i tij mund të çojë në kosto shtesë dhe madje rezultate të pasakta nëse në bazën e të dhënave gjendet një procedurë me të njëjtin emër si e juaja.
Përdorni IF EXISTS (ZGJIDH 1) në vend të IF EXISTS (SELECT *):
Për të kontrolluar nëse një rekord ekziston në një tabelë tjetër, ne përdorim deklaratën IF EXISTS. Kjo shprehje kthehet e vërtetë nëse të paktën një vlerë kthehet nga shprehja e brendshme, nuk ka rëndësi "1", të gjitha kolonat ose një tabelë. Të dhënat e kthyera, në parim, nuk përdoren në asnjë mënyrë. Kështu, për të kompresuar trafikun gjatë transmetimit të të dhënave, është më logjike të përdoret "1", siç tregohet më poshtë.
Kur punojnë me SQL Server, përdoruesit mund të krijojnë procedurat e tyre që zbatojnë veprime të caktuara. Procedurat e ruajtura janë objekte të plota të bazës së të dhënave, dhe për këtë arsye secila prej tyre ruhet në një bazë të dhënash specifike. Thirrja e drejtpërdrejtë e një procedure të ruajtur është e mundur vetëm nëse kryhet në kontekstin e bazës së të dhënave ku ndodhet procedura.
Llojet e procedurave të ruajtura
SQL Server ka disa lloje procedurash të ruajtura.
Procedurat e ruajtura në sistem janë krijuar për të kryer veprime të ndryshme administrative. Pothuajse të gjitha veprimet e administrimit të serverit kryhen me ndihmën e tyre. Mund të thuhet se procedurat e ruajtura në sistem janë një ndërfaqe që ofron punë me tabelat e sistemit, e cila, në fund të fundit, zbret në ndryshimin, shtimin, fshirjen dhe marrjen e të dhënave nga tabelat e sistemit të bazave të të dhënave të përdoruesve dhe të sistemit. Procedurat e ruajtura në sistem janë të prefiksuara me sp_, ruhen në bazën e të dhënave të sistemit dhe mund të thirren në kontekstin e çdo baze të dhënash tjetër.
Procedurat e ruajtura me porosi zbatojnë veprime të caktuara. Procedurat e ruajtura janë një objekt i plotë i bazës së të dhënave. Si rezultat, çdo procedurë e ruajtur ndodhet në një bazë të dhënash specifike, ku edhe ekzekutohet.
Procedurat e përkohshme të ruajtura ekzistojnë vetëm për një kohë të shkurtër, pas së cilës ato shkatërrohen automatikisht nga serveri. Ato ndahen në lokale dhe globale. Procedurat lokale të ruajtura të përkohshme mund të thirren vetëm nga lidhja në të cilën janë krijuar. Kur krijoni një procedurë të tillë, duhet t'i jepet një emër që fillon me një karakter të vetëm #. Ashtu si të gjithë objektet e përkohshme, procedurat e ruajtura të këtij lloji fshihen automatikisht kur përdoruesi shkëput, rinis ose ndalon serverin. Procedurat globale të ruajtura të përkohshme janë të disponueshme për çdo lidhje në një server që ka të njëjtën procedurë. Për ta përcaktuar, mjafton t'i vini një emër që fillon me karakteret ##. Këto procedura fshihen kur serveri riniset ose ndalet, ose kur lidhja në kontekstin e së cilës janë krijuar mbyllet.
nxitësit
nxitësit janë një nga varietetet e procedurave të ruajtura. Ekzekutimi i tyre ndodh kur një deklaratë e gjuhës së manipulimit të të dhënave (DML) ekzekutohet në tabelë. Shkaktarët përdoren për të kontrolluar integritetin e të dhënave dhe gjithashtu për të rikthyer transaksionet.
Kthesëështë një procedurë e përpiluar SQL, ekzekutimi i së cilës përcaktohet nga ndodhja e ngjarjeve të caktuara brenda një baze të dhënash relacionale. Përdorimi i nxitësve është, në pjesën më të madhe, shumë i përshtatshëm për përdoruesit e bazës së të dhënave. Megjithatë, përdorimi i tyre shpesh shoqërohet me kosto burimesh shtesë për operacionet I/O. Kur të njëjtat rezultate (me shumë më pak shpenzime të burimeve) mund të arrihen duke përdorur procedura ose aplikacione të ruajtura, aktivizuesit janë të papërshtatshëm.
nxitësitështë një mjet special i serverit SQL që përdoret për të ruajtur integritetin e të dhënave në një bazë të dhënash. Kufizimet e integritetit, rregullat dhe parazgjedhjet mund të mos ofrojnë gjithmonë nivelin e dëshiruar të funksionalitetit. Shpesh është e nevojshme të zbatohen algoritme komplekse të vërtetimit të të dhënave për të siguruar që ato janë të vlefshme dhe reale. Përveç kësaj, ndonjëherë është e nevojshme të gjurmohen ndryshimet në vlerat e tabelës në mënyrë që të ndryshohen të dhënat përkatëse sipas nevojës. Shkaktarët mund të mendohen si një lloj filtrash që hyjnë në veprim pasi të gjitha operacionet janë kryer sipas rregullave, vlerave të paracaktuara, etj.
Kthesëështë një lloj i veçantë i procedurës së ruajtur që drejtohet automatikisht nga serveri kur ai përpiqet të ndryshojë të dhënat në tabelat me të cilat lidhen nxitësit. Secili Kthesëështë e lidhur me një tryezë të caktuar. Të gjitha modifikimet e të dhënave që ai bën trajtohen si një transaksion. Nëse zbulohet një gabim ose shkelje e integritetit të të dhënave, transaksioni rikthehet. Prandaj, ndryshimet janë të ndaluara. Çdo ndryshim i bërë tashmë nga këmbëza është gjithashtu i zhbërë.
Krijon shkas vetëm pronari i bazës së të dhënave. Ky kufizim ju lejon të shmangni ndryshimet aksidentale në strukturën e tabelave, mënyrat e lidhjes së objekteve të tjera me to, etj.
Kthesëështë një mjet shumë i dobishëm dhe në të njëjtën kohë i rrezikshëm. Pra, me logjikën e gabuar të punës së saj, ju lehtë mund të shkatërroni të gjithë bazën e të dhënave, kështu që shkaktarët duhet të korrigjohen me shumë kujdes.
Ndryshe nga një nënprogram i rregullt, shkas ekzekutohet në mënyrë implicite sa herë që ndodh ngjarja e nxitjes dhe nuk ka argumente. Aktivizimi i tij nganjëherë referohet si shkrepja e këmbëzës. Shkaktarët arrijnë qëllimet e mëposhtme:
vërtetimi i të dhënave të futura dhe zbatimi i kufizimeve komplekse të integritetit të të dhënave që janë të vështira, nëse jo të pamundura, për t'u mbajtur me kufizimet e integritetit të vendosura në tryezë;
lëshimi i paralajmërimeve që ju kujtojnë nevojën për të kryer veprime të caktuara kur përditësoni një tabelë të zbatuar në një mënyrë të caktuar;
grumbullimi i informacionit të auditimit duke fiksuar informacionin për ndryshimet e bëra dhe personat që i kanë kryer ato;
mbështetje për replikimin.
Formati bazë i komandës CREATE TRIGGER tregohet më poshtë:
<Определение_триггера>::=
KRIJO TRIGGER emri i këmbëzës
PARA | PAS<триггерное_событие>
AKTIV<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Ngjarjet e aktivizimit përbëhen nga futja, fshirja dhe përditësimi i rreshtave në një tabelë. Në rastin e fundit, emrat specifikë të kolonave të tabelës mund të specifikohen për ngjarjen e nxitjes. Koha e fillimit të një shkas përcaktohet duke përdorur fjalë kyçe PARA ( Kthesë zjarret para ekzekutimit të ngjarjeve të lidhura me të) ose PAS (pas ekzekutimit të tyre).
Veprimet e kryera nga aktivizuesi vendosen për çdo rresht (PER CDO RRESHT) të mbuluar nga kjo ngjarje, ose vetëm një herë për çdo ngjarje (PER CDO DEKLARIME).
Shkaktarët e shkruar gabimisht mund të çojnë në probleme serioze, siç është shfaqja e bravave "të vdekura". Aktivizuesit mund të bllokojnë shumë burime për një kohë të gjatë, kështu që duhet t'i kushtoni vëmendje të veçantë minimizimit të konflikteve të aksesit.
Kthesë mund të krijohet vetëm në bazën aktuale të të dhënave, por bazat e të dhënave të tjera mund të aksesohen brenda këmbëzës, duke përfshirë ato të vendosura në një server të largët.
Procedura e ruajturështë një lloj i veçantë i grupit të deklaratave Transact-SQL i krijuar duke përdorur gjuhën SQL dhe shtesat procedurale. Dallimi kryesor midis një pakete dhe një procedure të ruajtur është se kjo e fundit ruhet si një objekt bazë të dhënash. Me fjalë të tjera, procedurat e ruajtura ruhen në anën e serverit për të përmirësuar performancën dhe qëndrueshmërinë e detyrave të përsëritura.
Motori i bazës së të dhënave mbështet procedurat e ruajtura dhe procedurat e sistemit. Procedurat e ruajtura krijohen në të njëjtën mënyrë si të gjitha objektet e tjera të bazës së të dhënave, d.m.th. duke përdorur gjuhën DDL. Procedurat e Sistemit ofrohen nga motori i bazës së të dhënave dhe mund të përdoren për të aksesuar dhe modifikuar informacionin në katalogun e sistemit.
Kur krijoni një procedurë të ruajtur, mund të përcaktoni një listë opsionale të parametrave. Kështu, procedura do të marrë argumentet e duhura sa herë që thirret. Procedurat e ruajtura mund të kthejnë një vlerë që përmban informacion të përcaktuar nga përdoruesi ose, në rast gabimi, një mesazh gabimi të përshtatshëm.
Një procedurë e ruajtur parapërpilohet para se të ruhet si objekt në bazën e të dhënave. Forma e parakompiluar e procedurës ruhet në bazën e të dhënave dhe përdoret sa herë që thirret. Kjo veçori e procedurave të ruajtura siguron përfitimin e rëndësishëm të eliminimit (në pothuajse të gjitha rastet) ripërpilimet e procedurave dhe një përmirësim përkatës të performancës. Kjo veçori e procedurave të ruajtura ka gjithashtu një efekt pozitiv në sasinë e të dhënave të shkëmbyera midis sistemit të bazës së të dhënave dhe aplikacioneve. Në veçanti, një thirrje në një procedurë të ruajtur prej disa mijëra bajte mund të kërkojë më pak se 50 bajt. Kur përdorues të shumtë kryejnë detyra të përsëritura duke përdorur procedura të ruajtura, efekti kumulativ i këtyre kursimeve mund të jetë i rëndësishëm.
Procedurat e ruajtura mund të përdoren gjithashtu për qëllimet e mëposhtme:
për të krijuar një regjistër të regjistrave për veprimet me tabelat e bazës së të dhënave.
Përdorimi i procedurave të ruajtura siguron një nivel kontrolli sigurie që shkon përtej sigurisë së ofruar nga përdorimi i deklaratave GRANT dhe REVOKE, të cilat u japin përdoruesve privilegje të ndryshme aksesi. Kjo është e mundur sepse autorizimi për të ekzekutuar një procedurë të ruajtur është i pavarur nga autorizimi për të modifikuar objektet që përmbahen në procedurën e ruajtur, siç përshkruhet në seksionin vijues.
Procedurat e ruajtura që log shkruan dhe/ose lexojnë në tabela ofrojnë siguri shtesë për bazën e të dhënave. Duke përdorur procedura të tilla, administratori i bazës së të dhënave mund të mbajë gjurmët e modifikimeve të bëra në bazën e të dhënave nga përdoruesit ose aplikacionet.
Krijimi dhe ekzekutimi i procedurave të ruajtura
Procedurat e ruajtura krijohen duke përdorur deklaratën KRIJO PROCEDURA, e cila ka sintaksën e mëposhtme:
CREATE PROC proc_name [((@param1) type1 [ NDRYSHON] [= default1] )] (, …) SI grup | EMRI I JASHTËM metoda_emri Konventat sintaksore
Parametri schema_name specifikon emrin e skemës që caktohet nga pronari i procedurës së ruajtur të krijuar. Parametri proc_name specifikon emrin e procedurës së ruajtur. Parametri @param1 është një parametër i procedurës (një argument formal) lloji i të dhënave të të cilit specifikohet nga parametri type1. Parametrat e procedurës janë lokale brenda një procedure, ashtu si variablat lokale janë lokale brenda një pakete. Parametrat e procedurës janë vlera që i kalohen nga thirrësi procedurës për përdorim në të. Parametri default1 specifikon vlerën e paracaktuar për parametrin e procedurës përkatëse. (Vlera e paracaktuar mund të jetë gjithashtu NULL.)
Opsioni OUTPUT specifikon që një parametër i procedurës është i kthyeshëm dhe mund të përdoret për të kthyer një vlerë nga një procedurë e ruajtur në procedurën ose sistemin thirrës.
Siç u përmend më herët, forma e parapërpiluar e procedurës ruhet në bazën e të dhënave dhe përdoret sa herë që thirret. Nëse, për ndonjë arsye, një procedurë e ruajtur duhet të përpilohet sa herë që thirret, deklarata e procedurës përdor opsioni ME RICOMPILE. Përdorimi i opsionit WITH RECOMPILE mohon një nga përfitimet më të rëndësishme të procedurave të ruajtura: përmirësimin e performancës për shkak të një përpilimi të vetëm. Prandaj, opsioni WITH RECOMPILE duhet të përdoret vetëm nëse ka ndryshime të shpeshta në objektet e bazës së të dhënave të përdorura nga procedura e ruajtur.
EKZEKUTOHET SI ofertë përcakton kontekstin e sigurisë në të cilin procedura e ruajtur duhet të ekzekutohet pasi të jetë thirrur. Duke vendosur këtë kontekst, motori i bazës së të dhënave mund të kontrollojë përzgjedhjen e llogarive të përdoruesve për të kontrolluar lejet e aksesit në objektet e referuara nga kjo procedurë e ruajtur.
Si parazgjedhje, vetëm anëtarët e rolit të serverit fiks sysadmin dhe roli i bazës së të dhënave fikse db_owner ose db_ddladmin mund të përdorin deklaratën CREATE PROCEDURE. Megjithatë, anëtarët e këtyre roleve mund t'ua caktojnë këtë të drejtë përdoruesve të tjerë duke përdorur udhëzimin PROCEDURA E KRIJIMIT TË GRANTIT.
Shembulli i mëposhtëm tregon se si të krijoni një procedurë të thjeshtë të ruajtur për të punuar me tabelën e Projektit:
PËRDORIMI SampleDb; SHKO KRIJONI PROCEDURËN Rritja e Buxhetit (@përqind INT=5) SI PËRDITËSIM SET E PROJEKTIT Buxheti = Buxheti + Buxheti * @përqind/100;
Siç u përmend më herët, përdoret për të ndarë dy pako udhëzim GO. Deklarata CREATE PROCEDURE nuk mund të kombinohet me deklarata të tjera Transact-SQL në të njëjtën grup. Procedura e ruajtur IncreaseBudget rrit buxhetet për të gjitha projektet me një përqindje të caktuar, të specifikuar nga parametri @percent. Procedura gjithashtu përcakton një vlerë përqindjeje të paracaktuar (5) që zbatohet nëse ky argument nuk është i pranishëm gjatë ekzekutimit të procedurës.
Procedurat e ruajtura mund të kenë qasje në tabela që nuk ekzistojnë. Kjo veçori ju lejon të korrigjoni kodin e procedurës pa krijuar më parë tabelat e duhura dhe pa u lidhur as me serverin e destinacionit.
Ndryshe nga procedurat bazë të ruajtura, të cilat ruhen gjithmonë në bazën aktuale të të dhënave, është e mundur të krijohen procedura të përkohshme të ruajtura, të cilat vendosen gjithmonë në tempdb të bazës së të dhënave të përkohshme të sistemit. Një nga arsyet për krijimin e procedurave të ruajtura të përkohshme mund të jetë shmangia e ekzekutimit të përsëritur të një grupi të caktuar deklaratash kur lidheni me një bazë të dhënash. Ju mund të krijoni procedura të përkohshme lokale ose globale. Për ta bërë këtë, emri i procedurës lokale specifikohet me një karakter # të vetëm (#proc_name), dhe emri i procedurës globale specifikohet me një karakter të dyfishtë (##proc_name).
Një procedurë e ruajtur e përkohshme lokale mund të ekzekutohet vetëm nga përdoruesi që e ka krijuar atë dhe vetëm gjatë lidhjes me bazën e të dhënave në të cilën është krijuar. Një procedurë e përkohshme globale mund të ekzekutohet nga të gjithë përdoruesit, por vetëm derisa lidhja e fundit në të cilën po funksionon (zakonisht lidhja e krijuesit të procedurës) të ndërpritet.
Cikli i jetës së një procedure të ruajtur përbëhet nga dy faza: krijimi dhe ekzekutimi i saj. Çdo procedurë krijohet një herë dhe ekzekutohet shumë herë. Procedura e ruajtur ekzekutohet nga EKZEKUTON deklaratat një përdorues që zotëron procedurën ose ka të drejtën EKZEKUTUESE për të hyrë në procedurë. Deklarata EXECUTE ka sintaksën e mëposhtme:
[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] vlera | [@parameter1=] @variable ] | DEFAULT).. Konventat sintaksore
Me përjashtim të parametrit return_status, të gjithë parametrat e deklaratës EXECUTE kanë të njëjtën vlerë boolean si parametrat e deklaratës CREATE PROCEDURE me të njëjtin emër. Parametri return_status përcakton një variabël të plotë që ruan statusin e kthimit të procedurës. Një vlerë mund t'i caktohet një parametri duke përdorur ose një konstante (vlerë) ose një ndryshore lokale (@variable). Rendi i vlerave të parametrave të emërtuar nuk është i rëndësishëm, por vlerat e parametrave të paemërtuar duhet të jepen në rendin në të cilin ato janë përcaktuar në deklaratën CREATE PROCEDURE.
Klauzola DEFAULT jep vlerat e paracaktuara për një parametër të procedurës që është specifikuar në përkufizimin e procedurës. Kur një procedurë pret një vlerë për një parametër për të cilin nuk është përcaktuar asnjë vlerë e paracaktuar dhe parametri mungon, ose është specifikuar fjala kyçe DEFAULT, ndodh një gabim.
Kur deklarata EXECUTE është deklarata e parë në një grup, fjala kyçe EXECUTE mund të hiqet. Sidoqoftë, është më e sigurt të përfshihet kjo fjalë në çdo paketë. Përdorimi i deklaratës EXECUTE tregohet në shembullin e mëposhtëm:
PËRDORIMI SampleDb; EKZEKUTOHET Rritja e Buxhetit 10;
Deklarata EXECUTE në këtë shembull ekzekuton procedurën e ruajtur IncreaseBudget, e cila rrit buxhetin e të gjitha projekteve me 10%.
Shembulli i mëposhtëm tregon se si të krijoni një procedurë të ruajtur për të përpunuar të dhënat në tabelat Employee dhe Works_on:
Procedura ModifyEmpId në shembull ilustron përdorimin e procedurave të ruajtura si pjesë e procesit të integritetit referues (në këtë rast midis tabelave Employee dhe Works_on). Një procedurë e tillë e ruajtur mund të përdoret brenda një përkufizimi të këmbëzës, i cili në të vërtetë zbaton integritetin referencial.
Shembulli i mëposhtëm tregon përdorimin e klauzolës OUTPUT në një procedurë të ruajtur:
Kjo procedurë e ruajtur mund të ekzekutohet duke përdorur deklaratat e mëposhtme:
DEKLARONI @quantityDeleteEmployee INT; EKZEKUTON DeleteEmployee @empId=18316, @ [email i mbrojtur] dalja; PRINT N"Punonjësit e fshirë: " + konverto(nvarchar(30), @quantityDeleteEmployee);
Kjo procedurë numëron numrin e projekteve që punon një punonjës me numër personeli @empId dhe ia cakton vlerën që rezulton parametrit ©counter. Pas fshirjes së të gjitha rreshtave për një numër të caktuar personeli nga tabelat Employee dhe Works_on, vlera e llogaritur i caktohet ndryshores @quantityDeleteEmployee.
Vlera e parametrit kthehet në procedurën e thirrjes vetëm nëse është specifikuar opsioni OUTPUT. Në shembullin e mësipërm, procedura DeleteEmployee ia kalon parametrin @counter procedurës së thirrjes, kështu që procedura e ruajtur ia kthen vlerën sistemit. Prandaj, parametri @counter duhet të specifikohet si në opsionin OUTPUT gjatë deklarimit të procedurës, ashtu edhe në deklaratën EXECUTE gjatë thirrjes së saj.
ME REZULTATET cakton klauzolën e një deklarate EKZEKUTIMI
Në SQL Server 2012, shtypet deklarata EXECUTE ME REZULTATET SET Klauzola Një që, në kushte të caktuara, mund të ndryshojë formën e grupit të rezultateve të procedurës së ruajtur.
Dy shembujt e mëposhtëm do të ndihmojnë në shpjegimin e kësaj fjalie. Shembulli i parë është një shembull hyrës që tregon se si mund të duket rezultati kur klauzola ME SETime të rezultateve hiqet:
Procedura EmployeesInDept është një procedurë e thjeshtë që shfaq numrat e personelit dhe mbiemrat e të gjithë punonjësve që punojnë në një departament të caktuar. Numri i departamentit është një parametër i procedurës dhe duhet të specifikohet kur thirret procedura. Ekzekutimi i kësaj procedure nxjerr një tabelë me dy kolona, titujt e të cilave përputhen me emrat e kolonave përkatëse në tabelën e bazës së të dhënave, d.m.th. identitetin dhe mbiemrin. Për të ndryshuar titujt e kolonave të rezultateve (si dhe llojin e të dhënave të tyre), SQL Server 2012 përdor klauzolën e re WITH RESULTS SET. Zbatimi i kësaj klauzole tregohet në shembullin e mëposhtëm:
PËRDORIMI SampleDb; EXEC EmployeesInDept "d1" ME SETET E REZULTATEVE (( INT NOT NULL, [Last Emri] CHAR(20) NOT NULL));
Rezultati i ekzekutimit të një procedure të ruajtur të quajtur në këtë mënyrë do të jetë si më poshtë:
Siç mund ta shihni, ekzekutimi i një procedure të ruajtur duke përdorur klauzolën WITH RESULT SETs në një deklaratë EXECUTE ju lejon të ndryshoni emrat dhe llojin e të dhënave të kolonave të grupeve të rezultateve të prodhuara nga procedura. Kështu, ky funksionalitet i ri ofron më shumë fleksibilitet në ekzekutimin e procedurave të ruajtura dhe vendosjen e rezultateve të tyre në një tabelë të re.
Ndryshimi i strukturës së procedurave të ruajtura
Motori i bazës së të dhënave gjithashtu mbështet deklaratën PROCEDURA ALTER për të modifikuar strukturën e procedurave të ruajtura. Deklarata ALTER PROCEDURE përdoret zakonisht për të modifikuar deklaratat Transact-SQL brenda një procedure. Të gjithë parametrat e deklaratës ALTER PROCEDURE kanë të njëjtin kuptim si parametrat e deklaratës CREATE PROCEDURE me të njëjtin emër. Qëllimi kryesor i përdorimit të kësaj deklarate është shmangia e tejkalimit të lejeve ekzistuese të procedurës së ruajtur.
Motori i bazës së të dhënave mbështet Lloji i të dhënave KURSOR. Ky lloj i të dhënave përdoret për të deklaruar kursorët në procedurat e ruajtura. Kursorinështë një konstrukt programimi që përdoret për të ruajtur rezultatet e një pyetjeje (zakonisht një grup rreshtash) dhe për të lejuar përdoruesit të shfaqin atë rezultat rresht pas rreshti.
Për të hequr një ose një grup procedurash të ruajtura, përdorni Deklarata e PROCEDURËS SË HEQJES. Vetëm pronari i procedurës së ruajtur ose anëtarët e roleve fikse db_owner dhe sysadmin mund të fshijnë një procedurë të ruajtur.
Procedurat e ruajtura dhe koha e ekzekutimit të gjuhës së përbashkët
SQL Server mbështet Common Language Runtime (CLR), i cili ju lejon të zhvilloni objekte të ndryshme të bazës së të dhënave (procedura të ruajtura, funksione të përcaktuara nga përdoruesi, aktivizues, agregate të përcaktuar nga përdoruesi dhe lloje të dhënash të përcaktuara nga përdoruesi) duke përdorur C# dhe Visual Basic. Runtime e gjuhës së përbashkët gjithashtu lejon që këto objekte të ekzekutohen duke përdorur sistemin e zakonshëm të ekzekutimit.
Koha e ekzekutimit të gjuhës së përbashkët aktivizohet dhe çaktivizohet nëpërmjet opsionit clr_enabled procedurën e sistemit sp_configure, i cili niset për ekzekutim nga udhëzimi RIKOFIGUROJE. Shembulli i mëposhtëm tregon se si mund të aktivizoni kohën e ekzekutimit të gjuhës së zakonshme duke përdorur procedurën e sistemit sp_configure:
PËRDORIMI SampleDb; EXEC sp_configure "clr_enabled",1 RICONFIGURE
Krijimi, përpilimi dhe ruajtja e një procedure duke përdorur CLR kërkon sekuencën e mëposhtme të hapave, sipas rendit të listuar:
Krijoni një procedurë të ruajtur në C# ose Visual Basic dhe më pas përpiloni atë duke përdorur përpiluesin e duhur.
Përdorimi i udhëzimeve KRIJO KUVENDIM, krijoni ekzekutuesin e duhur.
Ekzekutoni një procedurë duke përdorur deklaratën EXECUTE.
Figura më poshtë tregon një diagram grafik të hapave të përshkruar më parë. Më poshtë është një përshkrim më i detajuar i këtij procesi.
Së pari, krijoni programin e dëshiruar në një mjedis zhvillimi si Visual Studio. Përpiloni programin e përfunduar në kodin e objektit duke përdorur përpiluesin C# ose Visual Basic. Ky kod ruhet në një skedar të bibliotekës së lidhjeve dinamike (.dll) që shërben si burim për deklaratën CREATE ASSEMBLY, e cila krijon kodin e ndërmjetëm të ekzekutueshëm. Më pas, lëshoni një deklaratë CREATE PROCEDURE për të ruajtur kodin që po ekzekutohet si një objekt bazë të dhënash. Së fundi, ekzekutoni procedurën duke përdorur deklaratën e njohur EXECUTE.
Shembulli më poshtë tregon kodin burimor të procedurës së ruajtur në C#:
Duke përdorur System.Data.SqlClient; duke përdorur Microsoft.SqlServer.Server; klasa publike e pjesshme StoredProcedures ( publike statike int CountEmployees() ( rreshtat int; lidhje SqlConnection = new SqlConnection("Context Connection=true"); lidhje.Open(); SqlCommand cmd = lidhje.CreateCommand(); cmd.CommandText = " count(*) si "Numri i punonjësve" " + "nga Punonjësi"; rreshtat = (int)cmd.ExecuteScalar(); lidhje. Mbyll (); kthen rreshtat; ) )
Kjo procedurë zbaton një pyetje për të numëruar numrin e rreshtave në tabelën Punonjës. Përdorimi i direktivave në fillim të një programi specifikon hapësirat e emrave të kërkuara për ekzekutimin e tij. Përdorimi i këtyre direktivave ju lejon të specifikoni emrat e klasave në kodin burimor pa specifikuar në mënyrë eksplicite hapësirat përkatëse të emrave. Më pas, përcaktohet klasa StoredProcedures, për të cilën Atributi SqlProcedure, i cili informon kompajlerin se kjo klasë është një procedurë e ruajtur. Brenda kodit të klasës, është përcaktuar metoda CountEmployees(). Lidhja me sistemin e bazës së të dhënave krijohet përmes një shembulli të klasës SqlConnection. Për të hapur një lidhje, përdoret metoda Open() e këtij shembulli. A Metoda CreateCommand(). ju lejon të aksesoni një shembull të një klase SqlCommnd, të cilit i kalon komanda e dëshiruar SQL.
Në pjesën e mëposhtme të kodit:
Cmd.CommandText = "zgjidhni numrin(*) si "Numrin e punonjësve" " + "nga Punonjësi";
përdor një deklaratë SELECT për të numëruar numrin e rreshtave në tabelën Employee dhe për të shfaqur rezultatin. Teksti i komandës specifikohet duke vendosur veçorinë CommandText të ndryshores cmd në instancën e kthyer nga metoda CreateCommand(). Tjetra quhet Metoda ExecuteScalar(). shembull i SqlCommand. Kjo metodë kthen një vlerë skalare që konvertohet në llojin e të dhënave të plotë int dhe i caktohet variablit të rreshtave.
Tani mund ta përpiloni këtë kod duke përdorur Visual Studio. E shtova këtë klasë në projekt me emrin CLRStoredProcedures, kështu që Visual Studio do të përpilojë asamblenë me të njëjtin emër me shtesën *.dll. Shembulli i mëposhtëm tregon hapin tjetër në krijimin e një procedure të ruajtur: krijimi i kodit për ekzekutim. Përpara se të ekzekutoni kodin në këtë shembull, duhet të dini vendndodhjen e skedarit të përpiluar .dll (zakonisht i vendosur në dosjen Debug të projektit).
PËRDORIMI SampleDb; SHKO KRIJO ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" ME PERMISSION_SET = SAFE
Deklarata CREATE ASSEMBLY merr kodin e menaxhuar si hyrje dhe krijon një objekt të përshtatshëm për të cilin mund të krijoni procedura të ruajtura në kohën e ekzekutimit të gjuhës së zakonshme (CLR), funksione të përcaktuara nga përdoruesi dhe aktivizues. Ky udhëzim ka sintaksën e mëposhtme:
CREATE ASSEMBLY assembly_name [ AUTHORIZATION_owner_name ] FROM (dll_file) Konventat sintaksore
Parametri assembly_name specifikon emrin e asamblesë. Klauzola opsionale e AUTORIZIMIT specifikon emrin e një roli si pronar i kësaj asambleje. Klauzola FROM specifikon shtegun ku ndodhet montimi për të ngarkuar.
Klauzola ME PERMISSION_SETështë një klauzolë shumë e rëndësishme e deklaratës CREATE ASSEMBLY dhe duhet të specifikohet gjithmonë. Ai përcakton grupin e të drejtave të aksesit që i jepen kodit të montimit. Seti i të drejtave SAFE është më kufizuesi. Kodi i Asamblesë që ka këto të drejta nuk mund të aksesojë burimet e jashtme të sistemit si skedarët. Seti i të drejtave EXTERNAL_ACCESS lejon kodin e montimit të aksesojë burime të caktuara të sistemit të jashtëm, ndërsa grupi i të drejtave UNSAFE ofron akses të pakufizuar në burime, brenda dhe jashtë sistemit të bazës së të dhënave.
Për të ruajtur informacionin e kodit të montimit, përdoruesi duhet të jetë në gjendje të lëshojë një deklaratë CREATE ASSEMBLY. Asambleja është në pronësi të përdoruesit (ose rolit) që ekzekuton instruksionin. Mund të ndryshoni zotëruesin e një asambleje duke përdorur klauzolën AUTORIZIM të deklaratës CREATE SCHEMA.
Motori i bazës së të dhënave gjithashtu mbështet deklaratat ALTER ASSEMBLY dhe DROP ASSEMBLY. Deklarata ALTER ASSEMBLY përdoret për të përditësuar një asamble në versionin më të fundit. Ky udhëzim gjithashtu shton ose heq skedarët e lidhur me asamblenë përkatëse. Deklarata e KUVENDOSJES SË HIQJES heq asamblenë e specifikuar dhe të gjithë skedarët e lidhur nga baza e të dhënave aktuale.
Shembulli i mëposhtëm tregon se si të krijoni një procedurë të ruajtur bazuar në kodin e menaxhuar të zbatuar më parë:
PËRDORIMI SampleDb; SHKO CREATE PROCEDURE CountEmployees AS EMRI I JASHTËM CLRSstoredProcedures.StoredProcedures.CountEmployees
Deklarata CREATE PROCEDURE në shembull ndryshon nga e njëjta deklaratë në shembujt e mëparshëm në atë që përmban Parametri EXTERNAL EMRI. Ky opsion specifikon që kodi gjenerohet nga CLR. Emri në këtë fjali përbëhet nga tre pjesë:
emri_assembly.emri_klases.emri_metod
assembly_name - specifikon emrin e asamblesë;
Klasa_emri - specifikon emrin e klasës së përgjithshme;
Emri_metodi - pjesa opsionale, specifikon emrin e metodës që vendoset brenda klasës.
Ekzekutimi i procedurës CountEmployees tregohet në shembullin e mëposhtëm:
PËRDORIMI SampleDb; DEKLARONI @count INT EXECUTE @count = CountEmployees PRINT @count -- Kthimet 7
Deklarata PRINT kthen numrin aktual të rreshtave në tabelën Employee.
