Izrada procedure sql poslužitelja. Stvaranje pohranjenih procedura u Microsoft sql poslužitelju. Promjena pohranjene procedure u T-SQL

Ova tema opisuje kako možete stvoriti Transact-SQL pohranjenu proceduru u SQL Server Management Studio pomoću naredbe Transact-SQL CREATE PROCEDURE.

Prije nego počnete, izvršite sljedeće korake. Dozvole

Napravite postupak pomoću: SQL Server Management Studio, Transact-SQL

Izvođenje ove izjave zahtijeva dopuštenje CREATE PROCEDURE za bazu podataka i dopuštenje ALTER za shemu u kojoj se kreira procedura.

Možete koristiti jednu od sljedećih metoda:

SQL Server Management Studio

Korištenje SQL Server Management Studio

Stvorite proceduru u Object Exploreru

U Istraživač objekata Povežite se s instancom Database Engine i implementirajte je.

Razvijte čvor uzastopno Baza podataka, baza podataka AdventureWorks2012 i čvor Programiranje.

Desni klik na element Pohranjene procedure i odaberite Stvorite pohranjenu proceduru.

Na jelovniku Zahtjev odaberite stavku Navedite vrijednosti za parametre predloška.

U dijaloškom okviru Postavljanje vrijednosti za parametre predloška Unesite sljedeće vrijednosti za prikazane parametre.

Parametar	Značenje
Autor	tvoje ime
datum nastanka	Današnji datum
Opis	Vraća podatke o zaposlenicima.
Naziv_procedure
@Param1	@Prezime
@Datatype_For_Param1	nvarchar(50)
Zadana_vrijednost_za_parametar1	NULL
@Param2	@Ime
@Datatype_For_Param2	nvarchar(50)
Zadana_vrijednost_za_parametar2	NULL

Pritisnite gumb u redu.
U uređivač upita zamijenite naredbu SELECT sljedećom naredbom:

Za provjeru sintakse odaberite Raščlanjivanje na jelovniku Zahtjev. Ako se vrati poruka o pogrešci, usporedite upute s gornjim i izvršite ispravke ako je potrebno.
Za izradu postupka, u izborniku Zahtjev odaberite stavku Izvršiti. Procedura se kreira kao objekt u bazi podataka.
Da biste vidjeli postupak u Object Exploreru, desnom tipkom miša kliknite element Pohranjene procedure i odaberite Ažuriraj.
Da biste izvršili proceduru, u Object Exploreru desnom tipkom miša kliknite naziv pohranjene procedure HumanResources.uspGetEmployeesTest i odaberite Izvršavanje pohranjene procedure.
U prozoru Provođenje postupka Unesite Margheim kao vrijednost za parametar @LastName i Diane kao vrijednost za parametar @FirstName.

Korištenje Transact-SQL-a

Stvaranje procedure u uređivaču upita

U Istraživač objekata Povežite se s instancom Database Engine.

Na jelovniku Datoteka odaberite stavku Kreirajte zahtjev.

Kopirajte sljedeći primjer u prozor upita i kliknite gumb Izvršiti. Ovaj primjer stvara istu pohranjenu proceduru kao prije, ali s drugačijim nazivom procedure.

KORISTITE AdventureWorks2012; KRENITE STVARATI PROCEDURE HumanResources .uspGetEmployeesTest2 @LastName nvarchar (50) , @FirstName nvarchar (50) AS SET NOCOUNT ON ; SELECT Ime, Prezime, Odjel FROM HumanResources.vEmployeeDepartmentHistory WHERE FirstName = @FirstName AND LastName = @LastName AND EndDate IS NULL; IĆI

Da biste dovršili postupak, kopirajte sljedeći primjer u prozor upita koji kreirate i kliknite gumb Izvršiti. Prikazane su različite metode za postavljanje vrijednosti parametara.

Kada rade sa SQL Serverom, korisnici mogu kreirati vlastite procedure koje provode određene akcije. Pohranjene procedure su punopravni objekti baze podataka i stoga je svaka od njih pohranjena u određenu bazu podataka. Izravan poziv pohranjenoj proceduri moguć je samo ako se to radi u kontekstu baze podataka u kojoj se procedura nalazi.

Vrste pohranjenih procedura

