Salvestatud protseduur - andmebaasiobjekt, mis on SQL-i käskude komplekt, mis kompileeritakse üks kord ja salvestatakse serverisse. Salvestatud protseduurid on väga sarnased tavaliste kõrgetasemeliste keeleprotseduuridega, neil võivad olla sisend- ja väljundparameetrid ning lokaalsed muutujad, nendega saab sooritada märgiandmetega numbrilisi arvutusi ja toiminguid, mille tulemusi saab omistada muutujatele ja parameetritele. Salvestatud protseduurid võivad teha standardseid andmebaasitoiminguid (nii DDL kui ka DML). Lisaks võimaldavad salvestatud protseduurid silmuseid ja harusid, see tähendab, et nad saavad täitmisprotsessi juhtimiseks kasutada juhiseid.
Salvestatud protseduurid on sarnased kasutaja määratletud funktsioonidega (UDF). Peamine erinevus seisneb selles, et kasutaja määratud funktsioone saab kasutada nagu kõiki teisi SQL-lause avaldisi, samas kui salvestatud protseduure tuleb kutsuda funktsiooni CALL abil:
HELISTAMISE protseduur (…)
LÄBI protseduur (…)
Salvestatud protseduurid võivad tagastada mitu tulemust, st SELECT päringu tulemused. Selliseid tulemuste komplekte saab töödelda kursorite, muude salvestatud protseduuride, mis tagastavad tulemuskomplekti osuti, või rakenduste abil. Salvestatud protseduurid võivad sisaldada ka deklareeritud muutujaid andmete ja kursorite töötlemiseks, mis võimaldavad teil tabelis mitme rea vahel ringi käia. SQL-standard pakub töötamiseks IF, LOOP, REPEAT, CASE ja paljusid muid funktsioone. Salvestatud protseduurid võivad aktsepteerida muutujaid, tagastada tulemusi või muuta muutujaid ja tagastada need olenevalt sellest, kus muutuja on deklareeritud.
Salvestatud protseduuride rakendamine on DBMS-i lõikes erinev. Enamik suuremaid andmebaasimüüjaid toetab neid ühel või teisel kujul. Olenevalt DBMS-ist saab salvestatud protseduure realiseerida erinevates programmeerimiskeeltes, nagu SQL, Java, C või C++. Salvestatud protseduurid, mis ei ole SQL-is kirjutatud, võivad SQL-päringuid iseseisvalt täita, kuid ei pruugi.
Taga
Loogika jagamine teiste rakendustega. Salvestatud protseduurid kapseldavad funktsionaalsust; see tagab ühenduvuse andmetele juurdepääsuks ja erinevate rakenduste haldamiseks.
Kasutajate isoleerimine andmebaasi tabelitest. See võimaldab teil anda juurdepääsu salvestatud protseduuridele, kuid mitte tabeliandmetele endale.
Pakub kaitsemehhanismi. Nagu eelmises punktis, kui pääsete andmetele juurde ainult salvestatud protseduuride kaudu, ei saa keegi teine teie andmeid käsu SQL DELETE kaudu kustutada.
Täiustatud täitmine vähenenud võrguliikluse tõttu. Salvestatud protseduure kasutades saab mitut päringut kombineerida.
Vastu
Andmebaasiserveri suurem koormus, kuna suurem osa tööst tehakse serveri poolel ja vähem kliendi poolel.
Peate palju õppima. Salvestatud protseduuride kirjutamiseks peate õppima MySQL-i avaldise süntaksi.
Te dubleerite oma rakendusloogikat kahes kohas: serveri koodis ja salvestatud protseduuride koodis, muutes sellega andmetega manipuleerimise protsessi keerulisemaks.
Üleminek ühest DBMS-ist teise (DB2, SQL Server jne) võib põhjustada probleeme.
Salvestatud protseduuride eesmärk ja eelised
Salvestatud protseduurid parandavad jõudlust, täiustavad programmeerimisvõimalusi ja toetavad andmeturbe funktsioone.
Sageli kasutatava päringu salvestamise asemel saavad kliendid viidata vastavale salvestatud protseduurile. Salvestatud protseduuri kutsumisel töötleb server kohe selle sisu.
Lisaks päringu tegelikule täitmisele võimaldavad salvestatud protseduurid teha ka arvutusi ja manipuleerida andmetega – muuta, kustutada, täita DDL-lauseid (mitte kõigis DBMS-ides!) ja kutsuda teisi salvestatud protseduure ning teostada keerulist tehinguloogikat. Üksik avaldus võimaldab teil helistada salvestatud protseduuris sisalduvale keerulisele skriptile, vältides sadade käskude saatmist üle võrgu ja eriti vajadust edastada kliendilt serverisse suuri andmemahtusid.
Enamikus DBMS-ides kompileeritakse salvestatud protseduuri esmakordsel käivitamisel (parsitakse ja luuakse andmetele juurdepääsuplaan). Tulevikus on selle töötlemine kiirem. Oracle DBMS tõlgendab andmesõnastikku salvestatud protseduurikoodi. Alates Oracle 10g-st toetatakse C-vormingus salvestatud protseduurikoodi nn natiivset kompileerimist ja seejärel sihtmasina masinkoodi, misjärel käivitatakse salvestatud protseduuri kutsumisel otse selle kompileeritud objektikood.
Programmeerimisvõimalused
Loodud salvestatud protseduuri saab igal ajal välja kutsuda, mis tagab modulaarsuse ja soodustab koodi taaskasutamist. Viimane muudab andmebaasi haldamise lihtsamaks, kuna see muutub ärireeglite muutumisest isoleerituks. Saate salvestatud protseduuri vastavalt uutele reeglitele igal ajal muuta. Pärast seda hakkavad kõik seda kasutavad rakendused automaatselt ilma otsese muutmiseta vastavusse uute ärireeglitega.
Ohutus
Salvestatud protseduuride kasutamine võimaldab piirata või täielikult välistada kasutajate otsejuurdepääsu andmebaasi tabelitele, jättes kasutajatele ainult õigused teostada salvestatud protseduure, mis tagavad kaudse ja rangelt reguleeritud juurdepääsu andmetele. Lisaks toetavad mõned DBMS-id salvestatud protseduuride teksti krüptimist (ümbristamist).
Need turvafunktsioonid võimaldavad andmebaasi struktuuri kasutajast eraldada, tagades andmebaasi terviklikkuse ja töökindluse.
Toimingute, näiteks SQL-i süstimise tõenäosus väheneb, kuna hästi kirjutatud salvestatud protseduurid kontrollivad lisaks sisendparameetreid enne päringu edastamist DBMS-ile.
Salvestatud protseduuride rakendamine
Salvestatud protseduurid luuakse tavaliselt SQL-keele või selle konkreetse teostuse abil valitud DBMS-is. Näiteks on neil eesmärkidel Microsoft SQL Serveri DBMS-is Transact-SQL keel, Oracle'is - PL/SQL, InterBase'is ja Firebirdis - PSQL, PostgreSQLis - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 keeles - SQL/PL (inglise keeles), Informixis - SPL. MySQL järgib üsna täpselt SQL:2003 standardit, selle keel on sarnane SQL/PL-ga.
Mõned DBMS-id võimaldavad kasutada mis tahes programmeerimiskeeles kirjutatud salvestatud protseduure, mis võivad luua sõltumatuid täitmisfaile, näiteks C++ või Delphi. Microsoft SQL Serveri terminoloogias nimetatakse selliseid protseduure laiendatud salvestatud protseduurideks ja need on lihtsalt funktsioonid, mis sisalduvad Win32 DLL-is. Ja näiteks Interbase'is ja Firebirdis on DLL/SO-st kutsutud funktsioonidel erinev nimi – UDF (User Defined Function). MS SQL 2005 tõi sisse võimaluse kirjutada salvestatud protseduure mis tahes .NET-i keeles ning laiendatud salvestatud protseduuridest on plaanis tulevikus loobuda. Oracle DBMS omakorda võimaldab salvestatud protseduure kirjutada Java keeles. IBM DB2-s on salvestatud protseduuride ja funktsioonide kirjutamine tavapärastes programmeerimiskeeltes traditsiooniline viis, mida toetatakse algusest peale, ja SQL-i protseduurilaiend lisati sellele DBMS-ile alles üsna hilistes versioonides, pärast selle lisamist ANSI standardisse. Informix toetab ka Java ja C protseduure.
Oracle DBMS-is saab salvestatud protseduure kombineerida nn pakettideks. Pakett koosneb kahest osast - paketi spetsifikatsioonist, mis määrab salvestatud protseduuri määratluse, ja paketi kehast, mis sisaldab selle teostust. Seega võimaldab Oracle eraldada programmi koodi liidese selle rakendamisest.
IBM DB2 DBMS-is saab salvestatud protseduure kombineerida mooduliteks.
Süntaks
LOO PROTSESS `p2`()
SQL TURVAMÄÄRAJA
KOMMENTAAR "Protseduur"
VALI "Tere maailm!";
Koodi esimene osa loob salvestatud protseduuri. Järgmine sisaldab valikulisi parameetreid. Siis tuleb nimi ja lõpuks protseduuri keha ise.
Salvestatud protseduuri 4 omadust:
Keel: teisaldatavuse huvides on vaikimisi SQL.
Deterministlik: kui protseduur tagastab alati sama tulemuse ja võtab samad sisendparameetrid. See on mõeldud paljundamiseks ja registreerimiseks. Vaikeväärtus EI OLE DETERMINISTILINE.
SQL-turvalisus: kõne ajal kontrollitakse kasutajaõigusi. INVOKER on kasutaja, kes kutsub salvestatud protseduuri. DEFINER on protseduuri "looja". Vaikeväärtus on DEFINER.
Kommentaar: dokumenteerimise eesmärgil on vaikeväärtus ""
Salvestatud protseduuri kutsumine
CALL salvestatud_protseduuri_nimi (param1, param2, ....)
CALL-protseduur1(10 , "stringi parameeter" , @parameetri_var);
Salvestatud protseduuri muutmine
MySQL-il on protseduuride muutmiseks käsk ALTER PROCEDURE, kuid see sobib ainult teatud omaduste muutmiseks. Kui teil on vaja muuta protseduuri parameetreid või keha, peaksite selle kustutama ja uuesti looma.
Eemaldusladustatudprotseduurid
KÜLGITAMISE PROTSESS, KUI OLEMAS OLEMAS p2;
See on lihtne käsk. Avaldus IF EXISTS tuvastab vea, kui sellist protseduuri pole.
Valikud
CREATE PROCEDURE proc1(): tühi parameetrite loend
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): üks sisendparameeter. Sõna IN on valikuline, kuna vaikeparameetrid on IN (in).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): tagastati üks parameeter.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): üks parameeter, nii sisend kui ka tagastus.
Muutujate deklaratsiooni süntaks näeb välja selline:
DECLARE varname DATA-TYPE DEFAULT vaikeväärtus;
SQL-i salvestatud protseduurid on käivitatavad programmimoodulid, mida saab salvestada erinevate objektide kujul. Teisisõnu, see on objekt, mis sisaldab SQL-lauseid. Neid salvestatud protseduure saab hea jõudluse saavutamiseks käivitada rakenduskliendis. Lisaks kutsutakse selliseid objekte sageli teistest skriptidest või isegi mõnest muust jaotisest.
Sissejuhatus
Paljud inimesed usuvad, et need on sarnased erinevate protseduuridega (vastavalt, välja arvatud MS SQL). Võib-olla on see tõsi. Neil on sarnased parameetrid ja need võivad anda sarnaseid väärtusi. Pealegi puudutavad nad mõnel juhul. Näiteks kombineeritakse neid DDL- ja DML-andmebaasidega, aga ka kasutajafunktsioonidega (koodnimega UDF).
Tegelikkuses on SQL-i salvestatud protseduuridel palju eeliseid, mis eristavad neid sarnastest protsessidest. Turvalisus, programmeerimise paindlikkus, tootlikkus – kõik see meelitab üha rohkem andmebaasidega töötavaid kasutajaid. Protseduuride populaarsuse tipphetk saavutati aastatel 2005–2010, mil ilmus Microsofti programm nimega “SQL Server Management Studio”. Tema abiga on andmebaasidega töötamine muutunud palju lihtsamaks, praktilisemaks ja mugavamaks. Aasta-aastalt kogus see programmeerijate seas populaarsust. Tänaseks on see täiesti tuttav programm, mis andmebaasidega "suhtlevate" kasutajate jaoks on Exceliga võrdne.
Kui protseduur kutsutakse, töötleb server seda koheselt ilma tarbetute protsesside või kasutaja sekkumiseta. Pärast seda saate teostada mis tahes kustutamist, käivitamist või muutmist. Kõige selle eest vastutab DDL-i operaator, kes üksi teostab objektide töötlemiseks kõige keerulisemaid toiminguid. Pealegi toimub see kõik väga kiiresti ja serverit tegelikult ei laadita. Selline kiirus ja jõudlus võimaldab väga kiiresti edastada suurel hulgal infot kasutajalt serverisse ja vastupidi.
Selle teabega töötamise tehnoloogia rakendamiseks on mitu programmeerimiskeelt. Nende hulka kuuluvad näiteks PL/SQL Oracle'ilt, PSQL InterBase'i ja Firebird süsteemides, aga ka klassikaline Microsoft Transact-SQL. Kõik need on mõeldud salvestatud protseduuride loomiseks ja täitmiseks, mis võimaldab suurtel andmebaasiprotsessoritel kasutada oma algoritme. See on vajalik ka selleks, et sellise teabe haldajad saaksid kaitsta kõiki objekte kolmandate isikute volitamata juurdepääsu ja vastavalt teatud andmete loomise, muutmise või kustutamise eest.
Tootlikkus
Neid andmebaasiobjekte saab programmeerida mitmel viisil. See võimaldab kasutajatel valida kõige sobivama kasutatava meetodi tüübi, säästes vaeva ja aega. Lisaks töödeldakse protseduuri ise, mis väldib serveri ja kasutaja vahelisele suhtlusele kuluvat tohutut aega. Samuti saab moodulit absoluutselt igal ajal ümber programmeerida ja soovitud suunas muuta. Eriti tasub tähele panna kiirust, millega SQL-i salvestatud protseduur käivitatakse: see protsess toimub kiiremini kui teised sellega sarnased, mis teeb selle mugavaks ja universaalseks.
Ohutus
Seda tüüpi teabetöötlus erineb sarnastest protsessidest selle poolest, et see tagab suurema turvalisuse. Selle tagab asjaolu, et teiste kasutajate juurdepääsu protseduuridele saab täielikult välistada. See võimaldab administraatoril nendega iseseisvalt toiminguid teha, kartmata teabe pealtkuulamist või volitamata juurdepääsu andmebaasile.
Andmete ülekanne
SQL-i salvestatud protseduuri ja klientrakenduse vaheline seos on parameetrite ja tagastusväärtuste kasutamine. Viimane ei pea andmeid salvestatud protseduurile edastama, vaid seda infot (peamiselt kasutaja soovil) töödeldakse SQL jaoks. Kui salvestatud protseduur on oma töö lõpetanud, saadab see andmepaketid tagasi (kuid valikuliselt uuesti) seda kutsunud rakendusele, kasutades erinevaid meetodeid, mida saab kasutada SQL-i salvestatud protseduuri kutsumiseks või tagastamiseks, näiteks:
Andmete edastamine parameetri Output type abil;
Andmete edastamine tagastusoperaatori abil;
Andmete edastamine operaatori valimisega.
Nüüd mõtleme välja, kuidas see protsess seestpoolt välja näeb.
1. Looge SQL-is salvestatud EXEC-protseduur
Protseduuri saate luua MS SQL-is (Managment Studio). Pärast protseduuri loomist loetletakse see andmebaasi programmeeritavas sõlmes, milles operaator teostab loomise protseduuri. Käivitamiseks kasutavad SQL-i salvestatud protseduurid EXEC-protsessi, mis sisaldab objekti enda nime.
Protseduuri loomisel kuvatakse kõigepealt selle nimi, millele järgneb üks või mitu sellele määratud parameetrit. Parameetrid võivad olla valikulised. Pärast parameetri(te) ehk protseduuri põhiosa kirjutamist tuleb teha mõned vajalikud toimingud.
Asi on selles, et kehas võivad paikneda lokaalsed muutujad ja need muutujad on ka protseduuride suhtes lokaalsed. Teisisõnu saab neid vaadata ainult Microsoft SQL Serveri protseduuri põhiosas. Salvestatud protseduure peetakse sel juhul kohalikeks.
Seega vajame protseduuri loomiseks protseduuri nime ja vähemalt ühte parameetrit protseduuri kehaks. Pange tähele, et sellisel juhul on suurepärane võimalus luua ja käivitada protseduur klassifikaatoris oleva skeemi nimega.
Protseduuri põhiosa võib olla mis tahes tüüpi, näiteks tabeli loomine, tabeli ühe või mitme rea sisestamine, andmebaasi tüübi ja olemuse kindlaksmääramine jne. Menetlusorgan piirab aga teatud toimingute tegemist enda sees. Mõned olulised piirangud on loetletud allpool:
Keha ei tohiks luua muid salvestatud protseduuri;
Keha ei tohiks jätta objektist vale muljet;
Keha ei tohiks tekitada käivitajaid.
2. Muutuja määramine protseduuri kehas
Muutujad saate määrata protseduuri põhiosale ja seejärel asuvad need ainult protseduuri põhiosas. Hea tava on luua muutujad salvestatud protseduuri keha alguses. Kuid saate määrata ka muutujaid antud objekti kehas kõikjal.
Mõnikord võite märgata, et ühele reale on seatud mitu muutujat ja iga muutuja parameeter on eraldatud komaga. Pange tähele ka seda, et muutuja ees on @. Protseduuri põhiosas saate määrata muutuja kuhu iganes soovite. Näiteks võib muutuja @NAME1 deklareerida protseduuri keha lõpu lähedal. Deklareeritud muutujale väärtuse määramiseks kasutatakse isikuandmete komplekti. Erinevalt olukorrast, kus samal real deklareeritakse rohkem kui üks muutuja, kasutatakse selles olukorras ainult ühte isikuandmete komplekti.
Kasutajad küsivad sageli küsimust: "Kuidas määrata protseduuri kehas ühes lauses mitu väärtust?" Noh. See on huvitav küsimus, kuid seda on palju lihtsam teha, kui arvate. Vastus: kasutades paare, näiteks "Select Var = väärtus". Neid paare saate kasutada, eraldades need komaga.
Erinevad näited näitavad, kuidas inimesed loovad lihtsa salvestatud protseduuri ja täidavad seda. Protseduur võib aga aktsepteerida selliseid parameetreid, et seda kutsuval protsessil on väärtused sellele lähedased (kuid mitte alati). Kui need langevad kokku, algavad kehas vastavad protsessid. Näiteks kui loote protseduuri, mis võtab helistajalt linna ja piirkonna vastu ning tagastab andmed selle kohta, kui palju autoreid vastavasse linna ja piirkonda kuulub. Protseduur küsib selle autoriloenduse tegemiseks andmebaasi autoritabeleid, näiteks Pubs. Näiteks nende andmebaaside hankimiseks laadib Google SQL2005 lehelt alla SQL-skripti.
Eelmises näites kasutab protseduur kahte parameetrit, mida inglise keeles nimetatakse tavapäraselt @State ja @City. Andmetüüp ühtib rakenduses määratletud tüübiga. Protseduuri kehas on sisemised muutujad @TotalAuthors ja seda muutujat kasutatakse autorite arvu kuvamiseks. Järgmisena tuleb päringu valiku sektsioon, mis arvutab kõik välja. Lõpuks prinditakse välja arvutatud väärtus printlause abil väljundaknas.
Kuidas käivitada SQL-is salvestatud protseduuri
Protseduuri läbiviimiseks on kaks võimalust. Esimene viis näitab parameetrite edastamisega, kuidas komadega eraldatud loend käivitatakse pärast protseduuri nime. Oletame, et meil on kaks väärtust (nagu eelmises näites). Need väärtused kogutakse protseduuride parameetrite @State ja @City abil. Selle parameetrite edastamise meetodi puhul on järjekord oluline. Seda meetodit nimetatakse järguargumendi edastamiseks. Teise meetodi puhul on parameetrid juba otse määratud ja sel juhul pole järjekord oluline. Seda teist meetodit nimetatakse nimega argumentide edastamiseks.
Protseduur võib tavapärasest veidi erineda. Kõik on sama, mis eelmises näites, kuid ainult siin nihutatakse parameetreid. See tähendab, et esmalt salvestatakse parameeter @City ja vaikeväärtuse kõrvale @State. Vaikeparameeter tõstetakse tavaliselt eraldi esile. SQL-i salvestatud protseduurid edastatakse lihtsalt parameetritena. Sellisel juhul asendab parameeter "UT" vaikeväärtust "CA". Teisel käivitamisel edastatakse parameetri @City jaoks ainult üks argumendi väärtus ja parameeter @State võtab vaikeväärtuse "CA". Kogenud programmeerijad soovitavad, et kõik muutujad peaksid asuma vaikimisi parameetrite loendi lõpus. Vastasel juhul pole täitmine võimalik ja siis peate töötama nimeliste argumentide edastamisega, mis on pikem ja keerulisem.
4. SQL Serveri salvestatud protseduurid: tagastamismeetodid
Nimetatud salvestatud protseduuris andmete saatmiseks on kolm olulist viisi. Need on loetletud allpool:
Tagastab salvestatud protseduuri väärtuse;
Salvestatud protseduuriparameetrite väljund;
Ühe salvestatud protseduuride valimine.
4.1 Väärtuste tagastamine SQL-i salvestatud protseduuridest
Selle tehnika puhul määrab protseduur kohalikule muutujale väärtuse ja tagastab selle. Protseduur võib ka otse konstantse väärtuse tagastada. Järgmises näites lõime protseduuri, mis tagastab autorite koguarvu. Kui võrdlete seda protseduuri eelmistega, näete, et prindiväärtus on vastupidine.
Nüüd vaatame, kuidas protseduuri teostada ja selle tagastusväärtust printida. Protseduuri teostamine nõuab muutuja seadistamist ja printimist, mis tehakse pärast kogu seda protsessi. Pange tähele, et print-lause asemel saate kasutada Select-lauset, näiteks Select @RetValue ja OutputValue.
4.2 SQL-i salvestatud protseduuri parameetri väljund
Vastuse väärtust saab kasutada ühe muutuja tagastamiseks, mida nägime eelmises näites. Väljundparameetri kasutamine võimaldab protseduuril saata helistajale üks või mitu muutuja väärtust. Väljundparameeter määratakse protseduuri loomisel täpselt selle märksõnaga “Output”. Kui parameeter on antud väljundparameetrina, siis protseduuriobjekt peab määrama sellele väärtuse. SQL-i salvestatud protseduurid, mille näiteid näete allpool, tagastage sel juhul kokkuvõtliku teabega.
Meie näites on kaks väljundnime: @TotalAuthors ja @TotalNoContract. Need on näidatud parameetrite loendis. Need muutujad määravad väärtused protseduuri põhiosas. Kui kasutame väljundparameetreid, näeb helistaja protseduuri põhiosas seatud väärtust.
Samuti deklareeritakse eelmises stsenaariumis kaks muutujat, et näha väärtusi, mille MS SQL Server salvestas protseduurid väljundparameetris. Seejärel viiakse protseduur läbi, sisestades parameetri “CA” normaalväärtuse. Järgmised parameetrid on väljundparameetrid ja seega edastatakse deklareeritud muutujad määratud järjekorras. Pange tähele, et muutujate edastamisel määratakse siin ka väljundmärksõna. Pärast protseduuri edukat lõpetamist kuvatakse teatekastis väljundparameetrite poolt tagastatud väärtused.
4.3 Ühe SQL-i salvestatud protseduuride valimine
Seda tehnikat kasutatakse väärtuste komplekti tagastamiseks andmetabelina (RecordSet) kutsuvale salvestatud protseduurile. Selles näites esitab @AuthID parameetritega SQL-i salvestatud protseduur päringu tabelist Autorid, filtreerides selle @AuthId parameetri abil tagastatud kirjed. Valiku avaldus otsustab, mis tuleks salvestatud protseduuri helistajale tagastada. Kui salvestatud protseduur on täidetud, edastatakse AuthId tagasi. See protseduur tagastab alati ainult ühe kirje või mitte ühtegi. Kuid salvestatud protseduuril ei ole piiranguid rohkem kui ühe kirje tagastamiseks. Harvad on näited, kus andmed tagastatakse valitud parameetrite abil, mis hõlmavad arvutatud muutujaid, esitades mitu kogusummat.
Lõpuks
Salvestatud protseduur on üsna tõsine programmimoodul, mis tagastab või läbib ning määrab tänu klientrakendusele ka vajalikud muutujad. Kuna salvestatud protseduur töötab serveris ise, saab vältida tohutut andmevahetust serveri ja klientrakenduse vahel (mõnede arvutuste jaoks). See võimaldab vähendada SQL-serverite koormust, millest on loomulikult kasu nende omanikele. Üks alatüüpe on T SQL-i salvestatud protseduurid, kuid nende uurimine on vajalik neile, kes loovad muljetavaldavaid andmebaase. Samuti on suur, isegi tohutu hulk nüansse, mis võivad olla kasulikud salvestatud protseduuride uurimisel, kuid seda on rohkem vaja neile, kes plaanivad programmeerimisega tegeleda, sealhulgas professionaalselt.
Salvestatud protseduur on eritüüpi Transact-SQL-i lausepakett, mis on loodud SQL-keele ja protseduurilaiendite abil. Peamine erinevus paketi ja salvestatud protseduuri vahel on see, et viimane salvestatakse andmebaasiobjektina. Teisisõnu salvestatakse salvestatud protseduurid serveri poolele, et parandada kordatavate ülesannete jõudlust ja järjepidevust.
Andmebaasimootor toetab salvestatud protseduure ja süsteemiprotseduure. Salvestatud protseduurid luuakse samamoodi nagu kõik teised andmebaasiobjektid, s.t. kasutades DDL keelt. Süsteemi protseduurid neid pakub andmebaasimootor ja neid saab kasutada süsteemikataloogi teabele juurdepääsuks ja selle muutmiseks.
Kui loote salvestatud protseduuri, saate määratleda valikulise parameetrite loendi. Nii aktsepteerib protseduur sobivaid argumente iga kord, kui seda kutsutakse. Salvestatud protseduurid võivad tagastada väärtuse, mis sisaldab kasutaja määratud teavet või tõrke korral sobiva veateate.
Salvestatud protseduur kompileeritakse enne selle objektina andmebaasi salvestamist. Protseduuri eelkompileeritud vorm salvestatakse andmebaasi ja seda kasutatakse iga kord, kui see välja kutsutakse. See salvestatud protseduuride omadus annab olulise eelise (peaaegu kõikidel juhtudel) korduvate protseduuride koostamise kõrvaldamiseks ja vastavate jõudluse täiustuste saavutamiseks. Sellel salvestatud protseduuride omadusel on positiivne mõju ka andmebaasisüsteemi ja rakenduste vahelisele andmevahetusele. Eelkõige võib mitme tuhande baiti suuruse salvestatud protseduuri kutsumine nõuda vähem kui 50 baiti. Kui mitu kasutajat täidavad korduvaid toiminguid, kasutades salvestatud protseduure, võib nende säästude kumulatiivne mõju olla üsna märkimisväärne.
Salvestatud protseduure saab kasutada ka järgmistel eesmärkidel:
andmebaasi tabelitega toimingute logi loomiseks.
Salvestatud protseduuride kasutamine tagab turbekontrolli taseme, mis ületab oluliselt GRANT- ja REVOKE-avalduste turvalisuse, mis annavad kasutajatele erinevad juurdepääsuõigused. See on võimalik, kuna volitus salvestatud protseduuri täitmiseks ei sõltu volitusest muuta salvestatud protseduuris sisalduvaid objekte, nagu on kirjeldatud järgmises jaotises.
Salvestatud protseduurid, mis loovad tabeli kirjutamise ja/või lugemise toimingute logisid, pakuvad andmebaasi turvalisuse lisavõimalust. Selliseid protseduure kasutades saab andmebaasi administraator jälgida kasutajate või rakendusprogrammide poolt andmebaasis tehtud muudatusi.
Salvestatud protseduuride loomine ja teostamine
Salvestatud protseduurid luuakse avalduse abil LOO PROTSEDUUR, millel on järgmine süntaks:
CREATE PROC proc_name [((@param1) type1 [ VARYING] [= vaike1] )] (, ...) AS partii | EXTERNAL NAME meetodi_nimi Süntaksi kokkulepped
Parameeter schema_name määrab skeemi nime, mille on määranud loodud salvestatud protseduuri omanik. Parameeter proc_name määrab salvestatud protseduuri nime. @param1 parameeter on protseduuri parameeter (formaalne argument), mille andmetüübi määrab parameeter type1. Protseduuri parameetrid on protseduuri sees kohalikud, nagu ka kohalikud muutujad on paketis kohalikud. Protseduuri parameetrid on väärtused, mille helistaja edastab protseduurile selles kasutamiseks. Parameeter default1 määrab vastava protseduuriparameetri vaikeväärtuse. (Vaikeväärtus võib olla ka NULL.)
VÄLJUNDI valik näitab, et protseduuri parameeter on tagastusparameeter ja seda saab kasutada väärtuse tagastamiseks salvestatud protseduurist kutsuvasse protseduuri või süsteemi.
Nagu varem mainitud, salvestatakse protseduuri eelkompileeritud vorm andmebaasi ja seda kasutatakse iga kord, kui seda kutsutakse. Kui salvestatud protseduur tuleb mingil põhjusel iga kord selle välja kutsumisel kompileerida, kasutage protseduuri deklareerimisel RECOMPILE valikuga. Suvandi WITH RECOMPILE kasutamine tühistab salvestatud protseduuride ühe olulisema eelise: jõudluse paranemise tänu ühele kompileerimisele. Seetõttu tuleks suvandit WITH RECOMPILE kasutada ainult siis, kui salvestatud protseduuris kasutatavaid andmebaasiobjekte sageli muudetakse.
EXECUTE AS klausel määratleb turbekonteksti, milles salvestatud protseduur peaks pärast selle väljakutsumist käivitama. Selle konteksti määramisega saab andmebaasimootor juhtida kasutajakontode valikut, et kontrollida juurdepääsuõigusi salvestatud protseduuriga viidatud objektidele.
Vaikimisi saavad käsku CREATE PROCEDURE kasutada ainult fikseeritud serverirolli sysadmin ja andmebaasi fikseeritud rollide db_owner või db_ddladmin liikmed. Kuid nende rollide liikmed saavad seda avaldust kasutades teistele kasutajatele selle õiguse määrata TOETUSE LOOMISE PROTSESS.
Allolev näide näitab, kuidas luua projektitabeliga töötamiseks lihtne salvestatud protseduur:
KASUTAGE SampleDb; MINGE PROTSEDUURI LOOMINE Suurenda eelarvet (@percent INT=5) AS VÄRSKENDUS Projekti SET Eelarve = Eelarve + Eelarve * @protsent/100;
Nagu varem öeldud, kasutage kahe paketi eraldamiseks GO juhised. CREATE PROCEDURE lauset ei saa kombineerida sama partii teiste Transact-SQL-i lausetega. Salvestatud protseduur IncreaseBudget suurendab kõigi projektide eelarveid teatud protsendi võrra, mille määrab @percent parameeter. Protseduur määratleb ka protsendi vaikeväärtuse (5), mida kasutatakse juhul, kui seda argumenti protseduuri käitamisel pole.
Salvestatud protseduurid pääsevad juurde tabelitele, mida pole olemas. See atribuut võimaldab teil protseduuri koodi siluda ilma vastavaid tabeleid loomata või isegi sihtserveriga ühendust loomata.
Erinevalt esmastest salvestatud protseduuridest, mis salvestatakse alati praeguses andmebaasis, on võimalik luua ajutisi salvestatud protseduure, mis salvestatakse alati ajutisse süsteemi andmebaasi tempdb. Üks põhjus ajutiste salvestatud protseduuride loomiseks võib olla teatud lausete rühma korduva täitmise vältimine andmebaasiga ühenduse loomisel. Saate luua kohalikke või globaalseid ajutisi protseduure. Selleks määratakse kohaliku protseduuri nimi ühe # märgiga (#proc_name) ja globaalse protseduuri nimi topeltmärgiga (##proc_name).
Kohalikku ajutiselt salvestatud protseduuri saab käivitada ainult selle loonud kasutaja ja ainult siis, kui see on ühendatud andmebaasiga, milles see loodi. Globaalset ajutist protseduuri saavad teostada kõik kasutajad, kuid ainult seni, kuni viimane ühendus, millel see käivitati (tavaliselt protseduuri looja ühendus), katkeb.
Salvestatud protseduuri elutsükkel koosneb kahest etapist: selle loomine ja teostamine. Iga protseduur luuakse üks kord ja viiakse läbi mitu korda. Salvestatud protseduur täidetakse kasutades TÄITDA juhiseid kasutaja, kes on protseduuri omanik või kellel on sellele protseduurile juurdepääsuõigus EXECUTE. Avaldusel EXECUTE on järgmine süntaks:
[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] väärtus | [@parameter1=] @muutuja ] | VAKEED).. Süntaksi kokkulepped
Kõik lause EXECUTE parameetrid, välja arvatud parameeter return_status, on sama loogilise tähendusega, mis lause CREATE PROCEDURE samadel parameetritel. Parameeter return_status määrab täisarvulise muutuja, mis salvestab protseduuri tagastusoleku. Väärtuse saab määrata parameetrile, kasutades kas konstanti (väärtus) või kohalikku muutujat (@muutuja). Nimega parameetrite väärtuste järjekord ei ole oluline, kuid nimeta parameetrite väärtused tuleb esitada selles järjekorras, milles need on määratletud lauses CREATE PROCEDURE.
DEFAULT klausel pakub protseduuri definitsioonis määratud protseduuriparameetri vaikeväärtust. Kui protseduur ootab väärtust parameetrile, mille jaoks pole vaikeväärtust määratletud ja parameeter puudub või on määratud DEFAULT märksõna, ilmneb tõrge.
Kui lause EXECUTE on partii esimene lause, võib märksõna EXECUTE välja jätta. Siiski on kindlam lisada see sõna igasse paketti. Lause EXECUTE kasutamine on näidatud allolevas näites:
KASUTAGE SampleDb; TÄITDA Suurenda eelarvet 10;
Selle näite lause EXECUTE käivitab IncreaseBudget salvestatud protseduuri, mis suurendab kõigi projektide eelarvet 10%.
Allolev näide näitab, kuidas luua salvestatud protseduur andmete töötlemiseks tabelites Employee ja Works_on:
Näidisprotseduur ModifyEmpId illustreerib salvestatud protseduuride kasutamist osana viiteterviklikkuse säilitamise protsessist (antud juhul tabelite Töötaja ja Works_on vahel). Sarnast salvestatud protseduuri saab kasutada päästiku definitsiooni sees, mis tegelikult tagab viitamise terviklikkuse.
Järgmine näide näitab OUTPUT-klausli kasutamist salvestatud protseduuris:
Seda salvestatud protseduuri saab käivitada järgmiste juhiste abil:
DEKLARERI @quantityDeleteEmployee INT; TEOSTA DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee VÄLJUND; PRINT N"Kustutatud töötajad: " + convert(nvarchar(30), @quantityDeleteEmployee);
See protseduur loendab projektide arvu, millega töötaja personalinumbriga @empId töötab, ja määrab saadud väärtuse parameetrile ©counter. Pärast seda, kui kõik antud personalinumbri read on tabelitest Töötaja ja Töötatavad kustutatud, määratakse arvutatud väärtus muutujale @quantityDeleteEmployee.
Parameetri väärtus tagastatakse kutsumisprotseduurile ainult siis, kui on määratud suvand OUTPUT. Ülaltoodud näites edastab protseduur DeleteEmployee kutsumisprotseduurile parameetri @counter, seega tagastab salvestatud protseduur süsteemile väärtuse. Seetõttu tuleb @counter parameeter määrata nii protseduuri deklareerimisel suvandis OUTPUT kui ka selle väljakutsumise lauses EXECUTE.
WITH RESULTS SETS lause EXECUTE klausel
SQL Server 2012-s sisestate lause EXECUTE jaoks WITH RESULTS SETS klausel, mille kaudu saate teatud tingimuste täitmisel muuta salvestatud protseduuri tulemuskomplekti vormi.
Seda lauset aitavad selgitada järgmised kaks näidet. Esimene näide on sissejuhatav näide, mis näitab, milline võib tulemus välja näha, kui klausel WITH RESULTS SETS on välja jäetud:
Protseduur EmployeesInDept on lihtne protseduur, mis kuvab kõigi konkreetses osakonnas töötavate töötajate personalinumbrid ja perekonnanimed. Osakonna number on protseduuri parameeter ja see tuleb sellele helistamisel täpsustada. Selle protseduuri teostamisel saadakse kahe veeruga tabel, mille pealkirjad ühtivad andmebaasi tabeli vastavate veergude nimetustega, st. Id ja perekonnanimi. Tulemuste veergude päiste (ja ka nende andmetüübi) muutmiseks kasutab SQL Server 2012 uut klauslit WITH RESULTS SETS. Selle lause rakendamine on näidatud allolevas näites:
KASUTAGE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS (( INT NOT NULL, [Perenimi] CHAR(20) NOT NULL));
Sel viisil kutsutud salvestatud protseduuri täitmise tulemus on järgmine:
Nagu näete, võimaldab salvestatud protseduuri käitamine, kasutades EXECUTE-lause WITH RESULT SETS klauslit, muuta protseduuri koostatud tulemuskomplekti veergude nimesid ja andmetüüpe. Seega pakub see uus funktsionaalsus suuremat paindlikkust salvestatud protseduuride täitmisel ja nende tulemuste uude tabelisse paigutamisel.
Salvestatud protseduuride struktuuri muutmine
Andmebaasimootor toetab ka juhiseid MUUDA PROTSEDUURI salvestatud protseduuride struktuuri muutmiseks. Käsku ALTER PROCEDURE kasutatakse tavaliselt protseduuris Transact-SQL-i lausete muutmiseks. Kõik lause ALTER PROCEDURE parameetrid omavad sama tähendust kui CREATE PROCEDURE lause samad parameetrid. Selle avalduse kasutamise peamine eesmärk on vältida olemasolevate salvestatud protseduuride õiguste tühistamist.
Andmebaasimootor toetab CURSOR andmetüüp. Seda andmetüüpi kasutatakse kursorite deklareerimiseks salvestatud protseduurides. Kursor on programmeerimiskonstruktsioon, mida kasutatakse päringu tulemuste (tavaliselt ridade komplekti) salvestamiseks ja võimaldab kasutajatel seda tulemust ridade kaupa kuvada.
Ühe või rühma salvestatud protseduuride kustutamiseks kasutage KUKKUMISE PROTSEDUURI juhend. Salvestatud protseduuri saavad kustutada ainult fikseeritud rollide db_owner ja sysadmin omanik või liikmed.
Salvestatud protseduurid ja levinud keele käitusaeg
SQL Server toetab Common Language Runtime (CLR), mis võimaldab arendada erinevaid andmebaasiobjekte (salvestatud protseduurid, kasutaja määratud funktsioonid, trigerid, kasutaja määratud agregaadid ja kohandatud andmetüübid), kasutades C# ja Visual Basic. CLR võimaldab teil ka neid objekte käivitada ühise käitusaja süsteemi abil.
Tavakeele käitusaeg on selle valiku abil lubatud ja keelatud clr_enabled süsteemi protseduur sp_configure, mis käivitatakse täitmiseks käsuga REKONFIGURERI. Järgmine näide näitab, kuidas saate CLR-i lubamiseks kasutada süsteemiprotseduuri sp_configure:
KASUTAGE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE
Protseduuri loomiseks, kompileerimiseks ja salvestamiseks CLR-i abil peate täitma järgmised sammud näidatud järjekorras.
Looge salvestatud protseduur C#-s või Visual Basicus ja seejärel kompileerige see sobiva kompilaatori abil.
Kasutades juhiseid LOO KOOSTAMINE, looge vastav käivitatav fail.
Käivitage protseduur lause EXECUTE abil.
Alloleval joonisel on kujutatud eelnevalt kirjeldatud sammude graafiline diagramm. Allpool on selle protsessi üksikasjalikum kirjeldus.
Esmalt loo vajalik programm arenduskeskkonnas nagu Visual Studio. Kompileerige valmis programm C# või Visual Basic kompilaatori abil objektkoodiks. See kood salvestatakse dünaamilise lingi teegi (.dll) faili, mis toimib CREATE ASSEMBLY lause allikana, mis loob vahepealse käivitatava koodi. Järgmisena väljastage täitmiskoodi andmebaasiobjektina salvestamiseks käsk CREATE PROCEDURE. Lõpuks käivitage protseduur tuttava EXECUTE-lause abil.
Allolev näide näitab C#-s salvestatud protseduuri lähtekoodi:
System.Data.SqlClient kasutamine; kasutades Microsoft.SqlServer.Server; public partial class StoredProcedures ( public static int CountEmployees() ( int read; SqlConnection ühendus = new SqlConnection("Context Connection=true"); ühendus.Open(); SqlCommand cmd = ühendus.CreateCommand(); cmd.CommandText = "select count(*) kui "Töötajate arv" " + "Töötajalt"; read = (int)cmd.ExecuteScalar(); connection.Close(); tagasta read; ) )
See protseduur rakendab päringu tabelis Töötaja ridade loendamiseks. Programmi alguses olevate direktiivide kasutamine määrake programmi käivitamiseks vajalikud nimeruumid. Nende direktiivide kasutamine võimaldab määrata klassinimesid lähtekoodis ilma vastavaid nimeruume selgesõnaliselt määramata. Järgmisena määratletakse klass StoredProcedures, mille jaoks SqlProcedure atribuut, mis teavitab kompilaatorit, et see klass on salvestatud protseduur. Meetod CountEmployees() on määratletud klassi koodi sees. Ühendus andmebaasisüsteemiga luuakse klassi eksemplari kaudu SqlConnection. Ühenduse avamiseks kasutatakse selle eksemplari meetodit Open(). A CreateCommand() meetod võimaldab teil pääseda juurde klassi eksemplarile SqlCommnd, millele edastatakse vajalik SQL-käsk.
Järgmises koodilõigul:
Cmd.CommandText = "valige arv(*) kui "Töötajate arv" " + "Töötajalt";
kasutab SELECT-lauset tabeli Töötaja ridade loendamiseks ja tulemuse kuvamiseks. Käsutekst määratakse, määrates cmd-muutuja atribuudi CommandText meetodi CreateCommand() poolt tagastatud eksemplarile. Järgmisena nimetatakse seda ExecuteScalar() meetod SqlCommandi eksemplar. See meetod tagastab skalaarväärtuse, mis teisendatakse täisarvuliseks andmetüübiks ja määratakse ridamuutujale.
Nüüd saate selle koodi koostada Visual Studio abil. Lisasin selle klassi projekti nimega CLRStoredProcedures, nii et Visual Studio koostab *.dll laiendiga samanimelise koostu. Allolev näide näitab järgmist sammu salvestatud protseduuri loomisel: käivitatava koodi loomine. Enne selle näite koodi käivitamist peate teadma kompileeritud dll-faili asukohta (tavaliselt asub see projekti silumiskaustas).
KASUTAGE SampleDb; MINGE KOOSTAMISE CLRStoredProcedures LOOMINE ALAST "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" KOOS PERMISSION_SET = OHUTU
Lause CREATE ASSEMBLY võtab sisendiks hallatud koodi ja loob vastava objekti, millele saate luua CLR-i salvestatud protseduure, kasutaja määratud funktsioone ja käivitajaid. Sellel juhisel on järgmine süntaks:
CREATE ASSEMBLY koostu_nimi [ AUTORISEERIMISE omaniku_nimi ] FROM (dll_fail) Süntaksi kokkulepped
Parameeter assembly_name määrab koostu nime. Valikuline klausel AUTORISEERIMINE määrab selle koostu omaniku rollinime. Klausel FROM määrab tee, kus laaditav koost asub.
PERMISSION_SET klausliga on CREATE ASSEMBLY lause väga oluline klausel ja seda tuleb alati täpsustada. See määratleb koostekoodile antud õiguste komplekti. SAFE-lubade komplekt on kõige piiravam. Nende õigustega koostukood ei pääse juurde välistele süsteemiressurssidele, näiteks failidele. Õiguste komplekt EXTERNAL_ACCESS võimaldab komplekteerimiskoodil juurdepääsu teatud välistele süsteemiressurssidele, samas kui UNSAFE õiguste komplekt võimaldab piiramatut juurdepääsu ressurssidele nii andmebaasisüsteemi sees kui ka väljaspool.
Koostekoodi teabe salvestamiseks peab kasutaja saama väljastada avalduse CREATE ASSEMBLY. Koostu omanik on käsku täitev kasutaja (või roll). Saate teha koostu omanikuks mõne teise kasutaja, kasutades lause CREATE SCHEMA klauslit AUThorisation.
Andmebaasimootor toetab ka avaldusi ALTER ASSEMBLY ja DROP ASSEMBLY. ALTER ASSEMBLY avaldus kasutatakse koostu värskendamiseks uusimale versioonile. See juhend lisab või eemaldab ka vastava koostuga seotud failid. Drop Assembly juhend Eemaldab praegusest andmebaasist määratud koostu ja kõik sellega seotud failid.
Allolev näide näitab, kuidas luua varem juurutatud hallatud koodi põhjal salvestatud protseduur.
KASUTAGE SampleDb; MINGE PROTSEDUURI loomine CountEmployees VÄLISnimena CLRStoredProcedures.StoredProcedures.CountTemployees
Näites olev CREATE PROCEDURE juhis erineb eelmiste näidete samast juhisest selle poolest, et sisaldab EXTERNAL NAME parameeter. See suvand määrab, et kood genereeritakse ühise keele käitusajaga. Selles lauses olev nimi koosneb kolmest osast:
koostu_nimi.klassi_nimi.meetodi_nimi
koostu_nimi - näitab koostu nime;
klassi_nimi - näitab üldklassi nime;
meetodi_nimi – valikuline osa, määrab klassi sees määratletud meetodi nime.
Protseduuri CountEmployees täitmine on näidatud allolevas näites:
KASUTAGE SampleDb; DECLARE @count INT EXECUTE @count = Töötajate arv PRINT @count -- Return 7
Avaldus PRINT tagastab tabelis Töötaja praeguse ridade arvu.
Salvestatud protseduur salvestatud protseduur) on nimega andmebaasiprogrammi objekt. SQL Serveril on mitut tüüpi salvestatud protseduure.
Süsteemi salvestatud protseduurid süsteemi salvestatud protseduurid) tarnivad DBMS-i arendajad ja neid kasutatakse süsteemikataloogiga toimingute tegemiseks või süsteemiteabe hankimiseks. Nende nimed algavad tavaliselt eesliitega "sp_". Käitate igat tüüpi salvestatud protseduure, kasutades käsku EXECUTE, mida saab lühendada EXEC-iks. Näiteks salvestatud protseduur sp_helplogins, mis töötab ilma parameetriteta, loob kaks aruannet kontonimede kohta (Inglise) sisselogimised) ja vastavad kasutajad igas andmebaasis (Inglise) kasutajad).
EXEC sp_helplogins;
Süsteemi salvestatud protseduuride abil tehtud toimingutest ettekujutuse andmiseks on tabel 10.6 näitab mõningaid näiteid. Kokku on SQL Serveris üle tuhande süsteemi salvestatud protseduuri.
Tabel 10.6
SQL Serveri süsteemi salvestatud protseduuride näited
Kasutaja saab luua salvestatud protseduure kasutajate andmebaasides ja ajutiste objektide andmebaasis. Viimasel juhul salvestatakse protseduur ajaline. Nagu ajutiste tabelite puhul, peab ajutise salvestatud protseduuri nimi algama eesliitega "#", kui see on kohalik ajutine salvestatud protseduur, või "##", kui see on globaalne. Kohalikku ajutist protseduuri saab kasutada ainult ühenduse sees, milles see loodi, globaalset saab kasutada ka muude ühenduste sees.
SQL Serveri programmeeritavaid objekte saab luua kas Transact-SQL tööriistade või koostude abil (Inglise) assembly) Microsoft.Net Frameworki CRL (Common Language Runtime) keskkonnas. See õpetus hõlmab ainult esimest meetodit.
Salvestatud protseduuride loomiseks kasutage käsku CREATE PROCEDURE (saab lühendada PROC), mille vorming on toodud allpool:
CREATE (PROC I PROCEDURE) protsessi_nimi [ ; number ]
[(gparameter data_type)
["vaikimisi] |
[KOOS
[REPLIKSEERIMISEKS]
AS ([ BEGIN ] sql_lause [;] [ ...n ] [ END ] )
Kui salvestatud protseduur (või triger, funktsioon, vaade) luuakse valikuga Krüptimine, teisendatakse selle kood selliselt, et tekst muutub loetamatuks. Samal ajal, nagu märgitud, kanti kasutatud algoritm SQL Serveri varasematest versioonidest üle ja seda ei saa pidada usaldusväärseks kaitsealgoritmiks - on olemas utiliidid, mis võimaldavad teil kiiresti pöördkonversiooni teha.
Valik RECOMPILE määrab, et süsteem kompileerib teksti uuesti iga kord, kui protseduur kutsutakse. Tavalisel juhul salvestatakse esimesel käivitamisel koostatud protseduur vahemällu, mis võimaldab jõudlust suurendada.
EXECUTE AS määrab turbekonteksti, milles protseduur viiakse läbi. Järgmisena üks väärtustest f CALLER | ISE | OMANIK | "kasutaja_nimi"). CALLER on vaikimisi ja tähendab, et kood käivitatakse seda moodulit kutsuva kasutaja turbekontekstis. Vastavalt sellele peavad kasutajal olema õigused mitte ainult programmeeritavale objektile endale, vaid ka teistele andmebaasiobjektidele, mida see mõjutab. TEOSTAMINE ISE tähendab kasutaja konteksti kasutamist, mis loob või muudab programmeeritavat objekti. OWNER määrab, et kood käivitatakse protseduuri praeguse omaniku kontekstis. Kui sellele pole omanikku määratud, siis eeldatakse selle skeemi omanikku, kuhu see kuulub. EXECUTE AS "kasutaja_nimi" võimaldab teil selgelt määrata kasutajanime (üksikute jutumärkidega).
Protseduuri jaoks saab määrata parameetreid. Need on kohalikud muutujad, mida kasutatakse väärtuste edastamiseks protseduurile. Kui parameeter on deklareeritud märksõnaga OUTPUT (või lühidalt OUT), on see väljundväärtus: sellele protseduuris pärast selle lõpetamist antud väärtust saab kasutada protseduuri kutsunud programm. Märksõna READONLY tähendab, et parameetri väärtust ei saa salvestatud protseduuri sees muuta.
Parameetritele saab määrata vaikeväärtused, mida kasutatakse juhul, kui parameetri väärtus pole protseduuri kutsumisel selgesõnaliselt määratud. Vaatame näidet:
CREATE PROC surma (@a int, @b int=0,
©tulemus int OUTPUT) AS
SET @result=0a+0b
Oleme loonud kolme parameetriga protseduuri ja parameetri @b vaikeväärtus on =0 ja parameeter @result on väljundparameeter: see tagastab väärtuse kutsuvale programmile. Tehtud toimingud on üsna lihtsad - väljundparameeter saab kahe sisendväärtuse summa.
SQL Server Management Studios töötades leiate loodud salvestatud protseduuri programmeeritavate andmebaasiobjektide jaotisest (Inglise) Programmeeritavus) salvestatud protseduuride alajaotises (joonis 10.2).
Protseduuri kutsumisel saab sisendparameetritena kasutada nii muutujaid kui konstante. Vaatame kahte näidet. Esimeses on protseduuri sisendparameetrid selgesõnaliselt määratud konstantidena ja väljundparameetri jaoks on kõnes määratud märksõna OUTPUT. Teine valik kasutab muutuja väärtust esimese sisendparameetrina ja määrab, et teise parameetri jaoks tuleks kasutada vaikeväärtust, kasutades märksõna DEFAULT:
Riis. 10.2.
DECLARE @с int;
EXEC summa 10.5,@c VÄLJUND;
PRINT 0c; – kuvatakse 15
DEKLARERI Gi int = 5;
– kasutage helistamisel vaikeväärtust
EXEC summa Gi,DEFAULT, 0c VÄLJUND;
PRINT 0c; – Kuvatakse 5
Vaatleme nüüd näidet selle tagastuskoodi analüüsiga, millega protseduur lõpeb. Oletame, et peame arvutama, mitu raamatut Bookli tabelis on antud aastate jooksul välja antud. Veelgi enam, kui esialgne aasta on suurem kui viimane, tagastab protseduur "1" ja seda ei arvestata, vastasel juhul loendame raamatute arvu ja tagastame 0:
CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
KUI 0eelmine aasta>0eelmine aasta TAGASTUS 1
SET @result= (SELECT COUNT(*) FROM dbo.Bookl
KUS 0esimese aasta ja 0eelmise aasta vahel);
Vaatleme selle protseduuri kutsumise varianti, kus tagastuskood salvestatakse täisarvulises muutujas 0ret, mille järel analüüsitakse selle väärtust (antud juhul on see 1). PRINT-lauses kasutatavat funktsiooni CAST kasutatakse täisarvulise muutuja Gres väärtuse teisendamiseks stringitüübiks:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
KUI 0ret=l PRINT "Algusaasta on suurem kui lõppaasta"
PRINT "Raamatute arv" + CAST (Gres as varchar(20))
Salvestatud protseduurid ei saa mitte ainult lugeda tabelist andmeid, vaid ka muuta andmeid ja isegi luua tabeleid ja mitmeid muid andmebaasiobjekte.
Siiski ei saa te salvestatud protseduurist luua skeeme, funktsioone, käivitajaid, protseduure ega vaateid.
Järgmine näide illustreerib nii neid võimalusi kui ka ajutiste objektide ulatusega seotud probleeme. Järgmine salvestatud protseduur kontrollib ajutise tabeli #TaL2 olemasolu; kui seda tabelit pole, loob see selle. Pärast seda sisestatakse tabelisse #TaL2 kahe veeru väärtused ja tabeli sisu kuvatakse SELECT-lause abil:
CREATE PROC My_Procl (@id int, @name varchar(30))
KUI OBJECT_ID("tempdb.dbo.#Tab21) ON NULL
SISESTAGE dbo.#Tab2 (id, nimi)VÄÄRTUSED (0id,0nimi)
SELECT * FROM dbo. #Tab2 –№1
Enne salvestatud protseduuri esmakordset väljakutsumist loome selles kasutatava ajutise tabeli #TaL2. Pöörake tähelepanu EXEC-operaatorile. Eelmistes näidetes anti parameetrid protseduurile "positsiooni järgi", kuid sel juhul kasutatakse parameetrite edastamiseks teistsugust vormingut - "nime järgi", parameetri nimi ja väärtus on selgelt märgitud:
CREATE TABLE dbo.#Tab2 (id int, nimi varchar(30));
EXEC My_Procl 0name="lvan", 0id=2;
SELECT * FROM dbo.#Tab2; –№2
Ülaltoodud näites töödeldakse SELECT-lauset kaks korda: esimene kord – protseduuri sees, teine kord – kutsuva koodi fragmendist (märgitud kommentaariga “Nr 2”).
Enne teist protseduuri kutsumist kustutame ajutise tabeli #TaL2. Seejärel luuakse salvestatud protseduurist samanimeline ajutine tabel:
TABLE DBO.#TAB2;
EXEC My_Procl 0name="Ivan", 0id=2;
SELECT * FROM dbo.#Tab2; –№2
Sel juhul kuvab andmeid ainult protseduuri sees asuv SELECT-lause (kommentaariga “Xa 1”). SELECT "Nr 2" käivitamine toob kaasa vea, kuna salvestatud protseduuris loodud ajutine tabel kustutatakse juba protseduuri naasmise ajal tempdb andmebaasist.
Saate loobuda salvestatud protseduurist, kasutades käsku DROP PROCEDURE. Selle vorming on esitatud allpool. Saate kustutada mitu salvestatud protseduuri ühe lausega, loetledes need komadega eraldatuna:
VÄLJASTA (PROC I MENETLUS) ( protseduur ) [
Näiteks kustutame eelnevalt loodud summaprotseduuri:
DROP PROC summa;
Olemasolevas protseduuris saate teha muudatusi (ja tegelikult selle uuesti määratleda), kasutades käsku ALTER PROCEDURE (lubatud
lühend PROC). Välja arvatud märksõna ALTER, on avalduse vorming sisuliselt sama, mis CREATE PROCEDURE oma. Näiteks muudame dbo protseduuri. rownum, määrates selle käivituma omaniku turbekontekstis:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int OUTPUT)
WITH EXECUTE AS Owner – paigaldatav võimalus
KUI 0eelmine aasta>0eelmine aasta TAGASI 1 MUUD ALGAS
SET 0result= (SELECT COUNT(*) FROM dbo.Bookl
KUS SFirsYear JA SLastYear vahel);
Mõnel juhul võib osutuda vajalikuks käsk dünaamiliselt genereerida ja see andmebaasiserveris täita. Seda probleemi saab lahendada ka operaatori EXEC abil. Allolev näide hangib kirjed tabelist Bookl, kui atribuut Aasta on võrdne muutuja määratud väärtusega:
DEKLARERI 0у int = 2000;
EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;
Dünaamiliselt genereeritud käskude täitmine loob eeldused arvutirünnakute, näiteks “SQL-i süsti” rakendamiseks. (Inglise) SQL-i süstimine). Rünnaku olemus seisneb selles, et ründaja sisestab dünaamiliselt genereeritud päringusse omaenda SQL-koodi. See juhtub tavaliselt siis, kui asendatavad parameetrid võetakse kasutaja sisestatud tulemustest.
Muudame veidi eelmist näidet:
DECLARE 0у varchar(100);
SET 0у="2ООО"; – saime selle kasutajalt
Kui eeldame, et saime kasutajalt SET-lauses määratud stringi väärtuse (ükskõik kuidas näiteks veebirakenduse kaudu), siis näide illustreerib meie koodi “tavalist” käitumist.
DECLARE 0у varchar(100);
SET 0у="2000; DELETE FROM dbo.Book2"; - süstimine
EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);
Sellistel juhtudel on võimalusel soovitatav kasutada süsteemi salvestatud protseduuri sp_executcsql, mis võimaldab juhtida parameetrite tüüpi, mis on üheks SQL-i süstimise takistuseks. Vaatlemata selle vormingut üksikasjalikult, vaatame näidet, mis sarnaneb varem esitatud näitega:
KÄIVITA sp_executesql
N"SELECT * FROM dbo.Bookl WHERE =0y",
See määrab täpselt päringus kasutatava parameetri tüübi ja SQL Server juhib seda täitmise ajal. Täht "N" enne jutumärke näitab, et see on Unicode'i literaalne konstant, nagu protseduur nõuab. Parameetrile saab määrata mitte ainult konstantse väärtuse, vaid ka mõne teise muutuja väärtuse.
Töö eesmärk– õppida looma ja kasutama andmebaasiserveris salvestatud protseduure.
1. Töötage läbi kõik näited, analüüsige nende täitmise tulemusi utiliidis SQL Server Management Studio. Loodud protseduuride olemasolu kontrollimine praeguses andmebaasis.
2. Kõikide näidete ja ülesannete täitmine laboritöö käigus.
3. Üksikute ülesannete täitmine vastavalt valikuvõimalustele.
Selgitused tööde teostamiseks
Salvestatud protseduuride programmeerimise valdamiseks kasutame näidisandmebaasi nimega DB_raamatud, mis loodi laboritöös nr 1. Näidete ja ülesannete täitmisel pöörake tähelepanu andmebaasi, tabelite ja muude projektiobjektide nimede vastavusele.
Salvestatud protseduurid on käskude kogum, mis koosneb ühest või mitmest SQL-lausest või funktsioonist ja on salvestatud andmebaasi kompileeritud kujul.
Salvestatud protseduuride tüübid
Süsteemi salvestatud protseduurid on mõeldud erinevate haldustoimingute tegemiseks. Peaaegu kõik serveri haldustoimingud tehakse nende abiga. Võime öelda, et süsteemi salvestatud protseduurid on liides, mis pakub tööd süsteemitabelitega. Süsteemi salvestatud protseduuride eesliide on sp_, need salvestatakse süsteemi andmebaasi ja neid saab kutsuda mis tahes muu andmebaasi kontekstis.
Kohandatud salvestatud protseduurid rakendavad teatud toiminguid. Salvestatud protseduurid on täieõiguslik andmebaasiobjekt. Selle tulemusena asub iga salvestatud protseduur kindlas andmebaasis, kus see täidetakse.
Ajutiselt salvestatud protseduurid eksisteerivad vaid lühikest aega, pärast mida server need automaatselt hävitab. Need jagunevad kohalikeks ja globaalseteks. Kohalikke ajutiselt salvestatud protseduure saab välja kutsuda ainult ühendusest, milles need on loodud. Sellise protseduuri loomisel peate andma sellele nime, mis algab ühe tähemärgiga #. Nagu kõik ajutised objektid, kustutatakse seda tüüpi salvestatud protseduurid automaatselt, kui kasutaja katkestab ühenduse või kui server taaskäivitatakse või peatatakse. Globaalsed ajutised salvestatud protseduurid on saadaval mis tahes ühenduse jaoks serverist, millel on sama protseduur. Selle määratlemiseks andke sellele lihtsalt nimi, mis algab tähemärkidega ##. Need protseduurid kustutatakse, kui server taaskäivitatakse või peatatakse või kui ühendus kontekstis, milles need loodi, suletakse.
Salvestatud protseduuride loomine, muutmine
Salvestatud protseduuri loomine hõlmab järgmiste probleemide lahendamist: juurdepääsuõiguste planeerimine. Salvestatud protseduuri loomisel pidage meeles, et sellel on andmebaasiobjektidele samad juurdepääsuõigused kui selle loonud kasutajal; salvestatud protseduuri parameetrite määratlemine, salvestatud protseduuridel võivad olla sisend- ja väljundparameetrid; salvestatud protseduurikoodi arendamine. Protseduurikood võib sisaldada mis tahes SQL-käskude jada, sealhulgas muude salvestatud protseduuride väljakutsed.
Operaatori süntaks uue või olemasoleva salvestatud protseduuri muutmiseks MS SQL Serveri tähistuses:
( CREATE | ALTER ) PROC[ EDURE] protseduuri_nimi [ ;number] [ ( @parameetri_nimi andmetüüp ) [ VARYING ] [ = VAIKESED ] [ VÄLJANDUS ] ] [ ,... n] [ WITH ( ÜMBERKOMPLEERIMINE | Krüpteerimine | ÜMBERKOMPLEERIMINE, Krüpteerimine ) ] [ REPLIKSIOONIKS] AS sql_statement [ ... n]Vaatame selle käsu parameetreid.
Kasutades eesliiteid sp_, #, ##, saab loodud protseduuri defineerida kui süsteemset või ajutist. Nagu näete käsu süntaksist, ei ole lubatud määrata omaniku nime, kellele loodud protseduur kuulub, samuti andmebaasi nime, kus see peaks asuma. Seetõttu peate loodava salvestatud protseduuri paigutamiseks konkreetsesse andmebaasi andma selle andmebaasi kontekstis käsu CREATE PROCEDURE. Kui pääsete juurde salvestatud protseduuri põhiosast sama andmebaasi objektidele, saate kasutada lühendatud nimesid, st ilma andmebaasi nime määramata. Kui teil on vaja juurdepääsu teistes andmebaasides asuvatele objektidele, on andmebaasi nime määramine kohustuslik.
Sisend- ja väljundandmete edastamiseks peavad loodud salvestatud protseduuri parameetrite nimed algama @-märgiga. Ühes salvestatud protseduuris saate määrata mitu parameetrit, eraldades need komadega. Protseduuri põhiosa ei tohiks kasutada kohalikke muutujaid, mille nimed langevad kokku selle protseduuri parameetrite nimedega. Salvestatud protseduuriparameetrite andmetüübi määramiseks sobivad kõik SQL-i andmetüübid, sealhulgas kasutaja määratud. Andmetüüpi CURSOR saab aga kasutada ainult salvestatud protseduuri väljundparameetrina, st. määrates märksõna OUTPUT.
Märksõna OUTPUT olemasolu tähendab, et vastav parameeter on mõeldud salvestatud protseduuri andmete tagastamiseks. See aga ei tähenda, et parameeter ei sobi väärtuste edastamiseks salvestatud protseduurile. Märksõna OUTPUT määramine juhendab serverit salvestatud protseduurist väljumisel määrama praeguse parameetri väärtuse kohalikule muutujale, mis määrati protseduuri kutsumisel parameetri väärtuseks. Pange tähele, et võtmesõna OUTPUT määramisel saab protseduuri kutsumisel vastava parameetri väärtust määrata ainult kohaliku muutuja abil. Tavaliste parameetrite jaoks lubatud avaldised või konstandid ei ole lubatud. Märksõna VARYING kasutatakse koos parameetriga OUTPUT, mille tüüp on CURSOR. See määrab, et väljundiks on tulemuskomplekt.
Märksõna DEFAULT tähistab väärtust, mille vastav parameeter vaikimisi võtab. Seega ei pea protseduuri kutsumisel vastava parameetri väärtust otseselt määrama.
Kuna server salvestab päringu täitmisplaani ja kompileeritud koodi vahemällu, kasutatakse järgmisel protseduuri väljakutsumisel valmisväärtusi. Mõnel juhul on siiski vaja protseduurikood uuesti kompileerida. Märksõna RECOMPILE määramine annab süsteemile käsu luua salvestatud protseduuri jaoks täitmisplaan iga kord, kui see välja kutsutakse.
Parameeter FOR REPLICATION on vajalik andmete paljundamiseks ja loodud salvestatud protseduuri kaasamisel artiklina väljaandesse. Märksõna ENCRYPTION annab serverile käsu krüpteerida salvestatud protseduurikood, mis võib pakkuda kaitset salvestatud protseduuri rakendavate patenteeritud algoritmide kasutamise eest. AS-i märksõna asetatakse salvestatud protseduuri keha algusesse. Protseduuri keha saab kasutada peaaegu kõiki SQL-i käske, deklareerida tehinguid, määrata lukke ja kutsuda teisi salvestatud protseduure. Salvestatud protseduurist saate väljuda käsu RETURN abil.
Salvestatud protseduuri eemaldamine
KÜLKIDA PROTSEDUUR (protseduuri_nimi) [,...n]
Salvestatud protseduuri läbiviimine
Salvestatud protseduuri täitmiseks kasutage käsku: [ [ EXEC [ UTE] protseduuri_nimi [ ;number] [ [ @parameetri_nimi= ] ( väärtus | @muutuja_nimi) [ OUTPUT ] | [VAIKESE] ] [ ,... n]
Kui salvestatud protseduurikutse ei ole paketi ainus käsk, siis on vajalik käsk EXECUTE. Pealegi on see käsk vajalik protseduuri kutsumiseks mõne teise protseduuri või päästiku põhiosast.
Märksõna OUTPUT kasutamine protseduuri kutsumisel on lubatud ainult parameetrite puhul, mis deklareeriti protseduuri loomisel võtmesõnaga OUTPUT.
Kui protseduuri kutsumisel on parameetri jaoks määratud võtmesõna DEFAULT, kasutatakse vaikeväärtust. Loomulikult on määratud sõna DEFAULT lubatud ainult nende parameetrite jaoks, millele on määratud vaikeväärtus.
Käsu EXECUTE süntaks näitab, et protseduuri kutsumisel võib parameetrite nimed ära jätta. Kuid sel juhul peab kasutaja määrama parameetrite väärtused samas järjekorras, milles need protseduuri loomisel loetleti. Te ei saa parameetrile vaikeväärtust määrata, jättes selle loendi ajal lihtsalt välja. Kui soovite vaikeväärtusega parameetreid välja jätta, piisab salvestatud protseduuri kutsumisel parameetrite nimede selgesõnalisest määramisest. Lisaks saate sel viisil loetleda parameetrid ja nende väärtused mis tahes järjekorras.
Pange tähele, et protseduuri kutsumisel määratakse parameetrite nimed koos väärtustega või ainult väärtused ilma parameetri nimeta. Nende kombineerimine ei ole lubatud.
RETURN kasutamine salvestatud protseduuris
Võimaldab protseduurist väljuda suvalises punktis vastavalt kindlaksmääratud tingimustele, samuti võimaldab protseduuri tulemust edastada numbrina, mille järgi saab hinnata protseduuri kvaliteeti ja õigsust. Näide parameetriteta protseduuri loomisest:
LOO PROTSEDUUR Count_Books AS SELECT COUNT (koodiraamat) FROM Books GO1. harjutus.
EXEC Count_BooksKontrolli tulemust.
Sisendparameetriga protseduuri loomise näide:
LOOMINE PROTSEDUUR Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Lehekülgi>= @Count_pages GO2. ülesanne. Looge see protseduur andmebaasi DB_Books jaotises Salvestatud protseduurid, kasutades SQL serveri Management Studio utiliiti. Käivitage see käsuga
EXEC Count_Books_Pages 100Kontrolli tulemust.
Sisendparameetritega protseduuri loomise näide:
LOOMINE PROTSEDUUR Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Lehekülgi>= @Loe_lehekülgi AND Pealkiri_raamat LIKE @Title GO3. ülesanne. Looge see protseduur andmebaasi DB_Books jaotises Salvestatud protseduurid, kasutades SQL serveri Management Studio utiliiti. Käivitage see käsuga
EXEC Count_Books_Title 100, "P%"Kontrolli tulemust.
Sisendparameetrite ja väljundparameetritega protseduuri loomise näide:
LOOMINE PROTSESS Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT VÄLJUND AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Lehekülgi>= @Count_pages AND Title_book LIKE @Title GO4. ülesanne. Looge see protseduur andmebaasi DB_Books jaotises Salvestatud protseduurid, kasutades SQL serveri Management Studio utiliiti. Käivitage käsukomplekti kasutades:
Sql> Deklareeri @q kui int EXEC Count_Books_Itogo 100, "P%", @q väljund vali @q
Kontrolli tulemust.
Sisendparameetrite ja RETURN-iga protseduuri loomise näide:
CREATE PROCEDURE checkname @param INT ASIF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Puškin A.S." TAGASI 1 MUU TAGASI 25. ülesanne. Looge see protseduur andmebaasi DB_Books jaotises Salvestatud protseduurid, kasutades SQL serveri Management Studio utiliiti. Käivitage see, kasutades käske:
DEKLARERI @return_status INT EXEC @return_status = kontrollnimi 1 VALI "Tagastamise olek" = @return_statusNäide parameetriteta protseduuri loomisest, et suurendada võtmevälja väärtust tabelis Ostud 2 korda:
CREATE PROC update_proc AS UPDATE Ostud SET Code_purchase = Code_purchase* 26. ülesanne. Looge see protseduur andmebaasi DB_Books jaotises Salvestatud protseduurid, kasutades SQL serveri Management Studio utiliiti. Käivitage see käsuga
EXEC update_procSisendparameetriga protseduuri näide konkreetse autori kohta kogu teabe saamiseks:
CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Autorid WHERE nimi_autor= @kÜlesanne 7.
EXEC select_author "Puškin A.S." või select_author @k= "Puškin A.S." või EXEC select_author @k= "Puškin A.S."Näide sisendparameetri ja vaikeväärtusega protseduuri loomisest, et suurendada võtmevälja väärtust tabelis Ostud määratud arv kordi (vaikimisi 2 korda):
CREATE PROC update_proc @p INT = 2 AS UPDATE Ostud SET Code_purchase = Code_purchase * @pProtseduur ei tagasta andmeid.
Ülesanne 8. Looge see protseduur andmebaasi DB_Books jaotises Salvestatud protseduurid, kasutades SQL serveri Management Studio utiliiti. Käivitage see, kasutades käske:
EXEC update_proc 4 või EXEC update_proc @p = 4 või EXEC update_proc --kasutatakse vaikeväärtust.Sisend- ja väljundparameetritega protseduuri loomise näide. Looge protseduur, et määrata kindlaks määratud perioodi jooksul täidetud tellimuste arv:
CREATE PROC count_postes @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 JA @d2 SET @c = ISNULL(@c, 0 )Ülesanne 9. Looge see protseduur andmebaasi DB_Books jaotises Salvestatud protseduurid, kasutades SQL serveri Management Studio utiliiti. Käivitage see, kasutades käske:
DEKLARERI @c2 INT EXEC ostude arv '01-jun-2006', '01-jul-2006', @c2 VÄLJUNDI VALIK @c2Laboratoorsete tööde nr 4 ülesannete valikud
Üldsätted. Looge SQL Server Management Studio utiliidis koodi jaoks uus leht (nupp „Loo päring”). Muutke loodud andmebaas DB_Books programmiliselt aktiivseks, kasutades käsku Use. Looge salvestatud protseduurid, kasutades funktsiooni Loo protseduurilaused, ja määrake protseduuride nimed ise. Iga protseduur käivitab ühe SQL-päringu, mis käivitati teises laboris. Veelgi enam, päringute SQL-koodi tuleb muuta, et need saaksid edastada otsinguks kasutatud väljade väärtusi.
Näiteks lähteülesanne ja -soov laboritöös nr 2:
/*Valige tarnijate kataloogist (Tarnetabel) ettevõtete nimed, telefoninumbrid ja INN (väljad Name_company, Phone ja INN), mille ettevõtte nimi (väli Name_company) on “OJSC MIR”.
SELECT Ettevõtte nimi, telefon, INN FROM Tarned WHERE Name_company = "OJSC MIR"*/ –Selles töös luuakse järgmine protseduur:
CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Tarned WHERE Name_company = @comp– Protseduuri alustamiseks kasutage käsku:
EXEC select_name_company "JSC MIR"Ülesannete nimekiri
Looge SQL Server Management Studios uus programm. Aktiveerige programmiliselt laboritöös nr 1 loodud individuaalne andmebaas, kasutades käsku Use. Looge salvestatud protseduurid, kasutades funktsiooni Loo protseduurilaused, ja määrake protseduuride nimed ise. Iga protseduur täidab ühe SQL-päringu, mis esitatakse vastavalt suvanditele eraldi ülesannete kujul.
valik 1
1. Kuvage nimekiri töötajatest, kellel on vähemalt üks laps.
2. Kuvage nimekiri lastest, kes said määratud ajavahemiku jooksul kingitusi.
3. Kuvage nimekiri vanematest, kellel on alaealised lapsed.
4. Kuvage kuupäeva järgi sorteeritud teave kingituste kohta, mille väärtus on suurem kui määratud arv.
2. võimalus
1. Kuvage määratud tüüpi seadmete loend.
2. Kuvage parandatud seadmete arv ja remondi kogumaksumus määratud tehnikult.
3. Kuvage seadmete omanike loend ja nende taotluste arv, mis on sorteeritud taotluste arvu järgi kahanevas järjekorras.
4. Kuvage teave käsitööliste kohta, kelle auaste on suurem kui määratud arv või kelle töölevõtmise kuupäev on määratud kuupäevast lühem.
3. võimalus
2. Kuvage loend müügikoodidest, mis müüsid lilli määratud numbrist suurema summa eest.
3. Kuva müügikuupäev, summa, müüja ja lill vastavalt määratud müügikoodile.
4. Kuvage lillede loend ja sort lillede jaoks, mille kõrgus on suurem kui määratud arv või õitseb.
4. võimalus
1. Kuvage kindlaksmääratud näidustusega ravimite loend.
2. Kuvage tarnekuupäevade loend, mille puhul müüdi rohkem kui määratud arv samanimelist ravimit.
3. Näidake tarnekuupäeva, summat, tarnijalt saadud juhi täisnime ja ravimi nimetust kviitungi koodiga, mis on suurem kui määratud number.
5. võimalus
2. Kuvage määratud põhjusel kasutusest kõrvaldatud seadmete loend.
3. Määratud ajavahemikul maha kantud seadmete puhul kuvada vastuvõtmise kuupäev, seadme nimi, vastutava isiku täisnimi ja mahakandmise kuupäev.
4. Kuvage seadmete loend, mille tüüp on määratud või mille vastuvõtukuupäev on suurem kui teatud väärtus
6. valik
1. Kuvage määratud arvust suurema kaaluga roogade loend.
2. Kuvage nende toodete loend, mille nimed sisaldavad määratud sõnafragmenti.
3. Kuva toote maht, roa nimetus, toote nimetus koos roa koodiga määratud algväärtusest kuni kindla lõppväärtuseni.
4. Kuvage roa valmistamise järjekord ja roa nimi, mille süsivesikute kogus on suurem kui teatud väärtus või kalorite kogus on suurem kui määratud väärtus.
7. valik
1. Kuvage määratud ametikohaga töötajate nimekiri.
3. Määratud ajavahemikul registreeritud dokumentide jaoks kuvage registreerimiskuupäev, dokumendi tüüp, registripidaja täisnimi ja organisatsiooni nimi.
4. Kuvage registreeritud dokumentide loend, millel on kindel dokumenditüüp või registreerimiskuupäev, mis on määratud väärtusest suurem.
8. valik
1. Kuvage töötajate nimekiri koos määratud vallandamise põhjusega.
3. Määratud ajavahemikul registreeritud dokumentide jaoks kuvage registreerimise kuupäev, vallandamise põhjus, töötaja täisnimi.
9. valik
1. Kuvage nimekiri töötajatest, kes võtsid määratud tüüpi puhkuse.
2. Kuvage dokumentide loend koos registreerimiskuupäevaga määratud ajavahemikus.
3. Määratud ajavahemikul registreeritud dokumentide jaoks kuvage registreerimise kuupäev, puhkuse liik, töötaja täisnimi.
4. Kuvage registreeritud dokumentide loend koos dokumendikoodiga määratud vahemikus.
10. valik
1. Kuvage määratud ametikohaga töötajate nimekiri.
2. Kuvage nende dokumentide loend, mille sisu sisaldab määratud sõnafragmenti.
3. Määratud ajavahemikul registreeritud dokumentide puhul kuvage registreerimiskuupäev, dokumendi tüüp, saatja täisnimi ja organisatsiooni nimi.
4. Kuvage registreeritud dokumentide loend, millel on määratud dokumendi tüüp või dokumendikood, mis on väiksem kui teatud väärtus.
11. valik
1. Kuvage määratud ametikohale määratud töötajate nimekiri.
2. Kuvage dokumentide loend koos registreerimiskuupäevaga määratud ajavahemikus.
3. Määratud ajavahemikul registreeritud dokumentide jaoks kuvage registreerimise kuupäev, ametikoht, töötaja täisnimi.
4. Kuvage registreeritud dokumentide loend koos dokumendikoodiga määratud vahemikus.
12. valik
3. Kuvage varustust rentinud inimeste loend ja nende taotluste arv, sorteerituna taotluste arvu järgi kahanevas järjekorras.
13. valik
1. Kuvage määratud tüüpi seadmete loend. 2. Kuva nimekiri seadmetest, mille konkreetne töötaja on maha kandnud.
3. Kuvage kasutusest kõrvaldatud seadmete kogus grupeerituna seadme tüübi järgi.
4. Kuvage teave töötajate kohta, kelle töölevõtmise kuupäev on teatud kuupäevast suurem.
14. valik
1. Printige määratud lehetüübiga lillede loend.
2. Kuvage kviitungikoodide loend, mille puhul müüdi lilli teatud väärtusest suurema summa eest.
3. Kuva laekumise kuupäev, summa, tarnija nimi ja värvid kindla tarnijakoodi järgi.
4. Kuvage teatud arvust suurema kõrgusega või õitsevate lillede loend ja sort.
15. variant
1. Kuvage nimekiri klientidest, kes on määratud ajavahemikul tubadesse registreerunud.
2. Kuvage iga kliendi jaoks tubade eest makstud kogusumma.
3. Kuvatakse saabumiskuupäev, toa tüüp, määratud perioodil registreeritud klientide täisnimed.
4. Kuvage teatud tüüpi ruumides registreeritud klientide nimekiri.
16. valik
1. Kuvage määratud tüüpi seadmete loend.
2. Kuvage konkreetse kliendi poolt laenutatud seadmete loend.
3. Kuvage varustust rentinud inimeste loend ja nende taotluste arv, sorteerituna taotluste arvu järgi kahanevas järjekorras.
4. Kuva klientide kohta aadressi järgi sorteeritud teave.
17. variant
1. Kuvage nimekiri väärisesemetest, mille ostuhind on suurem kui teatud väärtus või garantiiaeg on suurem kui määratud arv.
2. Kuvage nende materiaalsete varade asukohtade loend, mille nimed sisaldavad määratud sõna.
3. Kuvage väärtuste summa määratud vahemikus koodiga.
4. Kuvage rahaliselt vastutavate isikute nimekiri koos tööle asumise kuupäevaga määratud vahemikus.
18. variant
1. Kuvage konkreetse tehniku tehtud remonditööde loend.
2. Kuvage loend tööetappidest, mis sisalduvad töös, mille pealkiri sisaldab määratud sõna.
3. Kuvatakse määratud vahemikus oleva koodiga tööde remonditööde etappide maksumuse summa.
4. Kuvage meistrite nimekiri koos töölevõtmise kuupäevaga määratud vahemikus.
19. variant
1. Kuvage konkreetse näidustusega ravimite loend.
2. Kuvage kviitunginumbrite loend, mille eest müüdi rohkem kui teatud arv ravimeid.
3. Näidake märgitud numbriga kviitungil müügikuupäev, summa, kassa nimi ja ravim.
4. Kuvage ravimite loend ja nende ravimite mõõtühikud, mille kogus pakendis on määratud numbrist suurem või ravimi kood väiksem kui teatud väärtus.
20. variant
1. Kuvage määratud ametikohaga töötajate nimekiri.
2. Kuvage nende dokumentide loend, mille sisu sisaldab määratud sõnafragmenti.
3. Määratud ajavahemikul registreeritud dokumentide puhul kuvada registreerimiskuupäev, dokumendi liik, täitja täisnimi ja täitmise fakt.
4. Kuvage registreeritud dokumentide loend koos määratud dokumenditüübi või dokumendikoodiga teatud vahemikus.
