Tallennetut menettelyt SQL:ssä | Yleiskatsaus

Tässä blogissa käsittelemme SQL:ssä tallennettuja toimenpiteitä, joita voit käyttää koodijoukon tallentamiseen ja käyttämiseen toistuvasti aina, kun tarvitset sitä. Tallennetut menettelyt ovat samanlaisia ​​kuin näkymät. Voit kuitenkin suorittaa esimerkiksi PUDOTA, TYÖNTÄ, POISTA jne. tallennetuilla toimenpiteillä, joita et voi suorittaa näkymien kanssa.

Tallennetut toiminnot on myös esikäännetty, jotta se toimii nopeammin kuin näkymät. Se myös minimoi tietokantapalvelimelle lähetettävän tiedon määrän.

Sisällysluettelo

• Syntaksi tallennetuille toimenpiteille SQL:ssä: Ilman parametreja
• Syntaksi tallennetuille toimenpiteille SQL:ssä: Parametreilla
• Esimerkki skenaario tallennetuista toimenpiteistä SSMS:ssä
• Toinen esimerkkiskenaario
• Kolmas esimerkkiskenaario
• Tallennettujen tallennettujen menettelyjen sijainti SQL:ssä
• Tallennetun proseduurin luominen SQL:ssä parametreilla
• Tallennetun toimenpiteen luominen oletusarvolla
• Johtopäätös

Syntaksi tallennetuille toimenpiteille SQL:ssä: Ilman parametreja

Jos haluat luoda tallennetun proseduurin ilman parametreja, sinun on aloitettava CREATE -toiminnolla. Lisää sitten PROC- funktio, kirjoita toimenpiteen nimi heti perään ja lisää AS- funktio.

Tallennetun toimintosarjan luominen on samanlainen kuin taulukoiden ja näkymien luominen. Ero on kuitenkin tietojen saantiprosessissa.

Jos esimerkiksi haluamme saada tietoja näkymästä, käytämme " SELECT * FROM view_name ". Toisaalta tallennetuille toimenpiteille käytämme EXEC-komentoa, joka tarkoittaa "suorita", ja kirjoita sitten tallennetun toiminnon nimi perään.

Kun suoritamme tallennetun toiminnon , se tuo esiin myös tiedot lisäämiesi ohjeiden tai komentojen perusteella.

Syntaksi tallennetuille toimenpiteille SQL:ssä: Parametreilla

Siirrytään kohtaan . Prosessi on samanlainen kuin edellinen esimerkki, jonka esitin. Ainoa ero on, että sinun on sisällytettävä parametrit ennen AS- toimintoa ja myös tietotyyppi heti sen jälkeen.

Voit myös käyttää parametriä, jonka olet ilmoittanut tallennetun toimintosarjan luomisessa komentoissasi. Näin voit säästää aikaa syöttämällä useita arvoja. Esimerkiksi sen sijaan, että kirjoitimme manuaalisesti asiakastunnus1 , asiakastunnus2 ja niin edelleen, käytimme @custid tai parametria välttääksemme pitkän koodisarjan.

Huomaa, että kun käytät tai sisällytät parametrin, on tärkeää käyttää @ -symbolia. Voit myös lisätä niin monta parametria kuin haluat, ei vain yhtä. Näin suoritamme tallennetut proseduurit parametreilla.

Kuten huomaat, käytimme myös parametria, jonka arvo on 1 , tallennetun toimintosarjan suorittamisessa. Tämä tuo vain tiedot osoitteesta custid1 .

Esimerkki skenaario tallennetuista toimenpiteistä SSMS:ssä

Otetaan lisää esimerkkejä SQL:ssä tallennetuista toimenpiteistä. Ensinnäkin aion luoda esimerkin tallennetusta menettelystä käyttämällä alla olevaa korostettua komentoa.

Tallennetun toimintosarjan luomisen nimeämiskäytännössä käytämme yleensä "usp" tai "sp" osoittamaan, että tämä on tallennettu toimintosarja. Jos mietit, mitä "usp" tarkoittaa, se tarkoittaa yksinkertaisesti käyttäjän määrittämää tallennettua menettelyä. 

