Proceduri stocate ( STORED PROCEDURES)

Avantaje:

ruleaza pe server(timpul de executie este mult mai mic decat pe o statie)

prin impartirea taskurilor in client si server descreste timpul necesar pentru compilarea proiectului. Se poate dezvolta partea de server separat de partea de client si se pot folosi componentele server in mai multe aplicatii client.

securitate se pot crea SP pentru toate operatiile de adaugare, modificare, stergere si listare

CREATE PROCEDURE [owner,] procedure_name[;number]

[@parameter_name datatype [=default] [OUTPUT] ]

[FOR REPLICATION]|[WITH RECOMPLILE] , ENCRYPTION

AS sql_statements

Ex:

Create procedure all_employees

As select * from employees (name depatment badge)

Exec all_employees =>run (Bob Sales 1234)

Folosirea parametrilor SP:

se poate defini unul sau mai multi parametrii intr-o procedura

se folosesc parametrii ca locatii de stocare (ca variabile in limbaj)

se foloseste simbolul @ inaintea unui nume de parametru pentru a-l indica ca parametru

numele parametrilor sunt locali procedurii in care sunt definite

Ex: Crearea unei SP cu parametrii de intrare

Create procedure proc4(@p1 char(15), @p2 char(20), @p3 int

As insert into Workers values (@p1,@p2,@p3)

Ex: Folosirea versiunilor de SP

Create procedure proc3;1 as

Print 'version1'

Create procedure proc3;2 as

Print 'versino2'

Proc3;1

Version1

Proc3;2

Version2

Proc3

Version1

; si numarul intreg de dupa numarul procedurii iti permite sa creezi versiuni multiple de proceduri cu acelasi nume.Cand procedura este executata numarul versiunii poate fi specificat pentru controlul versiunii . Daca nu este specificata versiunea se executa prima.

Listarea si editarea PROCEDURILOR

Procedurile sistem:

sp_helptext se foloseste pentru a lista definirea procedurilor

sp_help pentru a arata informatiile de control despre proc

sp_stored_procedures este folosita pentru a lista informatii despre SP

Stergerea SP existente

DROP PROCEDURE procedure_name_1,.., procedure_name_n

Ex: drop procedure proc3- sterge cele doua versiunii ale procedurii

-sp_makestartup procedure_name se defineste o procedura care sa se execute automat la startarea SQL Server(pot fi oricite)

-sp_helpstartup procedura sistem care listeaza procedurile care sunt definite sa se execute la startare

-sp_unmakestartup sa previna o procedura de la executia automata

Ex: o noua procedura care este marcata pentru o executie autometa cand SQL Server starteaza

Create procedure test_startup as

Print' test procedure executed at startup'

Go

Sp_makestartup test_startup

Go

Procedure has been marked as'startup'

Sp_helpstartup

Go

Startup stored procedures:

Test_startup

(1row affected)

sp_unmakestartup test_startup

go

Procedure id no longer marked as'startup'

Sp_helpstartup

Startup stored procedures

ISQL commands

Ex: use employees

Go query1.sql

Select * from workers

Select max(rownume) from Rownumber

Rezultatele: isql/u/i query1.sql/n/p''

IF.ELSE

Ex: if exists(select * from workers where badge=1234)

Print'entry avaible'

Print'no entry'

If exists (select * from emploees

Where name='Bob Smith')

Print'emploee present'

Else print'emplee not found'

Ex:if exists(select *from employees

Where badge=1234)

Begin

Print'entry available'

Select name,department from employees

Where badge=1234

End

Entry available

Name    Department

Bob Smith    Sales

Ex: if exists(select * from employees

Where department='sales')

Begin

Print'row(s) found'

Select name,department from employees

Where department='Sales'

End

Else print'no entry'

Row(s) found

Name    Department

Bob Smith    Sales

Mary Jones    Sales

WHILE

While

<boolean_expression>

<sql_statement>

Ex: declare @x int    (1 row(s)affected)

Select @x=1 x still less than 5- de 5 ori

While @x<5

Begin

Print'x still less than 5'

Select @x=@x+1

End

Go

BREAK

While

<boolean_expression>

<sql_statement>

break

<sql_statement>

CONTINUE

While

<boolean_expression>

< statement>

break

< statement>

continue

Ex: declare @x int

Declare @y tinyint

Select @x=1, @y=1

While @x<5

Begin

Print'x still less than 5'

Select @x=@x+1

Select @y=@y+1

If @y=2

Begin

Print'y is 2 so break out of loop'

Break

End

End

Print'out of while loop'

Ex:begin tran

While(select avg(price) from titles)<$30

Begin

Select title_id, price

From titles

Where price>$20

Update titles set price= price*2

End

Obs:- while exists(select hours_worked from pays)

Print'hours_worked is less than 55'

-while(select hours_worked from pays)>55

print'.'

EROARE!!!nu trebuie folosit =,!=,<,<=,>,>=

Sintaxa unei variabile locale

DECLARE @ variable_name data type [,varible_name datatype.]

Sintaxa select este folosita pentru asignarea valorilor variabilelor locale

Select @variable_name=expression | select statement

[,@ variable_name=expression select statement]

[FROM list of tables] [WHERE expression]

[GROUP BY ]

[HAVING .]

[ORDER BY..]

Ex:

Declare @mynum int

Select @mynum=count(*) from workers

Declare @mychar char(2)

Select @mychar=convert (char(2), @mynum)

Declare @mess char(40)

Select @mess='there are'+@mychar+'rows in the tables workers'

Print @mess

PRINT print 'text'| @local_variable| @@global_variabile

Varibile globale

nu sunt definite prin rutine, ci sunt definite la nivel server

-se pot folosi doar variabile globale predeclarate si definite

-se face referire la o variabila globala precedind numele cu @@

-nu trebuie definite variabile locale cu acelasi nume cu variabilele sistem pentru ca se pot obtine rezultate neasteptate in aplicatie

Ex:

Print @@ version

Declare @ mess1 char(21)

Select @ mess1='server name is'+@@servername

Print @mess1

GOTO - transfera de la o sintaxa la alta sintaxa care contine o eticheta definita de utilizator

Label:

GOTO label

Ex:

Declare @count smallint

Select @count=1 yes de 4 ori

Restart:

Print'yes'

Select @count=@count+1

While @count<=4

Goto start

RETURN return [integer]

Ex: 0:executie cu succes

-1 missing object

EXECUTE @return_status=procedure_name

Specifica o variabila locala pentru starea returnata

Ex: create procedure proc1 as

Select * from emplyees

Declare @status int

Execute @ status=proc1

Select status = @status

Name    Department Badge

Bob Smith Sales 1234

Mary Jones    Sales 5514

Status

succes

Daca dupa a doua linie as fi introdus return 5 atunci la status ar fi aparut 5

Ex: declare @status int

Execute @status = proc1

If (@status=0)

Begin

Print''

Print'proc 1 executed succesfully'

End

RAISERROR

Raiserror(<integer_expression>|<'text of message'>,[severity][, state a [,argument1] [,argument2])

[WITH LOG]

-integer_expression este o eroare specificata se utilizator sau un numar cuprins intre 50.000 si 2.147.483.647. Ea se afla in variabila globala @@ERROR

Ex:

Declare @err char(5)

Raiserror 99999 'Guru meditation error'

Select @err = convert(char(5),@@ERROR)

Print @err

go

sp_addmessage message_id, severity, massage text

Ex: folosirea SP sistem care adauga un mesaj cu numarul si identificatorul severity asociat

Sp_addmassage 99999,13,'Guru meditation error'

Go

Select * from sysmessages where error =99999

Go

Raiserror(99999,13,-1)

Go

New message added

Error Severity level Description languid

13 0 Guru meditation error 0

sp_dropmessage [message_id [language|'all']]

Pentru stergerea unui mesj definit de utilizator

WAITFOR [DELAY<'time'>|TIME<'time'>||ERROREXIT|PROCESSEXIT]

Delay-specifica un interval de timp pentru a se declansa

Time -un timp specificat

Ex: waitfor delay'00:00:40' waitfor time '15:10:51'=se executa la acea ora

Select * from employees

CASE[expression]

WHEN simple expression1| boolea expression1 THEN expression1

[WHEN simple expression2| boolea expression1 THEN expression2]

[ELSE expression N]

END

Ex:

Select name, division=

Case department

When "Sales" then "Sales&Marketing"

When "Field Service" then "Support Group"

When "Logistics" then "Parts"

ELSE "other department"

End

Badge

From company

GO

Ex:

Select "Hours Worked" =

Case

When Hours_Worked<40 then "Work Insufficient Hours"

When Hours_Worked=40 then "Work Sufficient Hours"

When Hours_Worked>40 then "Overworker"

ELSE "Outside the range of permisible work"

End

From Pais

GO

Ex:

Select name, badge=coalesce(nullif(old_badge, badge), badge)

From Company2

GO