Care sunt procedurile în db. Proceduri stocate în mediul server ms sql

Obiectiv– învățați cum să creați și să utilizați proceduri stocate pe serverul bazei de date.

1. Lucrând toate exemplele, analizând rezultatele execuției lor în utilitarul SQL Server Management Studio. Verificarea dacă procedurile create există în baza de date curentă.

2. Finalizarea tuturor exemplelor și sarcinilor în cursul lucrărilor de laborator.

3. Îndeplinirea sarcinilor individuale în funcție de opțiuni.

Explicații de lucru

Pentru a stăpâni programarea procedurilor stocate, folosim un exemplu de bază de date numită DB_Carti, care a fost creat în lucrarea de laborator nr.1. Când efectuați exemple și sarcini, acordați atenție corespondenței dintre numele bazei de date, tabele și alte obiecte ale proiectului.

Proceduri stocate sunt un set de comenzi constând din una sau mai multe instrucțiuni sau funcții SQL și stocate în baza de date într-o formă compilată.

Tipuri de proceduri stocate

Procedurile stocate de sistem sunt concepute pentru a efectua diverse acțiuni administrative. Aproape toate acțiunile de administrare a serverului sunt efectuate cu ajutorul lor. Putem spune că procedurile stocate de sistem sunt o interfață care oferă lucru cu tabelele de sistem. Procedurile stocate de sistem sunt prefixate cu sp_ și sunt stocate în baza de sistem date și poate fi apelat în contextul oricărei alte baze de date.

Procedurile stocate personalizate implementează anumite acțiuni. Procedurile stocate sunt un obiect complet al bazei de date. Ca urmare, fiecare procedură stocată se află într-o anumită bază de date, unde este executată.

Procedurile stocate temporar există doar pentru o perioadă scurtă de timp, după care sunt distruse automat de server. Ele sunt împărțite în locale și globale. Procedurile stocate temporare locale pot fi apelate numai de la conexiunea pe care au fost create. La crearea unei astfel de proceduri, trebuie să i se dea un nume care începe cu un singur caracter #. La fel ca toate obiectele temporare, procedurile stocate de acest tip sunt șterse automat atunci când utilizatorul se deconectează, repornește sau oprește serverul. Procedurile stocate temporare globale sunt disponibile pentru orice conexiune pe un server care are aceeași procedură. Pentru a-l defini este suficient să-i dai un nume care să înceapă cu caracterele ##. Aceste proceduri sunt șterse atunci când serverul este repornit sau oprit sau când conexiunea în al cărei context au fost create este închisă.

Crearea, modificarea procedurilor stocate

Crearea unei proceduri stocate presupune rezolvarea următoarelor sarcini: planificarea drepturilor de acces. Când creați o procedură stocată, rețineți că aceasta va avea aceleași drepturi de acces la obiectele bazei de date ca și utilizatorul care a creat-o; definirea parametrilor unei proceduri stocate, procedurile stocate pot avea parametri de intrare și de ieșire; dezvoltarea codului de procedură stocată. Codul procedurii poate conține orice secvență de comenzi SQL, inclusiv apeluri către alte proceduri stocate.

Sintaxa pentru crearea unei proceduri noi sau modificarea unei proceduri stocate existente în notația MS SQL Server este:

( CREATE | ALTER ) PROC[ EDURE] procedure_name [ ;număr] [ ( @parameter_name datatype ) [ VARYING ] [ = DEFAULT ] [ OUTPUT] ] [ ,... n] [ WITH ( RECOMPILE | ENCRIPTION | RECOMPILE, ENCRIPTION ) ] [ PENTRU REPLICARE] AS sql_statement [ ... n]

Luați în considerare parametrii acestei comenzi.

Folosind prefixele sp_, #, ##, procedura creată poate fi definită ca o procedură de sistem sau temporară. După cum puteți vedea din sintaxa comenzii, nu este permis să specificați numele proprietarului căruia îi va aparține procedura creată, precum și numele bazei de date unde ar trebui să fie plasată. Prin urmare, pentru a plasa procedura stocată pe care o creați într-o anumită bază de date, trebuie să rulați comanda CREATE PROCEDURE în contextul acelei baze de date. Când accesați obiecte din aceeași bază de date din corpul unei proceduri stocate, puteți utiliza nume prescurtate, adică fără a specifica numele bazei de date. Când doriți să faceți referire la obiecte aflate în alte baze de date, specificarea numelui bazei de date este obligatorie.

Pentru a transmite date de intrare și de ieșire în procedura stocată generată, numele parametrilor trebuie să înceapă cu caracterul @. Puteți specifica mai mulți parametri separați prin virgule într-o singură procedură stocată. Corpul unei proceduri nu trebuie să utilizeze variabile locale ale căror nume sunt aceleași cu numele parametrilor procedurii. Orice tip de date SQL, inclusiv cele definite de utilizator, este potrivit pentru definirea tipului de date a parametrilor procedurii stocate. Cu toate acestea, tipul de date CURSOR poate fi utilizat doar ca parametru de ieșire al unei proceduri stocate, de exemplu. cu cuvântul cheie OUTPUT.

Prezența cuvântului cheie OUTPUT înseamnă că parametrul corespunzător este destinat să returneze date dintr-o procedură stocată. Cu toate acestea, acest lucru nu înseamnă că parametrul nu este potrivit pentru transmiterea de valori către o procedură stocată. Specificarea cuvântului cheie OUTPUT indică serverului, la ieșirea din procedura stocată, să atribuie valoarea curentă a parametrului variabilei locale care a fost specificată ca valoare a parametrului atunci când procedura a fost apelată. Rețineți că atunci când specificați cuvântul cheie OUTPUT, valoarea parametrului corespunzător la apelarea procedurii poate fi setată numai folosind o variabilă locală. Orice expresii sau constante permise pentru parametrii obișnuiți nu sunt permise. Cuvântul cheie VARYING este utilizat împreună cu un parametru OUTPUT de tip CURSOR. Specifică faptul că parametrul de ieșire va fi setul de rezultate.

Cuvântul cheie DEFAULT este valoarea pe care parametrul corespunzător o va prelua în mod implicit. Astfel, atunci când apelați o procedură, nu puteți specifica în mod explicit valoarea parametrului corespunzător.

Deoarece serverul memorează în cache planul de execuție a interogării și codul compilat, data viitoare când procedura este apelată, vor fi utilizate valorile deja pregătite. Cu toate acestea, în unele cazuri este încă necesară recompilarea codului de procedură. Specificarea cuvântului cheie RECOMPILE instruiește sistemul să creeze un plan de execuție pentru procedura stocată de fiecare dată când este apelată.

Opțiunea PENTRU REPLICAȚIE este necesară atunci când replicați datele și includeți procedura stocată generată ca articol într-o publicație. Cuvântul cheie ENCRYPTION instruiește serverul să cripteze codul procedurii stocate, care poate oferi protecție împotriva utilizării algoritmilor proprietari care implementează procedura stocată. Cuvântul cheie AS este plasat la începutul corpului procedurii stocate în sine. Aproape toate comenzile SQL pot fi folosite în corpul procedurii, tranzacțiile pot fi declarate, blocările pot fi setate și alte proceduri stocate pot fi apelate. Puteți ieși dintr-o procedură stocată cu comanda RETURN.