SQL Server ima nekoliko vrsta pohranjenih procedura.

Pohranjene procedure sustava dizajnirane su za izvođenje raznih administrativnih radnji. Gotovo sve aktivnosti administracije poslužitelja obavljaju se uz njihovu pomoć. Možemo reći da su sistemske pohranjene procedure 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. Pohranjene procedure sustava imaju prefiks sp_ i pohranjuju se u baza sustava podataka i može se pozvati u kontekstu bilo koje druge baze podataka.

Prilagođene pohranjene procedure provode određene radnje. Pohranjene procedure su punopravni objekt baze podataka. Kao rezultat toga, svaka pohranjena procedura nalazi se u određenoj bazi podataka, gdje se i izvršava.

Privremeno pohranjene procedure postoje samo kratko vrijeme, nakon čega ih poslužitelj automatski uništava. Dijele se na lokalne i globalne. Lokalne privremene pohranjene procedure mogu se pozvati samo iz veze u kojoj su stvorene. Kada kreirate takvu proceduru, morate joj dati naziv koji počinje s jednim znakom #. Kao i svi privremeni objekti, pohranjene procedure ove vrste automatski se brišu kada korisnik prekine vezu ili se poslužitelj ponovno pokrene ili zaustavi. Globalne privremene pohranjene procedure dostupne su svakoj vezi s poslužitelja koji ima istu proceduru. Da biste ga definirali, samo mu dodijelite naziv koji 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.

Okidači

Okidači su vrsta pohranjene procedure. Izvode 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.

Okidač je prevedena SQL procedura čije je izvođenje uvjetovano pojavom određenih događaja unutar relacijske baze podataka. Korištenje okidača je većinom vrlo zgodno za korisnike baze podataka. Ipak, njihova uporaba često uključuje dodatne troškove resursa za I/O operacije. Kada se isti rezultati (s mnogo manje troškova) mogu postići korištenjem pohranjenih procedura ili aplikacijskih programa, upotreba okidača nije praktična.

Okidači je poseban SQL poslužiteljski alat koji se koristi za održavanje integriteta podataka u bazi podataka. Ograničenja integriteta, pravila i zadane vrijednosti možda neće uvijek postići željenu razinu funkcionalnosti. Često je potrebno implementirati složene algoritme za provjeru podataka kako bi se osigurala njihova pouzdanost i stvarnost. Osim toga, ponekad morate pratiti promjene u tabličnim vrijednostima tako da se povezani podaci mogu mijenjati prema potrebi. Okidače možemo zamisliti kao neku vrstu filtara koji stupaju na snagu nakon što su sve operacije dovršene u skladu s pravilima, standardnim vrijednostima itd.

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

Stvara okidač samo vlasnik baze podataka. Ovo ograničenje omogućuje izbjegavanje slučajnih promjena strukture tablica, načina povezivanja drugih objekata s njima itd.

Okidač To je vrlo koristan, au isto vrijeme opasan lijek. Dakle, ako je logika njegovog rada netoč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 implicitno izvršava kad god se dogodi događaj okidača i nema argumenata. Aktiviranje se ponekad naziva okidanjem okidača. Korištenjem okidača postižu se sljedeći ciljevi:

Potvrđivanje ispravnosti unesenih podataka i provođenje složenih ograničenja integriteta podataka koje je teško, ako ne i nemoguće, održavati 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;

prikupljanje revizijskih informacija bilježenjem informacija o učinjenim promjenama i osobama koje su ih izvršile;

podrška za replikaciju.

Osnovni format naredbe CREATE TRIGGER prikazan je u nastavku:

<Определение_триггера>::=

CREATE TRIGGER trigger_name

PRIJE | NAKON<триггерное_событие>

NA<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

Događaji okidača sastoje se od umetanja, brisanja i ažuriranja redaka u tablici. U potonjem slučaju možete navesti određene nazive stupaca tablice za događaj okidača. Vrijeme okidača određeno je pomoću ključnih riječi PRIJE ( Okidač pokreće se prije nego što se izvrše događaji povezani s njim) ili NAKON (nakon što se izvrše).

Radnje koje izvodi okidač navedene su za svaki redak (ZA SVAKI REDAK) obuhvaćen događajem ili samo jednom za svaki događaj (ZA SVAKI IZJAV).

