SQL pohranjene procedure: stvaranje i korištenje

Pohranjeni postupci SQL je izvršni programski modul koji se može pohraniti u bazu podataka kao razne objekte. Drugim riječima, to je objekt koji sadrži SQL izraze. Ove pohranjene procedure mogu se izvršiti u aplikacijskom klijentu kako bi dobili dobru izvedbu. Osim toga, takvi se objekti često nazivaju iz drugih scenarija ili čak iz nekog drugog odjeljka.

uvod

Mnogi vjeruju da su slični postupcima raznih programskih jezika na visokoj razini (odnosno, osim MS SQL). Možda je to doista tako. Oni imaju slične parametre, mogu dati slične vrijednosti. Štoviše, u brojnim slučajevima oni su u kontaktu. Na primjer, kombiniraju se s bazama podataka DDL i DML, kao i s korisničkim funkcijama (kodni naziv - UDF).

Zapravo, SQL pohranjene procedure imaju širok raspon prednosti koje ih razlikuju od sličnih procesa. Sigurnost, varijabilnost programiranja, produktivnost - sve to privlači korisnike koji rade s bazama podataka, sve više i više. Vrhunac popularnosti postupaka dogodio se u razdoblju 2005-2010, kada je Microsoft objavio program pod nazivom SQL Server Management Studio. Uz pomoć, suradnja s bazama podataka postala je mnogo jednostavnija, praktičnija i praktičnija. Od godine do godine ova je metoda prijenosa informacija postala popularna među programerima. Danas je MS SQL Server apsolutno poznat program koji je za korisnike koji "komuniciraju" s bazama podataka porastao uz "Excel".

Kada se pozove neki postupak, on odmah obrađuje sam poslužitelj, bez nepotrebnih procesa i intervencije korisnika. Nakon toga možete izvesti sve radnje s informacijama: brisanje, izvršenje, promjena. Za sve to je odgovornost DDL operatera, koji sami obavlja najsloženiju obradu objekata. I sve se to događa vrlo brzo, a poslužitelj nije zapravo učitan. Ova brzina i performanse omogućuju vam da vrlo brzo prenesete velike količine informacija od korisnika do poslužitelja i obrnuto.

Da bi se ova tehnologija implementirala s informacijama, postoji nekoliko programskih jezika. To uključuje, na primjer, PL / SQL iz sustava za upravljanje bazom podataka Oracle, PSQL u InterBase i Firebird sustavima, kao i klasični "Microsoft Transact-SQL". Svi su dizajnirani za stvaranje i izvršavanje pohranjenih postupaka, što omogućuje upotrebu velikih algoritama u velikim poslužiteljima baze podataka. Također je potrebno za one koji upravljaju takvim informacijama da bi zaštitili sve objekte od neovlaštenog pristupa od strane trećih strana, te prema tome stvorili, mijenjali ili izbrisali određene podatke.

produktivnost

Ovi objekti baze podataka mogu se programirati na različite načine. To korisnicima omogućuje da odaberu vrstu korištene metode koja će biti najprikladnija, čime se štedi vrijeme i trud. Nadalje, postupak se obrađuje, čime se izbjegava veliko vrijeme provedeno na razmjeni između poslužitelja i korisnika. Također, modul se može reprogramirati i mijenjati u željenom smjeru u bilo kojem trenutku. Posebno je vrijedno napomenuti brzinu kojom započinje SQL pohranjena procedura: ovaj proces je brži od drugih, sličan onome što ga čini prikladnim i svestranim.

sigurnosni

Ova vrsta obrade informacija razlikuje se od sličnih procesa, jer jamči povećanu sigurnost. To osigurava činjenica da se pristup ostalim korisnicima u postupke može potpuno i potpuno isključiti. To će administratoru omogućiti samostalno obavljanje poslova s njima, bez straha od presretanja informacija ili neovlaštenog pristupa bazi podataka.

Prijenos podataka

Odnos između SQL pohranjene procedure i aplikacije klijenta je korištenje parametara i povratnih vrijednosti. Potonji ne mora prenijeti podatke u pohranjenu proceduru, no ove informacije (uglavnom na zahtjev korisnika) i obrađene za SQL. Nakon što pohranjena procedura dovrši svoj rad, on šalje pakete podataka natrag (ali opet, ako to želi) aplikaciji koja ju je nazvala, koristeći različite metode koje se mogu koristiti kao poziv na pohranjenu proceduru SQL i povratak, na primjer:

- prijenos podataka pomoću izlaznog parametra;

- prijenos podataka putem povratnika;

- prijenos podataka pomoću operatora za odabir.

Pogledajmo sada kako taj proces izgleda iznutra.

1. Izradite EXEC pohranjenu proceduru u SQL

Možete izraditi postupak u MS SQL (Managment Studio). Nakon što se postupak stvori, bit će prebačen na programirani čvor baze podataka u kojem postupak izrade izvršava operater. Za izvršavanje, SQL pohranjene procedure koriste EXEC proces koji sadrži naziv samog objekta.

