Kun olet lukenut tämän artikkelin, sinun tulee saada oppia perusasiat dynaaminen SQL, miten rakentaa lausuntojen perusteella muuttujan arvot, ja miten toteuttaa rakennettu lausuntoja käyttäen sp_executesql ja SUORITA() sisällä tallennettu menettely.
Kaikki esimerkkejä löytyy tämä opetus perustuu Microsoft SQL Server Management Studio ja näyte tietokantoja AdventureWorks ja WideWorldImporters. Voit aloittaa käyttämällä näitä ilmaisia työkaluja minun opas, alkaa käyttää SQL Server.,
Rakenna dynaaminen SQL tallennetussa menettelyssä.
monet SQL we write on nimenomaisesti kirjoitettu tallennettuun menettelyyn. Tätä kutsutaan staattiseksi SQL: ksi. Sellainen on nimetty, koska se ei muutu. Kun se on kirjoitettu, se tarkoittaa, että se on vasaroitu kiveksi.
Alla on esimerkki staattinen SQL:
SELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1970GROUP BY JobTitleSELECT JobTitle, Count(BusinessEntityID)FROM HumanResources.EmployeeWHERE Year(BirthDate) = 1971GROUP BY JobTitle
huomasitteko, että on olemassa kaksi lausumaa täällä? Jokainen lausunto palauttaa yhteenvedon työnhakijoista tietyn työntekijän syntymävuoden osalta. Jos haluamme lisätä syntymävuosia, meidän on lisättävä lausuntoja., Entä jos pitäisi kirjoittaa lausunto vain kerran ja pystyä vaihtamaan vuosi lennossa?
tässä kohtaa dynaaminen SQL tulee kuvaan mukaan.
Dynamic SQL on SQL, joka luodaan ja toteutetaan ajonaikaisena. Se kuulostaa monimutkaiselta, mutta se ei todellakaan ole. Sen sijaan lausunnot kirjoitetaan suoraan tallennettu menettely, SQL-lausunnot ovat ensimmäinen rakennettu ja määritelty muuttujia.
näiden muuttujien koodi suoritetaan tämän jälkeen. Nyt, jatketaan esimerkkimme, tässä on sama koodi käyttäen dynaamista SQL:
dynaaminen SQL on korostettu vihreä., Tämä on SQL, joka on rakennettu jokaiselle @birthYear. Kun SQL on rakennettu, se tallennetaan @statement. Se suoritetaan sitten käyttäen sp_executesql, jonka selitämme alla.
Johdanto sp_executesql
Voit käyttää sp_executeslq suorittaa transact SQL tallennettu muuttujaan. Ilmoitus lomake on
EXECUTE sp_executesql @statement.
jos mietit, sp_executesql on järjestelmään tallennettu menettely. Järjestelmään tallennetut menettelyt laajentavat kieltä ja tarjoavat enemmän ominaisuuksia, joita voit käyttää.,
Tässä on yksinkertainen esimerkki kokeilla:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE sp_executesql @statement
Jos et suorita kysely-ikkuna, voit saada samanlainen tulos, kuten tämä:
2018-01-24 18:49:30.143
Nyt kun olet nähnyt, miten sp_executeslq toimii, laitetaan se käytäntöön. Oletetaan, että sinua on pyydetty kirjoittamaan store menettely, joka palauttaa joko keskimääräinen LineTotal tai summa LineTotal, jonka ProductID tuotteita toimitetaan vuonna 2011.
pomosi haluaisi olla tämä kirjoitettu tallennettu menettely. Tallennetun menettelyn tulisi hyväksyä yksi parametri @ReturnAverage., Jos totta, niin palautat keskiarvon, muuten summa.
tietenkin, voit kirjoittaa sen kaksi erillistä kyselyt kuten seuraavassa tallennettu proc mutta se ei olisi hauskaa, koska se olisi liian paljon kirjoittamista ja altis virheitä!
CREATE PROCEDURE uspCalcuateSalesSummaryStatic@returnAverage bitASIF (@returnAverage = 1)BEGIN SELECT SOD.ProductID, AVG(SOD.LineTotal) as ResultAvg FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDENDELSEBEGIN SELECT SOD.ProductID, SUM(SOD.LineTotal) as ResultSum FROM Sales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHEader SOH ON SOH.SalesOrderID = SOD.SalesOrderID WHERE YEAR(SOH.ShipDate) = 2011 GROUP BY SOD.ProductIDEND
huono osa tässä on paljon päällekkäistä koodia, jonka olen värjännyt vihreäksi. Ei ole paljon ainutlaatuinen koodi, mutta että on, on värillinen punainen.
kaiken tämän irtisanomisia, meillä on loistava tilaisuus keuliminen joitakin dynaaminen SQL. Mennään!,
täällä, sen sijaan, että SQL: stä olisi kaksi täydellistä versiota, toinen AVG: lle, toinen summalle, rakennamme pyydetyn version lennolle.
SQL on rakennettu ja tallennettu muuttujaan @lausunnon. Tämä muuttuja on rakennettu parametriarvon @returnAverage perusteella. Jos asetettu 1, niin @ – funktio edustaa keskiarvoa; muussa tapauksessa summaa.
voit nähdä, missä SQL sitten rakennetaan statement. Huomaa värikoodaus. Sen pitäisi vastata samanlaisia osia staattisen version; tämän pitäisi auttaa sinua tekemään vertailun.,
virheenkorjaus dynaaminen SQL
saatat ihmetellä, miltä SQL näyttää ajonaikana. Voit helposti tarkastaa koodin avulla debuggeri:
Suorita tallennettu menettely debuggerin käyttö on ajaa komento, ja sitten Astu koodi
Jatka Vaiheeseen Osaksi koodia, ennen kuin olet lukenut Suorittaa julkilausumassa korostetaan alla.
Kun olet saavuttanut tämän lausunnon, hääriä @lausunnon, ja kun työkalun kärki on näkyvissä, valitse teksti visualizer.,
debuggeri on voimakas ja ymmärtämisen arvoinen. Jos erittäin kannustaa sinua oppimaan lisää siitä täällä.
Käyttämällä sp_executesql Parametrit
Voit käyttää sp_executesql viitata parametrit onglemista lausunto. Tämä lopulta tekee koodin helpompi lukea ja tarjoaa joitakin optimointi etuja, koska lausunto voidaan koota kerran ja käyttää uudelleen monta kertaa.
selvitys vie muodossa:
EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …
Joten katsotaanpa selittää kappaletta.
- @statement on SQL, jonka haluamme suorittaa.,
- @parameterDefinition on merkkijono, joka sisältää määritelmän kaikki parametrit viitataan @lausunnon. Jokainen parametri ja tyyppi löytyy @statement on lueteltu. Nimen ja tyypin erottaa toisistaan tila. Pilkku erottaa useita parametreja.
seuraavaksi asetamme parametriarvot määrittelemällä parametrit ja halutun arvon. Parametrit on lueteltu @parameterDefinition-merkkijonossa määritellyssä järjestyksessä.
- @parm1 on ensimmäinen parametri määritelty @parameterDefinition merkkijono. Arvo on arvo, haluat asettaa sen.,
- @parm2, on toinen parametrit, jos määritellään, kuten julisti @parameterDefinition.
- jne…
Tässä on yksinkertainen esimerkki, joka lisää kaksi numeroa, voit kokeilla:
eri osia lausunnon ovat värikoodatut:
- @lausunnon (vihreä) – huomaa, että se sisältää 2 parametrit: @ja @b. Huomaa myös, nämä eivät ole ilmoitettu TSQL. Sen sijaan ne on määritelty parametrin määritelmässä.
- @parameterDefinition (blue) – jokainen lueteltu parametri on määritelty tyypin int.
- parametriarvot (punainen) – asetamme parametrien arvon tähän.,
paketoidakseen, tässä esimerkissä meillä on dynaamisesti toteutettu SQL-lauseke, joka lisää kaksi parametria.
nämä parametrit määritellään kokonaislukuina. Jokaisen parametrin arvo on asetettu sp_executesql-komennossa.
esimerkki käyttäen sp_executesql parametreineen
otetaan edellinen esimerkki ja laajennetaan sitä. Sen sijaan, että salakuuntelisimme aluksen tiedusteluun kuten teimme, otetaan se parametriksi. Tämä tekee kyselystä joustavamman ja toimii muina vuosina kuin 2011.,
tehdäksemme tämän muutoksen lisäämme parametrin tallennettuun menettelyymme sekä dynaamiseen kyselyyn. Käytämme sp_executesql-komentoa kutsuaksemme dynaamista kyselyä näillä parametreilla.
päivitetty tallennettu menettely muutoksineen on esitetty alla. Tallennetun menettelyn parametri on vihreä ja dynaaminen kyselyparametri punainen.
Voit suorittaa tämän, yksinkertaisesti soittaa uspCalculateSalesSummaryDynamic2 proc kyselyn windows käyttämällä seuraavaa komentoa:
EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011
Jos teet niin, voit nähdä nämä tulokset.,
Anna minun näyttää sinulle yhden hyvin yksinkertaistaminen, katsotaanpa yhdistää @shipDateYear ja @shipDate osaksi yksi parametri. Poistamme @shipDateYear koodistamme. Tämä helpottaa seurata ja lukea:
Huomaa, että SUORITTAA lausunto on paljon yksinkertaisempaa, ei tarvitse määrittää SQL-lauseen parametrin @shipDateYear kauppaan menettelyn parametri @shipDate arvo.
tämä tekee lausunnosta kompaktimpi ja helppolukuisemman., Virtaus näyttää lukea paremmin, koska sinun ei tarvitse henkisesti tekemään yhteyksiä tallennettu menettely parametrit ja SQL parametrit
Suorita Dynaaminen SQL, joissa EXECUTE()
Voit myös käyttää EXEC tai SUORITA komento suorittaa dynaaminen SQL. Muoto tämä komento on
EXECUTE (@statement)
Tässä on yksinkertainen esimerkki kokeilla:
DECLARE @statement NVARCHAR(4000)SET @statement = N"SELECT getdate()"EXECUTE (@statement)
on tärkeää liittää @ilmoitus suluissa. Jos et SUORITA selvitys vie @julkilausuman, ja sen sijaan käynnissä dynaaminen SQL, se luulee, että muuttujan arvo on nimi tallennettu menettely., Saat seuraavan virheilmoituksen:
Msg 2812, Level 16, State 62, Line 3Could not find stored procedure "SELECT getdate()".
tietenkin, tämä on hyvä vihje! Jos haluat, voit määrittää muuttujien avulla, mihin tallennettuihin menettelyihin voit soittaa.
sp_executesql versus EXECUTE
saatat ihmetellä, miksi käyttää sp_executesql versus EXECUTEA. Mitä eroa niillä on?
Täällä on useita syitä, miksi Microsoft suosittelee, että käytät sp_executesql ajaa dynaaminen SQL:
- Kanssa SUORITTAA kaikki parametrit paljon muunnetaan äidinkielen tyyppi Unicode., Tämä vaikeuttaa optimizer kykyä vastata dynaamisesti rakennettu SQL valmiiksi suunnitelma.
- käyttämällä sp_executesql, optimizer tunnistaa parametrien dynaaminen SQL, jolloin se on helpompi optimizer vastaamaan suunnitelmia.
- parametrisoitujen kyselyjen lukeminen on helpompaa kuin niiden sisältämien konventoitujen tekstien lukeminen.
- Parametrisoidut kyselyt ovat vähemmän alttiita SQL-injektiohyökkäyksille.