Ștergerea unei proceduri stocate

DROP PROCEDURE (nume_procedură) [ ,... n]

Executarea unei proceduri stocate

Următoarea comandă este utilizată pentru a executa o procedură stocată: [ [ EXEC [ UTE] procedure_name [ ;număr] [ [ @parameter_name= ] ( valoare | @variable_name) [ OUTPUT ] | [ IMPLICIT ] ] [ ,...n]

Dacă apelul procedurii stocate nu este singura comandă din lot, atunci prezența comenzii EXECUTE este obligatorie. Mai mult, această comandă este necesară pentru a apela o procedură din corpul altei proceduri sau declanșatoare.

Utilizarea cuvântului cheie OUTPUT într-un apel de procedură este permisă numai pentru parametrii care au fost declarați când procedura a fost creată cu cuvântul cheie OUTPUT.

Când o procedură este apelată cu cuvântul cheie DEFAULT pentru un parametru, se va folosi valoarea implicită. Desigur, cuvântul specificat DEFAULT este permis numai pentru acei parametri pentru care este definită o valoare implicită.

Din sintaxa comenzii EXECUTE, puteți vedea că numele parametrilor pot fi omise la apelarea unei proceduri. Cu toate acestea, în acest caz, utilizatorul trebuie să specifice valorile parametrilor în aceeași ordine în care au fost listați atunci când a fost creată procedura. Nu puteți atribui o valoare implicită unui parametru, pur și simplu omițându-l în enumerare. Dacă doriți să omiteți parametrii care au o valoare implicită, este suficientă specificarea explicită a numelor parametrilor la apelarea procedurii stocate. Mai mult, în acest fel, puteți enumera parametrii și valorile acestora în orice ordine.

Rețineți că atunci când este apelată o procedură, sunt specificate fie nume de parametri cu valori, fie numai valori fără nume de parametru. Combinația lor nu este permisă.

Utilizarea RETURN într-o procedură stocată

Vă permite să părăsiți procedura în orice moment în funcție de condiția specificată și, de asemenea, vă permite să transferați rezultatul executării procedurii ca număr, prin care puteți judeca calitatea și corectitudinea procedurii. Un exemplu de creare a unei proceduri fără parametri:

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Exercitiul 1.

EXEC Count_Books

Verificați rezultatul.

Un exemplu de creare a unei proceduri cu un parametru de intrare:

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pagini>= @Count_pages GO

Sarcina 2. Crea această procedurăîn secțiunea Stored Procedures a bazei de date DB_Books prin utilitarul SQL Server Management Studio. Rulați-l cu comanda

EXEC Count_Books_Pages 100

Verificați rezultatul.

Un exemplu de creare a unei proceduri cu parametrii de intrare:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pagini>= @Count_pages AND Title_book LIKE @Title GO

Sarcina 3. Creați această procedură în secțiunea Proceduri stocate din baza de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l cu comanda

EXEC Count_Books_Title 100 , „P%”

Verificați rezultatul.

Un exemplu de creare a unei proceduri cu parametri de intrare și un parametru de ieșire:

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pagini>= @Count_pages AND Title_book LIKE @Title GO

Sarcina 4. Creați această procedură în secțiunea Proceduri stocate din baza de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați cu un set de comenzi:

Sql> Declare @q As int EXEC Count_Books_Itogo 100, "P%", @q output select @q

Verificați rezultatul.

Un exemplu de creare a unei proceduri cu parametrii de intrare și RETURN:

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Pushkin A.S." RETURNARE 1 ELSE RETURN 2

Sarcina 5. Creați această procedură în secțiunea Proceduri stocate din baza de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l cu comenzile:

DECLARE @return_status INT EXEC @return_status = checkname 1 SELECTAȚI „Return Status” = @return_status

Un exemplu de creare a unei proceduri fără parametri pentru a dubla valoarea unui câmp cheie din tabelul Achiziții:

CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2

Sarcina 6. Creați această procedură în secțiunea Proceduri stocate din baza de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l cu comanda

EXEC update_proc

Un exemplu de procedură cu un parametru de intrare pentru a obține toate informațiile despre un anumit autor:

CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE nume_author= @k

Sarcina 7.

EXEC select_author "Pushkin A.S." sau select_author @k= „Pushkin A.S.” sau EXEC select_author @k= "Pushkin A.S."

Un exemplu de creare a unei proceduri cu un parametru de intrare și o valoare implicită pentru a crește valoarea unui câmp cheie din tabelul Achiziții de un număr specificat de ori (de 2 ori în mod implicit):

CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @p

Procedura nu returnează date.

Sarcina 8. Creați această procedură în secțiunea Proceduri stocate din baza de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l cu comenzile:

EXEC update_proc 4 sau EXEC update_proc @p = 4 sau EXEC update_proc --se va folosi valoarea implicită.

Un exemplu de creare a unei proceduri cu parametrii de intrare și de ieșire. Creați o procedură pentru a determina numărul de comenzi finalizate într-o perioadă specificată:

CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Data_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@c, 0 )

Sarcina 9. Creați această procedură în secțiunea Proceduri stocate din baza de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l cu comenzile:

DECLARE @c2 INT EXEC count_purchases '01- jun- 2006 ', '01- jul- 2006 ', @c2 OUTPUT SELECT @c2

Opțiuni de sarcină pentru munca de laborator №4

Dispoziții generale.În SQL Server Management Studio, creați pagina noua pentru cod (butonul „Creare cerere”). Activați baza de date DB_Books creată din punct de vedere programatic utilizând instrucțiunea Use. Creați proceduri stocate utilizând instrucțiunile Create procedure și definiți singur numele procedurilor. Fiecare procedură va executa o interogare SQL, care a fost efectuată în al doilea laborator. Mai mult, codul SQL al interogărilor trebuie modificat în așa fel încât să poată trece valorile câmpurilor căutate.

De exemplu, sarcina și interogarea inițială din laboratorul #2:

/*Selectați din directorul furnizorilor (tabel Livrări) numele companiilor, numerele de telefon și TIN (Fields Name_company, Phone and INN), al căror nume de companie (Field Name_company) este OAO MIR.

SELECT Nume_companie, Telefon, INN FROM Livrări WHERE Nume_companie = „JSC MIR”

*/ – În această lucrare se va crea o procedură:

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Livrări WHERE Name_company = @comp

– Pentru a începe procedura, utilizați comanda:

EXEC select_name_company „JSC MIR”

Lista de sarcini

Creați un nou program în SQL Server Management Studio. Activați baza de date individuală creată în Laboratorul #1 folosind instrucțiunea Use. Creați proceduri stocate utilizând instrucțiunile Create procedure și definiți singur numele procedurilor. Fiecare procedură va executa o interogare SQL, care sunt prezentate ca sarcini separate prin opțiuni.

Opțiunea 1

1. Afișează o listă cu angajații care au cel puțin un copil.

2. Afișează o listă cu copiii care au primit cadouri în perioada specificată.

3. Afișați o listă cu părinții care au copii minori.

4. Afișați informații despre cadouri cu o valoare mai mare decât numărul specificat, sortate după dată.

Opțiunea 2

1. Afișați o listă de corpuri de iluminat cu tipul specificat.

2. Afișați numărul de dispozitive reparate și costul total al reparațiilor de la masterul specificat.

3. Afișați o listă de proprietari de dispozitive și numărul de accesări ale acestora, sortate după numărul de accesări în ordine descrescătoare.

4. Afișați informații despre maeștri cu un rang mai mare decât numărul specificat sau cu o dată de angajare mai mică decât data specificată.

Opțiunea 3

2. Afișați o listă de coduri de vânzare pentru care au fost vândute flori pentru o sumă mai mare decât numărul specificat.

3. Afișați data vânzării, suma, vânzătorul și floarea pentru codul de vânzare specificat.

4. Afișați o listă de culori și sortați pentru florile cu o înălțime mai mare decât numărul specificat sau înflorite.

Opțiunea 4

1. Afișați o listă de medicamente cu indicația de utilizare specificată.

2. Afișați o listă cu datele de livrare pentru care a fost vândut mai mult decât numărul specificat de medicament cu același nume.

3. Afișați data livrării, suma, numele complet al managerului de la furnizor și numele medicamentului prin codul de chitanță mai mare decât numărul specificat.

Opțiunea 5

2. Afișați o listă de echipamente scoase din funcțiune din motivul specificat.

3. Afișați data primirii, numele echipamentului, numele complet al responsabilului și data scoaterii din funcțiune pentru echipamentele scoase din funcțiune în perioada specificată.

4. Afișați o listă de echipamente cu tipul specificat sau cu o dată de sosire mai mare decât o anumită valoare

Opțiunea 6

1. Afișați o listă de feluri de mâncare cu o greutate mai mare decât numărul specificat.

2. Afișați o listă de produse ale căror nume conțin fragmentul de cuvânt specificat.

3. Afișați volumul produsului, numele vasului, numele produsului cu codul vasului de la valoarea inițială specificată până la o anumită valoare finală.

4. Afișați ordinea de gătire a preparatului și numele preparatului cu cantitatea de carbohidrați mai mare decât o anumită valoare sau numărul de calorii mai mare decât valoarea specificată.

Opțiunea 7

1. Afișați o listă de angajați cu postul specificat.

3. Afișați data înregistrării, tipul documentului, numele complet al registratorului și numele organizației pentru documentele înregistrate în perioada specificată.

4. Afișați o listă de documente înregistrate cu un anumit tip de document sau cu o dată de înregistrare mai mare decât valoarea specificată.

Opțiunea 8

1. Afișați o listă de angajați cu motivul specificat pentru plecare.

3. Afiseaza data inregistrarii, motivul concedierii, numele complet al angajatului pentru documentele inregistrate in perioada specificata.

Opțiunea 9

1. Afișați o listă cu angajații care au luat o vacanță de tipul specificat.

2. Afișați o listă de documente cu o dată de înregistrare în perioada specificată.

3. Afiseaza data inregistrarii, tipul concediului de odihna, numele complet al angajatului pentru documentele inregistrate in perioada specificata.

4. Afișați o listă de documente înregistrate cu un cod de document în intervalul specificat.

Opțiunea 10

1. Afișați o listă de angajați cu postul specificat.

2. Afișați o listă de documente care conțin fragmentul de cuvânt specificat.

3. Afișați data înregistrării, tipul documentului, numele complet al expeditorului și numele organizației pentru documentele înregistrate în perioada specificată.

4. Afișați o listă de documente înregistrate cu tipul de document specificat sau cu un cod de document mai mic decât o anumită valoare.

Opțiunea 11

1. Afișați o listă de angajați alocați pe postul specificat.

2. Afișați o listă de documente cu o dată de înregistrare în perioada specificată.

3. Afiseaza data inregistrarii, functia, numele complet al angajatului pentru documentele inregistrate in perioada specificata.

4. Afișați o listă de documente înregistrate cu un cod de document în intervalul specificat.

Opțiunea 12

3. Afișați o listă cu persoanele care au închiriat echipamente și numărul de solicitări ale acestora, sortate după numărul de solicitări în ordine descrescătoare.

Opțiunea 13

1. Afișați o listă de echipamente cu tipul specificat. 2. Afișați o listă de echipamente pe care un anumit angajat le-a scos din funcțiune.

3. Afișați cantitatea de echipamente scoase din funcțiune, grupată după tipul de echipament.

4. Afișați informații despre angajații cu o dată de angajare mai mare decât o anumită dată.

Opțiunea 14

1. Afișați o listă de flori cu tipul de frunze specificat.

2. Afișați o listă de coduri de chitanță pentru care s-au vândut flori pentru sume mai mari decât o anumită valoare.

3. Afișați data primirii, suma, numele furnizorului și culorile pentru un anumit cod de furnizor.

4. Afișați o listă de flori și un sort pentru florile cu o înălțime mai mare decât un anumit număr sau înflorite.

Opțiunea 15

1. Afișați o listă cu clienții care au sosit în camere în perioada specificată.

2. Afișați suma totală a plăților pentru camere pentru fiecare client.

3. Afișați data sosirii, tipul camerei, numele complet al clienților înregistrați în perioada specificată.

4. Afișează o listă de clienți înregistrați în camere de un anumit tip.

Opțiunea 16

1. Afișați o listă de echipamente cu tipul specificat.

2. Afișați o listă de echipamente pe care un anumit client le-a închiriat.

3. Afișați o listă cu persoanele care au închiriat echipamente și numărul de solicitări ale acestora, sortate după numărul de solicitări în ordine descrescătoare.

4. Afișați informații despre clienți sortate după adrese.

Opțiunea 17

1. Afișați o listă de obiecte de valoare cu un preț de achiziție mai mare decât o anumită valoare sau cu o perioadă de garanție mai mare decât un anumit număr.

2. Afișați o listă de locații ale activelor materiale, în numele căreia apare cuvântul specificat.

3. Afișați suma costului obiectelor de valoare cu un cod în intervalul specificat.

4. Afișați o listă de persoane responsabile financiar cu data angajării în intervalul specificat.

Opțiunea 18

1. Afișați o listă lucrări de reparații realizat de un anumit maestru.

2. Afișați o listă cu etapele de lucru incluse în lucrare, în titlul căreia apare cuvântul specificat.

3. Afișați suma costului etapelor lucrărilor de reparații pentru lucrări cu un cod în intervalul specificat.

4. Afișați o listă de master cu o dată de angajare în intervalul specificat.

Opțiunea 19

1. Afișați o listă de medicamente cu o indicație specifică.

2. Afișați o listă de numere de chitanță care au vândut mai mult de un anumit număr de medicamente.

3. Afiseaza data vanzarii, suma, numele complet al casierului si medicamentul pentru chitanta cu numarul specificat.

4. Afișați o listă de medicamente și unități de măsură pentru medicamentele cu o cantitate de ambalaj mai mare decât numărul specificat sau un cod de medicament mai mic decât o anumită valoare.

Opțiunea 20

1. Afișați o listă de angajați cu postul specificat.

2. Afișați o listă de documente care conțin fragmentul de cuvânt specificat.

3. Afiseaza data inregistrarii, tipul actului, numele complet al executorului si faptul executarii pentru actele inregistrate in perioada specificata.

4. Afișați o listă de documente înregistrate cu tipul de document specificat sau cu un cod de document într-un anumit interval.

procedură stocată - un obiect de bază de date, care este un set de instrucțiuni SQL care este compilat o singură dată și stocat pe server. Procedurile stocate sunt foarte asemănătoare cu procedurile obișnuite din limbile de nivel înalt, pot avea parametri de intrare și ieșire și variabile locale, pot efectua calcule numerice și operații pe date de caractere, ale căror rezultate pot fi atribuite variabilelor și parametrilor. Procedurile stocate pot efectua operațiuni standard de baze de date (atât DDL, cât și DML). În plus, buclele și ramurile sunt posibile în procedurile stocate, adică pot folosi instrucțiuni pentru a controla procesul de execuție.

Procedurile stocate sunt similare cu funcțiile definite de utilizator (UDF). Principala diferență este că funcțiile definite de utilizator pot fi utilizate ca orice altă expresie în interogare SQL, în timp ce procedurile stocate trebuie apelate folosind funcția CALL:

Procedura APEL(…)

EXECUTARE procedura(…)

Procedurile stocate pot returna seturi de rezultate, adică rezultatele unei interogări SELECT. Astfel de seturi de rezultate pot fi procesate folosind cursoare, prin alte proceduri stocate care returnează un pointer de set de rezultate sau prin aplicații. Procedurile stocate pot conține, de asemenea, variabile declarate pentru manipularea datelor și cursoarelor, care vă permit să treceți peste mai multe rânduri dintr-un tabel. Standardul SQL oferă IF, LOOP, REPEAT, CASE și multe alte expresii cu care să lucrați. Procedurile stocate pot accepta variabile, pot returna rezultate sau pot modifica variabile și le pot returna, în funcție de locul în care este declarată variabila.

Implementarea procedurilor stocate variază de la un SGBD la altul. Majoritatea furnizorilor majori de baze de date le susțin într-o formă sau alta. În funcție de SGBD, procedurile stocate pot fi implementate în diferite limbaje de programare precum SQL, Java, C sau C++. Procedurile stocate scrise în non-SQL pot sau nu executa interogări SQL pe cont propriu.

In spate

Partajarea logicii cu alte aplicații. Procedurile stocate încapsulează funcționalitatea; aceasta asigură accesul la date și conectivitate de gestionare între diferite aplicații.

Izolați utilizatorii de tabelele bazei de date. Acest lucru vă permite să acordați acces la procedurile stocate, dar nu și la datele tabelului în sine.

Oferă un mecanism de protecție. Conform punctului anterior, dacă puteți accesa datele doar prin proceduri stocate, nimeni altcineva nu vă poate șterge datele prin comanda SQL DELETE.

Performanță îmbunătățită ca urmare a reducerii traficului de rețea. Cu procedurile stocate, mai multe interogări pot fi combinate.

Împotriva

Încărcare crescută pe serverul bazei de date datorită faptului că cea mai mare parte a muncii se face pe partea de server și mai puțin pe partea clientului.

Trebuie să înveți multe. Va trebui să învățați sintaxa expresiei MySQL pentru a vă scrie procedurile stocate.

Dublați logica aplicației în două locuri: codul serverului și codul pentru procedurile stocate, complicând astfel procesul de manipulare a datelor.

Migrarea de la un SGBD la altul (DB2, SQL Server etc.) poate duce la probleme.

Scopul și beneficiile procedurilor stocate

Procedurile stocate îmbunătățesc performanța, îmbunătățesc opțiunile de programare și acceptă funcțiile de securitate a datelor.

În loc să stocheze o interogare utilizată frecvent, clienții se pot referi la procedura stocată corespunzătoare. Când o procedură stocată este apelată, conținutul acesteia este procesat imediat de server.

Pe lângă executarea efectivă a interogării, procedurile stocate vă permit, de asemenea, să efectuați calcule și să manipulați datele - modificați, ștergeți, executați instrucțiuni DDL (nu în toate SGBD-urile!) și apelați alte proceduri stocate, efectuați o logică tranzacțională complexă. O singură instrucțiune vă permite să apelați un script complex care este conținut într-o procedură stocată, ceea ce evită trimiterea a sute de comenzi prin rețea și, în special, nevoia de a transfera cantități mari de date de la client la server.

În majoritatea SGBD-urilor, prima dată când o procedură stocată este rulată, aceasta este compilată (parsată și este generat un plan de acces la date). În viitor, procesarea acestuia este mai rapidă. Oracle DBMS interpretează codul procedural stocat stocat în dicționarul de date. Începând cu Oracle 10g, este suportată așa-numita compilare nativă (compilare nativă) a codului procedural stocat în C și apoi în codul mașină al mașinii țintă, după care, atunci când procedura stocată este apelată, codul său obiect compilat este executat direct.

Opțiuni de programare

Odată ce creați o procedură stocată, o puteți apela în orice moment, ceea ce oferă modularitate și încurajează reutilizarea codului. Acesta din urmă facilitează întreținerea bazei de date, deoarece devine izolată de schimbarea regulilor de afaceri. Puteți modifica oricând o procedură stocată pentru a respecta noile reguli. După aceea, toate aplicațiile care îl folosesc vor respecta automat noile reguli de afaceri fără modificare directă.

Securitate

Utilizarea procedurilor stocate vă permite să restricționați sau să excludeți complet accesul direct al utilizatorilor la tabelele bazei de date, lăsând utilizatorilor doar permisiuni de a executa proceduri stocate care oferă acces indirect și strict reglementat la date. În plus, unele DBMS acceptă criptarea textului (încheierea) a unei proceduri stocate.

Aceste caracteristici de securitate vă permit să izolați structura bazei de date de utilizator, ceea ce asigură integritatea și fiabilitatea bazei de date.

Acțiuni precum „injectarea SQL” sunt mai puțin probabile, deoarece procedurile stocate bine scrise validează suplimentar parametrii de intrare înainte de a trece interogarea către DBMS.

Implementarea procedurilor stocate

Procedurile stocate sunt create de obicei folosind limbajul SQL sau implementarea sa specifică în DBMS-ul ales. De exemplu, în aceste scopuri în SGBD Microsoft SQL Server există limbajul Transact-SQL, în Oracle - PL/SQL, în InterBase și Firebird - PSQL, în PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, în IBM DB2 - SQL/PL ( ing. ), în Informix - SPL. MySQL urmează standardul SQL:2003 destul de îndeaproape, iar limbajul său este similar cu SQL/PL.

În unele SGBD, este posibil să se utilizeze proceduri stocate scrise în orice limbaj de programare capabil să creeze fișiere executabile independente, cum ar fi C++ sau Delphi. În terminologia Microsoft SQL Server, aceste proceduri sunt numite proceduri stocate extinse și sunt pur și simplu funcții conținute într-un Win32-DLL. Și, de exemplu, în Interbase și Firebird pentru funcțiile numite din DLL / SO, este definit un alt nume - UDF (User Defined Function). În MS SQL 2005, a devenit posibil să se scrie proceduri stocate în orice limbaj .NET, iar procedurile stocate extinse sunt planificate să fie abandonate în viitor. Oracle DBMS, la rândul său, permite scrierea procedurilor stocate în limbajul Java. În IBM DB2, scrierea procedurilor și funcțiilor stocate în limbaje de programare convenționale este modalitatea tradițională, suportată de la început, iar extensia procedurală SQL a fost adăugată la acest SGBD abia destul de târziu, după ce a fost inclusă în standardul ANSI. Informix acceptă, de asemenea, proceduri Java și C.

În Oracle DBMS, procedurile stocate pot fi combinate în așa-numitele pachete. Pachetul constă din două părți - specificația (specificația pachetului în engleză), care specifică definiția procedurii stocate, și corpul (corpul pachetului în engleză), unde se află implementarea sa. Astfel, Oracle vă permite să separați interfața codului programului de implementarea acestuia.

În IBM DB2, procedurile stocate pot fi combinate în module.

Sintaxă

CREATE PROCEDURA `p2`()

DEFINITOR DE SECURITATE SQL

COMENTAȚI „O procedură”

SELECTAȚI „Hello World!”;

Prima parte a codului creează o procedură stocată. Următorul - conține parametri opționali. Apoi urmează numele și în sfârșit corpul procedurii în sine.

4 caracteristici ale unei proceduri stocate:

Limbă: în scopuri de portabilitate, implicit este SQL.

Determinist: Dacă procedura returnează același rezultat tot timpul și ia aceiași parametri de intrare. Aceasta este pentru procesul de replicare și înregistrare. Valoarea implicită NU este DETERMINISTICĂ.

Securitate SQL: în timpul apelului, drepturile utilizatorului sunt verificate. INVOKER este utilizatorul care apelează procedura stocată. DEFINER este „creatorul” procedurii. Valoarea implicită este DEFINER.

Comentariu: în scopuri de documentare, valoarea implicită este „”

Apelarea unei proceduri stocate

Apelați nume_procedură_stocat (param1, param2, ....)

CALL procedure1(10 , "parametru șir" , @parameter_var);

Modificarea unei proceduri stocate

MySQL are o instrucțiune ALTER PROCEDURE pentru modificarea procedurilor, dar este potrivită doar pentru modificarea anumitor caracteristici. Dacă trebuie să modificați parametrii sau corpul procedurii, trebuie să îl ștergeți și să îl recreați.

Îndepărtareastocateproceduri

PROCEDURA DE REDARE DACĂ EXISTĂ p2;

Aceasta este o comandă simplă. Instrucțiunea IF EXISTS detectează o eroare dacă nu există o astfel de procedură.

Parametrii

CREATE PROCEDURE proc1(): listă de parametri goală

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): un parametru de intrare. Cuvântul IN este opțional deoarece parametrii impliciti sunt IN (incoming).

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): un parametru returnat.

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): un parametru, atât de intrare cât și de ieșire.