Prilikom izrade postupka prvo se pojavljuje naziv, nakon čega se proizvodi jedan ili više parametara. Parametri mogu biti izborni. Nakon što su napisani parametri (e), tj. Tijelo postupka, morate obaviti neke potrebne operacije.

Stvar je u tome da tijelo može imati lokalne varijable koje su u njemu, a te su varijable lokalne iu odnosu na postupke. Drugim riječima, oni se mogu pregledati samo unutar tijela postupka Microsoft SQL Server. Pohranjene procedure smatraju se lokalnim.

Dakle, za izradu postupka trebamo naziv postupka i barem jedan parametar kao tijelo postupka. Napominjemo da je izvrsna opcija u ovom slučaju stvaranje i izvršavanje postupka s nazivom sheme u klasifikatoru.

Tijelo postupka može imati bilo kakve SQL izraze, na primjer, kao što je stvaranje tablice, umetanje jednog ili više redaka tablice, postavljanje vrste i prirode baze podataka i tako dalje. Ipak, tijelo postupka ograničava izvršavanje određenih operacija u njemu. Neka su važna ograničenja navedena u nastavku:

- tijelo ne bi trebalo stvoriti drugu pohranjenu proceduru;

- Tijelo ne bi trebalo stvoriti lažnu ideju o objektu;

- Tijelo ne bi trebalo stvoriti nikakvu okidač.

2. Postavljanje varijable u tijelu postupka

Varijable možete napraviti lokalno u tijelu, a zatim će se nalaziti isključivo unutar tijela postupka. Dobra je praksa stvaranje varijabli na početku tijela pohranjene procedure. Ali također možete postaviti varijable bilo gdje u tijelu ovog objekta.

Ponekad ćete primijetiti da se u jednoj liniji postavljaju nekoliko varijabli, a svaka varijabla je odvojena zarezom. Također imajte na umu da varijabla ima prefiks @. U tijelu postupka možete postaviti varijablu na željenu lokaciju. Na primjer, varijabla @ NAME1 može se proglasiti bliže kraju tijela postupka. Da biste dodijelili vrijednost deklarirane varijable, koristi se skup osobnih podataka. Za razliku od situacije kada se u jednoj liniji proglašava više od jedne varijable, u ovoj se situaciji koristi samo jedan skup osobnih podataka.

Često korisnici postavljaju pitanje: "Kako dodijeliti više vrijednosti u jednom operatoru u tijelu postupka?" Pa. Pitanje je zanimljivo, ali mnogo je lakše učiniti nego što mislite. Odgovor: pomoću parova kao što je "Select Var = value". Možete koristiti te parove, odvajajući ih zarezom.

3. Izrada SQL pohranjenog postupka

U različitim primjerima ljudi pokazuju kako izraditi jednostavnu pohranjenu proceduru i izvršiti je. Međutim, postupak može poduzeti takve parametre da proces pozivanja ima vrijednosti koje su bliske njoj (ali ne uvijek). Ako se podudaraju, tada odgovarajuće procese počinju unutar tijela. Na primjer, ako izradite postupak koji će odvesti grad i regiju od pozivatelja i vratiti podatke o tome koliko je autora povezano s relevantnim gradom i regijom. Postupak će uputiti tablice autora baze podataka, na primjer, Pubovi, za izvođenje tog broja autora. Da biste, primjerice, dobili ove baze podataka, Google učitava SQL skriptu s SQL2005 stranice.

U prethodnom primjeru, postupak poduzima dva parametra, koji će na engleskom jeziku biti konvencionalno nazvani @State i @City. Vrsta podataka odgovara vrsti definiranoj u prijavi. Tijelo postupka ima interne varijable @TotalAuthors (sve autore), a ova se varijabla koristi za prikaz njihova broja. Zatim postoji odjeljak za odabir upita, koji se sve računa. Konačno, izračunata vrijednost se prikazuje u izlaznom prozoru pomoću operatora ispisa.

Kako izvršiti pohranjenu proceduru u SQL-u

Postoje dva načina za obavljanje postupka. Prva staza prikazuje parametre koji prolaze, budući da se popis imena odvojenih zarezima izvodi nakon naziva postupka. Pretpostavimo da imamo dvije vrijednosti (kao u prethodnom primjeru). Ove se vrijednosti prikupljaju pomoću varijabli parametara @State i @City. Red je važan u ovom načinu davanja parametara. Ova se metoda naziva redni prijenos argumenata. U drugom načinu, parametri su već izravno dodijeljeni, au ovom slučaju redoslijed nije važan. Ova druga metoda je poznata kao prijenos nazvanih argumenata.

