Implementarea Logicii in Afaceri: Programarea Procedurilor Stocate
O procedura stocata este un obiect al bazei de date care grupeaza una sau mai multe declaratii Transact-SQL. Princiala diferenta intre o procedura stocata si un set de declaratii este aceea ca o procedura stocata poate fii reutilizata simplu prin apelul acesteia cu numele ei. De aceea, daca doriti sa rulati codul inca odata, nu sunteti obligat sa executati intregul set de declaratii care compun procedura stocata .
Ca si creator al bazei de date, veti petrece majoritatea timpului programand, reparand si optimizand procedurile stocate deoarece ele pot fi utilizate in mii de scopuri. Nu numai ca poate fii folosita pentru a incapsula logica in afaceri pentru aplicati ile dumneavoastra, dar deasemenea pot fi lolosite in scopuri administrative in interiorul Serverului SQL.
Acest capitol va invata urmatoarele lucruri :
Avantajele folosirii procedurilor stocate
Tipurile de proceduri stocate in Serverul SQL
Tipurile de parametrii ai procedurilor stocate
Cum sa creati, modificati si sa executati proceduri stocate
Cum sa va descurcati in cazul aparitiei unor erori in cadrul procedurilor stocate
Consideratiile despre securitate cand lucrati cu proceduri stocate
Avantajele Folosirii Procedurilor Stocate
In mod normal, procedurile stocate sunt folosite pentru a incapsula sau aplica reguli de afaceri in baza dumneavoastra de date. De exemplu, daca aveti de facut anumite calcule inaintea inserarii datelor in table, puteti ingloba aceasta logica intr-o procedura stocata si apoi insera datele folosind procedurile stocate. in mod similar, daca doriti ca utilizatorii sa nu acceseze in mod direct tabelele si oricare alte obiecte, puteti creea proceduri stocate pentru a accesa aceste obiecte sis a permiteti utilizatorilor folosirea lor, in locul manipularii obiectelor in mod direct. De exemplu, Microsoft descurajeaza utilizatorii sa produca modificari in mod direct in tabelele system; cu toate acestea, Serverul SQL vine cu proceduri stocate system pentru a manipula tabelele system.
Atentie
Daca, creati aplicati i care modifica tabele sistem, ar trebui sa incetati sa faceti acest lucru. Va informam ca in viitoarele lansari ale Serverului SQL, Microsoft nu va mai permite utlizatoriloi sa modifice tabelele sistem in mod direct.
Urmatoarele sunt beneficiile si avantajele procedurilor stocate:
Sunt declaratii precompilate—Un plan de executie este creat si stocat in memorie de prima data de cand procedura stocata este rulata, si este folosit in mod consecvent defiecare data cand executati procedura stocata , in acest fel minimalizand timpul necesar rularii. Astfel este mult mai efficient decat executarea fiecarei declaratii in mod separate, una cate una, deoarece Serverul SQL ar trebui sa genereze un plan de acces pentru fiecare declaratie de fiecare data cand este rulata.
Optimizeaza traficul din retea—A-ti putea spune ca procedurile stocate nu au nici un fel de legatura cu traficul din retea. Totusi, in momentul in care executati o procedura stocata care contine multe declaratii, trebuie sa apelati procedura stocata o singura data, nu fiecare declaratie in mod separat. Cu alte cuvinte, intregul bloc de cod (intregul set de declaratii) nu are nevoie sa fie trimis de la client catre server. De exemplu, daca creati o procedura stocata cu 10 declaratii si o executati, trebuie sa trimiteti numai o singura adresare catre Serverul SQL in loc de 10 instructii separate. Aceasta se traduce printr-o optimizare a traficului din retea.
Pot fi folosite ca un mecanism de securitate—In particular, daca proprietarul unui obiect nu doreste sa acorde permisii directe utilizatorilor la baza de date a obiectelor, poate creea 838g66i o procedura stocata care manipuleaza aceste obiecte, si apoi sa acorde permisii de executare pe aceste proceduri stocate.
In acest fel, utilizatorilor vi se va permite numai sa execute aceste proceduri stocate, si nu vor putea sa manipuleze in mod direct obiectele la care fac referire procedurile stocate. Procedurile stocate sistem sunt un exemplu in acest sens. Serverul SQL pune la dispozitie proceduri stocate sistem pentru a preveni ca utilizatorii sa aiba acces direct la tabelele sistem.
Permit programarea modulara—Puteti incapsula logica dumneavoastra de afaceri in interiorul procedurilor stocate, si apoi doar sa le apelati din aplicati i. De aceea, toate declaratiile care compun o procedura stocata sunt executate ca un intreg in interiorul serverului. Mai departe, puteti incorpora logica conditionala intr-o procedura stocata folosind oricare declaratie de control (IFELSE, WHILE) disponibile in Transact-SQL.
Pot fi setate sa se execute automat cand Serverul SQL porneste—Fiecare sarcina care trebuie executata de fiecare data cand serviciul Serverul SQL starteaza poate fi programata ca o procedura de stocare si apoi configurata sa ruleze automat folosind procedura stocata sistem sp_procoption.
Pot folosi paramatrii—aceasta este una din solutiile prin care procedurile stocate primesc si retrimit date aplicati ei emitatoare. Parametrii pot fi fie de intrare, care sunt similari trecute cu valoarea, sau de iesire care se comporta ca variabile trecute cu refernta.
Tipuri de Proceduri Stocate
In Serverul SQL, exista patru tipuri de proceduri stocate: proceduri stocate sistem, proceduri stocate definite de catre utilizatori, proceduri stocate temporale si proceduri stocate extinse. Procedurile stocate sistem si extinse sunt create automat in timpul instalarii. Celelalte tipuri (definite de utilizator, temporare) sunt acelea create in mod explicit de catre utilizator.
Proceduri Stocate Sistem
Procedurile stocate sistem sunt create automat in sistemul bazelor de date in momentul instalarii Serverului SQL. De fapt ele reprezinta o cale de interactiune cu tablele sistem. Mai mult, exista o procedura stocata sistem pentru aproape orice sarcina adiministrativa pe care o executati sau pe care o aplicati in Serverul SQL. De asemenea, datorita faptului ca Microsoft nu recomanda contactul direct cu tabelele sistem, acesta este modul preferat de a interactiona cu aceste proceduri stocate. Fiecare nume de procedura stocata gobala sistem are prefixul sp_, si din acest motiv pot fi executate din orice baza de date. Paragraful II.1 demonstreaza aceasta caracteristica, apeland procedura stocata sistem sp_helpdb (care furnizeaza informatii generale despre baza de date) din baza de date Northwind.
Paragraful II.1 Executarea unei Proceduri Stocate Sistem (care este stocata in Master) din Baza de Date Northwind
USE Northwind
GO
sp_helpdb
--Afisarea a fost simplificata
name db_size owner dbid created compatibility_level
------- ----- ----- --------
master 12.19 MB sa 1 Aug 6 2000 80
model 1.13 MB sa 3 Aug 6 2000 80
msdb 13.50 MB sa 4 Aug 6 2000 80
Northwind 3.94 MB sa 6 Aug 6 2000 80
pubs 2.13 MB sa 5 Aug 6 2000 80
tempdb 8.50 MB sa 2 Jan 22 2001 80
Transact-SQL pune la dispozitie o functie sistem, OBJECTPROPERTY, care este folosita pentru a verifica varietatea propietatilor obiectului. in mod specific, 'IsMSShipped' verifica daca un obiect oarecare este un obiect sistem. Cu toate acestea, poate fi folosit pentru a identifica daca o porcedura stocata este o procedura stocata sistem. Aceasta functie sistem, ca multe altele in Serverul SQL, primeste id-ul obiectului ca un parametru, care poate fi obtinut folosind functia sistem OBJECT_ID. functia OBJECTPROPERTY returneaza 0 daca proprietatea este adevarata sau 1 in caz contrar.
Paragraful II.2 arata intrebuintarea acestei proprietati.
Paragraful II.2 Folosirea Functiei Sistem OBJECTPROPERTY Pentru a Verifica daca un Obiect a Fost Creat in Timpul Instalarii Serverului SQL
USE Master
SELECT OBJECTPROPERTY(OBJECT_ID('sp_help'),'IsMSShipped')
GO
(1 row(s) affected)
Proceduri Stocate Definite de Utilizator
Dumneavoastra create o procedura stocata definite de utilizator in Serverul SQL pentru a implementa logica in afaceri. Orice sarcina, indifferent de cat de simpla sau complexa este, care contine declaratii si conditii multiple poate fi programata ca o procedura stocata , si apoi aplicatia care o apeleaza are nevoie doar sa execute procedura stocata , in locul executarii intregului set de declaratii individual.
Procedurile stocate definite de catre utilizator sunt create folosind declaratia CREATE PROCEDURE, si apoi Serverul SQL le stocheaza in baza de date curenta.
Numele procedurilor stocate, ca orice alt nume de obiect, trebuie sa fie unice in interiorul bazei de date. Este posibil in anumite baze de date ca 2 proceduri stocate sa existe cu acelasi nume dar cu proprietary diferiti.
Orice procedura stocata care este create in baza de data principala cu prefixul sp_ --de exemplu, sp_myprocedure—poate fi accesata din interiorul oricarei alte baza de date. in general, cand o procedura stocata este executata si numele acesteia are prefixul sp_, Serverul SQL il cauta, prima data in baza de date curenta, si apoi, daca nu a fost gasita in baza de date curenta, Serverul SQL o cauta in baza de date principala.
Atentie
Daca creati o procedura stocata definite de utilizator in orice baza de date alta decat master, cu prefixul sp_, si exista o procedura stocata in baza de date principala cu acelasi nume, procedura stocata definite de utilizator care se afla in in baza de date a utilizatorului va fi executata numai cand va fi apelata din baza de date a utilizatorului. Aceasta datorita faptului ca Serverul SQL executa orice procedura stocata care contine prefixul sp_, Serverul SQL cauta prima data in baza de date curenta, si apoi in principala daca nu gaseste in baza de date curenta.
De exemplu, puteti creea o procedura stocata definita de utilizator in principala, cum este demonstrate in Paragraful II.3, si apelata din alta baza de date.
Paragraful II.3 Crearea Unei Proceduri Stocate, cu Prefixul sp_, in Principala, si Executia in Pubs
USE Northwind
GO
CREATE PROCEDURE sp_showdatabasename
AS
SELECT 'Northwind'
GO
USE Master
GO
CREATE PROCEDURE sp_showdatabasename
AS
SELECT 'Master'
GO
-- cand este executata din , Serverul SQL executa
-- aceea stocata in Northwind
USE Northwind
EXEC sp_showdatabasename
GO
-- cand este executata din Pubs, Serverul SQL executa
-- aceea stocata in Master, deoarece nu exista
-- o procedura stocata numita sp_showdatabasename
-- in baza de date Pubs
USE Pubs
EXEC sp_showdatabasename
GO
Proceduri Stocate Temporar
Exista proceduri stocate create de utilizatori si stocate in baza de date tempdb. Acestea se numesc temporare deoarece sunt dezactivate automat de Serverul SQL, doar daca nu folositi declaratia DROP PROCEDURE. Ca orice obiect temporar din Serverul SQL, cand creati proceduri stocate temporar, folositi prefixul # pentru local si prefixul ## pentru proceduri stocate temporar e globale. Paragraful II.4 arata crearea unei proceduri stocate temporare.
Paragraful II.4 Crearea Unei Proceduri Stocate Temporare
CREATE PROC #getdatabasename
AS
SELECT db_name() AS database_name
GO
De fapt, procedurile stocate temporar au aceiasi functionalitate ca si procedurile stocate definite de utilizator, cu o singura exceptie; sunt dezactivate in momentul in care conexiunea care le-a creat este dezactivata.
Observatie
O procedura stocata temporara, odata creata (si stocata automat in tempdb de catre Serverul SQL), poate fi apelata din orice baza de date.
Proceduri Stocate Extinse
Procedurile stocate extinse sunt programe LL scrise in C++ care maresc capabilitatile Serverului SQL. Ele sunt localizate in baza de date principala. Serverul SQL are setul sau de proceduri stocate extinse ale caror nume incepe cu xp_, care sunt folosite in principal in scopuri administrative. Cu toate acestea, exista anumite proceduri stocate extinse care incep cu sp_doar sa le considerati ca globale—de exemplu, sp_OACreate. puteti creea propriile dumneavoastra Proceduri stocate extinse, codificand un DLL folosind C++ si apoi adaugandu-l la Serverul SQL ca o procedura stocata extinsa, folosind procedura stocata sistem sp_addextendedproc.
Creare si Renuntarea la Procedurile Stocate
Procedurile stocate sunt create folosind declaratia CREATE PROCEDURE sau echivalentul acesteia CREATE PROC. in momentul crearii unei proceduri stocate, proprietatile sale sunt stocate in tabelul sistem sysobjects si definitiile sale (toate declaratiile pe care le contine ) in tabelul sistem syscomments system table. O procedura este stocata in baza de date curenta ; de aceea, daca doriti sa create o procedura stocata in alte baze de date, trebuie sa faceti acele baze de date curente inainte sa o create(folosind declaratia USE).
In Paragraful II.5, puteti vedea un exemplu a sintaxei folosita pentru a creea procedura stocata . Urmata de creare, aceasta arata recuperarea proprietatilor procedurei stocata , folosind sp_help, si codul acesteia, folosind sp_helptext.
Paragraful II.5 Crearea Unei Proceduri Stocate si Recuperarea Proprietatilor si Codului Acesteia
USE Northwind
GO
CREATE PROC dbo.getcurrenttime
AS
SELECT CURRENT_TIMESTAMP
GO
EXEC sp_help 'getcurrenttime'
EXEC sp_helptext 'getcurrenttime'
GO
Name Owner Type Created_datetime
getcurrenttime dbo stored procedure 2000-09-18 01:35:06.257
Text
CREATE PROC getcurrenttime
AS
SELECT CURRENT_TIMESTAMP
Exista trei pasi pe care Serverul SQL ii executa in cazul procedurilor stocate: analizare, nume rezolutie si optimizare.
Serverul SQL analizeaza o procedura stocata cand aceasta este create pentru a verifica corectitudinea sintexei. Atunci, informatia procedurii stocate este stocata in sysobjects and syscomments .
De prima data de cand procedura stocata este executata, Serverul SQL verifica daca toate obiectele la care face referire exista.
La ultimul pas, Serverul SQL gaseste un plan de executie optimizat, cautand cea mai buna cale de executie a fiecarei declaratii in interiorul procedurii stocate. Atunci, un plan de executie optimizata este generat si stocata in cache-ul procedurii, care apartine memoriei allocate de Serverul SQL in acest scop.
Figura II.1 arata acest proces in 3 pasi (analizare, nume rezolutie si optimizare)
Planul de executie a unei proceduri de stocare va ramane in memorie pana cand Serverul SQL este oprit sau cand Serverul SQL are nevoie de memoria alocata planului. De aceea, daca cache-ul procedurii devine plin, se renunta la planurile stocate cu scopul de a creea spatiu pentru a face loc celor noi.
Dupa ce planul de executie a fost creat si stocat in cache-ul procedurii(memorie), oricand executati procedura stocata , Serverul SQL trebuie doar sa refoloseasca planul pentru a manipula datele. Serverul SQL arata aceasta informatie cache daca interogati tabelul sistem syscacheobjects . aveti grija ca syscacheobjects este un tabel virtual, nu unul real. Singurul scop al acestui tabel virtual este de apune la dispozitie suport pentru proceduri interne si comenzi DBCC, si tabelul este umplut in mod automat cu date cand il utilizati.
Acest proces de generare a unui bun plan de acces implica evaluarea multo factori, cum ar fi indecsi si date in table. Acesta este unul din motivele pentru care ar trebui sa aveti indecsii in regula in tabele
O trasatura a procedurilor stocate, asa cum s-a mentionat anterior, este ca ele pot fi setate sa fie executate automat cand serviciul Server SQL este pornit. Deoarece acestea nu vor avea nici o interactiune cu nici o aplicati e, nu pot avea nici un parametru de intrare. Procedura de stocare trebuie creata de administratorul de retea in baza de date principala, si apoi procedura de stocare sistem sp_procoption trebuie utilizata cu scopul de a o seta sa fie executata in momentul in care serviciul Server SQl este pornit.
De exemplu, sa presupunem ca doriti sa stiti de fiecare data cand serviciul Server SQL a fost pornit. Pentru a realiza acest lucru, trebuie sa creati un tabel in principal pentru a stoca datele si momentul in care serviciul Server SQL a fost pornit, si apoi creati o procedura care insereaza o linie in acest tabel cu datele curente. in final, setati aceasta procedura stocata sa se execute automat de fiecare data cand Serverul SQL porneste. Paragraful II.6 arata codul folosit pentru realizarea acestor lucruri.
Paragraful II.6 Folosirea Procedurilor Stocate Sistem sp_procoption
USE Master
GO
CREATE tabel dbo.Sqlstatus (
lasttime DATETIME
GO
CREATE PROC dbo.insertsqlstatus
AS
INSERT Sqlstatus (lasttime)
VALUES (CURRENT_TIMESTAMP)
GO
EXEC sp_procoption 'insertsqlstatus','startup','true'
Observatie
O alta cale de a afla ultima data cand Serverul SQL a fost pornit este folosind coloana crdate din tabelul sistem din principal. Aceasta coloana stocheaza data creearii bazei de date, si datorita faptului ca baza de date temporara este recreate de fiecare data cand serviciul Server SQL porneste, puteti afla cand a fost ultima data cand Serverul SQL a pornit.
Cateva declaratii nu pot fi incluse in codul procedurilor stocate. Aceste declaratii sunt CREATE
DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, si CREATE VIEW.
Procedurile stocate pot fi create folosind optiunea WITH ENCRYPTION, care cripteaza definitia din tabelul sistem syscomments; de aceea, nimeni nu poate citi definitia. daca incercati sa vizualizati codul unei proceduri stocate(folosind sp_helptext sau oricare alte metode) si aceasta a fost criptata, veti vedea aceasta eroare
The object comments have been encrypted
Fiti atenti cand criptati o definitie a procedurii stocate, deoarece nu veti putea sa le afisati din nou doar daca pastrati codul sursa origina. De aceea, daca doriti sa modificati definitia unei proceduri stocatecare a fost create folosind optiunea WITH ENCRYPTION, trebuie sa folositi codul sursa original. Este mereu o idee buna sa pastrati o copie a scriptului original pe care l-ati folosit pentru a genera schema bazei de date.
Paragraful II.7 creaza procedura stocata getcurrentuser folosind optiunea WITH ENCRYPTION, si apoi incearca sa afiseze codul procedurii stocate folosind sp_helptext,fara succes.
Paragraful II.7 Crearea Unei Proceduri Stocate Folosind Optiunea WITH ENCRYPTION
USE Northwind
GO
CREATE PROC dbo.getcurrentuser
WITH ENCRYPTION
AS
SELECT USER
GO
sp_helptext 'getcurrentuser'
The object comments have been encrypted.
Folosirea parametrilor
Ca orice alta functie sau procedura din orice limbaj de programare, procedurile stocate comunica cu aplicati ile sau clintii prin intermediul parametrilor. numarul maxim de parametrii dintr-o procedura stocata este 2,100.
Cand creati proceduri stocate , pentru a putea accesa valoarea unui parametru din interiorul corpului unei proceduri stocate, trebuie doar sa specifica ti numele parametrilor(incluzand si caracterul ‘@’).
Odata creata, informatia despre parametrii procedurilor stocate este stocata in tabelul sistem syscolumns (sysobjects stocheaza informatii generale si syscomments stocheaza codul procedurii stocate).
Parametrii sunt definiti imediat dupa numele procedurilor stocate cand are loc crearea procedurilor stocate. Numele parametrilor trebuie sa aiba caracterul ‘@’ ca si character initial(ca orice variabila in Transact-SQL). dupa numele parametrului, tipul datei trebuie specifica t, si apoi o valoare implicita (valoarea implicita este optionala).
Paragraful II.8 arata un exemplu de creare a unei proceduri stocate (getemployeesbylastname) care contine un parametru (@emplastname).
Aceasta procedura stocata selecteaza angajatii care au ca nume stringul indicat de catre parametrul @emplastname. Observati ca, ;a creearea procedurii stocate, parametrii sunt declarati intre numele procedurilor stocate si cuvantul cheie AS.
Paragraful II.8 Crearea unei Proceduri Stocate Folosind Parametri
USE Northwind
GO
CREATE PROC dbo.getemployeesbylastname
@emplastname VARCHAR(40)
AS
SELECT *
FROM Employees
WHERE lastname LIKE '%'+ @emplastname + '%'
GO
Valoarea implicita a unui parametru poate fi setata cu NULL. daca parametrul nu are o valoare, trebuie sa i se aloce una de catre aplicatia apelatoare in momentul executiei procedurii stocate. Pe de alta parte, daca un parametru are o valoare implicita, aplicatia apelatoare nu trebuie sa aloce o valoare pentru acest parametru daca doreste sa foloseasca valoarea implicita.
Paragraful II.9 creaza o procedura stocata (getemployeesbylastname_default, o varianta a procedurii stocate descrisa in Paragraful II.8), care contine un parametru (@emplastname) cu o valoare implicita.
Paragraful II.9 Crearea unei Proceduri Stocate Folosind Parametrii cu Valoare Implicita
USE Northwind
GO
CREATE PROC dbo.getemployeesbylastname_default
@emplastname VARCHAR(40) = 'a'
AS
SELECT *
FROM Employees
WHERE lastname LIKE '%'+ @emplastname + '%'
GO
Exista doua tipuri de parametrii, de intrare si de iesire :
Un parametru de intrare este similar cu o variabilatrecuta printr-o valoare. De aceea, procedura stocata retine o copie a unei date si acest lucru nu afecteaza data in afara procedurii stocate. Cu alte cuvinte, daca folositi o variabila ca un parametru a unei proceduri stocate, si valoarea acestei variabile este modificata in interiorul procedurii stocate, aceasta nu schimba valoarea variabilei in afara procedurii stocate.
Un parametru de iesire este ca o variabila vazuta ca o referinta. Deoarece procedura stocata ia valoarea unui pointer al unei variabile, orice schimbare este reflectata in afara procedurii stocate. Folosind acest tip de parametru, o procedura stocata poate trimite valori inapoi aplicati ei apelatoare. Pentru a putea profita la maxim de parametrii de iesire si pentru a-i diferentia de parametrii de intrare, cuvantul cheie OUTPUT trebuie specifica t cand creati procedura stocata si de asemenea in momentul executiei.
Paragraful II.10 arata crearea unei proceduri stocate (getemployeeaddress)
care contine un parametru de intrare (@employeeaddress). Aceasta procedura stocata stocheaza adresa completa unui angajat anume in parametrul de iesire @employeeaddress. Observati ca, cuvantul cheie OUTPUT trebuie specifica t in momentul declararii parametrilor de iesire.
Paragraful II.10 Folosirea Parametrilor de Iesire si Intrare
USE Northwind
GO
CREATE PROC dbo.getemployeeaddress
@employeeid INT,
@employeeaddress NVARCHAR(120) OUTPUT
AS
SELECT @employeeaddress = address + '. '+ city + '. '+ region + '. '
postalcode + '. '+ country
FROM Employees
WHERE employeeid = @employeeid
GO
Un avantaj al folosirii procedurilor stocate este acela ca ele pot returna seturile de rezultate, folosind declaratia SELECT in corpul procedurii stocate. Totusi, una din limitarile folosirii parametrilor este aceea ca nu ii puteti folosi pentru a trece numele unui obiect al unei baze de date(tabele, coloane sau proceduri stocate) intr-o procedura stocata . in acest scop, trebuie sa construiti cerinta la momentul rularii, generand o cerinta dinamica(folosind EXEC sau
sp_executesql).
Pentru a ilustra aceasta idée, imaginati-va ca doriti sa creati o procedura stocata cu un singur parametru, si acest parametru este tabelul pe care doriti sa-l interogati. Paragraful II.11 arata codul necesar creearii acestei proceduri stocate, folosind declaratia EXEC.
Paragraful II.11 Folosirea Obiectelor ca Parametrii si Constructia Interogarilor in Momentul Rularii
USE Northwind
GO
CREATE PROC dbo.issuequery
@tablename NVARCHAR(256)
AS
DECLARE @query NVARCHAR(1000)
SET @query = 'SELECT * FROM '+ @tablename
EXEC (@query)
GO
Alterarea definitiilor Procedurilor Stocate
Codul unei proceduri stocate poate fi modificat folosind declaratia ALTER PROCEDURE, sau echivalentul acesteia ALTER PROC.
Paragraful II.12 modifica definitia unei proceduri stocate creata in Paragraful II.11. Noua procedura stocata , in plus fata de numele tabelului, primeste un nume de coloana ca si parametru.
Paragraful II .12 Folosirea ALTER tabel Pentru Modificarea Codului unei Proceduri Stocate
USE Northwind
GO
ALTER PROC dbo.issuequery
@tablename NVARCHAR(256),
@columname NVARCHAR(256)
AS
DECLARE @query NVARCHAR(1000)
SET @query = 'SELECT '+ @columname + 'FROM '+ @tablename
EXEC (@query)
GO
Cand modificati o definitie a unei proceduri stocate(folosind declaratia ALTER PROC):
Serverul SQL retine permisiunea intacta privind procedura stocata . Drept urmare, orice set de permisiuni in procedura stocata sunt retinute dupa modificarea codului procedurii stocate folosind ALTER TABLE.
Acest lucru nu afecteaza obiectele dependente (tabele, triggeri, sau proceduri stocate). De exemplu, daca modificati definitia unei proceduri stocate si acesta face referire la un tabel, tabelul nu este afectat.
Aceasta nu afecteaza proprietatea de a rula automat cand Serverul SQL porneste, daca acesta a fost anterior setat folosind procedura stocata sistem sp_procoption. De exemplu, daca modificati codul procedurii stocate create in Paragraful II.6(insertsqlstatus, care a fost setata sa ruleze automat de fiecare data cand Serverul SQL este pornit), Serverul SQL mentine aceasta proprietate intacta.
Cu alte cuvinte, fie daca doriti sa schimbati codul procedurii fara sa afectati permisiunile si proprietatile, sau doriti sa modificati optiunile procedurii stocate(WITH ENCRYPTION sau WITH RECOMPILE), puteti folosi declaratia ALTER PROCEDURE. Cu toate acestea, observati ca, daca aveti nevoie sa modificati o optiune, trebuie sa specifica ti intregul cod al procedurii stocate. in mod similar, daca trebuie doar sa modificati sau sa schimbati codul si sa pastrati optiunile, de asemenea trebuie sa specifica ti optiunile.
De exemplu, daca doriti sa criptati codul aratat in Paragraful II.12, ar trebui sa adaugati optiunea WITH ENCRYPTION la definitia procedurii stocate. Paragraful II.13 arata cum sa realizati acest lucru, si de asemenea demonstreaza ca, codul este de fapt criptat dupa executia scriptului.
Paragraful II.13 Folosirea ALTER tabel Pentru a Modifica Codul Unei Proceduri Stocate
USE Northwind
GO
ALTER PROC dbo.issuequery
@tablename NVARCHAR(256),
@columname NVARCHAR(256)
WITH ENCRYPTION
AS
DECLARE @query NVARCHAR(1000)
SET @query = 'SELECT '+ @columname + 'FROM '+ @tablename
EXEC (@query)
GO
sp_helptext issuequery
GO
The object comments have been encrypted.
Observati ca daca doriti numai sa adaugati o optiune la codul procedurii stocate (WITH ENCRYPTION, din exemplul anterior), tot trebuie sa specifica ti intregul cod.
Declaratia RETURN
Declaratia RETURN este folosita sa iesiti in mod neconditionat dintr-o procedura stocata . Cu alte cuvinte daca Serverul SQL primeste o declaratie RETURN in timpul executarii unei proceduri stocate acesta se opreste din procesat si retuneaza controlul aplicati ei apelatoare .
Declaratia RETURN are un singur parametru, valoarea de intoarcere, care este un intreg ce poate fi folosit pentru a comunica cu aplicatia apelatoare. in timp ce creeati o procedura stocata , daca folositi un tip de date altul decat intreg pentru valoarea de intoarcere, Serverul SQL va permite sa creeati procedura stocata , dar veti primi o eroare in momentul executarii.
Valoarea de intoarcere este 0 implicit; de aceea daca o procedura stocata contine o declaratie RETURN fara acest parametru, valoarea de intoarcere va fi 0. De aceea, este acelasi lucru sa spunem RETURN 0 sau RETURN. in mod similar, daca o procedura stocata nu are nici o declaratie de intoarcere, valoarea de intoarcere este 0. in general, o valoare de intoarcere 0 indica o executare completa a procedurii stocate. Orice alta valoare diferita de 0 indica de obicei ca a fost o eroare in executia procedurii stocata .
Conventia generala folosita in procedurile stocate sistem este 0 inseamna success, si oricare alta valoare indica faptul ca s-a ivit o eroare.
De obicei, declaratia RETURN este foarte folositoare in faza de verificare a erorilor a procedurilor stoacate; cu toate ca, daca exista o eroare pe care doriti sa o captati in aplicatia apelatoare, declaratia RETURN poate fi folosita sa intoarca un cod de eroare.
Deoarece puteti folosii alte numere decat 0 pentru a returna codul de eroare ale aplicati ei apelatoare, daca doriti sa aveti prelucrate coduri de eroare din aplicatia dumneavoastra, puteti sa alegeti un numar pentru fiecare tip de eroare, si apoi cand aplicatia primeste unul din aceste coduri de reroare, stie cum sa le interpretez.
Paragraful II.14 arata un exemplu al unei proceduri stocate (getemployee) ce foloseste valori de intoarcere pentru a indica daca un anumit employeeid exista in tabelul Employees. Getemployee returneaza –1 daca employeeid nu exista in tabelul Employees, si returneaza 0 daca exista. Observati cea de-a doua declaratie RETURN nu are valoare de intoarcere, deci este 0(implicit).
Paragraful II.14 Folosirea Declaratiei RETURN in Procedurile Stocate
USE Northwind
GO
CREATE PROC dbo.getemployee
@employeeid INT
AS
IF NOT EXISTS (SELECT * FROM Employees WHERE employeeid = @employeeid)
RETURN -1
ELSE
SELECT * FROM Employees WHERE employeeid = @employeeid
RETURN
GO
Atentie
Valoarea de intoarcere nu este un parametru de iesire al unei proceduri stoacate; aceste asunt lucruri diferite. Creatorii bazelor de date fac cateodata confuzie intre valoare de intoarcere si parametrul de iesire. Retineti ca o procedura stocata poate avea mai mult decat un parametru de iesire dar o singura valoare de intoarcere.
Executarea Procedurilor Stocate
Exista o varietate de metode prin care se pot executa procedurile executate. Toate depind de aplicatia apelatoare., limbajul folosit, si interfata de programare.(OLE-DB, ODBC, ADO, si asa mai departe). in Transact-SQL, sintaxa de baza pentru a executa o procedura stocata este urmatoarea:
EXECUTE @return_value = procedure_name parameter_1,..,parameter_n
Declaratia EXECUTE trebuie sa fie folosita daca exista mai mult de o instructiune. in caz contrar,daca doriti sa executati doar procedura stocata si nu mai exista alte instructiuni puteti omite declaratia EXECUTE.
Observatie
Daca exista mai mult de o instructiune procedura stocata este apelata din prima linie de comanda, puteti omite declaratia EXECUTE.
Exista doua cai de a specifica parametrii de intrare cand executati o procedura stocata :
Folositi numele variabilelor utilizate in declaratia parametrului procedurii stocate si valoarea acestora –cu aceasta abordare, puteti omite variabile daca doriti sa folositi valoarea lor implicita. De aseamenea, ordinea parametrilor nu este importanta. De exemplu, Paragraful II.15 creeaza o procedura stocata care insereaza o linie in tabelul Customers, si apoi il executa. Observati cum toti parametrii care nu au valoare implicita trebuie sa fie specifica ti.
Paragraful II.15 Executarea Procedurilor Stocate cu Parametrii
USE Northwind
GO
CREATE PROC dbo.InsertCustomer
@customerid NCHAR(10),
@companyname NVARCHAR(80),
@contactname NVARCHAR(60),
@contacttitle NVARCHAR(60) = 'Owner',
@address NVARCHAR(120) = NULL,
@city NVARCHAR(30) = 'Miami',
@region NVARCHAR(30) = 'FL',
@postalcode NVARCHAR(20) = '33178',
@country NVARCHAR(30) = 'USA',
@phone NVARCHAR(48) = NULL,
@fax NVARCHAR(48) = NULL
AS
INSERT INTO Customers (customerid,companyname,contactname,contacttitle,address,
city,region,postalcode,country,phone,fax)
VALUES (@customerid,@companyname,@contactname,@contacttitle,@address,@city,
@region,@postalcode,@country,@phone,@fax)
GO
InsertCustomer @customerid='MACMI',@contactname='Carlos Eduardo Rojas',
@companyname = 'Macmillan'
GO
Folositi doar valorile actuale care doriti sa le transmiteti procedurii stocate—Cu aceasta metoda, ordinea valorilor este importanta. Din acest motiv, valorile trebuie specifica te in aceeasi ordine in care variabilele apar in sectiunea de declaratie a parametrilor procedurii stocate. De asemenea, valorile implicite pot fi folosite, dar ele trebuie sa fie ultimele in declaratia parametrilor; in caz contrar, veti intrerupe secventa . Paragraful II.16 arata executia unei proceduri stoccate din Paragraful II.15 folosind aceasta abordare.
Paragraful II.18 Folosirea Parametrilor de Iesire
USE Northwind
GO
CREATE PROC dbo.getCustomerInfo
@customerid NCHAR(10),
@contact NVARCHAR(60) OUTPUT,
@company NVARCHAR(80) OUTPUT
AS
SELECT @contact = contactname,
@company = companyname
FROM Customers
WHERE customerid = @customerid
GO
DECLARE @customer_id NCHAR(10),@customer_name NVARCHAR(60),
@customer_company NVARCHAR(80)
SET @customer_id = 'SAMSP'
EXEC getCustomerInfo @customer_id, @customer_name OUTPUT,
@customer_company OUTPUT
SELECT @customer_name + '- '+ @customer_company
GO
Maria Rojas - Sams Publishing
(1 row(s) affected)
Atentie
Daca, cuvantul cheie OUTPUT este omis cand procedura stocata este executata, parametrul se comporta ca un parametru de intrare.
Paragraful II.19 contine executia aceleiasi proceduri stocate executate in Paragraful II.18 dar Paragraful II.19 omite cuvantul cheie OUTPUT in ambele variabile. Observati ca aceste valori sunt pierdute dupa executia procedurii stocate.
Paragraful II.19 Folosirea Parametrilor de Iesire fara Cuvantul Cheie OUTPUT
USE Northwind
GO
DECLARE @customer_id NCHAR(10),@customer_name NVARCHAR(60),
@customer_company NVARCHAR(80)
SET @customer_id = 'SAMSP'
EXEC getCustomerInfo @customer_id, @customer_name, @customer_company
SELECT @customer_name + '- '+ @customer_company
GO
NULL
(1 row(s) affected)
Daca doriti sa procesati valorea de intoarcere a unei proceduri stocate, trebuie sa o stocati intr-o variabila in momentul executarii procedurii stocate. Paragraful II.20 executa procedura stocata din Paragraful II.14 (getemployee), si demonstreaza cum sa stocati valoarea de intoarcere intr-o variabila locala pentru o procesare viitoare.
Paragraful II.20 Stocarea Valorii de Intoarcere a Unei Proceduri Stocate intr-o Variabila
USE Northwind
GO
DECLARE @employeexists INT
EXEC @employeexists = getemployee 88
SELECT @employeexists
GO
Setul de rezultate returnat de procedura de stocare executata (daca aceasta contine declaratia SELECT) poate fi inserata intr-un tabel folosind declaratia INSERT urmata de executia procedurii stocate. Tipurile de date ale setului de rezultate trebuie sa fie compatibile cu cele ale tabelului. Compatibile inseamna ca tipurile de date trebuie sa fie ori la fel sau convertite in mod automat de Serverul SQL. De asemenea, numarul de coloane al setului de rezultate apartinand procedurii stocate trebuie sa se potriveasca cu definitia tabelului. De exemplu, daca procedura stocata produce un set de rezultate cu 3 coloane, nu o puteti insera intr-un tabel cu 2 coloane. in Paragraful II.21, o procedura stocata este create pentru a stoca setul de rezultate returnat de executia procedurii stocate.
Paragraful II.21 Inserarea intr-un Tabel a Unui Set de Rezultate al Unei Proceduri stocate
USE Northwind
GO
CREATE PROC dbo.GetEmployeesCountry
@country NVARCHAR(30)
AS
SELECT employeeid,lastname,firstname
FROM Employees
WHERE country = @country
GO
CREATE tabel #Employees_in_usa (
emp_id INT NOT NULL,
emp_lname NVARCHAR (20) NOT NULL,
emp_fname NVARCHAR (10) NOT NULL
GO
INSERT INTO #Employees_in_usa
EXEC GetEmployeesCountry 'USA'
SELECT * FROM #Employees_in_usa
GO
emp_id emp_lname emp_fname
Davolio Nancy
Fuller Andrew
Leverling Janet
Peacock Margaret
Callahan Laura
(5 row(s) affected)
O procedura stocata poate fi apelata din orice baza de data folosind nume calificate in totalitate. Observati ca atnci cand apelati o procedura stocata din oricare alta baza de date decat din cea in care a fost creeata, trebuie ca numele procedurii stocate sa fie in totalitate calificat. in acest fel, Serverul SQL stie unde sa caute procedura stocata . De exemplu, daca doriti sa executati o procedura stcata extinsa dintr-o oricare alta baza de date decat cea principala, trebuie sa indicati daca aceasta procedura apartine bazei de date principala. in special, Paragraful II.22 arata executia xp_fixeddrives, carea listeaza toate partitiile si spatial disponibil, din baza de date Northwind. Observati ca iesirea pe care o obtineti poate fi foarte variata in functie de numarul de partitii disponibile pe calculatorul dumneavoastra si de spatiul disponibil de fiecare dintre ele.
Paragraful II.22 Folosirea Numelor Autorizate in Totalitate Privind Procedurile Stocate
USE Northwind
GO
EXEC master..xp_fixeddrives
drive MB free
C 8315
D 8487
E 8316
(3 row(s) affected)
Folosirea Obiectului de Analiza a Cerintelor Pentru a Executa Procedurile Stocate
In SQL 2000, browser-ul newobject va permite sa executati proceduri stocate folosind o interfata grafica. Folosind aceasta metoda, trebuie doar sa introduceti valoare fiecarui parametru folosind GUI, si apoi Analizatorul de Cerinte denereaza in mod automat codul ncesar pentru executarea procedurii stocate. Pentru a executa o procedura stocata folosind browser-ul obiectului, urmariti acesti pasi:
1. Deschideti Analizatorul de Cerinte.
2. Conectati-va la server si alegeti baza de date.
3.Fiti siguri ca obiectul browser este deschis. daca nu este deschis, alegeti Tools, Object Browser, sau apasati F8.
4. in browserul obiect, extindeti o baza de date si apoi directorul procedurii stocate.
5. Click-dreapta pe procedura stocata , si apoi click pe optiunea Open.
6. Analizatorul de Cerinte se deschide fereastra Execute Procedure, in care introduceti valoarea fiecarui parametru.
7. Click pe butonul Execute.
Recompilarea Procedurilor Stocate
Dupa cum stiti deja, Serverul Sql creaza un plan de executie optimizat, care este stocat in memorie, de prima data de cand o procedura stocata este executata. in general, doriti ca Serverul SQL sa reutilizeze acest plan de executie pentru executiile subsecventiale ale procedurilor stocate. Cu toate acestea, din motive diverse, cateodata a-ti dori sa fortati Serverul SQL sa modifice un plan de executie. Motivul ar putea sa fie : Valoarea parametilor s-a schimbat semnificativ, obiectele la care face referire procedura stocata s-au modificat intr-un fel sau altul, datele s-au schimbat semnificativ sau indecsii au fost alterati.
Exista 3 cai de a forta in mod explicit Serverul SQL in a genera un alt plan de executie:
Folositi optiunea WITH RECOMPILE in momentul creari procedurii stocate(CREATE PROC .. WITH RECOMPILE)— Cu aceasta abordare, Serverul SQL nu trece in memoria cache planul de executie al procedurii stocate.
In schimb, Serverul SQL compileaza procedura stocata de fiecare data cand aceasta este executata, generand un nou plan de executie. Paragraful II.23 ilustreaza o procedura stocata folosind optiunea WITH RECOMPILE.
Paragraful II.23 Crearea Unei Proceduri Stocate Folosind Optiunea WITH RECOMPILE
USE Northwind
GO
CREATE PROC dbo.GetEmployeesCountry2
@country NVARCHAR(30)
WITH RECOMPILE
AS
SELECT employeeid,lastname,firstname
FROM Employees
WHERE country = @country
GO
La momentul executiei, folositi optiunea WITH RECOMPILE(EXECUTE .. WITH RECOMPILE)— daca este folosita aceasta metoda, Serverul SQL genereaza un nou plan de executie care este folosit in executiile subsecventiale ale procedurii stocate. Paragraful II.24 arata executia unei proceduri stocate, folosind optiunea WITH RECOMPILE, fortand Serverul SQL sa genereze un nou plan de executie.
Paragraful II.24 Folosirea WITH RECOMPILE
USE Northwind
GO
EXEC GetEmployeesCountry 'USA'WITH RECOMPILE
GO
Folositi procedura stocata sistem sp_recompile— Aceasta este o modalitate putin diferita de a recompile procedurile stocate. sp_recompile primeste numele unui obiect ca un parametru; obiectul poate fi o procedura stocata , table, imagine sau trigger. daca numele unei proceduri stocate sau numele unui trigger este folosit ca un parametru, obiectul(trigger sau procedura stocata ) este recompilat data viitoare cand va fi executat. Pe de alta parte, daca parametrul specifica t este numele tabelului sau o poza, orice procedura stocata care face referire la acest tabel sau poza va fi recompilata data viitoare cand va fi executata. Aceasta era cea mai plauzibila alternative de a cere recompilaretuturor procedurilor stocate (care fac referire la un tabel anume sau poza) cu doar o instructiune. Paragraful II.25 arata utilizarea procedurii stocate sp_recompile, fortand Serverul SQL sa recompileze orice procedura stocata care face referire la tabelul Employees din baza de date Northwind.
Paragraful II.25 Folosirea sp_recompile Pentru a forta Serverul SQL sa Genereze Un Nou Plan de Executie Pentru Fiecare Procedura stocata Care Face Referire La Tabelul Autorului
USE Northwind
GO
sp_recompile 'Employees'
GO
Object 'Employees'was successfully marked for recompilation.
Solutionarea Erorilor
Un element important din al oricarui program este in sectiunea error-checking. in timpul crearii codului sursa, este o buna tehnica de programare a verifica dupa erori in codul dumneavoastra, si anularea executiei programului dumneavoastra sau sa capturati eraorea cand o gasiti. daca programul esueaza, este o mare probabilitate ca el sa fi esuat datorita faptului ca ati neglijat sa verificati sau sa captati o eroare.
Transact-SQL pune la dispozitie doua elemente care ne permit sa verificam si sa detectam erorile in timpul programarii. Aceste elemente sunt functiile sistem fara parametrii @@ERROR si declaratia RAISERROR.
Functia sistem @@ERROR returneaza eroarea de cod(un numar intreg diferit de 0) al ultimei declaratii executate, daca a existat o eroare. Pe de alta parte, daca ultima declaratie a fost executata cu success, @@ERROR intoarce valoarea 0. aveti grija ca aceasta valoare se modifica de la o declaratie la alta; de aceea, trebuie sa verificati aceasta valoare chiar dupa ce declaratia a fost executata.
RAISERROR este folosit pentru a genera in mod automat o eroare. puteti folosi un mesaj ad hoc sau un mesaj stocat in tabelul sistem Sysmessages (toate mesajele de eroare Serverului SQL sunt stocate in Sysmessages). puteti adauga mesajele dumneavoastra la acest tabel sistem prin intermediul procedurii stocate sistem sp_addmessage, si pentru a sterge mesaje, prin intermediul sp_dropmessage. Observati ca atunci cand create un mesaj definit de utilizator prin intermediul sp_addmessage, trebuie sa specifica ti un indentificator al mesajului mai mare decat 50,001 (identificatorii de mesaje mai mici de 50,000 sunt rezervati de Serverul SQL).
RAISERROR (msg_id | msg_text, severity, state) WITH option
Primul parametru este identificatorul de mesaj sau mesajul text. daca specifica ti un identificator de mesaj, trebuie sa aveti creat un mesaj definit de utilizator cu sp_addmessage.Daca doriti sa folositi un mesaj ad hoc, textul de mesaj poate avea pana la 400 de caractere. Cel de-al doilea parametru este nivelul de severitate al erorii, care este un numar intre 0 si 25(nivelurile de severitate mai mari de 20 trebuie folosite de administratorii de sistem pentru erori critice). daca nivelul de severitate se incadreaza intre 0 si 10, este considerat un mesaj de informare. Atunci, nivelele de severitate de la 10 la 19 sunt folosite pentru erori captabile, si de la 20 la 25 pentru erori critice(care inchid conexiunea dupa ce clientul primeste mesajul de eroare)
Al treilea parametru, statutul erorii, este in numar intreg intre 0 si 127 care, prin documentatie, nu este semnificativ Serverului SQL. in final, sunt doua optiuni(oricare poate fi folosita) in ultimul parametru, care sunt optionale:
LOG—Stocheaza informatia de eroare in log-ul de erori din Serverul SQL si in log-ul NT Application. Aceasta optiune trebuie specifica ta cand folositi nivele de severitate mai mari decat 19.
NOWAIT—Aceasta optiune treimite un mesaj de eroare imediat la aplicatia client.
Dupa executie RAISERROR, @@ERROR returneaza valoare identificatorului de mesaj al erori sau, daca folositi un mesaj ad hoc, va returna 50,000.
Paragraful II.26 demonstreaza utilitatea si functionalitatea sp_addmessage, @@ERROR, si RAISERROR.
USE Northwind
GO
sp_addmessage 50001,11,'An error occurred'
GO
CREATE PROC generateerror
AS
RAISERROR (50001,11,1) WITH LOG
SELECT @@ERROR
GO
generateerror
(1 row(s) affected)
Server: Msg 50001, Level 11, State 1, Procedure generateerror, Line 4
An error occurred
(1 row(s) affected)
Proceduri Stocate Inlantuite
Procedurile stocate pot fi inlantuite pana la 32 de nivele. . Inlantuite inseamna ca o procedura stocata apeleaza pe alta, si asa mai departe. daca nivelul de inlantuire depaseste 32, executia intregului grup de proceduri stocate cedeaza. Serverul SQL pune la dispozitie o cale de a verifica nivelul de inlantuire folosind functia sistem @@nestlevel.
In momentul in care o procedura apeleaza alta procedura stocata , nivelul de inlantuire ester incrementat cu 1, si apoi cand o procedura stocata din interior termina de executat, nivelul de inlantuire este decrementat cu 1.
Paragraful II.27 arata crearea a 2 proceduri stocate. Prima dintre ele, CheckSupplier, returneaza –1 daca un nume de distribuitor exista deja in tabelul Suppliers, si 0 in caz contrar. A doua procedura, InsertSupplier, apeleaza procedura stocata CheckSupplier pentru a verifica daca este deja stocata in baza de date, si daca nu este, va fi inserata. Observatila afisarea Paragrafului II.27 ca nivelul de inlantuire este incrementat cu 1 de fiecare data cand o procedura stocata este apelata de catre ala procedura stocata .
Paragraful II.27 Folosirea Functiei Sistem @@nestlevel
USE Northwind
GO
CREATE PROC dbo.CheckSupplier
@supplier_name VARCHAR(40)
AS
PRINT '3) The nesting level is '+ CAST(@@nestlevel AS VARCHAR(5))
IF EXISTS (SELECT * FROM Suppliers WHERE companyname = @supplier_name)
RETURN -1
ELSE
RETURN 0
GO
CREATE PROC dbo.InsertSupplier
@suppliername NVARCHAR(40),
@contactname NVARCHAR(30),
@contacttitle NVARCHAR(30)
AS
DECLARE @supplier_exists INT
PRINT '2) The nesting level is '+ CAST(@@nestlevel AS VARCHAR(5))
EXEC @supplier_exists = CheckSupplier @suppliername
PRINT '4) The nesting level is '+ CAST(@@nestlevel AS VARCHAR(5))
IF @supplier_exists = 0
INSERT INTO dbo.Suppliers (companyname,contactname,contacttitle)
VALUES (@suppliername,@contactname,@contacttitle)
ELSE
PRINT 'This supplier already exists in the database'
GO
PRINT '1) The nesting level is '+ CAST(@@nestlevel AS VARCHAR(5))
EXEC InsertSupplier 'ACME','Fernando Guerrero','Owner'
PRINT '5) The nesting level is '+ CAST(@@nestlevel AS VARCHAR(5))
GO
The nesting level is 0
2) The nesting level is 1
3) The nesting level is 2
4) The nesting level is 1
(1 row(s) affected)
5) The nesting level is 0
Cand o procedura stocata apeleaza principala apeleaza alta procedura stocata , trebuie sa fie apelate una dupa alta, si, asa cum probabil stiti, aceasta nu este considerate o inlantuire. Cu toate acestea, deaorece aceasta nu este considerate inlantuire, puteti apela mai mult de 32 de proceduri stocate din cea prncipala. Paragraful II.28 ilustreaza aceasta situatie. Fiti foarte atenti la nivelul de inlantuire de la afisare; valoarea maxima este 1(deoarece nu este nici o inlantuire).
Paragraful II.28 Folosirea Functiei Sistem @@nestlevel
USE Northwind
GO
CREATE PROC dbo.ShowSecurityInfo
AS
EXEC sp_helpgroup
PRINT '2) The nesting level is '+ CAST(@@nestlevel AS VARCHAR(5))
EXEC sp_helpuser
PRINT '3) The nesting level is '+ CAST(@@nestlevel AS VARCHAR(5))
GO
PRINT '1) The nesting level is '+ CAST(@@nestlevel AS VARCHAR(5))
EXEC ShowSecurityInfo
PRINT '4) The nesting level is '+ CAST(@@nestlevel AS VARCHAR(5))
GO
1) The nesting level is 0
Group_name Group_id
db_accessadmin 16385
db_backupoperator 16389
db_datareader 16390
db_datawriter 16391
db_ddladmin 16387
db_denydatareader 16392
db_denydatawriter 16393
db_owner 16384
db_securityadmin 16386
public 0
2) The nesting level is 1
UserName GroupName LoginName DefDBName UserID SID
dbo db_owner sa master 1 0x01
guest public NULL NULL 2 0x00
3) The nesting level is 1
4) The nesting level is 0
Un avantaj al inlantuirii este acela ca in momentul apelarii unei proceduri stocate din alta procedura stocata , cea din interior poate accesa toatea obiectele create de procedura stocata din afara. De exemplu, Paragraful II.29 arata crearea a doua proceduri stocate, outerprocedure si innerprocedure, si dupa cum puteti vedea, innerprocedure poate accesa tabelul temporar creat de outerprocedure.
Paragraful II.29 Accesarea Obiectelor Create de Procedura stocata Exterioara, Dintr-o Procedura stocata Interioara
USE Northwind
GO
CREATE PROC outerprocedure
AS
SELECT orderid, orderdate
INTO #Spain_orders
FROM Orders
WHERE Shipcountry = 'Spain'
AND Shipcity = 'Barcelona'
EXEC innerprocedure
GO
CREATE PROC innerprocedure
AS
SELECT *
FROM #Spain_orders
GO
EXEC outerprocedure
orderid orderdate
(5 row(s) affected)
Securitatea aplicati ei Folosind Procedurile Stocate
Unul din avantajele procedurilor stocate este ca acestea pot fi folosite ca un mechanism de securitate pentru a prevenii ca utilizatorii sa lucreze direct cu tabelele. Acest process este foarte direct: Prima data, creati procedura stocata , si apoi alocati permisiuni de executie utilizatorilor din procedurile stocate. De aceea, utilizatorii nu au nevoie sa aiba permisiunea asupra fiecarui obiect la care face referire procedura stocata . De exemplu, daca creati o procedura stocata care returneaza date de la un anumit tabel (folosind cerinta SELECT), trebuie sa alocati permisiune de executie asupra procedurii stocate utilizatorilor, si ei vor putea sa ruleze procedura stocata (fara sa aiba permisiunea directa asupra tabelului care este referit de procedura stocata ).
Primul pas pe care Serverul SQL il executa atunci cand un utilizator executa o procedura stocata este de a verifica permisiunea de executie. in general, utilizatorul care executa procedura stocata trebuie sa aiba doar permisiunea EXECUTE. Cu toate acestea, sunt doua exceptii de la aceasta regula:
Daca este o cerinta dinamica in procedura stocata (contine fie delcaratia EXECUTE sau procedura stocata sistem sp_executesql), utilizatorul care o executa trebuie sa aiba permisiunea asupra obiectelor care sunt referite de cerinta dinamica. Cu alte cuvinte, Serverul SQL verifica permisiunea asupra fiecarui obiect care este referit de catre cerinta dinamica. Aceasta este datorita faptului ca daca procedura stocata contine o cerinta dinamica in momentul creearii, Serverul SQL nu stie care obiect este referit de cerinta dinamica pana in momentul executiei. Pentru a ilustra acest lucru, daca, create o procedura stocata care acceseaza tabelul Orders printr-o cerinta dinamica, orice utilizator care executa procedura stocata , in afara permisiunilor de executie, trebuie sa decida permisiunile din tabelul Orders.
Daca lantul este interrupt, Serverul SQL verifica permisiunile asupra fiecarui obiect cu posesor diferit, si doar declaratiile cu permisiuni diferite vor fi executate. Din aceasta cauza este recomandat ca posesorul unei proceduri stocate sa aiba toate obiectele, pentru a evita aceasta intrerupere a lantului.
De exemplu sa presupunem ca sunt
trei utilizatori in baza de date, Fernando, Carlos, si Michelle. Fernando
detine un tabel numit Countries, si
Carlos detine un tabel numit Cities. Acest scenario apare in Figura II.4.
Figure 8.4. Folosind Lantul Posesiunii
Carlos aloca permisiunea SELECT tabelului Cities lui Fernando. Atunci, Fernando creaza o procedura stocata numita citiesandcountries care acceseaza aceste doua tabele(Cities and Countries). dupa crearea procedurii stocate, Fernando acorda permisiunea EXECUTE lui Michelle asupra procedurii stocate, si in momentul in care Michelle o executa, ea obtine doar rezultatul celui de-al doilea set de cerinte din procedura stocata . Aceasta se datoreaza faptului ca Michelle acceseaza tabelul lui Carlos indirect, si Carlos nu i-a dat permisiunea lui Michelle pentru acest table.
In acest caz, lantul posesunii este intrerupt datorita faptului ca procedura stocata acceseaza un tabel care are alt posesor decat cel al procedurii. in particular, Serverul SQL trebuie sa verifice permisiunile la tabelul Cities datorita faptului ca posesorul acestui tabel nu este acelasi posesor al procedurii stocate citiesandcountries.
Pentru a rezolva aceasta problema, Carlos ar trebui sa-i acorde permisiunea SELECT pe acest tabel lui Michelle. Observati ca Fernando nu este nevoit sa acorde permisiunii SELECT permissions on his tabel to Michelle, asupra tabelului sau lui Michelle. Deoarece aceasta este deja posesoarea procedurii stocate. Pe scurt daca toate obiectele care sunt referite de o procedura stocata apartin posesorului procedurii stocate, si nu sunt cerinte dinamice in interiorul definitiei procedurii stocate, orice utilizator cu permisiuni de executie poate executa cu success procedura stocata .
|