Sintaxa pentru declararea unei variabile arată astfel:

DECLARE varname DATA-TYPE DEFAULT valoare implicită;

Declaratie de procedura

CREAȚI PROCEDURA [({IN|OUT|INOUT} [,…])]
[SET DE REZULTATE DINAMIC ]
ÎNCEPE [ATOMIC]

SFÂRȘIT

Cuvinte cheie
. IN (Intrare) – parametru de intrare
. OUT (Ieșire) – parametru de ieșire
. INOUT - intrare și ieșire, precum și un câmp (fără parametri)
. SETARE REZULTATE DINAMICĂ indică faptul că procedura poate deschide numărul specificat de cursoare care vor rămâne deschise după ce procedura revine

Note
Nu este recomandat să folosiți mulți parametri în procedurile stocate (în primul rând numere mari și șiruri de caractere) din cauza congestionării rețelei și a stivei. În practică, dialectele existente Transact-SQL, PL/SQL și Informix prezintă abateri semnificative de la standard, atât în ​​declararea parametrilor, cât și în utilizarea, declararea variabilelor și invocarea subrutinei. Microsoft recomandă utilizarea următoarei aproximări pentru a estima dimensiunea memoriei cache a procedurilor stocate:
=(numărul maxim de utilizatori concurenți)*(dimensiunea plan mare performanta)*1.25. Determinarea dimensiunii planului de execuție în pagini se poate face cu ajutorul comenzii: DBCC MEMUSAGE.