Seuraavaksi kirjoitamme kyselyitä tallennetulle proseduurille, joka käsitellään, kun se suoritetaan.

Edellisen esimerkin kyselyt yksinkertaisesti poistavat taulukon nimeltä dbo.stageOrders . Sitten se luo uudelleen dbo.stageOrders- tiedoston Sales.SalesOrderHeader- taulukon tiedoilla. 

Luodaan tämä tallennettu menettely korostamalla seuraava koodi ja napsauttamalla Suorita- painiketta.

Sen jälkeen sinun pitäisi nähdä tällainen viesti.

Sitten suoritamme nyt tallennetun toiminnon suorittamalla EXEC usp_TEST .

Tallennetun usp_TEST- proseduurin suorittamisen jälkeen dbo.stageOrdersilla pitäisi nyt olla tiedot Sales.SalesOrderHeader -tiedostosta . Tarkastetaan dbo.stageOrders- tiedoston sisältö suorittamalla alla oleva komento.

Tämän seurauksena nämä ovat tiedot, jotka loimme dbo.stageOrdersissa tallennettuun toimintosarjaamme lisäämiemme komentojen perusteella.

Toinen esimerkkiskenaario

Siirrytään toiseen esimerkkiin. Tällä kertaa en halua pudottaa dbo.stageOrders- taulukkoa ja luoda samaa taulukkoa uudelleen Sales.SalesOrderHeader- taulukosta tulevilla tiedoilla.

Sen sijaan haluan vain Sales.SalesOrderHeader-taulukon tietojen näkyvän aina, kun suoritan tallennetun usp_TEST - proseduurin .

Tämän suorittamiseksi minun on tehtävä muutoksia äskettäin luotuun tallennettuun toimintosarjaan käyttämällä ALTER -käskyä.

Tässä esimerkissä käytimme ALTER -käskyä, jota käytetään tallennettujen toimintojemme muokkaamiseen. Voit käyttää tätä myös näkymissä ja taulukoissa. Kun suoritamme yllä olevan kuvakaappauksen koodin, sen pitäisi muuttaa tallennetun menettelymme käsittelemiä kyselyjä.

Tällä kertaa se ei luo pöytää uudelleen kuten teimme aiemmin. Jos suoritamme " usp_TEST ", se vain tuo tiedot Sales.SalesOrderHeader- taulukosta .

Kolmas esimerkkiskenaario

Tehdään toinen esimerkki. Tällä kertaa käytämme uudelleen dbo.stageOrders-taulukkoa, joka meillä oli ensimmäisessä esimerkissämme tallennetun proseduurin luomisesta SSMS: ään . Aloitamme tästä esimerkistä valitsemalla dbo.stageOrders-taulukon. 

Kun kaikki tiedot valitaan dbo.stageOrdersista, tulosten pitäisi olla tämän kaltaisia.

Tyhjennetään nyt dbo.stageOrders-taulukko suorittamalla usp_TEST . Voimme tehdä sen lisäämällä seuraavan komennon.

Kun olet lisännyt TRUNCATE TABLE -komennon, meidän on päivitettävä usp_TEST .

Sen jälkeen tulee näkyviin viesti, jossa sanotaan, että komennot on suoritettu onnistuneesti . Sitten suoritamme usp_TESTin uudelleen.

Kun usp_TEST suoritetaan, se näyttää kaikki Sales.SalesOrderHeader- tietueet ja tyhjentää myös dbo.stageOrders- taulukon. 

Tarkistaaksemme, onko dbo.stageOrders-taulukko tyhjä, meidän on valittava se seuraavalla komennolla ja suoritettava.

Kun yllä oleva koodi suoritetaan, näemme, että dbo.stageOrders-taulukko on nyt tyhjä. Tämä johtuu TRUNCATE TABLE -komennosta, jota olemme käyttäneet päivittääksemme usp_TEST .

Tallennettujen tallennettujen menettelyjen sijainti SQL:ssä

Jos haluat nähdä, mihin tallennettu toimintosarja tallennetaan, siirry vasemmalla olevaan Object Explorer -paneeliin ja napsauta “ + ” -kuvaketta ennen käsittelemääsi tietokantaa.

