Pohranjene procedure. Kreiranje uskladištenih procedura na Microsoft sql serveru

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 poboljšati performanse prilikom njihovog izvršavanja eliminirajući potrebu za raščlanjivanjem, optimizacijom i kompajliranjem koda pohranjene 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; u skladu s tim, ako je kompajlirana pohranjena procedura keširana, tada je njen plan izvršenja keširan, pa stoga pohranjena procedura neće biti optimiziran za određenu situaciju i parametre upita.
Uradimo mali eksperiment da to demonstriramo.

KORAK 1. Kreiranje baze podataka.
Za eksperiment ćemo napraviti posebnu 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 testnim redovima. Duplicirani redovi se namjerno dodaju u tabelu. 10.000 redova sa brojevima od 1 do 10.000 i 10.000 redova sa brojevima od 50.000.

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. Kreiranje neklasterisanog indeksa. Budući da je plan izvršenja keširan s procedurom, indeks će se koristiti na isti način u svim pozivima.

CREATE NENCLUSTERED INDEX CL_perf_test NA sp_perf_test(kolona1)
GO

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 stupac1=@param
GO

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 ovo je najbolji način da se dohvati red. Procedura optimizirana za dohvaćanje jednog reda pohranjuje se u proceduralni keš.

EXEC proc1 1234
GO

KORAK 7. Pokrenite pohranjenu proceduru s neselektivnim parametrom. Vrijednost koja se koristi kao parametar je 50000. Postoji oko 10000 redova sa ovom vrijednošću u prvoj koloni, stoga je korištenje neklasteriranog indeksa i operacija pretraživanja oznake neefikasna, ali pošto je prevedeni kod sa planom izvršenja pohranjen u proceduralni keš, to je ono što će se koristiti. 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. Prilikom pokretanja zasebnog upita, upit se optimizira i kompajlira sa specifičnom vrijednošću za prvu kolonu. Kao rezultat toga, optimizator upita utvrđuje 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 indeksa koji nije klaster.

SELECT column1, column2 FROM sp_perf_test WHERE stupac1=50000
GO

Stoga možemo zaključiti da korištenje pohranjenih procedura ne može uvijek poboljšati performanse upita. Trebali biste biti vrlo oprezni u vezi s pohranjenim procedurama koje rade na rezultatima s promjenjivim brojem redova i koje koriste različite planove izvršenja.
Možete koristiti skriptu da ponovite eksperiment na vašem MS SQL serveru.

Vrste pohranjenih procedura

SQL Server ima nekoliko tipova pohranjene procedure.

• Sistem pohranjene procedure dizajniran za obavljanje raznih administrativnih radnji. Uz njihovu pomoć obavljaju se gotovo sve aktivnosti administracije servera. Možemo reći da je sistemski pohranjene procedure su interfejs koji omogućava rad sa sistemskim tabelama, koji se na kraju svodi na promenu, dodavanje, brisanje i preuzimanje podataka iz sistemskih tabela i korisničke i sistemske baze podataka. Sistem pohranjene procedure imaju sp_ prefiks, pohranjeni su u sistemskoj bazi podataka i mogu se pozvati u kontekstu bilo koje druge baze podataka.
• Custom pohranjene procedure sprovesti određene radnje. Pohranjene procedure– punopravni objekat baze podataka. Kao rezultat, svaki pohranjena procedura nalazi se u određenoj bazi podataka u kojoj se izvršava.
• Privremeno pohranjene procedure postoje samo neko vrijeme, nakon čega ih server automatski uništava. Dijele se na lokalne i globalne. Lokalno privremeno pohranjene procedure mogu biti pozvani samo iz veze u kojoj su kreirani. 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 ili se server ponovo pokrene ili zaustavi. Globalno privremeno pohranjene procedure dostupni su za sve veze sa servera koji ima istu proceduru. Da biste ga definirali, samo mu dajte ime koje počinje znakovima ## . Ove procedure se brišu kada se server ponovo pokrene ili zaustavi, ili kada se zatvori veza u kontekstu u kojem su kreirani.

Kreirajte, modificirajte i izbrišite pohranjene procedure

Kreacija pohranjena procedura uključuje rješavanje sljedećih problema:

• određivanje vrste kreiranog pohranjena procedura: privremeno ili prilagođeno. Osim toga, možete kreirati vlastiti sistem pohranjena procedura, dajući mu ime sa prefiksom sp_ i stavljajući ga u sistemsku bazu podataka. Ova procedura će biti dostupna u kontekstu bilo koje baze podataka lokalnog servera;
• planiranje prava pristupa. Tokom stvaranja pohranjena procedura treba uzeti u obzir da će imati ista prava pristupa objektima baze podataka kao i korisnik koji ju je kreirao;
• definicija parametri pohranjene procedure. Slično procedurama uključenim 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 pozive drugima pohranjene procedure.

Kreiranje novog i mijenjanje postojećeg pohranjena procedura urađeno pomoću sljedeće naredbe:

<определение_процедуры>::= (CREATE | ALTER ) procedure_name [;broj] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]

Pogledajmo parametre ove naredbe.

Koristeći prefikse sp_, #, ##, kreirana procedura se može definirati kao sistemska ili privremena. Kao što vidite iz sintakse komande, nije dozvoljeno navesti ime vlasnika koji će biti vlasnik kreirane procedure, kao ni naziv baze podataka u kojoj bi se ona trebala nalaziti. Dakle, da bi se postavilo stvoreno pohranjena procedura u određenoj bazi podataka, morate izdati naredbu CREATE PROCEDURE u kontekstu te baze podataka. Prilikom okretanja od tijela pohranjena procedura skraćena imena mogu se koristiti za objekte iste baze podataka, tj. bez navođenja imena baze podataka. Kada trebate pristupiti objektima koji se nalaze u drugim bazama podataka, navođenje imena baze podataka je obavezno.

Broj u imenu je identifikacioni broj pohranjena procedura, što ga jedinstveno identificira u grupi procedura. Radi lakšeg upravljanja, procedure su logično iste vrste pohranjene procedure mogu se grupisati dajući im isto ime ali različite identifikacione brojeve.

Za prijenos ulaznih i izlaznih podataka u kreirani 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 parametara odvojenih zarezima. Tijelo procedure ne bi trebalo koristiti lokalne varijable čija se imena poklapaju s imenima parametara ove procedure.

Da biste odredili tip podataka koji odgovara parametar pohranjene procedure, svi SQL tipovi podataka su prikladni, uključujući one koje definira korisnik. Međutim, tip podataka CURSOR se može koristiti samo kao izlazni parametar pohranjena procedura, tj. specificirajući ključnu riječ 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 ne znači da parametar nije prikladan za prosljeđivanje vrijednosti pohranjena procedura. Određivanje ključne riječi OUTPUT daje instrukcije poslužitelju da izađe pohranjena procedura dodijeli trenutnu vrijednost parametra lokalnoj varijabli koja je specificirana prilikom poziva procedure 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. Nisu dozvoljeni svi izrazi ili konstante koje su dozvoljene za regularne parametre.

Ključna riječ VARYING se koristi zajedno sa

Pohranjene procedure