Apel de procedură

În multe SGBD existente, procedurile stocate sunt apelate folosind instrucțiunea:

EXECUTĂ PROCEDURA [(][)]

Notă: Un apel la procedurile stocate poate fi efectuat dintr-o aplicație, o altă procedură stocată sau interactiv.

Exemplu de declarație de procedură

CREATE PROCEDURE Proc1 AS //declararea unei proceduri
DECLARE Cur1 CURSOR FOR SELECT SNname, City FROM SalesPeople WHERE Rating>200 //declara a cursor
OPEN Cur1 //deschide cursorul
FETCH NEXT FROM Cur1 //citește datele de pe cursor
ÎN CAZUL @@Fetch_Status=0
ÎNCEPE
FETCH NEXT FROM Cur1
SFÂRȘIT
CLOSE Cur1 //închide cursorul
DEALLOCATE Cur1
EXECUTE Proc1 //rulați procedura

Polimorfismul
Două subprograme cu același nume pot fi create în aceeași schemă dacă parametrii celor două subprograme sunt suficient de diferiți unul de celălalt încât să poată fi distinși. Pentru a face distincția între două rutine cu același nume în aceeași schemă, fiecare primește un nume alternativ și unic (nume specific). Un astfel de nume poate fi specificat în mod explicit atunci când este definită subrutina. La apelarea subrutinelor cu mai multe nume identice, determinarea subrutinei dorite se realizează în mai mulți pași:
. Inițial, toate procedurile sunt definite cu nume dat, iar dacă nu există, atunci toate funcțiile cu numele dat.
. Pentru o analiză ulterioară, au rămas doar acele subprograme, în raport cu care utilizator dat are privilegiul EXECUTE.
. Pentru ei sunt selectați cei al căror număr de parametri corespunde numărului de argumente de apel. Sunt verificate tipurile de date specificate ale parametrilor și pozițiile acestora.
. Dacă au rămas mai multe subrutine, atunci este selectată cea cu numele de calificare mai scurt.
În practică, Oracle acceptă polimorfismul pentru funcțiile declarate numai într-un pachet, [email protected]- în diferite scheme, iar supraîncărcarea este interzisă în Sybase și MS SQL Server.

