Razmatramo situaciju u kojoj pohranjene procedure mogu degradirati performanse upita.
Prilikom kompajliranja uskladištenih procedura u MS SQL Server 2000, pohranjene procedure se stavljaju u predmemoriju procedura, što može pomoći u povećanju performansi kada se izvode eliminišući potrebu za raščlanjivanjem, optimizacijom i kompajliranjem 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 naredbi koje čine kod procedure, odnosno, ako je kompajlirana pohranjena procedura keširana, tada se kešira i 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 demonstriramo.
KORAK 1. Kreiranje baze podataka.
Za eksperiment, napravimo zasebnu bazu podataka.
CREATE DATABASE test_sp_perf
UKLJUČENO (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
PRIJAVITE SE (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
KORAK 2. Kreiranje tabele.
CREATE TABLE sp_perf_test(kolona1 int, stupac2 char(5000))
KORAK 3. Popunjavanje tabele test nizovima. Duplicirani redovi su namjerno dodani u tabelu. 10000 redova sa brojevima od 1 do 10000 i 10000 redova sa brojevima od 50000.
DECLARE @i int
SET @i=1
WHILE(@i<10000)
POČNI
INSERT INTO sp_perf_test(kolona1, stupac2) VALUES(@i,"Test string #"+CAST(@i kao char(8)))
INSERT INTO sp_perf_test(kolona1, stupac2) VALUES(50000,"Test string #"+CAST(@i kao char(8)))
POSTAVI @i= @i+1
KRAJ
SELECT COUNT(*) FROM sp_perf_test
GO
KORAK 4. Kreirajte negrupisani indeks. Pošto je plan izvršenja keširan sa procedurom, indeks će se koristiti na isti način za sve pozive.
CREATE NENCLUSTERED INDEX CL_perf_test NA sp_perf_test(kolona1)
GO
KORAK 5. Kreirajte pohranjenu proceduru. Procedura jednostavno izvršava naredbu SELECT sa uslovom.
CREATE PROC proc1 (@paramint)
AS
SELECT column1, column2 FROM sp_perf_test WHERE [email protected]
GO
KORAK 6. Pokrenite pohranjenu proceduru. Prilikom pokretanja ranjive procedure, posebno se koristi selektivni parametar. Kao rezultat postupka, dobivamo 1 red. Plan izvršenja ukazuje na upotrebu neklasterisanog indeksa, kao upit je selektivan i ovo je najbolji način za izdvajanje stringa. Procedura optimizirana za dohvaćanje jednog reda je pohranjena u predmemoriji procedura.
EXEC proc1 1234
GO
KORAK 7. Izvođenje pohranjene procedure s neselektivnim parametrom. Kao parametar se koristi vrijednost 50000. Redovi s takvom vrijednošću prvog stupca od oko 10000, odnosno korištenjem indeksa koji nije grupiran u klasteru i operacija pretraživanja oznake su neefikasni, ali pošto je prevedeni kod sa planom izvršenja pohranjen u proceduralnim keš memorija, koristit će se. Plan izvršenja to pokazuje, kao i činjenica da je izvršena operacija pretraživanja bookmarka za 9999 redova.
EXEC proc1 50000
GO
KORAK 8. Izvođenje odabira redova sa prvim poljem jednakim 50000. Kada se izvrši poseban upit, upit se optimizira i kompajlira sa specifičnom vrijednošću prve kolone. Kao rezultat toga, optimizator upita utvrđuje da je polje duplicirano mnogo puta i odlučuje da koristi operaciju skeniranja tablice, koja je u ovom slučaju mnogo efikasnija od korištenja indeksa koji nije klaster.
SELECT column1, column2 FROM sp_perf_test WHERE stupac1=50000
GO
Stoga možemo zaključiti da upotreba pohranjenih procedura možda neće uvijek poboljšati performanse upita. Trebali biste biti vrlo pažljivi s onim pohranjenim procedurama koje rade s rezultatima s promjenjivim brojem redova i koriste različite planove izvršenja.
Možete koristiti skriptu da ponovite eksperiment na vlastitom MS SQL serveru.
Vrste pohranjenih procedura
Postoji nekoliko tipova u SQL Serveru pohranjene procedure.
- Sistemski pohranjene procedure dizajniran za obavljanje raznih administrativnih radnji. Uz njihovu pomoć izvode se gotovo sve radnje administracije servera. Možemo reći da sistem pohranjene procedure su interfejs koji omogućava rad sa sistemskim tabelama, koji se, u krajnjoj liniji, svodi na promenu, dodavanje, brisanje i preuzimanje podataka iz sistemskih tabela i korisničke i sistemske baze podataka. Sistemski pohranjene procedure imaju prefiks sp_, pohranjuju se u sistemsku bazu podataka i mogu se pozvati u kontekstu bilo koje druge baze podataka.
- Custom pohranjene procedure sprovesti određene radnje. Pohranjene procedure- kompletan objekt baze podataka. Kao rezultat ovoga, svaki pohranjena procedura se nalazi u određenoj bazi podataka, gdje se izvršava.
- Privremeno pohranjene procedure postoje samo kratko vrijeme, nakon čega ih server automatski uništava. Dijele se na lokalne i globalne. Lokalno privremeno pohranjene procedure mogu se pozvati samo iz veze u kojoj su kreirani. Kada kreirate takvu proceduru, mora joj se 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. Globalno privremeno pohranjene procedure dostupno za sve serverske veze koje imaju istu proceduru. Da biste ga definirali, dovoljno je dati mu ime koje počinje znakovima ## . Ove procedure se brišu kada se server ponovo pokrene ili zaustavi, ili kada se zatvori veza u čijem kontekstu su kreirane.
Kreiranje, mijenjanje i brisanje pohranjenih procedura
Kreacija pohranjena procedura uključuje rješavanje sljedećih zadataka:
- definisanje vrste pohranjena procedura: privremeno ili prilagođeno. Osim toga, možete kreirati vlastiti sistem pohranjena procedura, dajući mu ime sa sp_ prefiksom i stavljajući ga u sistemsku bazu podataka. Takav postupak će biti dostupan u kontekstu bilo koje baze podataka na lokalnom serveru;
- planiranje pristupa. Tokom stvaranja pohranjena procedura imajte na umu da će imati ista prava pristupa objektima baze podataka kao i korisnik koji ju je kreirao;
- definicija parametri pohranjene procedure. Kao i procedure uključene u većinu programskih jezika, pohranjene procedure može imati ulazne i izlazne parametre;
- razvoj koda pohranjena procedura. Kod procedure može sadržavati niz bilo koje SQL naredbe, uključujući pozivanje drugih. pohranjene procedure.
Kreiranje novog i izmjena postojećeg pohranjena procedura se radi sljedećom komandom:
<определение_процедуры>::= (CREATE | ALTER) ime_procedure [;broj] [(@naziv_parametra tip podataka) [=podrazumevano] ][,...n] AS sql_izjava [...n]
Razmotrite parametre ove naredbe.
Koristeći prefikse sp_, #, ##, kreirana procedura se može definirati kao sistemska ili privremena procedura. Kao što vidite iz sintakse naredbe, nije dozvoljeno navesti ime vlasnika kome će kreirana procedura pripadati, kao ni naziv baze podataka u koju treba da bude smeštena. Dakle, kako bi se prilagodili stvorenom pohranjena procedura u određenoj bazi podataka, morate pokrenuti naredbu CREATE PROCEDURE u kontekstu te baze podataka. Kada se rukuje sa tijela pohranjena procedura Skraćena imena se mogu koristiti za objekte u istoj bazi podataka, tj. bez navođenja imena baze podataka. Kada želite da upućujete na objekte koji se nalaze u drugim bazama podataka, navođenje imena baze podataka je obavezno.
Broj u nazivu je identifikacioni broj pohranjena procedura, što ga jedinstveno definira u grupi procedura. Za praktičnost upravljanja procedurama, logički isti tip pohranjene procedure mogu se grupirati davanjem istog imena ali različitih identifikacionih brojeva.
Za prosljeđivanje ulaznih i izlaznih podataka u kreiranom pohranjena procedura mogu se koristiti parametri, čija imena, kao i imena lokalnih varijabli, moraju početi sa simbolom @. Jedan pohranjena procedura Možete navesti više opcija odvojenih zarezima. Tijelo procedure ne smije koristiti lokalne varijable čija su imena ista kao imena parametara procedure.
Da biste odredili tip podataka koji odgovara parametar pohranjene procedure, bilo koji SQL tip podataka je u redu, uključujući i one koje definira korisnik. Međutim, tip podataka CURSOR se može koristiti samo kao izlazni parametar pohranjena procedura, tj. sa ključnom riječi OUTPUT .
Prisustvo ključne riječi OUTPUT znači da je odgovarajući parametar namijenjen za vraćanje podataka iz pohranjena procedura. Međutim, to uopće ne znači da parametar nije prikladan za prosljeđivanje vrijednosti pohranjena procedura. Određivanje ključne riječi OUTPUT daje instrukcije serveru za izlazak pohranjena procedura dodijeli trenutnu vrijednost parametra lokalnoj varijabli koja je specificirana kada je procedura pozvana kao vrijednost parametra. Imajte na umu da kada specificirate ključnu riječ OUTPUT, vrijednost odgovarajućeg parametra pri pozivanju procedure može se postaviti samo pomoću lokalne varijable. Bilo koji izrazi ili konstante dozvoljeni za normalne parametre nisu dozvoljeni.
Ključna riječ VARYING se koristi zajedno sa
Pohranjena procedura pohranjena procedura) je imenovani programski objekt baze podataka. SQL Server ima nekoliko tipova uskladištenih procedura.
Sistemske pohranjene procedure sistemska pohranjena procedura) isporučuju DBMS programeri i koriste se za izvođenje radnji na sistemskom katalogu ili dobijanje sistemskih informacija. Njihova imena obično počinju prefiksom "sp_". Pohranjene procedure svih tipova se pokreću naredbom EXECUTE, koja se može skratiti na EXEC. Na primjer, pohranjena procedura sp_helplogins, pokrenuta bez parametara, generira dva izvještaja o nazivima računa (engleski) prijave) i njihove odgovarajuće korisnike u svakoj bazi podataka (engleski) korisnicima).
EXEC sp_helplogins;
Da biste dali ideju o radnjama koje se izvode pomoću sistemskih pohranjenih procedura, u tabeli. 10.6 prikazuje neke primjere. Ukupno, postoji više od hiljadu sistemskih uskladištenih procedura u SQL Serveru.
Tabela 10.6
Primjeri pohranjenih procedura sustava SQL Server
Korisnik može kreirati pohranjene procedure u korisničkim bazama podataka iu bazi podataka za privremene objekte. U potonjem slučaju, pohranjena procedura bi bila temporalni. Kao i kod privremenih tabela, ime privremene pohranjene procedure mora početi s prefiksom "#" ako je to lokalna privremena pohranjena procedura, ili "##" ako je globalna. Lokalna privremena procedura se može koristiti samo unutar veze u kojoj je kreirana, a globalna se može koristiti i unutar drugih veza.
Programabilni SQL Server objekti mogu se kreirati bilo pomoću Transact-SQL alata ili pomoću sklopova (engleski) sklop) u okruženju CRL (Common Language Runtime) Microsoft .Net Framework. U ovom vodiču će se razmatrati samo prva metoda.
Za kreiranje pohranjenih procedura koristite naredbu CREATE PROCEDURE (može biti skraćeno od PROC), čiji je format dat u nastavku:
CREATE (PROC I PROCEDURE) ime_proc [ ; broj]
[(gparametar data_type )
[“podrazumevano] |
[WITH
[ ZA REPLIKACIJU ]
AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )
Ako je pohranjena procedura (ili okidač, funkcija, pogled) kreirana s opcijom ENCRYPTION, njen kod se pretvara na takav način da tekst postaje nečitljiv. U isto vrijeme, kao što je navedeno u , korišteni algoritam se prenosi iz ranijih verzija SQL Servera i ne može se smatrati pouzdanim zaštitnim algoritmom - postoje uslužni programi koji vam omogućavaju da brzo izvršite obrnutu konverziju.
Opcija RECOMPILE specificira da svaki put kada se pozove procedura, sistem će ponovo kompajlirati tekst. U uobičajenom slučaju, procedura sastavljena pri prvom pokretanju pohranjuje se u keš memoriju, što vam omogućava da povećate performanse.
EXECUTE AS definira sigurnosni kontekst u kojem se procedura treba izvršiti. Zatim, jedna od vrijednosti f CALLER | SELF | VLASNIK | "user_name"). CALLER je zadana vrijednost i znači da će se kod izvršiti u sigurnosnom kontekstu korisnika koji poziva ovaj modul. U skladu s tim, korisnik mora imati dozvole ne samo za sam objekt koji se može programirati, već i za druge objekte baze podataka na koje utiče. IZVRŠITI KAO SAM znači korištenje konteksta korisnika koji kreira ili modificira objekt koji se može programirati. OWNER specificira da će se kod izvršiti u kontekstu trenutnog vlasnika procedure. Ako za njega nije definiran vlasnik, tada se pretpostavlja vlasnik sheme kojoj pripada. EXECUTE AS "user_name" vam omogućava da eksplicitno navedete korisničko ime (u jednostrukim navodnicima).
Parametri se mogu specificirati za proceduru. Ovo su lokalne varijable koje se koriste za prosljeđivanje vrijednosti proceduri. Ako je parametar deklarisan ključnom riječi OUTPUT (ili skraćeno OUT), to je izlaz: vrijednost koja mu je data u proceduri može se koristiti od strane programa koji je pozvao proceduru nakon završetka procedure. Ključna riječ READONLY znači da se vrijednost parametra ne može promijeniti unutar pohranjene procedure.
Parametrima se mogu dodijeliti zadane vrijednosti, koje će se koristiti ako vrijednost parametra nije eksplicitno specificirana prilikom pozivanja procedure. Razmotrimo primjer:
CREATE PROC surma (@a int, @b int=0,
©rezultat int IZLAZ) AS
SET @result=0a+0b
Napravili smo proceduru sa tri parametra, a parametar @b ima zadanu vrijednost =0, a parametar @result je izlazni parametar: preko njega se vrijednost vraća pozivnom programu. Radnje koje se izvode su prilično jednostavne - izlazni parametar prima vrijednost zbira dva ulaza.
Kada radite u SQL Server Management Studio-u, kreirana uskladištena procedura može se naći u odeljku programabilnih objekata baze podataka (engleski) Programabilnost) u odjeljku za pohranjene procedure (slika 10.2).
Prilikom pozivanja procedure, i varijable i konstante se mogu koristiti kao ulazni parametri. Razmotrimo dva primjera. U prvom, ulazni parametri procedure su eksplicitno postavljeni konstantama, ključna riječ OUTPUT je specificirana za izlazni parametar u pozivu. U drugoj opciji, vrijednost varijable se koristi kao prvi ulazni parametar, a drugi parametar je specificiran pomoću ključne riječi DEFAULT da treba koristiti zadanu vrijednost:
Rice. 10.2.
DECLARE @sa int;
EXEC suma 10,5,@c OUTPUT;
PRINT0c; - 15 će biti prikazano
DECLARE Gi int = 5;
- prilikom pozivanja koristite zadanu vrijednost
EXEC suma Gi,DEFAULT, 0s OUTPUT;
PRINT0c; - 5 će biti prikazano
Razmotrimo sada primjer s analizom povratnog koda s kojim se završava procedura. Neka je potrebno izračunati koliko je knjiga u Booklovoj tabeli objavljeno u određenom rasponu godina. U ovom slučaju, ako se ispostavilo da je početna godina veća od konačne, procedura vraća "1" i ne računa se, u suprotnom računamo broj knjiga i vraćamo 0:
CREATE PROC dbo.rownum(0FirsYear int, GLastYear int, 0result int OUTPUT) AS
IF 0FirsYear>0LastYear POVRATAK 1
SET @result= (ODABIR COUNT(*) IZ dbo.Bookl
GDJE IZMEĐU 0Prve godine I 0Prošle godine) ;
Razmotrimo varijantu pozivanja ove procedure, u kojoj se povratni kod pohranjuje u cjelobrojnu varijablu 0ret, nakon čega se analizira njena vrijednost (u ovom slučaju će biti 1). Funkcija CAST koja se koristi u naredbi PRINT koristi se za pretvaranje vrijednosti Gres cjelobrojne varijable u tip stringa:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret=l PRINT "Početna godina je veća od krajnje godine"
ŠTAMPA "Broj knjiga "+ CAST(Gres kao varchar(20))
Pohranjene procedure mogu ne samo čitati podatke iz tablice, već i modificirati podatke, pa čak i kreirati tabele i niz drugih objekata baze podataka.
Međutim, sheme, funkcije, okidači, procedure i pogledi ne mogu se kreirati iz pohranjene procedure.
Sljedeći primjer ilustruje i ove mogućnosti i probleme u vezi s opsegom privremenih objekata. Sljedeća pohranjena procedura provjerava postojanje privremene tabele #Tab2; ako ova tabela ne postoji, kreira je. Nakon toga se vrijednosti dvije kolone unose u tablicu #Tab2, a sadržaj tabele se prikazuje naredbom SELECT:
CREATE PROC My_Procl (@id int, @name varchar(30))
AKO JE OBJECT_ID("tempdb.dbo.#Tab21) NULL
INSERT INTO dbo.#Tab2 (id, name)VRIJEDNOSTI (0id,0name)
SELECT * IZ dbo. #Tab2 -#1
Prije prvog poziva pohranjene procedure, napravimo privremenu tablicu #Tab2 koja se koristi u njoj. Obratite pažnju na EXEC operatera. U prethodnim primjerima parametri su proslijeđeni proceduri "po poziciji", ali u ovom slučaju se koristi drugačiji format za prosljeđivanje parametara - "po imenu", naziv parametra i njegova vrijednost su eksplicitno naznačeni:
CREATE TABLE dbo.#Tab2 (id int, ime varchar(30));
EXEC My_Procl 0name="lvan", 0id=2;
SELECT * FROM dbo.#Tab2; –#2
U gornjem primjeru, naredba SELECT će se izvršiti dva puta: prvi put - unutar procedure, drugi put - iz fragmenta pozivnog koda (označenog komentarom "br. 2").
Prije drugog poziva procedure, izbrišemo privremenu tablicu #Tab2. Tada će se iz pohranjene procedure kreirati privremena tablica istog imena:
DROP TABLE dbo.#Tab2;
EXEC My_Procl 0name="Ivan", 0id=2;
SELECT * FROM dbo.#Tab2; –#2
U ovom slučaju, samo izraz SELECT unutar procedure (sa komentarom "Xa 1") će prikazati podatke. Izvršavanje SELECT "#2" će rezultirati greškom, jer će privremena tablica kreirana u pohranjenoj proceduri već biti izbrisana iz tempdb baze podataka kada se procedura vrati.
Možete ispustiti pohranjenu proceduru koristeći izraz DROP PROCEDURE. Njegov format je prikazan ispod. S jednom naredbom možete izbrisati nekoliko pohranjenih procedura tako što ćete ih navesti odvojene zarezima:
DROP (PROCED I POSTUPAK) (procedura) [
Na primjer, uklonimo prethodno kreiranu proceduru suma:
DROP PROC summa;
Možete napraviti promjene u postojećoj proceduri (u stvari, nadjačati je) koristeći izraz ALTER PROCEDURE (dozvoli
skraćenica PROC). Sa izuzetkom ključne riječi ALTER, format iskaza je gotovo isti kao kod CREATE PROCEDURE. Na primjer, promijenimo proceduru dbo. rownum postavljanjem da se izvršava u vlasničkom sigurnosnom kontekstu:
ALTER PROC dbo.rownum(SFirsYear int,
SLastYear int, Result int OUTPUT)
SA IZVRŠITI KAO Vlasnik - postavljena opcija
IF 0FirsYear>0LastYear RETURN 1 ELSE POČNI
POSTAVI 0rezultat= (IZABIR BROJ(*) IZ dbo.Bookl
GDJE IZMEĐU SFirsYear I SLastYear);
U nekim slučajevima može biti potrebno dinamički generirati naredbu i izvršiti je na poslužitelju baze podataka. Ovaj zadatak se također može riješiti korištenjem EXEC operatora. Sljedeći primjer dohvaća zapise iz tablice Bookl na osnovu uvjeta da je atribut Year jednak vrijednosti specificiranoj varijablom:
DECLARE 0y int = 2000;
EXEC("SELECT * FROM dbo.Bookl WHERE = " [email protected]) ;
Izvršenje dinamički generiranih instrukcija stvara preduvjete za implementaciju kompjuterskih napada kao što je "SQL injekcija" (engleski) SQL injekcija). Suština napada je da počinitelj ubrizgava svoj vlastiti SQL kod u dinamički generirani upit. Ovo se obično dešava kada se inline parametri uzimaju iz rezultata unosa korisnika.
Promenimo malo prethodni primer:
DECLARE 0y varchar(100);
SET 0y="2OOO"; - ovo je ono što smo dobili od korisnika
Ako pretpostavimo da smo primili vrijednost niza dodijeljenu u SET izrazu od korisnika (bez obzira na to kako, na primjer, putem web aplikacije), tada primjer ilustruje "uobičajeno" ponašanje našeg koda.
DECLARE 0y varchar(100);
SET 0y="2000; IZBRIŠI IZ dbo.Book2"; – injekcija
EXEC("SELECT * FROM dbo.Book2 WHERE="+0y);
Preporučljivo je, kad god je to moguće, koristiti sistemsku pohranjenu proceduru sp_executcsql u takvim slučajevima, koja vam omogućava kontrolu tipa parametara, što je jedna od prepreka za SQL injekciju. Bez detaljnijeg razmatranja njegovog formata, analizirat ćemo primjer sličan prethodno predstavljenom:
EXECUTE sp_executesql
N"SELECT * FROM dbo.Bookl WHERE =0y",
Ovo eksplicitno specificira tip parametra koji se koristi u upitu, a SQL Server će ga kontrolirati tokom izvršavanja. Slovo "N" ispred navodnika označava da je ovo Unicode literalna konstanta, kako to zahtijeva procedura. Parametru se može dodijeliti ne samo konstantna vrijednost, već i vrijednost druge varijable.
Pohranjena procedura je poseban tip grupe Transact-SQL iskaza 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 se pohranjuju na strani servera kako bi se poboljšale performanse i konzistentnost zadataka koji se ponavljaju.
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 uzeti 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, odgovarajuću 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 eliminacije (u skoro svim slučajevima) ponovne kompilacije procedura i odgovarajuće poboljšanje 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 od 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 dnevnika o akcijama sa tabelama baze podataka.
Upotreba uskladištenih procedura obezbeđuje nivo bezbednosne kontrole koji nadilazi sigurnost koju pruža upotreba 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 pohranjenoj proceduri, kao što je opisano u sljedećem odjeljku.
Pohranjene procedure koje upisuju u dnevnik i/ili čitaju tablice pružaju dodatnu sigurnost za bazu podataka. Koristeći takve procedure, administrator baze podataka može pratiti modifikacije 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] )] (, …) KAO batch | EXTERNAL NAME method_name Sintaksne konvencije
Parametar schema_name specificira ime sheme koje je dodijelio 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 specificira default vrijednost za odgovarajući parametar procedure. (Zadana vrijednost također može biti NULL.)
OUTPUT opcija specificira da se parametar procedure može vratiti i da 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, deklaracija procedure koristi opcija SA RECOMPILE. Korištenje opcije WITH RECOMPILE negira jednu od najvažnijih prednosti pohranjenih procedura: poboljšanje performansi zbog jedne kompilacije. Stoga, opciju WITH RECOMPILE treba koristiti samo ako postoje česte promjene objekata baze podataka koje koristi pohranjena procedura.
IZVRŠITI KAO ponudu definira sigurnosni kontekst u kojem se pohranjena procedura mora izvršiti nakon što je pozvana. Postavljanjem ovog konteksta, Database Engine može kontrolirati izbor korisničkih računa 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. Međutim, č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; KRAJTE PROCEDURU Povećajte Budžet (@percent INT=5) KAO AŽURIRANJE Projekt SET Budžet = Budžet + Budžet * @percent/100;
Kao što je ranije spomenuto, za razdvajanje dva paketa koristi se GO uputstvo. Izraz CREATE PROCEDURE ne može se kombinirati s drugim Transact-SQL izrazima u istoj grupi. Pohranjena procedura IncreaseBudget povećava budžete za sve projekte za određeni postotak, specificiran parametrom @percent. Procedura također definira zadanu vrijednost procenta (5) koja se primjenjuje ako ovaj argument nije prisutan tokom izvršavanja procedure.
Pohranjene procedure mogu pristupiti tablicama koje ne postoje. Ovo svojstvo vam omogućava da otklonite greške u kodu procedure bez prethodnog kreiranja odgovarajućih tabela i bez 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 ponavljanog izvršavanja određene grupe izraza prilikom povezivanja na bazu podataka. Možete kreirati lokalne ili globalne privremene procedure. Da biste to učinili, ime lokalne procedure navodi se jednim znakom # (#proc_name), a ime globalne procedure je navedeno dvostrukim znakom (##proc_name).
Lokalnu privremenu pohranjenu proceduru može izvršiti samo korisnik koji ju je kreirao i to samo za vrijeme konekcije na bazu podataka u kojoj je kreirana. Globalnu privremenu proceduru mogu izvršiti svi korisnici, ali samo dok se ne prekine posljednja veza na kojoj je pokrenuta (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 i izvršava više puta. Pohranjenu proceduru izvršava EXECUTE iskazi korisnik koji posjeduje proceduru ili ima EXECUTE pravo pristupa proceduri. Naredba EXECUTE ima sljedeću sintaksu:
[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] vrijednost | [@parameter1=] @variable ] | DEFAULT).. Sintaksne konvencije
Sa izuzetkom parametra return_status, svi parametri izraza EXECUTE imaju istu logičku vrijednost kao i parametri istog imena CREATE PROCEDURE izraza. Parametar return_status definira cjelobrojnu varijablu koja pohranjuje povratni status procedure. Vrijednost se može dodijeliti parametru pomoću konstante (vrijednost) ili lokalne varijable (@varijable). Redoslijed imenovanih vrijednosti parametara nije važan, ali neimenovane vrijednosti parametara moraju biti navedene redoslijedom kojim su definirane u naredbi CREATE PROCEDURE.
DEFAULT klauzula daje zadane vrijednosti za parametar procedure koji je naveden u definiciji procedure. Kada procedura očekuje vrijednost za parametar za koji nije definirana zadana vrijednost i parametar nedostaje, ili je specificirana ključna riječ DEFAULT, dolazi do greške.
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 svih projekata za 10%.
Sljedeći primjer 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). Takva pohranjena procedura može se koristiti unutar definicije okidača, koja zapravo provodi referentni integritet.
Sljedeći primjer pokazuje upotrebu OUTPUT klauzule u pohranjenoj proceduri:
Ova pohranjena procedura može se izvršiti korištenjem sljedećih izraza:
DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @empId=18316, @ [email protected] OUTPUT; PRINT N"Zaposleni izbrisani: " + convert(nvarchar(30), @quantityDeleteEmployee);
Ova procedura broji broj projekata na kojima radi zaposlenik sa kadrovskim brojem @empId i dodjeljuje rezultirajuću vrijednost parametru ©counter. 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 Serveru 2012 upisuje se izraz EXECUTE WITH RESULTS SETS klauzula A koji, pod određenim uslovima, može 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 odjelu. Broj odjela je parametar procedure i mora biti specificiran prilikom pozivanja procedure. Izvođenje ove procedure daje tabelu sa dvije kolone čiji naslovi odgovaraju imenima odgovarajućih kolona u tabeli baze podataka, tj. id i prezime. Za promjenu zaglavlja stupaca rezultata (kao i njihov tip podataka), SQL Server 2012 koristi novu klauzulu WITH RESULTS SETS. Primjena ove klauzule 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 možete vidjeti, pokretanje pohranjene procedure korištenjem klauzule WITH RESULT SETS u naredbi EXECUTE omogućava vam da promijenite imena i tip podataka kolona skupa rezultata koje proizvodi 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. Naredba 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 naredbe CREATE PROCEDURE istog imena. Primarna svrha korištenja ove izjave je izbjegavanje nadjačavanja postojećih dozvola pohranjenih procedura.
Database Engine podržava CURSOR tip podataka. 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.
Da biste uklonili jednu ili grupu pohranjenih procedura, koristite DROP PROCEDURE izraz. Samo vlasnik pohranjene procedure ili članovi fiksnih uloga db_owner i sysadmin mogu izbrisati pohranjenu proceduru.
Pohranjene procedure i vrijeme izvođenja zajedničkog jezika
SQL Server podržava Common Language Runtime (CLR) koji vam omogućava da razvijete različite objekte baze podataka (pohranjene procedure, korisnički definirane funkcije, okidači, korisnički definirani agregati i korisnički definirani tipovi podataka) koristeći C# i Visual Basic. Zajedničko vrijeme izvođenja također omogućava da se ovi objekti izvršavaju korištenjem zajedničkog sistema vremena izvršavanja.
Vrijeme izvođenja zajedničkog jezika je omogućeno i onemogućeno preko opcije clr_enabled sistemska procedura sp_configure, koji se pokreće za izvršenje po instrukciji RECONFIGURE. Sljedeći primjer pokazuje kako možete omogućiti vrijeme izvođenja zajedničkog jezika koristeći sistemsku proceduru sp_configure:
USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE
Kreiranje, kompajliranje i spremanje procedure pomoću CLR-a zahtijeva sljedeći niz koraka, navedenim redoslijedom:
Kreirajte pohranjenu proceduru u C# ili Visual Basic-u, a zatim je prevedite pomoću odgovarajućeg kompajlera.
Korištenje instrukcija CREATE ASSEMBLY, kreirajte odgovarajuću izvršnu datoteku.
Izvršite proceduru koristeći naredbu EXECUTE.
Slika ispod prikazuje grafički dijagram prethodno navedenih koraka. U nastavku slijedi detaljniji opis ovog procesa.
Prvo kreirajte željeni 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 pohranjen u datoteci biblioteke dinamičkih veza (.dll) koja služi kao izvor za naredbu CREATE ASSEMBLY, koja kreira srednji izvršni kod. Zatim izdajte izraz CREATE PROCEDURE za spremanje koda koji se izvršava kao objekt baze podataka. Konačno, pokrenite proceduru koristeći poznatu naredbu EXECUTE.
Primjer ispod prikazuje izvorni kod pohranjene procedure u C#:
Using System.Data.SqlClient; koristeći Microsoft.SqlServer.Server; javna parcijalna klasa StoredProcedures ( public static 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. Korištenje direktiva na početku programa specificira prostore imena potrebne za njegovo izvršavanje. Upotreba 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. Unutar koda klase definirana je metoda CountEmployees(). Veza sa sistemom baze podataka uspostavlja se preko instance klase SqlConnection. Za otvaranje veze koristi se Open() metoda ove instance. A CreateCommand() metoda omogućava vam pristup instanci klase SqlCommnd, kojoj se prosljeđuje željena SQL naredba.
U sljedećem isječku koda:
Cmd.CommandText = "odaberite count(*) 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-a. Ova metoda vraća skalarnu vrijednost koja se konvertuje u cjelobrojni tip podataka int i dodjeljuje varijabli rows.
Sada možete kompajlirati ovaj kod koristeći Visual Studio. Dodao sam ovu klasu u projekat sa imenom CLRStoredProcedures, tako da će Visual Studio kompajlirati istoimeni sklop sa *.dll ekstenzijom. Primjer ispod pokazuje sljedeći korak u kreiranju pohranjene procedure: kreiranje koda za pokretanje. Prije izvršavanja koda u ovom primjeru, morate znati lokaciju kompajlirane .dll datoteke (obično se nalazi u fascikli za otklanjanje grešaka u projektu).
USE SampleDb; IDI KREIRATI ASSEMBLY CLRStoredProcedures IZ "D:\Projects\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 pohranjene procedure zajedničkog jezika runtime (CLR), korisnički definirane funkcije i okidače. Ova instrukcija ima sljedeću sintaksu:
CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM (dll_file) Sintaksne konvencije
Parametar 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 je treba specificirati. Definira skup prava pristupa koja se dodjeljuju asemblerskom kodu. SAFE set prava je najrestriktivniji. Asemblerski kod koji ima ova prava ne može pristupiti vanjskim sistemskim resursima kao što su datoteke. Skup prava EXTERNAL_ACCESS dozvoljava asemblerskom kodu da pristupi određenim vanjskim sistemskim resursima, dok UNSAFE skup prava pruža neograničen pristup resursima, kako unutar tako i izvan sistema baze podataka.
Za pohranjivanje informacija o asemblerskom kodu, korisnik mora biti u mogućnosti izdati naredbu CREATE ASSEMBLY. Sklop je u vlasništvu korisnika (ili uloge) koji izvršava instrukciju. Možete promijeniti vlasnika sklopa korištenjem klauzule AUTHORIZATION izraza 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 instrukcija 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.
Sljedeći primjer pokazuje kako kreirati pohranjenu proceduru na osnovu upravljanog koda implementiranog ranije:
USE SampleDb; IDI CREATE PROCEDURE CountEmployees KAO VANJSKI IME CLRStoredProcedures.StoredProcedures.CountEmployees
Naredba CREATE PROCEDURE u primjeru razlikuje se od iste naredbe u ranijim primjerima po tome što sadrži EXTERNAL NAME parametar. Ova opcija specificira da kod generira CLR. Ime u ovoj rečenici sastoji se od tri dijela:
assembly_name.class_name.method_name
assembly_name - specificira ime sklopa;
class_name - specificira ime opšte klase;
method_name - opcioni dio, specificira ime metode koja je postavljena 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.
U Microsoft SQL Server za implementaciju i automatizaciju vlastitih algoritama ( kalkulacije) možete koristiti pohranjene procedure, pa ćemo danas pričati o tome kako se kreiraju, mijenjaju i brišu.
Ali prvo, malo teorije da shvatite šta su pohranjene procedure i čemu služe u T-SQL-u.
Bilješka! Programerima početnicima preporučujem sljedeće korisne materijale na temu T-SQL:
- Za detaljnije proučavanje T-SQL jezika preporučujem i čitanje knjige - Put T-SQL programera. Transact-SQL vodič.
Šta su pohranjene procedure u T-SQL-u?
Pohranjene procedure- To su objekti baze podataka u koje je algoritam ugrađen u obliku skupa SQL instrukcija. Drugim riječima, možemo reći da su pohranjene procedure programi unutar baze podataka. Pohranjene procedure se koriste za pohranjivanje koda za višekratnu upotrebu na serveru, na primjer, napisali ste algoritam, sekvencijalno izračunavanje ili višestepenu SQL naredbu, a kako ne biste svaki put izvršili sve instrukcije uključene u ovaj algoritam, možete organizirati to kao pohranjena procedura. Istovremeno, kada kreirate SQL proceduru, server kompajlira kod, a zatim, svaki put kada pokrenete ovu proceduru, SQL server ga više neće kompajlirati ponovo.
Da biste pokrenuli pohranjenu proceduru u SQL Serveru, potrebno je napisati naredbu EXECUTE prije njenog imena, također je moguće napisati ovu EXEC naredbu skraćeno. Pozovite pohranjenu proceduru u SELECT izrazu, na primjer, jer funkcija više neće raditi, tj. procedure se vode odvojeno.
U pohranjenim procedurama, za razliku od funkcija, već je moguće izvršiti operacije modifikacije podataka kao što su: UNSERT, UPDATE, DELETE. Također, u procedurama možete koristiti gotovo bilo koju vrstu SQL izraza, na primjer, CREATE TABLE za kreiranje tablica ili EXECUTE, tj. pozivanje drugih procedura. Izuzetak je nekoliko tipova instrukcija kao što su: kreiranje ili promjena funkcija, pogleda, okidača, kreiranje shema i nekoliko drugih sličnih instrukcija, na primjer, također je nemoguće prebaciti kontekst povezivanja baze podataka (USE) u pohranjenoj proceduri.
Pohranjena procedura može imati ulazne parametre i izlazne parametre, može vratiti tabelarne podatke, ne može ništa vratiti, već samo izvršiti instrukcije sadržane u njoj.
Pohranjene procedure su vrlo korisne, pomažu nam da automatiziramo ili pojednostavimo mnoge operacije, na primjer, stalno trebate generirati razne složene analitičke izvještaje koristeći pivot tablice, tj. PIVOT operator. Da biste pojednostavili formiranje upita sa ovim operatorom ( kao što znate, PIVOT sintaksa je prilično komplikovana), možete napisati proceduru koja će za vas dinamički generirati zbirne izvještaje, na primjer, u materijalu „Dinamički PIVOT u T-SQL-u“, prikazan je primjer implementacije ove funkcije u obliku pohranjene procedure.
Primjeri rada sa pohranjenim procedurama u Microsoft SQL Serveru
Početni podaci za primjere
Svi primjeri u nastavku će biti izvedeni u Microsoft SQL Server 2016 Express. Da bismo pokazali kako pohranjene procedure rade sa stvarnim podacima, potrebni su nam ovi podaci, hajde da ih kreiramo. Na primjer, napravimo probnu tabelu i dodamo joj neke zapise, recimo da će to biti tabela koja sadrži listu proizvoda sa njihovom cijenom.
Naredba za kreiranje tabele CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Dodaj iskaz podataka INSERT INTO TestTable(CategoryId, ProductName, Price) VRIJEDNOSTI (1 , "Miš", 100), (1, "Tastatura", 200), (2, "Telefon", 400) IDI -- ODABERI * IZ upita TestTable
Postoje podaci, sada idemo na kreiranje pohranjenih procedura.
Kreiranje pohranjene procedure u T-SQL - naredba CREATE PROCEDURE
Pohranjene procedure se kreiraju pomoću izraza CREATE PROCEDURE, nakon ove instrukcije morate napisati naziv vaše procedure, a zatim, ako je potrebno, definirati ulazne i izlazne parametre u zagradama. Nakon toga upišete ključnu riječ AS i otvorite blok instrukcija sa ključnom riječi BEGIN, zatvorite ovaj blok riječju END. Unutar ovog bloka pišete sve instrukcije koje implementiraju vaš algoritam ili neku vrstu sekvencijalnog proračuna, drugim riječima, programirate u T-SQL-u.
Na primjer, napišimo pohranjenu proceduru koja će dodati novi zapis, tj. nova stavka u našem testnom grafikonu. Da bismo to uradili, definisat ćemo tri ulazna parametra: @CategoryId - identifikator kategorije proizvoda, @ProductName - naziv proizvoda i @Price - cijena proizvoda, ovaj parametar će za nas biti opcioni, tj. ne može se proći u proceduru ( na primjer, još ne znamo cijenu), za ovo ćemo postaviti zadanu vrijednost u njegovoj definiciji. Ovi parametri se nalaze u tijelu procedure, tj. u bloku BEGIN…END može se koristiti na isti način kao i obične varijable ( kao što znate, varijable su označene znakom @). Ako trebate navesti izlazne parametre, onda nakon naziva parametra navedite ključnu riječ OUTPUT ( ili skraćeno OUT).
U blok BEGIN…END upisaćemo instrukciju za dodavanje podataka, a na kraju procedure i naredbu SELECT tako da pohranjena procedura vraća tabelarne podatke o proizvodima u navedenoj kategoriji, uzimajući u obzir nove, upravo dodat proizvod. Takođe u ovoj uskladištenoj proceduri dodao sam obradu ulaznog parametra, odnosno uklanjanje dodatnih razmaka na početku i na kraju tekstualnog niza kako bi se izbegle situacije kada je nekoliko razmaka slučajno uneto.
Evo koda za ovu proceduru I ja sam to komentarisao).
Kreirajte proceduru CREATE PROCEDURE TestProcedure (--Dolazni parametri @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) KAO POČETAK --Upute koje implementiraju vaš algoritam --Obrada ulaznih parametara --Uklanjanje dodatnih razmaka na početku i na kraju tekstualnog niza SET @ProductName = LTRIM(RTRIM(@ProductName)); --Dodaj novi unos INSERT INTO TestTable(CategoryId, ProductName, Price) VRIJEDNOSTI (@CategoryId, @ProductName, @Price) --Vrati podatke SELECT * IZ TestTable GDJE CategoryId = @CategoryId END GO
Pokretanje pohranjene procedure u T-SQL - naredba EXECUTE
Možete pokrenuti pohranjenu proceduru, kao što sam već primijetio, koristeći naredbu EXECUTE ili EXEC. Dolazni parametri se prosljeđuju procedurama jednostavnim nabrajanjem i davanjem odgovarajućih vrijednosti nakon naziva procedure ( za izlazne parametre, također morate specificirati naredbu OUTPUT). Međutim, naziv parametara možda neće biti specificiran, ali je u ovom slučaju potrebno pratiti redoslijed specificiranja vrijednosti, tj. navedite vrijednosti redoslijedom kojim su definirani ulazni parametri ( ovo se također odnosi na izlazne parametre).
Parametri koji imaju zadane vrijednosti možda neće biti specificirani, to su takozvani opcioni parametri.
Evo nekoliko različitih, ali ekvivalentnih načina za pokretanje pohranjenih procedura, posebno naše testne procedure.
1. Pozovite proceduru bez navođenja cijene EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. Proceduru sa navedenom cijenom zovemo EXEC TestProcedure @CategoryId = 1, @ProductName = "Test proizvod 2", @Price = 300 --3. Proceduru pozivamo bez navođenja imena EXEC parametara TestProcedure 1, "Test item 3", 400
Promjena pohranjene procedure u T-SQL - naredba ALTER PROCEDURE
Možete izvršiti promjene u algoritmu postupka koristeći upute ALTER PROCEDURE. Drugim riječima, da biste promijenili već postojeću proceduru, trebate samo napisati ALTER PROCEDURE umjesto CREATE PROCEDURE i promijeniti sve ostalo po potrebi.
Recimo da moramo napraviti promjene u našoj proceduri testiranja, recimo parametar @Price, tj. cijena, učinit ćemo to obaveznim, za ovo ćemo ukloniti zadanu vrijednost, a također zamislimo da više ne trebamo dobivati rezultujući skup podataka, za to ćemo jednostavno ukloniti SELECT naredbu iz pohranjene procedure.
Promijenite proceduru ALTER PROCEDURE TestProcedure (--Dolazni parametri @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) KAO POČETAK --Upute koje implementiraju vaš algoritam --Obrada ulaznih parametara --Uklonite dodatne razmake na početku i na kraju redova teksta SET @ProductName = LTRIM(RTRIM(@ProductName)); --Dodaj novi zapis INSERT INTO TestTable(CategoryId, ProductName, Price) VRIJEDNOSTI (@CategoryId, @ProductName, @Price) KRAJ GO
Brisanje pohranjene procedure u T-SQL - DROP PROCEDURE izrazu
Ako je potrebno, možete izbrisati pohranjenu proceduru, to se radi pomoću naredbe DROP POSTUPAK.
Na primjer, izbrišemo testnu proceduru koju smo kreirali.
DROP PROCEDURE TestProcedure
Prilikom brisanja pohranjenih procedura, vrijedi zapamtiti da ako se na proceduru pozivaju druge procedure ili SQL izrazi, nakon brisanja neće uspjeti s greškom, jer procedura na koju se pozivaju više ne postoji.
Imam sve, nadam se da vam je materijal bio zanimljiv i koristan, ćao!
