Proceduri stocate SQL: creare și utilizare. Proceduri stocate în SQL
Includeți o linie în proceduri - SET NOCOUNT ON:
Cu fiecare expresie DML, serverul SQL ne returnează cu atenție un mesaj care conține numărul de înregistrări procesate. Aceste informații ne pot fi utile în timp ce depanăm codul, 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.
Transact-SQL
Utilizaț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.
Transact-SQL
SELECT * FROM dbo.MyTable --A face acest lucru este bine --În loc de SELECT * FROM MyTable --Și a face acest lucru este rău --Apelarea procedurii EXEC dbo.MyProc --Bine din nou --În loc de EXEC MyProc --Rău!
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ă.
Folosiți IF EXISTS (SELECT 1) în loc de IF EXISTS (SELECT *):
Pentru a verifica existența unei înregistrări într-un alt tabel, folosim instrucțiunea IF EXISTS. Această expresie returnează adevărat dacă cel puțin o valoare este returnată din expresia internă, nu contează „1”, toate coloanele sau un tabel. În principiu, datele returnate nu sunt utilizate în niciun fel. Astfel, pentru a comprima traficul în timpul transmisiei de date, este mai logic să folosiți „1”, așa cum se arată mai jos.
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 ale limbajului 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, procedurile stocate permit bucle și ramuri, 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 dintr-o instrucțiune SQL, în timp ce procedurile stocate trebuie apelate folosind funcția CALL:
Procedura APEL(…)
EXECUTARE procedura(…)
Procedurile stocate pot returna mai multe rezultate, adică rezultatele unei interogări SELECT. Astfel de seturi de rezultate pot fi procesate folosind cursoare, alte proceduri stocate care returnează un pointer pentru set de rezultate sau aplicații. Procedurile stocate pot conține, de asemenea, variabile declarate pentru procesarea datelor și cursoare, care vă permit să treceți peste mai multe rânduri dintr-un tabel. Standardul SQL oferă IF, LOOP, REPEAT, CASE și multe altele pentru a lucra. 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 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 care nu sunt scrise în 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 oferă conectivitate pentru accesul și gestionarea datelor în diferite aplicații.
Izolarea utilizatorilor 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 numai prin proceduri stocate, nimeni altcineva nu vă poate șterge datele prin comanda SQL DELETE.
Execuție îmbunătățită ca urmare a reducerii traficului de rețea. Folosind proceduri 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 este efectuată pe partea de server și mai puțin pe partea clientului.
Va trebui 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 capacitățile de programare și acceptă funcțiile de securitate a datelor.
În loc să stocheze o interogare utilizată frecvent, clienții pot face referire 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 - modificarea, ștergerea, executarea instrucțiunilor DDL (nu în toate SGBD-urile!) și apelarea altor proceduri stocate și efectuarea unei logici tranzacționale complexe. O singură instrucțiune vă permite să apelați un script complex conținut într-o procedură stocată, evitând trimiterea a sute de comenzi în 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 este rulată o procedură stocată, 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ă a codului procedural stocat în C și apoi în codul mașină al mașinii țintă, după care, atunci când este apelată o procedură stocată, codul său obiect compilat este executat direct.
Capabilitati de programare
Procedura stocată creată poate fi apelată în orice moment, ceea ce oferă modularitate și încurajează reutilizarea codului. Acesta din urmă face baza de date mai ușor de întreținut, deoarece devine izolată de schimbarea regulilor de afaceri. Puteți modifica oricând o procedură stocată în conformitate cu noile reguli. După aceasta, toate aplicațiile care îl folosesc vor intra automat în conformitate cu noile reguli de afaceri fără modificare directă.
Siguranță
Utilizarea procedurilor stocate vă permite să limitați sau să eliminaț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 SGBD-uri acceptă criptarea textului (încheierea) a unei proceduri stocate.
Aceste caracteristici de securitate permit izolarea structurii bazei de date de utilizator, asigurând integritatea și fiabilitatea bazei de date.
Probabilitatea unor acțiuni precum injectarea SQL este redusă deoarece procedurile stocate bine scrise verifică suplimentar parametrii de intrare înainte de a transmite interogarea către DBMS.
Implementarea procedurilor stocate
Procedurile stocate sunt create de obicei folosind limbajul SQL sau implementarea sa specifică în DBMS selectat. De exemplu, în aceste scopuri, în SGBD-ul 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 (engleză), în Informix - SPL. MySQL urmează standardul SQL:2003 destul de îndeaproape, limbajul său este similar cu SQL/PL.
Unele SGBD-uri permit utilizarea procedurilor stocate scrise în orice limbaj de programare care poate crea fișiere executabile independente, de exemplu, C++ sau Delphi. În terminologia Microsoft SQL Server, astfel de proceduri sunt numite proceduri stocate extinse și sunt pur și simplu funcții conținute într-un DLL Win32. Și, de exemplu, în Interbase și Firebird, funcțiile apelate de la DLL/SO au un alt nume - UDF (User Defined Function). MS SQL 2005 a introdus capacitatea de a 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 Java. În IBM DB2, scrierea procedurilor și funcțiilor stocate în limbaje de programare convenționale este o modalitate tradițională, susținută de la bun început, iar extensia procedurală SQL a fost adăugată acestui DBMS doar în versiuni destul de târzii, după includerea sa în standardul ANSI. Informix acceptă și proceduri în Java și C.
În SGBD-ul Oracle, procedurile stocate pot fi combinate în așa-numitele pachete. Un pachet este format din două părți - o specificație a pachetului, care specifică definiția unei proceduri stocate și un corp de pachet, care conține implementarea acestuia. Astfel, Oracle vă permite să separați interfața codului programului de implementarea acestuia.
În IBM DB2 DBMS, 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ă întotdeauna același rezultat și ia aceiași parametri de intrare. Aceasta este pentru procesul de replicare și înregistrare. Valoarea implicită NU este DETERMINISTICĂ.
Securitate SQL: drepturile utilizatorului sunt verificate în timpul apelului. 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
CALL 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 unei proceduri, ar trebui să o ștergeți și să o recreați.
Îndepărtareastocateproceduri
PROCEDURA DE REDARE DACĂ EXISTĂ p2;
Aceasta este o comandă simplă. Instrucțiunea IF EXISTS prinde o eroare dacă o astfel de procedură nu există.
Opțiuni
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 (in).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): a fost returnat un parametru.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): un parametru, atât de intrare, cât și de returnat.
Sintaxa declarației variabilelor arată astfel:
DECLARE varname DATA-TYPE DEFAULT valoare implicită;
Scopul lucrării– să învețe să creeze și să utilizeze proceduri stocate pe serverul bazei de date.
1. Analizați toate exemplele, analizați rezultatele execuției lor în utilitarul SQL Server Management Studio. Verificarea prezenței procedurilor create în baza de date curentă.
2. Completarea tuturor exemplelor și sarcinilor în timpul lucrului de laborator.
3. Finalizarea sarcinilor individuale în funcție de opțiuni.
Explicații pentru efectuarea lucrării
Pentru a stăpâni programarea procedurilor stocate, folosim o bază de date exemplu numită DB_Carti, care a fost creat în lucrarea de laborator nr.1. Când completați exemple și sarcini, acordați atenție corespondenței numelor bazei de date, tabelelor și altor 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 într-o bază de date în formă compilată.
Tipuri de proceduri stocate
Procedurile stocate de sistem sunt concepute pentru a efectua diverse acțiuni administrative. Aproape toate activitățile 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_, sunt stocate în baza de date a sistemului și pot fi apelate în contextul oricărei alte baze de date.
Procedurile stocate personalizate implementează anumite acțiuni. Procedurile stocate sunt un obiect de bază de date cu drepturi depline. Ca urmare, fiecare procedură stocată este localizată într-o bază de date specifică, 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 în care sunt create. Când creați o astfel de procedură, trebuie să îi dați un nume care începe cu un singur caracter #. Ca toate obiectele temporare, procedurile stocate de acest tip sunt șterse automat când utilizatorul se deconectează sau serverul este repornit sau oprit. Procedurile stocate temporare globale sunt disponibile pentru orice conexiune de la un server care are aceeași procedură. Pentru a-l defini, trebuie doar să îi dați un nume care începe cu caracterele ##. Aceste proceduri sunt șterse atunci când serverul este repornit sau oprit, sau când conexiunea în contextul în care au fost create este închisă.
Crearea, modificarea procedurilor stocate
Crearea unei proceduri stocate presupune rezolvarea următoarelor probleme: 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 o secvență de orice comenzi SQL, inclusiv apeluri către alte proceduri stocate.
Sintaxa operatorului pentru crearea unei proceduri noi sau modificarea unei proceduri stocate existente în notația MS SQL Server:
( CREATE | ALTER ) PROC[ EDURE] procedure_name [ ;număr] [ ( @parameter_name data_type ) [ VARYING ] [ = DEFAULT ] [ OUTPUT ] ] [ ,... n] [ WITH ( RECOMPILE | ENCRIPTION | RECOMPILE, CRYPTION ) ] [ PENTRU REPLICARE] AS sql_statement [ ... n]Să ne uităm la parametrii acestei comenzi.
Folosind prefixele sp_, #, ##, procedura creată poate fi definită ca sistem sau temporară. După cum puteți vedea din sintaxa comenzii, nu este permis să specificați numele proprietarului care va deține procedura creată, precum și numele bazei de date unde ar trebui să fie localizată. Prin urmare, pentru a plasa procedura stocată pe care o creați într-o anumită bază de date, trebuie să lansaț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 trebuie să accesați obiecte aflate în alte baze de date, specificarea numelui bazei de date este obligatorie.
Pentru a transmite date de intrare și de ieșire, numele parametrilor din procedura stocată pe care o creați trebuie să înceapă cu caracterul @. Puteți specifica mai mulți parametri într-o singură procedură stocată, separați prin virgule. Corpul unei proceduri nu trebuie să utilizeze variabile locale ale căror nume coincid cu numele parametrilor acestei proceduri. Orice tip de date SQL, inclusiv cele definite de utilizator, este potrivit pentru determinarea tipului de date a parametrilor procedurii stocate. Cu toate acestea, tipul de date CURSOR poate fi folosit doar ca parametru de ieșire al unei proceduri stocate, de exemplu. specificând 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 dintr-o procedură stocată, să atribuie valoarea parametrului curent 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 care sunt permise pentru parametri normali nu sunt permise. Cuvântul cheie VARYING este folosit împreună cu parametrul OUTPUT, care este de tip CURSOR. Specifică faptul că rezultatul va fi setul de rezultate.
Cuvântul cheie DEFAULT reprezintă valoarea pe care parametrul corespunzător o va lua în mod implicit. Astfel, atunci când apelați o procedură, nu trebuie să specificați î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 gata făcute. 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ă.
Parametrul FOR REPLICATION este necesar la replicarea datelor și la includerea procedurii stocate create 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. Corpul procedurii poate folosi aproape toate comenzile SQL, poate declara tranzacții, poate seta blocări și poate apela alte proceduri stocate. Puteți ieși dintr-o procedură stocată folosind comanda RETURN.
Eliminarea unei proceduri stocate
DROP PROCEDURE (nume_procedură) [,...n]
Executarea unei proceduri stocate
Pentru a executa o procedură stocată, utilizați comanda: [ [ 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 este necesară comanda EXECUTE. Mai mult, această comandă este necesară pentru a apela o procedură din corpul altei proceduri sau declanșatoare.
Utilizarea cuvântului cheie OUTPUT la apelarea unei proceduri este permisă numai pentru parametrii care au fost declarați când procedura a fost creată cu cuvântul cheie OUTPUT.
Când cuvântul cheie DEFAULT este specificat pentru un parametru la apelarea unei proceduri, se va folosi valoarea implicită. Desigur, cuvântul DEFAULT specificat este permis numai pentru acei parametri pentru care este definită o valoare implicită.
Sintaxa comenzii EXECUTE arată 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 la crearea procedurii. Nu puteți atribui o valoare implicită unui parametru prin simpla omitere în timpul enumerarii. Dacă doriți să omiteți parametrii care au o valoare implicită, este suficient să specificați în mod explicit numele parametrilor atunci când apelați procedura stocată. Mai mult, în acest fel puteți enumera parametrii și valorile acestora în orice ordine.
Rețineți că atunci când apelați o procedură, sunt specificate fie nume de parametri cu valori, fie numai valori fără nume de parametru. Combinarea 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 o condiție specificată și, de asemenea, vă permite să transmiteți rezultatul procedurii ca un 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 GOExercitiul 1.
EXEC Count_BooksVerificaț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 GOSarcina 2. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l folosind comanda
EXEC Count_Books_Pages 100Verificaț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 GOSarcina 3. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l folosind 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 GOSarcina 4. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați folosind setul 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 2Sarcina 5. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l folosind comenzile:
DECLARE @return_status INT EXEC @return_status = checkname 1 SELECTAȚI „Return Status” = @return_statusUn exemplu de creare a unei proceduri fără parametri pentru a crește de 2 ori valoarea unui câmp cheie din tabelul Achiziții:
CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2Sarcina 6. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l folosind comanda
EXEC update_procUn 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 Autori WHERE name_author= @kSarcina 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 * @pProcedura nu returnează date.
Sarcina 8. Creați această procedură în secțiunea Proceduri stocate a bazei de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l folosind 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 a bazei de date DB_Books folosind utilitarul SQL Server Management Studio. Rulați-l folosind comenzile:
DECLARE @c2 INT EXEC count_purchases '01-jun- 2006 ', '01- jul- 2006 ', @c2 OUTPUT SELECT @c2Opțiuni pentru sarcini pentru munca de laborator Nr. 4
Dispoziții generale.În utilitarul SQL Server Management Studio, creați o pagină nouă pentru cod (butonul „Creați interogare”). Activați baza de date DB_Books creată din punct de vedere programatic folosind instrucțiunea Use. Creați proceduri stocate folosind Creare instrucțiuni de procedură și definiți singur numele procedurilor. Fiecare procedură va executa o interogare SQL care a fost executată în al doilea laborator. Mai mult, codul SQL al interogărilor trebuie schimbat, astfel încât acestea să poată transmite valorile câmpurilor utilizate pentru căutare.
De exemplu, sarcina și cererea inițială în munca de laborator nr. 2:
/*Selectați din directorul furnizorilor (Tabel de livrare) numele firmelor, numerele de telefon și INN (câmpurile Nume_firma, Telefon și INN), a căror denumire a companiei (câmpul Nume_companie) este „OJSC MIR”.
SELECT Nume_companie, Telefon, INN FROM Livrări WHERE Nume_companie = "OJSC MIR"*/ –În această lucrare se va crea următoarea 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 programatic baza de date individuală creată în lucrarea de laborator nr. 1 utilizând instrucțiunea Use. Creați proceduri stocate folosind Creare instrucțiuni de procedură și definiți singur numele procedurilor. Fiecare procedură va executa o interogare SQL, care este prezentată sub formă de sarcini separate în funcție de opțiuni.
Opțiunea 1
1. Afișați o listă cu angajații care au cel puțin un copil.
2. Afișați 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 dispozitive cu tipul specificat.
2. Afișați numărul de dispozitive reparate și costul total al reparațiilor de la tehnicianul specificat.
3. Afișați o listă de proprietari de dispozitive ș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 meșteri 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 care au vândut 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 conform codului de vânzare specificat.
4. Afișați o listă de flori și varietatea de flori cu o înălțime mai mare decât numărul specificat sau înflorit.
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, cantitatea, numele complet al managerului de la furnizor și numele medicamentului prin cod 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 persoanei responsabile și data radierii pentru echipamentul radiat în perioada specificată.
4. Afișați o listă de echipamente cu un tip specificat sau cu o dată de primire 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 preparare a unui fel de mâncare și numele unui fel de mâncare cu cantitatea de carbohidrați mai mare decât o anumită valoare sau cantitatea de calorii mai mare decât o anumită valoare.
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 concediere.
3. Afișați data înregistrării, motivul concedierii, numele complet al angajatului pentru documentele înregistrate în perioada specificată.
Opțiunea 9
1. Afișați o listă cu angajații care au luat concediu 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, 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 al căror conținut conține 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. Afișați data înregistrării, funcția, numele complet al angajatului pentru documentele înregistrate în perioada specificată.
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 care au fost anulate de un anumit angajat.
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. Imprimaț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 printr-un anumit cod de furnizor.
4. Afișați o listă de flori și varietatea pentru flori cu o înălțime mai mare decât un anumit număr sau înflorite.
Opțiunea 15
1. Afișați o listă de clienți care s-au cazat î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șați 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 care au fost închiriate de un anumit client.
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ă adresă.
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 ale căror nume conțin cuvântul specificat.
3. Afișați suma valorilor 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ă de reparații efectuate de un anumit tehnician.
2. Afișați o listă a etapelor de lucru incluse în lucrarea al cărei titlu conține cuvântul specificat.
3. Afișați suma costului etapelor lucrărilor de reparație pentru lucru cu un cod în intervalul specificat.
4. Afișați o listă de maiștri cu data angajării î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ță pentru care au fost vândute mai mult de un anumit număr de medicamente.
3. Afișați data vânzării, suma, numele casierului și medicamentul pe chitanță cu numărul specificat.
4. Afișați o listă de medicamente și unități de măsură pentru medicamentele cu o cantitate în 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 al căror conținut conține fragmentul de cuvânt specificat.
3. Afiseaza data inregistrarii, tipul documentului, numele complet al executorului si faptul executarii pentru documentele inregistrate in perioada specificata.
4. Afișați o listă de documente înregistrate cu tipul de document specificat sau codul de document într-un anumit interval.
procedură stocată este posibilă numai dacă este efectuată în contextul bazei de date în care se află procedura.Tipuri de proceduri stocate
SQL Server are mai multe tipuri proceduri stocate.
- Sistem proceduri stocate concepute pentru a efectua diverse acţiuni administrative. Aproape toate activitățile de administrare a serverului sunt efectuate cu ajutorul lor. Putem spune că sistemic proceduri stocate sunt o interfață care oferă lucru cu tabelele de sistem, care în cele din urmă se rezumă la modificarea, adăugarea, ștergerea și preluarea datelor din tabelele de sistem atât ale bazelor de date ale utilizatorilor, cât și ale sistemului. Sistem proceduri stocate au prefixul sp_, sunt stocate în baza de date a sistemului și pot fi apelate în contextul oricărei alte baze de date.
- Personalizat proceduri stocate implementează anumite acțiuni. Proceduri stocate– un obiect de bază de date cu drepturi depline. Drept urmare, fiecare procedură stocată se află într-o anumită bază de date unde este executat.
- Temporar proceduri stocate există doar pentru o perioadă, după care sunt distruse automat de server. Ele sunt împărțite în locale și globale. Local temporar proceduri stocate pot fi apelate doar din conexiunea în care au fost create. Când creați o astfel de procedură, trebuie să îi dați un nume care începe cu un singur caracter #. Ca toate obiectele temporare, proceduri stocate de acest tip sunt șterse automat când utilizatorul se deconectează sau serverul este repornit sau oprit. Global temporar proceduri stocate sunt disponibile pentru orice conexiuni de la un server care are aceeași procedură. Pentru a-l defini, trebuie doar să îi dați un nume care începe cu caracterele ## . Aceste proceduri sunt șterse atunci când serverul este repornit sau oprit, sau când conexiunea în contextul în care au fost create este închisă.
Creați, modificați și ștergeți procedurile stocate
Creare procedură stocată presupune rezolvarea următoarelor probleme:
- determinarea tipului de creat procedură stocată: temporar sau personalizat. În plus, vă puteți crea propriul sistem procedură stocată, dându-i un nume prefixat cu sp_ și plasându-l în baza de date a sistemului. Această procedură va fi disponibilă în contextul oricărei baze de date de server local;
- drepturi de acces de planificare. În timp ce creați procedură stocată trebuie avut în vedere faptul că va avea aceleași drepturi de acces la obiectele bazei de date ca și utilizatorul care a creat-o;
- definiție parametrii procedurii stocate. Similar cu procedurile incluse în majoritatea limbajelor de programare, proceduri stocate poate avea parametri de intrare și de ieșire;
- dezvoltarea codului procedură stocată. Codul procedurii poate conține o secvență de orice comenzi SQL, inclusiv apeluri către alte comenzi proceduri stocate.
Crearea unuia nou și schimbarea unuia existent procedură stocată realizat folosind următoarea comandă:
<определение_процедуры>::= (CREATE | ALTER ) procedure_name [;număr] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]
Să ne uităm la parametrii acestei comenzi.
Folosind prefixele sp_ , # , ## , procedura creată poate fi definită ca sistem sau temporară. După cum puteți vedea din sintaxa comenzii, nu este permis să specificați numele proprietarului care va deține procedura creată, precum și numele bazei de date unde ar trebui să fie localizată. Astfel, pentru a plasa creatul procedură stocatăîntr-o anumită bază de date, trebuie să lansați comanda CREATE PROCEDURE în contextul acelei baze de date. Când te întorci de pe corp procedură stocată numele scurtate pot fi folosite pentru obiectele aceleiași baze de date, adică fără a specifica numele bazei de date. Când trebuie să accesați obiecte aflate în alte baze de date, specificarea numelui bazei de date este obligatorie.
Numărul din nume este un număr de identificare procedură stocată, care îl identifică în mod unic într-un grup de proceduri. Pentru ușurința gestionării, procedurile sunt în mod logic de același tip proceduri stocate pot fi grupate dându-le același nume, dar numere de identificare diferite.
Pentru a transfera date de intrare și de ieșire în formatul creat procedură stocată pot fi utilizați parametri, ale căror nume, ca și numele variabilelor locale, trebuie să înceapă cu simbolul @. unu procedură stocată Puteți specifica mai mulți parametri separați prin virgule. Corpul unei proceduri nu trebuie să utilizeze variabile locale ale căror nume coincid cu numele parametrilor acestei proceduri.
Pentru a determina tipul de date care corespunde parametrul procedurii stocate, toate tipurile de date SQL sunt potrivite, inclusiv cele definite de utilizator. Cu toate acestea, tipul de date CURSOR poate fi folosit doar ca parametrul de ieșire procedură stocată, adică specificând cuvântul cheie OUTPUT.
Prezența cuvântului cheie OUTPUT înseamnă că parametrul corespunzător este destinat să returneze date de la procedură stocată. Cu toate acestea, acest lucru nu înseamnă că parametrul nu este potrivit pentru transmiterea de valori către procedură stocată. Specificarea cuvântului cheie OUTPUT indică serverului să iasă procedură stocată atribuiți valoarea curentă a parametrului variabilei locale care a fost specificată la apelarea procedurii ca valoare a parametrului. 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 care sunt permise pentru parametrii obișnuiți nu sunt permise.
Cuvântul cheie VARYING este folosit împreună cu
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)
. DINAMIC RESULT SET indică faptul că procedura poate deschide un anumit număr 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 supraîncărcării rețelei și a stivei. În practică, dialectele existente ale Transact-SQL, PL/SQL și Informix au diferențe semnificative față de standard, atât în declararea și utilizarea parametrilor, declarațiile variabilelor, cât și în apelul de subrutine. Microsoft recomandă utilizarea următoarei aproximări pentru a estima dimensiunea memoriei cache a procedurii stocate:
=(numărul maxim de utilizatori concurenți)*(dimensiunea celui mai mare plan de execuție)*1.25. Determinarea dimensiunii planului de execuție în pagini se poate face cu ajutorul comenzii: DBCC MEMUSAGE.
Apelarea unei proceduri
În multe SGBD existente, procedurile stocate sunt apelate folosind operatorul:
EXECUTĂ PROCEDURA [(][)]
Notă: Apelurile către procedurile stocate pot fi efectuate dintr-o aplicație, o altă procedură stocată sau interactiv.
Exemplu de declarație de procedură
CREATE PROCEDURE Proc1 AS //declară procedura
DECLARE Cur1 CURSOR FOR SELECT SNname, City FROM SalesPeople WHERE Rating>200 //declara 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
Polimorfism
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 distinge î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 necesare se realizează în mai mulți pași:
. Inițial, toate procedurile cu numele specificat sunt definite, iar dacă nu există, atunci toate funcțiile cu numele dat.
. Doar acele subrutine pentru care utilizatorul are privilegiul EXECUTE sunt reținute pentru analize ulterioare.
. 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 al cărui nume de calificare este mai scurt.
În practică, în Oracle polimorfismul este suportat pentru funcțiile declarate doar într-un pachet, DB@ - în diferite scheme, iar în Sybase și MS SQL Server supraîncărcarea este interzisă.
Ștergerea și modificarea procedurilor
Pentru a elimina o procedură, utilizați operatorul:
Pentru a schimba o procedură, utilizați operatorul:
PROCEDURA DE ALTERARE [([{IN|OUT|INOUT}])]
ÎNCEPE [ATOMIC]
Sfârşit
Privilegii de a efectua proceduri
GRANT EXECUTE ON LA |PUBLIC [CU OPȚIUNE DE GRANT]
Proceduri de sistem
Multe SGBD-uri (inclusiv SQL Server) au un set specific de proceduri stocate în sistem încorporate pe care le puteți utiliza în propriile scopuri.