Îndepărtarea și modificarea procedurilor
Următoarea instrucțiune este utilizată pentru a șterge o procedură:

Pentru a modifica o procedură, utilizați instrucțiunea:

PROCEDURA DE ALTERARE [([{IN|OUT|INOUT}])]
ÎNCEPE [ATOMIC]

SFÂRȘIT

Privilegii de a executa proceduri

GRANT EXECUTE ON LA |PUBLIC [CU OPȚIUNE DE GRANT]

Proceduri de sistem
Multe SGBD (inclusiv SQL Server) au un anumit set de proceduri stocate în sistem încorporate pe care le puteți utiliza în propriile scopuri.

Într-un articol anterior acest ciclu Am analizat cum puteți extrage date din tabele, modifica structura acestora, creați, modificați și ștergeți baze de date și obiecte conținute în acestea. În acest articol, vom vorbi mai detaliat despre obiectele specifice DBMS-ului serverului: vizualizări, declanșatoare și proceduri stocate.

În primul articol al acestei serii, publicat în numărul 3'2000 al revistei noastre, am observat că majoritatea SGBD-urilor moderne de pe partea de server acceptă vizualizări, declanșatoare și proceduri stocate. Vizualizările sunt, de asemenea, acceptate de multe DBMS desktop, cum ar fi Access, dBase, Clipper.