Neispravno napisani okidači mogu dovesti do ozbiljnih problema, poput mrtvih brava. Okidači mogu blokirati mnoge resurse na dulja vremena, stoga posebnu pozornost treba obratiti na minimiziranje sukoba pristupa.

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

MySQL 5 ima mnogo novih značajki, od kojih je jedna od najznačajnijih stvaranje pohranjenih procedura. U ovom vodiču govorit ću o tome što su oni i kako vam mogu olakšati život.

Uvod

Pohranjena procedura je način za kapsuliranje radnji koje se ponavljaju. Pohranjene procedure mogu deklarirati varijable, manipulirati protokom podataka i koristiti druge tehnike programiranja.

Razlog za njihov nastanak je jasan i potvrđuje ga česta uporaba. S druge strane, ako razgovarate s onima koji s njima neredovito surađuju, mišljenja će se podijeliti na dvije potpuno suprotne strane. Ne zaboravi ovo.

Iza

Dijeljenje logike s drugim aplikacijama. Pohranjene procedure enkapsuliraju funkcionalnost; ovo omogućuje povezivanje za pristup podacima i upravljanje 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.
Duplicirate svoju logiku aplikacije na dva mjesta: kod poslužitelja i kod za pohranjene procedure, čime komplicirate proces manipulacije podacima.
Migracija s jednog DBMS-a na drugi (DB2, SQL Server, itd.) može dovesti do problema.

Alat s kojim radim zove se MySQL Query Browser, što je prilično standardno za interakciju s bazama podataka. Alat naredbeni redak MySQL je još jedan odličan izbor. Razlog zašto vam ovo govorim je taj što svačiji omiljeni phpMyAdmin ne podržava pokretanje pohranjenih procedura.

Usput, koristim osnovnu strukturu tablice kako bih vam olakšao razumijevanje ove teme. Govorim o pohranjenim procedurama, a one su dovoljno složene da zahtijevaju dubljenje u glomaznu strukturu tablice.

Korak 1: Postavite limiter

Razdjelnik je znak ili niz znakova koji se koristi da pokaže MySQL klijentu da ste završili pisanje SQL izraza. Godinama je točka i zarez bio razdjelnik. Međutim, može doći do problema jer u pohranjenoj proceduri može postojati više izraza, od kojih svaki mora završavati točkom i zarezom. U ovom vodiču koristim niz "//" kao razdjelnik.

Korak 2: Kako raditi s pohranjenim procedurama

Stvaranje pohranjene procedure

DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER KOMENTAR "Procedura" BEGIN SELECT "Hello World!"; KRAJ//

Prvi dio koda stvara pohranjenu proceduru. Sljedeći sadrži neobavezne parametre. Zatim dolazi naziv i, konačno, tijelo samog postupka.

Imena pohranjenih procedura razlikuju velika i mala slova. Također ne možete kreirati više procedura s istim imenom. Unutar pohranjene procedure ne mogu postojati izrazi koji mijenjaju samu bazu podataka.

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

Da biste pozvali pohranjenu proceduru, morate upisati ključna riječ CALL, zatim naziv procedure, au zagradama navesti parametre (varijable ili vrijednosti). Zagrade su obavezne.