Predmet ovog poglavlja je jedan od najmoćnijih alata koji se nudi programerima InterBase aplikacija baze podataka za implementaciju poslovne logike. Pohranjene procedure (engleski, stoied proceduies) omogućavaju vam da implementirate značajan dio logike aplikacije na nivou baze podataka i na taj način povećate performanse cijele aplikacije, centralizirati obradu podataka i smanjiti količinu koda potrebnog za dovršenje dodijeljenih zadataka Gotovo svaka prilično složena aplikacija baze podataka ne može bez upotrebe pohranjenih procedura.
Pored ovih dobro poznatih prednosti korištenja pohranjenih procedura, uobičajenih za većinu relacijskih DBMS-ova, InterBase pohranjene procedure mogu djelovati kao gotovo potpuni skupovi podataka, omogućavajući da se rezultati koje vraćaju koriste u običnim SQL upitima.
Često programeri početnici zamišljaju pohranjene procedure jednostavno kao skup specifičnih SQL upita koji rade nešto unutar baze podataka, a postoji mišljenje da je rad sa pohranjenim procedurama mnogo teži od implementacije iste funkcionalnosti u klijentskoj aplikaciji, u visokoj nivo jezika
Dakle, šta su pohranjene procedure u InterBase-u?
Pohranjena procedura (SP) je dio metapodataka baze podataka, koji je potprogram kompajliran u internu reprezentaciju InterBase-a, napisan na posebnom jeziku, čiji je kompajler ugrađen u jezgro InteiBase servera
Pohranjena procedura se može pozvati iz klijentskih aplikacija, iz okidača i iz drugih pohranjenih procedura. Pohranjena procedura se pokreće unutar serverskog procesa i može manipulirati podacima u bazi podataka, kao i vratiti rezultate svog izvršavanja klijentu koji ju je pozvao (tj. okidač, HP, aplikacija)
Osnova moćnih mogućnosti svojstvenih HP-u je proceduralni programski jezik, koji uključuje modificirane izraze regularnog SQL-a, kao što su INSERT, UPDATE i SELECT, kao i alate za organiziranje grana i petlji (IF, WHILE), kao i Alati za rukovanje greškama i izuzetne situacije Jezik uskladištenih procedura omogućava implementaciju složenih algoritama za rad sa podacima, a zbog fokusa na rad sa relacionim podacima, HP je mnogo kompaktniji od sličnih procedura u tradicionalnim jezicima.
Treba napomenuti da se isti programski jezik koristi za okidače, sa izuzetkom brojnih karakteristika i ograničenja. Razlike između podskupa jezika koji se koristi u okidačima i HP jezika detaljno su razmotrene u poglavlju “Okidači” (1. dio).

Primjer jednostavne pohranjene procedure

Vrijeme je da kreirate svoju prvu pohranjenu proceduru i koristite je kao primjer da naučite proces kreiranja pohranjenih procedura. Ali prvo treba reći nekoliko riječi o tome kako raditi sa pohranjenim procedurama.Činjenica je da HP svoju reputaciju opskurnog i nezgodnog alata duguje izuzetno lošim standardnim alatima za razvoj i otklanjanje grešaka pohranjenih procedura. InterBase dokumentacija preporučuje kreiranje procedura pomoću datoteka SQL skripte koje sadrže HP tekst, koji se dostavljaju kao ulaz u isql interpreter, i na taj način kreiranje i modificiranje HP If u ovoj SQL skripti, u fazi kompajliranja teksta procedure u BLR (oko BLR, pogledajte Poglavlje "Struktura baze podataka InterBase" (4. dio)) ako dođe do greške, isql će prikazati poruku o tome na kojoj liniji datoteke SQL skripte se ova greška dogodila. Ispravite grešku i ponovite sve iznova. O otklanjanju grešaka u modernom smislu te riječi, odnosno o praćenju izvršavanja, uz mogućnost pregleda međuvrijednosti varijabli, uopće nema govora. Očigledno, ovaj pristup ne doprinosi rastu atraktivnosti pohranjenih procedura u očima programera.
Međutim, pored standardnog minimalističkog pristupa HP razvoju<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Sintaksa pohranjenih procedura je opisana kako slijedi:

CREATE PROCEDURE ime
[ (param datatype [, param datatype ...]) ]
)]
AS
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE var datatype;

=
POČNI
< compound_statement>
[< compound_statement> ...]
KRAJ
< compound_statement> = (izjava ;)

Izgleda prilično obimno i možda čak i glomazno, ali u stvari je sve vrlo jednostavno.Da bismo postepeno savladali sintaksu, pogledajmo postepeno složenije primjere.
Dakle, evo primjera vrlo jednostavne pohranjene procedure koja uzima dva broja kao ulaz, zbraja ih i vraća rezultat:

CREATE PROCEDURE SP_Dodaj(prvi_arg DOUBLE PRECIZNOSTI,
second_arg DVOSTRUKA PRECIZNOST)
POVRATAK (Rezultat DOUBLE PRECISION)
AS
POČNI
Rezultat=prvi_arg+drugi_arg;
SUSPEND;
KRAJ

Kao što vidite, sve je jednostavno: nakon naredbe CREATE PROCEDURE navodi se naziv novokreirane procedure (koja mora biti jedinstvena unutar baze podataka) - u ovom slučaju SP_Add, zatim su HP ulazni parametri - first_arg i second_arg - navedeni u zagradama, odvojeni zarezima, označavajući njihove vrste.
Lista ulaznih parametara je opcioni dio naredbe CREATE PROCEDURE - postoje slučajevi kada procedura prima sve podatke za svoj rad kroz upite do tabela unutar tijela procedure.

Pohranjene procedure koriste bilo koje skalarne tipove podataka InteiBase Ne dozvoljava korištenje nizova i korisnički definiranih tipova - domena

Slijedi ključna riječ RETURNS, nakon koje su vraćeni parametri navedeni u zagradama, označavajući njihove tipove – u ovom slučaju samo jedan – Rezultat.
Ako procedura ne bi trebala vratiti parametre, tada nedostaje riječ RETURNS i lista vraćenih parametara.
Nakon RETURNSQ specificira se ključna riječ AS. Prije ključne riječi AS ide naslov, a nakon toga - techo procedure.
Tijelo pohranjene procedure je lista opisa njenih internih (lokalnih) varijabli (ako ih ima, pogledat ćemo ih detaljnije u nastavku), odvojenih točkom i zarezom (;) i blokom naredbi zatvorenih u operatorskim zagradama POČNI KRAJ. U ovom slučaju, tijelo HP-a je vrlo jednostavno - tražimo da dodamo dva ulazna argumenta i dodijelimo njihov rezultat jednom izlaznom, a zatim pozivamo SUSPEND naredbu. Malo kasnije ćemo objasniti suštinu radnje ove naredbe, ali za sada ćemo samo napomenuti da je potrebno prenijeti povratne parametre tamo odakle je pohranjena procedura pozvana.

Ograničenja u pohranjenim procedurama

Imajte na umu da se izjava unutar procedure završava tačkom-zarezom (;). Kao što znate, tačka-zarez je standardni separator naredbi u SQL-u - to je signal SQL interpretatoru da je tekst naredbe upisan u cijelosti i da bi trebao početi s obradom. Ne bi li se ispostavilo da će, ako SQL interpretator pronađe tačku-zarez u sredini HP-a, pretpostaviti da je naredba unesena u potpunosti i pokušat će izvršiti dio pohranjene procedure? Ova pretpostavka nije bez osnova. Zaista, ako kreirate datoteku u koju ćete napisati gornji primjer, dodati naredbu za povezivanje iz baze podataka i pokušati izvršiti ovu SQL skriptu koristeći isql interpreter, vratit će se greška zbog neočekivanog, po mišljenju tumača, završetka naredbe za kreiranje pohranjene procedure. Ako kreirate pohranjene procedure koristeći datoteke SQL skripte, bez korištenja specijaliziranih alata za razvojne programere InterBase, tada prije svake naredbe za kreiranje HP-a (isto vrijedi i za okidače) trebate promijeniti razdjelnik naredbi skripte u drugi znak osim točke-zarez, a nakon teksta HP da ga vratite nazad. Isql naredba koja mijenja separator SQL klauzule izgleda ovako:

SET TERM

Za tipičan slučaj kreiranja pohranjene procedure, to izgleda ovako:

SET TERM^;
CREATE PROCEDURE neka_procedura
... . .
KRAJ
^
SET TERM ;^

Pozivanje pohranjene procedure

No, vratimo se na našu pohranjenu proceduru. Sada kada je kreiran, morate ga nekako pozvati, proslijediti mu parametre i dobiti rezultate. Ovo je vrlo lako učiniti - samo napišite SQL upit ovako:

ODABIR *
OD Sp_add(181.35, 23.09)

Ovaj upit će nam vratiti jedan red koji sadrži samo jedno polje rezultata, koje će sadržavati zbir brojeva 181,35 i 23,09, odnosno 204,44.
Stoga se naša procedura može koristiti u običnim SQL upitima koji se izvršavaju iu klijentskim programima i u drugim HP-ovima ili okidačima. Ova upotreba naše procedure je omogućena upotrebom naredbe SUSPEND na kraju pohranjene procedure.
Činjenica je da u InterBase-u (i u svim njegovim klonovima) postoje dvije vrste pohranjenih procedura: procedure koje se mogu odabrati i procedure koje se mogu izvršiti. Razlika u radu ova dva tipa HP-a je u tome što procedure uzorkovanja obično vraćaju mnogo skupova izlaznih parametara, grupisanih red po red, koji izgledaju kao skup podataka, a izvršne procedure ili ne mogu vratiti parametre ili vraćaju samo jedan skup izlaznih parametara, navedenih u Povratcima, gdje je jedan red parametara. Procedure za odabir se pozivaju u SELECT upitima, a izvršne procedure se pozivaju pomoću naredbe EXECUTE PROCEDURE.
Obje vrste pohranjenih procedura imaju istu sintaksu kreiranja i formalno se ne razlikuju, tako da se bilo koja izvršna procedura može pozvati u SELECT upitu i bilo koja procedura odabira može se pozvati pomoću EXECUTE PROCEDURE. Pitanje je kako će se HP ponašati pod različitim tipovima poziva. Drugim riječima, razlika leži u dizajniranju procedure za određenu vrstu poziva. To jest, procedura odabira je posebno kreirana da bude pozvana iz SELECT upita, a izvršna procedura je posebno kreirana da bude pozvana korištenjem EXECUTE PROCEDURE. Pogledajmo koje su razlike u dizajnu ova dva tipa HP-a.
Da biste razumjeli kako procedura uzorkovanja funkcionira, morat ćete uroniti malo dublje u teoriju. Zamislimo običan SQL upit kao što je SELECT ID, NAME FROM Table_example. Kao rezultat njegovog izvršenja, dobijamo tabelu koja se sastoji od dve kolone (ID i NAME) i određenog broja redova (jednako broju redova u tabeli Table_example). Tablica vraćena kao rezultat ovog upita također se naziva SQL skup podataka. Razmislimo o tome kako se skup podataka formira tokom izvršavanja ovog upita. Server, nakon što je primio upit, određuje na koje se tabele odnosi, a zatim pronalazi odrediti koji podskup zapisa iz ovih tabela treba biti uključen u rezultat upita. Zatim server čita svaki zapis koji zadovoljava rezultate upita, odabire potrebna polja iz njega (u našem slučaju ID i NAME) i šalje ih klijentu. Zatim se proces ponovo ponavlja - i tako za svaki odabrani zapis.
Sva ova digresija je neophodna kako bi dragi čitatelj shvatio da se svi SQL skupovi podataka generiraju red po red, uključujući i pohranjene procedure! A glavna razlika između procedura dohvaćanja i izvršnih procedura je u tome što su prve dizajnirane da vrate mnogo redova, dok su druge dizajnirane da vrate samo jedan. Zbog toga se koriste drugačije: procedura odabira se poziva pomoću naredbe SELECT, koja "zahtijeva" da procedura odustane od svih zapisa koje može vratiti. Izvršna procedura se poziva pomoću EXECUTE PROCEDURE, koja "izvlači" samo jednu liniju iz HP-a, a zanemaruje ostale (čak i ako postoje!).
Pogledajmo primjer procedure uzorkovanja kako bismo bili jasniji. Za > oprost, napravimo pohranjenu proceduru koja radi točno kao upit SELECT ID, NAME FROM Table_Example, to jest, jednostavno bira polja ID i NAME iz cijele tabele. Evo ovog primjera:

CREATE PROCEDURE Simple_Select_SP
POVRATAK (
procID INTEGER,
procNAME VARCHAR(80))
AS
POČNI
ZA
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
POČNI
SUSPEND;
KRAJ
KRAJ

Pogledajmo korake ove procedure, nazvane Simple_Select_SP. Kao što vidite, nema ulaznih parametara i ima dva izlazna parametra - ID i NAME. Najzanimljivija stvar, naravno, leži u tijelu procedure. Ovdje se koristi konstrukcija FOR SELECT:

ZA
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
POČNI

/*uradi nešto sa varijablama procID i procName*/

KRAJ

Ovaj dio koda znači sljedeće: za svaki red odabran iz tablice Table_example, stavite odabrane vrijednosti u procID i procName varijable, a zatim učinite nešto s tim varijablama.
Možda ćete se iznenaditi i pitati: "Varijable? Koje druge varijable? 9" Nekako je iznenađenje ovog poglavlja da možemo koristiti varijable u pohranjenim procedurama. U HP jeziku, možete deklarirati obje vlastite lokalne varijable unutar procedure i koristiti ulazne i izlazne parametre kao varijable.
Da biste deklarirali lokalnu varijablu u pohranjenoj proceduri, potrebno je postaviti njen opis iza ključne riječi AS i prije prve riječi BEGIN. Opis lokalne varijable izgleda ovako:

DECLARE VARIABLE ;

Na primjer, da deklarirate cjelobrojnu lokalnu varijablu Mylnt, trebali biste umetnuti sljedeću deklaraciju između AS i BEGIN

DECLARE VARIABLE Mylnt INTEGER;

Varijable u našem primjeru počinju dvotočkom. Ovo je učinjeno zato što im se pristupa unutar FOR SELECT SQL naredbe, tako da za razliku između polja u tablicama koje se koriste u SELECT i varijabli, potonjima mora prethoditi dvotočka. Na kraju krajeva, varijable mogu imati potpuno isto ime kao polja u tabelama!
Ali dvotočka prije imena varijable treba se koristiti samo unutar SQL upita. Izvan tekstova, varijabla se poziva bez dvotočke, na primjer:

procName="Neko ime";