Napsauta sitten hiiren kakkospainikkeella Ohjelmoitavuus ja valitse Päivitä .

Laajenna Ohjelmoitavuus - kansiota tai ryhmää napsauttamalla “ + ” -kuvaketta. Laajenna sitten Tallennetut menettelyt -ryhmää tekemällä sama vaihe. Tallennetut menettelyt -ryhmässä sinun pitäisi nähdä dbo.usp_TEST .

Jos haluat tarkistaa, mitä komentoja tai kyselyitä tietty tallennettu SQL-toimintosarja suorittaa, voit napsauttaa tallennettua toimintosarjaa hiiren kakkospainikkeella ja seurata alla olevan kuvakaappauksen ohjeita.

Sen jälkeen se avaa tallennetun menettelyn eri välilehdellä, jossa näet siinä annetut komennot. Tältä dbo.usp_TEST näyttää avattaessa se.

Kuten näet, ennen CREATE -käskyä on joitain oletuskomentoja. Voit yksinkertaisesti poistaa sen, jos haluat. 

Nyt tiedät kuinka tarkistaa, mitä komentoja SQL:ään tallennettu toimintosarja suorittaa.

Tallennetun proseduurin luominen SQL:ssä parametreilla

Seuraavaksi aiomme luoda tallennetun proseduurin parametrein. Käytämme esimerkiksi seuraavaa koodia uuden tallennetun menettelyn luomiseen.

Esimerkkikoodissa käytin samaa prosessia, kun luotiin tallennettu toimintosarja nimeltä usp_GetCustomer . Sitten lisäsin parametrin, joka on @CustomerID ja jonka syöttötyyppi on INT .

Huomaa, että kun olet lisännyt parametrin luodessasi tallennettua toimintosarjaa, sinun tulee aina antaa parametri aina, kun aiot suorittaa komennon.

Katsotaan mitä tapahtuu, jos suoritamme usp_GetCustomerin antamatta parametria.

Kun usp_GetCustomer suoritettiin ilman parametria, näkyviin tuli virheilmoitus. Tältä se näyttäisi, jos suoritamme usp_GetCustomerin parametrilla.

Annetulla parametrilla voimme saada oikean tuloksen suorittaessamme tallennettua toimintoa .

Tallennetun toimenpiteen luominen oletusarvolla

Jos haluat välttää virheilmoituksen suoritettaessa tallennettua toimintosarjaa parametrilla, voit asettaa oletusarvon, joka toimii oletusparametrina.

Luomme esimerkiksi tallennetun proseduurin nimeltä usp_GetOrdersByYear .

Sitten aion lisätä parametrin @OrderYear , jonka syöttötyyppi on " INT " ja oletusarvo, joka on yhtä suuri kuin 2011 .

Jos suoritamme usp_GetOrdersByYear ilman parametria, se näyttää tietueet vuodelta 2011 .

Toisaalta, jos suoritamme usp_GetOrdersByYear parametrin 2014 kanssa , sen pitäisi näyttää tietueet vuodella 2014 .

Näin hyödynnät tallennettuja toimenpiteitä päivittäisissä tiedonhallintatehtävissäsi.

Johtopäätös

Kun kaikki otetaan huomioon, olet oppinut, mikä on tallennettu toimintosarja SQL:ssä ja sen tarkoitus. Olemme myös keskustelleet ALTER -käskystä, jota käytetään muutosten tai päivitysten tekemiseen nykyiseen tallennettuun toimintosarjaan.

Lisäksi olet oppinut, että SQL:ssä on erilaisia ​​menetelmiä tallennettujen toimintosarjojen luomiseen, ja olet oppinut välttämään virheiden vastaanottamisen tallennetun toiminnon suorittamisen aikana antamalla oletusarvon.

Mikä tärkeintä, olet oppinut käyttämään tallennettuja menetelmiä komentosarjojen tallentamiseen välttääksesi pitkien koodisarjojen toistuvan suorittamisen. Viimeisenä muistutuksena, älä unohda käyttää @ -symbolia parametria annettaessa.

Kaikki parhaat,

Hafiz