CALL ime_spremljene_procedure (param1, param2, ....) CALL procedure1(10 , "string parameter" , @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.

Uklanjanje pohranjene procedure

ISPUSTI POSTUPAK AKO POSTOJI p2;

Ovaj jednostavna naredba. Naredba IF EXISTS hvata pogrešku ako takva procedura ne postoji.

Korak 3: Opcije

Pogledajmo kako možemo proslijediti parametre pohranjenoj proceduri.

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.

Naravno, možete navesti nekoliko parametara različitih vrsta.

Primjer IN parametra

DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS rezultat; KRAJ//

Primjer OUT parametra

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Ovo je test"; KRAJ //

Primjer INOUT parametra

DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; KRAJ //

Korak 4: Varijable

Sada ću vas naučiti kako stvoriti varijable i pohraniti ih unutar procedura. Morate ih deklarirati eksplicitno na početku bloka BEGIN/END, zajedno s njihovim tipovima podataka. Nakon što ste deklarirali varijablu, možete je koristiti na isti način kao varijable sesije, literale ili imena stupaca.

Sintaksa deklaracije varijable izgleda ovako:

DECLARE varname DATA-TYPE DEFAULT zadana vrijednost;

Deklarirajmo neke varijable:

DECLARE a, b INT DEFAULT 5; DEKLIRAJTE str VARCHAR(50); PROGLASI danas TIMESTAMP DEFAULT CURRENT_DATE; DEKLIRAJ v1, v2, v3 TINYINT;

Rad s varijablama

Nakon što ste deklarirali varijablu, možete postaviti njezinu vrijednost pomoću naredbi SET ili SELECT:

DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DEKLIRAJTE str VARCHAR(50); PROGLASI danas TIMESTAMP DEFAULT CURRENT_DATE; DEKLIRAJ v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "Ja sam niz"; SELECT CONCAT(str,paramstr), danas FROM table2 WHERE b >=5; KRAJ //

Korak 5: Kontrolne strukture niti

MySQL podržava IF, CASE, ITERATE, LEAVE LOOP, WHILE i REPEAT konstrukcije za kontrolu niti unutar pohranjene procedure. Pogledat ćemo kako koristiti IF, CASE i WHILE jer se oni najčešće koriste.

IF dizajn

Koristeći IF konstrukciju, možemo izvršiti zadatke koji sadrže uvjete:

DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE varijabla1 INT; SET varijabla1 = param1 + 1; IF varijabla1 = 0 THEN SELECT varijabla1; ZAVRŠI AKO; IF param1 = 0 THEN SELECT "Vrijednost parametra = 0"; ELSE SELECT "Vrijednost parametra<>0"; KRAJ AKO; KRAJ //

CASE dizajn

CASE je još jedna metoda testiranja uvjeta i odabira odgovarajućeg rješenja. Ovo je sjajan način za zamjenu mnogih IF konstrukcija. Konstrukcija se može opisati na dva načina, pružajući fleksibilnost u upravljanju višestrukim uvjetnim izrazima.

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE varijabla1 INT; SET varijabla1 = param1 + 1; CASE varijabla1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (varijabla1); ELSE INSERT INTO table1 VALUES (99); END CASE; KRAJ //

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE varijabla1 INT; SET varijabla1 = param1 + 1; CASE WHEN varijabla1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN varijabla1 = 1 THEN INSERT INTO table1 VALUES (varijabla1); ELSE INSERT INTO table1 VALUES (99); END CASE; KRAJ //

WHILE dizajn

Tehnički, postoje tri vrste petlji: petlja WHILE, petlja LOOP i petlja REPEAT. Također možete raditi u petlji pomoću tehnike programiranja Darth Vader: GOTO naredbe. Evo primjera petlje:

DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE varijabla1, varijabla2 INT; SET varijabla1 = 0; WHILE varijabla1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Korak 6: Kursori

Kursori se koriste za prelazak skupa redaka koje vraća upit i obradu svakog retka.

MySQL podržava kursore u pohranjenim procedurama. Ovdje je kratka sintaksa za stvaranje i korištenje kursora.

DECLARE cursor-name CURSOR FOR SELECT ...; /*Deklariranje kursora i njegovo popunjavanje */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Što učiniti kada više nema zapisa*/ OPEN cursor-name; /*Otvori kursor*/ FETCH naziv kursora INTO varijabla [, varijabla]; /*Dodijeli vrijednost varijabli koja je jednaka trenutnoj vrijednosti stupca*/ CLOSE cursor-name; /*Zatvori kursor*/

U ovom primjeru izvršit ćemo neke jednostavne operacije pomoću kursora:

DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DEKLIRAJ NASTAVI OBRAĐIVAČ ZA NIJE PRONAĐEN SKUP b = 1; OTVORENO cur1; POSTAVITI b = 0; POSTAVITI c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; ZAVRŠI AKO; END WHILE; ZATVORI cur1; POSTAVITE param1 = c; KRAJ //

Kursori imaju tri svojstva koja morate razumjeti kako biste izbjegli dobivanje neočekivanih rezultata:

Nije osjetljivo: kursor koji se otvori jednom neće odražavati promjene u tablici koje se dogode kasnije. U stvarnosti, MySQL ne jamči da će kursor biti ažuriran, stoga se nemojte oslanjati na njega.
Samo za čitanje: kursori se ne mogu mijenjati.
Bez premotavanja unatrag: kursor se može kretati samo u jednom smjeru - naprijed, nećete moći preskakati retke bez da ih odaberete.

Zaključak

U ovom vodiču upoznao sam vas s osnovama rada s pohranjenim procedurama i nekim od specifičnih svojstava povezanih s tim. Naravno, morat ćete produbiti svoje znanje u područjima kao što su sigurnost, SQL izrazi i optimizacija prije nego što postanete pravi MySQL procedure guru.

Trebali biste izračunati prednosti korištenja pohranjenih procedura u vašoj specifičnoj aplikaciji, a zatim kreirati samo potrebne procedure. Općenito, koristim postupke; Po mom mišljenju, isplati ih se implementirati u projekte zbog njihove sigurnosti, održavanja koda i cjelokupnog dizajna. Također, imajte na umu da su MySQL procedure još u tijeku. Očekujte poboljšanja u pogledu funkcionalnosti i poboljšanja. Slobodno podijelite svoje mišljenje.

Pohranjeni postupak pohranjena procedura) je imenovani programski objekt baze podataka. SQL Server ima nekoliko vrsta pohranjenih procedura.

