Pentru programarea procedurilor stocate extinse, Microsoft furnizează API-ul ODS (Open Data Service), un set de macrocomenzi și funcții utilizate pentru a construi aplicații server care vă permit să extindeți funcționalitatea MS SQL Server 2000.
Procedurile stocate extinse sunt funcții obișnuite scrise în C/C++ folosind API-ul ODS și API-ul WIN32, concepute ca o bibliotecă de link-uri dinamice (dll) și concepute, așa cum am spus deja, pentru a extinde funcționalitatea serverului SQL. API-ul ODS oferă dezvoltatorului un set bogat de funcții care vă permite să transferați date către client, primite de la orice sursă de date externă, sub forma unor seturi de înregistrări obișnuite. De asemenea, o procedură stocată extinsă poate returna valori prin parametrul care i-a fost transmis (parametrul OUTPUT).
Cum funcționează procedurile stocate extinse.
- Atunci când o aplicație client apelează o procedură stocată extinsă, cererea este transmisă în format TDS prin Serviciul Net-Libraries și Open Data către nucleul MS SQL SERVER.
- SQL Server găsește biblioteca dll asociată cu numele procedurii stocate extinse și o încarcă în contextul său, dacă nu a fost încărcată acolo înainte, și apelează procedura stocată extinsă implementată ca funcție în interiorul dll-ului.
- Procedura stocată extinsă efectuează acțiunile necesare pe server și transferă un set de rezultate către aplicația client folosind serviciul oferit de API-ul ODS.
Caracteristici ale procedurilor stocate extinse.
- Procedurile stocate extinse sunt funcții care se execută în spațiul de adrese MS SQL Server și în contextul de securitate al contului sub care rulează serviciul MS SQL Server;
- Odată ce un dll de procedură stocată extinsă a fost încărcat în memorie, acesta rămâne acolo până când SQL Server este oprit sau până când un administrator îl forțează să îl descarce folosind comanda:
DBCC DLL_name (GRATIS). - O procedură stocată extinsă este executată în același mod ca o procedură stocată obișnuită:
EXECUTE xp_extendedProcName @param1, @param2 IEȘIRE
@param1 parametru de intrare
@param2 parametru de intrare/ieșire
Deoarece procedurile stocate extinse rulează în spațiul de adrese al procesului de serviciu MS SQL Server, orice erori critice problemele care apar în funcționarea lor pot deteriora nucleul serverului, așa că este recomandat să vă testați temeinic DLL-ul înainte de a-l instala pe un server de producție.
Crearea de proceduri stocate extinse.
O procedură stocată extinsă este o funcție care are următorul prototip:
SRVRETCODE xp_extendedProcName(SRVPROC * pSrvProc);
Parametru pSrvProc Un pointer către o structură SRVPROC, care este un handle pentru fiecare conexiune client specifică. Câmpurile din această structură sunt nedocumentate și conțin informații pe care biblioteca ODS le folosește pentru a gestiona comunicațiile și datele dintre aplicația server Open Data Services și client. În orice caz, nu este nevoie să accesați această structură, cu atât mai puțin să o modificați. Acest parametru trebuie să fie specificat atunci când apelați orice funcție API ODS, așa că nu mă voi opri mai departe asupra descrierii sale.
Utilizarea prefixului xp_ este opțională, dar există o convenție pentru a începe numele unei proceduri stocate extinse în acest fel pentru a o distinge de o procedură stocată obișnuită, care, după cum știți, își începe numele cu prefixul sp_.
De asemenea, ar trebui să vă amintiți că numele de proceduri stocate extinse sunt sensibile la majuscule și minuscule. Nu uitați de acest lucru atunci când apelați o procedură stocată extinsă, altfel veți primi un mesaj de eroare în loc de rezultatul așteptat.
Dacă trebuie să scrieți codul de inițializare/deinițializare dll, utilizați funcția standard DllMain() pentru aceasta. Dacă nu aveți o astfel de nevoie și nu doriți să scrieți DLLMain(), atunci compilatorul va construi propria sa versiune a funcției DLLMain(), care nu face nimic decât pur și simplu returnează TRUE. Toate funcțiile apelate dintr-un dll (adică procedurile stocate extinse) trebuie să fie declarate ca exportabile. Dacă scrieți în MS Visual C++, utilizați directiva __declspec(dllexport). Dacă compilatorul dumneavoastră nu acceptă această directivă, descrieți funcția exportată în secțiunea EXPORTĂRI a fișierului DEF.
Deci, pentru a crea un proiect, vom avea nevoie de următoarele fișiere:
- Fișierul antet Srv.h, conține o descriere a funcțiilor și macrocomenzilor API ODS;
- Opends60.lib este un fișier de import pentru biblioteca Opends60.dll, care implementează întregul serviciu oferit de API-ul ODS.
Declspec(dllexport) ULONG __GetXpVersion()
{
returnează ODS_VERSION;
}
Când MS SQL Server încarcă un DLL cu o procedură stocată extinsă, mai întâi apelează această funcție pentru a obține informații despre versiunea bibliotecii utilizate.
Pentru a scrie prima procedură stocată extinsă, va trebui să instalați pe computer:
MS SQL Server 2000 din orice ediție (am Personal Edition). În timpul procesului de instalare, asigurați-vă că selectați opțiunea eșantion sursă
- MS Visual C++ (am folosit versiunea 7.0), dar știu sigur că 6.0 va face
Instalarea SQL Server -a este necesară pentru a testa și depana DLL-ul. Depanarea prin rețea este, de asemenea, posibilă, dar nu am făcut niciodată acest lucru, așa că am instalat totul pe mine disc local. În curs de livrare Microsoft Visual C++ 7.0 Interprise Edition include Extended Stored Procedure DLL Wizard. În principiu, nu face nimic extra natural, ci generează doar un șablon șablon pentru o procedură stocată extinsă. Dacă vă plac maeștrii, îl puteți folosi. Prefer să fac totul manual și, prin urmare, nu voi lua în considerare acest caz.
Acum la obiect:
- Lansați Visual C++ și creați proiect nou- Win32 Dynamic Link Library.
- Includeți un fișier antet în proiect - #include
- Accesați meniul Instrumente => Opțiuni și adăugați căi de căutare pentru fișierele de includere și bibliotecă. Dacă nu ați schimbat nimic la instalarea MS SQL Server, atunci specificați:
C:Fișiere de programMicrosoft SQL Server80ToolsDevToolsInclude pentru fișierele antet;
- C:Program FilesMicrosoft SQL Server80ToolsDevToolsLib pentru fișiere de bibliotecă.
- Specificați numele fișierului de bibliotecă opends60.lib în opțiunile de linker.
În acest moment, etapa pregătitoare este finalizată, puteți începe să scrieți prima procedură stocată extinsă.
Formularea problemei.
Înainte de a începe programarea, trebuie să aveți o idee clară despre unde să începeți, care ar trebui să fie rezultatul final și cum să îl obțineți. Deci, iată specificația tehnică:
Dezvoltați o procedură stocată extinsă pentru MS SQL Server 2000 care primește lista plina utilizatorii înregistrați în domeniu și îl returnează clientului sub forma unui set de înregistrări standard. Ca prim parametru de intrare, funcția primește numele serverului care conține baza de date de catalog ( Director activ), adică numele controlorului de domeniu. Dacă acest parametru este NULL, atunci o listă de grupuri locale trebuie să fie transmisă clientului. Al doilea parametru va fi utilizat de procedura stocată extinsă pentru a returna valoarea rezultatului operației cu succes/nereușit (parametrul OUTPUT). Dacă procedura stocată extinsă este finalizată cu succes, atunci este necesar să treceți numărul de înregistrări returnate la setul de înregistrări client; dacă în timpul funcționării nu a fost posibilă obținerea informațiilor necesare, valoarea celui de-al doilea parametru trebuie setată la - 1, ca semn de finalizare nereușită.
Prototipul condiționat al unei proceduri stocate extinse este următorul:
xp_GetUserList(@NameServer varchar, @CountRec int OUTPUT);
Și iată șablonul de procedură stocată extinsă pe care trebuie să-l umplem cu conținut:
#include
#include
#define XP_NOERROR 0
#define XP_ERROR -1
__declspec(dllexport) SERVRETCODE xp_GetGroupList(SRVPROC* pSrvProc)
{
//Verificarea numărului de parametri trecuți
//Verificarea tipului parametrilor trecuți
//Se verifică dacă parametrul 2 este un parametru OUTPUT
//Verifică dacă parametrul 2 este suficient de lung pentru a stoca valoarea
//Obținerea parametrilor de intrare
//Obținerea unei liste de utilizatori
// Trimiterea datelor primite către client sub forma unui set de înregistrări standard
//Setarea valorii parametrului OUTPUT
return (XP_NOERROR);
}
Lucrul cu parametrii de intrare
În acest capitol, nu vreau să vă împrăștiez atenția asupra lucrurilor străine, dar vreau să o concentrez pe lucrul cu parametrii trecuți procedurii stocate extinse. Prin urmare, vom simplifica oarecum specificațiile noastre tehnice și vom dezvolta doar acea parte a acesteia care funcționează cu parametrii de intrare. Dar mai întâi, nu prea multă teorie
Prima acțiune pe care trebuie să o efectueze procedura noastră stocată extinsă este de a primi parametrii care i-au fost transmise atunci când a fost apelată. Urmând algoritmul de mai sus, trebuie să parcurgem următorii pași:
Determinați numărul de parametri trecuți;
- Asigurați-vă că parametrii trecuți au tipul de date corect;
- Asigurați-vă că parametrul OUTPUT specificat este de lungime suficientă pentru a stoca în el valoarea returnată de procedura noastră stocată extinsă.
- Primește parametrii trecuți;
- Setați valorile parametrilor de ieșire ca urmare a finalizării cu succes/nereușite a procedurii stocate extinse.
Acum să ne uităm la fiecare punct în detaliu:
Determinarea numărului de parametri trecuți unei proceduri stocate extinse
Pentru a obține numărul de parametri trecuți, trebuie să utilizați funcția:
int srv_rpcparams(SRV_PROC * srvproc);
Dacă are succes, funcția returnează numărul de parametri trecuți procedurii stocate extinse. Dacă procedura stocată extinsă a fost apelată fără parametri, srv_rpcparams va returna -1. Parametrii pot fi transferați după nume sau după poziție (nenumite). În orice caz, aceste două metode nu trebuie amestecate. O încercare de a transmite parametrii de intrare unei funcții după nume și după poziție în același timp va avea ca rezultat o eroare, iar srv_rpcparams va returna 0.
Determinarea tipului de date și a lungimii parametrilor trecuți
Pentru a obține informații despre tipul și durata transmisiei setări Microsoft recomandă utilizarea funcției srv_paramifo. Această funcție generică înlocuiește apelurile srv_paramtype, srv_paramlen, srv_parammaxlen, care sunt acum depreciate. Iată prototipul său:
int srv_paraminfo(
SRV_PROC * srvproc,
int n,
BYTE * pbType,
ULONG* pcbMaxLen,
ULONG * pcbActualLen,
BYTE * pbData,
BOOL * pfNull);
pbType specifică numărul de serie al parametrului. Numărul primului parametru începe de la 1.
pcbMaxLen pointer către o variabilă în care funcția stochează valoarea maximă a lungimii parametrului. Această valoare se datorează tip specific date ale parametrului transmis, le vom folosi pentru a ne asigura că parametrul OUTPUT are o lungime suficientă pentru a stoca datele transmise.
pcbActualLen pointer către lungimea reală a parametrului transmis procedurii stocate extinse atunci când este apelat. Dacă parametrul transmis are lungime zero și indicatorul pfNull este setat la FALSE, atunci (* pcbActualLen) ==0.
pbData- un pointer către un buffer pentru care trebuie alocată memorie înainte de a apela srv_paraminfo. În acest buffer, funcția plasează parametrii de intrare primiți din procedura stocată extinsă. Dimensiunea tamponului în octeți este egală cu valoarea pcbMaxLen. Dacă acest parametru este setat la NULL, nu sunt scrise date în buffer, dar funcția returnează corect valorile *pbType, *pcbMaxLen, *pcbActualLen, *pfNull. Prin urmare, trebuie să apelați srv_paraminfo de două ori: mai întâi cu pbData=NULL, apoi, după ce ați alocat dimensiunea de memorie necesară pentru un buffer egal cu pcbActualLen, apelați a doua oară srv_paraminfo, trecând un pointer către blocul de memorie alocat către pbData.
pfNull pointer către un steag NULL. srv_paraminfo îl setează la TRUE dacă valoarea parametrului de intrare este NULL.
Se verifică dacă al doilea parametru IEȘIRE este un parametru.
Funcția srv_paramstatus() este concepută pentru a determina starea parametrului transmis:
int srv_paramstatus (
SRV_PROC * srvproc,
int n
);
n este numărul parametrului transmis procedurii stocate extinse atunci când este apelată. Permiteți-mi să vă reamintesc: parametrii sunt întotdeauna numerotați începând de la 1.
Pentru a returna o valoare, srv_paramstatus folosește bitul zero. Dacă este setat la 1, parametrul trecut este un parametru OUTPUT, dacă este setat la 0, este un parametru normal transmis după valoare. Dacă procedura stocată extinsă a fost apelată fără parametri, funcția va returna -1.
Setarea valorii parametrului de ieșire.
Parametrului de ieșire transmis celui stocat extins poate primi o valoare folosind funcția srv_paramsetoutput. Acest optiune nouaînlocuiește apelul la funcția srv_paramset, care este acum depreciată deoarece nu acceptă noi tipuri de date introduse în API-ul ODS și datele de lungime zero.
int srv_paramsetoutput(
SRV_PROC *srvproc,
int n,
BYTE *pbData,
ULONG cbLen,
BOOL fNull
);
pbData pointer către un buffer cu date care vor fi trimise către client pentru a seta valoarea parametrului de ieșire.
cbLen lungimea bufferului de date trimis. Dacă tipul de date al parametrului transmis către OUTPUT specifică date cu lungime constantă și nu permite stocarea unei valori NULL (de exemplu, SRVBIT sau SRVINT1), atunci funcția ignoră parametrul cbLen. cbLen=0 indică date de lungime zero, iar fNull trebuie setat la FALSE.
fNull setați acest lucru la TRUE dacă parametrul returnat trebuie setat la NULL și cbLen trebuie să fie 0 sau funcția va eșua. În toate celelalte cazuri fNull=FALSE.
Dacă are succes, funcția returnează SUCCEED. Dacă valoarea returnată este FAIL, atunci apelul a eșuat. Totul este simplu și clar
Acum știm suficient pentru a scrie prima noastră procedură stocată extinsă, care va returna o valoare prin parametrul care i-a fost transmis. Să fie, conform tradiției stabilite, acesta să fie șirul Hello world! O versiune de depanare a exemplului poate fi descărcată aici.
#include
#define XP_NOERROR 0
#define XP_ERROR 1
#define MAX_SERVER_ERROR 20000
#define XP_HELLO_ERROR MAX_SERVER_ERROR+1
void printError(SRV_PROC*, CHAR*);
#ifdef __cplusplus
extern „C” (
#endif
SRVRETCODE __declspec(dllexport) xp_helloworld(SRV_PROC* pSrvProc);
#ifdef __cplusplus
}
#endif
SRVRETCODE xp_helloworld(SRV_PROC* pSrvProc)
{
char szText = „Bună lume!”;
BYTE bType;
ULONG cbMaxLen;
ULONG cbActualLen;
BOOL fNull;
/* Determinarea numărului de transferuri către stocarea extinsă
procedura parametrilor */
dacă (srv_rpcparams(pSrvProc) != 1)
{
printError(pSrvProc, „Număr nevalid de parametri!”);
return (XP_ERROR);
}
/* Obținerea de informații despre tipul de date și lungimea parametrilor trecuți */
dacă (srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen,
&cbActualLen, NULL, &fNull) == FAIL)
{
printError(pSrvProc,
„Nu se pot obține informații despre parametrii de intrare...”);
return (XP_ERROR);
}
/* Verificați dacă parametrul OUTPUT transmis este un parametru */
if ((srv_paramstatus(pSrvProc, 1) & SRV_PARAMRETURN) == FAIL)
{
printError(pSrvProc,
„Parametrul trecut nu este un parametru OUTPUT!”);
return (XP_ERROR);
}
/* Verificați tipul de date al parametrului transmis */
if (bType != SRVBIGVARCHAR && bType != SRVBIGCHAR)
{
printError (pSrvProc, „Tipul parametrului transmis nu este corect!”);
return (XP_ERROR);
}
/* Asigurați-vă că parametrul transmis este suficient de lung pentru a stoca șirul returnat */
dacă (cbMaxLen< strlen(szText))
{
printError(pSrvProc,
„Parametrul transmis nu este de lungime suficientă pentru a stoca șirul n returnat!”);
return (XP_ERROR);
}
/* Setați valoarea parametrului OUTPUT */
dacă (FAIL == srv_paramsetoutput(pSrvProc, 1, (BYTE*)szText, 13, FALSE))
{
printError(pSrvProc,
"Nu pot seta valoarea parametrului OUTPUT...");
return (XP_ERROR);
}
srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);
return (XP_NOERROR);
}
void printError (SRV_PROC *pSrvProc, CHAR* szErrorMsg)
{
srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_HELLO_ERROR, SRV_INFO, 1,
NULL, 0, 0, szErrorMsg,SRV_NULLTERM);
Srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
}
Funcțiile srv_sendmsg și srv_senddone au rămas neconsiderate. Funcția srv_sendmsg este utilizată pentru a trimite mesaje către client. Iată prototipul său:
int srv_sendmsg (
SRV_PROC * srvproc,
int msgtype,
DBINT msgnum,
clasa DBTINYINT
stare DBTINYINT,
DBCHAR * rpcname,
int rpcnamelen,
DBUSMALLINT linenum,
DBCHAR *mesaj,
int msglen
);
numărul mesajului msgnum;
clasă- gravitatea erorii survenite. Mesaje informative au o valoare a severității mai mică sau egală cu 10;
stat Numărul de stare de eroare pentru mesajul curent. Acest parametru oferă informații despre contextul erorii care a apărut. Valorile valide variază de la 0 la 127;
rpcname este momentan neutilizat;
rpcnamelen - nu este utilizat în prezent;
linenum aici puteti specifica numarul liniei cod sursa. Pe baza acestei valori, ulterior va fi ușor de determinat unde a apărut eroarea. Dacă nu doriți să utilizați această funcție, setați linenum la 0;
indicator de mesaj către un șir trimis către client;
msglen specifică lungimea în octeți a șirului de mesaje. Dacă acest șir se termină cu un caracter nul, atunci valoarea acestui parametru poate fi setată la SRV_NULLTERM.
Valori returnate:
- daca reuseste REUSE
- dacă FAIL eșuează.
În timpul funcționării, procedura stocată extinsă trebuie să-și raporteze în mod regulat starea aplicației client, adică. trimite mesaje despre acțiunile finalizate. Pentru asta este concepută funcția srv_senddone:
int srv_senddone(
SRV_PROC * srvproc,
starea DBUSMALLINT,
informații DBUSMALLINT,
DBINT număr
);
steag de stare de stare. Valoarea acestui parametru poate fi setată folosind operatorii logici AND și OR pentru a combina constantele date în tabel:
Steagul de stare Descriere
SRV_DONE_FINAL Setul de rezultate curent este final;
SRV_DONE_MORE Setul de rezultate curent nu este final; ar trebui să fie așteptat următorul lot de date;
SRV_DONE_COUNT Parametrul de numărare conține o valoare validă
SRV_DONE_ERROR Folosit pentru a notifica când apar erori și se încheie imediat.
în rezervat, trebuie setat la 0.
count este numărul de seturi de rezultate trimise clientului. Dacă indicatorul de stare este setat la SRV_DONE_COUNT, atunci numărul trebuie să conțină numărul corect de seturi de înregistrări trimise clientului.
Valori returnate:
- daca reuseste REUSE
- dacă FAIL eșuează.
Instalarea procedurilor stocate extinse pe MS SQL Server 2000
1.Copiați biblioteca dll cu procedura stocată extinsă în directorul binn de pe mașina cu MS SQL Server instalat. Calea mea este următoarea: C:Program FilesMicrosoft SQL ServerMSSQLBinn;
2. Înregistrați procedura stocată extinsă pe server rulând următorul script:
UTILIZAȚI Master
EXECUTE SP_ADDEXTENDEDPROC xp_helloworld, xp_helloworld.dll
Testați xp_helloworld rulând următorul script:
DECLARE @Param varchar(33)
EXECUTARE xp_helloworld @Param OUTPUT
SELECTAȚI @Param AS OUTPUT_Param
Concluzie
Aceasta se încheie prima parte a articolului meu. Acum sunt sigur că ești gata să ne descurci termeni de referinta la 100%. În articolul următor vei afla:
- Tipuri de date definite în API-ul ODS;
- Caracteristici de depanare a procedurilor stocate extinse;
- Cum să creați seturi de înregistrări și să le transferați în aplicația client;
- Vom lua în considerare parțial funcțiile Active Directory Network Manegment API necesare pentru a obține o listă de utilizatori ai domeniului;
- Vom realiza un proiect finalizat (vom implementa specificatiile noastre tehnice)
Sper sa te vad curand!
PS: descărcați fișiere exemplu pentru articolul pentru Studio 7.0
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. Aceasta informatie Ne poate fi util î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 sa poate duce la Cheltuieli suplimentareși chiar un rezultat incorect dacă în baza de date se găsește o procedură cu același nume cu al tău.
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 tabelul. Î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.
- Studiați operatorii care descriu procedurile stocate și principiile transmiterii parametrilor lor de intrare și ieșire.
- Aflați cum să creați și să depanați procedurile stocate pe MS SQL Server 2000.
- Dezvoltați cinci proceduri stocate de bază pentru baza de date de instruire a bibliotecii.
- Întocmește un raport cu privire la munca depusă în format electronic.
1. Înțelegerea procedurilor stocate
Procedură stocată acesta este un set de comenzi stocate pe server și executate ca o singură unitate. Procedurile stocate sunt un mecanism prin care puteți crea rutine care rulează pe server și sunt controlate de procesele acestuia. Astfel de rutine pot fi invocate de aplicația care le apelează. Ele pot fi, de asemenea, cauzate de regulile sau declanșatoarele de integritate a datelor.
Procedurile stocate pot returna valori. Procedura poate compara valorile introduse de utilizator cu informațiile prestabilite în sistem. Procedurile stocate profită de soluțiile hardware puternice SQL Server. Acestea sunt centrate pe baze de date și lucrează îndeaproape cu optimizatorul SQL Server. Acest lucru vă permite să obțineți performanta ridicata la prelucrarea datelor.
Puteți transmite valori procedurilor stocate și puteți primi rezultate de la acestea, nu neapărat legate de foaia de lucru. O procedură stocată poate calcula rezultatele pe măsură ce rulează.
Există două tipuri de proceduri stocate: comunȘi extins. Procedurile stocate obișnuite sunt un set de comenzi Transact-SQL, în timp ce procedurile stocate extinse sunt reprezentate ca biblioteci cu legături dinamice (DLL). Astfel de proceduri, spre deosebire de cele obișnuite, au prefixul xp_. Serverul are un set standard de proceduri extinse, dar utilizatorii își pot scrie propriile proceduri în orice limbaj de programare. Principalul lucru este să utilizați interfața de programare SQL Server deschis Data Services API. Procedurile stocate extinse pot locui numai în baza de date Master.
Procedurile stocate obișnuite pot fi, de asemenea, împărțite în două tipuri: sistemicăȘi personalizat. Proceduri de sistem acestea sunt proceduri standard utilizate pentru a opera serverul; personaliza orice proceduri create de utilizator.
1.1. Beneficiile procedurilor stocate
În cel mai general caz, procedurile stocate au următoarele avantaje:
- Performanta ridicata. Este rezultatul locației procedurilor stocate pe server. Serverul, de regulă, este o mașină mai puternică, astfel încât timpul de execuție a procedurii pe server este semnificativ mai mic decât pe stație de lucru. În plus, informațiile bazei de date și procedura stocată se află pe același sistem, astfel încât este puțin timp petrecut pentru transferul înregistrărilor prin rețea. Procedurile stocate au acces direct la bazele de date, ceea ce face ca lucrul cu informații să fie foarte rapid.
- Avantajul dezvoltării unui sistem într-o arhitectură client-server. Constă în posibilitatea creării separat de software client și server. Acest avantaj este esențial în dezvoltare și poate reduce semnificativ timpul necesar pentru finalizarea unui proiect. Codul care rulează pe server poate fi dezvoltat separat de codul clientului. În acest caz, componentele serverului pot fi partajate cu componentele clientului.
- Nivel de securitate. Procedurile stocate pot acționa ca un instrument de îmbunătățire a securității. Puteți crea proceduri stocate care efectuează operațiuni de adăugare, editare, ștergere și afișare a listei, oferindu-vă control asupra fiecărui aspect al accesului la informații.
- Consolidarea regulilor serverului care lucrează cu date. Acesta este unul dintre cele mai multe motive importante aplicarea unui motor inteligent de baze de date. Procedurile stocate vă permit să aplicați reguli și alte logici care ajută la controlul informațiilor introduse în sistem.
Deși SQL este definit ca un limbaj non-procedural, SQL Server folosește cuvinte cheie legate de gestionarea fluxului de proceduri. Astfel de cuvinte cheie sunt folosite pentru a crea proceduri care pot fi salvate pentru execuție ulterioară. Procedurile stocate pot fi utilizate în locul programelor create folosind limbaje de programare standard (de exemplu, C sau Visual Basic) și efectuarea de operațiuni în baza de date date SQL Server.
Procedurile stocate sunt compilate prima dată când sunt executate și stocate într-un tabel de sistem din baza de date curentă. Ele sunt optimizate atunci când sunt compilate. Aceasta selectează cea mai bună modalitate de a accesa informațiile din tabel. Această optimizare ia în considerare poziția actuală a datelor în tabel, indici disponibili, încărcarea tabelului etc.
Procedurile stocate compilate pot îmbunătăți semnificativ performanța sistemului. Este de remarcat, totuși, că statisticile datelor din momentul creării unei proceduri până în momentul în care este executată pot deveni depășite, iar indexurile pot deveni ineficiente. Deși puteți actualiza statisticile și adăugați indici noi, mai eficienți, planul de execuție al procedurii a fost deja scris, adică procedura a fost compilată, iar ca urmare modul în care accesați datele poate să nu mai fie eficient. Prin urmare, este posibil să se recompileze proceduri de fiecare dată când sunt apelate.
Pe de altă parte, recompilarea va dura de fiecare dată. Prin urmare, problema eficacității recompilării unei proceduri sau a elaborării unui plan pentru executarea acesteia este destul de delicată și ar trebui luată în considerare pentru fiecare caz specific separat.
Procedurile stocate pot fi executate fie pe mașina locală, fie pe un sistem SQL Server la distanță. Acest lucru face posibilă activarea proceselor pe alte mașini și nu numai lucrul cu baze de date locale date, dar și cu informații pe mai multe servere.
Aplicațiile scrise într-un limbaj de nivel înalt, cum ar fi C sau Visual Basic .NET, pot apela, de asemenea, proceduri stocate, oferind o echilibrare optimă a sarcinii între partea client și software-ul server SQL.
1.2. Crearea procedurilor stocate
Pentru a crea o procedură stocată, utilizați instrucțiunea Create Procedure. Numele procedurii stocate poate avea până la 128 de caractere, inclusiv caracterele # și ##. Sintaxa definirii procedurii:
CREATE PROC nume_procedura [; număr]
[(@data_type parameter) [= default_value] ] [,...n]
LA FEL DE
<Инструкции_SQL>
Să ne uităm la parametrii acestei comenzi:
- Nume_procedură numele procedurii; trebuie să îndeplinească regulile pentru identificatori: lungimea sa nu poate depăși 128 de caractere; pentru procedurile temporare locale, numele este precedat de semnul #, iar pentru procedurile temporare globale se folosesc semnele ##;
- Număr Un număr întreg opțional utilizat pentru a grupa mai multe proceduri sub un singur nume;
- @parameter data_type o listă de nume de parametri de procedură care indică tipul de date corespunzător pentru fiecare; Pot exista până la 2100 de astfel de parametri. NULL poate fi transmis ca valoare a parametrului. Toate tipurile de date pot fi utilizate, cu excepția tipurilor text, ntext și imagine. Puteți utiliza tipul de date Cursor ca parametru de ieșire (cuvântul cheie OUTPUT sau VARYING). Parametrii cu tipul de date Cursor pot fi doar parametri de ieșire;
- Cuvânt cheie VARYING care specifică faptul că setul de rezultate este utilizat ca parametru de ieșire (utilizat numai pentru tipul Cursor);
- OUTPUT indică faptul că parametrul specificat poate fi utilizat ca ieșire;
- valoare implicită utilizat atunci când un parametru este omis la apelarea unei proceduri; trebuie să fie o constantă și poate include caractere wildcard (%, _, [, ], ^) și o valoare NULL;
- CU RECOMPILE cuvinte cheie care indică faptul că SQL Server nu va scrie planul procedurii în cache, ci îl va crea de fiecare dată când este executat;
- CU CRIPTARE cuvinte cheie care indică faptul că SQL Server va cripta procedura înainte de a o scrie în tabelul de sistem Syscomments. Pentru a face textul procedurilor criptate imposibil de recuperat, este necesar să eliminați tuplurile corespunzătoare din tabelul syscomments după criptare;
- FOR REPLICATION cuvinte cheie care indică faptul că această procedură este creată numai pentru replicare. Această opțiune nu este compatibilă cu cuvintele cheie WITH RECOMPILE;
- AS începutul definirii textului procedurii;
- <Инструкции_SQL>set de instrucțiuni SQL valide, limitate doar de dimensiunea maximă a procedurii stocate 128 KB. Următoarele declarații sunt nevalide: ALTER DATABASE, ALTER PROCEDURE, ALTER TABLE, CREATE DEFAULT, CREATE PROCEDURE, ALTER TRIGGER, ALTER VIEW, CREATE DATABASE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE VIEW, DISK INIT, DROP DATAZE ASE, DROP DEFAULT, DROP PROCEDURE, DROP RULE, DROP TRIGGER, DROP VIEW, RESTARE BAZA DE DATE, RESTORE LOG, RECONFIGARE, UPDATE STATISTICS.
Să ne uităm la un exemplu de procedură stocată. Să dezvoltăm o procedură stocată care numără și afișează numărul de copii ale cărților care se află în prezent în bibliotecă:
CREATE Procedura Count_Ex1
- procedura de numărare a numărului de exemplare ale cărților,
- în prezent în bibliotecă,
- și nu în mâinile cititorilor
La fel de
-- setați o variabilă locală temporară
Declara @N int
Selectați @N = count(*) din Exemplar unde Da_Nu = „1”
Selectați @N
MERGE
Deoarece o procedură stocată este o componentă cu drepturi depline a bazei de date, atunci, după cum ați înțeles deja, puteți crea o nouă procedură numai pentru baza de date curentă. Când lucrați în SQL Server Query Analyzer, setarea bazei de date curentă se face cu o instrucțiune Use urmată de numele bazei de date în care urmează să fie creată procedura stocată. De asemenea, puteți selecta baza de date curentă folosind lista derulantă.
După ce o procedură stocată este creată în sistem, SQL Server o compilează și verifică rutinele care sunt executate. Dacă apar probleme, procedura este respinsă. Erorile trebuie corectate înainte de retransmitere.
SQL Server 2000 folosește rezoluția de nume întârziată rezoluția numelui), deci dacă procedura stocată conține un apel către o altă procedură care nu a fost încă implementată, atunci este afișat un avertisment, dar apelul către procedura inexistentă este păstrat.
Dacă lăsați un apel către o procedură stocată dezinstalată din sistem, utilizatorul va primi un mesaj de eroare atunci când încearcă să o execute.
De asemenea, puteți crea o procedură stocată cu folosind SQL Server Enterprise Manager:
Pentru a verifica funcționalitatea procedurii stocate create, trebuie să mergeți la Query Analyzer și să rulați procedura de execuție de către operator EXEC<имя процедуры> . Rezultatele rulării procedurii create de noi sunt prezentate în Fig. 4.
Orez. 4. Rularea unei proceduri stocate în Query Analyzer
Orez. 5. Rezultatul executării procedurii fără operatorul de afișare
1.3. Parametrii procedurii stocate
Procedurile stocate sunt un instrument foarte puternic, dar eficiența maximă poate fi atinsă doar făcându-le dinamice. Dezvoltatorul trebuie să fie capabil să transmită valorile procedurii stocate cu care va lucra, adică parametrii. Mai jos sunt prezentate principiile de bază pentru utilizarea parametrilor în procedurile stocate.
- Puteți defini unul sau mai mulți parametri pentru o procedură.
- Parametrii sunt folosiți ca locuri denumite pentru stocarea datelor, la fel ca variabilele în limbaje de programare precum C, Visual Basic .NET.
- Numele parametrului trebuie să fie precedat de simbolul @.
- Numele parametrilor sunt locale pentru procedura în care sunt definite.
- Parametrii sunt utilizați pentru a transmite informații unei proceduri atunci când aceasta este executată. Vor înnebuni Linie de comanda după numele procedurii.
- Dacă o procedură are mai mulți parametri, aceștia sunt separați prin virgule.
- Pentru a determina tipul de informații transmise ca parametru, sunt utilizate tipuri de date de sistem sau utilizator.
Mai jos este definiția unei proceduri care are un parametru de intrare. Să schimbăm sarcina anterioară și să numărăm nu toate exemplarele cărților, ci doar copiile unei anumite cărți. Cărțile noastre sunt identificate în mod unic printr-un ISBN unic, așa că vom trece acest parametru procedurii. În acest caz, textul procedurii stocate se va schimba și va arăta astfel:
Creați procedura Count_Ex(@ISBN varchar(14))
La fel de
Declara @N int
Selectați @N
MERGE
Când lansăm această procedură pentru execuție, trebuie să îi transmitem valoarea parametrului de intrare (Fig. 6).
Orez. 6. Pornirea unei proceduri cu trecerea unui parametru
Pentru a crea mai multe versiuni ale aceleiași proceduri care au același nume, urmați numele de bază cu un punct și virgulă și un număr întreg. Cum să faceți acest lucru este prezentat în exemplul următor, care descrie cum să creați două proceduri cu același nume, dar cu numere diferite versiunile (1 și 2). Numărul este folosit pentru a controla ce versiune a acestei proceduri este executată. Dacă nu este specificat niciun număr de versiune, se execută prima versiune a procedurii. Această opțiune nu este afișată în exemplul anterior, dar este încă disponibilă pentru aplicația dvs.
Ambele proceduri folosesc o instrucțiune de tipărire pentru a tipări un mesaj care identifică versiunea. Prima versiune numără numărul de exemplare gratuite, iar a doua numărul de exemplare disponibile pentru o anumită carte.
Textul ambelor versiuni ale procedurilor este prezentat mai jos:
CREATE Procedura Count_Ex_all; 1
(@ISBN varchar(14))
-- procedura de numărare a exemplarelor gratuite ale unei cărți date
La fel de
Declara @N int
Selectați @N = count(*) din Exemplar unde ISBN = @ISBN și Yes_No = „1”
Selectați @N
--
MERGE
--
CREATE Procedura Count_Ex_all; 2
(@ISBN varchar(14))
-- procedura de numărare a exemplarelor gratuite ale unei cărți date
La fel de
Declara @N1 int
Selectați @N1 = count(*) din Exemplar unde ISBN = @ISBN și Yes_No = "0"
Selectați @N1
MERGE
Rezultatele efectuării procedurii cu diferite versiuni sunt prezentate în Fig. 7.
Orez. 7. Lansați rezultatele versiuni diferite aceeași procedură stocată
Când scrieți mai multe versiuni, țineți cont de următoarele restricții: Deoarece toate versiunile unei proceduri sunt compilate împreună, toate variabilele locale sunt considerate partajate. Prin urmare, dacă acest lucru este cerut de algoritmul de procesare, este necesar să folosim diferite nume de variabile interne, ceea ce am făcut prin apelarea variabilei @N în a doua procedură cu numele @N1.
Procedurile pe care le-am scris nu returnează un singur parametru, ci pur și simplu afișează numărul rezultat pe ecran. Cu toate acestea, cel mai adesea trebuie să obținem un parametru pentru procesarea ulterioară. Există mai multe moduri de a returna parametrii dintr-o procedură stocată. Cel mai simplu este să folosești operatorul RETURN. Acest operator va returna unul valoare numerica. Dar trebuie să specificăm numele sau expresia variabilei care este atribuită parametrului returnat. Următoarele sunt valorile returnate de instrucțiunea RETURN și sunt rezervate de sistem:
| Cod | Sens |
| 0 | Totul e bine |
| 1 | Obiect nu a fost găsit |
| 2 | Eroare de tip de date |
| 3 | Procesul a căzut victima unui impas |
| 4 | eroare de acces |
| 5 | Eroare de sintaxă |
| 6 | Ceva eroare |
| 7 | Eroare la resurse (fără spațiu) |
| 8 | A apărut o eroare internă recuperabilă |
| 9 | Limita sistemului a fost atinsă |
| 10 | Încălcarea incorigibilă a integrității interne |
| 11 | Aceeași |
| 12 | Distrugerea tabelului sau a indexului |
| 13 | Distrugerea bazei de date |
| 14 | Eroare hardware |
Astfel, pentru a nu contrazice sistemul, putem returna doar numere întregi pozitive prin acest parametru.
De exemplu, putem schimba textul procedurii stocate scrise anterior Count_ex după cum urmează:
Creați procedura Count_Ex2(@ISBN varchar(14))
La fel de
Declara @N int
Selectați @N = count(*) din Exemplar
Unde ISBN = @ISBN și YES_NO = „1”
-- returnează valoarea variabilei @N,
-- dacă valoarea variabilei nu este definită, returnează 0
Return Coalesce(@N, 0)
MERGE
Acum putem obține valoarea variabilei @N și o putem folosi pentru procesarea ulterioară. În acest caz, valoarea returnată este atribuită procedurii stocate în sine și, pentru a o analiza, puteți utiliza următorul format de instrucțiune de apel de procedură stocată:
Exec<переменная> = <имя_процедуры> <значение_входных_параметров>
Un exemplu de apelare a procedurii noastre este prezentat în Fig. 8.
Orez. 8. Transmiterea valorii de returnare a unei proceduri stocate unei variabile locale
Parametrii de intrare a procedurii stocate pot folosi o valoare implicită. Această valoare va fi utilizată dacă valoarea parametrului nu a fost specificată la apelarea procedurii.
Valoarea implicită este specificată folosind un semn egal după descrierea parametrului de intrare și tipul acestuia. Luați în considerare o procedură stocată care numără numărul de copii ale cărților dintr-un anumit an de publicare. Anul de lansare implicit este 2006.
CREATE PROCEDURE ex_books_now(@year int = 2006)
-- numărarea numărului de exemplare ale cărților dintr-un anumit an de publicare
LA FEL DE
Declara @N_books int
selectați @N_books = count(*) din cărți, exemplar
unde Books.ISBN = exemplar.ISBN și YEARIZD = @year
returnează coalesce(@N_books, 0)
MERGE
În fig. Figura 9 prezintă un exemplu de apelare a acestei proceduri cu și fără specificarea unui parametru de intrare.
Orez. 9. Apelarea unei proceduri stocate cu și fără parametru
Toate exemplele de mai sus de utilizare a parametrilor în procedurile stocate au furnizat doar parametrii de intrare. Cu toate acestea, parametrii pot fi de asemenea ieșiți. Aceasta înseamnă că valoarea parametrului după finalizarea procedurii va fi transmisă persoanei care a apelat această procedură (o altă procedură, declanșator, pachet de comandă etc.). Desigur, pentru a primi un parametru de ieșire, atunci când apelați, ar trebui să specificați nu o constantă, ci o variabilă ca parametru real.
Rețineți că definirea unui parametru ca parametru de ieșire într-o procedură nu vă obligă să îl utilizați ca atare. Adică, dacă specificați o constantă ca parametru real, atunci nu va apărea nicio eroare și va fi folosită ca parametru de intrare normal.
Pentru a indica faptul că un parametru este o ieșire, este utilizată instrucțiunea OUTPUT. Acest cuvânt cheie este scris după descrierea parametrului. Când descrieți parametrii procedurilor stocate, este recomandabil să specificați valorile parametrilor de ieșire după cei de intrare.
Să ne uităm la un exemplu de utilizare a parametrilor de ieșire. Să scriem o procedură stocată care, pentru o anumită carte, numără numărul total de exemplare ale acesteia din bibliotecă și numărul de exemplare gratuite. Nu vom putea folosi instrucțiunea RETURN aici, deoarece returnează doar o valoare, așa că trebuie să definim aici parametrii de ieșire. Textul procedurii stocate ar putea arăta astfel:
CREATE Procedura Count_books_all
(@ISBN varchar(14), @all int ieșire, @free int ieșire)
-- procedura de numărare a numărului total de exemplare ale unei cărți date
-- și numărul de copii gratuite
La fel de
-- numărarea numărului total de copii
Selectați @all = count(*) din Exemplar Where ISBN = @ISBN
Selectați @free = count(*) din Exemplar unde ISBN = @ISBN și Yes_No = „1”
MERGE
Un exemplu al acestei proceduri este prezentat în Fig. 10.
Orez. 10. Testarea unei proceduri stocate cu parametrii de ieșire
După cum am menționat mai devreme, pentru a obține valorile parametrilor de ieșire pentru analiză, trebuie să le setăm la variabile, iar aceste variabile trebuie descrise de operatorul Declare. Ultima declarație de ieșire ne-a permis să tipărim pur și simplu valorile rezultate pe ecran.
Parametrii procedurii pot fi chiar variabile de tip Cursor . Pentru a face acest lucru, variabila trebuie descrisă ca un tip de date special VARYING, fără a se lega de cele standard tipuri de sisteme date. În plus, trebuie indicat că aceasta este o variabilă de tip Cursor .
Să scriem o procedură simplă care afișează o listă de cărți din biblioteca noastră. Mai mult, dacă nu există mai mult de trei cărți, atunci le afișăm numele în cadrul procedurii în sine, iar dacă lista de cărți depășește numărul specificat, atunci le trecem ca cursor către programul sau modulul apelant.
Textul procedurii arată astfel:
CREATE PROCEDURA GET3TITLES
(@MYCURSOR CURSOR IEȘIRE VARIABILĂ)
-- procedura de tipărire a titlurilor cărților cu un cursor
LA FEL DE
-- definiți local variabilă de tip Cursorul în procedură
SET @MYCURSOR = CURSOR
PENTRU SELECTAREA TITLU DISTINCT
DIN CĂRȚI
-- deschide cursorul
DESCHIDE @MYCURSOR
-- descrie variabilele locale interne
DECLARE @TITLE VARCHAR(80), @CNT INT
--- setați starea inițială a contorului de cărți
SET @CNT = 0
-- mergeți la prima linie a cursorului
-- în timp ce există linii de cursor,
-- adică în timp ce trecerea la linie nouă corect
WHILE (@@FETCH_STATUS = 0) ȘI (@CNT<= 2) BEGIN
PRINT @TITLE
FETCH NEXT DE LA @MYCURSOR ÎN @TITLE
-- schimbați starea contorului de cărți
SET @CNT = @CNT + 1
Sfârşit
DACĂ @CNT = 0 PRINT „FĂRĂ CĂRȚI POTRIVITE”
MERGE
Un exemplu de apelare a acestei proceduri stocate este prezentat în Fig. unsprezece.
În procedura de apelare, cursorul trebuie declarat ca o variabilă locală. Apoi am apelat procedura și i-am transmis numele unei variabile locale de tip Cursor. Procedura a început să funcționeze și a afișat primele trei nume pe ecran, apoi a transferat controlul către procedura de apelare și a continuat să proceseze cursorul. Pentru a face acest lucru, ea a organizat o buclă While folosind variabila globală @@FETCH_STATUS, care monitorizează starea cursorului, iar apoi în buclă a afișat toate celelalte linii ale cursorului.
În fereastra de ieșire vedem o spațiere crescută între primele trei linii și titlurile ulterioare. Acest interval arată doar că controlul a fost transferat către un program extern.
Rețineți că variabila @TITLE, fiind locală procedurii, va fi distrusă când se încheie, deci este declarată din nou în blocul care apelează procedura. Crearea și deschiderea cursorului din acest exemplu are loc într-o procedură, iar închiderea, distrugerea și procesarea suplimentară sunt efectuate în blocul de comandă în care este apelată procedura.
Cel mai simplu mod de a vizualiza textul unei proceduri, de a o modifica sau de a o șterge este utilizarea interfeței grafice Enterprise Manager. Dar puteți face acest lucru și folosind proceduri speciale stocate de sistem Transact-SQL. În Transact-SQL, puteți vizualiza o definiție de procedură folosind procedura de sistem sp_helptext și puteți utiliza procedura de sistem sp_help pentru a afișa informații de control despre procedură. Procedurile de sistem sp_helptext și sp_help sunt, de asemenea, utilizate pentru a vizualiza obiectele bazei de date, cum ar fi tabelele, regulile și setările implicite.
Informațiile despre toate versiunile unei proceduri, indiferent de număr, sunt afișate imediat. Ștergerea diferitelor versiuni ale aceleiași proceduri stocate are loc, de asemenea, simultan. Următorul exemplu arată cum sunt tipărite definițiile versiunii 1 și versiunii 2 ale Count_Ex_all atunci când numele acestuia este specificat ca parametru al procedurii de sistem sp_helptext (Figura 12).
Orez. 12. Vizualizarea textului procedurii stocate utilizând o procedură stocată de sistem
Procedura de sistem SP_HELP afișează caracteristicile și parametrii procedurii create în următoarea formă:
|
Created_datetime | |||||
|
2006-12-06 23:15:01.217 | |||||
|
Scară | Param_order Collation | ||||
|
NUL | 1 | Chirilic_General_CI_AS | |||
|
0 | 2 | NUL | |||
|
0 | 3 | NUL | |||
Încercați să descifrați singuri acești parametri. Despre ce vorbesc ei?
1.4. Compilarea unei proceduri stocate
Avantajul utilizării procedurilor stocate pentru a executa un set de instrucțiuni Transact-SQL este că acestea sunt compilate prima dată când sunt executate. În timpul procesului de compilare, instrucțiunile Transact-SQL sunt convertite din reprezentarea lor simbolică originală în formă executabilă. Orice obiect accesat în procedură este, de asemenea, convertit într-o reprezentare alternativă. De exemplu, numele tabelelor sunt convertite în identificatori de obiecte, iar numele coloanelor sunt convertite în identificatori de coloane.
Planul de execuție este creat în același mod ca și pentru executarea unei singure instrucțiuni Transact-SQL. Acest plan conține, de exemplu, indecși folosiți pentru a citi rândurile din tabelele accesate prin procedură. Planul de execuție al procedurii este stocat în cache și utilizat de fiecare dată când este apelat.
Notă: Cache-ul procedurilor poate fi dimensionat pentru a conține majoritatea sau toate procedurile disponibile pentru execuție. Acest lucru va economisi timpul necesar pentru regenerarea planului de procedură.
1.5. Recopilare automată
De obicei, planul de execuție este localizat în memoria cache a procedurii. Acest lucru vă permite să creșteți performanța execuției sale. Cu toate acestea, în anumite circumstanțe, procedura este recompilată automat.
- Procedura este întotdeauna recompilată la pornirea SQL Server. Acest lucru are loc de obicei după ce sistemul de operare este repornit și prima dată când procedura este executată după creare.
- Planul de execuție al unei proceduri este întotdeauna recompilat automat dacă indexul de pe tabelul accesat de procedură este eliminat. Deoarece planul curent accesează un index care nu mai există pentru a citi rândurile de tabel, trebuie creat un nou plan de execuție. Interogările de procedură vor fi executate numai dacă este actualizată.
- Compilarea planului de execuție are loc și dacă un alt utilizator lucrează în prezent cu acest plan aflat în cache. O copie individuală a planului de execuție este creată pentru al doilea utilizator. Dacă prima copie a planului nu ar fi ocupată, nu ar fi nevoie să creați oa doua copie. Când un utilizator finalizează o procedură, planul de execuție este disponibil în cache pentru alt utilizator care are permisiunea de acces corespunzătoare.
- O procedură este recompilată automat dacă este ștearsă și recreată. Deoarece noua procedură poate diferi de versiunea veche, orice copii ale planului de execuție din cache sunt eliminate și planul este recompilat.
SQL Server se străduiește să optimizeze procedurile stocate prin memorarea în cache a celor mai utilizate proceduri. Prin urmare, vechiul plan de execuție încărcat în cache poate fi folosit în locul noului plan. Pentru a preveni această problemă, ar trebui să ștergeți și să recreați procedura stocată sau să opriți și să reporniți SQL Server. Acest lucru va șterge memoria cache a procedurii și va elimina posibilitatea de a lucra cu un plan de execuție vechi.
Procedura poate fi creată și cu opțiunea CU RECOMPILE. În acest caz, va fi recompilat automat de fiecare dată când este executat. Opțiunea WITH RECOMPILE ar trebui utilizată în cazurile în care procedura accesează tabele foarte dinamice ale căror rânduri sunt adăugate, șterse sau actualizate frecvent, deoarece acest lucru provoacă modificări semnificative la indecșii definiți pe tabele.
Dacă procedurile nu sunt recompilate automat, le puteți forța să facă acest lucru. De exemplu, dacă statisticile utilizate pentru a determina dacă un index poate fi utilizat într-o anumită interogare sunt actualizate sau dacă este creat un index nou, trebuie efectuată o recompilare forțată. Pentru a forța o recompilare, utilizați clauza WITH RECOMPILE din instrucțiunea EXECUTE:
EXECUTE procedura_nume;
LA FEL DE
<инструкции Transact-SQL>
CU RECOMPILE
Dacă procedura funcționează cu parametri care controlează ordinea în care este executată, ar trebui să utilizați opțiunea CU RECOMPILE. Dacă parametrii unei proceduri stocate pot determina cea mai bună cale pentru a o executa, se recomandă să creați un plan de execuție pe măsură ce îl rulați, mai degrabă decât să creați unul prima dată când apelați procedura pentru utilizare în toate apelurile ulterioare.
Notă: Uneori poate fi dificil să determinați dacă să utilizați opțiunea WITH RECOMPILE atunci când creați o procedură sau nu. Dacă aveți îndoieli, cel mai bine este să nu utilizați această opțiune, deoarece recompilarea procedurii de fiecare dată când este executată va pierde timp foarte valoros al procesorului. Dacă trebuie să recompilați o procedură stocată în viitor, puteți face acest lucru adăugând o clauză WITH RECOMPILE la instrucțiunea EXECUTE.
Nu puteți utiliza opțiunea WITH RECOMPILE într-o instrucțiune CREATE PROCEDURE care conține o opțiune FOR REPLICATION. Utilizați această opțiune pentru a crea o procedură care rulează în timpul procesului de replicare.
1.6. Imbricarea procedurilor stocate
Procedurile stocate pot apela alte proceduri stocate, dar există o limită a nivelului de imbricare. Nivelul maxim de imbricare este 32. Nivelul de imbricare curent poate fi determinat folosind variabila globală @@NESTLEVEL.
2. Funcții definite de utilizator (UDF)
MS SQL SERVER 2000 are multe funcții predefinite care vă permit să efectuați diverse acțiuni. Cu toate acestea, poate fi întotdeauna necesar să utilizați unele funcții specifice. Pentru a face acest lucru, începând cu versiunea 8.0 (2000), a devenit posibil să se descrie funcțiile definite de utilizator (UDF) și să le stoceze ca obiect de bază de date cu drepturi depline, împreună cu proceduri stocate, vizualizări etc.
Comoditatea utilizării funcțiilor definite de utilizator este evidentă. Spre deosebire de procedurile stocate, funcțiile pot fi încorporate direct într-o instrucțiune SELECT și pot fi utilizate atât pentru a prelua valori specifice (în clauza SELECT), cât și ca sursă de date (în clauza FROM).
Când se utilizează UDF-uri ca surse de date, avantajul lor față de vizualizări este că UDF-urile, spre deosebire de vizualizări, pot avea parametri de intrare care pot fi utilizați pentru a influența rezultatul funcției.
Funcțiile definite de utilizator pot fi de trei tipuri: funcții scalare, funcții inlineȘi funcții cu mai multe instrucțiuni care returnează un rezultat de tabel. Să aruncăm o privire mai atentă asupra tuturor acestor tipuri de funcții.
2.1. Funcții scalare
Funcțiile scalare returnează un singur rezultat scalar. Acest rezultat poate fi oricare dintre tipurile descrise mai sus, cu excepția tipurilor text, ntext, imagine și timestamp. Acesta este cel mai simplu tip de funcție. Sintaxa sa este următoarea:
RETURNS scalar_data_type
ÎNCEPE
body_function
RETURN expresie_scalar
Sfârşit
- Parametrul ENCRYPTION a fost deja descris în secțiunea privind procedurile stocate;
- SCHEMABINDING leagă o funcție la o schemă. Aceasta înseamnă că nu puteți șterge tabelele sau vederile pe care se bazează funcția fără a șterge sau modifica funcția în sine. De asemenea, nu puteți modifica structura acestor tabele dacă partea care este schimbată este utilizată de o funcție. Astfel, această opțiune vă permite să eliminați situațiile în care funcția folosește niște tabele sau vizualizări, iar cineva, fără să știe, le-a șters sau schimbat;
- RETURNS scalar_data_type descrie tipul de date pe care funcția îl returnează;
- expresie_scalar o expresie care returnează direct rezultatul unei funcții. Trebuie să fie de același tip cu cel descris după RETURNURI;
- function_body set de instrucțiuni Transact-SQL.
Să ne uităm la exemple de utilizare a funcțiilor scalare.
Creați o funcție care va selecta cel mai mic dintre două numere întregi furnizate ca intrare ca parametri.
Lăsați funcția să arate astfel:
CREATE FUNCTION min_num(@a INT, @b INT)
RETURNĂRI INT
ÎNCEPE
DECLARE @c INT
În cazul în care o< @b SET @c = @a
ELSE SET @c = @b
RETURN @c
Sfârşit
Să executăm acum această funcție:
SELECTAȚI dbo.min_num(4, 7)
Ca rezultat, obținem valoarea 4.
Puteți utiliza această funcție pentru a găsi cea mai mică dintre valorile coloanei din tabel:
SELECT min_lvl, max_lvl, min_num(min_lvl, max_lvl)
DIN Locuri de munca
Să creăm o funcție care va primi un parametru de tip datetime ca intrare și va returna data și ora corespunzătoare începutului zilei specificate. De exemplu, dacă parametrul de intrare este 09.20.03 13:31, atunci rezultatul va fi 09.20.03 00:00.
CREATE FUNCTION dbo.daybegin(@dat DATETIME)
RETURNĂ smalldatetime AS
ÎNCEPE
RETURN CONVERT(datatime, FLOOR(convert(FLOAT, @dat)))
Sfârşit
Aici funcția CONVERT efectuează conversia tipului. Mai întâi, tipul datetime este turnat în FLOAT. Cu această reducere, partea întreagă este numărul de zile care numără de la 1 ianuarie 1900, iar partea fracțională este timpul. Apoi, este rotunjit la un întreg mai mic folosind funcția FLOOR și convertit la un tip de dată-oră.
Să verificăm funcția:
SELECTAȚI dbo.daybegin(GETDATE())
Aici GETDATE() este o funcție care returnează data și ora curente.
Funcțiile anterioare au folosit doar parametrii de intrare în calculele lor. Cu toate acestea, puteți utiliza și datele stocate într-o bază de date.
Să creăm o funcție care va lua ca parametri două date: începutul și sfârșitul unui interval de timp și să calculăm venitul total din vânzări pentru acest interval. Data vânzării și cantitatea vor fi preluate din tabelul Vânzări, iar prețurile pentru titlurile vândute vor fi preluate din tabelul Titluri.
CREATE FUNCTION dbo.SumSales(@datebegin DATETIME, @dateend DATETIME)
RETUR Bani
LA FEL DE
ÎNCEPE
DECLARE @Suma de bani
SELECT @Suma = suma(t.price * s.qty)
RETURN @Suma
Sfârşit
2.2. Funcții în linie
Acest tip de funcție returnează ca rezultat nu o valoare scalară, ci un tabel, sau mai degrabă un set de date. Acest lucru poate fi foarte convenabil în cazurile în care același tip de subinterogare este adesea executat în diferite proceduri, declanșatoare etc. Apoi, în loc să scrieți această interogare peste tot, puteți crea o funcție și o puteți utiliza în viitor.
Funcțiile de acest tip sunt și mai utile în cazurile în care doriți ca tabelul returnat să depindă de parametrii de intrare. După cum știți, vizualizările nu pot avea parametri, așa că numai funcțiile inline pot rezolva acest tip de problemă.
Particularitatea funcțiilor inline este că pot conține o singură solicitare în corpul lor. Astfel, funcțiile de acest tip sunt foarte asemănătoare vizualizărilor, dar pot avea în plus parametri de intrare. Sintaxa funcției inline:
CREATE FUNCTION [proprietar.]function_name
([(@nume_parametrul tip_date_scalare [= valoare_default]) [, n]])
TABEL DE RETUR
ÎNTOARCERE [(<запрос>)]
Definiția funcției afirmă că va returna un tabel;<запрос>aceasta este cererea, al cărei rezultat va fi rezultatul funcției.
Să scriem o funcție similară cu funcția scalară din ultimul exemplu, dar returnând nu numai rezultatul însumării, ci și rândurile vânzărilor, inclusiv data vânzării, titlul cărții, prețul, numărul de bucăți și valoarea vânzării. Trebuie selectate doar acele vânzări care se încadrează într-o anumită perioadă de timp. Să criptăm textul funcției, astfel încât alți utilizatori să îl poată folosi, dar să nu îl poată citi și corecta:
CREATE FUNCTION Sales_Period (@datebegin DATETIME, @dateend DATETIME)
TABEL DE RETUR
CU CRIPTARE
LA FEL DE
ÎNTOARCERE (
SELECTează t.titlu, t.price, s.qty, ord_date, t.price * s.qty as stoim
FROM Titles t JOIN Sales s ON t.title_Id = s.Title_ID
UNDE ord_date ÎNTRE @datebegin și @dateend
)
Acum să numim această funcție. După cum sa menționat deja, poate fi apelat doar în clauza FROM a instrucțiunii SELECT:
SELECTAȚI * FROM Sales_Period("09/01/94", "09/13/94")
2.3. Funcții cu mai multe instrucțiuni care returnează un rezultat de tabel
Primul tip de funcții luate în considerare a permis utilizarea a câte instrucțiuni Transact-SQL se dorește, dar a returnat doar un rezultat scalar. Al doilea tip de funcție ar putea returna tabele, dar corpul său reprezintă o singură interogare. Funcțiile cu mai multe instrucțiuni care returnează un rezultat de tabel vă permit să combinați proprietățile primelor două funcții, adică pot conține multe instrucțiuni Transact-SQL în corp și pot returna un tabel ca rezultat. Sintaxa funcției cu mai multe instrucțiuni:
CREATE FUNCTION [proprietar.]function_name
([(@parameter_name scalar_data_type [= default_value]) [,... n]])
RETURNS @result variable_name TABLE
<описание_таблицы>
ÎNCEPE
<тело_функции>
ÎNTOARCERE
Sfârşit
- MASA<описание_таблицы> descrie structura tabelului returnat;
- <описание_таблицы> conține o listă de coloane și constrângeri.
Acum să ne uităm la un exemplu care poate fi realizat numai folosind funcții de acest tip.
Să existe un arbore de directoare și fișiere conținute în ele. Fie ca întreaga structură să fie descrisă în baza de date sub formă de tabele (Fig. 13). În esență, aici avem o structură ierarhică pentru directoare, astfel încât diagrama arată relația dintre tabelul Folders și el însuși.
Orez. 13. Structura bazei de date pentru a descrie ierarhia fișierelor și directoarelor
Acum să scriem o funcție care va lua un identificator de director ca intrare și ieșire pentru toate fișierele care sunt stocate în ea și în toate directoarele din ierarhie. De exemplu, dacă directoarele Faculty1, Faculty2 etc. sunt create în directorul Institutului, ele conțin directoare de departamente, iar fiecare director conține fișiere, atunci când specificăm identificatorul directorului Institutului ca parametru al funcției noastre, o listă cu toate fișierele pentru toate aceste directoare. Pentru fiecare fișier ar trebui să fie afișate numele, dimensiunea și data creării.
Problema nu poate fi rezolvată folosind o funcție inline, deoarece SQL nu este conceput pentru a efectua interogări ierarhice, deci o interogare SQL nu este suficientă. Nici o funcție scalară nu poate fi folosită, deoarece rezultatul trebuie să fie un tabel. Aici ne va veni în ajutor o funcție cu mai multe instrucțiuni care returnează un tabel:
CREATE FUNCTION dbo.GetFiles(@Folder_ID int)
RETURNS @files TABLE(Nume VARCHAR(100), Data_Create DATETIME, FileSize INT) AS
ÎNCEPE
DECLARE @tmp TABLE(Folder_Id int)
DECLARE @Cnt INT
INSERT INTO @tmp values(@Folder_ID)
SET @Cnt = 1
ÎN CAZUL @Cnt<>0 ÎNCEPE
INSERT INTO @tmp SELECT Folder_Id
FROM Folders f JOIN @tmp t ON f.parent=t.Folder_ID
WHERE F.id NOT IN(SELECT Folder_ID FROM @tmp)
SET @Cnt = @@ROWCOUNT
Sfârşit
INSERT INTO @Files(Nume, Data_Create, FileSize)
SELECTează F.Nume, F.Data_Creare, F.Dimensiune fișier
FROM Fișiere f JOIN Folders Fl pe f.Folder_id = Fl.id
JOIN @tmp t pe Fl.id = t.Folder_Id
ÎNTOARCERE
Sfârşit
Aici, într-o buclă, toate subdirectoarele de la toate nivelurile de imbricare sunt adăugate la variabila @tmp până când nu mai rămân subdirectoare. Variabila rezultat @Files înregistrează apoi toate atributele necesare ale fișierelor aflate în directoarele listate în variabila @tmp.
Sarcini pentru munca independentă
Trebuie să creați și să depanați cinci proceduri stocate din următoarea listă necesară:
Procedură 1. Mărirea cu o săptămână a termenului limită de depunere a exemplarelor cărții dacă data scadentă curentă se află în intervalul de la trei zile înainte de data curentă la trei zile după data curentă.
Procedura 2. Numărarea numărului de exemplare gratuite ale unei cărți date.
Procedura 3. Verificarea existenței unui cititor cu un nume de familie dat și data nașterii.
Procedura 4. Introducerea unui nou cititor, verificarea existenței acestuia în baza de date și determinarea noului său număr de card de bibliotecă.
Procedura 5. Calculul amenzilor în bani pentru cititorii debitori.
Scurtă descriere a procedurilor
Procedura 1. Creșterea termenului de livrare a cărților
Pentru fiecare înregistrare din tabelul Exemplar, se verifică dacă data scadentă a cărții se încadrează în intervalul de timp specificat. Dacă se întâmplă, data de returnare a cărții este prelungită cu o săptămână. Când efectuați procedura, trebuie să utilizați funcția pentru a lucra cu datele:
DataAdd(zi,<число добавляемых дней>, <начальная дата>)
Procedura 2. Numărarea numărului de exemplare gratuite ale unei cărți date
Parametrul de intrare al procedurii este ISBN, cifrul unic al cărții. Procedura returnează 0 (zero) dacă toate copiile acestei cărți sunt în mâinile cititorilor. Procedura returnează valoarea N, egală cu numărul de exemplare ale cărții care se află în prezent în mâinile cititorilor.
Dacă o carte cu ISBN-ul dat nu se află în bibliotecă, atunci procedura returnează 100 (minus o sută).
Procedura 3. Verificarea existenței unui cititor cu un nume de familie dat și data nașterii
Procedura returnează numărul cardului de bibliotecă dacă există un cititor cu astfel de date și 0 (zero) în caz contrar.
Când comparați data nașterii, trebuie să utilizați funcția de conversie Convert() pentru a converti data nașterii, o variabilă caracter Varchar(8) folosită ca parametru de intrare în procedură, în datatime, care este utilizată în tabelul Readers. În caz contrar, operația de comparare la căutarea unui anumit cititor nu va funcționa.
Procedura 4: Introducerea unui nou cititor
Procedura are cinci parametri de intrare și trei de ieșire.
Parametrii de intrare:
- Numele complet cu inițiale;
- Abordare;
- Data nașterii;
- Telefon fix;
- Telefonul funcționează.
Parametri de ieșire:
- numărul cardului bibliotecii;
- O indicație dacă cititorul a fost înregistrat anterior în bibliotecă (0 nu a fost, 1 a fost);
- Numărul de cărți pe care le deține un cititor.
Procedura 5. Calculul amenzilor în bani pentru cititorii debitori
Procedura funcționează cu un cursor care conține o listă cu numerele cardurilor de bibliotecă ale tuturor debitorilor. În timpul procesului de lucru, ar trebui creat un tabel temporar global ##DOLG, în care pentru fiecare debitor să fie înscrisă datoria sa totală în termeni monetari pentru toate cărțile pe care le-a deținut mai mult decât perioada de returnare. Compensația în numerar este calculată la 0,5% din prețul per carte pe zi de întârziere.
Comandă de lucru
- copii ale ecranelor (capturi de ecran) care confirmă modificările aduse bazelor de date;
- conținutul tabelelor bazei de date care sunt necesare pentru a verifica funcționarea corectă;
- textul procedurii stocate cu comentarii;
- procesul de rulare a unei proceduri stocate și de ieșire a rezultatelor lucrării.
Sarcini suplimentare
Următoarele proceduri stocate suplimentare sunt pentru lucrări individuale.
Procedura 6. Numărarea numărului de cărți pe o anumită temă care sunt disponibile în prezent în bibliotecă în cel puțin un exemplar. Tematica este transmisă ca parametru de intrare.
Procedura 7. Introducerea unei noi cărți indicând numărul de exemplare. Când introduceți copii ale unei cărți noi, asigurați-vă că introduceți numerele de acces corecte ale acestora. Gândește-te cum poți face asta. Ca o reamintire, aveți funcțiile Max și Min, care vă permit să găsiți valoarea maximă sau minimă a oricărui atribut numeric folosind o interogare Select.
Procedura 8. Formarea unui tabel cu o listă cu cititorii debitori, adică cei care ar fi trebuit să returneze cărțile la bibliotecă, dar încă nu le-au returnat. În tabelul rezultat, fiecare cititor debitor ar trebui să apară o singură dată, indiferent de câte cărți datorează. Pe lângă numele complet și numărul cardului de bibliotecă, trebuie să indicați adresa și numărul de telefon în tabelul rezultat.
Procedura 9. Căutați o copie gratuită folosind un anumit titlu de carte. Dacă există o copie gratuită, procedura returnează numărul de inventar al exemplarului; dacă nu, atunci procedura returnează o listă cu cititorii care au această carte, indicând data returnării cărții și numărul de telefon al cititorului.
Procedura 10. Afișarea unei liste de cititori care nu au în prezent nicio carte în mână. Vă rugăm să indicați numele și numărul dvs. de telefon în listă.
Procedura 11. Afișarea unei liste de cărți indicând numărul de exemplare ale unei anumite cărți din bibliotecă și numărul de exemplare gratuite în acest moment.
versiune tipărită
În Microsoft SQL Server pentru a implementa și automatiza propriile algoritmi ( calculele) puteți folosi proceduri stocate, așa că astăzi vom vorbi despre cum sunt create, modificate și șterse.
Dar mai întâi, puțină teorie pentru a înțelege ce sunt procedurile stocate și de ce sunt necesare în T-SQL.
Notă! Pentru programatorii începători, recomand următoarele materiale utile pe T-SQL:
- Pentru un studiu mai detaliat al limbajului T-SQL, recomand să citești și cartea - Calea programatorului T-SQL. Tutorial despre limbajul Transact-SQL;
- Cursuri online profesionale despre T-SQL
Ce sunt procedurile stocate în T-SQL?
Proceduri stocate– acestea sunt obiecte de bază de date care conțin un algoritm sub forma unui set de instrucțiuni SQL. Cu alte cuvinte, putem spune că procedurile stocate sunt programe din interiorul unei baze de date. Procedurile stocate sunt folosite pentru a stoca cod reutilizabil pe server, de exemplu, ați scris un anumit algoritm, calcul secvenţial sau instrucţiune SQL în mai multe etape, iar pentru a nu executa de fiecare dată toate instrucţiunile incluse în acest algoritm, îl puteți formata. ca procedură stocată. În același timp, atunci când creați o procedură SQL, serverul compilează codul și apoi, de fiecare dată când rulați această procedură SQL, serverul nu o va recompila.
Pentru a rula o procedură stocată în SQL Server, trebuie să scrieți comanda EXECUTE înaintea numelui acesteia; de asemenea, este posibil să abreviați această comandă ca EXEC. Apelarea unei proceduri stocate într-o instrucțiune SELECT, de exemplu, ca o funcție nu va mai funcționa, de exemplu. procedurile sunt lansate separat.
În procedurile stocate, spre deosebire de funcții, este deja posibilă efectuarea unor operațiuni de modificare a datelor precum: UNSERT, UPDATE, DELETE. De asemenea, puteți utiliza instrucțiuni SQL de aproape orice tip în proceduri, de exemplu, CREATE TABLE pentru a crea tabele sau EXECUTE, de exemplu. apelând la alte proceduri. Excepție fac mai multe tipuri de instrucțiuni, cum ar fi: crearea sau modificarea funcțiilor, vizualizărilor, declanșatorilor, crearea schemelor și alte câteva instrucțiuni similare, de exemplu, de asemenea, nu puteți comuta contextul conexiunii la baza de date (USE) într-o procedură stocată.
O procedură stocată poate avea parametri de intrare și parametri de ieșire, poate returna date tabelare sau nu poate returna nimic, executa doar instrucțiunile conținute în ea.
Procedurile stocate sunt foarte utile, ne ajută să automatizăm sau să simplificăm multe operațiuni, de exemplu, trebuie să generați în mod constant diverse rapoarte analitice complexe folosind tabele pivot, de ex. operator PIVOT. Pentru a facilita formularea interogărilor cu acest operator ( după cum știți, sintaxa lui PIVOT este destul de complexă), Puteți scrie o procedură care va genera în mod dinamic rapoarte rezumative pentru dvs., de exemplu, materialul „Dynamic PIVOT în T-SQL” oferă un exemplu de implementare a acestei caracteristici sub forma unei proceduri stocate.
Exemple de lucru cu proceduri stocate în Microsoft SQL Server
Date sursă pentru exemple
Toate exemplele de mai jos vor fi rulate în Microsoft SQL Server 2016 Express. Pentru a demonstra cum funcționează procedurile stocate cu date reale, avem nevoie de aceste date, să le creăm. De exemplu, să creăm un tabel de testare și să adăugăm câteva înregistrări la el, să spunem că va fi un tabel care va conține o listă de produse cu prețurile lor.
Instrucțiune pentru crearea unui tabel CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Instrucțiune pentru adăugarea datelor INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , „Mouse”, 100), (1, „Keyboard”, 200), (2, „Phone”, 400) GO --Selectează interogarea SELECT * FROM TestTable
Avem datele, acum să trecem la crearea procedurilor stocate.
Crearea unei proceduri stocate în T-SQL - instrucțiunea CREATE PROCEDURE
Procedurile stocate sunt create folosind o instrucțiune CREAȚI PROCEDURA, după această instrucțiune trebuie să scrieți numele procedurii dvs., apoi, dacă este necesar, să definiți parametrii de intrare și de ieșire între paranteze. După aceasta, scrieți cuvântul cheie AS și deschideți blocul de instrucțiuni cu cuvântul cheie BEGIN, închideți acest bloc cu cuvântul END. În interiorul acestui bloc, scrii toate instrucțiunile care implementează algoritmul tău sau un fel de calcul secvenţial, cu alte cuvinte, programezi în T-SQL.
De exemplu, să scriem o procedură stocată care va adăuga o nouă înregistrare, de exemplu. produs nou pe masa noastră de testare. Pentru a face acest lucru, vom defini trei parametri de intrare: @CategoryId – identificatorul categoriei de produse, @ProductName – numele produsului și @Price – prețul produsului; acest parametru va fi opțional, adică. nu va fi necesar să-l treci la procedură ( de exemplu, nu știm încă prețul), în acest scop vom seta o valoare implicită în definiția acesteia. Acești parametri se află în corpul procedurii, de exemplu. în blocul BEGIN...END pot fi folosite, la fel ca variabilele obișnuite ( După cum știți, variabilele sunt notate cu semnul @). Dacă trebuie să specificați parametrii de ieșire, atunci după numele parametrului indicați cuvântul cheie OUTPUT ( sau OUT pe scurt).
În blocul BEGIN...END vom scrie o instrucțiune de adăugare a datelor, precum și o instrucțiune SELECT la finalul procedurii, astfel încât procedura stocată să ne returneze date tabelare despre produsele din categoria specificată, luând în considerare contează produsul nou, tocmai adăugat. Tot în această procedură stocată am adăugat procesarea parametrului de intrare și anume eliminarea spațiilor suplimentare la începutul și la sfârșitul șirului de text pentru a elimina situațiile în care mai multe spații au fost introduse accidental.
Iată codul pentru această procedură ( Am comentat si eu).
Creați o procedură CREATE PROCEDURE TestProcedure (--Parametrii de intrare @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Instrucțiuni care implementează algoritmul dvs. --Procesarea parametrilor de intrare --Eliminarea spațiilor suplimentare la început și la sfârșitul liniei de text SET @ProductName = LTRIM(RTRIM(@ProductName)); --Adăugați o înregistrare nouă INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Return data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Rularea unei proceduri stocate în T-SQL - comanda EXECUTE
Puteți rula o procedură stocată, așa cum am observat deja, folosind comanda EXECUTE sau EXEC. Parametrii primiți sunt trecuți procedurilor prin simpla listă a acestora și specificând valorile corespunzătoare după numele procedurii ( pentru parametrii de ieșire trebuie să specificați și comanda OUTPUT). Cu toate acestea, este posibil ca numele parametrilor să nu fie specificate, dar în acest caz este necesar să se urmeze succesiunea de specificare a valorilor, adică. specificați valorile în ordinea în care sunt definiți parametrii de intrare ( acest lucru se aplică și parametrilor de ieșire).
Parametrii care au valori implicite nu trebuie să fie specificați; aceștia sunt așa-numiții parametri opționali.
Iată câteva moduri diferite, dar echivalente, de a rula procedurile stocate, în special procedura noastră de testare.
1. Apelați procedura fără a specifica prețul EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. Apelați procedura indicând prețul EXEC TestProcedure @CategoryId = 1, @ProductName = „Testați produsul 2”, @Price = 300 --3. Apelați procedura fără a specifica numele parametrilor EXEC TestProcedure 1, "Test product 3", 400
Schimbarea unei proceduri stocate în instrucțiunea T-SQL - ALTER PROCEDURE
Puteți face modificări ale algoritmului procedurii folosind instrucțiunile PROCEDURA DE ALTERARE. Cu alte cuvinte, pentru a schimba o procedură deja existentă, trebuie doar să scrieți ALTER PROCEDURE în loc de CREATE PROCEDURE și să modificați orice altceva după cum este necesar.
Să presupunem că trebuie să facem modificări procedurii noastre de testare, să spunem parametrul @Price, de exemplu. preț, îl vom face obligatoriu, pentru aceasta vom elimina valoarea implicită și, de asemenea, ne imaginăm că nu mai trebuie să obținem setul de date rezultat, pentru aceasta vom elimina pur și simplu instrucțiunea SELECT din procedura stocată.
Schimbăm procedura ALTER PROCEDURE TestProcedure (--Parametrii de intrare @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Instrucțiuni care implementează algoritmul dvs. --Procesarea parametrilor de intrare --Eliminarea spațiilor suplimentare la început și sfârșitul liniilor de text SET @ProductName = LTRIM(RTRIM(@ProductName)); --Adăugați o înregistrare nouă INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO
Ștergerea unei proceduri stocate în instrucțiunea T-SQL - DROP PROCEDURE
Dacă este necesar, puteți șterge procedura stocată; acest lucru se face folosind instrucțiunile PROCEDURA DE RĂDARE.
De exemplu, să ștergem procedura de testare creată de noi.
DROP PROCEDURE TestProcedure
La ștergerea procedurilor stocate, merită să ne amintim că, dacă procedura este referită prin alte proceduri sau instrucțiuni SQL, după ce va fi ștearsă, acestea vor eșua cu o eroare, deoarece procedura la care se referă nu mai există.
Atât am, sper că materialul ți-a fost interesant și util, pa!
