CUPRINS

I.         DEFINIREA SI UTILIZAREA REGULILOR CE DEFINESC CONSTRANGERILE SI VALORILE IMPLICITE

DEFINIREA REGULILOR

Crearea regulilor

Legarea regulilor

Prioritatea regulilor

Afisarea legarilor de reguli

Dezlegarea regulilor

Redenumirea regulilor

Stergerea regulilor

DEFINIREA VALORILOR IMPLICITE

2.1 Crearea valorilor implicite

2.2 Legarea valorilor implicite

2.3 Aplicarea valorilor implicite

2.4 Afisarea legarilor de valori implicite

2.5 Afisatea valorilor implicite

2.6 Dezlegarea valorilor implicite

2.7 Redenumirea valorilor implicite

2.8 Stergerea valorilor implicite

II PROCEDURI STOCATE. INSTRUCTIUNI DE CONTROL AL FLUXULUI DE EXECUTIE

1. DEFINIREA PROCEDURILOR STOCATE

1.1 Transmiterea de parametric catre proceduri

1.2 Apelarea din cadrul aplicatiilor a proceduriilor stocate

1.3 Afisarea si editarea procedurilor

1.4 Modificarea si stergerea procedurilor stocate

1.5 Despre interpretarea si compilarea procedurilor

1.6 Recompilarea automata

1.7 Stabilirea procedurilor cu executie automata

1.8 Despre restrictiile asupra procedurilor si scripturilor

1.9 Despre semnalul GO de sfarsit de script

2. UTILIZAREA INSTRUCTIUNILOR PENTRU CONTROLUL FLUXULUI DE EXECUTIE

2.1 Utilizarea instructiunii IF . ELSE

2.2 Utilizarea blocurilor BEGIN . END

2.3 Utilizarea instructiunii WHILE

2.4 Utilizarea instructiunii BREAK

2.5 Utilizarea instructiunii CONTINUE

2.6 Exemple de utilizare a instructiunilor

DEFINIREA SI UTILIZAREA VARIABILELOR

Utilizarea variabilelor inpreuna cu instructiunea PRINT

Utilizarea variabelor global

ALTE INSTRUCTIUNI PENTRU PROCEDURI

Utilizarea instructiunii GOTO

Utilizarea instructiunii RETURn

Utilizarea instructiunii RAISSEROR

Utilizarea instructiunii WAITFOR

GESTIONAREA SI UTILIZAREA REGULILOR, CONSTRANGERILOR SI VALORILOR INPLICITE

1. DEFINIREA REGULILOR

O regula defineste o restrictie asupra valorilor dintr-o coloana a unei tabele sau din cadrul unui tip de date definit de utilizator. Orice date pe care incercati sa le introduceti intr-o astfel de coloana sau de tip de date definit de utilizator trebuie sa satisfaca criteriul definit. Regulile se folosesc pentru implementarea unor restrictii sau limitari.

Regurile permit specificarea testului de efectuat in mai multe feluri diferite. Pe de o parte ,puteti sa folositi o functie care sa testeze informatiile in cauza. Functia va intoarce un indicator de comparatie pe care-l veti utiliza pentru a valida inscrierea in coloana a valorii.

Alternativ, puteti sa folositi operatori de comparare precum BETWEEN, LIKE sau IN pentru a realiza testul asupra noilor date. Si de aceasta data, scopul este sa testati noua valoare si sa va asigurati ca satisface criterile dorite.

De exemplu, puteti sa definiti o regula care sa limiteze valorile inscrise intr-o coloana ce contine compartimente dintr-o firma la numele compartimentelor existente. Daca nu exista decat pentru compartimente din care sa poata face parte un angajat, puteti sa definiti o regula care sa limiteze valorile inscrise in coloana Departament la numele celor patru compartimente. Tot prin intermediul unei reguli puteti sa specificati domeniul de valori admise pentru o coloana care are un tip definit de utilizator.

Va amintesc ca este posibil sa definiti un tip de date propriu pe baza unui tip de date al sistemului, precum "char" sau "int", sau a unui tip de date specializat. Tipurile de date definite de utilizator pentru coloane din tabele trebuie sa aiba aceleasi definitii pentru toate tabelel. In plus, nu puteti sa definiti o regula pentru un tip de date al sistemului, ci doar pentru un tip de date definit de utilizator. Spre exemplu, in loc sa definiti de fiecare data o coloana precum Badge Number, care este folosita in mai multe tabele pentru a mijloci asocierile relationale, puteti sa definiti un tip de date propriu pe baza caruia sa definiti aceasta coloana oriunde apare ea. Daca domeniile de valori ale coloanelor Badge Number sunt identice, puteti sa definiti o regula pentru un tip de date propriu, numit "badge", pe care sa-l asociati tuturor coloanelor Badge din tabele.

1.1 CREAREA REGURILOR

Ati retinut ca regulile sunt obiecte in cadrul bazei de date. Din aceasta cauza va trebui mai intai sa creati o regula si apoi sa o legati la o coloana. Crearea unei reguli se face prin intermediul instructiunii CREATE RULE. Sintaxa pentru aceasta instructiune este urmatoarea:

CREATE RULE nume_regula

AS expresie_conditionala

Daca creati o regula in cadrul bazei de date curente, aceasta poate fi aplicata doar in coloanelor sau tipurilor de date definite de utilizator in respectiva baza de date. Intr-o regula puteti utiliza orice expresie care poate fi specificata intr-o clauza WHERE, fiind posibila folosirea operatiilor de comparare si aritmetici.

In expresia conditionala din cadrul unei reguli se foloseste simbolul @. Acest simbol permite specificarea unui parametru ce reprezinta valoarea inscrisa ulterior intr-o coloana, printr-o instructiune UPDATE sau INSERT.

In exemplul de mai jos, CREATE RULE defineste o lista de valori posibile, folosind cuvantul cheie IN pentru formarea unei expresii conditionale. Desi parametrul specificat in expresia conditionala are acelasi nume cu coloana de tabela la care urmeaza sa fie regula, acest parametru poate avea orice alt nume.

create rule departament_values

as

@departament in ('Sales','Filed', 'Service', 'Logistics', 'Software')

O regula inpune restrictii asupra unor valori compatibile cu tipul de date asociat unei coloane. Nu puteti sa folositi intr-o expresie conditionala constante care nu sunt compatibile cu coloana sau cu tipul de date definit de utilizator la care va fi legata ulterior regula in cauza. Alegeti un nume sugestiv pentru o regula, care sa includa numele coloanei sau al tipului definit de utilizator la care va fii legata regula respectiva.

Regulile pot fi create si prin intermediul administratorului de sistem SQL, urmand pasii de mai jos:

Dupa ce lansati administratorul de sistem SQL, selectati serverul si baza de date in cadrul careia veti defini regula.

Expandati catalogul Objects si selectati Rules.

Efectuati un clic cu butonul drept pe Rules si selectati New Rule pentru a deschide caseta de dialog Rule Propertis.

Introduceti numele regulii in campul Rule si formula SQL a regulii in caseta Text.

Efectuati un clic pe OK pentru a crea o noua regula.

1.2 LEGAREA REGULILOR DE DATE

Definirea unei reguli nu specifica nimic despre aplicarea regulii asupra unei coloane sau a unui tip de date definit de utilizator. Daca doar definiti o regula, aceasta nu are nici un efect, este pur si simplu creata ca obiect in cadrul bazei de date. Odata definita o regula, trebuie sa o legati la o coloana sau la un tip de baze de date definit de utilizator. Acesta este momentul in care regula in cauza intra in vigoare. Toate valorile introduse in coloana sau in tipul de date respectiv vor trebui sa satisfaca criteriul stabilit de catre regula.

Legarea unei reguli la o coloana sau la un tip de date definit de utilizator se face prin comanda sp_bindrule , care are urmatoare sintaxa:

sp_bindrule 'nume_regula' , 'nume_tabela.nume_coloana' , ['futureonly']

Dupa legarea unei reguli la o coloana sau la un tip de date definit de utilizator, in tabelele sistemului sunt inscrise informatiile corespunzatoare. Un numar de identificare unic al regulii este inscris intr-una din tabelele de sistem syscolumns sau systypes. Identificatorul este inscris in syscolumns atunci cand regula este legata la o coloana si, respectiv, sin systypes atunci cand regula este legata la un tip de date definit de utilizator.

Primul parametru al instructiunii sp_bindrule specifica numele regulii. Acesta poate sa contina pana la 30 de caractere, astfel ca veti putea include, in mod normal, numele coloanei sau al tipului definit de utilizator la care intetionati sa legati regula.

In continuare introduceti numele coloanei de tabela sau al tipului de date definit de utilizator asupra caruia va fi aplicata regula. Numele unei coloane trebuie precedat de numele tabelei in care acesta este definita. Totul trebuie incadrat de simboluri apostrof. Daca specificati doar un nume de obiect, SQL Server il va considera ca nume al unui tip de date definit de utilizator. Atunci cand introduceti un nume de coloana, folositi un punct (.) pentru a separa numele tabelei de numele coloanei la care este legata regula. Prin legarea unei reguli la un tip de date se limiteaza valorile ce pot fi inscrise intr-o coloana care este definita pe baza acelui tip.

Cel de-al treilea parametru, futureonly, se foloseste doar in cazul legarii la un tip de date definit de utilizator. Specificarea sa previne aplicarea regulii asupra coloanelor care au fost definite deja pe baza tipului de date respectiv. Acest parametru indica faptul ca regula va fi aplicata doar acelor coloane create ulterior pe baza tipului de date la care este legata respectiva regula.

Legarea unei reguli la o coloana de tabela sau la un tip de date definit de utilizator se poate si prin intermediul administratorului de sistem SQL, urmand pasii de mai jos:

Dupa ce ati lansat administratorul de sistem SQL, selectati serverul si baza de date in cadrul careia a fost definita regula.

Expandati catalogul Objects sai selectati Rules.

Efectuati un dublu clic pe regula pe care doriti sa o legati.

Efectuati un clic pe unul din butoanele Bind Columns sau Bind UDTs. In cazul legarii la o coloana, selectati numele tabelei in cadrul campului Table si specificati cu ajutorul butonului Add>>coloana(-ele) la care doriti sa fie legata regula.

Efectuati un clic pe OK.

Efectuati un clic pe Close pentru a inchide caseta de dialog Rules Properties.

1.3 PRIORITATEA REGULILOR

Probabil ca v-ati dat seama deja ca regulile pot sa provoace conflicte si ca exista anumite conventii de prioritate care se folosesc pentru solutionarea acestor conflicte. Ati putea intalni o situatie in care o coloana a unei tabele este definita pe baza unui tip de date creat de utilizator si exista cate o regula distincta legata la coloana si la tipul de date. Lista de mai jos cuprinde trei reguli de prioritate care se aplica la legarea regurilor:

■ Regulile legate la coloane sunt prioritare fata de regulile legate la tipuri de date. Daca exista o regula legata la o coloana si o alta regula legata la tipul de date al aceleiasi coloane, se foloseste regula legata la coloana. Daca legati o noua regula la coloana, aceasta se suprapune peste o regula legata la tipul de date al coloanei.

■ Daca legati o noua regula la o coloana sau la un tip de date, acesta inlocuieste o eventuala regula anterioara. La o coloana sau la un tip de date nu poate fi legata decat o singura regula.