Pohranjene procedure sustava sistemska pohranjena procedura) isporučuju programeri DBMS-a i koriste se za izvođenje radnji sa sistemskim imenikom ili dobivanje informacije o sustavu. Njihova imena obično počinju s prefiksom "sp_". Sve vrste pohranjenih procedura pokrećete pomoću naredbe EXECUTE, koja se može skratiti na EXEC. Na primjer, pohranjena procedura sp_helplogins, pokrenuta bez parametara, proizvodi dva izvješća o nazivima računa (Engleski) prijave) i odgovarajuće korisnike u svakoj bazi podataka (Engleski) korisnici).

EXEC sp_helplogins;

Da biste dobili ideju o radnjama koje se izvode pomoću sistemskih pohranjenih procedura, tablica 10.6 prikazuje neke primjere. Ukupno postoji više od tisuću sistemskih pohranjenih procedura u SQL Serveru.

Tablica 10.6

Primjeri pohranjenih procedura sustava SQL Server

Korisnik može kreirati pohranjene procedure u korisničkim bazama podataka i u bazi podataka za privremene objekte. U potonjem slučaju, pohranjena procedura će biti temporalni. Kao i kod privremenih tablica, naziv privremene pohranjene procedure mora započeti s prefiksom "#" ako se radi o lokalnoj privremeno pohranjenoj proceduri ili s "##" ako je globalna. Lokalna privremena procedura može se koristiti samo unutar veze u kojoj je kreirana, globalna se može koristiti i unutar drugih veza.

Programabilni objekti SQL Servera mogu se stvoriti pomoću Transact-SQL alata ili sklopova (Engleski) sklop) u CRL (Common Language Runtime) okruženju Microsoft.Net Frameworka. Ovaj vodič će pokriti samo prvu metodu.

Za stvaranje pohranjenih procedura upotrijebite naredbu CREATE PROCEDURE (može se skratiti na PROC), čiji je format naveden u nastavku:

CREATE (PROC I PROCEDURE) proc_name [ ; broj]

[(gparameter data_type)

[“zadano] |

[S [ ,...n ] ]

[ZA REPLIKACIJU]

AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )

Ako se pohranjena procedura (ili okidač, funkcija, pogled) kreira s opcijom ŠIFRIRANJE, njezin se kod transformira na takav način da tekst postaje nečitljiv. U isto vrijeme, kao što je navedeno u, korišteni algoritam je prenesen iz ranije verzije SQL Server se ne može smatrati pouzdanim zaštitnim algoritmom - postoje uslužni programi koji vam omogućuju brzo obavljanje obrnute pretvorbe.

Opcija RECOMPILE specificira da će sustav ponovno kompajlirati tekst svaki put kada se pozove procedura. U normalnom slučaju, procedura sastavljena pri prvom pokretanju pohranjuje se u predmemoriju, što omogućuje povećanu izvedbu.

EXECUTE AS specificira sigurnosni kontekst u kojem se procedura treba izvršiti. Zatim, jedna od vrijednosti f CALLER | SEBI | VLASNIK | "Korisničko ime"). CALLER je zadana vrijednost i znači da će se kôd izvršiti u sigurnosnom kontekstu korisnika koji poziva ovaj modul. Sukladno tome, korisnik mora imati dopuštenja ne samo za sam programabilni objekt, već i za druge objekte baze podataka na koje on utječe. EXECUTE AS SELF znači korištenje konteksta korisnika koji stvara ili mijenja programabilni objekt. OWNER specificira da će se kôd izvršiti u kontekstu trenutnog vlasnika procedure. Ako za njega nije naveden vlasnik, tada se pretpostavlja vlasnik sheme kojoj pripada. EXECUTE AS "user_name" omogućuje eksplicitno navođenje korisničkog imena (u jednostrukim navodnicima).

Parametri se mogu specificirati za proceduru. To su lokalne varijable koje se koriste za prosljeđivanje vrijednosti proceduri. Ako je parametar deklariran ključnom riječi OUTPUT (ili skraćeno OUT), to je izlazna vrijednost: vrijednost koja mu je dana u proceduri nakon njezina završetka može se koristiti od strane programa koji je pozvao proceduru. Ključna riječ READONLY znači da se vrijednost parametra ne može promijeniti unutar pohranjene procedure.

Parametrima se mogu dodijeliti zadane vrijednosti, koje će se koristiti ako vrijednost parametra nije eksplicitno navedena prilikom pozivanja procedure. Pogledajmo primjer:

CREATE PROC surma (@a int, @b int=0,

©result int OUTPUT) AS

POSTAVITE @rezultat=0a+0b

Napravili smo proceduru s tri parametra, a parametar @b ima zadanu vrijednost =0, a parametar @result je izlazni parametar: vraća vrijednost programu koji poziva. Izvedene radnje su prilično jednostavne - izlazni parametar prima vrijednost zbroja dvaju ulaznih.

Kada radite u SQL Server Management Studio, stvorena pohranjena procedura može se pronaći u odjeljku programabilnih objekata baze podataka (Engleski) Programmability) u pododjeljku za pohranjene procedure (Sl. 10.2).

Kada pozivate proceduru, možete koristiti i varijable i konstante kao ulazne parametre. Pogledajmo dva primjera. U prvom su ulazni parametri procedure eksplicitno navedeni kao konstante, a ključna riječ OUTPUT navedena je za izlazni parametar u pozivu. Druga opcija koristi vrijednost varijable kao prvi ulazni parametar i navodi da se zadana vrijednost treba koristiti za drugi parametar pomoću ključne riječi DEFAULT:

Riža. 10.2.

DEKLIRAJ @s int;

EXEC suma 10.5,@c IZLAZ;

ISPIS 0c; – Prikazat će se 15

DEKLARACIJA Gi int = 5;

– prilikom poziva koristite zadanu vrijednost

EXEC suma Gi,DEFAULT , 0c IZLAZ;

ISPIS 0c; – Prikazat će se 5

Razmotrimo sada primjer s analizom povratnog koda s kojim završava procedura. Pretpostavimo da trebamo izračunati koliko je knjiga u tablici Bookl objavljeno u određenom rasponu godina. Štoviše, ako je početna godina veća od konačne godine, postupak vraća "1" i ne broji se, u suprotnom, brojimo broj knjiga i vraćamo 0:

CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS

AKO 0FirsYear>0LastYear POVRATAK 1

SET @result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN 0FirsYear AND 0LastYear);

Razmotrimo varijantu poziva ove procedure, u kojoj se povratni kod sprema u cjelobrojnu varijablu 0ret, nakon čega se analizira njegova vrijednost (u ovom slučaju to će biti 1). Funkcija CAST koja se koristi u naredbi PRINT koristi se za pretvaranje vrijednosti cjelobrojne varijable Gres u tip niza:

IZJAVI 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT "Početna godina je veća od završne godine"

PRINT "Broj knjiga" + CAST(Gres as varchar(20))

Pohranjene procedure ne samo da mogu čitati podatke iz tablice, već i mijenjati podatke, pa čak i stvarati tablice i niz drugih objekata baze podataka.

