Razmatra se situacija u kojoj pohranjene procedure mogu degradirati performanse upita.
Prilikom kompajliranja uskladištenih procedura u MS SQL Server 2000, uskladištene procedure se smeštaju u proceduralni keš, što može poboljšati performanse tokom njihovog izvršavanja eliminišući potrebu za raščlanjivanjem, optimizacijom i kompilacijom koda uskladištene procedure.
S druge strane, postoje zamke u pohranjivanju kompajliranog koda pohranjene procedure koje mogu imati suprotan efekat.
Činjenica je da se prilikom kompajliranja pohranjene procedure sastavlja plan izvršenja onih operatora koji čine kod procedure, odnosno, ako je kompajlirana pohranjena procedura keširana, tada se kešira njen plan izvršenja, pa stoga pohranjena procedura neće biti optimiziran za određenu situaciju i parametre upita.
Uradićemo mali eksperiment da to pokažemo.
KORAK 1... Kreiranje baze podataka.
Kreirajmo 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 = 1Mb)
PRIJAVITE SE (NAME = "test_log", FILENAME = "c: \ temp \ test_log", VELIČINA = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)
KORAK 2. Kreiranje tabele.
CREATE TABLE sp_perf_test (kolona1 int, stupac2 char (5000))
KORAK 3. Popunjavanje tabele test linijama. Duplicirani redovi su namjerno dodani u tabelu. 10.000 redova numerisanih od 1 do 10.000, a 10.000 redova broja 50.000.
DECLARE @i int
SET @ i = 1
DOK (@i<10000)
POČNI
INSERT INTO sp_perf_test (kolona1, stupac2) VRIJEDNOSTI (@i, "Test string #" + CAST (@i kao char (8)))
INSERT INTO sp_perf_test (kolona1, stupac2) VRIJEDNOSTI (50000, "Test string #" + CAST (@i kao char (8)))
POSTAVI @ i = @ i + 1
KRAJ
SELECT COUNT (*) IZ sp_perf_test
Idi
KORAK 4. Kreirajte negrupisani indeks. Pošto je plan izvršenja keširan sa procedurom, indeks će se koristiti isti za sve pozive.
CREATE NENCLUSTERED INDEX CL_perf_test NA sp_perf_test (kolona1)
Idi
KORAK 5. Kreiranje pohranjene procedure. Procedura jednostavno izvršava naredbu SELECT sa uslovom.
CREATE PROC proc1 (@param int)
AS
SELECT column1, column2 FROM sp_perf_test WHERE [email protected]
Idi
KORAK 6. Izvođenje pohranjene procedure. Prilikom pokretanja ranjive procedure posebno se koristi selektivni parametar. Kao rezultat postupka, dobijamo 1 red. Plan izvršenja ukazuje na upotrebu neklasterisanog indeksa jer upit je selektivan i najbolji je način da se dohvati red. Procedura optimizirana za dohvaćanje jednog reda pohranjena je u proceduralnom kešu.
EXEC proc1 1234
Idi
KORAK 7. Izvođenje pohranjene procedure s neselektivnim parametrom. Kao parametar se koristi vrijednost od 50 000. Linije s takvom vrijednošću prve kolone od oko 10 000, respektivno, korištenje neklasteriranog indeksa i operacija pretraživanja bookmarka je neefikasna, ali pošto je kompajlirani kod sa planom izvršenja pohranjen u proceduralni keš, koristit će se. Plan izvršenja to pokazuje, kao i činjenica da je operacija traženja obeleživača izvršena za 9999 linija.
EXEC proc1 50.000
Idi
KORAK 8. Dohvaćanje redova sa prvim poljem jednakim 50 000. Izvršavanje zasebnog upita će optimizirati i kompajlirati upit sa specifičnom vrijednošću prve kolone. Kao rezultat toga, optimizator upita otkriva da je polje duplicirano mnogo puta i odlučuje da koristi operaciju skeniranja tablice, što je u ovom slučaju mnogo efikasnije od korištenja neklasteriranog indeksa.
SELECT column1, column2 FROM sp_perf_test WHERE stupac1 = 50000
Idi
Stoga možemo zaključiti da korištenje pohranjenih procedura možda neće uvijek poboljšati performanse upita. Trebali biste biti vrlo oprezni u vezi s pohranjenim procedurama koje rade na rezultatima s promjenjivim brojem linija i koriste različite planove izvršenja.
Možete koristiti skriptu da ponovite eksperiment na novom MS SQL serveru.
Uključite u svoje procedure liniju - POSTAVI NOCOUNT ON:
Sa svakom DML naredbom, SQL server nam pažljivo vraća poruku koja sadrži broj obrađenih zapisa. Ove informacije nam mogu biti korisne prilikom otklanjanja grešaka koda, ali nakon toga će biti potpuno beskorisne. Propisivanjem SET NOCOUNT ON, mi onemogućavamo ovu funkciju. Za pohranjene procedure koje sadrže nekoliko izraza ili \ i petlji, ova akcija može dati značajno povećanje performansi, jer će se količina prometa značajno smanjiti.
Transact-SQL
Koristite ime šeme sa imenom objekta:
Pa, mislim da je jasno. Ova operacija govori serveru gdje da traži objekte i umjesto da nasumično pretura po svojim kantama, on će odmah znati gdje treba da ide i šta da uzme. Sa velikim brojem baza podataka, tabela 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 - Ponovo dobro --Umjesto EXEC MyProc --Loše!
Nemojte koristiti prefiks "sp_" u nazivu vaših pohranjenih procedura:
Ako ime naše procedure počinje sa "sp_", SQL Server će prvo pogledati svoju glavnu bazu podataka. Poenta je da se ovaj prefiks koristi za lične interne serverske pohranjene procedure. Stoga, njegovo korištenje može dovesti do dodatnih troškova, pa čak i do pogrešnog rezultata ako se u njegovoj bazi podataka nađe procedura s istim imenom kao što je vaša.
Koristite IF EXISTS (ODABIR 1) umjesto IF EXISTS (SELECT *):
Za provjeru zapisa u drugoj tabeli koristimo naredbu IF EXISTS. Ovaj izraz vraća true ako je barem jedna vrijednost vraćena iz unutrašnjeg izraza, nije bitno "1", sve kolone ili tabela. Vraćeni podaci se u principu ne koriste ni na koji način. Stoga je za komprimiranje saobraćaja tokom 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 se svaka od njih pohranjuje u određenu bazu podataka. Direktan poziv pohranjenoj proceduri je moguć samo ako je napravljen u kontekstu baze podataka u kojoj se procedura nalazi.
Tipovi pohranjenih procedura
Postoji nekoliko tipova uskladištenih procedura u SQL Serveru.
Sistemske pohranjene procedure su dizajnirane za izvođenje različitih administrativnih radnji. Uz njihovu pomoć izvode se gotovo sve radnje administracije servera. Možemo reći da su sistemske pohranjene procedure sučelje koje omogućava rad sa sistemskim tabelama, što se u konačnici svodi na promjenu, dodavanje, brisanje i dohvaćanje podataka iz sistemskih tabela i korisničke i sistemske baze podataka. Sistemske pohranjene procedure imaju prefiks sp_, pohranjene su u sistemskoj bazi podataka i mogu se pozvati u kontekstu bilo koje druge baze podataka.
Prilagođene pohranjene procedure implementiraju određene radnje. Pohranjene procedure su potpuni objekt baze podataka. Kao rezultat, svaka pohranjena procedura se nalazi u određenoj bazi podataka, gdje se izvršava.
Privremene pohranjene procedure postoje samo kratko vrijeme, nakon čega ih server automatski uništava. Dijele se na lokalne i globalne. Lokalne privremene pohranjene procedure mogu se pozvati samo iz veze u kojoj su kreirane. Kada kreirate takvu proceduru, morate joj dati ime koje počinje jednim znakom #. Kao i svi privremeni objekti, pohranjene procedure ovog tipa se automatski brišu kada korisnik prekine vezu, ponovo pokrene ili zaustavi server. Globalne privremene pohranjene procedure dostupne su za bilo koju serversku vezu koja ima istu proceduru. Da biste ga definirali, trebate mu samo dati ime koje počinje simbolima ##. Ove procedure se brišu kada se server ponovo pokrene ili zaustavi, ili kada se zatvori veza u čijem kontekstu su kreirani.
Okidači
Okidači su jedna od varijanti pohranjenih procedura. Izvršavaju se kada se naredba jezika manipulacije podacima (DML) izvrši na tablici. Okidači se koriste za provjeru integriteta podataka, kao i za vraćanje transakcija.
Trigger Je kompajlirana SQL procedura, čije je izvršavanje uvjetovano pojavom određenih događaja unutar relacijske baze podataka. Uglavnom, upotreba okidača je vrlo zgodna za korisnike baze podataka. Ipak, njihova upotreba je često povezana s dodatnim ulazno/izlaznim troškovima. Kada se isti rezultati (sa mnogo manje troškova) mogu postići korištenjem pohranjenih procedura ili aplikacija, okidači nisu praktični.
Okidači Je poseban alat SQL servera 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 željeni nivo funkcionalnosti. Često je potrebno implementirati sofisticirane algoritme za provjeru valjanosti podataka kako bi se osigurala valjanost i realnost podataka. Osim toga, ponekad morate pratiti promjene vrijednosti tablice kako biste po potrebi modificirali povezane podatke. Okidači se mogu smatrati vrstom filtera koji stupaju na snagu nakon što se sve operacije izvode u skladu s pravilima, standardnim vrijednostima itd.
Trigger je posebna vrsta pohranjenih procedura koje server automatski pokreće kada pokuša izmijeniti podatke u tabelama s kojima su okidači povezani. Svaki Trigger vezuje se za određenu tabelu. Sve izmjene podataka koje izvrši tretiraju se kao jedna transakcija. Ako se otkrije greška ili kršenje integriteta podataka, transakcija se vraća nazad. Stoga je unošenje izmjena zabranjeno. Sve promjene koje je već napravio okidač također se odbacuju.
Kreira okidač samo vlasnik baze podataka. Ovo ograničenje omogućava izbjegavanje slučajnih promjena u strukturi tabela, načinima povezivanja drugih objekata s njima itd.
Trigger je vrlo koristan i u isto vrijeme opasan alat. Dakle, ako je logika njegovog 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č se izvršava 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 primjena složenih ograničenja integriteta podataka koja je teško, ako ne i nemoguće, održati korištenjem ograničenja integriteta postavljenih u tabeli;
izdavanje upozorenja koja podsjećaju da je potrebno izvršiti neke radnje prilikom ažuriranja tabele, implementirane na određeni način;
akumulacija revizorskih informacija fiksiranjem informacija o izvršenim promjenama i licima koja su ih izvršila;
podrška za replikaciju.
Osnovni format naredbe CREATE TRIGGER prikazan je u nastavku:
<Определение_триггера>::=
CREATE TRIGGER ime_okidača
PRIJE | POSLIJE<триггерное_событие>
ON<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Događaji okidača sastoje se od umetanja, brisanja i ažuriranja redova u tabeli. U potonjem slučaju, možete specificirati specifična imena stupaca tablice za događaj okidača. Vrijeme početka okidača se određuje pomoću ključnih riječi BEFORE ( Trigger pokrenut prije nego što se izvrše povezani događaji) ili NAKON (nakon što se izvrše).
Akcije koje izvodi okidač su specificirane za svaki red (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 duže vremenske periode, tako da biste trebali obratiti posebnu pažnju na minimiziranje sukoba pristupa.
Trigger može se kreirati samo u trenutnoj bazi podataka, ali je dozvoljen pristup drugim bazama podataka unutar okidača, uključujući one koje se nalaze na udaljenom serveru.
Pohranjena procedura je posebna vrsta grupe Transact-SQL izraza kreiranih korištenjem SQL jezika i proceduralnih ekstenzija. Glavna razlika između paketa i pohranjene procedure je u tome što se potonja pohranjuje kao objekt baze podataka. Drugim riječima, pohranjene procedure ostaju na strani servera kako bi se poboljšale performanse i ponovljivost.
Database Engine podržava pohranjene procedure i sistemske procedure. Pohranjene procedure se kreiraju na isti način kao i svi ostali objekti baze podataka, tj. koristeći DDL jezik. Sistemske procedure su obezbeđeni od strane Database Engine i mogu se koristiti za pristup i modifikovanje informacija u sistemskom katalogu.
Kada kreirate pohranjenu proceduru, možete definirati opcionalnu listu 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 greške, pridruženu poruku o grešci.
Pohranjena procedura je unaprijed kompajlirana prije nego što se pohrani kao objekt u bazi podataka. Unaprijed kompajlirani oblik procedure je pohranjen u bazi podataka i koristi se svaki put kada se pozove. Ovo svojstvo uskladištenih procedura pruža važnu prednost jer eliminiše (u skoro svim slučajevima) ponovnu kompilaciju procedure i obezbeđuje odgovarajuća poboljšanja performansi. Ovo svojstvo uskladištenih procedura takođe ima pozitivan efekat na količinu podataka razmenjenih između sistema baze podataka i aplikacija. Konkretno, poziv pohranjenoj proceduri koja je veličine nekoliko hiljada bajtova može zahtijevati manje od 50 bajtova. Kada više korisnika obavlja zadatke koji se ponavljaju koristeći pohranjene procedure, kumulativni učinak ovih ušteda može biti značajan.
Pohranjene procedure se također mogu koristiti u sljedeće svrhe:
da kreirate dnevnik akcija sa tabelama baze podataka.
Korišćenje uskladištenih procedura obezbeđuje nivo bezbednosne kontrole koji značajno premašuje nivo bezbednosti koji se obezbeđuje korišćenjem naredbi GRANT i REVOKE, koje korisnicima daju različite privilegije pristupa. Ovo je moguće jer je ovlaštenje za izvršavanje 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 dnevnike za pisanje i/ili čitanje tablica pružaju dodatnu sigurnost za bazu podataka. Koristeći ove procedure, 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 se kreiraju pomoću izraza CREATE PROCEDURE koji ima sljedeću sintaksu:
CREATE PROC proc_name [((@ param1) type1 [VARYING] [= default1])] (,…) AS batch | EXTERNAL NAME naziv_ metode Sintaksne konvencije
Parametar schema_name specificira ime sheme koja je dodijeljena kao vlasnik generirane pohranjene procedure. Parametar proc_name specificira ime 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. (Podrazumevana 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 sistemu.
Kao što je ranije spomenuto, unaprijed kompajlirani oblik procedure je pohranjen u bazi podataka i koristi se svaki put kada se pozove. Ako, iz nekog razloga, pohranjenu proceduru treba kompajlirati svaki put kada se pozove, prilikom deklariranja procedure, koristite opcija SA RECOMPILE... Korišćenje opcije WITH RECOMPILE negira jednu od najvažnijih prednosti uskladištenih procedura: poboljšanja performansi iz jedne kompilacije. Stoga se opcija WITH RECOMPILE treba koristiti samo kada se prave česte promjene objekata 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 naloga za provjeru dozvola pristupa za objekte 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 instrukciju GRANT CREATE PROCEDURA.
Primjer u nastavku pokazuje kako kreirati jednostavnu pohranjenu proceduru za rad s projektnom tablicom:
USE SampleDb; IDI KREIRAJ PROCEDURU Povećaj Budžet (@percent INT = 5) KAO AŽURIRANJE Projekt SET Budžet = Budžet + Budžet * @ posto / 100;
Kao što je ranije spomenuto, za razdvajanje dva paketa koristite GO uputstvo... Izraz CREATE PROCEDURE ne može se kombinirati s drugim Transact-SQL izrazima u istom paketu. Pohranjena procedura IncreaseBudget povećava budžete za sve projekte za određeni broj postotaka, specificiranih parametrom @percent. Procedura također definira zadanu vrijednost procenta (5), koja se koristi ako ovaj argument nije prisutan tokom procedure.
Pohranjene procedure mogu pristupiti tablicama koje ne postoje. Ovo svojstvo vam omogućava da otklanjate greške kod procedure bez prethodnog kreiranja odgovarajućih tabela ili čak povezivanja na odredišni server.
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 sistemsku bazu podataka tempdb. Jedan od razloga za kreiranje privremenih pohranjenih procedura može biti izbjegavanje ponavljajućeg izvršavanja određene grupe izraza prilikom povezivanja na bazu podataka. Možete kreirati lokalne ili globalne privremene procedure. Za ovo, ime lokalne procedure je navedeno sa jednim simbolom # (#proc_name), a ime globalne procedure je specificirano sa dvostrukim (## proc_name).
Lokalnu privremenu pohranjenu proceduru može izvršiti samo korisnik koji ju je kreirao i to samo za vrijeme povezivanja s bazom podataka u kojoj je kreirana. Globalnu privremenu proceduru mogu izvršiti svi korisnici, ali samo dok se 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 izvršava EXECUTE iskazi korisnik koji posjeduje proceduru ili ima dozvolu 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 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 daje zadane vrijednosti za parametar procedure koji je naveden u definiciji procedure. Greška se javlja kada procedura očekuje vrijednost za parametar za koji nije definirana zadana vrijednost i nije specificiran parametar ili je specificirana ključna riječ DEFAULT.
Kada je izraz EXECUTE prvi izraz u grupi, ključna riječ EXECUTE se može izostaviti. Međutim, sigurnije je uključiti ovu riječ u svaki paket. Upotreba naredbe EXECUTE prikazana je u primjeru ispod:
USE SampleDb; IZVRŠITI Povećanje budžeta 10;
Naredba EXECUTE u ovom primjeru izvršava pohranjenu proceduru IncreaseBudget, koja povećava budžet za sve projekte za 10%.
Primjer ispod pokazuje kako kreirati pohranjenu proceduru za obradu podataka u tablicama Employee i Works_on:
Procedura ModifyEmpId u primjeru ilustruje 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.
Primjer ispod pokazuje upotrebu OUTPUT klauzule u pohranjenoj proceduri:
Ova pohranjena procedura se može izvršiti pomoću sljedećih uputa:
DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @ empId = 18316, @ [email protected] OUTPUT; PRINT N "Izbrisani zaposleni:" + pretvoriti (nvarchar (30), @quantityDeleteEmployee);
Ova procedura broji broj projekata na kojima je zaposlenik sa kadrovskim brojem @empId zauzet i dodeljuje rezultirajuću vrijednost parametru © brojač. Nakon brisanja svih redova za dati broj osoblja iz tabela Employee i Works_on, izračunata vrijednost se dodjeljuje varijabli @quantityDeleteEmployee.
Vrijednost parametra se vraća pozivnoj proceduri samo ako je specificirana opcija OUTPUT. U gornjem primjeru, DeleteEmployee procedura prosljeđuje parametar @counter proceduri koja poziva, tako da pohranjena procedura vraća vrijednost sistemu. Stoga, parametar @counter mora biti specificiran i u opciji OUTPUT kada se deklariše procedura, i u naredbi EXECUTE kada je pozivate.
WITH RESULTS SETS klauzula naredbe EXECUTE
U SQL Server 2012 unosi se izraz EXECUTE WITH RESULTS SETS klauzula, preko koje, kada su ispunjeni određeni uslovi, možete promijeniti oblik skupa rezultata pohranjene procedure.
Sljedeća dva primjera pomoći će vam da objasnite ovu rečenicu. Prvi primjer je uvodni primjer koji pokazuje kako bi rezultat mogao izgledati kada se izostavi klauzula WITH RESULTS SETS:
Procedura EmployeesInDept je jednostavna procedura koja prikazuje kadrovske brojeve i prezimena svih zaposlenih koji rade u određenom odjeljenju. Broj odjeljenja je parametar procedure i mora se navesti prilikom poziva. Izvođenjem ove procedure prikazuje se tabela sa dvije kolone, čija se zaglavlja poklapaju sa nazivima odgovarajućih kolona tabele baze podataka, tj. Id i prezime. SQL Server 2012 koristi novu klauzulu WITH RESULTS SETS da promijeni naslove kolona rezultata (kao i njihov tip podataka). Primjena ovog prijedloga prikazana je u primjeru ispod:
USE SampleDb; EXEC EmployeesInDept "d1" SA SETOVIMA REZULTATA ((INT NOT NULL, [Prezime] CHAR (20) NOT NULL));
Rezultat izvršavanja pohranjene procedure nazvane na ovaj način bit će sljedeći:
Kao što vidite, pokretanje uskladištene procedure pomoću klauzule WITH RESULT SETS u naredbi EXECUTE omogućava vam da promijenite imena i tip podataka stupaca u skupu rezultata koji vraća procedura. Dakle, 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 izjavu ALTER PROCEDURE modificirati strukturu pohranjenih procedura. ALTER PROCEDURE se obično koristi za modificiranje Transact-SQL izraza unutar procedure. Svi parametri izraza 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 deklarisanje kursora u pohranjenim procedurama. Kursor je programska konstrukcija koja se koristi za pohranjivanje rezultata upita (obično skup redova) i za omogućavanje korisnicima da prikažu taj rezultat red po red.
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ćava da razvijete različite objekte baze podataka (pohranjene procedure, UDF-ove, okidače, UDF-ove i UDD-ove) koristeći C# i Visual Basic. CLR vam također omogućava da izvršite ove objekte koristeći uobičajeni sistem vremena izvršavanja.
CLR je dozvoljen i odbijen opcijom clr_enabled sistemska procedura sp_configure, koji je pokrenut za izvršenje instrukcijom RECONFIGURE... Primjer ispod pokazuje kako se CLR može omogućiti korištenjem sistemske procedure sp_configure:
USE SampleDb; EXEC sp_configure "clr_enabled", 1 RECONFIGURE
Da biste kreirali, kompajlirali i spremili proceduru pomoću CLR-a, morate dovršiti sljedeći niz koraka, kako bi:
Kreirajte pohranjenu proceduru u C# ili Visual Basic-u, a zatim je prevedite pomoću odgovarajućeg kompajlera.
Koristeći uputstvo CREATE ASSEMBLY, kreirajte odgovarajuću izvršnu datoteku.
Izvršite proceduru koristeći naredbu EXECUTE.
Slika ispod prikazuje dijagram toka prethodno navedenih koraka. U nastavku slijedi detaljniji opis ovog procesa.
Prvo, kreirajte potreban program u razvojnom okruženju kao što je Visual Studio. Prevedite gotov program u objektni kod koristeći C# ili Visual Basic kompajler. Ovaj kod je sačuvan u datoteci biblioteke dinamičkih veza (.dll), koja je izvor za izraz CREATE ASSEMBLY, koji generiše izvršni međukod. Zatim pokrenite naredbu CREATE PROCEDURE da spremite izvršni kod kao objekt baze podataka. Konačno, pokrenite proceduru koristeći poznatu naredbu EXECUTE.
Primjer ispod pokazuje izvorni kod za pohranjenu proceduru u C #:
Using System.Data.SqlClient; koristeći Microsoft.SqlServer.Server; javna parcijalna 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 zaposlenih" "+" od Employee "; rows = (int) cmd.ExecuteScalar (); connection.Close (); return rows;))
Ova procedura implementira upit za prebrojavanje broja redova u tabeli Employee. Direktive using na početku programa određuju prostore imena potrebne za izvršavanje programa. Korištenje ovih direktiva vam omogućava da specificirate imena klasa u izvornom kodu bez eksplicitnog navođenja odgovarajućih imenskih prostora. Zatim se definira klasa StoredProcedures, za koju je Atribut SqlProcedure koji informiše kompajlera da je ova klasa pohranjena procedura. Metoda CountEmployees () definirana je unutar koda klase. Veza sa sistemom baze podataka uspostavlja se preko instance klase SqlConnection... Za otvaranje veze koristi se Open () metoda te instance. A CreateCommand () metoda omogućava vam pristup instanci klase SqlCommnd kojoj se prosljeđuje potrebna SQL naredba.
U sljedećem isječku koda:
Cmd.CommandText = "odaberi broj (*) kao" Broj zaposlenih "" + "od zaposlenog";
koristi naredbu SELECT za prebrojavanje broja redova u tabeli Employee i prikaz rezultata. Tekst naredbe je specificiran postavljanjem svojstva CommandText varijable cmd na instancu koju vraća metoda CreateCommand (). Sljedeći se zove ExecuteScalar () metoda instanca SqlCommand. Ova metoda vraća skalarnu vrijednost koja se pretvara u int tip podataka i dodjeljuje redovima.
Sada možete kompajlirati ovaj kod koristeći Visual Studio. Ovu klasu sam dodao projektu pod nazivom CLRStoredProcedures, tako da će Visual Studio kompajlirati istoimeni sklop sa * .dll ekstenzijom. Primjer ispod pokazuje sljedeći korak u kreiranju pohranjene procedure: kreiranje izvršnog koda. Prije pokretanja koda u ovom primjeru, morate znati lokaciju kompajlirane dll datoteke (obično se nalazi u fascikli za otklanjanje grešaka u projektu).
USE SampleDb; KRAJTE ASSEMBLY CLRStoredProcedures IZ "D: \ Projekti \ CLRStoredProcedures \ bin \ Debug \ CLRStoredProcedures.dll" SA PERMISSION_SET = SIGURNO
Izraz CREATE ASSEMBLY uzima upravljani kod kao ulaz i kreira odgovarajući objekt za koji možete kreirati CLR pohranjene procedure, UDF-ove i okidače. Ova izjava ima sljedeću sintaksu:
CREATE ASSEMBLY assembly_name [AUTHORIZATION owner_name] FROM (dll_file) Sintaksne konvencije
Assembly_name specificira ime sklopa. Opciona klauzula AUTHORIZATION specificira ime uloge kao vlasnika ovog sklopa. FROM klauzula specificira putanju na kojoj 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 asemblerskom kodu. SIGURNI skup prava je najrestriktivniji. Asemblerski kod koji ima ova prava ne može pristupiti vanjskim sistemskim resursima kao što su datoteke. Skup privilegija EXTERNAL_ACCESS dozvoljava asemblerskom kodu da pristupi određenim vanjskim sistemskim resursima, dok UNSAFE skup privilegija daje neograničen pristup resursima, kako unutar tako i izvan sistema baze podataka.
Korisnik mora biti u mogućnosti da izvrši naredbu CREATE ASSEMBLY da sačuva 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 ALTER ASSEMBLY i DROP ASSEMBLY izraze. 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.
Donji primjer pokazuje kako kreirati pohranjenu proceduru zasnovanu na upravljanom kodu koji ste ranije implementirali:
USE SampleDb; IDI CREATE PROCEDURE CountEmployees KAO VANJSKI IME CLRStoredProcedures.StoredProcedures.CountEmployees
Naredba CREATE PROCEDURE u primjeru razlikuje se od iste izjave u prethodnim primjerima po tome što sadrži EXTERNAL NAME parametar... Ovaj parametar označava da kod generira CLR. Ime u ovoj rečenici ima tri dijela:
assembly_name.class_name.method_name
assembly_name Određuje ime sklopa.
class_name - označava naziv opšte klase;
method_name - opciono, specificira ime metode koja je navedena unutar klase.
Izvršenje procedure CountEmployees prikazano je u primjeru ispod:
USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count - vraća 7
Naredba PRINT vraća trenutni broj redova u tabeli Employee.
