Razmatra se situacija u kojoj pohranjene procedure mogu pogoršati performanse upita.
Prilikom prevođenja pohranjenih procedura u MS SQL Server 2000, pohranjene procedure se stavljaju u proceduralni cache, što može poboljšati performanse tijekom njihovog izvršavanja eliminirajući potrebu za raščlanjivanjem, optimizacijom i kompajliranjem koda pohranjene procedure.
S druge strane, postoje zamke u pohranjivanju prevedenog koda pohranjene procedure koje mogu imati suprotan učinak.
Činjenica je da se prilikom sastavljanja pohranjene procedure sastavlja plan izvršenja onih operatora koji čine kod procedure, odnosno, ako je prevedena pohranjena procedura predmemorana, tada se predmemorira njezin plan izvršenja, pa stoga pohranjena procedura neće biti optimiziran za određenu situaciju i parametre upita.
Napravit ćemo mali eksperiment da to pokažemo.
KORAK 1... Kreiranje baze podataka.
Napravimo zasebnu bazu podataka za eksperiment.
CREATE DATABASE test_sp_perf
UKLJUČENO (NAME = "test_data", FILENAME = "c: \ temp \ test_data", VELIČINA = 1, MAXSIZE = 10, FILEGROWTH = 1 Mb)
PRIJAVA (NAME = "test_log", FILENAME = "c: \ temp \ test_log", VELIČINA = 1, MAXSIZE = 10, FILEGROWTH = 1 Mb)
KORAK 2. Izrada tablice.
CREATE TABLE sp_perf_test (stupac1 int, stupac2 znak (5000))
KORAK 3. Ispunjavanje tablice testnim linijama. Duplikati redaka namjerno su dodani u tablicu. 10.000 redaka brojalo je od 1 do 10.000, a 10.000 redaka brojalo je 50.000.
IZJAVITI @i int
POSTAVI @ i = 1
Dok ja<10000)
POČETI
INSERT INTO sp_perf_test (stupac1, stupac2) VRIJEDNOSTI (@i, "Test string #" + CAST (@i kao char (8)))
INSERT INTO sp_perf_test (stupac1, stupac2) VRIJEDNOSTI (50000, "Test string #" + CAST (@i kao char (8)))
POSTAVI @ i = @ i + 1
KRAJ
ODABIR COUNT (*) IZ sp_perf_test
Ići
4. KORAK. Izradite negrupisani indeks. Budući da je plan izvršenja spremljen u predmemoriju s procedurom, indeks će se koristiti isti za sve pozive.
STVORI NEKLASTERIRAN INDEX CL_perf_test NA sp_perf_test (stupac1)
Ići
KORAK 5. Kreiranje pohranjene procedure. Procedura jednostavno izvršava naredbu SELECT s uvjetom.
CREATE PROC proc1 (@param int)
KAO
SELECT stupac1, stupac2 FROM sp_perf_test WHERE [e-mail zaštićen]
Ići
KORAK 6. Izvođenje pohranjene procedure. Prilikom pokretanja ranjivog postupka posebno se koristi selektivni parametar. Kao rezultat postupka, dobivamo 1 redak. Plan izvršenja ukazuje na korištenje neklasteriranog indeksa jer upit je selektivan i najbolji je način za dohvaćanje retka. Procedura optimizirana za dohvaćanje jednog retka pohranjena je u proceduralnoj predmemoriji.
EXEC proc1 1234
Ići
KORAK 7. Izvođenje pohranjene procedure s neselektivnim parametrom. Kao parametar koristi se vrijednost od 50 000. Redovi s takvom vrijednošću prvog stupca od oko 10 000, korištenje neklasteriranog indeksa i operacija pretraživanja oznake su neučinkoviti, ali budući da je prevedeni kod s planom izvršenja pohranjen u proceduralna predmemorija, koristit će se. Plan izvršenja to pokazuje, kao i činjenica da je operacija traženja oznake izvršena za 9999 redaka.
EXEC proc1 50.000
Ići
KORAK 8. Dohvaćanje redaka s prvim poljem jednakim 50 000. Izvođenje zasebnog upita optimizirat će i sastaviti upit s određenom vrijednošću prvog stupca. Kao rezultat toga, optimizator upita otkriva da je polje duplicirano mnogo puta i odlučuje upotrijebiti operaciju skeniranja tablice, što je u ovom slučaju puno učinkovitije od upotrebe neklasteriranog indeksa.
ODABIR stupac1, stupac2 IZ sp_perf_test WHERE stupac1 = 50000
Ići
Stoga možemo zaključiti da korištenje pohranjenih procedura možda neće uvijek poboljšati performanse upita. Trebali biste biti vrlo oprezni s pohranjenim procedurama koje rade na rezultatima s promjenjivim brojem redaka i koriste različite planove izvršenja.
Možete koristiti skriptu za ponavljanje eksperimenta na novom MS SQL poslužitelju.
U svoje postupke uključite redak - POSTAVI NOCOUNT ON:
Uz svaki DML izraz, SQL poslužitelj nam pažljivo vraća poruku koja sadrži broj obrađenih zapisa. Ove nam informacije mogu biti korisne tijekom otklanjanja pogrešaka koda, ali nakon toga će biti potpuno beskorisne. Propisivanjem SET NOCOUNT ON onemogućavamo ovu funkciju. Za pohranjene procedure koje sadrže nekoliko izraza ili \ i petlji, ova radnja može dati značajno povećanje performansi, jer će se količina prometa značajno smanjiti.
Transact-SQL
Koristite naziv sheme s nazivom objekta:
Pa, evo mislim da je jasno. Ova operacija govori poslužitelju gdje treba tražiti objekte i umjesto nasumično prekapavanja po svojim spremnicima, odmah će znati kamo treba ići i što uzeti. S velikim brojem baza podataka, tablica i pohranjenih procedura može nam značajno uštedjeti vrijeme i živce.
Transact-SQL
SELECT * FROM dbo.MyTable --Dobro je to učiniti - Umjesto SELECT * FROM MyTable - I loše je to učiniti --Pozivanje EXEC procedure dbo.MyProc - Opet dobro --Umjesto EXEC MyProc --Loše!
Nemojte koristiti prefiks "sp_" u nazivu svojih pohranjenih procedura:
Ako naziv naše procedure počinje sa "sp_", SQL Server će prvo pogledati svoju glavnu bazu podataka. Poanta je da se ovaj prefiks koristi za osobne interne poslužiteljske pohranjene procedure. Stoga njegovo korištenje može dovesti do dodatnih troškova, pa čak i do netočnog rezultata ako se u njegovoj bazi podataka nađe postupak s istim imenom kao što je vaš.
Koristite IF EXISTS (ODABIR 1) umjesto IF EXISTS (ODABIR *):
Za provjeru zapisa u drugoj tablici koristimo naredbu IF EXISTS. Ovaj izraz vraća true ako se iz unutarnjeg izraza vrati barem jedna vrijednost, nije važno "1", svi stupci ili tablica. Vraćeni podaci se u načelu ne koriste ni na koji način. Stoga je za komprimiranje prometa tijekom prijenosa podataka logičnije koristiti "1", kao što je prikazano u nastavku.
Kada rade sa SQL Serverom, korisnici mogu kreirati vlastite procedure koje implementiraju određene radnje. Pohranjene procedure su punopravni objekti baze podataka i stoga je svaki od njih pohranjen u određenoj bazi podataka. Izravan poziv pohranjenoj proceduri moguć je samo ako je napravljen u kontekstu baze podataka u kojoj se procedura nalazi.
Vrste pohranjenih procedura
Postoji nekoliko vrsta pohranjenih procedura u SQL Serveru.
Pohranjene procedure sustava dizajnirane su za obavljanje raznih administrativnih radnji. Uz njihovu pomoć izvode se gotovo sve radnje administracije poslužitelja. Možemo reći da su pohranjene procedure sustava sučelje koje omogućuje rad sa sistemskim tablicama, što se u konačnici svodi na promjenu, dodavanje, brisanje i dohvaćanje podataka iz sistemskih tablica i korisničke i sistemske baze podataka. Pohranjene procedure sustava imaju prefiks sp_, pohranjene su u bazi podataka sustava i mogu se pozvati u kontekstu bilo koje druge baze podataka.
Prilagođene pohranjene procedure provode određene radnje. Pohranjene procedure su potpuni objekt baze podataka. Kao rezultat, svaka pohranjena procedura nalazi se u određenoj bazi podataka, gdje se izvršava.
Privremene pohranjene procedure postoje samo kratko vrijeme, nakon čega ih poslužitelj automatski uništava. Dijele se na lokalne i globalne. Lokalne privremene pohranjene procedure mogu se pozvati samo iz veze u kojoj su stvorene. Kada kreirate takav postupak, morate mu dati ime koje počinje s jednim znakom #. Kao i svi privremeni objekti, pohranjene procedure ove vrste automatski se brišu kada korisnik prekine vezu, ponovno pokrene ili zaustavi poslužitelj. Globalne privremene pohranjene procedure dostupne su za bilo koju vezu poslužitelja koja ima istu proceduru. Da biste ga definirali, samo mu trebate dati ime koje počinje simbolima ##. Ove procedure se brišu kada se poslužitelj ponovno pokrene ili zaustavi, ili kada se zatvori veza u čijem kontekstu su stvoreni.
Okidači
Okidači su jedna od varijanti pohranjenih procedura. Izvršavaju se kada se naredba Data Manipulation Language (DML) izvrši na tablici. Okidači se koriste za provjeru integriteta podataka, kao i za vraćanje transakcija.
Okidač Je prevedena SQL procedura, čije je izvođenje uvjetovano pojavom određenih događaja unutar relacijske baze podataka. Uglavnom je korištenje okidača vrlo zgodno za korisnike baze podataka. Ipak, njihova je upotreba često povezana s dodatnim ulazno/izlaznim troškovima. Kada se isti rezultati (s mnogo manje troškova) mogu postići korištenjem pohranjenih procedura ili aplikacija, okidači nisu praktični.
Okidači Je poseban alat SQL poslužitelja koji se koristi za održavanje integriteta podataka u bazi podataka. Ograničenja integriteta, pravila i zadane postavke možda neće uvijek pružiti željenu razinu funkcionalnosti. Često je potrebno implementirati sofisticirane algoritme za provjeru valjanosti podataka kako bi se osigurala valjanost i stvarnost podataka. Osim toga, ponekad trebate pratiti promjene vrijednosti tablice kako biste po potrebi modificirali povezane podatke. Okidači se mogu smatrati vrstom filtara koji stupaju na snagu nakon što se sve operacije izvedu u skladu s pravilima, standardnim vrijednostima itd.
Okidač je posebna vrsta pohranjenih procedura koje poslužitelj automatski pokreće kada pokuša izmijeniti podatke u tablicama s kojima su pridruženi okidači. Svaki Okidač veže se na određenu tablicu. Sve izmjene podataka koje izvrši tretiraju se kao jedna transakcija. Ako se otkrije pogreška ili kršenje integriteta podataka, transakcija se poništava. Stoga je unošenje promjena zabranjeno. Sve promjene koje je već napravio okidač također se odbacuju.
Stvara okidač samo vlasnik baze podataka. Ovo ograničenje omogućuje izbjegavanje slučajnih promjena u strukturi tablica, načinima povezivanja drugih objekata s njima itd.
Okidač je vrlo koristan i u isto vrijeme opasan alat. Dakle, ako je logika njegova rada pogrešna, možete lako uništiti cijelu bazu podataka, tako da okidači moraju biti vrlo pažljivo otklonjeni.
Za razliku od obične potprograma, okidač izvršava se implicitno kad god se dogodi okidač i nema argumente. Ispaljivanje se ponekad naziva ispaljivanjem okidača. Okidači postižu sljedeće ciljeve:
provjera ispravnosti unesenih podataka i provođenje složenih ograničenja integriteta podataka koja je teško, ako ne i nemoguće, održati korištenjem ograničenja integriteta postavljenih u tablici;
izdavanje upozorenja koja podsjećaju na potrebu izvođenja nekih radnji prilikom ažuriranja tablice, implementirane na određeni način;
akumuliranje revizijskih informacija fiksiranjem informacija o učinjenim promjenama i osobama koje su ih izvršile;
podrška za replikaciju.
Osnovni format naredbe CREATE TRIGGER prikazan je u nastavku:
<Определение_триггера>::=
CREATE TRIGGER naziv_okidača
PRIJE | NAKON<триггерное_событие>
NA<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Događaji okidača sastoje se od umetanja, brisanja i ažuriranja redaka u tablici. U potonjem slučaju, možete odrediti specifične nazive stupaca tablice za događaj okidača. Vrijeme početka okidača određuje se pomoću ključnih riječi BEFORE ( Okidač pokrenut prije nego što se izvedu povezani događaji) ili NAKON (nakon što se izvrše).
Radnje koje izvodi okidač navedene su za svaki redak (ZA SVAKI RED) obuhvaćen ovim događajem ili samo jednom za svaki događaj (ZA SVAKI IZJAVA).
Pogrešno napisani okidači mogu dovesti do ozbiljnih problema kao što su zastoji. Okidači mogu blokirati mnoge resurse na dulje vrijeme, stoga biste trebali obratiti posebnu pozornost na minimiziranje sukoba pristupa.
Okidač može se kreirati samo u trenutnoj bazi podataka, ali je dopušten pristup drugim bazama podataka unutar okidača, uključujući one koje se nalaze na udaljenom poslužitelju.
Pohranjena procedura je posebna vrsta serije Transact-SQL izraza kreiranih korištenjem SQL jezika i proceduralnih ekstenzija. Glavna razlika između paketa i pohranjene procedure je u tome što je potonja pohranjena kao objekt baze podataka. Drugim riječima, pohranjene procedure ostaju na strani poslužitelja kako bi se poboljšale performanse i ponovljivost.
Database Engine podržava pohranjene procedure i procedure sustava. Pohranjene procedure kreiraju se na isti način kao i svi ostali objekti baze podataka, tj. koristeći DDL jezik. Postupci sustava osigurava Database Engine i može se koristiti za pristup i izmjenu informacija u katalogu sustava.
Kada kreirate pohranjenu proceduru, možete definirati izborni popis parametara. Dakle, procedura će prihvatiti odgovarajuće argumente svaki put kada se pozove. Pohranjene procedure mogu vratiti vrijednost koja sadrži korisnički definirane informacije ili, u slučaju pogreške, pridruženu poruku o pogrešci.
Pohranjena procedura je unaprijed kompilirana prije nego što se pohrani kao objekt u bazi podataka. Unaprijed kompilirani oblik procedure pohranjuje se u bazu podataka i koristi se svaki put kada se pozove. Ovo svojstvo pohranjenih procedura pruža važnu prednost jer eliminira (u gotovo svim slučajevima) ponovnu kompilaciju procedure i pruža odgovarajuća poboljšanja performansi. Ovo svojstvo pohranjenih procedura također ima pozitivan učinak na količinu podataka razmijenjenih između sustava baze podataka i aplikacija. Konkretno, poziv pohranjenoj proceduri veličine nekoliko tisuća bajtova može zahtijevati manje od 50 bajtova. Kada više korisnika obavlja zadatke koji se ponavljaju koristeći pohranjene procedure, kumulativni učinak tih ušteda može biti značajan.
Pohranjene procedure također se mogu koristiti u sljedeće svrhe:
za stvaranje dnevnika radnji s tablicama baze podataka.
Korištenje pohranjenih procedura osigurava razinu sigurnosne kontrole koja značajno premašuje razinu sigurnosti koju pruža korištenje izraza GRANT i REVOKE, koji korisnicima daju različite privilegije pristupa. To je moguće jer je ovlaštenje za izvođenje pohranjene procedure neovisno o ovlaštenju za modificiranje objekata sadržanih u danoj pohranjenoj proceduri, kao što je opisano u sljedećem odjeljku.
Pohranjene procedure koje generiraju zapisnike za pisanje i/ili čitanje tablica pružaju dodatnu sigurnost za bazu podataka. Koristeći ove postupke, administrator baze podataka može pratiti izmjene koje su izvršili korisnici ili aplikacije u bazi podataka.
Kreiranje i izvođenje pohranjenih procedura
Pohranjene procedure kreiraju se pomoću izraza KREIRATI POSTUPAK koji ima sljedeću sintaksu:
CREATE PROC proc_name [((@ param1) type1 [VARYING] [= default1])] (,…) KAO batch | VANJSKO IME naziv_ metode Sintaksne konvencije
Parametar schema_name specificira ime sheme koja je dodijeljena kao vlasnik generirane pohranjene procedure. Parametar proc_name specificira naziv pohranjene procedure. Parametar @ param1 je parametar procedure (formalni argument) čiji je tip podataka specificiran parametrom type1. Parametri procedure su lokalni unutar procedure, baš kao što su lokalne varijable lokalne unutar paketa. Parametri procedure su vrijednosti koje pozivatelj prosljeđuje proceduri za korištenje u njoj. Parametar default1 definira zadanu vrijednost za odgovarajući parametar procedure. (Zadana vrijednost također može biti NULL.)
OUTPUT opcija označava da je parametar procedure povratni parametar koji se može koristiti za vraćanje vrijednosti iz pohranjene procedure pozivnoj proceduri ili sustavu.
Kao što je ranije spomenuto, unaprijed kompilirani oblik procedure pohranjuje se u bazu podataka i koristi se svaki put kada se pozove. Ako iz nekog razloga pohranjenu proceduru treba prevesti svaki put kada se pozove, prilikom deklariranja procedure upotrijebite opcija SA REKOMPILACIJOM... Korištenje opcije WITH RECOMPILE negira jednu od najvažnijih prednosti pohranjenih procedura: poboljšanja performansi iz jedne kompilacije. Stoga se opcija WITH RECOMPILE treba koristiti samo kada se često mijenjaju objekti baze podataka koje koristi pohranjena procedura.
EXECUTE AS klauzula definira sigurnosni kontekst u kojem bi se pohranjena procedura trebala izvršiti nakon što je pozvana. Postavljanjem ovog konteksta možete koristiti Database Engine za kontrolu odabira korisničkih računa za provjeru dopuštenja pristupa objektima na koje upućuje ova pohranjena procedura.
Po defaultu, samo članovi fiksne uloge poslužitelja sysadmin i fiksne uloge baze podataka db_owner ili db_ddladmin mogu koristiti izraz CREATE PROCEDURE. Ali članovi ovih uloga mogu dodijeliti ovo pravo drugim korisnicima koristeći upute POSTUPAK IZRADE GRANT.
Primjer u nastavku pokazuje kako stvoriti jednostavnu pohranjenu proceduru za rad s tablicom projekta:
KORISTITE SampleDb; IDI STVORI POSTUPAK Povećaj Budžet (@percent INT = 5) KAO AŽURIRANJE Projekt POSTAVI Proračun = Proračun + Proračun * @ posto / 100;
Kao što je ranije spomenuto, za razdvajanje dva paketa koristite GO upute... Izraz CREATE PROCEDURE ne može se kombinirati s drugim Transact-SQL izrazima u istom paketu. Pohranjena procedura IncreaseBudget povećava proračune za sve projekte za određeni broj postotaka, naveden parametrom @percent. Procedura također definira zadanu vrijednost postotka (5), koja se koristi ako ovaj argument nije prisutan tijekom postupka.
Pohranjene procedure mogu pristupiti tablicama koje ne postoje. Ovo svojstvo omogućuje vam otklanjanje pogrešaka koda procedure bez prethodnog stvaranja odgovarajućih tablica ili čak povezivanja s odredišnim poslužiteljem.
Za razliku od osnovnih pohranjenih procedura, koje su uvijek pohranjene u trenutnoj bazi podataka, moguće je kreirati privremene pohranjene procedure koje se uvijek postavljaju u privremenu bazu podataka sustava tempdb. Jedan od razloga za kreiranje privremenih pohranjenih procedura može biti izbjegavanje ponavljajućeg izvršavanja određene grupe izraza pri povezivanju s bazom podataka. Možete kreirati lokalne ili globalne privremene procedure. Za to se naziv lokalne procedure navodi s jednim simbolom # (#proc_name), a naziv globalne procedure s dvostrukim (## proc_name).
Lokalnu privremenu pohranjenu proceduru može izvršiti samo korisnik koji ju je kreirao i to samo tijekom povezivanja s bazom podataka u kojoj je kreirana. Globalnu privremenu proceduru mogu izvršiti svi korisnici, ali samo dok ne završi posljednja veza u kojoj se izvršava (obično veza kreatora procedure).
Životni ciklus pohranjene procedure sastoji se od dvije faze: njenog kreiranja i njenog izvršenja. Svaka procedura se kreira jednom, a izvršava se više puta. Pohranjenu proceduru izvodi EXECUTE izjave korisnik koji posjeduje proceduru ili ima dopuštenje EXECUTE za pristup toj proceduri. Naredba EXECUTE ima sljedeću sintaksu:
[] [@return_status =] (proc_name | @proc_name_var) ([[@ parameter1 =] vrijednost | [@ parameter1 =] @variable] | DEFAULT) .. Sintaksne konvencije
Osim parametra return_status, svi parametri izraza EXECUTE imaju isto logičko značenje kao i parametri istog imena za izraz CREATE PROCEDURE. Parametar return_status definira cjelobrojnu varijablu koja pohranjuje povratno stanje procedure. Vrijednost se može dodijeliti parametru pomoću konstante (vrijednost) ili lokalne varijable (@varijable). Redoslijed vrijednosti imenovanih parametara nije važan, ali vrijednosti neimenovanih parametara moraju biti dostavljene redoslijedom kojim su definirane u naredbi CREATE PROCEDURE.
DEFAULT klauzula pruža zadane vrijednosti za parametar procedure koji je naveden u definiciji procedure. Pogreška se događa kada procedura očekuje vrijednost za parametar za koji nije definirana zadana vrijednost i nije naveden nijedan parametar ili je navedena ključna riječ DEFAULT.
Kada je izraz EXECUTE prvi izraz u grupi, ključna riječ EXECUTE može se izostaviti. Međutim, sigurnije je uključiti ovu riječ u svaki paket. Upotreba izraza EXECUTE prikazana je u primjeru ispod:
KORISTITE SampleDb; IZVRŠITE Povećanje budžeta 10;
Naredba EXECUTE u ovom primjeru izvršava pohranjenu proceduru IncreaseBudget, koja povećava proračun za sve projekte za 10%.
Primjer u nastavku pokazuje kako stvoriti pohranjenu proceduru za obradu podataka u tablicama Employee i Works_on:
Procedura ModifyEmpId u primjeru ilustrira upotrebu pohranjenih procedura kao dijela procesa referentnog integriteta (u ovom slučaju između tablica Employee i Works_on). Slična pohranjena procedura može se koristiti unutar definicije okidača koja zapravo provodi referentni integritet.
Sljedeći primjer pokazuje upotrebu klauzule OUTPUT u pohranjenoj proceduri:
Ova pohranjena procedura može se izvršiti pomoću sljedećih uputa:
DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @ empId = 18316, @ [e-mail zaštićen] IZLAZ; PRINT N "Izbrisani zaposlenici:" + pretvoriti (nvarchar (30), @quantityDeleteEmployee);
Ovaj postupak broji broj projekata na kojima je zaposlenik s brojkom osoblja @empId zauzet, a dobivenu vrijednost dodjeljuje parametru © brojač. Nakon brisanja svih redaka za dani broj osoblja iz tablica Employee i Works_on, izračunata vrijednost se dodjeljuje varijabli @quantityDeleteEmployee.
Vrijednost parametra se vraća pozivnoj proceduri samo ako je navedena opcija OUTPUT. U gornjem primjeru, DeleteEmployee procedura prosljeđuje parametar @counter pozivnoj proceduri, tako da pohranjena procedura vraća vrijednost sustavu. Stoga, parametar @counter mora biti specificiran i u opciji OUTPUT pri deklariranju procedure i u naredbi EXECUTE kada je pozivate.
WITH RESULTS SETS klauzula naredbe EXECUTE
U SQL Server 2012 unosi se izraz EXECUTE WITH REULTS SETS klauzula, putem kojeg, kada su ispunjeni određeni uvjeti, možete promijeniti oblik skupa rezultata pohranjene procedure.
Sljedeća dva primjera pomoći će objasniti ovu rečenicu. Prvi primjer je uvodni primjer koji pokazuje kako bi rezultat mogao izgledati kada se izostavi klauzula WITH RESULTS SETS:
Procedura EmployeesInDept jednostavna je procedura koja prikazuje osobne brojeve i prezimena svih zaposlenika koji rade u određenom odjelu. Broj odjela je parametar postupka i mora se navesti prilikom poziva. Izvođenjem ovog postupka prikazuje se tablica s dva stupca čija se zaglavlja podudaraju s nazivima odgovarajućih stupaca tablice baze podataka, tj. Id i prezime. SQL Server 2012 koristi novu klauzulu WITH RESULTS SETS za promjenu naslova stupaca rezultata (kao i njihov tip podataka). Primjena ovog prijedloga prikazana je u primjeru u nastavku:
KORISTITE SampleDb; EXEC EmployeesInDept "d1" SA SETOVIMA REZULTATA ((INT NOT NULL, [Prezime] CHAR (20) NOT NULL));
Rezultat izvođenja pohranjene procedure nazvane na ovaj način bit će sljedeći:
Kao što možete vidjeti, izvođenje pohranjene procedure pomoću klauzule WITH RESULT SETS u izrazu EXECUTE omogućuje vam promjenu imena i vrste podataka stupaca u skupu rezultata koji vraća procedura. Stoga ova nova funkcionalnost pruža veću fleksibilnost u izvršavanju pohranjenih procedura i postavljanju njihovih rezultata u novu tablicu.
Promjena strukture pohranjenih procedura
Database Engine također podržava tu izjavu IZMIJENI POSTUPAK modificirati strukturu pohranjenih procedura. ALTER PROCEDURE se obično koristi za modificiranje Transact-SQL izraza unutar procedure. Svi parametri naredbe ALTER PROCEDURE imaju isto značenje kao i parametri istog imena za izraz CREATE PROCEDURE. Glavna svrha korištenja ove izjave je izbjegavanje nadjačavanja postojećih prava pohranjene procedure.
Database Engine podržava tip podataka CURSOR... Ovaj tip podataka se koristi za deklariranje kursora u pohranjenim procedurama. Pokazivač je programska konstrukcija koja se koristi za pohranjivanje rezultata upita (obično skup redaka) i za omogućavanje korisnicima da prikažu taj rezultat red po redak.
Za brisanje jedne ili grupe pohranjenih procedura koristite DROP PROCEDURE izraz... Samo vlasnik ili članovi fiksnih uloga db_owner i sysadmin mogu izbrisati pohranjenu proceduru.
Pohranjene procedure i CLR
SQL Server podržava Common Language Runtime (CLR) koji vam omogućuje razvoj različitih objekata baze podataka (pohranjenih procedura, UDF-ova, okidača, UDF-ova i UDD-ova) koristeći C# i Visual Basic. CLR vam također omogućuje izvršavanje ovih objekata korištenjem uobičajenog sustava izvođenja.
CLR je dopušten i odbijen opcijom clr_enabled postupak sustava sp_configure, koji se instrukcijom pokreće za izvršenje REKONFIGURIRAJ... Primjer u nastavku pokazuje kako se CLR može omogućiti korištenjem procedure sustava sp_configure:
KORISTITE SampleDb; EXEC sp_configure "clr_enabled", 1 RECONFIGURE
Za stvaranje, prevođenje i spremanje procedure pomoću CLR-a, morate dovršiti sljedeći slijed koraka, kako bi:
Napravite pohranjenu proceduru u C # ili Visual Basicu, a zatim je prevedite pomoću odgovarajućeg prevoditelja.
Koristeći uputu STVORI SKUPŠTINU, kreirajte odgovarajuću izvršnu datoteku.
Izvršite proceduru pomoću naredbe EXECUTE.
Slika ispod prikazuje dijagram toka prethodno navedenih koraka. Slijedi detaljniji opis ovog procesa.
Prvo stvorite potreban program u razvojnom okruženju kao što je Visual Studio. Prevedite gotov program u objektni kod pomoću prevoditelja C# ili Visual Basica. Ovaj se kod sprema u datoteku biblioteke dinamičke veze (.dll), koja je izvor za izraz CREATE ASSEMBLY, koji generira izvršni međukod. Zatim pokrenite izraz CREATE PROCEDURE da spremite izvršni kod kao objekt baze podataka. Konačno, pokrenite proceduru koristeći poznatu naredbu EXECUTE.
Primjer u nastavku prikazuje izvorni kod za pohranjenu proceduru u C #:
Korištenje System.Data.SqlClient; korištenjem Microsoft.SqlServer.Server; javna djelomična klasa StoredProcedures (javni statički int CountEmployees () (int rows; SqlConnection veza = nova SqlConnection ("Context Connection = true"); connection.Open (); SqlCommand cmd = connection.CreateCommand (); cmd.CommandText = "odaberi count (*) kao "Broj zaposlenika" "+" od Employee "; rows = (int) cmd.ExecuteScalar (); connection.Close (); return rows;))
Ovaj postupak implementira upit za prebrojavanje broja redaka u tablici Employee. Smjernice using na početku programa određuju prostore imena potrebne za izvršavanje programa. Korištenje ovih direktiva omogućuje vam da navedete imena klasa u izvornom kodu bez eksplicitnog navođenja odgovarajućih imenskih prostora. Zatim se definira klasa StoredProcedures, za koju je Atribut SqlProcedure koji informira prevoditelja da je ova klasa pohranjena procedura. Metoda CountEmployees () definirana je unutar koda klase. Veza sa sustavom baze podataka uspostavlja se putem instance klase SqlConnection... Za otvaranje veze koristi se Open () metoda te instance. A CreateCommand () metoda omogućuje vam pristup instanci klase SqlCommnd kojemu se prosljeđuje potrebna SQL naredba.
U sljedećem isječku koda:
Cmd.CommandText = "odaberi broj (*) kao" Broj zaposlenika "" + "od zaposlenika";
koristi naredbu SELECT za prebrojavanje broja redaka u tablici Employee i prikaz rezultata. Tekst naredbe određuje se postavljanjem svojstva CommandText varijable cmd na instancu koju vraća metoda CreateCommand (). Sljedeći se zove Metoda ExecuteScalar (). instancu SqlCommand. Ova metoda vraća skalarnu vrijednost koja se pretvara u int tip podataka i dodjeljuje recima.
Sada možete kompajlirati ovaj kod koristeći Visual Studio. Dodao sam ovu klasu projektu pod nazivom CLRStoredProcedures, tako da će Visual Studio kompajlirati istoimeni sklop s * .dll ekstenzijom. Primjer u nastavku prikazuje sljedeći korak u stvaranju pohranjene procedure: stvaranje izvršnog koda. Prije pokretanja koda u ovom primjeru, morate znati lokaciju kompajlirane dll datoteke (obično se nalazi u mapi Debug projekta).
KORISTITE SampleDb; IDI STVORI ASSEMBLY CLRStoredProcedures IZ "D: \ Projekti \ CLRStoredProcedures \ bin \ Debug \ CLRStoredProcedures.dll" SA PERMISSION_SET = SIGURNO
Izraz CREATE ASSEMBLY uzima upravljani kod kao ulaz i stvara odgovarajući objekt za koji možete kreirati CLR pohranjene procedure, UDF-ove i okidače. Ova izjava ima sljedeću sintaksu:
CREATE ASSEMBLY naziv_sklopa [AUTHORIZATION owner_name] IZ (dll_file) Sintaksne konvencije
Assembly_name specificira naziv sklopa. Opcijska klauzula AUTHORIZATION navodi ime uloge kao vlasnika ovog sklopa. Klauzula FROM specificira put na kojem se nalazi sklop za učitavanje.
WITH PERMISSION_SET klauzula je vrlo važna klauzula izjave CREATE ASSEMBLY i uvijek mora biti uključena. Definira skup prava pristupa koja se dodjeljuju kodu sklopa. SIGURNI skup prava je najrestriktivniji. Asemblerski kod koji ima ta prava ne može pristupiti vanjskim resursima sustava kao što su datoteke. Skup privilegija EXTERNAL_ACCESS omogućuje asemblerskom kodu pristup određenim vanjskim resursima sustava, dok UNSAFE skup povlastica dopušta neograničen pristup resursima, unutar i izvan sustava baze podataka.
Korisnik mora biti u mogućnosti izvršiti naredbu CREATE ASSEMBLY kako bi sačuvao informacije o asemblerskom kodu. Sklop je u vlasništvu korisnika (ili uloge) koji izvršava ovu naredbu. Vlasnika sklopa možete učiniti drugim korisnikom korištenjem klauzule AUTHORIZATION naredbe CREATE SCHEMA.
Database Engine također podržava izraze ALTER ASSEMBLY i DROP ASSEMBLY. ALTER ASSEMBLY izjava koristi se za ažuriranje sklopa na najnoviju verziju. Ova izjava također dodaje ili uklanja datoteke povezane s odgovarajućim sklopom. DROP ASSEMBLY izjava uklanja navedeni sklop i sve povezane datoteke iz trenutne baze podataka.
Primjer u nastavku pokazuje kako stvoriti pohranjenu proceduru na temelju upravljanog koda koji ste prethodno implementirali:
KORISTITE SampleDb; IDI STVORI POSTUPAK CountEmployees KAO VANJSKI NAZIV CLRStoredProcedures.StoredProcedures.CountEmployees
Izraz CREATE PROCEDURE u primjeru razlikuje se od istog iskaza u prethodnim primjerima po tome što sadrži parametar VANJSKI NAZIV... Ovaj parametar označava da kod generira CLR. Ime u ovoj rečenici ima tri dijela:
naziv_sklopa.ime_klase.ime_metode
assembly_name Određuje naziv sklopa.
class_name - označava naziv opće klase;
method_name - neobavezno, specificira naziv metode koja je navedena unutar klase.
Izvršenje postupka CountEmployees prikazano je u primjeru u nastavku:
KORISTITE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count - vraća 7
Naredba PRINT vraća trenutni broj redaka u tablici Employee.