Postupak može odstupati od tipičnog. Sve je isto kao u prethodnom primjeru, ali samo su ovdje parametri pomaknuti. To znači da je @City parametar pohranjen prvi, a @State je pohranjen pored zadane vrijednosti. Zadana postavka obično je odvojena. Pohranjene SQL procedure prolaze kao jednostavni parametri. U tom slučaju, pod uvjetom, parametar "UT" zamjenjuje zadanu vrijednost "CA". U drugom izvršenju, za parametar @City prošao je samo jednu argumentnu vrijednost, a parametar @State uzima zadanu vrijednost "CA". Iskusni programeri savjetuju da se sve varijable prema zadanim postavkama nalaze bliže kraju popisa parametara. Inače, izvršenje nije moguće, a zatim morate raditi s prijenosom navedenih argumenata, što je duže i teže.

4. SQL Server pohranjeni postupci: Metode povratka

Postoje tri važna načina za slanje podataka u pohranjenu proceduru pod nazivom. Navedene su sljedeće:

- vratiti vrijednost pohranjene procedure;

- izlaz parametra pohranjene procedure;

- Odaberite jednu od pohranjenih postupaka.

4.1 Vraćanje vrijednosti SQL pohranjenih postupaka

U ovoj tehnici, postupak dodjeljuje vrijednost lokalnoj varijabli i vraća je. Postupak također može izravno vratiti konstantnu vrijednost. U sljedećem primjeru stvorili smo postupak koji vraća ukupan broj autora. Ako usporedite ovaj postupak s prethodnim, možete vidjeti da je vrijednost za ispis zamijenjena suprotnim.

Sada ćemo vidjeti kako izvršiti postupak i izlaziti vrijednost koja se vraća na njega. Izvođenje postupka zahtijeva postavljanje varijable i ispis, koji se provodi nakon svega ovog procesa. Imajte na umu da umjesto ispisa ispisa možete koristiti operatera Odabir, na primjer, Odaberi @RetValue i također OutputValue.

4.2 Izađite iz parametra SQL pohranjene procedure

Vrijednost odgovora može se koristiti za vraćanje jedne varijable, što smo vidjeli u prethodnom primjeru. Upotrebom izlaznog parametra omogućuje se postupak slanja jedne ili više varijabilnih vrijednosti pozivatelju. Izlazni parametar označava ovaj put kao ključna riječ "Izlaz" prilikom izrade postupka. Ako je parametar naveden kao izlazni parametar, objekt postupka mora dodijeliti vrijednost. Pohranjeni SQL postupci, čiji se primjeri mogu vidjeti u nastavku, vraćaju se sa sažetim informacijama.

U našem primjeru postoje dva izlaznog naziva: @TotalAuthors i @TotalNoContract. Navedeni su u popisu parametara. Te varijable dodjeljuju vrijednosti unutar tijela postupka. Kada koristimo izlazne parametre, pozivatelj može vidjeti vrijednost postavljenu unutar tijela postupka.

Osim toga, u prethodnom scenariju, dvije varijable su deklarirane kako bi vidjeli vrijednosti koje postavljaju pohranjene procedure MS SQL Server u izlazni parametar. Zatim se postupak provodi dobavljanjem normalne vrijednosti parametra "CA". Izlazni su sljedeći parametri i stoga se deklarirane varijable prenose u utvrđenom redoslijedu. Imajte na umu da je prilikom donošenja varijabli ovdje navedena i izlazna ključna riječ. Nakon što je postupak uspješno dovršen, vrijednosti koje se vraćaju izlaznih parametara prikazuju se u prozoru poruka.

4.3 Odaberite jedan od SQL pohranjenih postupaka

Ta se tehnika koristi za vraćanje skupa vrijednosti kao tablice podataka (RecordSet) u pozvani pohranjeni postupak. U ovom primjeru, SQL pohranjena procedura s parametrima @AuthID upita tablicu Autori filtriranjem vraćenih zapisa pomoću ovog @AuthId parametra. Odabir operatora odlučuje što treba vratiti pohranjenoj proceduri poziva. Pri izvršavanju pohranjene procedure AuthId se vraća natrag. Takav postupak ovdje uvijek daje samo jedan zapis ili ništa. No pohranjena procedura nema ograničenja za povrat više od jednog zapisa. Često je moguće pronaći primjere u kojima povrat podataka pomoću odabranih parametara uz sudjelovanje izračunatih varijabli događa se davanjem nekoliko sažetih vrijednosti.

U zaključku

Pohranjena procedura je prilično ozbiljan softverski modul koji vraća ili prenosi, a također uspostavlja potrebne varijable putem aplikacije klijenta. Budući da se pohranjena procedura izvršava na samom poslužitelju, razmjena podataka u ogromnim količinama između poslužitelja i aplikacije klijenta (za neke izračune) može se izbjeći. To vam omogućuje da smanjite opterećenje na SQL poslužitelju, koji, naravno, ide ruku pod ruku svojim nositeljima. Jedna od podvrsta je T SQL pohranjene procedure, no one moraju proučavati oni koji stvaraju impresivne baze podataka. Postoji i veliki, čak i veliki broj nijansi koji mogu biti korisni u proučavanju pohranjenih postupaka, ali to je više za one koji se planiraju uključiti u programiranje, uključujući i profesionalno.