shranjeni postopek - objekt baze podatkov, ki je nabor stavkov SQL, ki je enkrat preveden in shranjen na strežniku. Shranjene procedure so zelo podobne navadnim proceduram v jezikih visoke ravni, imajo lahko vhodne in izhodne parametre ter lokalne spremenljivke, lahko izvajajo številčne izračune in operacije nad znakovnimi podatki, katerih rezultate lahko pripišemo spremenljivkam in parametrom. Shranjene procedure lahko izvajajo standardne operacije baze podatkov (tako DDL kot DML). Poleg tega so v shranjenih procedurah možne zanke in veje, to pomeni, da lahko uporabljajo navodila za nadzor postopka izvajanja.
Shranjene procedure so podobne uporabniško definiranim funkcijam (UDF). Glavna razlika je v tem, da je uporabniško definirane funkcije mogoče uporabiti kot kateri koli drug izraz v poizvedbi SQL, medtem ko je treba shranjene procedure poklicati s funkcijo CALL:
Postopek klicanja (...)
IZVEDI postopek(...)
Shranjene procedure lahko vrnejo nize rezultatov, to je rezultate poizvedbe SELECT. Takšne nize rezultatov je mogoče obdelati s kazalci, z drugimi shranjenimi procedurami, ki vrnejo kazalec nabora rezultatov, ali z aplikacijami. Shranjene procedure lahko vsebujejo tudi deklarirane spremenljivke za ravnanje s podatki in kazalci, ki vam omogočajo zanko čez več vrstic v tabeli. Standard SQL ponuja izraze IF, LOOP, REPEAT, CASE in številne druge izraze za delo. Shranjene procedure lahko sprejmejo spremenljivke, vrnejo rezultate ali spremenijo spremenljivke in jih vrnejo, odvisno od tega, kje je spremenljivka deklarirana.
Izvedba shranjenih postopkov se od ene do druge DBMS razlikuje. Večina večjih ponudnikov baz podatkov jih podpira v takšni ali drugačni obliki. Glede na DBMS se lahko shranjene procedure izvajajo v različnih programskih jezikih, kot so SQL, Java, C ali C++. Shranjene procedure, napisane v ne-SQL, lahko same izvajajo poizvedbe SQL ali pa tudi ne.
Zadaj
Delitev logike z drugimi aplikacijami. Shranjeni postopki zajemajo funkcionalnost; to zagotavlja dostop do podatkov in povezljivost upravljanja med različnimi aplikacijami.
Izolirajte uporabnike iz tabel baze podatkov. To vam omogoča dostop do shranjenih postopkov, ne pa tudi do samih podatkov tabele.
Zagotavlja zaščitni mehanizem. V skladu s prejšnjo točko, če lahko dostopate do podatkov samo prek shranjenih postopkov, nihče drug ne more izbrisati vaših podatkov z ukazom SQL DELETE.
Izboljšana zmogljivost zaradi zmanjšanega omrežnega prometa. S shranjenimi procedurami je mogoče kombinirati več poizvedb.
Proti
Povečana obremenitev strežnika baze podatkov zaradi dejstva, da se večina dela opravi na strani strežnika, manj pa na strani odjemalca.
Moraš se veliko naučiti. Za pisanje shranjenih postopkov se boste morali naučiti sintakse izraza MySQL.
Svojo aplikacijsko logiko podvajate na dveh mestih: strežniška koda in koda za shranjene procedure, s čimer otežuje proces manipulacije podatkov.
Selitev iz enega DBMS v drugega (DB2, SQL Server itd.) lahko povzroči težave.
Namen in prednosti shranjenih postopkov
Shranjene procedure izboljšujejo zmogljivost, izboljšajo možnosti programiranja in podpirajo varnostne funkcije podatkov.
Namesto shranjevanja pogosto uporabljene poizvedbe se lahko odjemalci sklicujejo na ustrezno shranjeno proceduro. Ko pokličete shranjeno proceduro, strežnik takoj obdela njeno vsebino.
Poleg dejanskega izvajanja poizvedbe vam shranjene procedure omogočajo tudi izračune in manipulacijo s podatki – spreminjanje, brisanje, izvajanje DDL stavkov (ne v vseh DBMS!) in klic drugih shranjenih procedur, izvajanje kompleksne transakcijske logike. En stavek vam omogoča, da pokličete zapleten skript, ki ga vsebuje shranjena procedura, kar preprečuje pošiljanje na stotine ukazov po omrežju in zlasti potrebo po prenosu velikih količin podatkov od odjemalca do strežnika.
V večini DBMS se ob prvem zagonu shranjene procedure prevede (razčleni in ustvari načrt dostopa do podatkov). V prihodnosti bo njegova obdelava hitrejša. Oracle DBMS interpretira shranjeno proceduralno kodo, shranjeno v podatkovnem slovarju. Začenši z Oracle 10g je podprta tako imenovana nativna kompilacija (native compilation) kode shranjene procedure v C in nato v strojno kodo ciljnega stroja, po kateri se ob klicu shranjene procedure prevede njena prevedena objektna koda. neposredno izvedeno.
Možnosti programiranja
Ko ustvarite shranjeno proceduro, jo lahko kadar koli pokličete, kar zagotavlja modularnost in spodbuja ponovno uporabo kode. Slednje olajša vzdrževanje baze podatkov, saj postane izolirana od spreminjanja poslovnih pravil. Shranjeno proceduro lahko kadar koli spremenite, da bo skladen z novimi pravili. Po tem bodo vse aplikacije, ki ga uporabljajo, samodejno skladne z novimi poslovnimi pravili brez neposrednih sprememb.
Varnost
Uporaba shranjenih postopkov vam omogoča, da omejite ali popolnoma izključite neposreden uporabniški dostop do tabel baze podatkov, uporabnikom pa pustite le dovoljenja za izvajanje shranjenih postopkov, ki zagotavljajo posreden in strogo reguliran dostop do podatkov. Poleg tega nekateri DBMS podpirajo šifriranje besedila (zavijanje) shranjene procedure.
Te varnostne funkcije vam omogočajo, da strukturo baze podatkov izolirate od uporabnika, kar zagotavlja celovitost in zanesljivost baze podatkov.
Dejanja, kot je "injekcija SQL", so manj verjetna, ker dobro napisane shranjene procedure dodatno preverijo vhodne parametre, preden pošljejo poizvedbo v DBMS.
Izvajanje shranjenih postopkov
Shranjene procedure so običajno izdelane z uporabo jezika SQL ali njegove specifične izvedbe v izbrani DBMS. Na primer, za te namene ima DBMS Microsoft SQL Server jezik Transact-SQL, Oracle ima PL/SQL, InterBase in Firebird ima PSQL, PostgreSQL ima PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 - SQL / PL (angleščina), v Informixu - SPL. MySQL zelo natančno sledi standardu SQL:2003, njegov jezik pa je podoben SQL/PL.
V nekaterih DBMS je mogoče uporabiti shranjene procedure, napisane v katerem koli programskem jeziku, ki lahko ustvari neodvisne izvedljive datoteke, kot sta C++ ali Delphi. V terminologiji Microsoft SQL Server se ti postopki imenujejo razširjene shranjene procedure in so preprosto funkcije, ki jih vsebuje Win32-DLL. In na primer v Interbase in Firebird za funkcije, ki jih kliče iz DLL / SO, je določeno drugo ime - UDF (uporabniško definirana funkcija). V MS SQL 2005 je postalo mogoče pisati shranjene procedure v katerem koli jeziku .NET, razširjene shranjene procedure pa naj bi v prihodnosti opustili. Oracle DBMS pa omogoča pisanje shranjenih procedur v Javi. V IBM DB2 je pisanje shranjenih procedur in funkcij v običajnih programskih jezikih tradicionalen način, podprt že od začetka, proceduralna razširitev SQL pa je bila dodana tej DBMS šele precej pozno, potem ko je bila vključena v standard ANSI. Informix podpira tudi postopke Java in C.
V DBMS Oracle je mogoče shranjene procedure združiti v tako imenovane pakete. Paket je sestavljen iz dveh delov – specifikacije (angl. package specification), ki določa definicijo shranjene procedure, in telesa (angleško telo paketa), kjer se nahaja njena izvedba. Tako vam Oracle omogoča, da ločite vmesnik programske kode od njegove izvedbe.
V IBM DB2 je shranjene procedure mogoče združiti v module.
Sintaksa
USTVARI POSTOPEK `p2`()
VARNOSTNI DEFINER SQL
KOMENTAR "Postopek"
IZBERITE "Hello World!";
Prvi del kode ustvari shranjeno proceduro. Naprej - vsebuje neobvezne parametre. Nato sledi ime in končno telo samega postopka.
4 značilnosti shranjene procedure:
Jezik: Za namene prenosljivosti je privzeto SQL.
Deterministično: če postopek ves čas vrača enak rezultat in vzame iste vhodne parametre. To je za postopek replikacije in registracije. Privzeta vrednost NI DETERMINISTIČNA.
Varnost SQL: med klicem se preverjajo pravice uporabnika. INVOKER je uporabnik, ki kliče shranjeno proceduro. DEFINER je "ustvarjalec" postopka. Privzeta vrednost je DEFINER.
Komentar: za namene dokumentacije je privzeta vrednost ""
Klicanje shranjene procedure
CALL shranjeno_procedure_name (param1, param2, ....)
CALL procedure1(10, "niz parameter" , @parameter_var);
Spreminjanje shranjene procedure
MySQL ima stavek ALTER PROCEDURE za spreminjanje postopkov, vendar je primeren le za spreminjanje določenih značilnosti. Če morate spremeniti parametre ali telo postopka, ga morate izbrisati in znova ustvariti.
Odstranitevshranjenipostopkov
POSTOPEK DROP, ČE OBSTAJA p2;
To je preprost ukaz. Stavek IF EXISTS ujame napako, če tak postopek ne obstaja.
Parametri
CREATE PROCEDURE proc1(): prazen seznam parametrov
CREATE PROCEDURE proc1 (V imenu variante DATA-TYPE): en vhodni parameter. Beseda IN ni obvezna, ker so privzeti parametri IN (dohodni).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): en vrnjeni parameter.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): en parameter, tako vhod kot izhod.
Sintaksa za deklariranje spremenljivke izgleda takole:
DECLARE varname DATA-TYPE DEFAULT privzeta vrednost;
Cilj– naučite se ustvarjati in uporabljati shranjene procedure na strežniku baz podatkov.
1. Delajte skozi vse primere, analizirajte rezultate njihove izvedbe v pripomočku SQL Server Management Studio. Preverjanje, ali ustvarjeni postopki obstajajo v trenutni bazi podatkov.
2. Izpolnjevanje vseh primerov in nalog v okviru laboratorijskega dela.
3. Izpolnjevanje posameznih nalog po možnostih.
Za obvladovanje programiranja shranjenih procedur uporabljamo primer baze podatkov z imenom DB_Books, ki je nastal v laboratorijskem delu št. Pri izvajanju primerov in nalog bodite pozorni na ujemanje imen baze podatkov, tabel in drugih predmetov projekta.
Shranjene procedure so nabor ukazov, sestavljen iz enega ali več stavkov ali funkcij SQL in so shranjeni v bazi podatkov v prevedeni obliki.
Sistemski shranjeni postopki so zasnovani za izvajanje različnih upravnih dejanj. Skoraj vsa dejanja administracije strežnika se izvajajo z njihovo pomočjo. Lahko rečemo, da so sistemske shranjene procedure vmesnik, ki omogoča delo s sistemskimi tabelami. Sistemske shranjene procedure imajo predpono sp_, shranjene so v sistemski bazi podatkov in jih je mogoče poklicati v kontekstu katere koli druge baze podatkov.
Shranjene procedure po meri izvajajo določena dejanja. Shranjene procedure so popoln objekt baze podatkov. Posledično je vsaka shranjena procedura v določeni bazi podatkov, kjer se izvaja.
Začasno shranjene procedure obstajajo le kratek čas, nato pa jih strežnik samodejno uniči. Delimo jih na lokalne in globalne. Lokalne začasne shranjene procedure je mogoče poklicati samo iz povezave, na kateri so bile ustvarjene. Ko ustvarjate tak postopek, mu je treba dati ime, ki se začne z enim samim znakom #. Tako kot vsi začasni objekti se tudi shranjene procedure te vrste samodejno izbrišejo, ko uporabnik prekine povezavo, znova zažene ali ustavi strežnik. Globalne začasne shranjene procedure so na voljo za katero koli povezavo na strežniku, ki ima enak postopek. Če ga želite definirati, je dovolj, da mu date ime, ki se začne z znaki ##. Ti postopki se izbrišejo, ko se strežnik znova zažene ali ustavi ali ko se zapre povezava, v okviru katere so bili ustvarjeni.
Izdelava shranjene procedure vključuje reševanje naslednjih nalog: načrtovanje pravic dostopa. Ko ustvarjate shranjeno proceduro, ne pozabite, da bo imela enake pravice dostopa do objektov baze podatkov kot uporabnik, ki jo je ustvaril; definicija parametrov shranjene procedure, shranjene procedure imajo lahko vhodne in izhodne parametre; razvoj kode shranjene procedure. Koda procedure lahko vsebuje katero koli zaporedje ukazov SQL, vključno s klici drugih shranjenih procedur.
Sintaksa za ustvarjanje nove ali spreminjanje obstoječe shranjene procedure v zapisu MS SQL Server je:
( CREATE | ALTER ) PROC[ EDURE] ime_procedure [ ;število] [ ( @parameter_name datatype ) [ VARYING ] [ = PRIVZETEK ] [ IZHOD] ] [ ,... n] [ WITH ( PREVOJANJE | ŠIFRIRANJE | PONOVNO PREVOJANJE, ŠIFRIRANJE ) ] [ ZA REPLIKACIJO] KOT sql_statement [ ... n]Upoštevajte parametre tega ukaza.
Z uporabo predpon sp_, #, ## lahko ustvarjeno proceduro definiramo kot sistemsko ali začasno. Kot je razvidno iz sintakse ukaza, ni dovoljeno podati imena lastnika, kateremu bo ustvarjena procedura pripadala, kot tudi imena baze podatkov, kamor naj bo postavljena. Če želite torej shranjeno proceduro, ki jo izdelujete, postaviti v določeno bazo podatkov, morate zagnati ukaz CREATE PROCEDURE v kontekstu te baze podatkov. Ko dostopate do objektov iz iste baze podatkov iz telesa shranjene procedure, lahko uporabite skrajšana imena, torej brez podajanja imena baze podatkov. Če se želite sklicevati na objekte, ki se nahajajo v drugih bazah podatkov, je podajanje imena baze podatkov obvezno.
Za posredovanje vhodnih in izhodnih podatkov v ustvarjeni shranjeni proceduri se morajo imena parametrov začeti z znakom @. V eni shranjeni proceduri lahko podate več parametrov, ločenih z vejicami. Telo procedure ne sme uporabljati lokalnih spremenljivk, katerih imena so enaka imenom parametrov procedure. Vsak podatkovni tip SQL, vključno z uporabniško definiranimi, je primeren za določanje podatkovnega tipa parametrov shranjene procedure. Vendar pa se lahko podatkovni tip CURSOR uporablja samo kot izhodni parameter shranjene procedure, tj. s ključno besedo OUTPUT.
Prisotnost ključne besede OUTPUT pomeni, da je ustrezni parameter namenjen vrnitvi podatkov iz shranjene procedure. Vendar to ne pomeni, da parameter ni primeren za posredovanje vrednosti v shranjeno proceduro. Podajanje ključne besede OUTPUT naroči strežniku, da ob izstopu iz shranjene procedure dodeli trenutno vrednost parametra lokalni spremenljivki, ki je bila podana kot vrednost parametra ob klicu procedure. Upoštevajte, da je pri podajanju ključne besede OUTPUT vrednost ustreznega parametra pri klicu procedure mogoče nastaviti samo z uporabo lokalne spremenljivke. Vsi izrazi ali konstante, dovoljeni za redne parametre, niso dovoljeni. Ključna beseda VARYING se uporablja v povezavi z OUTPUT parametrom tipa CURSOR. Določa, da bo izhodni parameter nabor rezultatov.
Ključna beseda DEFAULT je vrednost, ki jo bo privzeto prevzel ustrezni parameter. Tako pri klicu procedure ne morete izrecno podati vrednosti ustreznega parametra.
Ker strežnik predpomni načrt izvedbe poizvedbe in prevedeno kodo, bodo ob naslednjem klicu postopka uporabljene že pripravljene vrednosti. Vendar pa je v nekaterih primerih še vedno potrebno ponovno prevesti kodo postopka. Če podate ključno besedo RECOMPILE, naročite sistemu, da vsakič, ko se pokliče, ustvari načrt izvajanja za shranjeno proceduro.
Možnost ZA REPLIKACIJO je potrebna pri podvajanju podatkov in vključitvi generirane shranjene procedure kot članka v publikacijo. Ključna beseda ENCRYPTION naroči strežniku, da šifrira kodo shranjene procedure, ki lahko zagotovi zaščito pred uporabo lastniških algoritmov, ki izvajajo shranjeno proceduro. Ključna beseda AS je postavljena na začetek samega telesa shranjene procedure. V telesu procedure je mogoče uporabiti skoraj vse ukaze SQL, deklarirati je mogoče transakcije, nastaviti zaklepanje in priklicati druge shranjene procedure. Shranjeno proceduro lahko zapustite z ukazom RETURN.
DROP PROCEDURE (ime_procedura) [ ,... n]
Za izvedbo shranjene procedure se uporablja naslednji ukaz: [ [ EXEC [ UTE] ime_procedure [ ;številka] [ [ @ime_parametra= ] ( vrednost | @ime_spremenljivke) [ IZHOD ] | [ PRIvzeto ] ] [ ,...n]
Če klic shranjene procedure ni edini ukaz v paketu, je prisotnost ukaza EXECUTE obvezna. Poleg tega je ta ukaz potreben za klic postopka iz telesa drugega postopka ali sprožilca.
Uporaba ključne besede OUTPUT v klicu procedure je dovoljena samo za parametre, ki so bili deklarirani, ko je bila procedura ustvarjena s ključno besedo OUTPUT.
Ko pokličete postopek s ključno besedo DEFAULT za parameter, bo uporabljena privzeta vrednost. Seveda je podana beseda DEFAULT dovoljena samo za tiste parametre, za katere je definirana privzeta vrednost.
Iz sintakse ukaza EXECUTE lahko vidite, da se imena parametrov lahko izpustijo pri klicanju procedure. Vendar mora v tem primeru uporabnik določiti vrednosti za parametre v istem vrstnem redu, v katerem so bili navedeni, ko je bil postopek ustvarjen. Parametru ne morete dodeliti privzete vrednosti tako, da ga preprosto izpustite v oštevilčenju. Če želite izpustiti parametre, ki imajo privzeto vrednost, zadostuje izrecno podajanje imen parametrov pri klicu shranjene procedure. Poleg tega lahko na ta način navedete parametre in njihove vrednosti v poljubnem vrstnem redu.
Upoštevajte, da so ob klicu postopka navedena bodisi imena parametrov z vrednostmi bodisi samo vrednosti brez imena parametra. Njihova kombinacija ni dovoljena.
Omogoča izhod iz postopka na kateri koli točki glede na določen pogoj, omogoča pa tudi prenos rezultata izvedbe postopka kot številko, po kateri lahko presodite kakovost in pravilnost postopka. Primer ustvarjanja postopka brez parametrov:
CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) IZ Books GOvaja 1.
EXEC Count_BooksPreverite rezultat.
Primer ustvarjanja procedure z vhodnim parametrom:
USTVARJAJTE POSTOPEK Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) IZ knjig KJE Strani>= @Count_pages GO2. naloga. Ustvarite ta postopek v razdelku Shranjene procedure baze podatkov DB_Books z uporabo pripomočka SQL Server Management Studio. Zaženite ga z ukazom
EXEC Število_knjig_Strani 100Preverite rezultat.
Primer ustvarjanja procedure z vhodnimi parametri:
USTVARJANJE POSTOPKA Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) IZ knjig KJE Strani>= @Count_pages IN Title_book LIKE @Title GO3. naloga. Ustvarite ta postopek v razdelku Shranjene procedure baze podatkov DB_Books z uporabo pripomočka SQL Server Management Studio. Zaženite ga z ukazom
EXEC Count_Books_Title 100, "P%"Preverite rezultat.
Primer ustvarjanja procedure z vhodnimi in izhodnimi parametri:
USTVARJANJE POSTOPKA Count_Books_Itogo @Count_pages INT , @Title CHAR (10) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) IZ knjig KJE Strani>= @Count_pages IN Title_book LIKE @Title GO4. naloga. Ustvarite ta postopek v razdelku Shranjene procedure baze podatkov DB_Books z uporabo pripomočka SQL Server Management Studio. Zaženite z nizom ukazov:
Sql> Razglasi @q kot int EXEC Count_Books_Itogo 100, "P%", @q izhod izberite @q
Preverite rezultat.
Primer ustvarjanja procedure z vhodnimi parametri in RETURN:
CREATE PROCEDURE checkname @param INT AS IF (IZBERITE Ime_avtorja IZ avtorjev KJE Code_author = @param) = "Pushkin A.S." VRANJE 1 DRUGO Vračilo 25. naloga. Ustvarite ta postopek v razdelku Shranjene procedure baze podatkov DB_Books z uporabo pripomočka SQL Server Management Studio. Zaženite ga z ukazi:
DECLARE @return_status INT EXEC @return_status = ime preverjanja 1 SELECT "Return Status" = @return_statusPrimer ustvarjanja postopka brez parametrov za podvojitev vrednosti ključnega polja v tabeli Nakupi:
CREATE PROC update_proc AS UPDATE Nakupi SET Code_purchase = Code_purchase* 26. naloga. Ustvarite ta postopek v razdelku Shranjene procedure baze podatkov DB_Books z uporabo pripomočka SQL Server Management Studio. Zaženite ga z ukazom
EXEC update_procPrimer postopka z vhodnim parametrom za pridobitev vseh informacij o določenem avtorju:
CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @k7. naloga.
EXEC select_author "Pushkin A.S." ali select_author @k= "Puškin A.S." ali EXEC select_author @k= "Puškin A.S."Primer ustvarjanja postopka z vhodnim parametrom in privzeto vrednostjo za povečanje vrednosti ključnega polja v tabeli Nakupi za določeno število krat (privzeto 2-krat):
CREATE PROC update_proc @p INT = 2 AS UPDATE Nakupi SET Code_purchase = Code_purchase * @pPostopek ne vrne nobenih podatkov.
8. naloga. Ustvarite ta postopek v razdelku Shranjene procedure baze podatkov DB_Books z uporabo pripomočka SQL Server Management Studio. Zaženite ga z ukazi:
EXEC update_proc 4 ali EXEC update_proc @p = 4 ali EXEC update_proc --uporabljena bo privzeta vrednost.Primer izdelave procedure z vhodnimi in izhodnimi parametri. Ustvarite postopek za določitev števila dokončanih naročil v določenem obdobju:
CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT IZHOD KOT SELECT @c= COUNT (Code_purchase) IZ Nakupov KJE Date_order MED @d1 IN @d2 NASTAVI @c = ISNULL(@c, 0 )Naloga 9. Ustvarite ta postopek v razdelku Shranjene procedure baze podatkov DB_Books z uporabo pripomočka SQL Server Management Studio. Zaženite ga z ukazi:
DECLARE @c2 INT EXEC count_purchases '01- junij- 2006 ', '01- jul- 2006 ', @c2 IZBIRA IZHOD @c2Variante nalog za laboratorijsko delo št. 4
Splošne določbe. V pripomočku SQL Server Management Studio ustvarite novo stran za kodo (gumb Ustvari poizvedbo). Programsko aktivirajte ustvarjeno bazo podatkov DB_Books z uporabo stavka Use. Ustvarite shranjene procedure z uporabo stavkov Ustvari proceduro in sami definirajte imena postopkov. Vsak postopek bo izvedel eno poizvedbo SQL, ki je bila izvedena v drugem laboratoriju. Poleg tega je treba kodo SQL poizvedb spremeniti tako, da lahko posredujejo vrednosti polj, ki se iščejo.
Na primer, prvotna naloga in poizvedba v laboratoriju #2:
/*Izberite v imeniku dobaviteljev (tabela Dostave) imena podjetij, telefonske številke in TIN (polja Ime_podjetje, Telefon in INN), katerih ime podjetja (Ime_podjetje_polja) je OAO MIR.
IZBERITE Ime_podjetje, Telefon, INN IZ Dostav KJE Ime_podjetje = "JSC MIR"*/ – V tem delu bo ustvarjen postopek:
CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Ime_podjetje, Telefon, INN IZ Dostav KJE Ime_podjetje = @comp– Za začetek postopka uporabite ukaz:
EXEC select_name_company "JSC MIR"Seznam opravil
Ustvarite nov program v SQL Server Management Studio. Programsko aktivirajte posamezno bazo podatkov, ustvarjeno v Laboratoriju #1, z uporabo izjave. Ustvarite shranjene procedure z uporabo stavkov Ustvari proceduro in sami definirajte imena postopkov. Vsak postopek bo izvedel eno poizvedbo SQL, ki so po možnostih predstavljene kot ločene naloge.
1. možnost
1. Prikažite seznam zaposlenih, ki imajo vsaj enega otroka.
2. Prikaži seznam otrok, ki so prejeli darila v določenem obdobju.
3. Prikažite seznam staršev, ki imajo mladoletne otroke.
4. Prikaži informacije o darilih z vrednostjo, ki je večja od navedenega števila, razvrščenih po datumu.
2. možnost
1. Prikažite seznam napeljave z določeno vrsto.
2. Prikažite število popravljenih naprav in skupne stroške popravil od navedenega poveljnika.
3. Prikažite seznam lastnikov naprav in število njihovih zadetkov, razvrščenih po številu zadetkov v padajočem vrstnem redu.
4. Prikažite informacije o mojstrih z rangom, višjim od navedenega števila, ali z datumom najema, ki je krajši od navedenega datuma.
3. možnost
2. Prikažite seznam prodajnih kod, za katere so bile rože prodane za znesek, ki je večji od navedenega števila.
3. Prikažite datum prodaje, znesek, prodajalca in cvet za navedeno prodajno kodo.
4. Prikažite seznam barv in razvrstite za rože, katerih višina je večja od navedenega števila ali cvetenje.
4. možnost
1. Prikažite seznam zdravil z določeno indikacijo za uporabo.
2. Prikažite seznam rokov dostave, za katere je bilo prodanih več kot določeno število istoimenskega zdravila.
3. Prikažite datum dostave, količino, polno ime upravljavca pri dobavitelju in ime zdravila s kodo prejema, ki je večja od navedene številke.
5. možnost
2. Prikažite seznam razgrajene opreme iz določenega razloga.
3. Prikažite datum prejema, ime opreme, polno ime odgovorne osebe in datum razgradnje opreme, ki je bila razgrajena v določenem obdobju.
4. Prikažite seznam opreme z določeno vrsto ali z datumom prejema, ki je večji od določene vrednosti
6. možnost
1. Prikažite seznam jedi z večjo težo od navedenega števila.
2. Prikažite seznam izdelkov, katerih imena vsebujejo določen delček besede.
3. Prikaz prostornine izdelka, ime jedi, ime izdelka s kodo jedi od podane začetne vrednosti do določene končne vrednosti.
4. Prikažite vrstni red kuhanja jedi in ime jedi s količino ogljikovih hidratov, ki je večja od določene vrednosti, ali številom kalorij, ki je večje od navedene vrednosti.
Možnost 7
1. Prikažite seznam zaposlenih z določenim delovnim mestom.
3. Prikažite datum registracije, vrsto dokumenta, polno ime registrarja in ime organizacije za dokumente, registrirane v določenem obdobju.
4. Prikažite seznam registriranih dokumentov z določeno vrsto dokumenta ali z datumom registracije, ki je višji od navedene vrednosti.
Možnost 8
1. Prikažite seznam zaposlenih z navedenim razlogom za odhod.
3. Prikažite datum registracije, razlog za odpuščanje, polno ime zaposlenega za dokumente, registrirane v določenem obdobju.
Možnost 9
1. Prikažite seznam zaposlenih, ki so vzeli dopust določene vrste.
2. Prikaz seznama dokumentov z datumom registracije v določenem roku.
3. Za dokumente, registrirane v določenem obdobju, prikažite datum prijave, vrsto dopusta, polno ime zaposlenega.
4. Prikažite seznam registriranih dokumentov s kodo dokumenta v določenem obsegu.
Možnost 10
1. Prikažite seznam zaposlenih z določenim delovnim mestom.
2. Prikažite seznam dokumentov, ki vsebujejo določen delček besede.
3. Prikažite datum registracije, vrsto dokumenta, polno ime pošiljatelja in ime organizacije za dokumente, registrirane v določenem obdobju.
4. Prikaz seznama registriranih dokumentov z določeno vrsto dokumenta ali s kodo dokumenta, ki je manjša od določene vrednosti.
Možnost 11
1. Prikažite seznam zaposlenih, dodeljenih na določeno delovno mesto.
2. Prikaz seznama dokumentov z datumom registracije v določenem roku.
3. Prikažite datum registracije, delovno mesto, polno ime zaposlenega za dokumente, registrirane v določenem obdobju.
4. Prikažite seznam registriranih dokumentov s kodo dokumenta v določenem obsegu.
Možnost 12
3. Prikažite seznam oseb, ki so najeli opremo, in število njihovih zahtev, razvrščenih po številu zahtev v padajočem vrstnem redu.
Možnost 13
1. Prikažite seznam opreme z določeno vrsto. 2. Prikažite seznam opreme, ki jo je določen zaposleni razgradil.
3. Prikažite količino razgrajene opreme, razvrščeno po vrsti opreme.
4. Prikažite informacije o zaposlenih z datumom najema, ki je višji od določenega datuma.
Možnost 14
1. Prikažite seznam cvetov z določeno vrsto listov.
2. Prikažite seznam kod prejemnic, za katere so bile rože prodane za zneske, ki so višji od določene vrednosti.
3. Prikažite datum prejema, znesek, imena dobavitelja in barve za določeno kodo dobavitelja.
4. Prikažite seznam cvetov in sorto za rože, katerih višina je večja od določenega števila ali cvetoče.
Možnost 15
1. Prikažite seznam strank, ki so prispele v sobe v določenem obdobju.
2. Prikažite skupni znesek plačil za sobe za vsako stranko.
3. Prikažite datum prihoda, vrsto sobe, polno ime strank, registriranih v določenem obdobju.
4. Prikaz seznama registriranih strank v sobah določenega tipa.
Možnost 16
1. Prikažite seznam opreme z določeno vrsto.
2. Prikaz seznama opreme, ki jo je določena stranka najela.
3. Prikažite seznam oseb, ki so najeli opremo, in število njihovih zahtev, razvrščenih po številu zahtev v padajočem vrstnem redu.
4. Prikažite informacije o strankah, razvrščenih po naslovih.
Možnost 17
1. Prikažite seznam dragocenosti, katerih nabavna cena je višja od določene vrednosti ali garancijske dobe, daljše od določenega števila.
2. Prikaz seznama lokacij materialnih sredstev, v imenu katerih je navedena beseda.
3. Prikaži vsoto stroškov dragocenosti s kodo v določenem obsegu.
4. Prikaz seznama finančno odgovornih oseb z datumom zaposlitve v določenem območju.
Možnost 18
1. Prikažite seznam popravil, ki jih je opravil določen mojster.
2. Prikaz seznama delovnih stopenj, vključenih v delo, v naslovu katerih je navedena beseda.
3. Prikažite vsoto stroškov faz popravil za dela s kodo v določenem območju.
4. Prikažite seznam mojstrov z datumom zaposlitve v določenem obsegu.
Možnost 19
1. Prikažite seznam zdravil s posebno indikacijo.
2. Prikažite seznam številk računov, ki so prodali več kot določeno število zdravil.
3. Prikažete datum prodaje, znesek, polno ime blagajne in zdravilo za račun z navedeno številko.
4. Prikaz seznama zdravil in merskih enot za zdravila s količino pakiranja, ki je večja od navedene številke ali kodo zdravila, manjšo od določene vrednosti.
Možnost 20
1. Prikažite seznam zaposlenih z določenim delovnim mestom.
2. Prikažite seznam dokumentov, ki vsebujejo določen delček besede.
3. Prikaz datuma registracije, vrste dokumenta, polnega imena izvršitelja in dejstva izvršitve za dokumente, registrirane v določenem obdobju.
4. Prikaz seznama registriranih dokumentov z določeno vrsto dokumenta ali s kodo dokumenta v določenem obsegu.
Shranjene procedure
Predmet tega poglavja je eno najzmogljivejših orodij, ki jih ponujamo razvijalcem aplikacij baze podatkov InterBase za implementacijo poslovne logike. Shranjene procedure (angleščina, stoied procedures) omogočajo implementacijo pomembnega dela logike aplikacije na ravni baze podatkov in s tem povečajo zmogljivosti celotne aplikacije, centralizirati obdelavo podatkov in zmanjšati količino kode, ki je potrebna za opravljanje nalog. Skoraj vsaka dovolj zapletena aplikacija baze podatkov zahteva uporabo shranjenih postopkov.
Poleg teh dobro znanih prednosti uporabe shranjenih procedur, ki so skupne večini sistemov relacijskih baz podatkov, lahko shranjene procedure InterBase igrajo vlogo skoraj popolnih naborov podatkov, kar vam omogoča uporabo rezultatov, ki jih vrnejo v običajnih poizvedbah SQL.
Razvijalci začetniki pogosto razmišljajo o shranjenih procedurah kot o naboru specifičnih poizvedb SQL, ki nekaj naredijo v bazi podatkov, in obstaja mnenje, da je delo s shranjenimi procedurami veliko težje kot implementacija iste funkcionalnosti v odjemalski aplikaciji, v visoko- ravni jezik
Kaj pravzaprav so shranjene procedure v InterBase?
Shranjena procedura (SP) je del metapodatkov baze podatkov, ki je podprogram, preveden v interni prikaz InterBase, napisan v posebnem jeziku, katerega prevajalnik je vgrajen v jedro strežnika InteiBase.
Shranjeno proceduro lahko pokličete iz odjemalskih aplikacij, iz sprožilcev in iz drugih shranjenih procedur. Shranjena procedura se izvaja znotraj strežniškega procesa in lahko manipulira s podatki v bazi podatkov, kot tudi vrne rezultate njenega izvajanja odjemalcu, ki jo je poklical (tj. trigger, HP, aplikacija)
Osnova zmogljivih funkcij, ki so lastne HP-ju, je proceduralni programski jezik, ki vključuje tako spremenjene običajne stavke SQL, kot so INSERT, UPDATE in SELECT, kot tudi orodja za razvejanje in zanke (IF, WHILE), kot tudi orodja za obravnavo napak. . in izjeme Jezik shranjene procedure vam omogoča implementacijo kompleksnih algoritmov za delo s podatki, zaradi poudarka na delu z relacijskimi podatki pa so SP-ji veliko bolj kompaktni kot podobni postopki v tradicionalnih jezikih.
Treba je opozoriti, da se za sprožilce uporablja isti programski jezik, z izjemo številnih funkcij in omejitev. Razlike med podmnožico jezika, ki se uporablja v sprožilcih, in jezikom HP-ja so podrobno obravnavane v poglavju Sprožilci (1. del).
Primer preproste shranjene procedure
Čas je, da ustvarimo prvo shranjeno proceduro in jo uporabimo kot primer za preučevanje procesa ustvarjanja shranjenih procedur. Najprej pa je treba povedati nekaj besed o delu s shranjenimi procedurami.Dejstvo je, da HP svoj sloves kot nejasno in neprijetno orodje dolguje izjemno slabim standardnim orodjem za razvoj in odpravljanje napak shranjenih procedur. V dokumentaciji InterBase je priporočljivo izdelati procedure s skriptnimi datotekami SQL, ki vsebujejo besedilo CP, ki se napajajo v isql interpreter in s tem ustvarijo in spremenijo CP. Če pride do napake, bo isql prikazal sporočilo, na katerem vrstico skriptne datoteke SQL je prišlo do napake. Popravite napako in ponovite vse znova. Odpravljanje napak v sodobnem pomenu besede, torej sledenje izvajanju, z možnostjo ogleda vmesnih vrednosti spremenljivk, se sploh ne razpravlja. Očitno ta pristop ne prispeva k rasti privlačnosti shranjenih postopkov v očeh razvijalca.
Vendar pa poleg standardnega minimalističnega pristopa k razvoju HP<_\ществ\ют также
инструменты сторонних разработчиков, которые делают работу с хранимыми
процедурами весьма удобной Большинство универсальных продуктов для работы с
InterBase, перечисленных в приложении "Инструменты администратора и разработчика
InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем
обязательно воспользоваться одним из этих инструментов для работы с хранимыми
процедурами и изложение материала будем вести в предположении, что у вас имеется
удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Sintaksa shranjenih postopkov je opisana kot sledi:
CREATE PROCEDURE ime
[ (tip podatkov parametra [, tip podatkov parametra ...]) ]
)]
AS
< procedure_body> =
[
<
block>
< vanable_declaration_list>
=
DECLARE VARIABLE tip podatkov var;
ZAČETI
< compound_statement>
[< compound_statement> ...]
KONEC
< compound_statement> =
(
Izgleda precej obsežno in je lahko celo okorno, v resnici pa je vse zelo preprosto. Da bi postopoma obvladali sintakso, si oglejmo primere, ki se postopoma zapletajo.
Torej, tukaj je primer zelo preproste shranjene procedure, ki vzame dve številki kot vhod, ju sešteje in vrne rezultat:
CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg DVOJNA NATAKNOST)
VRAČI (rezultat DOUBLE PRECISION)
AS
ZAČETI
Rezultat=prvi_arg+drugi_arg;
ZAUSTAVI;
KONEC
Kot lahko vidite, je vse preprosto: po ukazu CREATE PROCEDURE je navedeno ime na novo ustvarjene procedure (ki mora biti edinstveno znotraj baze podatkov) - v tem primeru SP_Add, nato vhodna parametra XP - first_arg in second_arg - so navedeni v oklepajih, ločeni z vejicami, kar označuje njihove vrste.
Seznam vhodnih parametrov je izbirni del stavka CREATE PROCEDURE - obstajajo primeri, ko procedura prejme vse podatke za svoje delo prek poizvedb v tabele znotraj telesa procedure.
Shranjene procedure uporabljajo kakršne koli skalarne podatkovne tipe InteiBase Brez uporabe nizov in uporabniško definiranih tipov - domen
Sledi ključna beseda RETURNS, po kateri so vrnjeni parametri navedeni v oklepajih, kar označuje njihove vrste – v tem primeru samo enega – Rezultat.
Če postopek ne bi smel vrniti parametrov, manjkata beseda RETURNS in seznam vrnjenih parametrov.
RETURNSQ sledi ključna beseda AS. Preden gre ključna beseda AS naslov, in po njem - techo postopkov.
Telo shranjene procedure je seznam deklaracij njenih notranjih (lokalnih) spremenljivk (če obstajajo, je podrobneje obravnavano spodaj), ločenih s podpičjem (;), in blok stavkov, priloženih v oklepajih STAVKA BEGIN END. V tem primeru je telo CP zelo preprosto - prosimo, da dodamo dva vhodna argumenta in dodelimo njihov rezultat izhodu, nato pa pokličemo ukaz SUSPEND. Malo kasneje bomo razložili bistvo dejanja tega ukaza, za zdaj pa le opozorimo, da je treba vrnjene parametre posredovati tja, od koder je bila shranjena procedura poklicana.
Ločevalniki v shranjenih procedurah
Upoštevajte, da se stavek znotraj postopka konča s podpičjem (;). Kot veste, je podpičje standardno ločilo ukazov v SQL - to je signal tolmaču SQL, da je bilo besedilo ukaza vneseno v celoti in da ga je treba obdelati. Ali se ne bi izkazalo, da bo tolmač SQL, potem ko je našel podpičje na sredini SP-ja, ocenil, da je bil ukaz vnesen v celoti, in bo poskušal izvesti del shranjene procedure? Ta domneva ni brez pomena. Dejansko, če ustvarite datoteko, v katero zapišete zgornji primer, dodate ukaz za povezavo z bazo podatkov in poskusite izvesti ta skript SQL s tolmačem isql, bo vrnjena napaka, povezana z nepričakovanim, po mnenju tolmača, koncem ukaz za ustvarjanje shranjene procedure. Če ustvarite shranjene procedure s skriptnimi datotekami SQL, ne da bi uporabljali specializirana orodja za razvijalce InterBase, morate pred vsakim ukazom za ustvarjanje CP (enako velja za sprožilce) spremeniti ločilo ukazov skripta v drug znak, ki ni podpičje, in po besedilo HP ga obnovi nazaj. Ukaz isql za spremembo ločila stavkov SQL izgleda takole:
NASTAVI TERMIN
Za tipičen primer ustvarjanja shranjene procedure je videti tako:
NASTAVI TERMIN ^;
CREATE PROCEDURE some_procedure
... . .
KONEC
^
DOLOČI TERMIN ;^
Klicanje shranjene procedure
Toda nazaj k naši shranjeni proceduri. Zdaj, ko je bil ustvarjen, ga moramo nekako poklicati, ji posredovati parametre in dobiti vrnjene rezultate. To je zelo enostavno narediti - samo napišite poizvedbo SQL v naslednji obliki:
IZBERI*
IZ Sp_add(181.35, 23.09)
Ta poizvedba nam bo vrnila eno vrstico, ki vsebuje samo eno polje Rezultat, ki bo vsebovalo vsoto številk 181,35 in 23,09, to je 204,44.
Tako lahko naš postopek uporabimo v navadnih poizvedbah SQL, ki se izvajajo tako v odjemalskih programih kot v drugih SP-jih ali sprožilcih. Ta uporaba našega postopka je omogočena z uporabo ukaza SUSPEND na koncu shranjene procedure.
Dejstvo je, da v InterBase (in v vseh njegovih klonih) obstajata dve vrsti shranjenih procedur: izbirne procedure in izvedljive procedure. Razlika v tem, kako delujeta ti dve vrsti CP-jev, je v tem, da postopki pridobivanja običajno vrnejo veliko nizov izhodnih parametrov, razvrščenih vrstico za vrstico, ki izgledajo kot nabor podatkov, izvršljivi postopki pa sploh ne morejo vrniti parametrov ali pa vrnejo samo en niz. izhodnih parametrov, navedenih v Vrnitvi, kjer je ena vrstica parametrov. Izbirne procedure se kličejo v poizvedbah SELECT, izvedljive procedure pa se kličejo z ukazom EXECUTE PROCEDURE.
Obe vrsti shranjenih procedur imata enako sintakso ustvarjanja in sta formalno enaki, tako da lahko v poizvedbi SELECT pokličete katero koli izvedljivo proceduro in katero koli izbirno proceduro lahko pokličete z uporabo EXECUTE PROCEDURE. Vprašanje je, kako se bo HP obnašal pri različnih vrstah klicev. Z drugimi besedami, razlika je v zasnovi postopka za določeno vrsto klica. To pomeni, da je izbrana procedura posebej ustvarjena za klic iz poizvedbe SELECT, izvedljiva procedura pa je posebej ustvarjena za klic z uporabo EXECUTE PROCEDURE. Poglejmo, kakšne so razlike v zasnovi teh dveh vrst HP-jev.
Da bi razumeli, kako poteka izbirni postopek, se morate nekoliko poglobiti v teorijo. Predstavljajmo si običajno poizvedbo SQL, kot je SELECT ID, NAME FROM Table_example. Kot rezultat njegove izvedbe dobimo na izhodu tabelo, sestavljeno iz dveh stolpcev (ID in IME) in določenega števila vrstic (enako številu vrstic v tabeli Table_example). Tabela, vrnjena kot rezultat te poizvedbe, se imenuje tudi nabor podatkov SQL. Pomislimo, kako se nabor podatkov oblikuje med izvajanjem te poizvedbe. Strežnik, ko je prejel poizvedbo, določi, katerim tabelam pripada, nato ugotovi, kateri podmnožica zapise iz teh tabel je treba vključiti v rezultat poizvedbe. Nato strežnik prebere vsak zapis, ki ustreza rezultatom poizvedbe, iz njega izbere zahtevana polja (v našem primeru sta to ID in IME) in ju pošlje odjemalcu. Nato se postopek znova ponovi – in tako naprej za vsak izbrani zapis.
Vsa ta digresija je potrebna, da dragi bralec razume, da se vsi nabori podatkov SQL oblikujejo vrstico za vrstico, tudi v shranjenih procedurah! Glavna razlika med izbranimi in izvedljivimi postopki je v tem, da so prvi zasnovani tako, da vrnejo več vrstic, medtem ko so slednji zasnovani tako, da vrnejo samo eno. Zato se uporabljajo drugače: izbirni postopek se pokliče z ukazom SELECT, ki "zahteva", da postopek vrne vse zapise, ki jih lahko vrne. Izvedljivi postopek se pokliče z uporabo EXECUTE PROCEDURE, ki "izvleče" samo eno vrstico iz CP, ostale pa ignorira (tudi če obstajajo!)
Oglejmo si primer postopka izbire, da bo bolj jasno. Za odpuščanje ustvarimo shranjeno proceduro, ki deluje natanko tako kot poizvedba SELECT ID, NAME FROM Table_Example, to pomeni, da preprosto izbere polji ID in NAME iz celotne tabele. Tukaj je ta primer:
USTVARJITE POSTOPEK Simple_Select_SP
VRAČILA (
procID INTEGER,
procNAME VARCHAR(80))
AS
ZAČETI
ZA
IZBERI ID, IME IZ tabele_primer
INTO:procID, :procNAME
NAREDI
ZAČETI
ZAUSTAVI;
KONEC
KONEC
Oglejmo si dejanja tega postopka, imenovanega Simple_Select_SP. Kot lahko vidite, nima vhodnih parametrov in ima dva izhodna parametra - ID in NAME. Najbolj zanimivo je seveda v telesu postopka. Tukaj se uporablja konstrukcija FOR SELECT:
ZA
IZBERI ID, IME IZ tabele_primer
INTO:procID, :procNAME
NAREDI
ZAČETI
/*naredi nekaj s spremenljivkama procID in procName*/
KONEC
Ta del kode pomeni naslednje: za vsako vrstico, izbrano iz tabele Table_example, vstavite izbrane vrednosti v spremenljivki procID in procName in nato izvedite nekaj dejanj na teh spremenljivkah.
Lahko si narišete presenečen obraz in vprašate: »Spremenljivke? Katere druge spremenljivke 9?« V tem poglavju je nekakšno presenečenje, da lahko spremenljivke uporabljamo v shranjenih procedurah. V XP lahko deklarirate obe lastni lokalni spremenljivki znotraj procedure in uporabite vhodne in izhodne parametre kot spremenljivke.
Če želite v shranjeni proceduri deklarirati lokalno spremenljivko, morate njeno deklaracijo postaviti za ključno besedo AS in pred prvo besedo BEGIN. Deklaracija lokalne spremenljivke je videti tako:
RAZGLASITE SPREMENLJIVKO
Če želite na primer razglasiti celoštevilsko lokalno spremenljivko Mylnt, bi med AS in BEGIN vstavili naslednjo deklaracijo
RAZGLASITE SPREMENLJIVKO MyInt INTEGER;
Spremenljivke v našem primeru se začnejo z dvopičjem. To je storjeno, ker se do njih dostopa znotraj ukaza FOR SELECT SQL, zato morate za razlikovanje med polji v tabelah, ki se uporabljajo v SELECT, in spremenljivkami, pred slednjimi postaviti dvopičje. Konec koncev imajo spremenljivke lahko popolnoma enako ime kot polja v tabelah!
Toda dvopičje pred imenom spremenljivke je treba uporabiti samo v poizvedbah SQL. Zunaj besedil je spremenljivka dostopna brez dvopičja, na primer:
procName="Nekatero ime";
Toda nazaj k telesu našega postopka. Klavzula FOR SELECT ne vrne podatkov v obliki tabele – nabora podatkov, temveč eno vrstico naenkrat. Vsako vrnjeno polje mora biti postavljeno v svojo spremenljivko: ID => procID, NAME => procName. V delu DO se te spremenljivke pošljejo odjemalcu, ki je poklical postopek p> z ukazom SUSPEND.
Tako se ukaz FOR SELECT... DO vrti po zapisih, izbranih v delu ukaza SELECT. V telesu zanke, ki jo tvori del DO, se naslednji ustvarjeni zapis prenese na odjemalca z ukazom SUSPEND.
Torej je izbirni postopek zasnovan tako, da vrne eno ali več vrstic, za katere je znotraj telesa CP organizirana zanka, ki izpolni nastale spremenljive parametre. In na koncu telesa te zanke je ukaz SUSPEND, ki bo odjemalcu vrnil naslednjo vrstico podatkov.
Zanke in operaterji vej
Poleg ukaza FOR SELECT... DO, ki organizira cikel skozi zapise določenega izbora, obstaja še ena vrsta cikla - WHILE...DO, ki vam omogoča, da organizirate cikel na podlagi preverjanja morebitnih pogojev. Tukaj je primer HP-ja, ki uporablja zanko WHILE..DO. Ta postopek vrne kvadrate celih števil od 0 do 99:
USTVARI PROCEDJRE QUAD
VRAČILA (KVADRATNO CELO)
AS
RAZGLASITE SPREMENLJIVKO I CELO;
ZAČETI
i = 1;
Medtem ko jaz<100) DO
ZAČETI
KVADRAT=I*I;
I=I+1;
ZAUSTAVI;
KONEC
KONEC
Kot rezultat izvedbe poizvedbe SELECT FROM QUAD bomo dobili tabelo, ki vsebuje en stolpec QUADRAT, v katerem bodo kvadrati celih števil od 1 do 99
Poleg iteracije rezultatov poizvedbe SQL in klasične zanke jezik shranjene procedure uporablja stavek IF...THEN..ELSE, ki vam omogoča, da organizirate razvejanje glede na izvedbo katerega koli \pogoja. Njegova sintaksa je podobno kot večina razvejanih stavkov v programskih jezikih na visoki ravni, kot sta Pascal in C.
Poglejmo si bolj zapleten primer shranjene procedure, ki naredi naslednje.
Najprej definirajmo ime HP-ja, pa tudi vhodne in izhodne parametre, vse to je zapisano v glavi shranjene procedure
USTVARJANJE POSTOPKA Povečanje cen(
Percent2 Povečaj DVOJNO NATOČNOST)
VRAČILA (ID INTEGER, IME VARCHAR(SO), new_price DOUBLE
PRECIZNOST KOT
Postopek se bo imenoval IncreasePrices, ima en vhodni parameter Peiceni21nciease, ki je tipa DOUBLE PRECISION, in 3 izhodne parametre - ID, NAME in new_pnce. Upoštevajte, da imata prva dva izhodna parametra enaka imena kot polja v tabeli Table_example, s katero bomo delali.To dovoljujejo pravila jezika shranjene procedure.
Zdaj moramo deklarirati lokalno spremenljivko, ki bo uporabljena za shranjevanje povprečne vrednosti. Deklaracija Ego bo videti takole:
DECLARE VARIABLE avg_price DVOJNA NATAKNOST;
Zdaj pa pojdimo na telo shranjene procedure. Odprimo telo CP
ključna beseda ZAČNI.
Najprej moramo izvesti prvi korak našega algoritma - izračunati povprečno ceno. Za to bomo uporabili naslednjo poizvedbo:
IZBERI AVG(cena_l)
IZ tabele_primer
INTO:avg_price,-
Ta poizvedba uporablja agregatno funkcijo AVG, ki vrne povprečno vrednost polja PRICE_1 med izbranimi vrsticami poizvedbe – v našem primeru povprečno vrednost PRICE_1 v celotni tabeli Table_example. Vrednost, ki jo vrne poizvedba, je postavljena v spremenljivko avg_price. Upoštevajte, da je pred spremenljivko avg_pnce dvopičje - da se razlikuje od polj, uporabljenih v zahtevi.
Značilnost te poizvedbe je, da vedno vrne natanko en in edini zapis. Takšne poizvedbe se imenujejo singleton poizvedbe in samo takšne izbire je mogoče uporabiti v shranjenih procedurah. Če poizvedba vrne več kot eno vrstico, jo je treba oblikovati kot konstrukcijo FOR SELECT...DO, ki organizira zanko za obdelavo vsake vrnjene vrstice
Tako smo dobili povprečno vrednost cene. Zdaj morate iti skozi celotno tabelo, primerjati vrednost cene v vsakem vnosu s povprečno ceno in ustrezno ukrepati.
Od začetka organiziramo ponovitev vsakega zapisa iz tabele Table_example
ZA
IZBERI ID, NAME, PRICE_1
IZ tabele_primer
INTO:ID, :NAME, :nova_price
NAREDI
ZAČETI
/*_tukaj oNaroči vsak zapis*/
KONEC
Ko se izvede ta konstrukcija, bodo podatki po vrsti priklicani iz tabele Table_example, vrednosti polj v vsaki vrstici pa bodo dodeljene spremenljivkam ID, NAME in new_pnce. Seveda se spomnite, da so te spremenljivke deklarirane kot izhodni parametri, vendar naj vas ne skrbi, da bodo izbrani podatki vrnjeni kot rezultati: dejstvo, da so izhodni parametri nekaj dodeljeni, ne pomeni, da bo odjemalec, ki kliče HP, takoj prejel te vrednosti.! Parametri se posredujejo le, ko se izvede ukaz SUSPEND, pred tem pa lahko izhodne parametre uporabimo kot običajne spremenljivke – v našem primeru to storimo s parametrom new_price.
Torej, znotraj telesa zanke BEGIN... .END lahko obdelamo vrednosti vsake vrstice. Kot se spomnite, moramo ugotoviti, kako se obstoječa cena primerja s povprečjem, in ustrezno ukrepati. Ta postopek primerjave izvajamo z uporabo stavka IF:
IF (nova_cena > povprečna_cena) THEN /*če je trenutna cena višja od povprečne cene*/
ZAČETI
/*nato nastavite novo ceno, enako povprečni ceni plus fiksni odstotek */
nova_cena = (povprečna_cena + povprečna_cena*(Percent2Increase/100));
POSODOBITE Tabela_primer
NASTAVI PRICE_1 = :nova_cena
KJE ID = :ID;
KONEC
DRUGO
ZAČETI
/* Če je trenutna cena nižja ali enaka povprečni ceni, potem nastavite ceno, enako prejšnji ceni plus polovico razlike med staro in povprečno ceno */
nova_cena = (novo_pnce + ((avg_pnce nova_cena)/2)) ;
POSODOBITE Tabela_primer
NASTAVI PRICE_1 = :nova_cena
KJE ID = .ID;
KONEC
Kot lahko vidite, smo dobili precej veliko konstrukcijo IF, ki bi jo težko razumeli, če ne bi bilo komentarjev, ki so obdani z znaki /**/.
Za spremembo cene v skladu z izračunano razliko bomo uporabili stavek UPDATE, ki vam omogoča spreminjanje obstoječih zapisov – enega ali več. Da bi nedvoumno označili, v katerem zapisu je treba spremeniti ceno, uporabimo polje primarnega ključa v stavku WHERE in ga primerjamo z vrednostjo spremenljivke, ki shranjuje vrednost ID-ja za trenutni zapis: ID=:ID. Upoštevajte, da je pred spremenljivko ID dvopičje.
Po izvedbi konstrukcije IF...THEN...ELSE spremenljivke ID, NAME in new_price vsebujejo podatke, ki jih moramo vrniti odjemalcu\, ki je poklical proceduro. Če želite to narediti, morate za IF vstaviti ukaz SUSPEND, ki bo podatke poslal tja, od koder je bil CP poklican. Za čas prenosa bo postopek začasno ustavljen in ko bo potreben nov zapis iz CP, se bo nadaljevalo znova - in to se bo nadaljevalo, dokler FOR SELECT...DO ne bo ponovil vseh zapisov svoje poizvedbe.
Upoštevati je treba, da poleg ukaza SUSPEND, ki samo prekine shranjeno proceduro, obstaja še ukaz EXIT, ki konča shranjeno proceduro po posredovanju niza. Vendar se ukaz EXIT redko uporablja, saj je potreben predvsem za prekinitev zanke, ko je dosežen določen pogoj.
V tem primeru, ko je bila procedura poklicana z stavek SELECT in končana z EXIT, zadnja pridobljena vrstica ne bo vrnjena. Se pravi, če morate prekiniti postopek in še vedno> dobiti to vrstico, morate uporabiti zaporedje
ZAUSTAVI;
IZHOD;
Glavni namen EXIT je pridobiti enotne nabore podatkov, vrniti parametre s klicem prek EXECUTE PROCEDURE. V tem primeru se nastavijo vrednosti izhodnih parametrov, vendar se iz njih ne oblikuje nabor podatkov SQL in postopek se konča.
Zapišimo besedilo naše shranjene procedure v celoti, da lahko na prvi pogled ujamemo njeno logiko:
USTVARJANJE POSTOPKA Povečanje cen(
Percent2 Povečaj DVOJNO NATOČNOST)
VRAČILA (ID INTEGER, IME VARCHAR(80),
new_price DVOJNA PRECIZNOST) AS
DECLARE VARIABLE avg_price DVOJNA NATAKNOST;
ZAČETI
IZBERI AVG(cena_l)
IZ tabele_primer
INTO:avg_price;
ZA
IZBERI ID, NAME, PRICE_1
IZ tabele_primer
INTO:ID, :NAME, :nova_price
NAREDI
ZAČETI
/*tukaj obdelamo vsak zapis*/
IF (new_pnce > avg_price) THEN /*če je trenutna cena višja od povprečne cene*/
ZAČETI
/*nastavi novo ceno, enako povprečni ceni plus fiksni odstotek */
nova_cena = (povprečna_cena + povprečna_cena*(Percent2Increase/100));
POSODOBITE Tabela_primer
NASTAVI PRICE_1 = :nova_cena
KJE ID = :ID;
KONEC
DRUGO
ZAČETI
/* Če je trenutna cena nižja ali enaka povprečni ceni, potem nastavi ceno enako prejšnji ceni plus polovico razlike med staro in povprečno ceno */
nova_cena = (nova_cena + ((povprečna_cena - nova_cena)/2));
POSODOBITE Tabela_primer
NASTAVI PRICE_1 = :nova_cena
KJE ID = :ID;
KONEC
ZAUSTAVI;
KONEC
KONEC
Ta primer shranjene procedure ponazarja uporabo osnovne shranjene procedure in konstrukcije sprožilnega jezika. Nato si bomo ogledali načine uporabe shranjenih postopkov za reševanje nekaterih pogostih težav.
Rekurzivne shranjene procedure
InterBase shranjene procedure so lahko rekurzivne. To pomeni, da lahko shranjena procedura pokliče samega sebe. Dovoljenih je do 1000 ravni gnezdenja shranjenih postopkov, vendar ne pozabite, da lahko prostih virov na strežniku zmanjka, preden je doseženo največje število HP-jevih gnezdenj.
Ena pogosta uporaba shranjenih postopkov je obdelava drevesnih struktur, shranjenih v bazi podatkov. Drevesa se pogosto uporabljajo v BOM, skladišču, HR in drugih običajnih aplikacijah.
Oglejmo si primer shranjene procedure, ki izbere vse izdelke določene vrste, začenši na določeni ravni gnezdenja.
Naj imamo naslednjo izjavo o problemu: imamo imenik blaga s hierarhično strukturo te vrste:
Blago
- Aparati
- Hladilniki
- Trikomorni
- Dvokomorna
- Enokomora
- pralni stroji
- Navpična
- Frontalno
- Klasično
- Ozko
- Računalniška tehnologija
....
Ta struktura imenika kategorij izdelkov ima lahko veje različnih globin. in se sčasoma tudi povečuje. Naša naloga je zagotoviti izbor vseh končnih elementov iz imenika z "razgrnitvijo polnega imena", začenši od katerega koli vozlišča. Na primer, če izberemo vozlišče "Pralni stroji", moramo dobiti naslednje kategorije:
Pralni stroji - Vertikalni
Pralni stroji - Frontal Classic
Pralni stroji - Frontalni ozki
Določimo strukturo tabel za shranjevanje informacij v katalogu blaga. Za organiziranje drevesa v eni tabeli uporabljamo poenostavljeno shemo:
USTVARJTE TABELE GoodsTree
(ID_GOOD INTEGER NI NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR(80),
omejitev primarni ključ pkGooci (ID_GOOD));
Ustvarimo eno tabelo GoodsTree, ki ima samo 3 polja: ID_GOOD je identifikator pametne kategorije, ID_PARENT_GOOD je identifikator nadrejenega drevesa kategorije za to kategorijo in GOOD_NAME je ime kategorije. Da zagotovimo celovitost podatkov v tej tabeli, bomo tej tabeli naložili omejitev tujega ključa:
ALTER TABLE GoodsTree
DODAJ OMEJITEV FK_goodstree
TUJI KLJUČ (ID_PARENT_GOOD)
REFERENCE GOODSTPEE (ID_GOOD)
Tabela se nanaša nase in dani tuji ključ to spremlja. tako da tabela ne vsebuje sklicevanj na neobstoječe starše, preprečuje pa tudi poskuse brisanja kategorij izdelkov, ki imajo otroke.
V našo tabelo damo naslednje podatke:
| ID_GOOD 1
|
ID_PARENT_GOOD 0
|
GOOD_NAME BLAGO |
Zdaj, ko imamo prostor za shranjevanje podatkov, lahko začnemo ustvarjati shranjeno proceduro, ki prikazuje vse "končne" kategorije izdelkov v "razširjeni" obliki - na primer za kategorijo "Trikomorni" polno ime kategorija bo izgledala kot "Hladilniki za gospodinjstvo s tremi komorami".
Shranjene procedure, ki obdelujejo drevesne strukture, so razvile lastno terminologijo. Vsak element drevesa se imenuje vozlišče; in razmerje med vozlišči, ki se nanašajo drug na drugega, se imenuje odnos starševsko-otroški. Vozlišča, ki so na samem koncu drevesa in nimajo otrok, se imenujejo "listje".
Naša shranjena procedura bo imela kot vhodni ID kategorije, iz katere bomo morali začeti razčlenitev. Shranjeni postopek bo videti takole:
USTVARI POSTOPKA GETPULNAME (ID_GOOD2SHOW INTEGER)
VRAČILA (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
AS
OZNAŠI SPREMENLJIVKO CURR_CHILD_NAME VARCHAR(80);
ZAČETI
/*0organizirajte zunanjo zanko FOR SELECT na neposrednih potomcih izdelka z ID_GOOD=ID_GOOD2SHOW */
ZA IZBERI gtl.id_good, gtl.good_name
IZ GoodsTree gtl
KJE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
NAREDI
ZAČETI
/"Preveri s funkcijo EXISTS, ki vrne TRUE, če poizvedba v oklepajih vrne vsaj eno vrstico. Če najdeno vozlišče z ID_PARENT_GOOD = ID_CHILD_GOOD nima podrejenih, potem je "list" drevesa in pride v rezultate * /
ČE (NE OBSTAJA(
IZBERI * IZ GoodsTree
KJE GoodsTree.id_parent_good=:id_child_good))
POTEM
ZAČETI
/* Podaj "list" drevesa rezultatom */
ZAUSTAVI;
KONEC
DRUGO
/* Za vozlišča, ki imajo otroke */
ZAČETI
/*shrani ime nadrejenega vozlišča v začasno spremenljivko */
CURR_CHILD_NAME=polno_ime_blaga;
/* zaženi ta postopek rekurzivno */
ZA
SELECT ID_CHILD_GOOD,polno_ime_blaga
FROM GETfulLNAME(:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
ZAČNITE
/*dodajte ime nadrejenega vozlišča najdenemu., podrejenemu imenu z uporabo operacije povezovanja nizov || */
full_goods_name=CURR_CHILD_NAME| " " | polno_ime_blaga,-
ZAUSTAVI; /* vrne polno ime izdelka */
KONEC
KONEC
KONEC
KONEC
Če ta postopek izvedemo z vhodnim parametrom ID_GOOD2SHOW= 1, dobimo naslednje:
Kot lahko vidite, smo z uporabo rekurzivne shranjene procedure pregledali celotno drevo kategorij in izbrali polno ime kategorij "listov", ki so na samih konicah vej.
Zaključek
S tem zaključujemo naš pregled glavnih značilnosti jezika shranjene procedure. Očitno je v enem poglavju nemogoče v celoti obvladati razvoj shranjenih procedur, vendar smo tukaj poskušali predstaviti in razložiti glavne koncepte, povezane s shranjenimi procedurami. Opisane zasnove in tehnike oblikovanja HP-ja je mogoče uporabiti v večini aplikacij za baze podatkov.
Nekatera pomembna vprašanja, povezana z razvojem shranjenih procedur, bodo obravnavana v naslednjem poglavju – »Razširitve jezika shranjenih procedur InterBase«, ki je namenjeno obravnavi izjem, obravnavi napak v shranjenih procedurah in delu z nizi.
Upoštevamo situacijo, ko lahko shranjene procedure poslabšajo zmogljivost poizvedbe.
Pri prevajanju shranjenih procedur v strežniku MS SQL Server 2000 so shranjene procedure postavljene v predpomnilnik postopkov, kar lahko pomaga povečati zmogljivost, ko se izvajajo, tako da odpravi potrebo po razčlenjevanju, optimizaciji in prevajanju kode shranjene procedure.
Po drugi strani pa obstajajo pasti pri shranjevanju prevedene kode shranjene procedure, ki ima lahko nasproten učinek.
Dejstvo je, da se pri prevajanju shranjene procedure prevede načrt izvajanja tistih stavkov, ki sestavljajo kodo procedure, oziroma, če je prevedena shranjena procedura predpomnjena, se predpomni tudi njen izvedbeni načrt, zato shranjena procedura ne bo biti optimiziran za določeno situacijo in parametre poizvedbe.
Naredil bo majhen eksperiment, da bi to dokazal.
KORAK 1. Ustvarjanje baze podatkov.
Za poskus ustvarimo ločeno bazo podatkov.
CREATE DATABASE test_sp_perf
ON (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
PRIJAVA (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
2. KORAK. Ustvarjanje tabele.
CREATE TABLE sp_perf_test(stolpec1 int, stolpec2 char(5000))
3. KORAK. Polnjenje tabele s testnimi nizi. Podvojene vrstice so namerno dodane v tabelo. 10000 vrstic s številkami od 1 do 10000 in 10000 vrstic s številkami 50000.
IZJAVA @i int
NASTAVI @i=1
Medtem ko jaz<10000)
ZAČETI
INSERT INTO sp_perf_test(column1, column2) VALUES(@i,"Preskusni niz #"+CAST(@i kot char(8)))
INSERT INTO sp_perf_test(column1, column2) VALUES(50000,"Preskusni niz #"+CAST(@i kot char(8)))
NASTAVI @i= @i+1
KONEC
SELECT COUNT(*) FROM sp_perf_test
POJDI
4. KORAK. Ustvarite indeks brez gruče. Ker je načrt izvedbe predpomnjen s postopkom, bo indeks uporabljen na enak način za vse klice.
USTVARJITE NEKLASTER INDEX CL_perf_test NA sp_perf_test(stolpec1)
POJDI
5. KORAK. Ustvarite shranjeno proceduro. Postopek preprosto izvede stavek SELECT s pogojem.
USTVARI PROC proc1 (@paramint)
AS
IZBERI stolpec 1, stolpec 2 FROM sp_perf_test WHERE [email protected]
POJDI
6. KORAK. Zaženite shranjeno proceduro. Pri zagonu ranljivega postopka se posebej uporabi selektivni parameter. Kot rezultat postopka dobimo 1 vrstico. Izvedbeni načrt označuje uporabo indeksa brez gruče, kot poizvedba je selektivna in to je najboljši način za ekstrakcijo niza. Postopek, optimiziran za pridobivanje ene vrstice, je shranjen v predpomnilniku postopkov.
EXEC proc1 1234
POJDI
7. KORAK. Izvajanje shranjene procedure z neselektivnim parametrom. Kot parameter je uporabljena vrednost 50000. Vrstice s tako vrednostjo prvega stolpca približno 10000, z uporabo indeksa brez gruče in operacije iskanja zaznamkov sta neučinkoviti, ker pa je prevedena koda z izvedbenim načrtom shranjena v proceduralnem predpomnilnik, bo uporabljen. Načrt izvajanja to kaže, pa tudi dejstvo, da je bila operacija iskanja zaznamka izvedena za 9999 vrstic.
EXEC proc1 50000
POJDI
8. KORAK. Izvajanje izbire vrstic s prvim poljem enakim 50000. Ko se izvede ločena poizvedba, se poizvedba optimizira in prevede s specifično vrednostjo prvega stolpca. Posledično optimizator poizvedb ugotovi, da je polje večkrat podvojeno, in se odloči za uporabo operacije skeniranja tabele, ki je v tem primeru veliko bolj učinkovita kot uporaba indeksa, ki ni v gruči.
IZBERI stolpec1, stolpec2 FROM sp_perf_test WHERE stolpec1=50000
POJDI
Tako lahko sklepamo, da uporaba shranjenih postopkov morda ne izboljša vedno učinkovitosti poizvedbe. Pri tistih shranjenih procedurah, ki delujejo z rezultati s spremenljivim številom vrstic in uporabljajo različne izvedbene načrte, morate biti zelo previdni.
Skript lahko uporabite za ponovitev poskusa na svojem strežniku MS SQL.