Trebuie remarcat faptul că declanșatoarele și procedurile stocate sunt de obicei scrise în limbaje de programare care sunt extensii procedurale ale limbajului SQL. Aceste extensii conțin instrucțiuni care vă permit să descrieți algoritmi, cum ar fi do...while, if...then...else, care nu sunt în limbajul SQL în sine (dacă vă amintiți, SQL este un limbaj non-procedural, și puteți formula o sarcină în ea, dar nu puteți descrie algoritmi pentru executarea acesteia). Spre deosebire de limbajul SQL, care urmează standardul, extensiile sale procedurale nu sunt standardizate în niciun fel, iar SGBD diferite utilizează constructe de sintaxă diferite pentru a implementa aceleași constructe algoritmice, dar o discuție despre diferențele de sintaxă a extensiilor SQL pentru diferite SGBD este dincolo. domeniul de aplicare al acestui articol.

Pentru a ilustra modul în care vizualizările, declanșatoarele și procedurile stocate pot fi utilizate, am ales Microsoft SQL Server 7.0 și baza de date NorthWind inclusă cu acea bază de date.

Înainte de a parcurge exemplele, rețineți că implementarea și stocarea declanșatorilor și a procedurilor stocate în SGBD-ul dvs. pot diferi de cele din acest articol. În plus, trebuie să aveți permisiunile corespunzătoare acordate de administratorul bazei de date pentru a crea obiecte server.

De asemenea, rețineți că unele drivere ODBC nu acceptă apelarea procedurilor stocate din aplicațiile client, chiar dacă acestea sunt acceptate de DBMS însuși. Cu toate acestea, în acest caz, procedurile stocate pot fi apelate în continuare de la declanșatoare.

Să începem cu vizualizări, apoi să discutăm despre procedurile stocate și să încheiem capitolul cu o prezentare generală a declanșatorilor.

Reprezentare

O vizualizare este un tabel virtual, care conține de obicei un set de coloane dintr-unul sau mai multe tabele. De fapt, vizualizarea nu conține date, ci doar o interogare SELECT SQL care specifică exact ce date și din ce tabele să ia atunci când accesați această vizualizare. Din această perspectivă, o vizualizare este o interogare stocată.

În cele mai multe cazuri, vizualizările sunt folosite pentru securitatea datelor. De exemplu, unele categorii de utilizatori pot avea acces la o vizualizare, dar nu și la tabelele ale căror date o formează; în plus, interogarea SQL poate conține un parametru USER (numele sub care utilizatorul este autentificat), caz în care datele disponibile la accesarea vizualizării vor depinde de numele de utilizator.

Principalele caracteristici ale vederilor sunt enumerate mai jos:

• vizualizările se comportă ca niște tabele;
• vizualizările nu conțin date;
• vizualizările pot folosi date din mai mult de un tabel.

Putem folosi instrucțiunea SQL CREATE VIEW pentru a crea o vizualizare, instrucțiunea ALTER VIEW pentru a o modifica și instrucțiunea DROP VIEW pentru a o șterge.

Vom începe cu instrucțiunea CREATE VIEW, care vă permite să creați o vizualizare pentru baza de date curentă.

Clauza CREATE VIEW

Sintaxa pentru crearea unei vizualizări este similară cu instrucțiunea SQL SELECT, cu câteva cuvinte cheie suplimentare. Mai jos este sintaxa sa simplificată:

CREATE VIEW view_name AS select_statement

Argumentul view_name indică numele vizualizării. Cuvântul cheie folosit în Microsoft SQL Server vă permite să ascundeți textul sursă al instrucțiunii CREATE VIEW în tabelul syscomments.

Cuvântul cheie AS specifică ce interogare SELECT va fi de fapt executată atunci când vizualizarea este accesată. Rețineți că această interogare nu poate conține cuvintele cheie ORDER BY, COMPUTE sau COMPUTE BY, INTO și nu poate face referire la un tabel temporar.

Pentru a modifica o vizualizare creată anterior, utilizați clauza ALTER VIEW, descrisă pe scurt în secțiunea următoare.

clauza DROP VIEW

Această clauză este folosită pentru a elimina o vizualizare din baza de date. Rețineți că atunci când un tabel este eliminat din baza de date, toate vizualizările care se referă la el sunt, de asemenea, eliminate. Folosind această clauză, trebuie să specificăm numele vizualizării care urmează să fie eliminată. După ce o vizualizare este abandonată, toate informațiile despre aceasta sunt eliminate din tabelele de sistem.

Un alt caz în care o vizualizare trebuie abandonată este atunci când structura tabelelor pe care se bazează s-a schimbat de când a fost creată vizualizarea. În acest caz, puteți șterge vizualizarea și apoi o puteți recrea folosind clauza CREATE VIEW.

Crearea și utilizarea vizualizărilor

Clauza CREATE VIEW este folosită pentru a crea vizualizări care vă permit să preluați date care îndeplinesc anumite cerințe. Vederea este creată în baza de date curentă și stocată ca obiect separat.

Cel mai bun mod de a crea o vizualizare este să creați o interogare SELECT și, după validarea acesteia, să adăugați partea lipsă a clauzei CREATE VIEW. Să ne uităm la codul sursă pentru vizualizarea Produse după categorie din baza de date NorthWind (Listing 1).

Prima linie îngroșată este modul în care instrucțiunea SQL pentru a crea o vizualizare diferă de o instrucțiune obișnuită SELECT care face munca de selectare a datelor. Clauza SELECT conținută în această vizualizare selectează câmpuri din două tabele - câmpul CategoryName din tabelul CATEGORIES și câmpurile ProductName, QuantityPerUnit, UnitsInStock, Discontinued din tabelul PRODUCTS. După aceea, datele celor două tabele sunt legate prin câmpul CategoryID, iar în setul de date rezultat sunt incluse doar acele produse care sunt încă în stoc (vezi criteriul după cuvântul cheie WHERE). Rezultatul accesării acestei vederi este prezentat în Fig. unu .

Acum să creăm o vizualizare care să arate toate teritoriile din regiunea de est. Această vizualizare se bazează pe următoarea interogare (Listing 2).

După ce ne asigurăm că clauza SELECT returnează rezultatele pe care le dorim, adăugăm o instrucțiune CREATE VIEW și denumim vizualizarea pe care o creăm EASTTERR (Listing 3).

În loc să creați manual textul de vizualizare, puteți utiliza instrumentele vizuale care sunt de obicei incluse în DBMS. Pe fig. Figura 2 arată cum poate fi creată aceeași vizualizare folosind instrumentul View Designer, care face parte din Enterprise Manager inclus cu Microsoft SQL Server.

Partea de sus a View Designer vă permite să specificați cum sunt legate tabelele și ce câmpuri vor fi afișate în vizualizare. Mai jos puteți specifica aliasuri de tabel și câmp, restricții asupra valorilor acestora, metoda de afișare. Textul sursă al vederii și rezultatele executării acesteia sunt prezentate mai jos.

Înainte să terminăm scurtă recenzie reprezentări, să vorbim puțin despre cum să obținem Informatii suplimentare despre ele. În Microsoft SQL Server 7.0 putem folosi următoarele proceduri stocate de sistem:

