Proceduri stocate în SQL | O imagine de ansamblu

În acest blog, vom discuta despre procedurile stocate în SQL pe care le puteți utiliza pentru a salva un set de cod și a-l folosi în mod repetat ori de câte ori aveți nevoie. Procedurile stocate sunt similare vizualizărilor. Cu toate acestea, puteți efectua operațiuni comune pe tabel , cum ar fi DROP, TRUNCATE, DELETE etc. cu proceduri stocate pe care nu le puteți efectua cu vizualizări.

Procedurile stocate sunt, de asemenea, pre-compilate, astfel încât să ruleze mai repede decât vizualizările. De asemenea, minimizează cantitatea de date trimisă către serverul bazei de date.

Cuprins

• Sintaxă pentru procedurile stocate în SQL: fără parametri
• Sintaxă pentru procedurile stocate în SQL: cu parametri
• Exemplu de scenariu de proceduri stocate în SSMS
• Al doilea exemplu de scenariu
• Al treilea exemplu de scenariu
• Locația procedurilor stocate salvate în SQL
• Crearea unei proceduri stocate în SQL cu parametri
• Crearea unei proceduri stocate cu o valoare implicită
• Concluzie

Sintaxă pentru procedurile stocate în SQL: fără parametri

Pentru a crea o procedură stocată fără parametri, trebuie să începeți cu funcția CREATE . Apoi adăugați funcția PROC , introduceți numele procedurii imediat după și adăugați funcția AS .

Crearea unei proceduri stocate în este similară cu crearea de tabele și vederi. Cu toate acestea, diferența este în procesul de obținere a datelor.

De exemplu, dacă dorim să obținem date dintr-o vizualizare, folosim „ SELECT * FROM view_name ”. Pe de altă parte, pentru procedurile stocate, folosim EXEC care înseamnă „execută”, apoi introducem numele procedurii stocate după.

Odată ce executăm o procedură stocată , aceasta va afișa și informațiile bazate pe instrucțiunile sau comenzile pe care le-ați adăugat.

Sintaxă pentru procedurile stocate în SQL: cu parametri

Să trecem la . Procesul este similar cu exemplul anterior pe care l-am demonstrat. Singura diferență este că trebuie să includeți parametrii înaintea funcției AS și, de asemenea, tipul de date imediat după.

De asemenea, puteți utiliza parametrul pe care l-ați declarat la crearea procedurii stocate în comenzile dvs. Acest lucru este pentru a economisi timp introducând mai multe valori. De exemplu, în loc să introducem manual customerID1 , customerID2 și așa mai departe, am folosit @custid sau parametrul pentru a evita un set lung de cod.

Vă rugăm să rețineți că atunci când utilizați sau includeți un parametru, este important să folosiți simbolul @ . De asemenea, puteți include oricât de mulți parametri doriți, nu doar unul. Așa executăm procedurile stocate cu parametri.

După cum puteți observa, am folosit și parametrul cu o valoare egală cu 1 în executarea procedurii stocate. Aceasta va aduce doar informațiile despre custid1 .

Exemplu de scenariu de proceduri stocate în SSMS

Să avem mai multe exemple de proceduri stocate în SQL. În primul rând, voi crea un exemplu de procedură stocată utilizând comanda evidențiată de mai jos.

Pentru convenția de denumire în crearea unei proceduri stocate, folosim de obicei „usp” sau „sp” pentru a indica faptul că aceasta este o procedură stocată. Dacă vă întrebați ce înseamnă „usp”, pur și simplu înseamnă procedura stocată definită de utilizator. 

În continuare, vom scrie interogări pentru ca procedura stocată să fie procesată atunci când este executată.

Interogările din exemplul anterior vor elimina pur și simplu tabelul numit dbo.stageOrders . Apoi va recrea dbo.stageOrders cu datele din tabelul Sales.SalesOrderHeader . 

Să creăm această procedură stocată evidențiind următorul cod și făcând clic pe butonul Execute .

După aceea, ar trebui să vedeți un mesaj ca acesta.

Apoi, acum vom executa procedura stocată prin rularea EXEC usp_TEST .

La executarea procedurii stocate usp_TEST , dbo.stageOrders ar trebui să aibă acum datele din Sales.SalesOrderHeader . Să verificăm conținutul dbo.stageOrders rulând comanda de mai jos.

Ca rezultat, acestea sunt datele pe care le-am creat în dbo.stageOrders pe baza comenzilor pe care le-am adăugat la procedura noastră stocată.

Al doilea exemplu de scenariu

Să trecem la un alt exemplu. De data aceasta nu vreau să arunc tabelul dbo.stageOrders și să recreez același tabel cu date care provin din tabelul Sales.SalesOrderHeader .

În schimb, vreau doar ca datele din tabelul Sales.SalesOrderHeader să fie afișate ori de câte ori execut procedura stocată „ usp_TEST ” .

Pentru a realiza acest lucru, trebuie să fac modificări la procedura stocată recent creată folosind instrucțiunea ALTER .

În acest exemplu, am folosit instrucțiunea ALTER care este folosită pentru a modifica procedura noastră stocată. Puteți aplica acest lucru și pentru vizualizări și tabele. Odată ce rulăm codul din captura de ecran de mai sus, ar trebui să schimbe interogările care urmează să fie procesate de procedura noastră stocată.