■ Daca legati o regula la un tip de date definit de utilizator, aceasta nu inlocuieste regula legata la o coloana care are respectivul tip. Tabelul 1 rezuma prioritatea regurilor.

Tabelul 1 Prioritatea regurilor

Regulile nu se aplica datelor care au fost deja introduse in tabele. Valorile aflate curent in cadrul tabelelor nu trebuie sa satisfaca criterile specificate de catre reguli. Daca doriti ca o regula sa restranga valorile introduse intr-o tabela, definiti regula direct sau indirect, prin intermediul unui tip de date definit de utilizator, inainte ca datele sa fie introduse in tabela.

In listingul 1, procedura sp_bindrule este folosita pentru a lega regula Jobs_list la o coloana departament a tabelei Employees. O instructiune INSERT ulterior esueaza la incercarea de a inscrie in aceasta coloana o valoare care nu satisface criteriile stabilite de catre regula. SQL Server intoarce un mesaj de eroare explicativ, care arata ca incercarea de inserare incalca regula asociata coloanei din tabela.

sp_bindrule departament_values, 'employees.departament'

go

Rule bound to table column.

insert into employeess

values('Dan Duryea','Shipping',3321)

go

Msg 513,Level 16, State 1

A column insert or update conflicts with a rule imposed by a previous

CREATE RULE comand. The comand was sorted. The conflict accurred in database 'master' , table 'employees' , column 'departament'

Listingul 2 defineste un nou tip de date si o regula care va fi ulterior legata la acest tip.

sp_addtype badge_type2, int, 'not null'

go

create rule badgerule2

as @badge_type2 >000 and @badge_type2 <9999

go

This comand did not return data, and it did not returnany rows

sp_bindrule badgerule2, badgetype2

Domeniul de valori admisibile poate fi restrans si prin folosirea unui tip de date adecvat. Ca de pilda smallint in loc de integer.

1.4 AFISAREA LEGARILOR DE REGULI

Procedura de sistem sp_help permite afisarea de informatii despre tipurile de date definite de utilizator sau coloanele de tabele la care sunt legate reguli. In listingul 3, printre informatiile afisate despre un tip de date creat intr-un exemplu anterior se afla si regula legata la acest tip.

sp_help employee

RULE on column Departament (bound with sp_bindrule)

Jobs_list (n/a) (n/a)

create rule [Jobs_list] as @list in (

'New hire,job not specified',

'Business executive officer Manager',

'Chief financial Officer',

'Publisher',

'Managing editor',

'Marketing Manager')

Asa cum am mentionat mai devreme, informatii despre legarile de reguli se pot obtine si prin intermediul casetei de dialog Rule Properties.

Sp_help prezinta informatii despre un obiect al bazei de date, cum ar fi un tip definit de utilizator sau o coloana, afisand inclusiv numele unei reguli legate la un tip de date sau la o coloana. Regula propriu-zisa nu este afisata de catre sp_help printre informatiile privind obiectul la care acesta este legata.

Procedura sp_help va permite sa obtineti informatii si despre o regula. Dar nu veti avea parte de prea multe informatii. La specificarea regulii badgerule2 intr-un apel sp_help afiseaza doar posesorul regulii, tipul de obiect si data si ora crearii.

Probabil ca veti fi mai interesat sa afisati regua propriu-zisa, si nu caracteristicile sale ca si obiect. Pentru a afisa definitia unei reguli, folositi procedura sp_helptext. Definitia unei reguli este salvata ca linie dintr-o tabela de sistem, asa ca este furnizata in consecinta. Exemplul de mai jos infatisaza regula utilizata pentru a delimita domeniul numerelor de legitimatie admisibile:

sp_helptext badgerule2

text

create rule badgerule2

as @badge_type2 >000 and @badge_type2

Pentru afisarea regulilor puteti utiliza si administratorul de sistem SQL. Definitia unei reguli este afisata de campul Description din fereastra Manager Rules. Daca efectuati un dublu clic pe o regula sau efectuati un clic cu butonul drept si selectati Proprieties, definitia regulii va aparea in caseta de dialog Rule Proprieties.

In fine, nu uitati ca regulile se definesc in cadrul unei multimi de tabele de sistem care este locala fiecarei baze de date. Regulile definite intr-o baza de date nu sunt disponibile in alta. Puteti sa selectati definitia unei reguli intr-o sesiune de lucru cu analizorul de interogari, sa o salvati ca un fisier si apoi sa deschideti fisierul pentru a recupera regula respectiva. Definirea unei reguli este posibila dupa executarea unei comenzi USE care sa va plaseze in cadrul bazei de date in care va fi utilizata regula.

1.5 DEZLEGAREA REGULILOR

Este posibil ca la un moment dat sa nu mai doriti ca valorile introduse in cadrul unei coloane sau al unui tip de date definit de utilizator sa fie limitate de catre o regula. Dezlegarea unei reguli se poate face cu ajutorul procedurii sp_unbindrule, care anuleaza legatura dintre regula si coloana sau tipul de date la care acesta este legata. Dezlegarea unei reguli opreste aplicarea sa asupra sp_unbindrule este urmatoarea:

sp_unbindrule nume_tabela.coloana sau tip_date_utilizator [futureonly]

Ca si in cazul procedurii sp_bindrule, daca primul parametru specificat este o coloana, aceasta trebuie precedata de numele tabelei care o contine, totul incadrat intre simboluri apostrof. In cazul contrar, primul parametru va fi considerat ca fiind numele unui tip de date definit de utilizator.

Cel de-al treilea parametru, futureonly, este optionalsi se foloseste numai pentru regulile legate la tipuri de date definite de utilizator. O astfel de regula este aplicata si coloanelor deja existente care sunt definite pe baza tipului de date la care este legata regula, mai putin in cazul in care specificati parametrul optional futureonly. Acesta previne mostenirea regulii de catre coloanele deja existente. Doar noile coloane definite pe baza tipului de date vor fi afectate de regula. Dezlegarea unei reguli de la o coloana de tabela dau de la un tip de date definit de utilizator se poate face si prin intermediul administratorului de sistem SQL, efectuand un clic pe Remove dupa deschiderea casetei de dialog Rule Propries.

In exemplul de mai jos, sp_help afiseaza informatii despre tabela Employees, la a carei coloana Departament este legata la o regula. Sp_bindrule efectueaza dezlegarea acestei reguli de coloana Departament. La o noua inspectare a tabelei cu ajutorul procedurii sp_help se observa ca regula nu mai este legata de coloana.

sp_help employees

go

Name    Owner Type

employees   dbo user table

Data_located_on_segment When_created

default May 12 1994 10:15AM

Column_name Type Length Nulle Default_name Rule_name

name char 20 0 (null) (null)

department char 20 0 (null) departament_values

badge  int 4 0 (null) (null)

Object does not have any indexes.

No defined keys for this object.

sp_unbindrule 'employees.departament'

go

Rule unbound from table column.

sp_help smployees

go

Name Ower Type

employees   dbo user table

Data_located_on_segment     When_created

default   May 12 1994 10:15AM

Column_name Type Length Nulls Default_name Rule_name

name   char 20 0 (null) (null)

departament    char 20 0 (null) (null)

badge  int 4 0 (null) (null)

Object does not have any indexes.

No defined keys for this object.

Dezlegarea unei reguli se poate face si prin inlocuirea acesteia cu o regula noua. Sp_bindrule leaga o noua regula la o coloana sau la un tip de date. Vechea regula este dezlegata automat de la coloana sau tipul de date in cauza.

In listingul 4 incercarea de redefinire a regulii deja existente departament_values esueaza, deoarece o regula nu poate fi inlocuita de o alta avand acelasi nume. Se creaza o noua regula si acesta se leaga la coloana la care este legata regula departament_values. In consecinta, noua regula inlocuieste vechea regula departament_values.

create rule departament_values

as @departament in ('Sles','Field Service','Logistics'.'Shipping')

go

Msg 2714, Level 16, State 1

There is already an object named 'departament_values' in the database.

create rule depart2

as @departament in ('Sales','Field Service','Logistics','Shipping')

go

This comand did not return data, and it did not return any rows

sp_bindrule depart2, 'employees.departament'

go

In listingul 5, care continua exemplul precedent, o operatie de inserare in cadrul tabelei Employees demonstreaza ca noua regula a fost legata la coloana Departament. Vechea regula nu ar fi permis adaugarea unei linii avand compartimentul Shipping. O instructiune SELECT dovedeste ca o astfel de linie a putut fi inserata in tabela. In fine, sp_help arata ca noua regula depart2 este legata la coloana Departament a tabelei Employees, inlocuind vechea regula departament_values.

insert into employees

values ('Dan Duryea','Shipping',3321)

go

(1 row(s) affected)

select * from employees

go

name departament badge

Bob Smith Sales 1234

Mary Jones   Sales 5514

Dan Duryea   Shipping 3321

(3 row(s) affected)

sp_help employees

go

Name    Owner Type

employees dbo user table

Data_located_on_segment When_created

default May 12 1994 10:15AM

Column_name Type Length Nulls Default_name Rule_name

name char 20 0 (null) (null)

departament    char 20 0 (null) depart2

badge  int 4 0 (null) (null)

Object does not have any indexes

1.6 REDENUMIREA REGULILOR

Regulile se pot redenumi, asemeni oricaror altor obiecte, prin intermediul procedurii sp_rename. Aceasta poate fi utilizata si pentru redenumirea altor obiecte definite de utilizator, printre care tabele, vederi, coloane, proceduri stocate, declansatoare si valori implicite. Sintaxa pentru procedura sp_rename este urmatoarea:

sp_rename nume_obiect, nume_nou

In listingul 6 se redenumeste o regula existenta. Dupa aceasta, afisarea informatiilor despre tabela Employees va arata ca noul nume al regulii a intrat in vigoare, figurand in dreptul coloanei Departament.

sp_rename depart2, depart3

go

O regula poate fi redefinita si prin intermediul administratorului de sistem SQL, efectuand un clic cu butonul drept pe regula vizata si selectand Rename. Introduceti noul nume al regulii si apasati Enter.

1.7 STERGEREA REGULILOR

Instructiunea DROP RULE va permite sa stergeti definitiv o regula din cadrul unei baze de date. Stergerea regulii se efectueaza imediat in cazul in care aceasta nu este legata la nici o coloana si la nici un tip definit de utilizator. In caz contrar, pentru a putea efectua stergera va trebui mai intai sa dezlegati regula de la orice coloana sau tip de date. Printr-o singura instructiune DROP RULE se pot sterge mai multe reguli. Sintaxa pentru DROP RULE este urmatoarea:

DROP RULE nume_regula_1[, . nume_regula_n]

In listingul 7, o prima incercare de stergere a unei reguli esueaza din cauza ca regula este legata la o coloana de tabela. Dupa dezlegarea regulii de la respectiva coloana, operatia de stergere reuseste. Sp_helptext dovedeste ca obiectul a fost sters.

drop rule departa

go

Msg 3716, Level 16, State 1

The rule 'departa' cannot be dropped because it is bound to one or more column.

sp_unbindrule 'employees.department'

go

Rule unbound from table column.

drop rule depart3

go

This comand did not return data, and it did not return any rows

sp_helptext depart3

go

Stergera regulilor se poate face si prin intermediul administratorului de sistem SQL. Avand selectat Rules, selectati numele regulii in cadrul sectiunii din dreapta a ferestrei administratorului de sistem. Apoi efectuati un clic pe butonul de stergere de pe bara cu instrumente.

2. DEFINIREA VALORILOR IMPLICITE

O valoare implicita permite stabilirea unei valori care este inscrisa automat intr-o coloana atunci cand nu se specifica explicit nici o alta valoare. Legarea unei valori implicite la o coloana sau la un tip de date definit de utilizator se face prin intermediul procedurii sp_binddefault. O valoare implicita trebuie sa fie compatibila cu tipul de date al coloanei lcare se leaga. De asemenea, o valoare implicita nu poate sa incalce o regula asociata unei coloane.

Definitiile de valori implicite sunt stocate in tabela syscomments, asemeni definitiilor de reguli. Tot ca in cazul regulilor, legarea la o coloana a unei noi valori implicite duce la inlocuirea automata a valorii precedente. Valoarea implicita legata la o coloana are prioritate fata de valoarea implicita legata la tipul de date definit de utilizator al acesteia.

2.1 CREAREA VALORILOR IMPLICITE

Valorile implicite se pot defini cu ajutorul instructiunii CREATE DEFAULT. Numele specificat ca al doilea parametru sp_bindefault este interpretat ca numele unui tip de data definit de utilizator, mai putin in cazul in care este precedat de numele unei tabele. In aceasta din urma situatie, numele este interpretat ca o coloana a unei tabele. Sintaxa instructiunii CREATE DEFAULT este urmatoarea:

CREATE DEFAULT nume_valoare_implicita AS valoare_constanta

Crearea unei valori implicite este posibila si prin intermediul administratorului de sistem SQL, urmand pasii de mai jos:

Dupa ce ati lansat administratorul de sistem SQL, selectati serverul si baza de date in cadrul careia doriti sa creati valoarea implicita

Expandati catalogul Objects si selectati Defaults.

Efectuati un clic cu butonul drept si selectati New Default pentru a apela caseta de dialog Default Proprietes.

Introduceti un nume si valoarea implicita si efectuati clic pe OK.

2.2 LEGAREA VALORILOR IMPLICITE

Pentru legarea unei valori implicite la o coloana de tabela sau la un tip de date definit de utilizator puteti sa apelati la procedura de sistem sp_bindefault. Cel de-al doilea parametru poate fi numele unei coloane dintr-o tabela sau al unui tip de date definit de utilizator. Prin specificarea celui de-al treilea parametru indicati ca valoarea implicita va fi aplicata doar pentru noile coloane ce se vor defini, nu si pentru coloanele existente. Iata sintaxa procedurii sp_bindefault:

Sp_bindefault nume_valoare_implicita, nume_tabela.nume_coloana, [futureonly]

Listingul 8 ilustreaza definirea unei valori implicite si legarea sa la o coloana Departament a tabelei Employees. In tabela se insereaza apoi o linie, omitandu-se specificarea unei valori pentru coloana Departament. O instructiune SELECT ulterioara dovedeste ca valoarea implicita a fost inscrisa in coloana Departament in dreptul liniei nou inserate.

create default Departament_default as 'Sales'

go

sp_bindefault Departament_default, 'eployees.departament'

go

Default bound to column.

insert into employees

(name, badge)

values ('John Garr',2221)

go

(1 row(s) affected)

select * from employees

where badge=2221

go

name department badge

John Garr    Sales 2221

Exemplul urmator defineste o valoare implicita si o leaga la un tip de date definit de utilizator. Cel de-al doilea parametru din apelul sp_bindefault este cinsiderat un tip de date definit de utilizator deoarece numele de obiect nu este precedat de un nume de tabela. Al treilea parametru nu este specificat, ceea ce inseamna ca valoarea implicita este aplicata tuturor coloanelor definite pe baza tipului de date in cauza.

create default badge_default

as 9999

sp_bindefault badge_default, badge_type2

Default bound to datatype.

Legarea unei valori implicite la o coloana de tabela sau la un tip de date definit de utilizator se poate face si prin intermediul administratorului de sistem SQL, urmand pasii de mai jos:

Dupa ce ati lansat administratorul de sistem SQL, selectati serverul si baza de date in cadrul careia este definita valoarea implicita.

Expandati catalogul Objects si selectati Defaults.

Efectuati un dublu clic pe valoarea implicita pe care doriti sa o legati.

Efectuati un clic pe unul din butoanele Bind Columns sau Bind UDTs.

In cazul legarii la o coloana, apasati butonul Add>> pentru a selecta coloanele la care doriti sa fie legata valoarea implicita. In cadrul legarii la un tip de date definit de utilizator, selectati tipul respectiv bifand caseta corespunzatoare din cadrul coloanei Bind.

Efectuati un clic pe OK si apoi efectuati un clic pe Close pentru a inchide caseta de dialog Default Proprieties.

La o coloana data nu poate fi legata decat o singura valoare implicita. Daca incercati legarea unei valori implicite la o coloana pentru care exista deja o valoare implicita, SQL Server va genera un mesaj de eroare. Folositi instructiunea sp_unbindefault pentru a inlatura valoarea implicita existenta si apoi legati la o coloana noua valoare implicita. Spre deosebire de reguli, valorile implicite se aplica in cadrul operatiilor de copiere in masa. De asemenea, daca pentru o coloana exista atat o valoare implicita, cat si o regula, valoarea implicita se aplica prima. Apoi este verificata regula. Aceasta scuteste regulile de a tine cont de valori NULL, pentru ca valoarea implicita va fi fost deja inscrisa.

2.4 AFISAREA LEGARILOR DE VALORI IMPLICITE

Procedura sp_help poate sa afiseze legarile de valori implicite la coloane de tabele sau tipuri de date definite de utilizator. In listingul 9, sp_help afiseaza valoarea implicita care este legata la coloana Badge.

sp_help employees

go

Name    Owner Type

employees dbo user table

Data_located_on_segment     When_created

default Oct 18 1994 12:52PM

Column_name Type Length Nulls Default_name Rule_name

-------- ----- ------ -------- ----- ------ -------- ----- ------ ---------

name char 20 0 (null) (null)

department char 20 0 (null) (null)

badge  int 4 0 badge_default (null)

Object does not have any indexes.

2.5 AFISAREA VALORILOR IMPLICITE

Afisarea definitiei unei valori implicite se poate face cu ajutorul procedurii sp_helptext. Definitiile de valori implicite sunt stocate ca linii in cadrul tabelei de sistem syscomments. Afisarea unei astfel de definitii se face in consecinta. In exemplul de mai jos, definitia unei valori implicite este afisata prin intermediul sp_helptext:

sp_helptext Department_default

go

text-------- ----- ------ ----- ----- ----------------

create default Department_default as 'Sales'

Definitia unei valori implicite poate fi afisata si in cadrul administratorului de sistem SQL. Acesta apare in campul Value al casetei de dialog Default Properties. Efectuati un dublu clic pe o valoare implicita sau efectuati un clic cu butonul drept si selectati Edit pentru a afisa definitia acesteia in cadrul casetei de dialog Default Properties.

DEZLEGAREA VALORILOR IMPLICITE

In momentul in care nu mai doriti inscrierea automata intr-o coloana sau intr-un tip de date definit de utilizator a unei valori implicite, trebuie sa dezlegati respectiva valoare implicita apeland la procedura sp_unbindefault, care anuleaza asocierea dintre o valoare implicita si coloana sau tipul de date la care aceasta era legata. Dezlegarea unei valori implicite opreste aplicarea sa asupra coloanei sau tipului de date definit de utilizator in cauza. Sintaxa pentru procedura sp_unbindefault este urmatoarea:

Sp_unbindefault nume_tabela.coloana sau tip_data_utilizator [, futureonly]

Daca specificati cel de-al treilea parametru, care este optional, aplicarea valorii implicite va inceta doar pentru noile coloane definite pe baza tipului de date la care este legata valoarea implicita. Acest parametru se foloseste numai pentru valorile implicite legate la tipul de date definit de utilizator. Nu se foloseste pentru valorile implicite legate la coloane de tabele. Exemplul urmator dezleaga o valoare implicita de la o coloana a unei tabele. Sp_help se apeleaza la inceput pentru a confirma ca valoarea implicita vizata este legata la coloana. Dupa dezlegarea valorii implicite, sp_help este apelata pentru a confirma ca valoarea implicita nu mai este legata la coloana in cauza.

sp_unbindefault 'employees.badge'

go

Default unbound from table column.

2.7 REDENUMIREA VALORILOR IMPLICITE

Pentru redenumirea unei valori implicite puteti utiliza procedura de sistem sp_rename. In listingul 10, o valoare implicita este redenumita cu ajutorul procedurii sp_rename. Dupa aceea, sp_help afiseaza informatii despre tabela ce contine o coloana la care este legata aceasta valoare implicita, confirmand redenumirea sa.

sp_rename Department_defaut, dept_default

go

Object name has been changed.

sp_help employees

go

Name    Owner Type

employees dbo user tabela

Data_located_on_segment When_created

default May 12 1994 10:15AM

Column_name Type length Nulls Default_name Rule_name

name char 20 0 (null) (null)

department char 20 0 dept_default (null)

badge    int 4 0 (null) (null)

Object does not have any indexes.

No defined keys for this object.

2.8 STERGEREA VALORILOR IMPLICITE

O valoare implicita poate fi inlaturata definitiv prin intermediul instructiunii DROP DEFAULT. Stergerea valorii implicite se efectueaza imediat in cazul in care aceasta nu este legata la nici o coloana si la nici un tip definit de utilizator. In caz contrar, pentru a putea efectua stergerea va trebui mai intai sa dezlegati valoarea implicita de la orice coloana sau tip de date. Printr-o singura instructiune DROP DEFAULT se pot sterge mai multe valori implicite. Sintaxa pentru DROP DEFAULT este urmatoarea:

DROP DEFAULT nume_valoare_implicita_1[,..nume_valoare_implicita_n]

In listingul 11, incercarea de a stergere a unei valori implicite esueaza din cauza ca aceasta este legata la o coloana dintr-o tabela. Dupa dezlegarea valorii implicite de la respectiva coloana, operatia de stergere reuseste.

Listingul 11 Stergerea unei valori implicite

drop default dept_default

go

Msg 3716, Level 16, State 1

The default 'dept_default' cannot be dropped because it is bound to use or more columns.

sp_unbindefault 'employees.department'

go

Default unbound from table column.

drop default dept_default

go

This comand did not return data, and it did not return any rows

sp_helptext dept_default

CONCRET

ODBC reprezinta in acelasi timp o binecuvantare si un blestem. Cu ODBC puteti sa interogati si sa manipulati o baza de date din aproape orice aplicatie Microsoft Ofice, de pe situri de Web etc. Aceasta facilitate este cu atat mai la indemana datorita capacitatii aplicatiilor Ofice 97 de a se conecta direct la baza de date. Ceea ce este nemaipomenit pentru utilizator. Poate fi dezastruos, insa, pentru dezvoltatori, deoarece acestia nu mai au nici un control asupra aplicatiilor client folosire pentru manipularea bazei de date.

Aici intervin regulile, valorile implicite si constrangerile. Prin implementarea acestor mecanisme de nivel scazut pe partea bazei de date, acolo unde in cele din urma sunt stocate informatiile, eliminati dependenta de aplicatiile client. In plus, baza de date devine suficient de puternica pentru a asigura relatiile dintre tabele.

In aplicatii de mare intindere, regulile si aceste tipuri de constrangeri pe partea de server joaca un alt rol, la fel de important. Este bine sa desemnati o persoana sau o echipa care sa raspunda de motorul de baze de date si de implementarea regulilor definite aici. Puteti evita multe dureri de cap daca responsabilitatea pentru gestionarea controlului asupra bazei de date este realizata.

In cazul unor echipe de dezvoltare mari, este greu sa faceti ca totii membrii sa cunoasca toate regulile privitoare la baza de data aflata in lucru. Pentru ca aceste reguli pot fi implementate separat de partea de dezvoltare, veti avea in continuare garantia unei aplicatii desciplinate, chiar daca numarul celor care scriu codul de accesare a bazei de date este mare. Dezlegand administrarea bazei de date catre o persoana sau o echipa centrala, puteti sa va asigurati ca nu se implementeaza nimic de doua ori si aveti posibilitateade a optimiza baza de date pentru a raspunde pe ansamblu la solicitarile diferitelor echipe.

GESTIONAREA PROCEDURILOR STOCATE SI UTILIZAREA INSTRUCTIUNILOR DE CONTROL AL FLUXULUI DE EXECUTIE

Pe m[sura ce sistemele pe care le creem devin tot mai complexe, va trebui s[ petrecem din ce @n ce mai mult timp integr`nd codul SQL, cu codul aplicatiei gazda. In capitolul de fata vom vedea care sunt instruciunile de control al fluxului de execuie pe care le putem folosi @n acest dialect SQL.

Procedurile stocate sunt instuciuni dintr-un dialect SQL precompilate ]i stocate pe un server BD ca s[ fie rulate pe server, de c[tre procesele serverului. Ele pot fi apelate de aplicaii, de declan]atoare ]i regulile de integritate ale datelor.

Avantajul procedurilor stocate vine din faptul c[ acestea ruleaz[ @n SQL Server, pe server care este cel mai potrivit. Dac[ la prima vedere nu pare un avantaj prea mare, acest fapt tine chiar de exsenta modelui clent/server. Fiindc[ SQL Server administreaz[ bazele de date din sistem, este logic ca acesta este locul cel mai bun de a rula proceduri stocate care opereaz[ cu date.

Procedurile stocate pot sa intoarca valori, sa modifice valori si se pot folosi pentru a compara o valoare furnizata de utilizator cu valori predefinite din sistem. Procedurile stocate ruleaza rapid gratie puterii de calcul mai mare pe care o ofera hardware-ul unui server SQL, fiind totodata capabile sa conlucreze cu bazele de date si sa profite de optimizarile SQL Server pentru a atinge performante maxime.

O procedura stocata poate, de asemenea, sa primeasca valori si sa intoarca valori care nu sut extrase neaparat dintr-o tabela, ci sunt calculate prin executia procedurii.

Dintr-o perspectiva mai larga, printre avantajele aduse de procedurile stocate se numara:

■ Performantele-Pentru ca procedurile stocate ruleaza pe server, care este de regula o masina mai puternica, timpul necesar executiei este in general mult mai mic decat pentru o statie de lucru. In plus, pentru ca informatiile din bazele de date sunt la indemana si aflate pe acelasi sistem fizic, nu se mai asteapta transmiterea prin retea pentru prelucrare. In schimb, procedura stocata beneficiaza de un acces imediat si direct la baza de date, ceea ce permite lucrul foarte rapid cu informatiile.

■ Beneficii pentru dezvoltarea client/server-Separand activitatile de dezvoltare pentru client si server, puteti uneori sa scurtati timpul necesar pentru realizarea unui proiect. Componentele pe partea de server pot fi dezvoltate separat de cele de pe partea de client, fiind posibila refolosirea componentelor de server intre aplicatiile client.

■ Securitatea-Puteti sa creati proceduri stocate pentru toate operatiile de adaugare/modificare/stergere/citire, controland programat fiecare dintre aceste tipuri de acces la informatii.

■ Impuneri pe partea de server a regulilor orientate spre date-In fine, unul din motivele supreme pentru utilizarea unui motor de baze de date inteligent, procedurile stocate va permit impunerea de reguli si de alte relatii logice care sporesc controlul asupra informatiilor inscrise in sistem.

Desi SQL este definit ca limbaj ne-procedural, SQL Server permite folosirea de instructiuni pentru controlul fluxului de executie. Instructiunile pentru controlul fluxului de executie se utilizeaza in cadrul procedurilor ce sunt stocate in vederea rularii ulterioare. Aceste proceduri pot fi folosite pentru efectuarea de operatii asupra unei baze de date SQL Server si a tabelelor sale, ca alternativa la scrierea de programe in limbaje de programare conventionale, precum C sau Visual Basic.

■ Procedurile stocate sunt compilate la prima rulare si stocate intr-o tabela de sistem a bazei de date curente. La compilare, procedurile sunt optimizate astfel incat sa aleaga cea mai buna cale de acces la informatiile din tabela. Aceste optimizari tin cont de caracteristicile datelor din tabela, de indecsii disponibili, de incarcarea tabelelor etc. Procedurile stocate compilate pot spori considerabil performantele sistemului

■ Un alt avantaj este faptul ca o procedura stocata poate fi executata atat pe un sistem SQL Server local, cat si pe unul aflat la distanta. Aceasta va permite sa rulati procese pe alte sisteme si sa lucrati cu informatii intre servere, nu doar cu baze de date locale.

■ o aplicatie scrisa intr-un limbaj precum C sau Visual Basic poate de asemena sa execute proceduri stocate, oferind o solutie optima intre partea de client si SQL Server.

1. DEFINIREA PROCEDURILOR STOCATE

Crearea unei proceduri stocate se face prin intermediul instructiunii CREATE PROC[EDURE]. Dreptul de executie pentru o procedura creata este acordat, implicit, posesorul bazei de date. Acesta poate acorda si altor utilizatori dreptul de executie a procedurii. Lungimea maxima pentru numele unei proceduri stocate este de 128 de ca caractere. Sintaxa care se foloseste la definirea unei noi proceduri este urmatoarea:

CREATE PROCEDURE [posesor,] nume_procedura [;numar]

[@nume_parametru tip_date [=default] [OUTput]

[@nume_parametru tip_date [=default] [OUTput]

[FOR REPLICATION] | [WITH RECOMPILE] , ENCRYPTION

AS instructiuni_sql

In exemplul de mai jos este creata o procedura simpla, care contine o instructiune SELECT ce afiseaza toate linile dintr-o tabela. Odata procedura creata, numele sau este introdus in linia de comanda pentru a fi executata. Daca numele unei proceduri stocate este precedat de alte instructiuni, executia procedurii se face printr-o instructiune

EXEC[UTE] nume_procedura

create procedure all_employees

as select * from employees

exec all_employees

name department badge

Bob Smith   Sales 1234

Mary Jones Sales 5514

(2 row(s) affected)

O procedura nu poate fi creata decat in baza de date curenta. Daca lucrati in ISQL sau in ISQL/W, pentru ca baza de date care doriti sa crati o procedura sa devina baza de date curenta puteti sa executati o instructiune USE urmata de numele respectivei baze de date. Procedurile stocate pot contine oricare dintre instructiunile Transact-SQL, mai putin instructiunile CREATE.

La crearea in sistem a unei proceduri stocate, SQL Server compileaza si verifica operatiile pe care aceasta le efectueaza. Daca apar probleme, procedura este respinsa si va trebui sa aduceti indreptarile necesare inainte de a o remite din nou. In cazul in care o procedura stocata apeleaza o alta procedura, dar care nu a fost inca implementata, veti primi un mesaj de avertizare, insa procedura va fi acceptata.

Daca nu mai creati procedura stocata apelata, utilizatorul va primi un mesaj de eroare la momentul executiei.

1.1 TRANSMITEREA DE PARAMETRI CATRE PROCEDURI

Procedurile stocate sunt foarte puternice, dar pentru a fi cat mai eficiente trebuie sa fie oarecum dinamice, permitand programatorului sa transmita valori care sa intervina in executia procedurii.

Iata cateva principii generale privind transmiterea de parametrii catre procedurile stocate:

■ O procedura poate primi unul sau mai multi parametri.

■ Parametrii pot fi utilizati ca locatii de stocare nominale, asa cum se folosesc variabilele in limbajele de programare conventionale, ca VB sau C.

■ Desemnarea parametrilor se realizeaza prin prefixare numelor acestora cu simbolul at(@).

■ Numele de parametri sunt locale procedurii in care sunt definite.

Parametrii se folosesc pentru a transmite informatii unei proceduri la apelul acesteia. In linia de comanda, parametrii se introduc dupa numele procedurii, fiind separati prin virgule daca sunt mai multi. Pentru a defini timpul informatiilor asteptate sub forma de parametri veti apela la tipurile de date din sistem.

In exemplul urmator este definita o procedura care primeste trei parametri de intrare. Acesti parametri de intrare sunt folositi in cadrul procedurii ca valori pentru clauze VALUES a unei instructiuni INSERT. La executia procedurii , cele trei valori specificate sunt folosite de catre instructiune INSERT. Rularea ulterioara a unei instructiuni SELECT dovedeste ca executia procedurii a dus la inserarea unei noi linii.

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

insert into Workers

values (@p1, @p2, @p3)

proc4 'Bob Lint' , Sales , 3333

select * from Workers

whre Badge=3333

Name    Department Badge

Bob Lint    Sales 3333

(1 row(s) affected)

Introducerea dupa numele unei proceduri a unui punct si virgula urmat de un intreg va permite sa creati mai multe versiuni ale unei proceduri, cu acelasi nume. In exemplul care urmeaza sunt create doua proceduri avand acelasi nume, dara numerele de versiune 1 si 2. La executia procedurii, numarul de versiune poate fi specificat pentru a indica versiunea de procedura care trebuie executata. Daca nu indicati un numar de versiune, va fi executata prima versiune a procedurii. Aceasta optiune nu a fost ilustrata in exemplul anterior, dar ea este disponibila in caz de necesitate. Cele doua proceduri create mai jos folosesc o instructiune PRINT pentru a intoarce un mesaj care identifica versiunea de procedura.

create procedure proc3; 1as

print 'version 1'

create procedure proc3; 2as

print 'version 2'

proc3;1

version1

proc3;2

version2

proc3

version1

In acest exemplu, proc3 a fost apelata fara a fi specificat cuvantul cheie EXECUTE, deoarece reprezinta prima instructiune dintr-o linie.

Crearea unei proceduri stocate se poate face nu numai prin ISQL sau ISQL/W, dar si prin intermediul administratorului de sistem SQL.

Pentru a crea o noua procedura stocata in cadrul administratorului de sistem SQL, urmati pasii de mai jos:

In cadrul ferestrei de administrare a serverelor, selectati Stored Procedures din catalogul Objects al bazei de date vizate.

Efectuati un clic cu butonul drept pe Stored Procedures si selectati New Stored Procedures din meniul afisat. Introduceti instructiunile Transact-SQL in caseta de dialog deschisa.

Inlocuiti <PROCEDURE NAME> cu numele noii proceduri.

Efectuati un clic pe OK pentru a crea si stoca noua procedura.

1.2 APELAREA DIN CADRUL APLICATILOR A PROCEDURILOR STOCATE

Atunci cand apelati proceduri stocate din cadrul aplicatiilor, exista cateva lucruri pe care este bine sa le stiti. In primul rand, in ceea ce priveste procedurile stocate care primesc parametri aveti mai multe optiuni. Pe de o parte, puteti intotdeauna sa specificati toti parametrii in ordinea in care acestia sunt declarati. Desi pentru inceput este cea mai simpla abordare, pe termen lung va trebui sa cantariti cu atentie. Vor fi, probabil, cazuri in care veti dori sa creati o procedura multi-functionala, care uneori primeste mai multi parametri decat de obicei. In astfel de situatii va asteptati sa aveti parametri care nu sunt specificati in fiecare apel.

Pentru a verifica daca un parametru a fost specificat veti face o comparatie cu valoarea NULL. Aceasta inseamna ca puteti sa efectuati o comparatie directa sau sa folositi operatorul de comparare ISNULL.

Din perspectiva aplicatiei, poate fi destul de neplacut sa trebuiasca sa specificati toti parametrii la fiecare apelal unei proceduri stocate, chiar si aceea care au valoarea NULL. Atunci exista posibilitatea de a transmite informatiile catre procedura stocata sub forma de parametri nominali. Spre exemplu, pentru o procedura stocata care primeste pana la trei parametri -nume, adresa si numar de telefon- puteti sa formulati urmatorul apel:

Exec sp_routine @name="blah"

Specificand numele parametrilor transmisi, SQL Server va putea sa mapeze valorile in mod corespunzator. De obicei, aceasta este cea mai buna modalitate de a transmite informatii catre SQL Server, sporind si lizibilitatea codului ca urmare a faptului ca semnificatia parametrilor este vizibila.

1.3 AFISAREA SI EDITAREA PROCEDURILOR

Procedura de sistem sp_helptext permite afisarea definitiei unei proceduri, iar procedura sp_help permite afisarea de informatii despre o procedura. Aceste proceduri de sistem sunt folosite pentru afisarea de informatii despre obiecte ale bazei de date, precum tabele, reguli sau valori implicite, inclusiv proceduri stocate.

Procedurile cu acelasi nume, dar numere de versiune diferita, sunt afisate impreuna si sterse impreuna. In exemplul de mai jos, apelul procedurii sp_helptext afiseaza definitiile pentru ambele versiuni ale procedurii proc3.

sp_helptext proc3

text

create procedure proc3;1 as

print 'version 1'

create procedure proc3;2 as

print 'version 2'

(1 row(s) affected)

In urmatorul exemplu, procedura de sistem sp_help afiseaza informatii despre procedura proc3. Daca la apelul sp_help specificati si numarul de versiune, veti obtine o eroare.

sp_help proc3

Name    Owener Type

proc3 dbo stored procedure

Data_located_on_segment When_created

not applicable Dec 7 1994 1:50PM

Exista o procedura de sistem care furnizeaza doar informatii despre procedurile stocate. Aceasta procedura este sp_stored_procedures, fiind folosita la afisarea de informatii referitoare la procedurile stocate. In urmatorul exemplu, procedura sp_stored_procedures afiseaza informatii despre o procedura creata anterior.

sp_stored_procedures procall

procedure_qualifier procedure_owner procedure_name num_input_params

▬► num_output_params num_result_sets remarks

master dbo procall;1 -1 -1 -1 (null)

(1 row(s) affected)

Afisarea si editarea procedurilor se poate face si prin intermediul administratorului de sistem SQL. Efectuati un dublu clic pe procedura dorita in cadrul listei de proceduri stocate din fereastra principala de administrarea a serverelor. Procedura respectiva este afisata si poata fi modificata in caseta de dialog Stored Prcedure Properties care este deschisa.

1.4 MODIFICAREA SI STERGEREA PROCEDURILOR STOCATE

Doua operatii relativ apropiate, pe care sigur veti ajunge sa le efectuati, sub modificarea procedurilor stocate existente si inlaturarea acelor proceduri stocate care nu se mai folosesc. In urmatoarele doua sectiuni veti vedea cum se efectueaza aceste operatii si veti intelege de ce sunt atat de strans legate.

Modificarea unei proceduri stocate existente

O procedura stocata poate fi modificata direct, asa ca veti fi nevoit sa stergeti procedura si apoi sa o creati la loc. Din pacate, nu exista nici o instructiune ALTER care sa permita modificarea continutului unei proceduri existente. Aceasta se datoreaza in mare parte planului de interogare care se creeaza si faptului ca procedurile stocate sunt deja compilate.

Pentru ca procedurile sunt compilate si planul de interogare se bazeaza pe aceste informatii compilate, SQL Server apeleaza pentru executia unei proceduri stocate la versiunea sa binara. Ar fi dificil sau chiar inposibil ca reprezentarea binara a unei proceduri stocate sa fie tradusa inapoi in "engleza" pentru a permite editarea. Din aceasta cauza este imperios necesar sa pastrati copii ale procedurilor stocate undeva in afara SQL Server. Chiar daca SQL Server poate sa furnizeze codul de definitie a unei proceduri stocate, este bine intotdeauna sa pastrati o copie de siguranta.

Continutul unei proceduri stocate poate fi obtinut cu ajutorul procedurii de sistem sp_helptext.

sp_helptext nume_prcedura

Alternativ, daca doriti sa folositi administratorul de sistem SQL pentru a afisa continutul unei proceduri stocate, puteti sa selectati baza de date care o contine si apoi procedura in cauza si sa efectuati un dublu clic pe respectiva procedura. Rezultatul este afisarea procedurii stocate. Odata ce aveti continutul, puteti sa reproduceti procedura cu modificarile dorite.

Stergera procedurilor stocate existente

Pentru stergerea unei proceduri stocate pe care ati creat-o anterior puteti apela la instructiunea DROP PROCEDURE. O singura astfel de instructiune permite stergerea mai multor proceduri stocate, specificate in continuarea comenzii si separate prin virgula, in conformitate cu sintaxa de mai jos:

DROP PROCEDURE nume_procedura_1, ,nume_procedura_n

Nu pot fi sterse selectiv versiuni ale unei proceduri. Toate versiunile cu acelasi nume ale unei proceduri se sterg impreuna, specificand in cadrul unei instructiuni DROP TABLE numele procedurii, fara nici un numar de versiune. Diferitele versiuni ale aceleiasi proceduri se sterg la un loc.

Exemplul de mai jos are ca efect stergerea celor doua versiuni ale procedurii proc3.

drop procedure proc3

This command did not return data, and it did not return any rows

Stergerea unei proceduri se poate face si in cadrul administratorului de sistem SQL. Efectuati un clic cu butonul drept al mouse-ului pe procedura vizata si selectati Delete din meniul afisat.

1.5 DESPRE INTERPRETAREA SI COMPILAREA PROCEDURILOR

Avantajul utilizarii unei proceduri stocate pentru executia unei secvente de instructiuni Transact-SQL vine din faptul ca aceasta este compilata de la prima rulare. Prin compilare, instructiunile Transact-SQL din cadrul procedurii sunt convertite de la forma textuala la o forma executabila. Tot in urma compilarii, obiectele adresate de catre procedura sunt aduse la reprezentari alternative. Spre exemplu, numele de tabele sunt inlocuite cu identificatorii obiectelor corespunzatoare, iar numele de coloane sunt inlocuite cu identificatorii acelor coloane.

Se creeaza, de asemenea, un plan de executie, asa cum se intampla chiar si pentru executia unei instructiuni Transact-SQL singulare. Acest plan de executie contine, de pilda, indecsii de folosit la extragerea de linii din tabelele adresate in cadrul procedurii. Planul de executie este stocat intr-o memorie cache, fiind folosit pentru rularea interogarilor din procedura la fiecare executie ulterioara.

1.6 RECOMPILAREA AUTOMATA

In mod normal, planul de executie al unei proceduri este rulat din memorie cache pentru proceduri, ceea ce permite o executie rapida. Procedurile sunt, insa, recompilate automat in urmatoarele circumstante:

■ O procedura este intotdeauna recompilata atunci cand SQL Server este incarcat, de regula dupa o reincarcare a sistemului de operare, si procedura este executata prima data.

■ Planul de executie al unei proceduri este, de asemenea, regenerat automat de fiecare data cand este sters un index dintr-una din tabelele adresate in procedura. Compilarea unui nou plan de executie este necesara din cauza ca cel curent adreseaza un obiect care nu mai exista-indexul-in scopul extragerii liniilor din tabela. Planul de executie trebuie refacut pentru a permite rularea interogarilor din cadrul procedurii.

■ Recompilarea unui plan de executie are loc si in cazul in care planul de executie din memoria cache este folosit curent de un alt utilizator. La aparitia unui al doilea utilizator trebuie creata o copie a planului de executie. Daca prima instanta a acestuia nu ar fi fost in folosinta, utilizarea sa ar fi eliminat necisitatea de a crea un nou plan de executie. In momentul in care un utilizator incheie rularea unei proceduri, planul de executie ramane disponibil in memoria cache, putand fi refolosit de un alt utilizator care are drepturi corespunzatoare.

■ o procedura este recompilata automat in cazul in care este stearsa si creata din nou. Toate copiile din memoria cache ale planului de executie sunt distruse, deoarece noua procedura poate sa difere substantial de versiunea originala si este necesar un nou plan de executie.

Retineti ca datorita incercarilor SQL Server de a optimiza utilizarea procedurilor stocate prin retinerea in cache a celor mai recent folosite dintre ele, este posibil ca un plan de executie mai vechi, incarcat anterior in cache, sa fie utilizat in locul noului plan de executie.

Pentru a preveni aceasta problema, trebuie sa stergeti si sa creati din nou procedura, fie sa opriti si sa reporniti SQL Server pentru a goli memoria cache pentru proceduri si a asiguar ca noua procedura este cea care va fi executata.

Puteti, de asemenea, sa specificati optiunea WITH RECOMPILE la crearea unei proceduri, iar respectiva procedura va fi recompilata automat la fiecare rulare. Aceasta optiune se foloseste in cazul in care tabelele vizate de interogarile dintr-o procedura sunt foarte dinamice. In cazul unor astfel de tabele, inserarile, stergerile si modificarile de linii sunt foarte frecvente, ceea ce duce la modificari dese ale indecsilori asociati.

In alte situatii ati putea dori sa formati recompilarea unei proceduri, care altfel nu s-ar fi efectuat automat. Spre exemplu, daca se modifica statisticile folosite pentru a determina daca un index trebuie utilizat intr-o interogare sau in cazul in care intr-o tabela se creeaza un nou index, recompilarea unei proceduri efectuate nu se produce automat. Atunci puteti specifica o clauza WITH RECOMPILE in cadrul EXECUTE de rularea a procedurii, determinand recompilarea acesteia. Sintaxa pentru instructiunea EXECUTE cu o clauza de recompilare este urmatoarea:

EXECUTE nume_procedura AS

.instructiune(-i) Transact -SQL

WITH RECOMPILE

Daca o procedura primeste parametri si acesti parametri infuenteaza functionalitatea rutinei, ar putea fi de preferat sa folositi optiunea RECOMPILE. Astfel, in cazul in care parametrii procedurii determina calea ed executie, poate fi mai avantajos ca planul de executie sa fie creat la momentul executiei, decat sa fie stabilit o data si folosit pentru toate rularile ulterioare.

Optiunea WITH RECOMPILE nu poate fi utilizata intr-o instructiune CREATE PROCEDURE impreuna cu optiunea FOR REPLICATION. Aceasta din urma se foloseste pentru a crea o procedura executata la momentul unei replicari.

Intr-o instructiune CREATE PROCEDURE puteti specifica si optiunea ENCRYPTiON, care duce la criptarea definitiei pentru procedura stocata, definitie inscrisa in tabela de sistem syscomments. Optiunea ENCRIPTION se foloseste pentru a impiedica alti utilizatori sa afiseze definitia unei proceduri si sa vada ce obiecte adreseaza si ce instructiuni Transact-SQL contine.

1.7 STABILIREA PROCEDURILOR CU EXECUTIE AUTOMATA

Procedura stocata sp_makestartup a sistemului va permite sa stabiliti executia automata a unei proceduri la pornirea SQL Server. Puteti stabili oricate proceduri care sa fie rulate automat la pornire. Sintaxa pentru sp_makestartup este urmatoare:

sp_makestartup nume_procedura

procedurile stabilite sa ruleze automat la pornire sunt executate imediat dupa pornirea si refacerea tuturor bazelor de date la incarcarea SQL Server. Procedura de sistem sp_helpstartup afiseaza procedurile marcate pentru executie la pornire. Pentru a anula rularea automata a une proceduri veti folosi procedura de sistem sp_unmakestartup.

In exemplul care urmeaza este creata o noua procedura, marcata apoi pentru execute automata la incarcarea SQL Server. In plus, se afiseaza lista procedurilor de pornire inainte si dupa ce noua procedura este inlaturata de la rularea automata la pornire.

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

(1 row(s) affected)

sp_unmakestartup test_startup

go

Procedure is no longer marked as 'startup'.

sp_helpstartup

Startup stored procedures:

1.8 DESPRE RESTRICTIILE ASUPRA PROCEDURILOR SI SCRIPTURILOR

Secventele de instructiuni Transact-SQL poarta numele de scripturi si printre ele se afla si procedurile stocate. Regulile sau sintaxa pentru utilizarea instructiunilor Transact-SQL in cadrul scripturilor se aplica urmatoarelor obiecte:

■ Proceduri

■ Reguli

■ Valori implicite

■ Declansatoare

■ Vederi

Sintaxa reprezinta, in principal, o multime de restrictii care limiteaza tipurile de instructiuni ce pot fi utilizate in scripturi. Majoritatea restrictiilor privesc instructiunile care creeaza obiecte sau care modifica baza de date sau mediul de interogare, efectele acestora aparand dupa terminarea scriptului.

Spre exemplu, chiar daca regulile si valorile implicite pot fi definite si legate la o coloana sau la un tip de date definit de utilizator in cadrul unui script, ele nu intra in vigoare decat dupa incheierea scriptului. De asemenea, nu puteti sa stergeti un obiect si apoi sa-l adresati sau sa-l creati din nou in cadrul aceluiasi script.

Pentru unele optiuni SET, definirea lor intr-un script nu se aplica interogarilor din scriptul respectiv. De exemplu, comanda SET NOCOUNT ON afecteaza toate interogarile ulterioare dintr-o procedura stocata, impiedicand numararea liniilor la executia instructiunilor SELECT. Comanda SET SHOWPLAN ON nu afecteaza interogarile din cadrul unei proceduri stocate, planul de executie nefiind afisat pentru aceste interogari.

1.9 DESPRE SEMNALUL GO DE SFARSIT DE SCRIPT

Cand folositi linia de comanda ISQL pentru executia unei secvente de intructiuni Transact-SQL, comanda GO este folosita pentru a indica finalul seriei de instructiuni. GO se introduce pe o linie, de sine statatoare. Aceasta comanda este necesara atunci cand introduceti interactiv o secventa de instructiuni sau cand preluati in ISQL instructiuni dintr-un fisier de intrare.

Comanda GO nu este necesara pentru executia unei secvente de instructiuni Transact-SQL in versiunea cu interfata grafica sub Windows a ISQL, ISQL/W. Nu este necesara nici in secventele de instructiuni Transact-SQL care se executa in cadrul unor obiecte de tip script, asa cum sunt procedurile stocate, regulile, valorile implicite, declansatoarele sau vederile.

In urmatorul exemplu de sesiune interactiva ISQL, comanda GO este folosita mai intai pentru a determina executia comenzii USE, iar apoi pentru a indica sfarsitul unui script compus din doua intructiuni SELECT.

C:>isql/U sa

Password:

1>use employees

2>go

1>select * from Workers

2>select count(*) from Workers

3>go

Name    Department Badge

Bob Smith Sales 1234

Sue Simmons  Sales 3241

Mary Watkins Field Service 6532

Linda Lovely  Library 7888

(4 row(s) affected)

(1 row(s) affected)

2. UTILIZAREA INSTRUCTIUNILOR PENTRU CONTROLUL FLUXULUI DE EXECUTIE

Transact-SQL include mai multe instructiuni care se folosesc pentru a controla ordinea de executie a instructiunilor dintr-o secventa, asa cum este o procedura stocata. Utilizarea acestor instructiuni pentru controlul fluxului de executie va permite sa organizati instructiunile din cadrul procedurilor stocate asa cum ati face-o intr-un limbaj de programare conventional, precum C sau COBOL. Veti vedea ca unele regasiri, modificari, stergeri, inserari si manipulari de linii din tabelele unei baze de date se pot efectua mai usor cu ajutorul instructiunilor de control al executiei in cadrul unor obiecte precum procedurile stocate.

2.1 UTILIZAREA INSTRUCTIUNII IFELSE

Cuvintele cheie IF si ELSE permit controlul conditional al executiei in cadrul unui script, asa cum este o procedura stocata. IF si ELSE va permit sa testati o conditie si sa executati fie instructiunile care formeaza ramura IF, fie pe cele care formeaza ramura ELSE. Conditia de test este o expresie specificata dupa cuvantul cheie IF. Sintaxa pentru instructiunea IFELSE este urmatoarea:

IF expresie

instructiune

[ELSE]

[IF expresie

Instructiune

Atunci cand este folosit intr-o instructiune IF, cuvantul cheie EXISTS este urmat, de regula, de o instructiune aflata intre paranteze. Formula EXISTS se evalueaza cu True sau False, dupa cum instructiunea dintre paranteze intoarce una sau mai multe linii sau, respectiv, nici o linie. Utilizarea clauzei ELSE nu este obligatorie in cadrul instructiunii IF. Cea mai simpla forma de instructiune IF nu contine o clauza ELSE. In exemplul urmator, o instructiune PRINT afiseaza un mesaj care confirma existenta unei linii intr-o tabela a bazei de date. Daca tabela nu contine nici o linie, este afisat mesajul NO entry. Acest mesaj este afisat si dupa afisarea mesajului de confirmare, deoarece nu se utilizeaza o clauza ELSE.

if exists (select * from Workers '

where Badge=1234)

print 'entry available'

print 'No entry'

entry available

No entry

In exemplul urmator, tabela nu mai contine linii, astfel ca este executata doar instructiunea PRINT care urmeaza blocului IF.

if exists (select * from Workers

wehre Badge=1235)

print 'entry available'

print 'No entry'

No entry

2.2 UTILIZAREA BLOCURILOR BEGIN..END

Cuvintele cheie BEGIN si END se folosesc pentru a defini o secventa de instructiuni Trasact-SQL care sa fie executata unitar. Cuvantul cheie BEGIN marcheaza inceputul unui bloc de instructiuni Transact-SQL. Cuvantul cheie END apare dupa ultima instructiune Transact-SQL a blocului. Iata sintaxa pentru BEGINEND:

BEGIN

Instructiuni

END.

BEGIN si END se folosesc de multe ori impreuna cu o instructiune conditionala, precum IF. Prin specificarea intr-o clauza IF sau ELSE, BEGIN si END fac posibila executarea mai multor instructiuni Transact-SQL in cadrul ramurii respective. In lipsa unui bloc BEGINEND care sa contina mai multe instructiuni, ramurile IF si ELSE nu pot avea decat o singura instructiune Transact-SQL.

In exemplul care urmeaza, BEGIN si END sunt folosite intr-o instructiune IF pentru a mijloci executarea mai multor instructiuni in cazul in care conditia testata este True. Instructiunea IF contine doar clauza IF; clauza ELSE nu este prezenta.

If exixts (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

row(s) affected)

In cazul de-al doilea exemplu, la instructiunea IF este adaugata o clauza ELSE care afiseaza un mesaj in cazul in care nu se gaseste nici o linie.

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

row(s) affected)

2.3 UTILIZAREA INSTRUCTIUNII WHILE

Instructiunea WHILE se foloseste pentru a executa una sau mai multe instructiuni Transact-SQL atunci cand o conditie este evaluata in True. Instructiunea care urmeaza dupa expresia din clauza WHILE se executa atat timp cat conditia testata este True. Sintaxa instructiunii WHILE este urmatoarea:

WHILE

<expresie_booleana>

<instructiune_sql>

In exemplul de mai jos, o instructiune WHILE executa o instructiune SELECT care intoarce o valoare numerica pana cand aceasta valoare atinge limita de cinci. Exemplul foloseste o variabila, care se aseamana cu un parametru prin aceea ca reprezinta o locatie de stocare nominala. Tipul unei variabile se stabileste printr-o instructiune DECLARARE, definind astfel modul de reprezentarea a informatiei in cadrul variabilei. Asemeni unui parametru, o variabila se specifica intotdeauna precedata de simbolul at(@).

In cadrul exemplului,valoarea stocata in variabila exte initializata la 1 si incrementeaza ulterior. Instructiunile din blocul WHILE se executa pana cand variabila atinge valoarea 5.

declarare @x int

select @x=1

while @x<5

begin

print 'x still less than 5'

select @x=@x+1

end

go

(1 row(s) affected)

x still less than 5

(1 row(s) affected)

x still less than 5

(1 row(s) affected)

x still less than 5

(1 row(s) affected)

x still less than 5

Pentru a putea infatisa un exemplu ma sugestiv de utilizare a instructiunii WHILE vom prezenta mai intai alte doua cuvinte cheie. Asadar, putin mai tarziu veti vedea un exemplu care se foloseste WHILE, inpreuna cu cuvintele cheie BREAK si CONTINUE.

2.4 UTILIZAREA INSTRUCTIUNII BREAK

BREAK se foloseste intr-un bloc de instructiuni Transact-SQL din cadrul unei instructiuni WHILE pentru a incheia executia acesteia. Dupa BREAK, urmatoarea instructiune executata este prima instructiune aflata dupa blocul curent.

Sintaxa pentru instructiunea BREAK este urmatoarea:

WHILE

<expresie_booleana>

<instructiune_sql>

BREAK

<instructiune_sql>

In exemplul de mai jos, instructiunea BREAK din blocul WHILE determina incheiarea acestuia din urma. Instructiunea PRINT este executata o singura data, fiind plasata inainte de BREAK. Odata intalnit BREAK, instructiunile din blocul WHILE nu mai sunt executate din nou.

Declare @x int

select @x=1

while @x<5

begin

print 'x still less than 5'

select @x=@x+1

break

end

(1 row(s) affected)

x still less then 5

(1 row(s) affected)

2.5 UTILIZAREA INSTRUCTIUNII CONTINUE

Instructiunea CONTINUE se foloseste intr-o instructiune conditionala, precum WHILE, pentru a determina explicit reluarea secventei de instructiuni din cadrul instructiunii conditinale. Sintaxa pentru CONTINUE este urmatoare:

WHILE

<expresie_booleana>

<instructiune>

BREAK

<instructiune>

CONTINUE

In exemplul de mai jos, CONTINUE se foloseste intr-o instructiune WHILE pentru a determina explicit reluarea executie blocului WHILE atat timp cat conditia specificata dupa WHILE exte True. Ca o consecinta a utilizarii instructiunii CONTINUE, ultima instructiune PRINT nu mai este executata

declare @x int

select @x=1

while @x<5

begin

print 'x stiil less than 5'

select @x=@x+1

continue

print 'this statement will not execute'

end

(1 row(s) affected)

x still less than 5

(1 row(s) affected)

x still less than 5

(1 row(s) affected)

x still less than 5

(1 row(s) affected)

x still less than 5

2.6 EXEMPLE DE UTILIZARE A INSTRUCTIUNILOR WHILE, BREAK SI CONTINUE

Desi in cele doua exemple anterioare instructiunile BREAK si CONTINUE erau folosite de sine statator, aceasta nu se intampla de regula in cadrul instructiunilor WHILE. Atat BREAK, cat si CONTINUE se folosesc de mai multe ori intr-o ramura IF sau ELSE din interiorul unei instructiuni WHILE, astfel incat intreruperea buclei se face pe baza unei conditii suplimentare. In cazul a doua sau mai multe bucle imbricate, BREAK transfera executia in bucla imediat exterioara.

In exemplul urmator, BREAK se foloseste intr-o instructiune IF, ambele aflandu-se in cadrul unei bucle WHILE. Rolul jucat de BREAK este sa incheie exectia instructiunilor din bucla WHILE in momentul in care conditia specificata in IF este True. Aceasta conditie este True daca valoarea variabilei locale @y este 2.

declarare @x int

declarare @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 brak aut of loop'

break

end

end

print 'out of while loop'

( row(s) affected)

x still less than 5

( row(s) affected)

( row(s) affected)

y is 2 so break out of loop

out of while loop

In exemplul urmator, o instructiune WHILE duce la modificarea liniilo dintr-o tabela atat timp cat este satisfacuta conditia specificata.

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

(0 row(s) affected)

title_id price

PC1035 22.95

PS1372    21.59

TC3218 20.95

row(s) affected)

(18 row(s) affected)

row(s) affected)

title_id price

BU1032 39.98

BU1111 23.90

BU7832 39.98

MC2222 39.98

PC1035 45.90

PS1372    43.18

PS2091    21.90

PS3333    39.98

TC3218 41.90

TC4203 23.90

TC7777 29.98

(12 row(s) affected)

(18 row(s) affected)

(0 row(s) affected)

Trebuie sa fiti atent atunci cand construiti o bucla WHILE si instructiunile din cadrul acesteia. Asa cum o arata urmatorul exemplu, in cazul in care conditia specificata in expresia WHILE ramane la valoarea True, bucla se va executa in continuu.

while exists (select hours_worked from pays)

print 'hours worked is less than 55'

(0 row(s) affected)

hours worked is less than 55

(0 row(s) affected)

Daca evaluarea expresiei din clauza WHILE produce mai multe valori, trebuie sa folositi EXISTS, nu operatori de comparare. In exemplul de mai jos, mesajul de eroare intors explica natura problemei.

while (select hours_worked from pays)>55

print 'hours worked is less than 55'

3. DEFINIREA SI UTILIZAREA VARIABILELOR

In Transact-SQL, variabilele pot fi locale sau globale. Variabilele locale se definesc prin intermediul instructiunii DECLARE, care stabileste si tipul lor. Atribuirea unei valori initiale pentru o variabila locala se face printr-o instructiune SELECT.

O variabila locala trebuie declarata, initializata si folosita intr-un acelasi script sau procedura stocata. Variabila nu este disponibila decat in scriptul sau in procedura in care a fost declarata, de unde si numele de variabila locala.

Variabilele locale se folosesc in scripturi si in proceduri stocate pe post de contoare sau de locatii temporare pentru stocarea altor variabile. Variabilele locale se specifica intotdeauna prin numele prefixat de @. Tipul unei variabile locale poate fi un tip definit de utiliztor sau unul din tipurile sistemului. Singura restrictie in acest sens este ca variabilele locale nu pot avea tipurile TEXT sau IMAGE.

Sintaxa pentru definirea unei variabile locale este urmatoarea:

DECLARE @nume_variabila tip_date [,@nume_variabila tip_date]

Atribuirea de valori variabilelor locale se face cu ajutorul instructiunii SELECT, sub urmatoarea forma:

SELECT @nume_variabila=expresie | instructiune select

[,@nume_variabila=expresie | instructiune select]

[FROM lista de tabel] [WHERE expresie]

[GROUP BY]

[HAVING]

[ORDER BAY]

Daca instructiunea SELECT intoarce mai multe valori, variabilei ii este atribuita ultima valoare intoarsa. In exemplul de mai jos se definesc doua variabile locale, folosite apoi pentru a retine numarul de linii dintr-o tabela. Functia CONVERT este necesara pentru a converti numarul de linii la un format textual care sa poata fi folosit in instructiunea PRINT. Mesajul afisat cu PRINT este construit in prealabil si in scris intr-o variabila locala, deoarece concatenarea nu se poate face in cazul instructiunii PRINT.

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 table workers'

print @mess

(1 row(s) affected)

(4 row(s) affected)

(1 row(s) affected)

There are 4 rows in the table workers

Fiecare instructiune SELECT din exemplul anterior intoarce un mesaj de contorizare

3.1 UTILIZAREA VARIABILELOR INPREUNA CU INSTRUCTIUNEA PRINT

Intructiunea PRINT permite afisarea de siruri ASCII sau variabile in limita a 8.000 de caractere. PRINT nu poate sa afiseze altceva decat valori de tip CHAR, nCHAR, VARCHAR sau nVARCHAR sau variabila globala @@VERSION. In cadrul unei instructiuni PRINT nu pot fi concatenati valori de tip sir de caractere. Textele sau variabilele trebuie concatenate intr-o variabila, afisata apoi printr-o instructiune PRINT. Sintaxa pentru instructiunea PRINT este urmatoarea:

PRINT 'text' | @variabila_locala | @@variabila_globala

3.2 UTILIZAREA VARIABILELOR GLOBALE

Daca parametrii procedurilor stocate au domeniul limitat la respectivele proceduri, SQL Server are definite mai multe variabile globale. Aceste variabile, definite si gestionate de catre sistem, sunt disponibile in orice moment in cadrul unei proceduri stocate. Atunci cand se lucreaza cu variabile globale, se iau in seama urmatoarela aspecte:

■ Variabilele globale nu sunt definite de catre proceduri; ele sunt

definite la nivelul serverului.

■ Nu se pot utiliza decat variabilele globale pre-declarate si definite.

■ O variabila globala se adreseaza intotdeauna prin prefixarea numelui cu doua simboluri at(@@).

■ Nu este bine a se defini variabile locale avand nume identice cu variabile de sistem, deoarce s-ar putea obtine rezultate imprevizibile in cadrul aplicatiei.

Variabilele globale se folosesc pentru a se obtine informatii despre server sau despre operatiile efectuate.

In exemplul care urmeaza, o variabila globala furnizeaza versiunea de SQL Server, fiind concatenata cu un sir de caractere si cu continutul unei a doua variabile.

PRINT @@VERSION

declare @mess1 char(21)

select @mess1 = 'Server name is ' + @@servername

PRINT @mess1

SQL Server for Windows NT 4.20 (Intel x86)

Aug 24 1993 00:00:00

(1 row(s) affected)

Server name is BOB486

4. ALTE INSTRUCTIUNI PENTRU PROCEDURI SI SCRIPTURI

Exista si alte instructiuni care pot fi utilizate in procedurile stocate si in secventele de comenzi Trasact-SQL. Acestea nu pot fi incadrate intr-o categorie anume din punct de vedere al functionalitatii. Printre aceste instructiuni se numara GOTO, RETURN, RAISERROR, WAITFOR si CASE.

4.1 UTILIZAREA INSTRUCTIUNII GOTO

GOTO permite efectuarea unui salt al executiei de la o instructiune la o alta instructiune, marcata printr-o eticheta definita de utilizator. Folosita de sine statator, instructiunea GOTO nu este conditionala. Numele etichetei destinatie trebuie sa respecte regulile pentru numele de identificatori si sa fie urmat de semnul doua puncte(

In cadrul instructiunii GOTO se va specifica decat numele etichetei, nu si semnul doua puncte.

Sintaxa pentru instructiunea GOTO se prezinta astfel:

eticheta:

GOTO eticheta

4.2 UTILIZAREA INSTRUCTIUNII RETURN

Instructiunea RETURN se foloseste pentru incheierea formala a unei interogari sau a unei proceduri, cu posibilitatea de intoarcere a unei valori pentru rutina apelanta. RETURN este utilizat frecvent atunci cand o procedura este apelata din cadrul alteia. Folosite de sine statator, instructiunea RETURN nu este conditionala, insa puteti sa o utilizati in cadrul unei instructiuni conditionale IF sau WHILE. Sintaxa instructiunii RETURN este urmatoarea:

RETURN [intreg]

RETURN poata sa apara in orice punct dintr-un script sau dintr-o procedura. Spre deosebire de BREAK, instructiunea RETURN permite intoarcerea unei valori intregi catre procedura care a apelat procedura ce contine aceasta instructiune. Executia contiunua cu instructiunea urmatoare celei care a efectuat apelul de procedura.

Pentru a intelege modul de utilizare a instructiunii RETURN trebuie intelese mai intai operatiile efectuate de SQL Server in momentul in care se incheie executia unei proceduri. SQL Server generaza intotdeauna o valoare intreaga la incheierea unei proceduri. Valoarea 0 arata ca procedura s-a incheiat cu succes. Valori negative intre -1 si -99 reprezinta cauze ale esecului instructiunilor din cadrul procedurii. Aceasta valoare intreaga este intoarsa intotdeauna dupa terminarea unei proceduri, chiar daca procedura nu contine o instructiune RETURN.

Exista posibilitatea de a inlocui valoarea generala de SQL Server cu o valoare proprie, specificand aceasta valoare in cadrul instructiunii RETURN. Este bine sa se foloseasca valori pozitive, astfel incat sa nu se intre in conflict cu codurile de stare furnizate de SQL Server. Daca nu se specifica nici o valoare, va fi folosita valoarea data de SQL Server. Daca s-au produs mai multe erori, se intoarce codul cu cea mai mare valoare in modul. Instructiunea RETURN nu poate sa intoarca valoarea NULL

4.3 UTILIZAREA INSTRUCTIUNII RAISERROR

Instructiunea RAISERROR se foloseste pentru a genera un mesaj specificat de utilizator, in aceeasi maniera in care SQL Server genereaza mesajele de eroare. Sintaxa instructiunii RAISERROR este urmatoarea:

RAISERROR (<expresie_intreaga> | < 'continut_mesaj' > , [gravitate]

▬►[ , stare [ , parametru1] [ , parametru2])

[WITH LOG]

Expresie_intreaga reprezinta un numar de eroare definit de utilizator si trebuie sa se incadreze intre 50.000 si 2.147.483.647. Aceasta valoare este inscrisa in variabila globala @@ERROR, care retine codul ultimei erori generate. RAISERROR stabileste intotdeauna nivelul implicit de gravitate 16 pentru eroarea produsa.

4.4 UTILIZAREA INSTRUCTIUNII WAITFOR

Instructiunea WAITFOR permite specificarea unui moment, a unui interval de tip sau a unui eveniment in scopul executarii unei instructiuni, a unui bloc de instructiuni, a unei proceduri stocate sau a unei tranzactii.

Sintaxa pentru instructiunea WAITFOR este urmatoarea :

WAITFOR(DELAY< 'interval'> | TIME<'ora'> | ERROREXIT

▬►PROCESSEXIT | MIRROREXIT |

Iata si semnificatiile care urmeaza dupa cuvantul cheie WAITFOR:

■ DELAY- Specifica un interval de timp de intarziere.

■ TIME- Specifica o ora, fara data calendaristica.

■ ERROREXIT- Specifica momentul incheierii cu eroare a unui proces.

■ PROCESSEXIT- Specifica momentul incheierii, cu sauf ara eroare a unui proces.

■ MIRROREXIT- Specifica momentul caderii unui dispozitiv oglindit.

clear

set talk off

set status off

set scor off

close all

relase all

if not file('marfuri.dbl')

create marfuri

endif

if not file('gestiune.dbl')

create gestiune

endif

if not file('inventar.dbl')

create inventar

enif

use marfuri index march.ndx

sele 2

use gestiune index gestiune.ndx

sele 3

use inventar index inventar.ndx

define window fer1 from 1,1 to 23,79

define menu meniu

define pad opt1 of meniu prompt '<MARFURI'

define pad opt2 of meniu prompt '<GESTIUNE'

define pad opt3 of meniu prompt '<INVENTAR'

define pad opt4 of meniu prompt '<LISTARI'

define pad opt5 of meniu prompt '<EXIT'

define popup subm

define bar 1 of subm prompt '<AFISARE'

define bar 2 of subm prompt '<ADAUGARE'

define bar 3 of subm prompt '<MODIFICARE'

define bar 4 of subm prompt '<STERGERE'

define popup cd

define bar 1 of cd prompt '<AFISARE'

define bar 2 of cd prompt '<ADAUGARE'

define bar 3 of cd prompt '<MODIFICARE'

define bar 4 of cd prompt '<STERGERE'

define popup ef

define bar 1 of ef prompt '<MARFURI'

define bar 2 of ef prompt '<DIFERENTE'

on pad opt1 of meniu activate popup subm

on pad opt2 of meniu activate popup ab

on pad opt3 of meniu activate popup cd

on pad opt4 of meniu activate popup ef

on selection bar 1 of subm do proc1

on selection bar 2 of subm do proc2

on selection bar 3 of subm do proc3

on selection bar 4 of subm do proc4

on selection bar 1 of ab do proc5

on selection bar 2 of ab do proc6

on selection bar 3 of ab do proc7

on selection bar 4 of ab do sterg

on selection bar 1 of cd do afis

on selection bar 2 of cd do proc8

on selection bar 3 of cd do proc9

on selection bar 4 of cd do proc10

on selection bar 1 of ef do list

on selection bar 2 of ef do dif

on selection pad opt5 of meniu deactivate menu

activate menu meniu

procedure proc1

clear

activate window fer1

@2,5 say 'Lista marfurilor:'

sele 1

display all of

read

deactivate window fer1

return

procedure proc2

clear

relase all

activate window fer1

sele1

do wile .t.

clear

cd=space(2)

den=space(10)

pr=0

unm=space(3)

@ 19.5 say 'Apasati WSC pentru terminare'

@ 5,5 say 'Introduceti codul marfii' get cd

read

@ 20,5 clear to 20,70

if term(12)

exit

endif

seek cd

if found()

@ 20,5 say 'Codul marfii introdus deja existent.Apasati o tasta.'

k=inkey(0)

loop

endif

@ 6,5 say 'denumire:' get den

read

@ 7,5 say ' prêt:' det pr

read

@ 8,5 say 'unitate de masura:' get unm

prêt with pr,um with unm all for codmarfa=' '

enddo

deactivate window fer1

return

function term

paramet tr

k1=readkey()

return tr=if(k>=256,k1-256,k1)

procedure proc3

clear

sele 1

change

return

procedure proc4

clear

relese all

sele 1

activate window fer1

do while .t.

clear

sele1

change

return

procedure proc4

clear

relese all

sele 1

activate window fer1

do while .t.

clear

cdm=' '

@ 19,5 say 'Apasati ESC pentru terminare.'

@ 5,5 say 'Introduceti codul marfii:' get cdm

read

@ 20,5 clear to 20,70

if term(12)

exit

endif

seek cdm

if not found()

@ 20,5 say 'CODUL NU EXISTA! Apasati o tasta'

k=inkey(0)

loop

endif

delete all for codmarfa=cdm

pak

enddo

deactivate window fer1

return

procedure proc5

clear

activate window fer1

sele2

@ 10,15 say 'GESTIUNILE'

sisplay all off

read

deactivate window fer1

return

procedure proc6

clear

sele 2

activate window fer1

do while .t.

clear

cdg=0

@ 19,5 say 'Apasati ESC pentru terminare.'

@ 5,5 say 'Introduceti codul gestiunii noi:' get cdg

read

@ 20,5 clear to 20,70

ifterm(12)

exit

endif

seek cdg

if found()

@ 20,5 say 'Codul introdus exista deja! Apasati o tasta pentru a continua'

k=inkey(0)

loop

endif

denmg=space(10)

@ 6,5 'Introduceti denumirea gestionarului :' get denmg

read

append blank

replace codgestiun with cdg,denumireg with denmg

enddo

deactivate window fer1

return

procedure afis

clear

activate window fer1

sele3

display all off

read

deactivate window fer1

return

procedure sterg

clear

sele2

activate window fer1

do while .t.

clear

codgest=0

@ 19,5 say 'Apasati ESC pentru terminare.'

@ 5,5 say 'Introduceti codul gestiunii:' get codgest

read

@ 20,5 clear 20,70

if term(12)

exit

endif

seek codgest

if not found()

@ 20,5 say'CODUL GESTIUNII PE CARE DORITI S-O STERGETI NU EXISTA!, Apasati o tasta.'

k=inkey(0)

loop

endif

delete

pack

enddo

deactivate window fer1

return

procedure proc7

sele 2

clear

change

return

procedure proc8

sele 3

clear

activate window fer1

do while

clear

co=0

com=space(2)

@ 19,5 say 'Apasati ESC pentru terminare.'

@5,5 say 'Introduceti codul gestiunii:' get co

read

@ 20,5 clear to 20,70

if term(12)

esit

endif

sele 2

seek co

if not found()

@ 20,5 say 'Codul gestiunii introdus nu exista in acte!!.Apasati o tasta'

k=inkey(0)

loop

endif

sele 3

@ 6,5 say 'Introduceti codul marfii' get com

read

seek co

b=0

if found()

do while

if b=1

exit

endif

if inventar.codmarfa=com

@ 20,5 say 'Structura mai exista!Apasati o tasta.'

read

b=1

endif

skip

enddo

endif

if b=1

loop

endif

cons=0

canf=0

@ 7,5 say 'Cantitatea scriptica:' get cans

read

@ 8,5 say 'Cantitatea faptica:' get canf

read

append blank

replace codgestiune with co,codmarfa with com,cantscr with cans,;

cantfapt with canf

enddo

deactivate window fer1

return

procedure proc9

clear

sele 3

change

return

procedure proc10

clear

activate window fer1

do while .t.

clear

cdgest=0

@ 19,5 say 'Apasati ESC pentru terminare.'

@ 5,5 say 'Introduceti codul gestiunii pe care doriti s-o stergeti:' get cdgest

read

@ 20,5 clear to 20,70

if term(12)

exit

endif

sele 2

seek cdgest

if not found()

@ 20,5 say 'Gestiunea cu codul respective nu exista in acte! Apasati o tasta.'

k=inkey(0)

loop

endif

sele 3

cdmf=space(2)

@ 7,5 say 'Introduceti codul marfii:' get cdmf

read

seek cdgest

q=0

do while ((cdgest=inventar.codgestiun) and not eof())

if inventar.codmarfa=cdmf

q=1

exit

endif

skip

enddo

if q=0

@ 20,5 say 'Structura pe care o doriti s-o stergeti nu exista!Apasati o tasta.'

k=inkey(0)

loop

endif

delete all for (codgestiun=cdgest and codmarfa=cdmf)

pack

enddo

deactivate window fer1

return

procedure list

clear

activate window fer1

do while not eof(2)

clear

sele 2

? 'Cod gestiune:' at 1,codgestiun,'Gestionar:' at 20,denumireg

? repl('-',50)

? 'Cod marfa' at 1,'Denumire' at 12,'Pret' at 24,;

'Um' at 30,'Candt scriptica' at 34

store codgestiun to c

sele 3

seek c

do while (c=codgestiun) and found()

store codmarfa to d

sele 1

seek d

sele 3

? codmarfa at 1,marfuri.denumire at 12,marfuri.pret at 24,;

marfuri.um at 30,cantscr at 35

skip

enddo

@ 20,5 say 'Apasati ESC pentru terminare!'

read

if term(12)

exit

endif

skip in 2

enddo

deactivate window fer 1

return

procedure

clear

activate window fer 1

do while not eof(2)

clear

sele 2

? 'Cod gestiune:' at 1,codgestiun,'Gestionar:' at 20,denumireg

? repl('-',70)

? 'Cod marfa' at 31,'Cant.fapt' at 43,'Val.scr' at 54

'Cant.scr' at 31,'Cant.fapt' at 43,'Val.scr' at 54.;

'Val.fapt' at 63

? repl('-',70)

store codgestiun to c

sele 3

seek c

v1=0

v2=0

v=0

do while (c=codgestiun) and(found())

store codmarfa to d

sele 1

seek d

sele 3

vs=0

vf=0

vs=cantscr*(marfuri.pret)

vf=cantfapt*(marfuri.pret)

? codmarfa at 1,marfuri,denumire at 12,marfuri.pret at 24,;

cantscr at 32,cantfapt at 43,vs at 52,vf at 62

v1=v1+vs

v2=v2+vf

skip

enddo

v=v1-v2

?

? repl('-',70)

? 'Total valoare:' at 2,v1 at 52,v2 at 62

? 'Diferenta inventar:' at 2,v at 57

@ 20,5 say 'Apasati ESC pentru terminare'

read

if term(12)

exit

endif

skip in 2

enddo

deactivate window fer 1

return

BIBLIOGRAFIE

Totul despre SQL Server 7.0

Stephen Wynkoop

Teora-2000

SQL Server 2000

Richard Waimire

Teora-2002

Bazele ORACLE

Tom Lwors

Teora-1998