• Puteți utiliza procedura de stocare a sistemului sp_help pentru a obține informații despre o vizualizare. De exemplu, sp_help EastTerr va returna informații despre vizualizarea nou creată;
• puteți utiliza procedura stocată sp_helptext pentru a obține textul sursă al vizualizării;
• puteți utiliza procedura de stocare a sistemului sp_depends pentru a găsi lista de tabele de care depinde o vizualizare;
• Puteți utiliza procedura de stocare a sistemului sp_rename pentru a redenumi o vizualizare.

ÎN aceasta sectiune am analizat cum să folosim vizualizările pentru a obține date care îndeplinesc anumite criterii. Cu toate acestea, să revenim la ultimul exemplu. Există patru regiuni în baza de date NorthWind și avem nevoie de patru vederi diferite pentru a obține o listă a teritoriilor tuturor regiunilor. Această sarcină ar putea fi simplificată dacă am putea trece valoarea RegionID ca parametru. Acest lucru se poate face folosind o procedură stocată, despre care vom discuta în secțiunea următoare.

Proceduri stocate

O procedură stocată este un set compilat de instrucțiuni SQL stocate într-o bază de date ca obiect numit și executat ca o singură bucată de cod. Procedurile stocate pot accepta și returna parametri. Când un utilizator creează o procedură stocată, serverul o compilează și o plasează într-un cache partajat, după care codul compilat poate fi folosit de mai mulți utilizatori. Când o aplicație folosește o procedură stocată, îi transmite parametri, dacă există, iar serverul execută procedura fără recompilare.

Procedurile stocate îmbunătățesc performanța aplicației. În primul rând, în comparație cu interogările SQL obișnuite trimise de la o aplicație client, acestea necesită mai puțin timp pentru a se pregăti pentru execuție, deoarece sunt deja compilate și salvate. În al doilea rând, traficul de rețea în acest caz este, de asemenea, mai mic decât în ​​cazul unei interogări SQL, deoarece se transmit mai puține date prin rețea. Orez. 3 ilustrează un apel către o procedură stocată de către o aplicaţie client.

Procedurile stocate sunt recompilate automat dacă se fac modificări obiectelor pe care le afectează; cu alte cuvinte, ele sunt întotdeauna relevante. După cum sa menționat mai sus, procedurile stocate pot lua parametri, ceea ce permite aplicatii diferite utilizați aceeași procedură cu seturi diferite de intrări.

Procedurile stocate sunt utilizate în mod obișnuit pentru a menține integritatea referențială a datelor și pentru a aplica regulile de afaceri. Acesta din urmă oferă o flexibilitate suplimentară, deoarece dacă regulile de afaceri se modifică, doar corpul procedurii poate fi schimbat fără modificarea aplicațiilor client.

Există instrucțiuni SQL speciale pentru crearea, modificarea și ștergerea procedurilor - CREATE PROCEDURE, ALTER PROCEDURE și DROP PROCEDURE. Le vom analiza în secțiunea următoare.

Clauza CREATE PROCEDURE

Clauza CREATE PROCEDURE este folosită pentru a crea o procedură stocată. Are următoarea sintaxă simplificată:

CREATE PROC proc_name [ (@parameter data_type) [= default] ] [...] AS sql_statements

Argumentul proc_name stabilește numele procedurii stocate, care trebuie să fie unic în baza de date curentă. Argumentul @parameter specifică un parametru de procedură. Unul sau mai mulți parametri pot fi specificați în clauza CREATE PROCEDURE. Dacă nu există o valoare implicită pentru un parametru, acesta trebuie să fie transmis de către utilizator (sau aplicația client) atunci când procedura este apelată. În Microsoft SQL Server 7.0, numărul de parametri de procedură stocată este limitat la 1024; implicit pot fi NULL.

Rețineți, totuși, că unele mecanisme generice de acces la date pot impune restricții suplimentare asupra numărului de parametri de procedură stocați. De exemplu, driverul BDE pentru Oracle 8 poate funcționa numai cu proceduri cu până la 10 parametri.

Argumentul data_type specifică tipul de date pentru parametru. Cuvântul cheie implicit poate fi folosit pentru a seta valori implicite - poate fi o constantă sau NULL. Dacă este specificată o valoare implicită, procedura poate fi apelată fără a specifica o valoare a parametrului. Dacă o procedură folosește un parametru cu cuvântul cheie LIKE, valoarea sa implicită poate conține metacaractere (%, _ și [^]).

Cuvântul cheie OUTPUT indică faptul că acesta este un parametru de returnare.

Cuvântul cheie AS specifică acțiunea care trebuie efectuată de procedură, sub forma oricărui număr de instrucțiuni SQL și instrucțiuni din extensia procedurală SQL specifică acestui server.

Procedura creată cu clauza CREATE PROCEDURE va fi stocată în baza de date curentă. În Microsoft SQL Server, numele procedurilor sunt stocate în tabelul de sistem sysobjects, iar textul sursă este stocat în tabelul syscomments.

Pentru a modifica o procedură stocată creată anterior, utilizați clauza ALTER PROCEDURE, care este descrisă pe scurt în secțiunea următoare.

Ofertă PROCEDURĂ DE CĂDERARE

Această clauză este folosită pentru a elimina procedurile stocate dintr-o bază de date. Clauza DROP PROCEDURE are un singur argument, numele procedurii care trebuie abandonată.

Când o procedură stocată este ștearsă, informațiile acesteia sunt eliminate din tabelele de sistem sysobjects și syscomments.

Crearea și utilizarea procedurilor stocate

În secțiunea despre vederi, am observat că ar fi convenabil dacă am putea trece un parametru vizualizării care conține valoarea RegionID pentru a selecta una dintre cele patru regiuni din baza de date NorthWind. Să aruncăm o altă privire la o interogare care returnează o listă de teritorii ale regiunii:

SELECTAȚI Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Regiune ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1

Pentru a selecta o altă regiune, trebuie să schimbăm condiția din clauza WHERE din ultima linie a interogării. Prin urmare, dacă folosim o variabilă (să o numim RegID), putem selecta una dintre cele patru regiuni fără a modifica alte părți ale interogării.

Există patru regiuni în baza de date NorthWind, numerotate de la 1 la 4. Aceasta înseamnă că variabila RegID trebuie să fie de tip întreg. Codul procedurii stocate este prezentat mai jos:

CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Regiune ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID

Observați că am lăsat aproape tot textul interogării SELECT intact (în italic) și am adăugat doar o clauză CREATE PROCEDURE cu numele procedurii stocate nou creată (pe prima linie), o declarație de parametri (pe a doua). linie), și cuvânt cheie AS, indicând începutul propozițiilor care realizează efectiv acțiuni.

Rezultatul executării procedurii create în SQL Server Query Analyzer pentru RegID =2 este prezentat în fig. 3 .

Evident, putem folosi proceduri stocate pentru mai mult decât doar vizualizări extinse sau interogări SELECT „inteligente”. Procedurile stocate oferă mecanisme pentru automatizarea multor sarcini de rutină.

În Microsoft SQL Server 7.0, putem folosi și proceduri stocate de sistem pentru a lucra cu proceduri stocate obișnuite:

• sp_stored_procedures - Afișează o listă de proceduri stocate.
• sp_helptext - arată textul sursă al procedurii stocate;
• sp_depends - Afișează informații despre dependențele procedurilor stocate.
• sp_procoption - Setează sau setează opțiunile de procedură stocată;
• sp_recompile - recompilează procedura în momentul următorului apel;
• sp_rename - Schimbă numele unei proceduri.

