h1

Proceduri stocate notiuni generale

February 25, 2010

SQL Profiler  -> Trace Stored Procedures

Procesarea procedurilor stocate – sunt realizate de execution plan din SQL Server.

Procedurile stocate – cresc rapiditatea interogarilor SQL

Sintaxa generala: CREATE PROC nume_proc_stocata

EXEC nume_procedura_stocata


In tempdb din System Databases – sunt create procedurile stocate temporare.

Procedurile stocate – pot referi tabele, view-uri, functii definite de utilizator (user defined functions) sau alte proceduri, precum si tabele temporare.

Pt. a executa o procedura stocata trebuie sa fii membru logat al sysadmin, database owner(db_owner) sau DataDefinition Language administrator (db_ddladmin) role, sau sa ai permisiunea de a scrie clauza CREATE PROCEDURE pt.crearea procedurii stocate.

CREATE PROCEDURE nu poate include urmatoarele tranzactii SQL:

CREATE DEFAULT, CREATE PROCEDURE,CREATE RULE,CREATE TRIGGER, CREATE VIEW!!!!!!

Dimensiunea maxima a unei procedure stocate este de maxim 128MB.

Pentru detalii despre procedurile stocate: (consultati procedurile stocate sistem):

sp_help,sp_helptext,sp_depends,sp_stored_procedures,sysobjects,syscomments,sysdepends.

Procedura stocata sp_help obtine help si informatii suplimentare despre procedura stocata la fel ca si sp_help text.

Procedura stocata sp_depends se executa atunci cand vrem sa determinam ce obiecte depind de procedura stocata creata cu CREATE PROCEDURE. 

Sp_executesql alternativa la EXECUTE statement pt.a executa dynamic un string dintr-o procedura stocata.

Executia unei proceduri stocate: (se foloseste EXEC)

EXEC nume_procedura stocata lista_de_valori_pt_parametri

Stergerea unei proceduri stocate (DROP PROCEDURE sau DROP PROC):

FOLOSIREA PARAMETRILOR IN PROCEDURI STOCATE:

Putem adauga un parametru intr-o procedura stocata care cauta in tabela Employee o data de angajare specificata. Apoi procedura stocata poate fi executata de fiecare data ,daca schimbam doar conditia procedura stocata se va executa cu o data diferita.

SQL Server suporta 2 tipuri de parametri : parametri de intrare si parametri de iesire.

Executia  procedurilor stocate

1) Folosind parametri de intrare

a) Trecerea valorilor prin numele parametrilor

b) Trecerea valorilor prin pozitie

EXEC AddCustomer ‘ALFKI’,’Alfreds’,’MariaAnders’, ‘Sales Representative’, ‘Obere Str.57’, ‘Berlin’,NULL,’12209’,’Germany’,’030-0074321’

Exemplul urmator creeaza procedura stocata AddCustomer care adauga un nou customer(client) in baza de date NorthWind. Se observa ca toate variabilele cu exceptia CustomerID si CompanyName au specificat sa primeasca valoare NULL.

2) Folosind parametri de iesire

4) Recompilarea explicita a procedurilor stocate (RECOMPILE)

Se recompileaza atunci cand:

–          Procedura stocata intoarce seturi de rezultate variabile

–         Un nou index este adaugat unei tabele

–          O valoare a unui parametru este atipica

Se recompileaza folosind:

CREATE PROCEDURE [WITH RECOMPILE]

EXECUTE [WITH RECOMPILE]

Sp_recompile

Valorile parametrilor sunt trecute la o procedura stocata care intorc widely result sets.

SQL Server furnizeaza 3 metode de recompilare a unei procedure de stocare explicit:

Exemplul1: Se creeaza o procedura stocata numita OrderCount care este recompilata de fiecare data cand este executata.

USE NorthWind

GO

CREATE PROC dbo.OrderCount

@CustomerID nchar(10)

WITH RECOMPILE

AS

SELECT count(*) FROM [Orders Qry]

WHERE CustomerID = @CustomerID

GO

Exemplul 2: Acest exemplu compileaza CustomerInfo stored procedure(procedura stocata) in timpul in care ea este executata:

EXEC CustomerInfo WITH RECOMPILE

Sp_recompile este o procedura stocata in sistem care recompileaza procedura stocata specificata sau arata viitoarea data cand aceasta va fi executata.

Exxemplul 3: Acest exemplu recompileaza toate procedurile stocate dintr-o tabela (din tabela Customers din BD NorthWind ) folosind procedura stocata sistem sp_recompile:

EXEC sp_recompile Customers

UN INDEX e creat in memoria RAM!!!

O procedura stocata e creata in memoria CACHE!

Pentru a sterge toate procedurile stocate in memoria cache vom folosi:

DBCC FREEPROCCACHE

CUM SE GENEREAZA O PROCEDURA STOCATA (GENERATE SCRIPT) PT.O TABELA dam click dreapta si alegem all task generate sql script(sau pe tabul stored procedure). Sau Click pe STK click pe tabul Programmability si dam click pe tabul Stored Procedures.

Ultimul printscreen arata cum se genereaza un script pt. o tabela.

Procedura stocata cu functia sistem @@ROWCOUNT:

Exemplul de mai jos creaza o procedura stocata GetOrders care regaseste informatiile din tabelele Orders si Customers interogand view-ul cu numele Orders Qry.

Statement-ul RETURN in procedura stocata GetOrders intoarce nr.total de randuri din clauza SELECT catre o alta procedura stocata. Deasemenea putem avea nested stored procedures  la procedura stocata GetOrders(cu o alta procedura stocata).


Sp_addmessage :

Procedura stocata ce permite dezvoltatorilor sunt create mesaje custom de eroare.

SQL Server trateaza atat sistemul cat si custom errormessages in acelasi mod.

Despre functia sistem @@ROWCOUNT

http://msdn.microsoft.com/en-us/library/ms187316.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: