Transact-sql, proceduri stocate
Procedura stocată - un tip special de pachet de instrucțiuni Transact-SQL create folosind SQL și extinderea limbaj procedural. Diferența majoră dintre pachetul și procedura stocată constă în aceea că acesta din urmă este stocată într-un obiect bază de date. Cu alte cuvinte, procedurile memorate sunt stocate pe partea de server pentru a îmbunătăți performanța și consecvența performanței sarcinilor repetitive.
Baza de date Engine suporta proceduri stocate și procedurile de sistem. Procedurile stocate sunt create în același mod ca și toate celelalte obiecte de baze de date, și anume cu limbajul DDL. Tratamentele sistemice sunt disponibile, iar motorul de baze de date poate fi folosit pentru a accesa informațiile din catalogul de sistem și modificările sale.
Când creați o procedură stocată, puteți defini o listă parametru opțional. Astfel, procedura va avea argumente adecvate de fiecare dată când este numit. Procedurile stocate pot returna o valoare care conține informații definite de utilizator sau, în cazul unei erori, un mesaj de eroare corespunzător.
Procedura memorată compilat anterior înainte de a fi stocat ca un obiect în baza de date. Pre-compilate formă a procedurii este stocată în baza de date și este utilizată de fiecare dată când este numit. Această proprietate oferă proceduri stocate beneficiu important este eliminarea (în aproape toate cazurile) recompiles procedurile și îmbunătățiri ale performanței corespunzătoare. Această proprietate are, de asemenea, proceduri stocate un efect pozitiv asupra cantității de date implicate în schimbul între sistemul de baze de date și aplicații. În special, pentru a apela o procedură stocată de câteva mii de octeți poate dura mai mult de 50 de octeți. Atunci când mai mulți utilizatori efectua sarcini repetitive cu utilizarea de proceduri stocate, efectul cumulat al acestor economii poate fi destul de semnificative.
Procedurile memorate pot fi de asemenea utilizate în următoarele scopuri:
pentru a crea un jurnal jurnal al acțiunilor la tabelele bazei de date.
Procedurile stocate care creează jurnalele de înregistrare și / sau tabele de citire, oferă o capacitate suplimentară pentru a asigura securitatea bazei de date. Folosind astfel de proceduri, administratorul bazei de date poate urmări modificările aduse utilizatorilor de baze de date sau aplicații.
Crearea și executarea procedurilor stocate
Procedurile stocate sunt create prin instrucțiunea CREATE PROCEDURE. care are următoarea sintaxă:
Parametrul nume_schema specifică numele schemei, care este numit de către proprietarul pentru a crea o procedură stocată. Parametrul proc_name specifică numele procedurii stocate. @ Parametrul param1 este un parametru al procedurii (argument formal), al cărui tip de date este determinat de parametrul Type1. Parametrii sunt tratamente locale în cadrul procedurii, la fel ca și variabilele locale sunt localizate în interiorul pachetului. parametrii de procedură - valorile care sunt transferate la procedura apelantului să-l folosească. Parametrul Default1 specifică valoarea implicită pentru parametrul de procesare corespunzător. (Valoarea implicită poate fi, de asemenea, NULL.)
Opțiunea de ieșire indică faptul că parametrul procedură este returnat, și poate fi folosit pentru a returna valoarea procedurii procedurii de asteptare stocate sau sistem.
EXECUTE clauză AS determină contextul de securitate în care procedura stocată după apelul trebuie să fie executat. Solicitarea acest context, cu ajutorul bazei de date Engine, puteți controla conturi de utilizator pentru a valida permisiunile pentru obiecte care se face referire prin această procedură stocată.
În mod implicit, utilizați instrucțiunile CREATE PROCEDURE Doar membrii rolul de server sysadmin fixe și rolul db_owner bază de date fixe sau db_ddladmin. Dar membrii acestor roluri pot atribui acest drept altor utilizatori folosind GRANT CREATE PROCEDURE.
Următorul exemplu arată cum să creați un proceduri simple stocate pentru lucrul cu masa de proiect:
Așa cum am menționat mai devreme, pentru a separa cele două pachete utilizate instrucțiuni GO. Instrucțiunea CREATE PROCEDURE nu pot fi combinate cu alte instrucțiuni Transact-SQL într-un singur lot. Procedura stocată IncreaseBudget crește bugetele pentru toate proiectele de un anumit număr de procente, determinat de parametrul @percent. Procedura este determinată și de valoarea implicită de interes (5), care este utilizat în cazul în care în timpul procedurii, acest argument este absent.
Procedurile memorate pot referi la o masă inexistentă. Această caracteristică vă permite să depana codul de procedură fără a crea o primă tabele relevante, chiar și fără a fi conectat la serverul terminal.
Spre deosebire de principalele proceduri stocate care sunt întotdeauna stocate în baza de date curentă, puteți crea proceduri temporare stocate, care sunt întotdeauna introduse în baza de date sistem temporar tempdb. Unul dintre motivele pentru crearea de proceduri temporare stocate pot fi dorința de a evita executia repetată a unui anumit grup de instrucțiuni atunci când este conectat la o bază de date. Puteți crea proceduri temporare locale sau globale. Pentru a face acest lucru, numele procedurii locale este specificat cu un singur caracter # (#proc_name), precum și numele de Global Action - dublu (## proc_name).
Procedura stocată local temporar poate efectua numai utilizatorul care a creat-o, și numai pentru o conexiune la baza de date în care a fost creat. Procedura temporară globală pentru toți utilizatorii, dar numai atâta timp cât acesta din urmă este completat compus în care se efectuează (de obicei, procedura de conectare, creatorul).
Ciclul de viață al procedurii stocată constă în două etape ale creației sale și executarea sa. Fiecare procedură este creată o singură dată și executat în mod repetat. O procedură stocată este efectuată de către utilizator EXECUTE de instrucțiuni care deține procedura sau are dreptul de a accesa EXECUTE această procedură. EXECUTE are următoarea sintaxă:
Cu excepția parametrului return_status, toți parametrii EXECUTE au aceeași valoare ca și logică același parametru CREATE PROCEDURE. Parametrul Return_status specifică o variabilă întreagă care stochează starea de revenire a procedurii. Parametrul valoare poate fi setată folosind fie constant (valoare), sau o variabilă locală (@variable). Ordinea parametrilor numit nu este important, dar valorile parametrilor fără nume trebuie să se acorde în ordinea în care acestea sunt definite în instrucțiunea CREATE PROCEDURE.
Clauza DEFAULT oferă o valoare implicită pentru parametrul procedură care a fost specificat în definiția procedurii. În cazul în care procedura așteaptă valoarea parametrului pentru care nu a fost stabilită o valoare prestabilită și nu există nici o opțiune, sau este specificat cuvântul cheie DEFAULT, atunci apare o eroare.
Când EXECUTE instrucțiunea este primul pachet de instrucțiuni, cuvântul cheie EXECUTE poate fi omisă. Cu toate acestea, acesta va fi mai sigur să includă acel cuvânt în fiecare pachet. Utilizarea EXECUTE instrucțiuni prezentat în exemplul de mai jos:
EXECUTE în acest exemplu execută procedura IncreaseBudget stocată care crește bugetul de 10% din proiectele.
Următorul exemplu arată cum să creați o procedură stocată pentru a procesa datele din tabele și angajaților Works_on:
Procedura ModifyEmpId în exemplul ilustrează utilizarea procedurilor stocate ca parte a procesului pentru a asigura integritatea referențială (în acest caz, între tabelele angajaților și Works_on). O astfel de procedură stocată poate fi utilizată în determinarea de declanșare, care prevede de fapt integritatea referențială.
Următorul exemplu ilustrează utilizarea unei proceduri stocate oferă rezultate:
Această procedură stocată poate fi rulat pentru a efectua folosind următoarele instrucțiuni:
Propunere cu rezultate SETURI EXECUTE
Următoarele două exemple vor ajuta pentru a explica propunerea. Primul exemplu este un exemplu introductiv, care arată modul în care pot să apară rezultatul atunci când propunerea este omisă cu rezultate SETURI:
Ieșirea procedurii stocată cauzată de o astfel de metodă este după cum urmează:
După cum se poate observa, execută o procedură stocată folosind CU SETS propuneri EXECUTE REZULTAT de instrucțiuni permite schimbarea coloanei numele și tipul de date din setul de rezultate, această procedură este scoasă la ieșire. Astfel, această nouă funcționalitate oferă o mai mare flexibilitate în executarea procedurilor stocate și punerea rezultatelor acestora într-un tabel nou.
Modificări în structura procedurilor stocate
Componenta Database Engine acceptă ALTER Procedura de modificare a structurii procedurilor stocate. ALTER instrucțiune procedură este utilizată în mod obișnuit pentru a modifica declarațiile Transact-SQL în cadrul procedurii. Toți parametrii ALTER procedura, au același înțeles ca același parametru CREATE PROCEDURE. Scopul principal al acestui manual este de a evita suprascrierea drepturilor existente ale procedurii memorate.
Pentru a elimina una sau un grup de proceduri stocate utilizate de PROCEDURA instrucțiune DROP. Procedura Ștergerea stocată poate fi doar proprietarul sau membrii rolurile predefinite db_owner și sysadmin.
procedurile memorate și CRJ
SQL Server suportă limbajul comun de execuție CLR (Common Language Runtime), care permite dezvoltarea diferitelor obiecte de baze de date (proceduri memorate, funcții definite de utilizator, declanșatoare, funcții agregate definite de utilizator și tipuri de date definite de utilizator) folosind limbaje C # si Visual Basic. CLR permite, de asemenea, aceste obiecte folosind un sistem de rulare comun.
CLR este permisă și interzisă de opțiuni de sistem de sp_configure clr_enabled. care este executat reconfigura de instrucțiuni. Următorul exemplu arată modul în care sistemul poate utiliza procedura de sp_configure permite mediului de utilizare CRJ:
Pentru a crea, compila și de a salva procedura cu ajutorul CLR este necesară pentru a efectua următoarele etape, în această ordine:
Creați o procedură stocată în C # sau Visual Basic, și apoi compilați-l folosind compilator corespunzător.
Folosind instrucțiunea CREATE ASAMBLARE. creați fișierul executabil corespunzător.
Salvați procedura ca obiect server folosind instrucțiunea CREATE PROCEDURE.
Urmați procedura folosind declarația EXECUTE.
Figura de mai jos prezintă o diagramă flux a etapelor prezentate anterior. Ceea ce urmează este o descriere mai detaliată a acestui proces.
În primul rând a crea programul dorit în orice mediu de dezvoltare, cum ar fi Visual Studio. Compilarea terminat programul în cod obiect folosind compilator C # sau Visual Basic. Acest cod este stocat într-un fișier bibliotecă de legătură dinamică (.dll), care servește ca sursă pentru instrucțiunilor de asamblare CREATE creează un cod executabil intermediar. Apoi, efectuați instrucțiunea CREATE PROCEDURE pentru a salva codul executabil sub forma unui obiect bază de date. În cele din urmă, începe procedura de executare, folosind instrucțiunea familiară EXECUTE.
Exemplul următor prezintă codul sursă pentru procedura stocată în limbajul C #:
În această procedură, o cerere este pusă în aplicare pentru a contoriza numărul de rânduri din tabelul de angajat. În utilizarea directivelor la începutul programului specificate namespace necesare pentru punerea sa în aplicare. Utilizarea acestor directive vă permite să specificați numele clasei de cod sursă, fără a specifica în mod explicit spațiul de nume corespunzător. StoredProcedures suplimentare determinate de clasă pentru care se aplică atributul SqlProcedure. care informează compilatorul că această clasă este o procedură stocată. CountEmployees interior () metoda definită de codul de clasă. Un compus cu sistemul de baze de date este stabilită prin clasa instanță SqlConnection. Pentru a deschide o conexiune, utilizați Deschidere () metoda de instanță. O metodă CreateCommand () vă permite să accesați o clasă de exemplu SqlCommnd. care difuzează SQL-comandă.
Următorul fragment de cod:
instrucțiune SELECT este folosită pentru a contoriza numărul de rânduri din tabelul de angajat și afișarea rezultatului. textul de comandă este specificat prin atribuirea proprietatea CommandText instanței cmd variabilă returnat de CreateCommand (). În continuare numit ExecuteScalar () metoda de SqlCommand instanță. Aceasta metoda returneaza o valoare scalară, care este transformată într-un întreg tip de date int, și atribuite rânduri.
Acum puteți compila codul folosind mediul Visual Studio. Am adăugat această clasă în proiectul numit CLRStoredProcedures, astfel încât Visual Studio va compila asamblarea același nume cu extensia * .dll. Următorul exemplu ilustrează următorul pas în crearea unei proceduri stocate: a crea un cod executabil. Înainte de a rula codul în acest exemplu, este necesar să se cunoască locația de fișier DLL compilat (de obicei, în localizat directorul Debug al proiectului).
CREATE instructiuni de montaj ia ca date de intrare cod gestionat și creează obiectul adecvat pentru care se poate crea un CRJ proceduri memorate, funcții definite de utilizator, și declanșează. Instrucțiunea are următoarea sintaxă:
În assembly_name parametru specifică numele ansamblului. Într-o clauză AUTORIZARE opțională specifică numele rolului ca proprietar al ansamblului. Clauza FROM specifică calea în care este încărcat ansamblul.
Cu propunerea PERMISSION_SET este o propunere foarte importantă CREATE asamblare și ar trebui să fie indicată întotdeauna. Acesta definește un set de drepturi de acces acordate de ansamblul de cod. set SAFE de drepturi este cel mai restrictiv. codul de asamblare cu aceste drepturi, nu pot avea acces la sistemul de resurse externe, cum ar fi fișierele. Drepturile EXTERNAL_ACCESS Set permite ansamblului de cod pentru a avea acces la anumite sisteme de resurse externe, precum și un set de drepturi oferă acces nelimitat nesigure la resurse, atât în interiorul cât și în afara sistemului de baze de date.
Pentru a memora informații despre ansamblul de cod, utilizatorul trebuie să fie capabil de a efectua instrucțiunea CREATE ASAMBLARE. de asamblare este deținut de către utilizator (sau rolul), executarea acestor instrucțiuni. Proprietarul ansamblului poate fi făcută dintr-un alt utilizator folosind instrucțiuni CREATE oferă SCHEMA autorizației.
Baza de date Engine sprijină, de asemenea, manual și ALTER ANSAMBLU DROP de asamblare. ALTER de asamblare este utilizată pentru a actualiza ansamblul de la cea mai recentă versiune. Această afirmație adaugă sau elimină fișierele asociate cu ansamblul respectiv. Instrucțiuni DROP ASAMBLARE elimină ansamblul specificat și toate fișierele sale asociate din baza de date curentă.
Următorul exemplu arată cum să creați o procedură stocată pe baza codului gestionat pus în aplicare anterior:
CREATE PROCEDURE în exemplul diferă de aceeași instrucțiune în exemplele de mai sus, prin aceea că acesta cuprinde un parametru EXTERNAL NAME. Acest parametru specifică faptul că codul creat de CLR. Denumiți această propunere constă din trei părți:
assembly_name - indică numele ansamblului;
class_name - indică denumirea generală de clasă;
METHOD_NAME - o parte opțională, specifică numele metodei, care este definită în cadrul clasei.
Efectuarea procedurii de CountEmployees se arată în exemplul de mai jos:
instrucțiune PRINT returnează numărul actual de rânduri din tabelul angajaților.