Proceduri stocate în sistem

Deoarece vorbim despre Microsoft SQL Server, trebuie remarcat numărul mare de proceduri stocate de sistem implementate în acesta. Numele procedurilor stocate de sistem încep cu SP_ sau XP_ și sunt stocate în baza de date master. Am descris deja mai sus câteva dintre procedurile stocate de sistem utilizate în mod obișnuit.

Rețineți că declanșatoarele nu ar trebui să returneze date utilizatorului.

Două tabele speciale pot fi folosite în clauza CREATE TRIGGER. De exemplu, tabelele șterse și inserate au aceeași structură ca tabelul pe care este definit declanșatorul și conțin valorile vechi și noi ale înregistrărilor modificate de utilizator. De exemplu, putem folosi următoarea instrucțiune SQL pentru a găsi înregistrările șterse:

SELECT * FROM șters

În tabel. 3 arată conținutul tabelelor șterse și introduse pentru toate modificările posibile de date.

Pentru a modifica un declanșator existent, utilizați clauza ALTER TRIGGER. Vom vorbi despre asta în secțiunea următoare.

Mai întâi, trebuie să adăugăm două câmpuri noi la tabel care va conține aceste informații. Să le numim UpdatedBy (numele managerului care a actualizat ultima dată înregistrarea) și UpdatedWhen (ora a fost schimbată înregistrarea). Apoi, să creăm un declanșator numit KeepTrack. Iată codul lui:

CREATE TRIGGER KeepTrack ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM introdus, Customers WHERE inserted.CustomerID = Customers.CustomerID

După cum puteți vedea din codul sursă al declanșatorului, acesta este executat după fiecare operație de INSERT și UPDATE din tabelul Clienți. Acest declanșator va stoca numele managerului (utilizatorul bazei de date) în câmpul Customers.UpdatedBy și data și ora modificării în câmpul Customers.UpdatedWhen. Aceste date sunt preluate din tabelul temporar inserat.

După cum puteți vedea, acest declanșator vă permite să monitorizați modificările și să introduceți înregistrări noi în tabel.

Înainte de a încheia scurta noastră prezentare generală a declanșatorilor, ar trebui să vă spunem unde puteți găsi informații despre declanșatorii disponibili. Tabelul sysobjects stochează informații despre declanșatori și tipurile acestora, iar tabelul syscomments conține codul sursă al acestora.

Concluzie

În această parte, am analizat mai multe tipuri de obiecte de bază de date - proceduri stocate, vizualizări și declanșatoare. Am învățat următoarele:

• O vizualizare este un tabel virtual, de obicei creat ca un subset al coloanelor unuia sau mai multor tabele. Clauza CREATE VIEW este folosită pentru a crea o vizualizare, clauza ALTER VIEW este folosită pentru a o modifica, iar clauza DROP VIEW este folosită pentru a o șterge.
• O procedură stocată este un set compilat de instrucțiuni SQL stocate într-o bază de date ca obiect numit și executat ca o singură bucată de cod. CREATE PROCEDURE este folosit pentru a crea o procedură stocată, ALTER PROCEDURE este folosit pentru a o modifica și DROP PROCEDURE este folosit pentru a o șterge.
• Un declanșator este un tip special de procedură stocată care este invocată automat atunci când datele dintr-un anumit tabel sunt adăugate, șterse sau modificate folosind o instrucțiune SQL INSERT, DELETE sau UPDATE. Declanșatoarele sunt create folosind clauza CREATE TRIGGER. Clauza ALTER TRIGGER este folosită pentru a modifica un declanșator, iar clauza DROP TRIGGER este folosită pentru a-l elimina.

ComputerPress 12"2000

1. Includeți în procedurile dumneavoastră linia - SET NOCOUNT ON: Cu fiecare instrucțiune DML, serverul SQL ne returnează cu atenție un mesaj care conține numărul de înregistrări procesate. Aceasta informatie ne poate fi util în timpul depanării codului, dar după aceea va fi complet inutil. Scriind SET NOCOUNT ON, dezactivăm această funcție. Pentru procedurile stocate care conțin mai multe expresii sau/și bucle, această acțiune poate oferi o creștere semnificativă a performanței, deoarece cantitatea de trafic va fi redusă semnificativ.

CREATE PROC dbo.ProcName
LA FEL DE
SETARE NOCOUNT ON;
--Codul de procedură aici
SELECTAȚI coloana1 DIN dbo.TblTable1
--Comutați SET NOCOUNT în starea inițială
SETĂ NOCOUNT OFF;
MERGE

2. Folosiți numele schemei cu numele obiectului: Ei bine, cred că este clar. Această operațiune îi spune serverului unde să caute obiecte și, în loc să scotoci aleatoriu prin coșurile sale, va ști imediat unde trebuie să meargă și ce să ia. Cu un număr mare de baze de date, tabele și proceduri stocate, ne poate economisi în mod semnificativ timpul și nervii.

SELECT * FROM dbo.MyTable --Acesta este o modalitate bună de a face acest lucru
-- În loc de
SELECT * FROM MyTable --Acesta este un lucru rău de făcut
--Apel de procedură
EXEC dbo.MyProc -- Bun din nou
--În loc de
EXEC MyProc --Rău!

3. Nu utilizați prefixul „sp_” în numele procedurilor dvs. stocate: Dacă numele procedurii noastre începe cu „sp_”, SQL Server va căuta mai întâi în baza de date principală. Faptul este că acest prefix este utilizat pentru procedurile interne stocate personale ale serverului. Prin urmare, utilizarea acestuia poate duce la costuri suplimentare și chiar la rezultate incorecte dacă în baza sa de date se găsește o procedură cu același nume cu al dumneavoastră.

4. Folosiți IF EXISTS (SELECT 1) în loc de IF EXISTS (SELECT *): Pentru a verifica dacă o înregistrare există într-un alt tabel, folosim instrucțiunea IF EXISTS. Această expresie returnează adevărat dacă cel puțin o valoare este returnată din expresia interioară, nu contează „1”, toate coloanele sau un tabel. Datele returnate, în principiu, nu sunt utilizate în niciun fel. Astfel, pentru a comprima traficul în timpul transmisiei de date, este mai logic să folosiți „1”, după cum se arată mai jos:

DACĂ EXISTĂ (SELECTARE 1 DIN sysobjects
WHERE nume = „MyTable” ȘI tip = „U”)

5. Utilizați TRY-Catch pentru a detecta erori:Înainte de serverul 2005, după fiecare solicitare, în procedură au fost scrise un număr mare de verificări de eroare. Mai mult cod consumă întotdeauna mai multe resurse și mai mult timp. Din 2005 SQL Server a apărut mai corect și mod convenabil solutii la aceasta problema:

ÎNCEPE ÎNCERCAȚI
--Codul
TERMINARE ÎNCERCARE
ÎNCEPE PRIMIREA
-- cod de captură de eroare
TERMINAT CAPTURĂ

Concluzie
Practic, asta e tot ce am pentru azi. Repet încă o dată că aici sunt doar acele tehnici pe care le-am folosit personal în practica mea și pot garanta eficacitatea lor.

P.S.
Prima mea postare, nu judeca strict.