Razmatramo situaciju u kojoj pohranjene procedure mogu pogoršati izvedbu upita.
Prilikom kompajliranja pohranjenih procedura u MS SQL Server 2000, pohranjene procedure se smještaju u predmemoriju procedura, što može poboljšati performanse prilikom njihovog izvođenja eliminirajući potrebu za raščlanjivanje, optimizacija i kompilacija koda pohranjene procedure.
S druge strane, postoje zamke u pohranjivanju kompiliranog koda pohranjene procedure koje mogu imati suprotan učinak.
Činjenica je da se pri sastavljanju pohranjene procedure shodno tome kompilira plan izvođenja onih operatora koji čine kod procedure, ako je pohranjena pohranjena procedura spremljena u predmemoriju, tada je i njen plan izvođenja u predmemoriji, a samim tim i pohranjena procedura; neće biti optimizirana za konkretna situacija i parametri upita.
Napravimo mali eksperiment da to pokažemo.
KORAK 1. Izrada baze podataka.
Za eksperiment ćemo izraditi zasebnu bazu podataka.
STVARANJE BAZE PODATAKA test_sp_perf
UKLJUČENO (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
PRIJAVA (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
KORAK 2. Izrada tablice.
CREATE TABLE sp_perf_test(column1 int, column2 char(5000))
KORAK 3. Popunjavanje tablice testnim redovima. Dvostruki retci namjerno su dodani u tablicu. 10.000 redaka s brojevima od 1 do 10.000 i 10.000 redaka s brojevima 50.000.
DEKLARIRAJ @i int
POSTAVITE @i=1
WHILE(@i<10000)
POČETI
INSERT INTO sp_perf_test(column1, column2) VALUES(@i,"Test string #"+CAST(@i as char(8)))
INSERT INTO sp_perf_test(column1, column2) VALUES(50000,"Testni niz #"+CAST(@i as char(8)))
POSTAVITI @i= @i+1
KRAJ
SELECT COUNT(*) FROM sp_perf_test
IĆI
KORAK 4. Stvaranje neklasteriziranog indeksa. Budući da je plan izvršenja predmemoriran s procedurom, indeks će se koristiti na isti način u svim pozivima.
STVARAJ NEKLUSTRIRANI INDEKS CL_perf_test NA sp_perf_test(stupac1)
IĆI
KORAK 5. Stvaranje pohranjene procedure. Procedura jednostavno izvršava naredbu SELECT s uvjetom.
STVARAJ PROC proc1 (@param int)
KAO
SELECT stupac1, stupac2 FROM sp_perf_test WHERE stupac1=@param
IĆI
KORAK 6. Izvođenje pohranjene procedure. Prilikom pokretanja ranjive procedure posebno se koristi selektivni parametar. Kao rezultat postupka dobivamo 1 redak. Plan izvršenja ukazuje na korištenje neklasteriziranog indeksa, jer Upit je selektivan i ovo je najbolji način za dohvaćanje retka. Procedura optimizirana za dohvaćanje jednog retka pohranjuje se u proceduralnu predmemoriju.
EXEC proc1 1234
IĆI
KORAK 7. Pokrenite pohranjenu proceduru s neselektivnim parametrom. Vrijednost koja se koristi kao parametar je 50 000. Postoji oko 10 000 redaka s ovom vrijednošću u prvom stupcu, stoga je korištenje neklasteriziranog indeksa i operacija traženja knjižne oznake neučinkovita, ali budući da je kompajlirani kod s planom izvršenja pohranjen u; proceduralni cache, to je ono što će se koristiti. Plan izvršenja to pokazuje, kao i činjenicu da je operacija traženja knjižne oznake izvršena za 9999 redaka.
EXEC proc1 50000
IĆI
KORAK 8. Izvođenje odabira redaka s prvim poljem jednakim 50000. Prilikom pokretanja zasebnog upita, upit se optimizira i kompilira s određenom vrijednošću za prvi stupac. Kao rezultat toga, optimizator upita utvrđuje da se polje duplicira mnogo puta i odlučuje koristiti operaciju skeniranja tablice, koja je u ovom slučaju mnogo učinkovitija od upotrebe neklasteriziranog indeksa.
SELECT stupac1, stupac2 FROM sp_perf_test WHERE stupac1=50000
IĆI
Stoga možemo zaključiti da korištenje pohranjenih procedura ne može uvijek poboljšati izvedbu upita. Trebali biste biti vrlo oprezni s pohranjenim procedurama koje rade na rezultatima s promjenjivim brojem redaka i koje koriste različite planove izvršenja.
Možete koristiti skriptu za ponavljanje eksperimenta na vašem MS SQL poslužitelju.
Pohranjeni postupak - objekt baze podataka, koji je skup SQL instrukcija koje se jednom kompiliraju i pohranjuju na poslužitelju. Pohranjene procedure vrlo su slične uobičajenim jezičnim procedurama visoke razine, mogu imati ulazne i izlazne parametre i lokalne varijable, mogu izvoditi numeričke izračune i operacije na znakovnim podacima, čiji se rezultati mogu dodijeliti varijablama i parametrima. Pohranjene procedure mogu izvoditi standardne operacije baze podataka (i DDL i DML). Osim toga, pohranjene procedure dopuštaju petlje i grananja, odnosno mogu koristiti upute za kontrolu procesa izvođenja.
Pohranjene procedure slične su korisnički definiranim funkcijama (UDF). Glavna razlika je u tome što se korisnički definirane funkcije mogu koristiti kao bilo koji drugi izraz u SQL izjavi, dok se pohranjene procedure moraju pozvati pomoću funkcije CALL:
Postupak POZIVA (…)
IZVRŠI postupak (…)
Pohranjene procedure mogu vratiti više rezultata, to jest rezultate SELECT upita. Takvi skupovi rezultata mogu se obraditi pomoću kursora, drugih pohranjenih procedura koje vraćaju pokazivač skupa rezultata ili aplikacija. Pohranjene procedure također mogu sadržavati deklarirane varijable za obradu podataka i pokazivače, koji vam omogućuju da prelazite preko više redaka u tablici. SQL standard nudi IF, LOOP, REPEAT, CASE i mnoge druge za rad. Pohranjene procedure mogu prihvatiti varijable, vratiti rezultate ili modificirati varijable i vratiti ih, ovisno o tome gdje je varijabla deklarirana.
Implementacija pohranjenih procedura razlikuje se od jednog DBMS-a do drugog. Većina velikih dobavljača baza podataka podržava ih u ovom ili onom obliku. Ovisno o DBMS-u, pohranjene procedure mogu se implementirati u različitim programskim jezicima, kao što su SQL, Java, C ili C++. Pohranjene procedure koje nisu napisane u SQL-u mogu ili ne moraju same izvršavati SQL upite.
Za
Dijeljenje logike s drugim aplikacijama. Pohranjene procedure enkapsuliraju funkcionalnost;
ovo omogućuje povezivanje za pristup i upravljanje podacima u različitim aplikacijama.
Izoliranje korisnika iz tablica baze podataka. To vam omogućuje davanje pristupa pohranjenim procedurama, ali ne i samim podacima tablice.
Pruža zaštitni mehanizam. Prema prethodnoj točki, ako podacima možete pristupiti samo putem pohranjenih procedura, nitko drugi ne može izbrisati vaše podatke putem SQL naredbe DELETE.
Poboljšano izvršenje kao rezultat smanjenog mrežnog prometa. Korištenjem pohranjenih procedura moguće je kombinirati više upita.
Protiv
Povećano opterećenje poslužitelja baze podataka zbog činjenice da se većina posla obavlja na strani poslužitelja, a manje na strani klijenta.
Morat ćeš puno naučiti. Morat ćete naučiti sintaksu izraza MySQL da biste napisali svoje pohranjene procedure.
Migracija s jednog DBMS-a na drugi (DB2, SQL Server, itd.) može dovesti do problema.
Svrha i prednosti pohranjenih procedura
Pohranjene procedure poboljšavaju performanse, poboljšavaju mogućnosti programiranja i podržavaju značajke sigurnosti podataka.
Umjesto pohranjivanja često korištenog upita, klijenti mogu referencirati odgovarajuću pohranjenu proceduru. Kada se pozove pohranjena procedura, poslužitelj odmah obrađuje njen sadržaj.
Osim stvarnog izvršavanja upita, pohranjene procedure vam također omogućuju izvođenje izračuna i manipuliranje podacima - mijenjanje, brisanje, izvršavanje DDL naredbi (ne u svim DBMS-ovima!) i pozivanje drugih pohranjenih procedura, te izvođenje složene transakcijske logike. Jedna izjava omogućuje vam da pozovete složenu skriptu koja je sadržana u pohranjenoj proceduri, izbjegavajući slanje stotina naredbi preko mreže i, posebno, potrebu za prijenosom velikih količina podataka od klijenta do poslužitelja.
U većini DBMS-ova, prvi put kada se pohranjena procedura pokrene, ona se kompajlira (raščlanjuje i generira se plan pristupa podacima). U budućnosti je njegova obrada brža. Oracle DBMS tumači pohranjeni proceduralni kod pohranjen u rječniku podataka. Počevši od Oracle 10g, podržana je takozvana nativna kompilacija pohranjenog proceduralnog koda u C-u, a zatim u strojni kod ciljnog stroja, nakon čega se, kada se pozove pohranjena procedura, njen prevedeni objektni kod izravno izvršava.
Mogućnosti programiranja
Kreirana pohranjena procedura može se pozvati u bilo kojem trenutku, što osigurava modularnost i potiče ponovnu upotrebu koda. Ovo posljednje čini bazu podataka lakšom za održavanje jer postaje izolirana od promjena poslovnih pravila. Možete izmijeniti pohranjenu proceduru u skladu s novim pravilima u bilo kojem trenutku. Nakon toga, sve aplikacije koje ga koriste automatski će se uskladiti s novim poslovnim pravilima bez izravnih izmjena.
Sigurnost
Korištenje pohranjenih procedura omogućuje vam da ograničite ili potpuno uklonite izravan korisnički pristup tablicama baze podataka, ostavljajući korisnicima samo dopuštenja za izvršavanje pohranjenih procedura koje pružaju neizravan i strogo reguliran pristup podacima. Dodatno, neki DBMS-ovi podržavaju enkripciju teksta (prematanje) pohranjene procedure.
Ove sigurnosne značajke omogućuju izoliranje strukture baze podataka od korisnika, osiguravajući integritet i pouzdanost baze podataka.
Vjerojatnost radnji kao što je SQL ubacivanje je smanjena jer dobro napisane pohranjene procedure dodatno provjeravaju ulazne parametre prije prosljeđivanja upita DBMS-u.
Implementacija pohranjenih procedura
Pohranjene procedure obično se kreiraju korištenjem SQL jezika ili njegove specifične implementacije u odabranom DBMS-u. Na primjer, za ove svrhe u DBMS-u Microsoft SQL Server postoji Transact-SQL jezik, u Oracle - PL/SQL, u InterBase i Firebird - PSQL, u PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, u IBM DB2 - SQL/PL ( eng.), u Informixu - SPL. MySQL poprilično slijedi standard SQL:2003, njegov je jezik sličan SQL/PL-u.
Neki DBMS-ovi dopuštaju korištenje pohranjenih procedura napisanih u bilo kojem programskom jeziku koji može stvoriti neovisne izvršne datoteke, na primjer, C++ ili Delphi. U terminologiji Microsoft SQL Servera, takve se procedure nazivaju proširenim pohranjenim procedurama i jednostavno su funkcije sadržane u Win32 DLL-u. I, na primjer, u Interbase i Firebird, funkcije pozvane iz DLL/SO imaju drugačiji naziv - UDF (korisnički definirana funkcija). MS SQL 2005 uveo je mogućnost pisanja pohranjenih procedura u bilo kojem .NET jeziku, a proširene pohranjene procedure planiraju se napustiti u budućnosti. Oracle DBMS pak omogućuje pisanje pohranjenih procedura u Javi. U IBM DB2, pisanje pohranjenih procedura i funkcija u konvencionalnim programskim jezicima je tradicionalan način, podržan od samog početka, a SQL proceduralno proširenje je dodano ovom DBMS-u tek u prilično kasnim verzijama, nakon uključivanja u ANSI standard. Informix također podržava procedure u Javi i C.
U Oracle DBMS-u pohranjene procedure mogu se kombinirati u tzv. pakete. Paket se sastoji od dva dijela - specifikacije paketa, koja navodi definiciju pohranjene procedure, i tijela paketa, koje sadrži njezinu implementaciju. Stoga Oracle omogućuje odvajanje sučelja programskog koda od njegove implementacije.
U IBM DB2 DBMS, pohranjene procedure se mogu kombinirati u module.
Sintaksa
STVARAJ PROCEDURU `p2`()
SQL DEFINITOR SIGURNOSTI
KOMENTAR "Procedura"
SELECT "Hello World!";
Prvi dio koda stvara pohranjenu proceduru. Sljedeći sadrži neobavezne parametre. Zatim dolazi naziv i, konačno, tijelo samog postupka.
4 karakteristike pohranjene procedure:
Jezik: Za potrebe prenosivosti, zadana vrijednost je SQL.
Deterministički: ako procedura uvijek vraća isti rezultat i uzima iste ulazne parametre. Ovo je za proces replikacije i registracije. Zadana vrijednost NIJE DETERMINISTIČKA.
SQL Sigurnost: prava korisnika provjeravaju se tijekom poziva. INVOKER je korisnik koji poziva pohranjenu proceduru. DEFINER je “kreator” procedure. Zadana vrijednost je DEFINER.
Komentar: Za potrebe dokumentacije, zadana vrijednost je ""
Pozivanje pohranjene procedure
POZIV ime_spremljene_procedure (param1, param2, ....)
CALL procedure1(10 , "string parametar" , @parameter_var);
Izmjena pohranjene procedure
MySQL ima naredbu ALTER PROCEDURE za promjenu procedura, ali je prikladna samo za promjenu određenih karakteristika. Ako trebate promijeniti parametre ili tijelo procedure, trebali biste je izbrisati i ponovno izraditi.
Uklanjanjepohranjenopostupcima
ISPUSTI POSTUPAK AKO POSTOJI p2;
Ovo je jednostavna naredba. Naredba IF EXISTS hvata pogrešku ako takva procedura ne postoji.
Mogućnosti
CREATE PROCEDURE proc1(): prazna lista parametara
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): jedan ulazni parametar. Riječ IN nije obavezna jer su zadani parametri IN (in).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): vraćen je jedan parametar.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): jedan parametar, i ulazni i povratni.
Sintaksa deklaracije varijable izgleda ovako:
DECLARE varname DATA-TYPE DEFAULT zadana vrijednost;
Deklaracija o postupku
KREIRAJTE PROCEDURU [({ULAZ|VAN|ULAZ} [,…])]
[DINAMIČKI SET REZULTATA ]
POČETI [ATOMSKI]
KRAJ
Ključne riječi
. IN (Input) – ulazni parametar
. OUT (Output) – izlazni parametar
. INOUT – ulaz i izlaz, kao i polje (bez parametara)
. DYNAMIC RESULT SET označava da procedura može otvoriti određeni broj kursora koji će ostati otvoreni nakon što se procedura vrati
Bilješke
Ne preporučuje se korištenje mnogih parametara u pohranjenim procedurama (prvenstveno velikih brojeva i nizova znakova) zbog preopterećenosti mreže i stoga. U praksi, postojeći dijalekti Transact-SQL, PL/SQL i Informix imaju značajne razlike u odnosu na standard, kako u deklaraciji i korištenju parametara, deklaracijama varijabli, tako iu pozivanju potprograma. Microsoft preporučuje korištenje sljedeće aproksimacije za procjenu veličine predmemorije pohranjene procedure:
=(maksimalni broj istodobnih korisnika)*(veličina najvećeg plana izvršenja)*1.25. Određivanje veličine plana izvršenja u stranicama može se izvršiti pomoću naredbe: DBCC MEMUSAGE.
Pozivanje postupka
U mnogim postojećim DBMS-ovima, pohranjene procedure se pozivaju pomoću operatora:
IZVRŠI POSTUPAK [(][)]
Bilješka: Pozivanje pohranjenih procedura može se izvršiti iz aplikacije, druge pohranjene procedure ili interaktivno.
Primjer deklaracije postupka
KREIRAJ PROCEDURU Proc1 AS //deklariraj proceduru
DEKLIRAJ Cursor1 KURSOR ZA SELECT SName, City FROM SalesPeople WHERE Rating>200 //deklariraj kursor
OTVORI Cur1 //otvori kursor
FETCH NEXT FROM Cur1 //čitanje podataka s kursora
WHILE @@Fetch_Status=0
POČETI
DOHVATI SLJEDEĆE IZ Cur1
KRAJ
CLOSE Cur1 //zatvaranje kursora
DEADOCAL Cur1
IZVRŠI Proc1 //pokreni proceduru
Polimorfizam
Dvije potprograme s istim imenom mogu se kreirati u istoj shemi ako su parametri dviju potprograma slični veliki prijatelj jedni od drugih tako da se mogu razlikovati. Za razlikovanje dvije rutine s istim imenom u istoj shemi, svakoj se daje alternativno i jedinstveno ime (specifično ime). Takav naziv može biti eksplicitno specificiran kada se definira potprogram. Prilikom pozivanja potprograma s nekoliko identičnih imena, određivanje potrebne potprograma provodi se u nekoliko koraka:
. U početku su svi postupci definirani s navedeno ime, a ako ih nema, onda sve funkcije sa zadanim imenom.
. Za daljnju analizu samo one potprograme u odnosu na koje ovaj korisnik ima privilegiju izvršenja (EXECUTE).
. Za njih su odabrani oni čiji broj parametara odgovara broju argumenata poziva. Provjeravaju se navedeni tipovi podataka parametara i njihove pozicije.
. Ako je preostalo više potprograma, odabire se onaj čije je kvalificirajuće ime kraće.
U praksi, Oracle podržava polimorfizam za funkcije deklarirane samo u paketu, DB@ - u drugačija shema, au Sybase i MS SQL Server preopterećenje je zabranjeno.
Brisanje i mijenjanje postupaka
Za uklanjanje procedure upotrijebite operator:
Za promjenu postupka upotrijebite operator:
PROMIJENI POSTUPAK [([{ULAZ|VAN|ULAZ}])]
POČETI [ATOMSKI]
KRAJ
Privilegije za obavljanje postupaka
ODOBRITI IZVRŠITI DALJE DO |JAVNOST [UZ MOGUĆNOST DOPOVANJA]
Sustavne procedure
Mnogi DBMS-ovi (uključujući SQL Server) imaju određeni skup ugrađenih sistemskih pohranjenih procedura koje možete koristiti za vlastite potrebe.
Vrste pohranjenih procedura
SQL Server ima nekoliko vrsta pohranjene procedure.
- sustav pohranjene procedure dizajniran za obavljanje raznih upravne radnje. Gotovo sve aktivnosti administracije poslužitelja obavljaju se uz njihovu pomoć. Možemo reći da sistemski pohranjene procedure su sučelje koje omogućuje rad sa sistemskim tablicama, što se u konačnici svodi na mijenjanje, dodavanje, brisanje i dohvaćanje podataka iz sistemskih tablica korisničkih i sistemskih baza podataka. sustav pohranjene procedure imaju prefiks sp_, pohranjeni su u bazi podataka sustava i mogu se pozvati u kontekstu bilo koje druge baze podataka.
- Običaj pohranjene procedure provesti određene akcije. Pohranjene procedure– punopravni objekt baze podataka. Kao rezultat toga, svaki pohranjeni postupak nalazi se u određenoj bazi podataka gdje se i izvršava.
- Privremeni pohranjene procedure postoje samo neko vrijeme, nakon čega ih poslužitelj automatski uništava. Dijele se na lokalne i globalne. Lokalni privremeni pohranjene procedure mogu se pozvati samo iz veze u kojoj su stvoreni. Kada kreirate takvu proceduru, morate joj dati naziv koji počinje s jednim znakom #. Kao i svi privremeni objekti, pohranjene procedure ove vrste se automatski brišu kada korisnik prekine vezu ili se poslužitelj ponovno pokrene ili zaustavi. Globalno privremeno pohranjene procedure dostupni su za sve veze s poslužitelja koji ima isti postupak. Da biste ga definirali, samo mu dodijelite ime koje počinje znakovima ##. Ovi se postupci brišu kada se poslužitelj ponovno pokrene ili zaustavi ili kada se zatvori veza u kontekstu u kojem su stvoreni.
Stvaranje, mijenjanje i brisanje pohranjenih procedura
Stvaranje pohranjeni postupak uključuje rješavanje sljedećih problema:
- određivanje vrste stvorenog pohranjeni postupak: privremeni ili prilagođeni. Osim toga, možete stvoriti vlastiti sustav pohranjeni postupak, dajući mu naziv s prefiksom sp_ i stavljajući ga u baza sustava podaci. Ovaj će postupak biti dostupan u kontekstu bilo koje baze podataka lokalnog poslužitelja;
- planiranje prava pristupa. Prilikom stvaranja pohranjeni postupak 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 postupcima uključenim u većinu programskih jezika, pohranjene procedure može imati ulazne i izlazne parametre;
- razvoj koda pohranjeni postupak. Kod procedure može sadržavati slijed bilo koje SQL naredbe, uključujući pozive drugim pohranjene procedure.
Stvaranje novog i mijenjanje postojećeg pohranjeni postupak učinjeno pomoću sljedeće naredbe:
<определение_процедуры>::= (CREATE | ALTER ) naziv_procedure [;broj] [(@parameter_name data_type) [=zadano] ][,...n] AS sql_operator [...n]
Pogledajmo parametre ove naredbe.
Korištenjem prefiksa sp_ , #, ##, stvorena procedura može se definirati kao sustavna ili privremena. Kao što vidite iz sintakse naredbe, nije dopušteno navesti ime vlasnika koji će posjedovati kreiranu proceduru, kao ni naziv baze podataka u kojoj se ona treba nalaziti. Tako, da bi se smjestilo stvoreno pohranjeni postupak u određenoj bazi podataka, morate izdati naredbu CREATE PROCEDURE u kontekstu te baze podataka. Pri okretanju od tijela pohranjeni postupak za objekte iste baze podataka mogu se koristiti skraćeni nazivi, tj. bez navođenja imena baze podataka. Kada trebate pristupiti objektima koji se nalaze u drugim bazama podataka, navođenje naziva baze podataka je obavezno.
Broj u imenu je identifikacijski broj pohranjeni postupak, što ga jedinstveno identificira u skupini postupaka. Radi lakšeg upravljanja, procedure su logično iste vrste pohranjene procedure mogu se grupirati dajući im isti naziv, ali različite identifikacijske brojeve.
Za prijenos ulaznih i izlaznih podataka u kreirani pohranjeni postupak mogu se koristiti parametri čija imena, kao i imena lokalnih varijabli, moraju započeti simbolom @. U jednoj pohranjeni postupak Možete navesti više parametara odvojenih zarezima. Tijelo procedure ne bi trebalo koristiti lokalne varijable čija se imena podudaraju s imenima parametara ove procedure.
Da biste odredili vrstu podataka koja odgovara parametar pohranjene procedure, bilo koja vrsta je prikladna SQL podaci, uključujući korisnički definirane. Međutim, tip podataka CURSOR može se koristiti samo kao izlazni parametar pohranjeni postupak, tj. navodeći ključnu riječ OUTPUT.
Prisutnost ključne riječi OUTPUT znači da je odgovarajući parametar namijenjen vraćanju podataka iz pohranjeni postupak. Međutim, to ne znači da parametar nije prikladan za prosljeđivanje vrijednosti pohranjeni postupak. Specificiranje ključne riječi OUTPUT upućuje poslužitelj da izađe pohranjeni postupak dodijeliti trenutnu vrijednost parametra lokalnoj varijabli koja je navedena prilikom pozivanja procedure kao vrijednost parametra. Imajte na umu da se prilikom navođenja ključne riječi OUTPUT vrijednost odgovarajućeg parametra prilikom pozivanja procedure može postaviti samo pomoću lokalne varijable. Bilo koji izrazi ili konstante koji su dopušteni za regularne parametre nisu dopušteni.
Ključna riječ VARYING koristi se zajedno s
Pohranjeno SQL procedure su izvršni softverski modul koji se može pohraniti u obrazac razne predmete. Drugim riječima, to je objekt koji sadrži SQL naredbe. Ove pohranjene procedure mogu se izvršiti u klijentu aplikacijski programi dobiti dobra izvedba. Osim toga, takvi se objekti često pozivaju iz drugih skripti ili čak iz nekog drugog odjeljka.
Uvod
Mnogi ljudi vjeruju da su slični različitim postupcima (osim MS SQL-a). Možda je ovo istina. Imaju slične parametre i mogu proizvesti slične vrijednosti. Štoviše, u nekim se slučajevima dodiruju. Na primjer, kombiniraju se s DDL i DML bazama podataka, kao i korisničkim funkcijama (kodnog naziva UDF).
U stvarnosti, SQL pohranjene procedure imaju širok raspon prednosti koje ih razlikuju od sličnih procesa. Sigurnost, fleksibilnost programiranja, produktivnost - sve to privlači sve više korisnika koji rade s bazama podataka. Vrhunac popularnosti postupaka dogodio se 2005.-2010., kada je objavljen Microsoftov program pod nazivom "SQL Server". Management Studio" Uz njegovu pomoć, rad s bazama podataka postao je puno lakši, praktičniji i praktičniji. Iz godine u godinu, ovaj je dobivao popularnost među programerima. Danas je to potpuno poznati program, koji je za korisnike koji "komuniciraju" s bazama podataka jednak Excelu.
Kada se procedura pozove, odmah ju obrađuje sam poslužitelj bez nepotrebni procesi i intervencija korisnika. Nakon toga možete izvršiti bilo koje brisanje, izvršenje ili modificiranje. Za sve to odgovoran je DDL operater koji sam izvodi najsloženije radnje obrade objekata. Štoviše, sve se to događa vrlo brzo, a poslužitelj zapravo nije učitan. Ova brzina i performanse omogućuju vrlo brz prijenos velikih količina informacija od korisnika do poslužitelja i obrnuto.
Za implementaciju ove tehnologije za rad s informacijama postoji nekoliko programskih jezika. Tu spadaju, primjerice, PL/SQL iz Oraclea, PSQL u sustavima InterBase i Firebird, kao i klasični Microsoft Transact-SQL. Svi su oni dizajnirani za kreiranje i izvršavanje pohranjenih procedura, što omogućuje velikim procesorima baza podataka korištenje vlastitih algoritama. To je također potrebno kako bi oni koji upravljaju takvim informacijama mogli zaštititi sve objekte od neovlaštenog pristupa trećih osoba te sukladno tome kreiranja, izmjene ili brisanja određenih podataka.
Produktivnost
Ovi objekti baze podataka mogu se programirati na razne načine. To omogućuje korisnicima odabir vrste korištene 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 poslužitelja i korisnika. Modul se također može reprogramirati i mijenjati pravom smjeru u apsolutno svakom trenutku. Posebno je vrijedno istaknuti brzinu kojom se pokreće SQL pohranjena procedura: ovaj se proces odvija brže od drugih sličnih, što ga čini praktičnim i univerzalnim.
Sigurnost
Ova vrsta obrade informacija razlikuje se od sličnih procesa po tome što jamči povećana sigurnost. To je osigurano činjenicom da pristup procedurama od strane drugih korisnika može biti potpuno isključen. To će omogućiti administratoru da samostalno obavlja operacije s njima, bez straha od presretanja informacija ili neovlašteni pristup u bazu podataka.
Prijenos podataka
Odnos između SQL pohranjene procedure i aplikacija klijenta leži u korištenju parametara i povratnih vrijednosti. Potonji ne mora proslijediti podatke pohranjenoj proceduri, ali se ti podaci (uglavnom na zahtjev korisnika) obrađuju za SQL. Nakon što pohranjena procedura završi svoj posao, šalje pakete podataka natrag (ali, opet, opcionalno) aplikaciji koja ju je pozvala, koristeći razne metode, uz pomoć koje se može izvršiti i poziv pohranjene SQL procedure i povratak, na primjer:
Prijenos podataka pomoću parametra vrste izlaza;
Prosljeđivanje podataka pomoću operatora return;
Prosljeđivanje podataka pomoću operatora odabira.
Sada shvatimo kako ovaj proces izgleda iznutra.
1. Stvorite EXEC pohranjenu proceduru u SQL-u
Proceduru možete kreirati u MS SQL (Managment Studio). Nakon što je procedura kreirana, bit će navedena u programabilnom čvoru baze podataka, u kojoj operater izvršava proceduru kreiranja. Za izvođenje, SQL pohranjene procedure koriste EXEC proces koji sadrži naziv samog objekta.
Kada kreirate proceduru, prvo se pojavljuje njeno ime, a zatim jedan ili više parametara koji su joj dodijeljeni. Parametri mogu biti izborni. Nakon što se ispiše parametar(i), odnosno tijelo procedure, potrebno je izvršiti neke potrebne operacije.
Radi se o tome da tijelo može imati lokalne varijable smještene u sebi, a te varijable su također lokalne u odnosu na procedure. Drugim riječima, mogu se vidjeti samo unutar tijela procedure Microsoft SQL Servera. Pohranjene procedure u ovom se slučaju smatraju lokalnima.
Dakle, da bismo kreirali proceduru, trebamo ime procedure i barem jedan parametar kao tijelo procedure. imajte na umu da izvrsna opcija u ovom slučaju, to je stvoriti i izvršiti proceduru s imenom sheme u klasifikatoru.
Tijelo procedure može biti bilo koje vrste, poput stvaranja tablice, umetanja jednog ili više redaka tablice, utvrđivanja tipa i prirode baze podataka itd. Međutim, tijelo procedure ograničava izvođenje određenih operacija unutar njega. Neka od važnih ograničenja navedena su u nastavku:
Tijelo ne bi trebalo stvarati druge pohranjene procedure;
Tijelo ne bi trebalo stvarati lažni dojam o objektu;
Tijelo ne bi trebalo stvarati nikakve okidače.
2. Postavljanje varijable u tijelo procedure
Varijable možete učiniti lokalnim za tijelo procedure, a onda ć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 određenog objekta.
Ponekad možete primijetiti da je nekoliko varijabli postavljeno u jednom retku, a svaki parametar varijable odvojen je 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 se deklarirati blizu kraja tijela procedure. Za dodjelu vrijednosti deklariranoj varijabli koristi se skup osobnih podataka. Za razliku od situacije u kojoj je više od jedne varijable deklarirano u istom retku, u ovoj se situaciji koristi samo jedan skup osobnih podataka.
Korisnici često postavljaju pitanje: "Kako dodijeliti više vrijednosti u jednoj izjavi u tijelu procedure?" Dobro. To je zanimljivo pitanje, ali puno ga je lakše napraviti nego što mislite. Odgovor: Korištenje parova kao što je "Select Var = value". Ove parove možete koristiti tako da ih odvojite zarezom.
U većini razni primjeri ljudi pokazuju stvaranje jednostavne pohranjene procedure i njezino izvršavanje. Međutim, procedura može prihvatiti parametre tako da će proces koji je poziva imati vrijednosti bliske njoj (ali ne uvijek). Ako se podudaraju, tada u tijelu počinju odgovarajući procesi. Na primjer, ako kreirate proceduru koja će prihvatiti grad i regiju od pozivatelja i vratiti podatke o tome koliko autora pripada odgovarajućem gradu i regiji. Procedura će postaviti upit tablicama autora baze podataka, kao što su Pubs, da izvrši ovo brojanje autora. Za dobivanje tih baza podataka, na primjer, Google preuzima SQL skripta sa stranice SQL2005.
U prethodnom primjeru, postupak uzima dva parametra, koji engleski uvjetno će se zvati @država i @grad. Vrsta podataka odgovara vrsti definiranoj u aplikaciji. Tijelo procedure ima interne varijable @TotalAuthors, a ova se varijabla koristi za prikaz broja autora. Slijedi odjeljak za odabir upita koji sve izračunava. Na kraju se izračunata vrijednost ispisuje u izlaznom prozoru pomoću naredbe za ispis.
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 izvršava lista odvojena zarezima nakon naziva procedure. Recimo da imamo dvije vrijednosti (kao u prethodnom primjeru). Ove se vrijednosti prikupljaju pomoću varijabli parametara procedure @State i @City. U ovoj metodi prosljeđivanja parametara bitan je redoslijed. Ova se metoda naziva rednim prosljeđivanjem argumenata. U drugoj metodi, parametri su već izravno dodijeljeni, au ovom slučaju redoslijed nije bitan. Ova druga metoda je poznata kao prosljeđivanje imenovanih argumenata.
Postupak može malo odstupati od uobičajenog. Sve je isto kao u prethodnom primjeru, samo su ovdje parametri pomaknuti. To jest, @City se pohranjuje prvi, a @State se pohranjuje pored zadane vrijednosti. Zadani parametar obično je zasebno označen. SQL pohranjene procedure prosljeđuju se samo kao parametri. U ovom slučaju, pod uvjetom, parametar "UT" zamjenjuje zadanu vrijednost "CA". U drugom se izvođenju prosljeđuje samo jedna vrijednost argumenta za parametar @City, a parametar @State preuzima zadanu vrijednost "CA". Iskusni programeri savjetuju da se sve varijable prema zadanim postavkama trebaju nalaziti pri kraju popisa 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 vraćanja
Postoje tri važne načine slanje podataka u pozvanoj pohranjenoj proceduri. Oni su navedeni u nastavku:
Vrati vrijednost pohranjene procedure;
Izlaz parametara 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 izravno vratiti konstantnu vrijednost. U sljedećem primjeru stvorili smo proceduru koja vraća ukupan broj autora. Ako usporedite ovaj postupak s prethodnima, možete vidjeti da je vrijednost ispisa obrnuta.
Pogledajmo sada kako izvršiti proceduru i ispisati njezinu povratnu vrijednost. Izvršenje procedure zahtijeva postavljanje varijable i ispis, koji se provodi 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 parametara SQL pohranjene procedure
Vrijednost odgovora može se koristiti za vraćanje jedne varijable, što smo vidjeli u prethodnom primjeru. Korištenje izlaznog parametra omogućuje proceduri slanje jedne ili više varijabilnih vrijednosti pozivatelju. Izlazni parametar je označen upravo ovom ključnom riječi “Izlaz” 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 vraćaju sa sažetim informacijama.
U našem primjeru bit će dva imena izlaza: @TotalAuthors i @TotalNoContract. Oni su naznačeni u popisu parametara. Ove varijable dodjeljuju vrijednosti unutar tijela procedure. Kada koristimo izlazne parametre, pozivatelj može vidjeti vrijednost postavljenu unutar tijela procedure.
Također, u prethodnom scenariju, dvije varijable su deklarirane da vide vrijednosti koje su pohranjene procedure MS SQL Servera postavile u izlaznom parametru. Zatim se postupak izvodi unosom normalne vrijednosti parametra “CA”. Sljedeće opcije izlaze i stoga se prosljeđuju deklarirane varijable na propisani način. Imajte na umu da pri prolasku kroz varijable izlaz ključna riječ pitao i ovdje. Nakon što je postupak uspješno dovršen, vrijednosti koje vraćaju izlazni parametri prikazuju se u okviru s porukom.
4.3 Odabir jedne od SQL pohranjenih procedura
Ova se tehnika koristi za vraćanje skupa vrijednosti kao podatkovne tablice (RecordSet) pozivnoj pohranjenoj proceduri. U ovom SQL primjer Pohranjena procedura s parametrima @AuthID ispituje tablicu autora filtriranjem zapisa vraćenih pomoću ovog parametra @AuthId. Odaberite operatera odlučuje što treba vratiti pozivatelju pohranjene procedure. Kada se pohranjena procedura izvrši, AuthId se vraća natrag. Ovaj postupak ovdje uvijek vraća samo jedan zapis ili nijedan. Ali pohranjena procedura nema ograničenja za vraćanje više od jednog zapisa. Nije neuobičajeno vidjeti primjere u kojima se podaci vraćaju pomoću odabranih parametara koji uključuju izračunate varijable davanjem više ukupnih vrijednosti.
U zaključku
Pohranjeni postupak je prilično ozbiljan softverski modul, vraćanje ili slanje, kao i postavljanje potrebnih varijabli zahvaljujući klijentskoj aplikaciji. Budući da se pohranjena procedura sama izvodi na poslužitelju, mogu se izbjeći velike količine razmjene podataka između poslužitelja i klijentske aplikacije (za neke izračune). To vam omogućuje smanjenje opterećenja SQL poslužitelj, što, naravno, ide u korist njihovim nositeljima. Jedna od podvrsta su T SQL pohranjene procedure, ali njihovo proučavanje potrebno je onima koji stvaraju impresivne baze podataka. Postoji također veliki, čak i ogroman broj nijansi koje mogu biti korisne pri proučavanju pohranjenih procedura, ali to je potrebno više za one koji se planiraju uključiti u programiranje, uključujući i profesionalno.