No, vratimo se na tijelo naše procedure. FOR SELECT klauzula vraća podatke ne u obliku tabele – skup podataka, već red po red. Svako vraćeno polje mora biti smješteno u vlastitu varijablu: ID => procID, NAME => procName. U dijelu DO, ove varijable se šalju klijentu koji je pozvao proceduru koristeći naredbu SUSPEND
Dakle, naredba FOR SELECT...DO petlja kroz zapise odabrane u SELECT dijelu naredbe. U tijelu petlje koju formira DO dio, sljedeći generirani zapis se prenosi na klijenta pomoću naredbe SUSPEND.
Dakle, procedura odabira je dizajnirana da vrati jedan ili više redova, za koje je petlja organizirana unutar tijela HP-a koja ispunjava rezultirajuće promjenjive parametre. A na kraju tijela ove petlje uvijek postoji naredba SUSPEND, koja će vratiti sljedeći red podataka klijentu.

Petlje i naredbe grananja

Pored naredbe FOR SELECT...DO, koja organizira petlju kroz zapise selekcije, postoji još jedan tip petlje - WHILE...DO, koja vam omogućava da organizirate petlju na osnovu provjere bilo kojih uslova. Evo primjera HP-a koji koristi WHILE..DO petlju. Ova procedura vraća kvadrate cijelih brojeva od 0 do 99:

CREATE PROCEDJRE QUAD
POVRATAK (KVADRATNI CIJELI)
AS
DECLARE VARIABLE I INTEGER;
POČNI
I = 1;
WHILE(i<100) DO
POČNI
KVADRAT= I*I;
I=I+1;
SUSPEND;
KRAJ
KRAJ

Kao rezultat izvršavanja upita SELECT FROM QUAD, dobićemo tabelu koja sadrži jedan QUADRAT stupac, koji će sadržavati kvadrate cijelih brojeva od 1 do 99
Pored ponavljanja rezultata SQL uzorka i klasične petlje, jezik pohranjene procedure koristi IF...THEN..ELSE operator, koji vam omogućava da organizirate grananje ovisno o izvršenju bilo kojeg uslova.Njegova sintaksa je slična većini operatora grananja u programskim jezicima visokog nivoa, kao što su Pascal i C.
Pogledajmo složeniji primjer pohranjene procedure koja radi sljedeće.

1. Izračunava prosječnu cijenu u tabeli Table_example (pogledajte poglavlje "Tabele primarni ključevi i generatori")
2. Zatim, za svaki unos u tabeli, vrši sljedeću provjeru: ako je postojeća cijena (PRICE) veća od prosječne cijene, tada postavlja cijenu jednaku prosječnoj cijeni, plus određeni fiksni postotak
3. Ako je postojeća cijena manja ili jednaka prosječnoj cijeni, tada se postavlja cijena jednaka prethodnoj cijeni, plus polovina razlike između prethodne i prosječne cijene.
4. Vraća sve izmijenjene redove u tabeli.

Prvo definišimo naziv HP-a, kao i ulazne i izlazne parametre, a sve to piše u zaglavlju pohranjene procedure.