De data aceasta nu va recrea nicio masă așa cum am făcut-o mai devreme. Dacă executăm „ usp_TEST ”, acesta va aduce doar datele din tabelul Sales.SalesOrderHeader .

Al treilea exemplu de scenariu

Să facem un alt exemplu. De data aceasta, vom reutiliza tabelul dbo.stageOrders pe care l-am avut în primul nostru exemplu de creare a unei proceduri stocate în SSMS . Vom începe cu acest exemplu selectând tabelul dbo.stageOrders. 

Rezultatele la selectarea tuturor datelor din dbo.stageOrders ar trebui să fie așa.

Acum să golim tabelul dbo.stageOrders executând usp_TEST . Putem face asta adăugând următoarea comandă.

După adăugarea comenzii TRUNCATE TABLE , trebuie să actualizăm usp_TEST .

După aceea, ar trebui să fie afișat un mesaj care spune că Comenzile au fost finalizate cu succes . Apoi vom executa din nou usp_TEST .

La executarea usp_TEST , va afișa toate înregistrările din Sales.SalesOrderHeader și, de asemenea, va goli tabelul dbo.stageOrders . 

Pentru a verifica dacă tabelul dbo.stageOrders este gol, trebuie să îl selectăm folosind următoarea comandă și să executăm.

La rularea codului de mai sus, putem vedea că tabelul dbo.stageOrders este acum gol. Acest lucru se datorează comenzii TRUNCATE TABLE pe care am folosit-o pentru a actualiza usp_TEST .

Locația procedurilor stocate salvate în SQL

Dacă doriți să vedeți unde este salvată procedura stocată în SQL , mergeți la panoul Object Explorer din partea stângă și faceți clic pe pictograma „ + ” dinaintea bazei de date la care lucrați.

Apoi faceți clic dreapta pe Programabilitate și selectați Reîmprospătare .

Extindeți folderul sau grupul de programabilitate făcând clic pe pictograma „ + ”. Apoi extindeți grupul de proceduri stocate făcând același pas. În cadrul grupului de proceduri stocate, ar trebui să vedeți dbo.usp_TEST .

Dacă doriți să verificați ce comenzi sau interogări efectuează o anumită procedură stocată în SQL, puteți face clic dreapta pe o procedură stocată și urmați pașii din captura de ecran de mai jos.

După aceea, va deschide procedura stocată într-o filă diferită, unde puteți vedea comenzile furnizate în ea. Așa arată dbo.usp_TEST la deschidere.

După cum puteți vedea, există câteva comenzi implicite înainte de instrucțiunea CREATE . Puteți șterge pur și simplu asta dacă doriți. 

Acum știți cum să verificați ce comenzi execută o procedură stocată în SQL .

Crearea unei proceduri stocate în SQL cu parametri

În continuare, vom crea o procedură stocată cu parametri. De exemplu, vom folosi următorul cod pentru a crea o nouă procedură stocată.

În exemplul de cod, am folosit același proces de creare a unei proceduri stocate numită usp_GetCustomer . Apoi am adăugat un parametru care este @CustomerID cu un tip de intrare INT .

Rețineți că, odată ce ați adăugat un parametru în crearea unei proceduri stocate, ar trebui să furnizați întotdeauna un parametru ori de câte ori veți executa o comandă.

Să vedem ce se va întâmpla dacă executăm usp_GetCustomer fără a furniza un parametru.

La executarea usp_GetCustomer fără un parametru, a apărut un mesaj de eroare. Așa ar arăta dacă executăm usp_GetCustomer cu un parametru.

Cu un parametru furnizat, putem obține un rezultat adecvat în executarea procedurii noastre stocate .

Crearea unei proceduri stocate cu o valoare implicită

Dacă doriți să evitați primirea unui mesaj de eroare la executarea unei proceduri stocate cu un parametru, puteți seta o valoare implicită care va servi ca parametru implicit.

De exemplu, vom crea o procedură stocată numită usp_GetOrdersByYear .

Apoi voi adăuga un parametru @OrderYear cu tipul de intrare „ INT ” și o valoare implicită egală cu 2011 .

Dacă executăm usp_GetOrdersByYear fără un parametru furnizat, va afișa înregistrările cu anul 2011 .

Pe de altă parte, dacă executăm usp_GetOrdersByYear cu 2014 ca parametru dat, ar trebui să afișeze înregistrările cu anul 2014 .

Acesta este modul în care utilizați procedurile stocate în sarcinile zilnice de gestionare a datelor.

Clauza HAVING în funcțiile de agregare SQL
ISNULL Funcția SQL în instrucțiunile caz
Extragerea datelor SQL folosind OFFSET și FETCH

Concluzie

Luând în considerare totul, ați învățat ce este o procedură stocată în SQL și scopul acesteia. Am discutat, de asemenea, declarația ALTER care este utilizată pentru a face modificări sau actualizări într-o procedură stocată actuală.

În plus, ați învățat că există diferite metode de a crea proceduri stocate în SQL și ați învățat cum să evitați primirea de erori atunci când executați o procedură stocată prin furnizarea unei valori implicite.

Cel mai important, ați învățat să utilizați procedurile stocate pentru stocarea seturi de comenzi pentru a evita rularea seturi lungi de cod în mod repetat. Ca o ultimă reamintire, nu uitați să utilizați simbolul „ @ ” atunci când furnizați un parametru.

Toate cele bune,

Hafiz