Međutim, ne možete kreirati sheme, funkcije, okidače, procedure i poglede iz pohranjene procedure.

Sljedeći primjer ilustrira ove mogućnosti i probleme koji se odnose na opseg privremenih objekata. Sljedeća pohranjena procedura provjerava postojanje privremene tablice #TaL2; ako ta tablica ne postoji, kreira je. Nakon toga, vrijednosti dva stupca se unose u tablicu #TaL2, a sadržaj tablice se prikazuje pomoću naredbe SELECT:

CREATE PROC My_Procl (@id int, @name varchar(30))

AKO JE OBJECT_ID("tempdb.dbo.#Tab21) NULL

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

SELECT * FROM dbo. #Tab2 –№1

Prije prvog pozivanja pohranjene procedure, stvorit ćemo privremenu tablicu #TaL2 koja se u njoj koristi. Obratite pozornost na operator EXEC. U prethodnim primjerima, parametri su proslijeđeni proceduri “po poziciji”, ali u ovom slučaju se koristi drugačiji format za prosljeđivanje parametara – “po imenu”, naziv parametra i njegova vrijednost su eksplicitno navedeni:

CREATE TABLE dbo.#Tab2 (id int, name varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

U gornjem primjeru, naredba SELECT bit će obrađena dva puta: prvi put – unutar procedure, drugi put – iz fragmenta pozivnog koda (označenog komentarom "br. 2").

Prije drugog poziva procedure izbrisat ćemo privremenu tablicu #TaL2. Tada će se iz pohranjene procedure stvoriti privremena tablica istog imena:

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

U tom će slučaju samo naredba SELECT koja se nalazi unutar procedure (s komentarom "Xa 1") prikazati podatke. Izvođenje SELECT "No. 2" rezultirat će pogreškom, budući da će privremena tablica stvorena u pohranjenoj proceduri već biti izbrisana iz tempdb baze podataka u trenutku kada se procedura vrati.

Možete ispustiti pohranjenu proceduru pomoću naredbe DROP PROCEDURE. Njegov format predstavljen je u nastavku. Možete izbrisati nekoliko pohranjenih procedura jednom naredbom, navodeći ih odvojene zarezima:

DROP (PROC I POSTUPAK) ( postupak ) [

Na primjer, izbrišite prethodno kreiranu summa proceduru:

DROP PROC summa;

Možete promijeniti postojeću proceduru (i zapravo je redefinirati) pomoću naredbe ALTER PROCEDURE (dopušteno

kratica PROC). S iznimkom ključne riječi ALTER, format naredbe je u biti isti kao kod CREATE PROCEDURE. Na primjer, promijenimo dbo proceduru. rownum, postavljajući ga da se izvršava u sigurnosnom kontekstu vlasnika:

ALTER PROC dbo.rownum (SFirsYear int,

SLLastYear int, Result int OUTPUT)

S IZVRŠI KAO Vlasnik – opcija koja se može instalirati

IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN SFirsYear AND SLastYear);

U nekim slučajevima može biti potrebno dinamički generirati naredbu i izvršiti je na poslužitelju baze podataka. Ovaj problem se također može riješiti korištenjem EXEC operatora. Primjer u nastavku dohvaća zapise iz tablice Bookl ako je atribut Year jednak vrijednosti navedenoj varijablom:

DEKLIRAJTE 0u int = 2000;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

Izvršenjem dinamički generiranih instrukcija stvaraju se preduvjeti za provedbu računalnih napada kao što je “SQL injection” (Engleski) SQL injekcija). Bit napada je da napadač ubaci vlastiti SQL kod u dinamički generirani upit. To se obično događa kada se parametri koji se zamjenjuju uzimaju iz rezultata korisničkog unosa.

Malo promijenimo prethodni primjer:

DEKLARIRAJTE 0u varchar(100);

POSTAVITE 0u="2OOO"; – dobili smo od korisnika

Ako pretpostavimo da smo vrijednost niza dodijeljenu u naredbi SET primili od korisnika (bez obzira kako, na primjer, putem web aplikacije), tada primjer ilustrira "normalno" ponašanje našeg koda.

DEKLARIRAJTE 0u varchar(100);

SET 0u="2000; DELETE FROM dbo.Book2"; – injekcija

EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);

U takvim slučajevima preporuča se, ako je moguće, koristiti sistemsku pohranjenu proceduru sp_executcsql, koja vam omogućuje kontrolu vrste parametara, što je jedna od prepreka za SQL injekcije. Bez detaljnog razmatranja njegovog formata, pogledajmo primjer sličan prethodno prikazanom:

IZVRŠI sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y",

Ovo eksplicitno specificira vrstu parametra koji se koristi u upitu, a SQL Server će ga kontrolirati tijekom izvođenja. Slovo "N" ispred navodnika označava da je ovo Unicode doslovna konstanta, kao što zahtijeva procedura. Parametru se može dodijeliti ne samo konstantna vrijednost, već i vrijednost druge varijable.

Zadnja izmjena: 14.08.2017

Često podatkovna operacija predstavlja skup instrukcija koje se moraju izvršiti u određenom nizu. Na primjer, prilikom dodavanja kupnje proizvoda potrebno je unijeti podatke u tablicu narudžbi. Međutim, prije nego što to učinite, morate provjeriti je li proizvod koji kupujete na zalihi. Možda ćete morati provjeriti niz dodatnih uvjeta. To jest, zapravo, proces kupnje proizvoda obuhvaća nekoliko radnji koje se moraju izvršiti u određenom slijedu. I u ovom slučaju bilo bi optimalnije sve te radnje sažeti u jedan objekt - pohranjeni postupak(pohranjeni postupak).

To jest, u biti, pohranjene procedure su skup instrukcija koje se izvode kao jedna cjelina. Stoga pohranjene procedure omogućuju pojednostavljenje složenih operacija i njihovo postavljanje u jedan objekt. Promijenit će se proces kupnje proizvoda, sukladno tome bit će dovoljno promijeniti šifru postupka. Odnosno, postupak također pojednostavljuje upravljanje kodom.

Pohranjene procedure također vam omogućuju da ograničite pristup podacima u tablicama i time smanjite vjerojatnost namjernih ili nenamjernih neželjenih radnji u vezi s tim podacima.

I još jedan važan aspekt je izvedba. Pohranjene procedure obično se izvršavaju brže od običnih SQL naredbi. To je zato što se kod procedure jednom kompilira kada se prvi put pokrene, a zatim sprema u kompajliranom obliku.

Za kreiranje pohranjene procedure koristite naredbu CREATE PROCEDURE ili CREATE PROC.

Dakle, pohranjena procedura ima tri glavne značajke: pojednostavljenje koda, sigurnost i izvedba.

Na primjer, recimo da postoji tablica u bazi podataka koja pohranjuje podatke o proizvodima:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

Kreirajmo pohranjenu proceduru za dohvaćanje podataka iz ove tablice:

USE productsdb; GO CREATE PROCEDURE ProductSummary AS SELECT ProductName AS Product, Manufacturer, Price FROM Products

Budući da se naredba CREATE PROCEDURE mora pozvati u zasebnom paketu, naredba USE koja postavlja trenutnu bazu podataka slijedi naredba GO za definiranje novog paketa.

Nakon naziva procedure mora slijediti ključna riječ AS.

Da biste odvojili tijelo procedure od ostatka skripte, kod procedure se često stavlja u blok BEGIN...END:

USE productsdb; GO CREATE PROCEDURE ProductSummary AS BEGIN SELECT ProductName AS Product, Manufacturer, Price FROM Products END;

Nakon dodavanja procedure, možemo je vidjeti u čvoru baze podataka u SQL Server Management Studio u podčvoru Programabilnost -> Pohranjene procedure:

A moći ćemo kontrolirati postupak i putem vizualnog sučelja.

Provođenje postupka

Da biste izvršili pohranjenu proceduru, pozovite naredbu EXEC ili EXECUTE:

EXEC ProductSummary

Uklanjanje postupka

Za uklanjanje procedure koristite naredbu DROP PROCEDURE:

POSTUPAK ISPUŠTANJA ProductSummary

Izrada procedure sql poslužitelja. Stvaranje pohranjenih procedura u Microsoft sql poslužitelju. Promjena pohranjene procedure u T-SQL - naredba ALTER PROCEDURE