KREIRAJTE PROCEDURU Povećajte cijene (
Postotak2lpovećanje DVOSTRUKE PRECIZNOSTI)
POVRATI (ID CIJELI, NAZIV VARCHAR(SO), nova_cijena DVOSTRUKA
PRECISION AS

Procedura će se zvati IncreasePrices, ima jedan ulazni parametar Peiceni21nciease tipa DOUBLE PRECISION, i 3 izlazna parametra - ID, NAME i new_pnce. Imajte na umu da prva dva izlazna parametra imaju ista imena kao i polja u tabeli Table_example sa kojima ćemo raditi.To je dozvoljeno pravilima jezika pohranjenih procedura.
Sada moramo deklarirati lokalnu varijablu koja će se koristiti za pohranjivanje prosječne vrijednosti. Deklaracija će izgledati ovako:

DECLARE VARIABLE avg_price DOUBLE PRECISION;

Sada pređimo na tijelo pohranjene procedure Otvorite tijelo HP-a ključna riječ BEGIN.
Prvo moramo izvršiti prvi korak našeg algoritma - izračunati prosječnu cijenu. Da bismo to uradili, koristićemo sledeću vrstu upita:

ODABIR AVG(Cijena_l)
IZ Tabele_Primjer
INTO:prosječna_cijena,-

Ovaj upit koristi AVG agregatnu funkciju, koja vraća prosek polja PRICE_1 među izabranim redovima upita—u našem slučaju, prosek PRICE_1 u celoj tabeli Table_example. Vrijednost vraćena zahtjevom stavlja se u varijablu avg_price. Imajte na umu da varijabli avg_pnce prethodi dvotočka kako bi se razlikovala od polja korištenih u zahtjevu.
Posebnost ovog upita je da uvijek vraća tačno jedan jedini zapis. Takvi upiti se nazivaju singleton upiti i samo takvi odabiri se mogu koristiti u pohranjenim procedurama. Ako upit vrati više od jednog reda, onda mora biti formatiran kao FOR SELECT...DO konstrukcija, koja organizira petlju za obradu svakog vraćenog reda
Dakle, dobili smo prosječnu cijenu. Sada morate proći kroz cijelu tabelu, uporediti vrijednost cijene u svakom unosu sa prosječnom cijenom i poduzeti odgovarajuće radnje
Od početka organizujemo pretragu za svaki zapis iz tabele Table_example

ZA
ODABIR ID, NAME, PRICE_1
IZ Tabele_Primjer
INTO:ID, :NAME, :nova_cijena
DO
POČNI
/*_ovdje opisujemo svaki unos*/
KRAJ

Kada se ova konstrukcija izvrši, podaci će biti ekstrahovani iz tabele Table_example red po red i vrednosti polja u svakom redu biće dodeljene varijablama ID, NAME i new_pnce. Vi, naravno, zapamtite da su ove varijable deklarirane kao izlazni parametri, ali nema potrebe za brigom da će odabrani podaci biti vraćeni kao rezultati: činjenica da je izlaznim parametrima nešto dodijeljeno ne znači da klijent poziva HP-u. će odmah primiti ove vrijednosti! Parametri se prosljeđuju samo kada se izvrši naredba SUSPEND, a prije toga možemo koristiti izlazne parametre kao obične varijable - u našem primjeru to radimo upravo s parametrom new_price.
Dakle, unutar tijela BEGIN... END petlje možemo obraditi vrijednosti svakog reda. Kao što se sećate, moramo da shvatimo koliko je postojeća cena u poređenju sa prosečnom i da preduzmemo odgovarajuće mere. Mi implementiramo ovu proceduru poređenja koristeći IF naredbu:

IF (nova_cijena > prosječna_cijena) THEN /*ako je postojeća cijena veća od prosječne cijene*/
POČNI
/*onda ćemo postaviti novu cijenu jednaku prosječnoj cijeni, plus fiksni postotak */
nova_cijena = (prosječna_cijena + prosječna_cijena*(Percent2Increase/100));
UPDATE Table_example
POSTAVI CIJENU_1 = :nova_cijena
GDJE ID = :ID;
KRAJ
ELSE
POČNI
/* Ako je postojeća cijena manja ili jednaka prosječnoj cijeni, onda postavite cijenu jednaku prethodnoj cijeni, plus pola razlike između prethodne i prosječne cijene */
nova_cijena = (nova_pnce + ((prosjek_pnce nova_cijena)/2)) ;
UPDATE Table_example
POSTAVI CIJENU_1 = :nova_cijena
WHERE ID = .ID;
KRAJ

Kao što vidite, rezultat je prilično velika IF konstrukcija, koju bi bilo teško razumjeti da nije komentara u /**/ simbolima.
Kako bismo promijenili cijenu u skladu sa izračunatom razlikom, koristićemo naredbu UPDATE, koja nam omogućava da modificiramo postojeće zapise - jedan ili više. Kako bismo nedvosmisleno naznačili u kojem zapisu treba promijeniti cijenu, koristimo polje primarnog ključa u uvjetu WHERE, upoređujući ga sa vrijednošću varijable koja pohranjuje vrijednost ID-a za trenutni zapis: ID=:ID. Imajte na umu da ID varijabli prethodi dvotočka.
Nakon izvršenja konstrukcije IF...THEN...ELSE, varijable ID, NAME i new_price sadrže podatke koje moramo vratiti klijentu koji je pozvao proceduru. Da biste to uradili, nakon IF potrebno je ubaciti naredbu SUSPEND, koja će poslati podatke odakle je HP pozvan. Tokom prijenosa, procedura će biti obustavljena, a kada je potreban novi zapis od HP-a, će se ponovo nastaviti - i to će se nastaviti sve dok FOR SELECT...DO neće iterirati kroz sve zapise u svom upitu.
Treba napomenuti da pored naredbe SUSPEND, koja samo suspenduje pohranjenu proceduru, postoji naredba EXIT koja prekida pohranjenu proceduru nakon prolaska niza. Međutim, naredba EXIT se koristi prilično rijetko, jer je uglavnom potrebna za prekid petlje kada se postigne uvjet
Međutim, u slučaju kada je procedura pozvana sa SELECT naredbom i završena sa EXIT, poslednji dohvaćeni red neće biti vraćen. To jest, ako trebate prekinuti proceduru i dalje >dobiti ovaj niz, trebate koristiti sekvencu

SUSPEND;
IZLAZ;

Glavna svrha EXIT-a je primanje pojedinačnih skupova podataka, vraćenih parametara pozivanjem EXECUTE PROCEDURE. U ovom slučaju se postavljaju vrijednosti izlaznih parametara, ali se SQL skup podataka ne generira iz njih i izvršavanje procedure se završava.
Zapišimo tekst naše pohranjene procedure u cijelosti kako bismo mogli na prvi pogled uhvatiti njenu logiku:

KREIRAJTE PROCEDURU Povećajte cijene (
Postotak2 Povećanje DVOSTRUKE PRECIZNOSTI)
POVRATAK (ID CIJELI, NAZIV VARCHAR(80),
new_price DOUBLE PRECISION) AS
DECLARE VARIABLE avg_price DOUBLE PRECISION;
POČNI
ODABIR AVG(Cijena_l)
IZ Tabele_Primjer
INTO:avg_price;
ZA
ODABIR ID, NAME, PRICE_1
IZ Tabele_Primjer
INTO:ID, :NAME, :nova_cijena
DO
POČNI
/*obradi svaki zapis ovdje*/
IF (new_pnce > avg_price) THEN /*ako je postojeća cijena veća od prosječne cijene*/
POČNI
/*postaviti novu cijenu jednaku prosječnoj cijeni plus fiksni postotak */
nova_cijena = (prosječna_cijena + prosječna_cijena*(Percent2lncrease/100));
UPDATE Table_example
POSTAVI CIJENU_1 = :nova_cijena
GDJE ID = :ID;
KRAJ
ELSE
POČNI
/* Ako je postojeća cijena manja ili jednaka prosječnoj cijeni, onda postavlja cijenu jednaku prethodnoj cijeni plus pola razlike između prethodne i prosječne cijene */
nova_cijena = (nova_cijena + ((prosječna_cijena - nova_cijena)/2));
UPDATE Table_example
POSTAVI CIJENU_1 = :nova_cijena
GDJE ID = :ID;
KRAJ
SUSPEND;
KRAJ
KRAJ

Ovaj primjer pohranjene procedure ilustrira upotrebu osnovnih konstrukcija jezika pohranjenih procedura i okidača. Zatim ćemo pogledati načine korištenja pohranjenih procedura za rješavanje nekih uobičajenih problema.

Rekurzivne pohranjene procedure

InterBase pohranjene procedure mogu biti rekurzivne. To znači da pohranjena procedura može pozvati samu sebe. Dozvoljeno je do 1000 nivoa ugniježđenja uskladištenih procedura, ali moramo imati na umu da slobodni resursi na serveru mogu ponestati prije nego što se postigne maksimalno ugnježđenje HP-a.
Jedna uobičajena upotreba pohranjenih procedura je obrada struktura stabla pohranjenih u bazi podataka. Drveće se često koristi u sastavu proizvoda, skladištu, osoblju i drugim uobičajenim aplikacijama.
Pogledajmo primjer pohranjene procedure koja odabire sve proizvode određenog tipa, počevši od određenog nivoa ugniježđenja.
Hajde da imamo sledeću formulaciju problema: imamo imenik robe sa hijerarhijskom strukturom sledećeg tipa:

Roba
- Aparati
- Frižideri
- Trokomorni
- Dvokomorna
- Jednokomorna
- Mašine za pranje veša
- Vertikalno
- Frontalni
- Klasika
- Usko
- Računarska tehnologija
....

Ova struktura kataloga kategorija proizvoda može imati grane različite dubine. a takođe se povećavaju tokom vremena. Naš zadatak je osigurati odabir svih konačnih elemenata iz direktorija sa "proširivanjem punog imena", počevši od bilo kojeg čvora. Na primjer, ako odaberemo čvor "Mašine za pranje rublja", tada moramo dobiti sljedeće kategorije:

Veš mašine - Vertikalne
Veš mašine - Front Classic
Mašine za veš - Prednji uski

Definirajmo strukturu tablice za pohranjivanje informacija direktorija proizvoda. Koristimo pojednostavljenu šemu za organiziranje stabla u jednoj tablici:

CREATE TABLE GoodsTree
(ID_GOOD INTEGER NIJE NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR(80),
ograničenje pkGooci primarni ključ (ID_GOOD));

Kreiramo jednu tabelu GoodsTree, u kojoj postoje samo 3 polja: ID_GOOD - pametni identifikator kategorije, ID_PARENT_GOOD - identifikator matične kompanije za ovu kategoriju i GOOD_NAME - naziv kategorije. Da bismo osigurali integritet podataka u ovoj tabeli, nametnut ćemo ograničenje stranog ključa na ovu tablicu:

ALTER TABLE GoodsTree
DODAJ OGRANIČENJE FK_goodstree
STRANI KLJUČ (ID_PARENT_GOOD)
REFERENCE DOBRA (ID__GOOD)

Tabela se odnosi na sebe i ovaj strani ključ to prati. tako da tabela ne sadrži reference na nepostojeće roditelje, a sprečava i pokušaje brisanja kategorija proizvoda koje imaju djecu.
Unesimo sljedeće podatke u našu tabelu:

Sada kada imamo mjesto za pohranu podataka, možemo početi kreirati pohranjenu proceduru koja će ispisati sve "konačne" kategorije proizvoda u "proširenom" obliku - na primjer, za kategoriju "Tri komore", punu kategoriju naziv bi bio "Hladnjaci za kućanstvo" Trokomorni".
Pohranjene procedure koje obrađuju strukture stabla imaju svoju terminologiju. Svaki element stabla naziva se čvor; a odnos između čvorova koji se međusobno pozivaju naziva se odnos roditelj-dijete. Čvorovi koji se nalaze na samom kraju stabla i nemaju djece nazivaju se "listovi".
Za ovu pohranjenu proceduru, ulazni parametar će biti identifikator kategorije, iz kojeg ćemo morati započeti analizu. Pohranjena procedura će izgledati ovako:

KREIRAJTE PROCEDURE GETPULNAME (ID_GOOD2SHOW INTEGER)
POVRATAK (PUN_NAZIV_ROBE VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
AS
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
POČNI
/*0organizirajte vanjsku FOR SELECT petlju prema neposrednim potomcima proizvoda sa ID_GOOD=ID_GOOD2SHOW */
ZA SELECT gtl.id_good, gtl.good_name
OD GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
DO
POČNI
/"Provjerite pomoću funkcije EXISTS, koja vraća TRUE ako upit u zagradama vrati barem jedan red. Ako pronađeni čvor sa ID_PARENT_GOOD = ID_CHILD_GOOD nema djece, onda je to "list" stabla i uključen je u rezultate */
AKO (NE POSTOJI(
SELECT * IZ GoodsTree
GDJE GoodsTree.id_parent_good=:id_child_good))
ONDA
POČNI
/* Proslijedite “list” stabla u rezultate */
SUSPEND;
KRAJ
ELSE
/* Za čvorove koji imaju djecu*/
POČNI
/*sačuvajte ime roditeljskog čvora u privremenu varijablu */
CURR_CHILD_NAME=puno_naziv_robe;
/* pokrenuti ovu proceduru rekurzivno */
ZA
SELECT ID_CHILD_GOOD, puni_naziv_robe
FROM GETPULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
POČNITE
/*dodavanje imena roditeljskog čvora pronađenom podređenom imenu koristeći operaciju konkatenacije stringova || */
full_goods_name=CURR_CHILD_NAME| " " | puni_ime_robe,-
SUSPEND; /* vrati puni naziv proizvoda*/
KRAJ
KRAJ
KRAJ
KRAJ

Ako ovu proceduru izvršimo sa ulaznim parametrom ID_GOOD2SHOW= 1, dobićemo sledeće:

Kao što vidite, koristeći rekurzivnu pohranjenu proceduru, prošli smo kroz cijelo stablo kategorija i prikazali puni naziv kategorija „lista“ koje se nalaze na samim vrhovima grana.

Zaključak

Ovo završava naše razmatranje glavnih karakteristika jezika pohranjenih procedura. Očigledno, nemoguće je u potpunosti savladati razvoj pohranjenih procedura u samo jednom poglavlju, ali ovdje smo pokušali uvesti i objasniti osnovne koncepte povezane sa pohranjenim procedurama. Opisani dizajni i tehnike za dizajniranje HP-a mogu se primijeniti u većini aplikacija baza podataka
Neka od važnih pitanja vezanih za razvoj uskladištenih procedura biće obrađena u sledećem poglavlju – „Napredne mogućnosti jezika pohranjenih procedura InterBase“, koje je posvećeno rukovanju izuzetcima, rešavanju situacija grešaka u uskladištenim procedurama i radu sa nizovima.

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 se vrši u kontekstu baze podataka u kojoj se procedura nalazi.

Vrste pohranjenih procedura

SQL Server ima nekoliko tipova uskladištenih procedura.

Sistemske pohranjene procedure su dizajnirane za izvođenje različitih administrativnih radnji. Uz njihovu pomoć obavljaju se gotovo sve aktivnosti administracije servera. Možemo reći da su sistemske pohranjene procedure sučelje koje omogućava rad sa sistemskim tabelama, koji se u konačnici svodi na promjenu, dodavanje, brisanje i preuzimanje 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 punopravni 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 ili se server ponovo pokrene ili zaustavi. Globalne privremene pohranjene procedure dostupne su za bilo koju vezu s poslužitelja koji ima istu proceduru. Da biste ga definirali, samo mu dajte ime koje počinje znakovima ##. Ove procedure se brišu kada se server ponovo pokrene ili zaustavi, ili kada se zatvori veza u kontekstu u kojem su kreirani.

Okidači

Okidači su vrsta pohranjene procedure. Izvršavaju se kada se na tablici izvrši operator jezika za manipulaciju podacima (DML). Okidači se koriste za provjeru integriteta podataka i za vraćanje transakcija.

Trigger je kompajlirana SQL procedura, čije je izvršavanje uvjetovano pojavom određenih događaja unutar relacijske baze podataka. Upotreba okidača je, uglavnom, vrlo zgodna za korisnike baze podataka. Ipak, njihova upotreba često uključuje dodatne troškove resursa za I/O operacije. Kada se isti rezultati (sa mnogo manje troškova) mogu postići korištenjem pohranjenih procedura ili aplikativnih programa, upotreba okidača nije praktična.

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 postići željeni nivo funkcionalnosti. Često je potrebno implementirati složene algoritme za verifikaciju podataka kako bi se osigurala njihova pouzdanost i realnost. Osim toga, ponekad morate pratiti promjene u vrijednostima tablice kako bi se povezani podaci mogli mijenjati po potrebi. Okidači se mogu smatrati vrstom filtera koji stupaju na snagu nakon što se sve operacije završe u skladu s pravilima, standardnim vrijednostima itd.

Trigger je posebna vrsta pohranjene procedure koju server automatski pokreće kada se pokuša promijeniti podaci u tablicama kojima su pridruženi okidači. Svaki Trigger je vezan za određenu tabelu. Sve izmjene podataka koje napravi smatraju se jednom transakcijom. Ako se otkrije greška ili kršenje integriteta podataka, transakcija se vraća nazad. Promjene su stoga zabranjene. Sve promjene koje je već napravio okidač također se poništavaju.

Kreira okidač samo vlasnik baze podataka. Ovo ograničenje vam omogućava da izbjegnete slučajne promjene strukture tabela, načina povezivanja drugih objekata s njima itd.

Trigger To je vrlo koristan i u isto vrijeme opasan lijek. Dakle, ako je logika njegovog rada pogrešna, možete lako uništiti cijelu bazu podataka, tako da se okidači moraju vrlo pažljivo otklanjati.

Za razliku od obične potprograma, okidač se izvršava implicitno kad god se dogodi okidač i nema argumente. Aktiviranje se ponekad naziva paljenjem okidača. Koristeći okidače, postižu se sljedeći ciljevi:

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 na tablici;

izdavanje upozorenja koja vas podsjećaju da izvršite određene radnje prilikom ažuriranja tablice implementirane na određeni način;

akumuliranje revizijskih informacija evidentiranjem 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 navesti specifična imena stupaca tablice za događaj okidača. Tajming okidača se određuje pomoću ključnih riječi BEFORE ( Trigger pokreće se prije nego što se događaji povezani s njim izvrše) ili NAKON (nakon što se izvrše).

Akcije koje izvodi okidač su specificirane za svaki red (ZA SVAKI RED) obuhvaćen događajem, ili samo jednom za svaki događaj (ZA SVAKI IZJAVA).

Neispravno napisani okidači mogu dovesti do ozbiljnih problema, kao što su mrtve brave. Okidači mogu blokirati mnoge resurse na duži vremenski period, tako da posebnu pažnju treba posvetiti minimiziranju sukoba pristupa.

Trigger može se kreirati samo u trenutnoj bazi podataka, ali je moguće pristupiti drugim bazama podataka unutar okidača, uključujući i one koje se nalaze na udaljenom serveru.

SQL pohranjene procedure su izvršni programski moduli koji se mogu pohraniti u obliku različitih objekata. Drugim riječima, to je objekt koji sadrži SQL izraze. Ove pohranjene procedure mogu se izvršiti u klijentu aplikacije da bi se postigle dobre performanse. Osim toga, takvi objekti se često pozivaju iz drugih skripti ili čak iz nekog drugog odjeljka.

Uvod

Mnogi ljudi vjeruju da su slični raznim procedurama (osim MS SQL-a). Možda je ovo istina. Imaju slične parametre i mogu proizvesti slične vrijednosti. Štaviše, u nekim slučajevima se dodiruju. Na primjer, oni su u kombinaciji s DDL i DML bazama podataka, kao i korisničkim funkcijama (kodnog naziva UDF).

U stvarnosti, SQL pohranjene procedure imaju širok spektar prednosti koje ih izdvajaju od sličnih procesa. Sigurnost, fleksibilnost programiranja, produktivnost - sve to privlači sve više korisnika koji rade sa bazama podataka. Vrhunac popularnosti procedura dogodio se 2005-2010, kada je pušten Microsoftov program pod nazivom “SQL Server Management Studio”. Uz njegovu pomoć, rad s bazama podataka postao je mnogo lakši, praktičniji i praktičniji. Iz godine u godinu ovaj je sticao popularnost među programerima. Danas je to potpuno poznat program, koji je za korisnike koji “komuniciraju” sa bazama podataka u rangu sa Excelom.

Kada se pozove procedura, server je trenutno obrađuje bez nepotrebnih procesa ili intervencije korisnika. Nakon toga, možete izvršiti bilo koje brisanje, izvršenje ili modifikaciju. Za sve to odgovoran je DDL operator, koji jedini izvodi najsloženije radnje za obradu objekata. Štaviše, sve se to dešava vrlo brzo, a server se zapravo ne učitava. Ova brzina i performanse vam omogućavaju da vrlo brzo prenesete velike količine informacija od korisnika do servera i obrnuto.

Za implementaciju ove tehnologije za rad sa informacijama postoji nekoliko programskih jezika. To uključuje, na primjer, PL/SQL iz Oraclea, PSQL u InterBase i Firebird sistemima, kao i klasični Microsoft Transact-SQL. Svi su dizajnirani za kreiranje i izvršavanje pohranjenih procedura, što omogućava velikim procesorima baza podataka da koriste vlastite algoritme. Ovo je također neophodno kako bi oni koji upravljaju takvim informacijama mogli zaštititi sve objekte od neovlaštenog pristupa trećih osoba i, shodno tome, kreiranja, izmjene ili brisanja određenih podataka.

Produktivnost

Ovi objekti baze podataka mogu se programirati na razne načine. Ovo omogućava korisnicima da odaberu vrstu metode koja je najprikladnija, štedeći trud i vrijeme. Osim toga, procedura se sama obrađuje, čime se izbjegava ogromno vrijeme utrošeno na komunikaciju između servera i korisnika. Također, modul se može reprogramirati i promijeniti u željenom smjeru u apsolutno bilo kojem trenutku. Posebno je vrijedno napomenuti brzinu kojom se pokreće SQL pohranjena procedura: ovaj proces se odvija brže od drugih sličnih njemu, što ga čini praktičnim i univerzalnim.

Sigurnost

Ova vrsta obrade informacija razlikuje se od sličnih procesa po tome što garantuje povećanu sigurnost. To je osigurano činjenicom da pristup procedurama drugih korisnika može biti potpuno isključen. Ovo će omogućiti administratoru da samostalno obavlja operacije s njima, bez straha od presretanja informacija ili neovlaštenog pristupa bazi podataka.

Prijenos podataka

Odnos između SQL pohranjene procedure i klijentske aplikacije je korištenje parametara i povratnih vrijednosti. Potonji ne mora proslijediti podatke u pohranjenu proceduru, ali se te informacije (uglavnom na zahtjev korisnika) obrađuju za SQL. Nakon što pohranjena procedura završi svoj rad, ona šalje pakete podataka nazad (ali opet opciono) aplikaciji koja ju je pozvala, koristeći različite metode koje se mogu koristiti ili za pozivanje SQL pohranjene procedure ili za povratak, na primjer:

Prijenos podataka pomoću parametra tipa izlaza;

Prenošenje podataka pomoću operatora return;

Prenošenje podataka pomoću operatora odabira.

Hajde sada da shvatimo kako ovaj proces izgleda iznutra.

1. Kreirajte EXEC pohranjenu proceduru u SQL-u

Možete kreirati proceduru u MS SQL-u (Managment Studio). Nakon kreiranja procedure, ona će biti navedena u programabilnom čvoru baze podataka, u kojem proceduru kreiranja izvršava operator. Za izvršenje, SQL pohranjene procedure koriste EXEC proces koji sadrži ime samog objekta.

Kada kreirate proceduru, prvo se pojavljuje njeno ime, a zatim jedan ili više parametara koji su joj dodijeljeni. Parametri mogu biti opcioni. Nakon što su parametar(i), odnosno tijelo procedure upisani, potrebno je izvršiti neke potrebne operacije.

Poenta je da tijelo može imati lokalne varijable smještene u njemu, a te varijable su također lokalne u odnosu na procedure. Drugim riječima, mogu se vidjeti samo unutar tijela Microsoft SQL Server procedure. Pohranjene procedure u ovom slučaju se smatraju lokalnim.

Dakle, da bismo kreirali proceduru, potrebno nam je ime procedure i barem jedan parametar kao tijelo procedure. Imajte na umu da je odlična opcija u ovom slučaju kreirati i izvršiti proceduru s imenom šeme u klasifikatoru.

Tijelo procedure može biti bilo koje vrste, kao što je kreiranje tablice, umetanje jednog ili više redova tablice, utvrđivanje tipa i prirode baze podataka, itd. Međutim, tijelo procedure ograničava obavljanje određenih operacija unutar njega. Neka od važnih ograničenja su navedena u nastavku:

Telo ne bi trebalo da kreira nikakvu drugu pohranjenu proceduru;

Telo ne bi trebalo da stvara lažan utisak o objektu;

Telo ne bi trebalo da stvara nikakve okidače.

2. Postavljanje varijable u tijelu procedure

Možete napraviti varijable lokalne za tijelo procedure, i tada će se nalaziti isključivo unutar tijela procedure. Dobra je praksa kreirati varijable na početku tijela pohranjene procedure. Ali također možete postaviti varijable bilo gdje u tijelu datog objekta.

Ponekad možete primijetiti da je nekoliko varijabli postavljeno u jedan red, a svaki parametar varijable je odvojen zarezom. Također imajte na umu da varijabla ima prefiks @. U tijelu procedure možete postaviti varijablu gdje god želite. Na primjer, varijabla @NAME1 može biti deklarirana blizu kraja tijela procedure. Da bi se deklarisanoj promenljivoj dodelila vrednost, koristi se skup ličnih podataka. Za razliku od situacije kada je više od jedne varijable deklarirano u istom redu, u ovoj situaciji se koristi samo jedan skup ličnih podataka.

Korisnici često postavljaju pitanje: "Kako dodijeliti više vrijednosti u jednom iskazu u tijelu procedure?" Pa. Zanimljivo je pitanje, ali mnogo je lakše nego što mislite. Odgovor: Koristeći parove kao što je "Select Var = value". Možete koristiti ove parove tako što ćete ih odvojiti zarezom.

Različiti primjeri pokazuju kako ljudi kreiraju jednostavnu pohranjenu proceduru i izvode je. Međutim, procedura može prihvatiti parametre tako da će proces koji je poziva imati bliske vrijednosti (ali ne uvijek). Ako se poklapaju, tada u tijelu počinju odgovarajući procesi. Na primjer, ako kreirate proceduru koja će prihvatiti grad i regiju od pozivaoca i vratiti podatke o tome koliko autora pripada odgovarajućem gradu i regiji. Procedura će upitati tabele autora baze podataka, kao što su Pubs, da izvrši ovo brojanje autora. Da bi dobio ove baze podataka, na primjer, Google preuzima SQL skriptu sa stranice SQL2005.

U prethodnom primjeru, procedura uzima dva parametra, koji će se na engleskom konvencionalno zvati @State i @City. Tip podataka odgovara tipu definiranom u aplikaciji. Tijelo procedure ima interne varijable @TotalAuthors, a ova varijabla se koristi za prikaz broja autora. Zatim dolazi odjeljak za odabir upita, koji sve izračunava. Konačno, izračunata vrijednost se ispisuje u izlaznom prozoru pomoću naredbe print.

Kako izvršiti pohranjenu proceduru u SQL-u

Postoje dva načina za izvođenje postupka. Prvi način pokazuje, prosljeđivanjem parametara, kako se nakon naziva procedure izvršava lista razdvojena zarezima. Recimo da imamo dvije vrijednosti (kao u prethodnom primjeru). Ove vrijednosti se prikupljaju pomoću varijabli parametara procedure @State i @City. U ovom načinu prosljeđivanja parametara, redoslijed je važan. Ova metoda se zove ordinalno prosljeđivanje argumenata. Kod druge metode, parametri su već direktno dodijeljeni, a u ovom slučaju redoslijed nije bitan. Ova druga metoda je poznata kao prosljeđivanje imenovanih argumenata.

Postupak može malo odstupiti od uobičajenog. Sve je isto kao u prethodnom primjeru, ali samo ovdje su parametri pomjereni. To jest, parametar @City se prvi pohranjuje, a @State se pohranjuje pored zadane vrijednosti. Zadani parametar se obično posebno ističe. SQL pohranjene procedure se prosljeđuju samo kao parametri. U ovom slučaju, pod uvjetom, parametar "UT" zamjenjuje zadanu vrijednost "CA". U drugom izvršenju, prosljeđuje se samo jedna vrijednost argumenta za parametar @City, a parametar @State preuzima zadanu vrijednost "CA". Iskusni programeri savjetuju da se sve varijable po defaultu nalaze na kraju liste parametara. U suprotnom izvršenje nije moguće, a onda morate raditi s prosljeđivanjem imenovanih argumenata, što je duže i složenije.

4. Pohranjene procedure SQL Servera: Metode povrata

Postoje tri važna načina za slanje podataka u zvanoj pohranjenoj proceduri. Oni su navedeni u nastavku:

Vrati vrijednost pohranjene procedure;

Izlaz parametra pohranjene procedure;

Odabir jedne od pohranjenih procedura.

4.1 Vraćanje vrijednosti iz SQL pohranjenih procedura

U ovoj tehnici, procedura dodjeljuje vrijednost lokalnoj varijabli i vraća je. Procedura također može direktno vratiti konstantnu vrijednost. U sljedećem primjeru kreirali smo proceduru koja vraća ukupan broj autora. Ako uporedite ovu proceduru sa prethodnim, možete vidjeti da je vrijednost ispisa obrnuta.

Sada da vidimo kako izvršiti proceduru i ispisati njenu povratnu vrijednost. Izvršavanje procedure zahtijeva postavljanje varijable i ispis, koji se izvodi nakon cijelog ovog procesa. Imajte na umu da umjesto naredbe za ispis možete koristiti naredbu Select, kao što je Select @RetValue kao i OutputValue.

4.2 Izlaz parametra SQL pohranjene procedure

Vrijednost odgovora se može koristiti za vraćanje jedne varijable, što smo vidjeli u prethodnom primjeru. Korištenje izlaznog parametra omogućava proceduri da pošalje jednu ili više varijabilnih vrijednosti pozivaocu. Izlazni parametar je određen upravo ovom ključnom riječi “Output” prilikom kreiranja procedure. Ako je parametar dan kao izlazni parametar, tada mu objekt procedure mora dodijeliti vrijednost. SQL pohranjene procedure, čiji se primjeri mogu vidjeti u nastavku, u ovom slučaju se vraćaju sa sažetim informacijama.

U našem primjeru bit će dva izlazna imena: @TotalAuthors i @TotalNoContract. Oni su naznačeni u listi parametara. Ove varijable dodjeljuju vrijednosti unutar tijela procedure. Kada koristimo izlazne parametre, pozivalac može vidjeti vrijednost postavljenu unutar tijela procedure.

Također, u prethodnom scenariju, dvije varijable su deklarirane da bi se vidjelo vrijednosti koje su pohranjene procedure MS SQL Servera postavile u izlaznom parametru. Zatim se postupak izvodi navođenjem normalne vrijednosti parametra “CA”. Sljedeći parametri su izlazni parametri i stoga se deklarirane varijable prosljeđuju navedenim redoslijedom. Imajte na umu da se kod prosljeđivanja varijabli ovdje postavlja i izlazna ključna riječ. Nakon što je postupak uspješno završen, vrijednosti koje vraćaju izlazni parametri se prikazuju u okviru za poruke.

4.3 Odabir jedne od SQL pohranjenih procedura

Ova tehnika se koristi za vraćanje skupa vrijednosti kao tablice podataka (RecordSet) u pohranjenu proceduru koja poziva. U ovom primjeru, SQL pohranjena procedura s @AuthID parametrima postavlja upite prema tablici Autori filtriranjem zapisa vraćenih korištenjem tog parametra @AuthId. Naredba Select odlučuje šta treba vratiti pozivaocu pohranjene procedure. Kada se izvrši pohranjena procedura, AuthId se vraća nazad. Ova procedura ovdje uvijek vraća samo jedan zapis ili nijednog. Ali pohranjena procedura nema nikakva ograničenja za vraćanje više od jednog zapisa. Nije neuobičajeno vidjeti primjere u kojima se podaci vraćaju korištenjem odabranih parametara koji uključuju izračunate varijable pružanjem višestrukih zbroja.

Konačno

Pohranjena procedura je prilično ozbiljan programski modul koji vraća ili prolazi, a također postavlja potrebne varijable zahvaljujući klijentskoj aplikaciji. Budući da se pohranjena procedura sama pokreće na serveru, velike količine razmjene podataka između servera i klijentske aplikacije (za neke proračune) mogu se izbjeći. Ovo vam omogućava da smanjite opterećenje na SQL serverima, što, naravno, koristi njihovim vlasnicima. Jedan od podtipova su T SQL pohranjene procedure, ali njihovo proučavanje je neophodno za one koji kreiraju impresivne baze podataka. Postoji i veliki, čak ogroman broj nijansi koje mogu biti korisne prilikom proučavanja pohranjenih procedura, ali to je više potrebno onima koji planiraju da se uključe u programiranje, uključujući i profesionalno.