Tárolt eljárások és felhasználásuk. Használjon speciális kéréseket. A tárolt függvények intoleranciája

Tárolt eljárások

A fejezet tárgya az egyik leghatékonyabb eszköz, amelyet az InterBase adatbázis-alkalmazások fejlesztői számára kínálnak az üzleti logika megvalósításához. a teljes alkalmazás teljesítményét, központosítsa az adatfeldolgozást és csökkentse a feladatok elvégzéséhez szükséges kód mennyiségét komplex alkalmazás Az adatbázisok nem teljesek tárolt eljárások használata nélkül.
A tárolt eljárások használatának ezen jól ismert előnyei mellett, amelyek a legtöbb relációs DBMS-re jellemzőek, az InterBase tárolt eljárások szinte teljes adatkészletként működhetnek, lehetővé téve a visszaadott eredmények felhasználását a szokásos SQL-lekérdezésekben.
A kezdő fejlesztők gyakran úgy képzelik el a tárolt eljárásokat, mint egy meghatározott SQL-lekérdezések halmazát, amelyek az adatbázison belül csinálnak valamit, és az a vélemény, hogy a tárolt eljárásokkal dolgozni sokkal nehezebb, mint ugyanazt a funkciót implementálni egy ügyfélalkalmazásba a nyelven. magas szint
Tehát melyek az InterBase tárolt eljárások?
A tárolt eljárás (SP) az adatbázis metaadatának egy része, amely az InterBase belső reprezentációjába összeállított, speciális nyelven írt szubrutin, amelynek fordítója az InteiBase szerver magjába van beépítve.
Tárolt eljárás hívható ügyfélalkalmazásokból, triggerekből és más tárolt eljárásokból. A tárolt eljárás a szerver folyamaton belül fut, és képes az adatbázisban lévő adatok manipulálására, valamint a végrehajtás eredményét visszaküldeni az azt hívó kliensnek (azaz triggernek, HP-nek, alkalmazásnak).
Alap erőteljes képességek A HP-be ágyazott procedurális programozási nyelv, amely tartalmazza a módosított normál SQL utasításokat, mint például az INSERT, UPDATE és SELECT, valamint eszközöket az elágazások és ciklusok szervezésére (IF, WHILE), valamint a hibák és kivételek kezelésére szolgáló eszközöket. A tárolt eljárásnyelv lehetővé teszi összetett algoritmusok megvalósítását az adatokkal való munkavégzéshez, és a relációs adatokkal való munkavégzésre összpontosítva a HP sokkal kompaktabb, mint a hagyományos nyelvek hasonló eljárásai.
Meg kell jegyezni, hogy ugyanazt a programozási nyelvet használják a triggerekhez, számos szolgáltatás és korlátozás kivételével. A triggerekben használt nyelv részhalmaza és a HP nyelv közötti különbségeket részletesen a „Triggerek” című fejezet tárgyalja (1. rész).

Példa egy egyszerű tárolt eljárásra

Itt az ideje, hogy létrehozza az első tárolt eljárást, és használja azt példaként, hogy megtanulja a tárolt eljárások létrehozásának folyamatát. De először is ejtünk néhány szót a tárolt eljárásokkal való munkavégzésről. A tény az, hogy a HP homályos és kényelmetlen eszközként a rendkívül gyenge szabványos eszközöknek köszönheti a tárolt eljárások fejlesztését és hibakeresését. Az InterBase dokumentációja azt javasolja, hogy hozzon létre eljárásokat HP szöveget tartalmazó SQL script fájlokat használva, amelyek bemenetként kerülnek az isql interpreterbe, és így a HP If létrehozását és módosítását ebben az SQL szkriptben az eljárás szövegének BLR-ben történő fordításának szakaszában (a BLR-ről lásd az "InterBase adatbázis-struktúra" fejezetet (4. rész)), ha hiba történik, az isql üzenetet jelenít meg arról, hogy az SQL-parancsfájl melyik sorában fordult elő ez a hiba. Javítsa ki a hibát, és tegye újra az egészet. Egyáltalán nem esik szó a szó modern értelmében vett hibakeresésről, azaz a végrehajtás nyomon követéséről, a változók köztes értékeinek megtekintésével. Nyilvánvalóan ez a megközelítés nem járul hozzá a tárolt eljárások vonzerejének növekedéséhez a fejlesztő szemében
A HP fejlesztés standard minimalista megközelítése mellett azonban<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
A tárolt eljárások szintaxisát a következőképpen írjuk le:

ELJÁRÁS név LÉTREHOZÁSA
[ (param adattípus [, param adattípus ...]) ]
)]
MINT
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE var adattípus;

=
KEZDŐDIK
< compound_statement>
[< compound_statement> ...]
VÉGE
< compound_statement> = (nyilatkozat;)

Elég terjedelmesnek tűnik, sőt nehézkes is, de valójában minden nagyon egyszerű A szintaxis fokozatos elsajátítása érdekében nézzünk meg fokozatosan bonyolultabb példákat.
Tehát itt van egy példa egy nagyon egyszerű tárolt eljárásra, amely két számot vesz be, összeadja őket, és visszaadja az eredményt:

ELJÁRÁS LÉTREHOZÁSA SP_Add(first_arg DOUBLE PRECISION,
second_arg DUPLA PONTOSSÁG)
VISSZATÉRÉS (DUPLA PONTOSSÁG)
MINT
KEZDŐDIK
Eredmény=first_arg+second_arg;
FÜGG.
VÉGE

Mint látható, minden egyszerű: a CREATE PROCEDURE parancs után megjelenik az újonnan létrehozott eljárás neve (amelynek egyedinek kell lennie az adatbázison belül) - ebben az esetben SP_Add, majd a HP bemeneti paraméterei - first_arg és second_arg - zárójelben felsorolva, vesszővel elválasztva, megjelölve a típusukat.
A bemeneti paraméterek listája a CREATE PROCEDURE utasítás nem kötelező része - vannak esetek, amikor egy eljárás az eljárás törzsén belüli táblák lekérdezésein keresztül megkapja a munkájához szükséges összes adatot.

A tárolt eljárások bármilyen skaláris adattípust használnak InteiBase Nem teszi lehetővé tömbök és felhasználó által definiált típusok - tartományok használatát

Következik kulcsszó RETURNS, amely után zárójelben szerepelnek a visszaadott paraméterek, megjelölve típusukat - jelen esetben csak egy - Eredmény.
Ha az eljárás nem ad vissza paramétereket, akkor hiányzik a RETURNS szó és a visszaadott paraméterek listája.
A RETURNSQ után az AS kulcsszó kerül megadásra. Mielőtt az AS kulcsszó elmegy cím,és azután - techo eljárások.
Egy tárolt eljárás törzse a belső (lokális) változóinak leírása (ha vannak ilyenek, az alábbiakban részletesebben is megvizsgáljuk őket), pontosvesszővel (;) elválasztva, és operátori zárójelben lévő utasításblokkkal. KEZDÉS VÉGE. Ebben az esetben a HP törzse nagyon egyszerű - két bemeneti argumentumot kérünk, és ezek eredményét hozzárendeljük a kimenethez, majd meghívjuk a SUSPEND parancsot. Kicsit később elmagyarázzuk ennek a parancsnak a működésének lényegét, de egyelőre csak azt jegyezzük meg, hogy a visszatérési paraméterek átviteléhez szükséges oda, ahonnan a tárolt eljárást hívták.

Határolók a tárolt eljárásokban

Vegye figyelembe, hogy az eljáráson belüli utasítás pontosvesszővel (;) végződik. Mint tudják, a pontosvessző egy szabványos parancselválasztó az SQL-ben – ez jelzi az SQL értelmezőnek, hogy a parancsszöveg teljes egészében beírásra került, és el kell kezdenie annak feldolgozását. Nem úgy alakulna ki, hogy ha az SQL értelmező pontosvesszőt talál a HP közepén, akkor azt feltételezi, hogy a parancsot teljes egészében megadta, és megpróbálja végrehajtani a tárolt eljárás egy részét? Ez a feltételezés nem alaptalan. Valóban, ha létrehoz egy fájlt, amelybe a fenti példát írja, hozzáad egy kapcsolódási parancsot az adatbázisból, és megpróbálja végrehajtani ezt az SQL-szkriptet az isql interpreter segítségével, akkor hibaüzenetet fog visszaadni a váratlan, az értelmező véleménye szerint a tárolt eljárás létrehozási parancsának. Ha tárolt eljárásokat SQL-parancsfájlok használatával hoz létre, speciális InterBase fejlesztői eszközök használata nélkül, akkor minden HP létrehozási parancs előtt (ugyanez vonatkozik a triggerekre is) módosítania kell a parancsfájl-parancs elválasztóját pontosvesszőtől eltérő karakterre, a szöveg után pedig a HP szövegre. visszaállítani. Az SQL záradékelválasztót módosító isql parancs így néz ki:

HATÁZAT BEÁLLÍTÁSA

A tárolt eljárás létrehozásának tipikus esete a következőképpen néz ki:

TERM BEÁLLÍTÁSA^;
ELJÁRÁS LÉTREHOZÁSA some_procedure
... . .
VÉGE
^
TERMÉK BEÁLLÍTÁSA ;^

Tárolt eljárás hívása

De térjünk vissza a tárolt eljárásunkhoz. Most, hogy létrejött, meg kell hívnia valahogy, paramétereket kell átadnia neki, és vissza kell kapnia az eredményeket. Ezt nagyon könnyű megtenni – csak írjon egy ilyen SQL-lekérdezést:

KIVÁLASZTÁS *
FROM Sp_add(181.35; 23.09)

Ez a lekérdezés egy sort ad vissza, amely csak egy Eredmény mezőt tartalmaz, amely a 181,35 és a 23,09, azaz a 204,44 számok összegét tartalmazza.
Így eljárásunk használható közönséges SQL lekérdezésekben, amelyek mind kliensprogramokban, mind más HP-ban vagy triggerekben futnak le. Eljárásunk ezen használatát a tárolt eljárás végén a SUSPEND parancs használatával teszi lehetővé.
Az a tény, hogy az InterBase-ben (és minden klónjában) kétféle tárolt eljárás létezik: kiválasztható eljárások és végrehajtható eljárások. A két HP-típus működésében az a különbség, hogy a mintavételi eljárások általában sok kimeneti paraméter-készletet adnak vissza soronként csoportosítva, amelyek úgy néznek ki, mint egy adathalmaz, míg a végrehajtható eljárások vagy egyáltalán nem, vagy csak adnak vissza paramétereket. kimeneti paraméterek egy készlete , a Returns listában, ahol egy sor paraméter. A kiválasztási eljárások a SELECT lekérdezésekben, a végrehajtható eljárások pedig az EXECUTE PROCEDURE paranccsal kerülnek meghívásra.
Mindkét típusú tárolt eljárásnak ugyanaz a létrehozási szintaxisa, és formálisan nem különböznek egymástól, így bármely végrehajtható eljárás meghívható egy SELECT lekérdezésben, és bármely kiválasztási eljárás meghívható az EXECUTE PROCEDURE segítségével. A kérdés az, hogy a HP mikor fog viselkedni különböző típusok hívás. Más szóval, a különbség abban rejlik, hogy az eljárást egy adott típusú híváshoz kell megtervezni. Ez azt jelenti, hogy a kiválasztási eljárás kifejezetten a SELECT lekérdezésből való meghívásra, a végrehajtható eljárás pedig kifejezetten az EXECUTE PROCEDURE használatával való meghívásra van létrehozva. Nézzük meg, mi a különbség e két HP-típus kialakításában.
A mintavételi eljárás működésének megértéséhez kissé mélyebbre kell ásnia az elméletet. Képzeljünk el egy szokásos SQL-lekérdezést, például SELECT ID, NAME FROM Table_example. Végrehajtása eredményeként egy két oszlopból (ID és NAME) és meghatározott számú sorból álló (a Táblázat_példa tábla sorainak számával megegyező) táblázatot kapunk. A lekérdezésből visszaadott táblát halmaznak is nevezik SQL adatok Gondoljuk végig, hogy ennek a lekérdezésnek a végrehajtása során hogyan alakul ki az adathalmaz. A szerver a lekérdezést fogadva meghatározza, hogy mely táblákra hivatkozik, majd kideríti, hogy ezekből a táblákból melyik rekordok részhalmazát kell szerepeltetni a lekérdezés eredményében. Ezután a szerver beolvassa a lekérdezés eredményét kielégítő minden rekordot, és kiválaszt belőle Szükséges Mezők(esetünkben ezek ID és NAME), és elküldi a kliensnek. Ezután a folyamat megismétlődik – és így tovább minden kiválasztott rekordnál.
Mindez a kitérő azért szükséges, hogy a tisztelt olvasó megértse, minden SQL adatsor soronként generálódik, így a tárolt eljárásokban is! A fő különbség a lekérési eljárások és a végrehajtható eljárások között az, hogy az előbbiek sok sort, míg az utóbbiak csak egy sort adnak vissza. Emiatt másképpen használják őket: a select eljárást a SELECT paranccsal hívják meg, ami "megköveteli" az eljárástól, hogy adja fel az összes visszaadható rekordot. A végrehajtható eljárást az EXECUTE PROCEDURE segítségével hívják meg, amely csak egy sort „vesz ki” a HP-ból, a többit pedig figyelmen kívül hagyja (még ha létezik is!).
Nézzünk egy példát a mintavételi eljárásra, hogy világosabb legyen. A > megbocsátáshoz készítsünk egy tárolt eljárást, ami pontosan úgy működik, mint egy SELECT ID, NAME FROM Table_Example lekérdezés, vagyis egyszerűen kiválasztja az ID és a NAME mezőket a teljes táblából. Itt van ez a példa:

ELJÁRÁS LÉTREHOZÁSA Simple_Select_SP
VISSZATÉRÉS (
procID INTEGER,
procNAME VARCHAR(80))
MINT
KEZDŐDIK
FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
KEZDŐDIK
FÜGG.
VÉGE
VÉGE

Nézzük meg ennek a Simple_Select_SP-nek nevezett eljárás lépéseit. Amint láthatja, nincs bemeneti paramétere, és két kimeneti paramétere van - ID és NAME. A legérdekesebb dolog természetesen az eljárás testében rejlik. A FOR SELECT konstrukció itt használatos:

FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
KEZDŐDIK

/*csinál valamit a procID és procName változókkal*/

VÉGE

Ez a kódrészlet a következőket jelenti: a Table_example táblából kiválasztott minden egyes sorhoz helyezze a kiválasztott értékeket a procID és procName változókba, majd tegyen valamit ezekkel a változókkal.
Meglepett arcot vághat, és megkérdezheti: "Változók? Milyen változók még? 9" Ennek a fejezetnek egyfajta meglepetése, hogy tárolt eljárásokban is használhatunk változókat. A HP nyelvben deklarálhatja saját helyi változóit egy eljáráson belül, és változóként használhatja a bemeneti és kimeneti paramétereket.
Egy tárolt eljárásban egy lokális változó deklarálásához a leírását az AS kulcsszó után és az első BEGIN szó elé kell helyezni. A lokális változó leírása így néz ki:

VÁLTOZÓ BEJELENTÉSE ;

Például egy Mylnt egész számú helyi változó deklarálásához a következő deklarációt kell beillesztenie az AS és a BEGIN közé

DECLARE VARIABLE Mylnt INTEGER;

Példánkban a változók kettősponttal kezdődnek. Ez azért van így, mert a FOR SELECT SQL parancson belül érhetők el, így a SELECT-ben használt táblák mezői és a változók közötti különbségtételhez az utóbbit kettőspontnak kell megelőznie. Hiszen a változóknak pontosan ugyanaz a neve, mint a táblák mezőinek!
A változónév előtti kettőspontot azonban csak az SQL-lekérdezéseken belül szabad használni. A szövegeken kívül a változókra kettőspont nélkül hivatkozunk, például:

procName="Valamilyen név";

De térjünk vissza eljárásunk lényegéhez. A FOR SELECT záradék nem táblázat formájában adja vissza az adatokat – egy adathalmazt, hanem soronként. Minden visszaadott mezőt a saját változójában kell elhelyezni: ID => procID, NAME => procName. A DO részben ezek a változók elküldésre kerülnek az eljárást a SUSPEND paranccsal meghívó ügyfélnek
Így a FOR SELECT...DO parancs végigfut a parancs SELECT részében kiválasztott rekordokon. A DO rész által alkotott ciklus törzsében a következő generált rekord a SUSPEND paranccsal átkerül a klienshez.
Tehát a kiválasztási eljárást úgy tervezték, hogy egy vagy több sort adjon vissza, amelyekhez a HP törzsében egy hurok van szervezve, amely kitölti a kapott változó paramétereit. És ennek a ciklusnak a törzsének végén mindig van egy SUSPEND parancs, amely a következő adatsort adja vissza a kliensnek.

Hurok és elágazási utasítások

A FOR SELECT...DO parancson kívül, amely egy ciklust szervez a kijelölés rekordjain keresztül, létezik egy másik típusú hurok - WHILE...DO, amely lehetővé teszi egy ciklus megszervezését bármely feltétel ellenőrzése alapján. Íme egy példa a HP WHILE..DO ciklus használatára. Ez az eljárás 0 és 99 közötti egész számok négyzetét adja vissza:

PROCEDJRE QUAD LÉTREHOZÁSA
VISSZATÉRÉS (QUADRAT INTEGER)
MINT
DECLARE VARIABLE I INTEGER;
KEZDŐDIK
I = 1;
Miközben én<100) DO
KEZDŐDIK
QUADRAT= I*I;
I=I+1;
FÜGG.
VÉGE
VÉGE

A SELECT FROM QUAD lekérdezés végrehajtása eredményeként egy QUADRAT oszlopot tartalmazó táblát kapunk, mely az 1-től 99-ig terjedő egész számok négyzeteit tartalmazza.
Az SQL minta és a klasszikus ciklus eredményein túlmenően a tárolt eljárásnyelv az IF...THEN..ELSE operátort használja, amely lehetővé teszi az elágazás megszervezését bármely feltétel végrehajtásától függően. A szintaxisa hasonló a legtöbb elágazó operátorhoz magas szintű programozási nyelvekben, mint például a Pascal és a C.
Nézzünk egy összetettebb példát egy tárolt eljárásra, amely a következőket teszi.

1. Kiszámítja az átlagárat a Table_example táblázatban (lásd a "Táblázatok elsődleges kulcsai és generátorai" című fejezetet)
2. Ezután a táblázat minden egyes bejegyzésénél elvégzi a következő ellenőrzést: ha a meglévő ár (PRICE) nagyobb, mint az átlagár, akkor az átlagárral megegyező árat állít be, plusz egy meghatározott fix százalék
3. Ha a meglévő ár kisebb vagy egyenlő, mint az átlagár, akkor az előző árral megegyező árat állít be, plusz az előző és az átlagár közötti különbség fele.
4. Visszaadja a táblázat összes módosított sorát.

Először is definiáljuk a HP nevét, valamint a bemeneti és kimeneti paramétereket. Mindezt a tárolt eljárás fejlécében írjuk.

ELJÁRÁS LÉTREHOZÁSA Növelje az árakat (
2 százalékos növekedés DUPLAS PONTOSSÁG)
VISSZATÉRÉS (ID INTEGER, NÉV VARCHAR(SO), new_price DOUBLE
PRECISION AS

Az eljárás neve IncreasePrices lesz, egy DOUBLE PRECISION típusú Peiceni21nciease bemeneti paraméterrel és 3 kimeneti paraméterrel rendelkezik - ID, NAME és new_pnce. Figyeljük meg, hogy az első két kimeneti paraméternek ugyanaz a neve, mint a Table_example táblázatban, amellyel dolgozni fogunk. Ezt a tárolt eljárásnyelv szabályai lehetővé teszik.
Most deklarálnunk kell egy helyi változót, amelyet az átlagos érték tárolására használunk. A deklaráció így fog kinézni:

DECLARE VARIABLE avg_price DUPLA PONTOSSÁG;

Most térjünk át a tárolt eljárás törzsére Nyissa meg a HP törzsét kulcsszó BEGIN.
Először is végre kell hajtanunk az algoritmusunk első lépését - ki kell számítani az átlagárat. Ehhez a következő típusú lekérdezést fogjuk használni:

AVG KIVÁLASZTÁSA (ár_l)
FROM Table_Example
INTO:átlag_ár,-

Ez a lekérdezés az AVG összesítő függvényt használja, amely a PRICE_1 mező átlagát adja vissza a kiválasztott lekérdezési sorok között – esetünkben a PRICE_1 átlagát a teljes Table_example táblázatban. A kérés által visszaadott érték az avg_price változóba kerül. Vegye figyelembe, hogy az avg_pnce változót kettőspont előzi meg, hogy megkülönböztesse a kérésben használt mezőktől.
Funkció ennek a kérésnek az, hogy mindig pontosan egyetlen rekordot ad vissza. Az ilyen lekérdezéseket singleton lekérdezéseknek nevezzük, és csak az ilyen kijelölések használhatók a tárolt eljárásokban. Ha egy lekérdezés egynél több sort ad vissza, akkor azt FOR SELECT...DO konstrukcióként kell formázni, amely ciklust szervez az egyes visszaadott sorok feldolgozásához.
Tehát megkaptuk az átlagárat. Most át kell mennie a teljes táblázaton, össze kell hasonlítania az egyes bejegyzések árértékét az átlagárral, és meg kell tennie a megfelelő lépéseket
Kezdettől fogva az egyes rekordok keresését a Table_example táblából szervezzük

FOR
VÁLASSZON ID, NÉV, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
KEZDŐDIK
/*_itt leírunk minden bejegyzést*/
VÉGE

Amikor ez a konstrukció végrehajtódik, a Table_example táblázatból soronként kinyerjük az adatokat, és az egyes sorokban lévő mezőértékeket az ID, NAME és new_pnce változókhoz rendeljük. Természetesen ne feledje, hogy ezek a változók kimeneti paraméterként vannak deklarálva, de nem kell attól tartani, hogy a kiválasztott adatok eredményként kerülnek visszaadásra: az a tény, hogy a kimeneti paraméterekhez hozzá van rendelve valami, nem jelenti azt, hogy a HP-t hívó ügyfél azonnal megkapja ezeket az értékeket! A paraméterek csak a SUSPEND parancs végrehajtása után kerülnek átadásra, és előtte a kimeneti paramétereket használhatjuk közönséges változóként - példánkban ezt tesszük a new_price paraméterrel.
Tehát a BEGIN... END ciklus törzsén belül feldolgozhatjuk az egyes sorok értékeit. Mint emlékszik rá, ki kell találnunk, hogy a jelenlegi ár hogyan viszonyul az átlaghoz, és meg kell tennünk a megfelelő lépéseket. Ezt az összehasonlítási eljárást az IF utasítás használatával valósítjuk meg:

HA (új_ár > átlagár) AKKOR /*ha a meglévő ár nagyobb, mint az átlagár*/
KEZDŐDIK
/*akkor új árat állítunk be, amely megegyezik az átlagárral, plusz egy fix százalékos */
új_ár = (átl._ár + átlagos_ár*(Százalék2Növekedés/100));
UPDATE Table_example
ÁR_1 = :új_ár BEÁLLÍTÁSA
WHERE ID = :ID;
VÉGE
MÁS
KEZDŐDIK
/* Ha a jelenlegi ár kisebb vagy egyenlő, mint az átlagár, akkor állítson be egy olyan árat, amely megegyezik az előző árral, plusz az előző és az átlagár közötti különbség fele */
új_ár = (új_pnce + ((átl._pnce új_ár)/2)) ;
UPDATE Table_example
ÁR_1 = :új_ár BEÁLLÍTÁSA
WHERE ID = .ID;
VÉGE

Amint látja, az eredmény egy meglehetősen nagy IF konstrukció, amelyet nehéz lenne megérteni, ha nem lennének a /**/ szimbólumokba foglalt megjegyzések.
A kalkulált különbözet ​​szerinti árváltoztatáshoz az UPDATE utasítást használjuk, amely lehetővé teszi a módosítást meglévő rekordok- egy vagy több. Annak érdekében, hogy egyértelműen jelezzük, melyik rekordban kell módosítani az árat, a WHERE feltételben az elsődleges kulcs mezőt használjuk, összehasonlítva az aktuális rekord azonosító értékét tároló változó értékével: ID=:ID. Vegye figyelembe, hogy az azonosító változót kettőspont előzi meg.
Az IF...THEN...ELSE konstrukció végrehajtása után az ID, NAME és new_price változók olyan adatokat tartalmaznak, amelyeket vissza kell küldenünk az eljárást meghívó kliensnek. Ehhez az IF után be kell szúrni a SUSPEND parancsot, amely oda küldi az adatokat, ahonnan a HP-t hívták új bejegyzés, akkor ismét folytatódik – és ez addig folytatódik, amíg a FOR SELECT...DO végig nem hajtja a lekérdezés összes rekordját.
Megjegyzendő, hogy a SUSPEND parancson kívül, amely csak a tárolt eljárást függeszti fel, van egy EXIT parancs, amely a karakterlánc átadása után leállítja a tárolt eljárást. Az EXIT parancsot azonban meglehetősen ritkán használják, mivel főként a ciklus megszakítására van szükség egy feltétel elérésekor
Abban az esetben azonban, ha az eljárást SELECT utasítással hívták meg, és EXIT-tel fejezték be, az utolsó beolvasott sor nem kerül visszaadásra. Azaz, ha meg kell szakítani az eljárást, és mégis > megkapja ezt a karakterláncot, akkor a szekvenciát kell használnia

FÜGG.
KIJÁRAT;

Az EXIT fő célja egyszemélyes adathalmazok, visszaadott paraméterek fogadása az EXECUTE PROCEDURE meghívásával. Ebben az esetben a kimeneti paraméterek értékei be vannak állítva, de az SQL adatkészlet nem generálódik belőlük, és az eljárás végrehajtása véget ér.
Írjuk le a tárolt eljárásunk szövegét teljes egészében, hogy egy pillantással megragadhassuk a logikáját:

ELJÁRÁS LÉTREHOZÁSA Növelje az árakat (
2. százalék DUPLA PONTOSSÁG növelése)
VISSZATÉRÉSEK (ID INTEGER, NÉV VARCHAR(80),
new_price DUPLA PRECISION) AS
DECLARE VARIABLE avg_price DOUBLE PRECISION;
KEZDŐDIK
AVG KIVÁLASZTÁSA (ár_l)
FROM Table_Example
INTO:átlag_ár;
FOR
VÁLASSZON ID, NÉV, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
KEZDŐDIK
/*minden rekord feldolgozása itt*/
IF (new_pnce > avg_price) THEN /*ha a meglévő ár nagyobb, mint az átlagár*/
KEZDŐDIK
/*állítson be egy új árat, amely megegyezik az átlagárral plusz egy fix százalékos értékkel */
új_ár = (átlag_ár + átlagos_ár*(százalék2lnövekedés/100));
UPDATE Table_example
ÁR_1 = :új_ár BEÁLLÍTÁSA
WHERE ID = :ID;
VÉGE
MÁS
KEZDŐDIK
/* Ha a jelenlegi ár kisebb vagy egyenlő, mint az átlagár, akkor az előző árral egyenlő árat állít be, plusz az előző és az átlagár közötti különbség fele */
új_ár = (új_ár + ((átl.ár - új_ár)/2));
UPDATE Table_example
ÁR_1 = :új_ár BEÁLLÍTÁSA
WHERE ID = :ID;
VÉGE
FÜGG.
VÉGE
VÉGE

Ez a tárolt eljárási példa az alapvető tárolt eljárásnyelvi konstrukciók és triggerek használatát szemlélteti. Ezután megvizsgáljuk, hogyan lehet tárolt eljárásokat használni néhány gyakori probléma megoldására.

Rekurzív tárolt eljárások

Az InterBase tárolt eljárások rekurzívak lehetnek. Ez azt jelenti, hogy egy tárolt eljárás hívhatja magát. A tárolt eljárások legfeljebb 1000 beágyazási szintje megengedett, de ne feledjük, hogy a kiszolgálón lévő szabad erőforrások elfogyhatnak, mielőtt elérjük a HP maximális beágyazását.
A tárolt eljárások egyik gyakori felhasználása az adatbázisban tárolt fastruktúrák feldolgozása. A fákat gyakran használják termékösszetételben, raktárban, személyzetben és más általános alkalmazásokban.
Nézzünk egy példát egy tárolt eljárásra, amely egy bizonyos beágyazási szinttől kezdve egy bizonyos típusú összes terméket kiválasztja.
Legyen a probléma megfogalmazása a következő: van egy árujegyzékünk a következő típusú hierarchikus szerkezettel:

Áruk
- Készülékek
- Hűtőszekrények
- Háromkamrás
- Kétkamrás
- Egykamrás
- Mosógépek
- Függőleges
- Elülső
- Klasszikus
- Keskeny
- Számítógépes technológia
....

A termékkategória-katalógusnak ez a szerkezete különböző mélységű ágakat tartalmazhat. és idővel növekedni is. A mi feladatunk, hogy mindenkiből választékot biztosítsunk véges elemek a könyvtárból a "teljes név kiterjesztésével", bármely csomóponttól kezdve. Például, ha a „Mosógépek” csomópontot választjuk, akkor a következő kategóriákat kell megkapnunk:

Mosógépek - Függőleges
Mosógépek - Front Classic
Mosógépek - Elöl keskeny

Határozzuk meg a táblaszerkezetet a termékkatalógus információk tárolására. Egyszerűsített sémát használunk a fa egy táblázatba rendezéséhez:

TÁBLÁZAT LÉTREHOZÁSA GoodsTree
(ID_GOOD INTEGER NEM NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR (80),
megszorítás pkGooci elsődleges kulcs (ID_GOOD));

Létrehozunk egy GoodsTree táblát, amelyben csak 3 mező van: ID_GOOD - a kategória intelligens azonosítója, ID_PARENT_GOOD - az anyacég azonosítója ehhez a kategóriához és GOOD_NAME - a kategória neve. A táblázatban szereplő adatok sértetlenségének biztosítása érdekében egy idegenkulcs-megszorítást fogunk előírni erre a táblára:

ALTER TABLE GoodsTree
KORLÁTOZÁS HOZZÁADÁSA FK_goodstree
IDEGEN KULCS (ID_PARENT_GOOD)
REFERENCIÁK GOODSTPEE (ID__GOOD)

A táblázat önmagára hivatkozik, és ezt az idegen kulcs tartja nyilván. hogy a táblázat ne tartalmazzon hivatkozásokat nem létező szülőkre, és megakadályozza a gyermekekkel rendelkező termékkategóriák törlésére irányuló kísérleteket is.
Írjuk be a táblázatunkba a következő adatokat:

Most, hogy van egy hely az adatok tárolására, elkezdhetjük létrehozni egy tárolt eljárást, amely az összes "végső" termékkategóriát "kibővített" formában adja ki - például a "háromkamrás" kategória esetében a teljes kategóriát. a neve "Háztartási gépek hűtőszekrényei" háromkamrás lenne.
A fastruktúrákat feldolgozó tárolt eljárásoknak saját terminológiájuk van. A fa minden elemét csomópontnak nevezzük; az egymásra hivatkozó csomópontok közötti kapcsolatot pedig szülő-gyermek kapcsolatnak nevezzük. Azokat a csomópontokat, amelyek a fa legvégén vannak, és nincs gyermekük, "leveleknek" nevezik.
Ennél a tárolt eljárásnál a bemeneti paraméter a kategória azonosító lesz, amelyből kell kezdenünk a részletezést. A tárolt eljárás így fog kinézni:

A GETFULLNAME ELJÁRÁS LÉTREHOZÁSA (ID_GOOD2 EGÉSZ SZÁM MEGJELENÍTÉSE)
VISSZATÉRÉS (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
MINT
CURR_CHILD_NAME VÁLTOZÓ DECLARE VARCHAR(80);
KEZDŐDIK
/*0Külső szervezés FOR hurok KIVÁLASZTÁS a termék közvetlen leszármazottainál ID_GOOD=ID_GOOD2SHOW */
FOR SELECT gtl.id_good, gtl.good_name
A GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_JÓ, :teljes_árunév
DO
KEZDŐDIK
/"Ellenőrizze az EXISTS függvénnyel, amely IGAZ értéket ad vissza, ha a zárójelben lévő lekérdezés legalább egy sort ad vissza. Ha a talált csomópontnak ID_PARENT_GOOD = ID_CHILD_JÓ nincs gyermeke, akkor ez a fa "levele" és szerepel az eredményekben */
HA (NOT EXISTS(
SELECT * FROM GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
AKKOR
KEZDŐDIK
/* Adja át a fa „levelét” az eredményeknek */
FÜGG.
VÉGE
MÁS
/* Gyermekekkel rendelkező csomópontokhoz */
KEZDŐDIK
/*menti a szülőcsomópont nevét egy ideiglenes változóba */
CURR_CHILD_NAME=teljes_árunév;
/* rekurzívan futtassa ezt az eljárást */
FOR
SELECT ID_CHILD_JÓ, teljes_árunév
GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_JÓ, :teljes_árunév
KEZDJEN EL
/*adja hozzá a szülőcsomópont nevét a talált gyermeknévhez a || karakterlánc-összefűzési művelettel */
full_goods_name=CURR_CHILD_NAME| " " | f ull_goods_name,-
FÜGG. /* visszaküldi a termék teljes nevét*/
VÉGE
VÉGE
VÉGE
VÉGE

Ha ezt az eljárást az ID_GOOD2SHOW= 1 bemeneti paraméterrel hajtjuk végre, akkor a következőket kapjuk:

Amint láthatja, egy rekurzív tárolt eljárással végigmentünk a teljes kategóriafán, és megjelenítettük a „levél” kategóriák teljes nevét, amelyek az ágak legvégén találhatók.

Következtetés

Ezzel befejeztük a tárolt eljárásnyelv főbb jellemzőinek vizsgálatát. Nyilvánvalóan lehetetlen egy fejezetben teljesen elsajátítani a tárolt eljárások fejlesztését, de itt megpróbáltuk bemutatni és elmagyarázni a tárolt eljárásokhoz kapcsolódó alapfogalmakat. A HP tervezésére vonatkozó leírt tervek és technikák a legtöbb adatbázis-alkalmazásban alkalmazhatók
A tárolt eljárások fejlesztésével kapcsolatos néhány fontos kérdésről a következő fejezet – „Az InterBase tárolt eljárásnyelv haladó képességei” című fejezetben lesz szó.

Tárolt eljárás - adatbázis objektum, amely SQL utasítások halmaza, amelyet egyszer lefordítanak és a szerveren tárolnak. A tárolt eljárások sokban hasonlítanak a közönséges magas szintű nyelvi eljárásokhoz, lehetnek bemeneti és kimeneti paramétereik, valamint lokális változóik, karakteres adatokon numerikus számításokat, műveleteket végezhetnek, melyek eredményeit változókhoz, paraméterekhez rendelhetjük. A tárolt eljárások szabványos adatbázis-műveleteket hajthatnak végre (DDL és DML egyaránt). Ezenkívül a tárolt eljárások lehetővé teszik a hurkokat és elágazásokat, vagyis utasításokat használhatnak a végrehajtási folyamat vezérlésére.

A tárolt eljárások hasonlóak a felhasználó által definiált függvényekhez (UDF). A fő különbség az, hogy a felhasználó által definiált függvények az SQL utasítások bármely más kifejezéséhez hasonlóan használhatók, míg a tárolt eljárásokat a CALL függvény segítségével kell meghívni:

CALL eljárás (…)

Az eljárás VÉGREHAJTÁSA (…)

A tárolt eljárások több eredményt is visszaadhatnak, azaz egy SELECT lekérdezés eredményeit. Az ilyen eredményhalmazok feldolgozhatók kurzorokkal, egyéb tárolt eljárásokkal, amelyek eredménykészlet-mutatót adnak vissza, vagy alkalmazásokkal. A tárolt eljárások deklarált változókat is tartalmazhatnak az adatok feldolgozásához és a kurzorokhoz, amelyek lehetővé teszik, hogy egy táblázatban több soron át hurkoljon. Az SQL szabvány biztosítja az IF, LOOP, REPEAT, CASE és még sok más használatát. A tárolt eljárások elfogadhatnak változókat, eredményeket adhatnak vissza, vagy módosíthatják és visszaadhatják azokat, attól függően, hogy a változó hol van deklarálva.

A tárolt eljárások megvalósítása DBMS-enként változó. A legtöbb nagy adatbázis-gyártó ilyen vagy olyan formában támogatja őket. A DBMS-től függően a tárolt eljárások különféle programozási nyelveken, például SQL, Java, C vagy C++ implementálhatók. A nem SQL-ben írt tárolt eljárások végrehajthatják vagy nem önmagukban hajtanak végre SQL-lekérdezéseket.

Mögött

Logika megosztása más alkalmazásokkal. A tárolt eljárások magukba foglalják a funkcionalitást; ez kapcsolatot biztosít az adatok eléréséhez és kezeléséhez a különböző alkalmazások között.

Felhasználók elkülönítése adatbázistáblákból. Ez lehetővé teszi, hogy hozzáférést biztosítson a tárolt eljárásokhoz, de magához a táblaadatokhoz nem.

Védelmi mechanizmust biztosít. Az előző pontnak megfelelően, ha csak tárolt eljárásokon keresztül férhet hozzá az adatokhoz, senki más nem törölheti az adatokat az SQL DELETE paranccsal.

Jobb végrehajtás a csökkent hálózati forgalom eredményeként. Tárolt eljárások használatával több lekérdezés kombinálható.

Ellen

Az adatbázis-szerver megnövekedett terhelése annak a ténynek köszönhető, hogy a munka nagy része a szerver oldalon történik, és kevesebb a kliens oldalon.

Sokat kell tanulnod. A tárolt eljárások írásához meg kell tanulnia a MySQL kifejezés szintaxisát.

Ön két helyen duplikálja az alkalmazási logikát: a szerver kódjában és a tárolt eljárások kódjában, ami megnehezíti az adatkezelés folyamatát.

Az egyik DBMS-ről a másikra (DB2, SQL Server stb.) történő migráció problémákhoz vezethet.

A tárolt eljárások célja és előnyei

A tárolt eljárások javítják a teljesítményt, javítják a programozási képességeket és támogatják az adatbiztonsági funkciókat.

A gyakran használt lekérdezések tárolása helyett az ügyfelek hivatkozhatnak a megfelelő tárolt eljárásra. Egy tárolt eljárás meghívásakor a kiszolgáló azonnal feldolgozza annak tartalmát.

A lekérdezés tényleges végrehajtása mellett a tárolt eljárások lehetővé teszik a számítások elvégzését és az adatok kezelését is – DDL utasítások módosítását, törlését, végrehajtását (nem minden DBMS-ben!) és egyéb tárolt eljárások meghívását, valamint összetett tranzakciós logika végrehajtását. Egyetlen utasítás lehetővé teszi egy tárolt eljárásban található összetett szkript meghívását, elkerülve ezzel több száz parancs küldését a hálózaton, és különösen azt, hogy nagy mennyiségű adatot kell átvinni a kliensről a szerverre.

A legtöbb DBMS-ben a tárolt eljárás első futtatásakor lefordítják azt (elemzik és létrehozzák az adathozzáférési tervet). A jövőben gyorsabb lesz a feldolgozása. Az Oracle DBMS értelmezi az adatszótárban tárolt, tárolt eljárási kódot. Az Oracle 10g-től kezdve a tárolt procedurális kódok úgynevezett natív fordítása támogatott C-ben, majd a célgép gépi kódjába, amely után egy tárolt eljárás meghívásakor annak lefordított objektumkódja közvetlenül végrehajtásra kerül.

Programozási képességek

A létrehozott tárolt eljárás bármikor meghívható, ami modularitást biztosít és ösztönzi a kód újrafelhasználását. Ez utóbbi megkönnyíti az adatbázis karbantartását, mivel elszigetelődik a változó üzleti szabályoktól. A tárolt eljárásokat az új szabályoknak megfelelően bármikor módosíthatja. Ezt követően az összes ezt használó alkalmazás közvetlen módosítás nélkül automatikusan megfelel az új üzleti szabályoknak.

Biztonság

A tárolt eljárások használata lehetővé teszi az adatbázistáblákhoz való közvetlen felhasználói hozzáférés korlátozását vagy teljes megszüntetését, így a felhasználók csak az adatokhoz közvetett és szigorúan szabályozott hozzáférést biztosító tárolt eljárások végrehajtására jogosultak. Ezenkívül egyes DBMS-ek támogatják a tárolt eljárások szövegtitkosítását (csomagolását).

Ezek a biztonsági szolgáltatások lehetővé teszik az adatbázis-struktúra elkülönítését a felhasználótól, biztosítva az adatbázis integritását és megbízhatóságát.

Az olyan műveletek valószínűsége, mint az SQL-befecskendezés, csökken, mivel a jól megírt tárolt eljárások ezenkívül ellenőrzik a bemeneti paramétereket, mielőtt a lekérdezést átadnák a DBMS-nek.

Tárolt eljárások végrehajtása

A tárolt eljárások általában a használatával jönnek létre SQL nyelv vagy annak konkrét megvalósítása a kiválasztott DBMS-ben. Például ezekre a célokra a DBMS-ben Microsoft SQL A szerveren van egy Transact-SQL nyelv, Oracle-ben - PL/SQL, InterBase-ben és Firebird-ben - PSQL, PostgreSQL-ben - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2-ben - SQL/PL ( eng. ), in Informix - SPL. A MySQL eléggé követi az SQL:2003 szabványt, nyelve hasonló az SQL/PL-hez.

Egyes DBMS-ek lehetővé teszik bármilyen programozási nyelven írt tárolt eljárások használatát, amelyek független futtatható fájlokat hozhatnak létre, például C++ vagy Delphi. A Microsoft SQL Server terminológiájában az ilyen eljárásokat kiterjesztett tárolt eljárásoknak nevezik, és egyszerűen a Win32 DLL-ben található függvények. És például az Interbase-ben és a Firebird-ben a DLL/SO-ból meghívott függvényeknek más a neve - UDF (User Defined Function). Az MS SQL 2005 bevezette a tárolt eljárások tetszőleges .NET nyelven történő megírásának lehetőségét, a kiterjesztett tárolt eljárások pedig a tervek szerint a jövőben megszűnnek. Az Oracle DBMS pedig lehetővé teszi a tárolt eljárások beírását Java nyelv. Az IBM DB2-ben a tárolt eljárások és függvények hagyományos programozási nyelveken való írása hagyományos módszer, a kezdetektől támogatott, és az SQL eljárási kiterjesztés csak meglehetősen késői verziókban, az ANSI szabványba való felvétele után került bele a DBMS-be. Az Informix Java és C nyelven is támogatja az eljárásokat.

Az Oracle DBMS-ben a tárolt eljárások úgynevezett csomagokká kombinálhatók. Egy csomag két részből áll - egy csomagspecifikációból, amely egy tárolt eljárás definícióját adja meg, és egy csomagtörzsből, amely annak megvalósítását tartalmazza. Így az Oracle lehetővé teszi a programkód interfészének elkülönítését a megvalósítástól.

Az IBM DB2 DBMS-ben a tárolt eljárások modulokká kombinálhatók.

Szintaxis

ELJÁRÁS LÉTREHOZÁSA `p2`()

SQL BIZTONSÁGI MEGHATÁROZÓ

MEGJEGYZÉS "Egy eljárás"

KIVÁLASZTÁSA "Hello World!";

A kód első része egy tárolt eljárást hoz létre. A következő választható paramétereket tartalmaz. Ezután jön a neve, és végül maga az eljárás törzse.

A tárolt eljárás 4 jellemzője:

Nyelv: A hordozhatóság érdekében az alapértelmezett az SQL.

Determinisztikus: ha az eljárás mindig ugyanazt az eredményt adja vissza, és ugyanazokat a bemeneti paramétereket veszi fel. Ez a replikációs és regisztrációs folyamatra vonatkozik. Az alapértelmezett érték NEM DETERMINISTIC.

SQL biztonság: a felhasználói jogosultságokat a hívás során ellenőrzik. Az INVOKER a tárolt eljárást meghívó felhasználó. A DEFINER az eljárás „alkotója”. Az alapértelmezett érték a DEFINER.

Megjegyzés: Dokumentációs célból az alapértelmezett érték ""

Tárolt eljárás hívása

CALL tárolt_eljárás_neve (param1, param2, ....)

CALL eljárás1(10 , "karakterlánc paraméter" , @parameter_var);

Tárolt eljárás módosítása

A MySQL rendelkezik egy ALTER PROCEDURE utasítással az eljárások megváltoztatásához, de ez csak bizonyos jellemzők megváltoztatására alkalmas. Ha módosítania kell egy eljárás paramétereit vagy törzsét, törölje és hozza létre újra.

Eltávolítástárolvaeljárások

ELJÁRÁS HAJTÁSA, HA LÉTEZIK p2;

Ez egy egyszerű parancs. Az IF EXISTS utasítás hibát észlel, ha nem létezik ilyen eljárás.

Lehetőségek

CREATE PROCEDURE proc1(): üres paraméterlista

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): egy bemeneti paraméter. Az IN szó nem kötelező, mert az alapértelmezett paraméterek IN (in).

CREATE PROCEDURE proc1 (OUT változónév DATA-TYPE): egy paramétert adott vissza.

PROCEDŪRA LÉTREHOZÁSA proc1 (INOUT varname DATA-TYPE): egy paraméter, mind a bemenet, mind a visszatérés.

A változó deklarációs szintaxisa így néz ki:

DECLARE varname DATA-TYPE DEFAULT alapértelmezett érték;

A sorozat előző cikkében megvizsgáltuk, hogyan lehet adatokat kinyerni a táblákból, módosítani a szerkezetüket, létrehozni, módosítani és törölni adatbázisokat és a bennük lévő objektumokat. Ebben a cikkben részletesebben fogunk beszélni a kiszolgálói DBMS-ekre jellemző objektumokról: nézetek, triggerek és tárolt eljárások.

A sorozat első cikkében, amelyet magazinunk 3’2000-es számában tettek közzé, megjegyeztük, hogy a legtöbb modern szerver DBMS támogatja a nézeteket, triggereket és a tárolt eljárásokat. A nézeteket számos asztali DBMS is támogatja, például az Access, a dBase, a Clipper.

Meg kell jegyezni, hogy a triggereket és a tárolt eljárásokat általában olyan programozási nyelveken írják, amelyek az SQL nyelv eljárási kiterjesztései. Ezek a kiterjesztések olyan operátorokat tartalmaznak, amelyek lehetővé teszik olyan algoritmusok leírását, például a do...míg, if...then...else, amelyek nem szerepelnek magában az SQL nyelvben (ha emlékszel, az SQL nem eljárási nyelv , és megfogalmazhatsz benne feladatot, de nem írhatod le a végrehajtásának algoritmusait). A szabványos SQL nyelvtől eltérően az eljárási kiterjesztései semmilyen módon nem szabványosítottak, és a különböző DBMS-ek különböző szintaktikai konstrukciókat használnak ugyanazon algoritmikus konstrukciók megvalósításához, de a különböző DBMS-ek SQL-bővítményeinek szintaxisában mutatkozó különbségek megvitatása túlmutat ennek keretein. cikk.

A nézetek, triggerek és tárolt eljárások használatának szemléltetésére a Microsoft SQL Server 7.0-t és a DBMS-hez mellékelt NorthWind adatbázist választottuk.

A példák futtatása előtt vegye figyelembe, hogy a triggerek és tárolt eljárások megvalósítása és tárolása az Ön által használt DBMS-ben eltérhet az ebben a cikkben bemutatottaktól. Ezenkívül a kiszolgálóobjektumok létrehozásához rendelkeznie kell az adatbázis-adminisztrátor által biztosított megfelelő jogosultságokkal.

Vegye figyelembe azt is, hogy egyes ODBC-illesztőprogramok nem támogatják a tárolt eljárások ügyfélalkalmazásokból történő hívását, még akkor sem, ha ezeket maga a DBMS támogatja. Ebben az esetben azonban a tárolt eljárások továbbra is meghívhatók triggerekből.

Kezdjük a nézetekkel, majd beszéljük meg a tárolt eljárásokat, és fejezzük be a fejezetet a triggerek áttekintésével.

Reprezentáció

A nézet egy virtuális táblázat, amely általában egy vagy több tábla oszlopait tartalmazza. A valóságban a nézet nem tartalmaz adatokat, hanem csak egy SELECT típusú SQL lekérdezést, amely jelzi, hogy a nézet elérésekor mely adatokat és milyen táblákat kell venni. Ebből a szempontból a nézet egy tárolt lekérdezés.

A legtöbb esetben nézeteket használnak az adatbiztonság biztosítására. Például a felhasználók bizonyos kategóriái hozzáférhetnek egy nézethez, de nem azokhoz a táblákhoz, amelyek adatai ezt alkotják; Ezenkívül az SQL lekérdezés tartalmazhat egy USER paramétert (azt a nevet, amelyen a felhasználó bejelentkezik), ebben az esetben a nézet elérésekor rendelkezésre álló adatok a felhasználói névtől függenek.

A nézetek főbb jellemzői a következők:

• a nézetek táblázatként viselkednek;
• a nézetek nem tartalmaznak adatokat;
• a nézetek több tábla adatait is felhasználhatják.

Nézet létrehozásához az SQL CREATE VIEW utasítást, módosításához az ALTER VIEW utasítást, törléséhez pedig a DROP VIEW utasítást használhatjuk.

Kezdjük a CREATE VIEW utasítással, amely lehetővé teszi, hogy nézetet hozzunk létre az aktuális adatbázishoz.

CREATE VIEW záradék

A nézet létrehozására szolgáló utasítás szintaxisa egy SQL SELECT utasításhoz hasonlít néhány további kulcsszóval. Alább látható az egyszerűsített szintaxis:

NÉZET LÉTREHOZÁSA nézet_név AS select_utasításként

A view_name argumentum a nézet nevét adja meg. A Microsoft SQL Serverben használt kulcsszó lehetővé teszi a CREATE VIEW utasítás eredeti szövegének elrejtését a rendszermegjegyzések táblájában.

Az AS kulcsszó határozza meg, hogy a nézet elérésekor melyik SELECT lekérdezés kerül végrehajtásra. Vegye figyelembe, hogy ez a lekérdezés nem tartalmazhatja az ORDER BY, COMPUTE vagy COMPUTE BY, INTO kulcsszavakat, és nem hivatkozhat ideiglenes táblára.

Egy korábban létrehozott nézet módosításához használja a következő részben röviden ismertetett ALTER VIEW záradékot.

DROP VIEW ajánlat

Ez a záradék egy nézet eltávolítására szolgál az adatbázisból. Vegye figyelembe, hogy amikor töröl egy táblát az adatbázisból, az összes rá hivatkozó nézet is törlődik. Ezzel a záradékkal meg kell adnunk a törölni kívánt nézet nevét. A nézet törlése után minden információ törlődik a rendszertáblázatokból.

Egy másik eset, amikor egy nézetet törölni kell, ha az alapjául szolgáló táblák szerkezete megváltozott a nézet létrehozása óta. Ebben az esetben törölheti a nézetet, majd újra létrehozhatja a CREATE VIEW záradékkal.

Nézetek létrehozása és használata

A CREATE VIEW záradék olyan nézetek létrehozására szolgál, amelyek lehetővé teszik a meghatározott követelményeknek megfelelő adatok lekérését. A nézet az aktuális adatbázisban jön létre, és külön adatbázis-objektumként kerül tárolásra.

Nézet létrehozásának legjobb módja, ha létrehozunk egy SELECT utasítást, és annak tesztelése után hozzáadjuk a CREATE VIEW utasítás hiányzó részét. Nézzük meg a NorthWind adatbázis Termékek kategóriák szerint nézetének forráskódját (1. lista).

Az első sor kiemelve kiemelten, azt jelzi, hogy a nézet létrehozására szolgáló SQL utasítás miben tér el a szokásos SELECT lekérdezéstől, amely az adatok kijelölését végzi. Az ebben a nézetben található SELECT záradék két tábla mezőit választja ki – a CategoryName mezőt a KATEGÓRIÁK táblából és a ProductName, QuantityPerUnit, UnitsInStock, UnitsInStock, Discontinued mezőket a TERMÉKEK táblából. A két tábla adatait ezután a CategoryID mező segítségével összekapcsoljuk, és csak a még raktáron lévő termékek (lásd a WHERE kulcsszó utáni kritériumot) kerülnek be a kapott adatkészletbe. Ennek a nézetnek az eredményét az ábra mutatja. 1 .

Most hozzunk létre egy nézetet, amely a keleti régió összes területét mutatja. Ez a nézet a következő lekérdezésen alapul (2. lista).

Miután megbizonyosodtunk arról, hogy a SELECT záradék a kívánt eredményeket adja vissza, hozzáadunk egy CREATE VIEW utasítást, és elnevezzük a létrehozandó nézetet EASTTERR-nek (3. lista).

A prezentáció szövegének manuális létrehozása helyett használhat vizuális eszközöket, amelyek általában a DBMS-hez tartoznak. ábrán. A 2. ábra azt mutatja be, hogyan hozható létre ugyanaz a nézet a Nézettervező eszközzel, amely az szerves része Az Enterprise Manager a Microsoft SQL Server része.

A Nézettervező felső része lehetővé teszi a táblázatok kapcsolatának meghatározását, és hogy mely mezők jelenjenek meg a nézetben. Az alábbiakban megadhatja a táblák és mezők álneveit, az értékükre vonatkozó korlátozásokat és a megjelenítési módot. Az alábbiakban a nézet forrásszövegét és a végrehajtásának eredményeit közöljük.

Mielőtt befejeznénk rövid áttekintésötleteket, beszéljünk egy kicsit arról, hogyan lehet megszerezni További információ róluk. A Microsoft SQL Server 7.0-ban a következő rendszerben tárolt eljárásokat használhatjuk:

• Használhatja az sp_help rendszerben tárolt eljárást a megtekintési információk megszerzéséhez. Például az sp_help EastTerr információkat ad vissza az újonnan létrehozott nézetről;
• Használhatja az sp_helptext tárolt eljárást a nézet forrásszövegének beszerzéséhez;
• Az sp_depends rendszerben tárolt eljárással megkeresheti azon táblák listáját, amelyektől a nézet függ;
• Használhatja az sp_rename rendszerben tárolt eljárást egy nézet átnevezéséhez.

Ebben a részben azt néztük meg, hogyan lehet nézeteket használni bizonyos feltételeknek megfelelő adatok lekérésére. Térjünk azonban vissza az utolsó példához. A NorthWind adatbázisban négy régió található, és az összes régió területeinek felsorolásához négy különböző nézetre van szükségünk. Ezt a feladatot megkönnyíthetnénk, ha a RegionID értéket paraméterként adnánk át. Ez megtehető egy tárolt eljárással, amelyről a következő részben fogunk beszélni.

Tárolt eljárások

A tárolt eljárás az adatbázisban elnevezett objektumként tárolt és egyetlen kódrészletként végrehajtott SQL utasítások lefordított halmaza. A tárolt eljárások paramétereket fogadhatnak és adhatnak vissza. Amikor a felhasználó létrehoz egy tárolt eljárást, a szerver lefordítja azt, és egy megosztott gyorsítótárba helyezi, ami után a lefordított kódot több felhasználó is használhatja. Amikor egy alkalmazás tárolt eljárást használ, akkor paramétereket ad át neki, ha szükséges, és a szerver újrafordítás nélkül hajtja végre az eljárást.

A tárolt eljárások javíthatják az alkalmazás teljesítményét. Először is, az ügyfélalkalmazásból küldött szokásos SQL-lekérdezésekhez képest kevesebb időre van szükség a végrehajtásra való felkészüléshez, mivel már le vannak fordítva és tárolva. Másodszor, a hálózati forgalom ebben az esetben is kisebb, mint SQL lekérdezés továbbítása esetén, mivel kevesebb adatot továbbítanak a hálózaton. Rizs. A 3. ábra egy tárolt eljáráshívást mutat be kliens alkalmazás.

A tárolt eljárások automatikusan újrafordításra kerülnek, ha az általuk érintett objektumokon bármilyen változtatás történik; más szóval mindig relevánsak. Mint fentebb említettük, a tárolt eljárások paramétereket fogadhatnak el, lehetővé téve a különböző alkalmazásoknak, hogy ugyanazt az eljárást használják különböző bemeneti adatkészletekkel.

A tárolt eljárásokat általában az adatok hivatkozási integritásának fenntartására és az üzleti szabályok megvalósítására használják. Ez utóbbi további rugalmasságot biztosít, mert az üzleti szabályok változása esetén csak az eljárásszöveg módosítható az ügyfélalkalmazások megváltoztatása nélkül.

Az eljárások létrehozásához, módosításához és törléséhez speciális SQL utasítások állnak rendelkezésre: CREATE PROCEDURE, ALTER PROCEDURE és DROP PROCEDURE. A következő részben megnézzük őket.

ELJÁRÁS LÉTREHOZÁSA

A CREATE PROCEDURE záradék egy tárolt eljárás létrehozására szolgál. A következő egyszerűsített szintaxissal rendelkezik:

CREATE PROC proc_name [ (@parameter data_type) [= alapértelmezett] ] [...] AS sql_statements

A proc_name argumentum a tárolt eljárás nevét adja meg, amelynek egyedinek kell lennie az aktuális adatbázison belül. A @parameter argumentum egy eljárásparamétert ad meg. A CREATE PROCEDURE záradékban megadhat egy vagy több paramétert. Ha egy paraméternek nincs alapértelmezett értéke, akkor azt a felhasználónak (vagy a kliens alkalmazásnak) át kell adnia az eljárás meghívásakor. A Microsoft SQL Server 7.0-ban a tárolt eljárás paramétereinek száma nem haladhatja meg az 1024-et; alapértelmezés szerint NULL értékeik lehetnek.

Vegye figyelembe azonban, hogy bizonyos általános adathozzáférési mechanizmusok előírhatják további korlátozások a tárolt eljárások paramétereinek számával. Például az Oracle 8 BDE illesztőprogramja csak olyan eljárásokkal tud működni, amelyek paramétereinek száma nem haladja meg a 10-et.

A data_type argumentum adja meg a paraméter adattípusát. Az alapértelmezett kulcsszó használható az alapértelmezett értékek beállítására - ez lehet állandó vagy NULL. Ha alapértelmezett érték van megadva, az eljárás paraméterérték megadása nélkül is meghívható. Ha egy eljárás paramétert használ a LIKE kulcsszóval, az alapértelmezett értéke helyettesítő karaktereket (%, _ és [^]) tartalmazhat.

Az OUTPUT kulcsszó azt jelzi, hogy ez egy visszatérési paraméter.

Az AS kulcsszó azt a műveletet határozza meg, amelyet az eljárásnak végre kell hajtania, tetszőleges számú SQL utasítás és utasítás formájában egy kiszolgálóspecifikus SQL eljárási kiterjesztésben.

A CREATE PROCEDURE záradékkal létrehozott eljárás az aktuális adatbázisba kerül mentésre. A Microsoft SQL Serverben az eljárásnevek a sysobjects rendszertáblában, a forrásszöveg pedig a syscomments táblában találhatók.

Egy korábban létrehozott tárolt eljárás módosításához az ALTER PROCEDURE záradékot kell használni, amelyet a következő részben ismertetünk röviden.

HAJTJA EL az ELJÁRÁS ajánlatát

Ez a záradék a tárolt eljárások adatbázisból való eltávolítására szolgál. A DROP PROCEDURE záradék egy argumentumot tartalmaz, az elvetendő eljárás nevét.

Amikor töröl egy tárolt eljárást, az azzal kapcsolatos információk törlődnek a sysobjects és syscomments rendszertáblázatokból.

Tárolt eljárások létrehozása és használata

A nézetek részben felhívtuk a figyelmet arra, hogy kényelmes lenne, ha a nézetnek egy RegionID értéket tartalmazó paramétert adnánk át, hogy a NorthWind adatbázis négy régiója közül válasszuk ki az egyiket. Vessünk még egy pillantást egy olyan lekérdezésre, amely egy régióban található területek listáját adja vissza:

SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Régió ON Territories.RegionID = Régió.RégióID WHERE Területek.RegionID = 1

Másik régió kiválasztásához meg kell változtatnunk a feltételt a WHERE záradékban a lekérdezés utolsó sorában. Ezért ha változót használunk (nevezzük RegID-nek), akkor a négy régió egyikét úgy tudjuk kiválasztani, hogy a lekérdezés többi részét nem változtatjuk meg.

A NorthWind adatbázisban négy régió található, 1-től 4-ig számozva. Ez azt jelenti, hogy a RegID változónak egész típusúnak kell lennie. A tárolt eljáráskód az alábbiakban látható:

ELJÁRÁS LÉTREHOZÁSA ShowRégió @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Régió ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID

Figyeljük meg, hogy a SELECT lekérdezés szinte teljes szövegét érintetlenül hagytuk (dőlt betűvel van), és csak egy CREATE PROCEDURE záradékot adtunk hozzá az újonnan létrehozott tárolt eljárás nevével (az első sorban), egy paraméter deklarációval (a második sorban), és az AS kulcsszó, amely a ténylegesen műveleteket végrehajtó mondatok elején jelzi.

Az SQL Server Query Analyzer programban a RegID =2 esetén létrehozott eljárás végrehajtásának eredménye az ábrán látható. 3.

Nyilvánvalóan a tárolt eljárásokat nem csak a nézetek fejlett verzióinak vagy az intelligens SELECT-lekérdezések megvalósítására használhatjuk. A tárolt eljárások olyan mechanizmusokat biztosítanak, amelyek lehetővé teszik számos rutinfeladat automatizálását.

A Microsoft SQL Server 7.0-ban rendszerben tárolt eljárásokat is használhatunk a szokásos tárolt eljárásokkal való együttműködéshez:

• sp_stored_procedures - megjeleníti a tárolt eljárások listáját;
• sp_helptext - a tárolt eljárás forrásszövegét mutatja;
• sp_depends - információkat mutat a tárolt eljárások függőségeiről;
• sp_procooption - beállítja vagy beállítja a tárolt eljárások beállításait;
• sp_recompile - újrafordítja az eljárást a következő híváskor;
• sp_rename - megváltoztatja az eljárás nevét.

Rendszerben tárolt eljárások

Mivel Microsoft SQL Serverről beszélünk, érdemes megjegyezni a benne megvalósított hatalmas számú rendszerben tárolt eljárást. A rendszerben tárolt eljárások nevei SP_ vagy XP_ karakterekkel kezdődnek, és a fő adatbázisban tárolódnak. Fentebb már leírtunk néhány gyakran használt rendszerben tárolt eljárást.

Felhívjuk figyelmét, hogy a triggerek nem adhatnak vissza adatokat a felhasználónak.

A CREATE TRIGGER záradékban két speciális tábla használható. Például a törölt és beszúrt táblák szerkezete megegyezik annak a táblával, amelyen a triggert meghatározták, és a felhasználó által módosított rekordok régi és új értékeit tartalmazzák. Például a következő SQL utasítást használhatjuk a törölt rekordok megkereséséhez:

SELECT * FROM törölve

táblázatban A 3. ábra a törölt és beszúrt táblák tartalmát mutatja az összes lehetséges adatmódosításhoz.

Egy meglévő trigger módosításához használja az ALTER TRIGGER záradékot. A következő részben beszélünk róla.

Először is hozzá kell adnunk két új mezőt a táblázathoz, amelyek ezeket az információkat tartalmazzák. Nevezzük őket UpdatedBy-nek (a rekordot utoljára frissítő menedzser neve) és UpdatedWhen-nek (a rekord módosításának időpontja). Ezután hozzunk létre egy KeepTrack nevű triggert. Íme a kódja:

CREATE TRIGGER KeepTrack ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM beszúrva, Customers WHERE beillesztve.CustomerID = Customers.CustomerID

Amint az a trigger forráskódjából látható, az Ügyfelek táblán minden INSERT és UPDATE művelet után lefut. Ez a trigger a kezelő (adatbázis-felhasználó) nevét tárolja a Customers.UpdatedBy mezőben, és a változás dátumát és időpontját a Customers.UpdatedWhen mezőben. Ezeket az adatokat a rendszer a beillesztett ideiglenes táblából kéri le.

Amint látható, ez a trigger lehetővé teszi a változások és az új rekordok táblázatba történő beszúrásának figyelését.

Mielőtt befejeznénk a triggerek rövid áttekintését, meg kell mondanunk, hogy hol találhat információkat az elérhető triggerekről. A sysobjects tábla a triggerekről és típusaikról tárol információkat, a syscomments tábla pedig a forrásszövegüket.

Következtetés

Ebben a részben többféle adatbázis-objektumot néztünk meg – tárolt eljárásokat, nézeteket és triggereket. A következőket tanultuk:

• A nézet egy virtuális tábla, amely általában egy vagy több tábla oszlopainak részhalmazaként jön létre. A CREATE VIEW záradék a nézet létrehozására szolgál, az ALTER VIEW záradék a módosításra, a DROP VIEW záradék pedig a törlésére szolgál.
• A tárolt eljárás az adatbázisban elnevezett objektumként tárolt és egyetlen kódrészletként végrehajtott SQL utasítások lefordított halmaza. A CREATE PROCEDURE záradék egy tárolt eljárás létrehozására szolgál, az ALTER PROCEDURE a módosításra, a DROP PROCEDURE pedig a törlésre.
• Az eseményindító egy speciális típusú tárolt eljárás, amely automatikusan meghívásra kerül, amikor egy adott tábla adatait INSERT, DELETE vagy UPDATE SQL utasítással hozzáadják, törlik vagy módosítják. A triggerek a CREATE TRIGGER záradékkal jönnek létre. A trigger módosításához használja az ALTER TRIGGER záradékot, törléséhez pedig a DROP TRIGGER záradékot.

ComputerPress 12"2000

Tárolva SQL eljárások egy végrehajtható programmodul, amely különféle objektumok formájában tárolható. Más szóval, ez egy objektum, amely SQL utasításokat tartalmaz. Ezek a tárolt eljárások végrehajthatók az alkalmazáskliensben a jó teljesítmény elérése érdekében. Ezenkívül az ilyen objektumokat gyakran más parancsfájlokból vagy akár más szakaszokból hívják meg.

Bevezetés

Sokan úgy vélik, hogy hasonlóak a különféle eljárásokhoz (illetve, kivéve az MS SQL-t). Talán ez igaz. Hasonló paraméterekkel rendelkeznek, és hasonló értékeket tudnak produkálni. Sőt, bizonyos esetekben összeérnek. Például kombinálják őket DDL és DML adatbázisokkal, valamint felhasználói funkciókkal (kódnéven UDF).

A valóságban az SQL tárolt eljárások rendelkeznek széleskörű előnyök, amelyek megkülönböztetik őket a hasonló folyamatoktól. Biztonság, programozási rugalmasság, termelékenység – mindez egyre több adatbázissal dolgozó felhasználót vonz. Az eljárások népszerűségének csúcspontja 2005-2010 között volt, amikor megjelent a Microsoft „SQL Server Management Studio” nevű programja. Segítségével az adatbázisokkal való munka sokkal könnyebbé, praktikusabbá és kényelmesebbé vált. Ez évről évre népszerűvé vált a programozók körében. Ma már teljesen ismerős program, amely az adatbázisokkal „kommunikáló” felhasználók számára egyenrangú az Excellel.

Ha egy eljárást meghívnak, azt a szerver azonnal feldolgozza, szükségtelen folyamatok vagy felhasználói beavatkozás nélkül. Ezt követően bármilyen törlést, végrehajtást vagy módosítást végezhet. Mindezért a DDL operátor a felelős, aki egyedül végzi el a legösszetettebb műveleteket az objektumok feldolgozásához. Ráadásul mindez nagyon gyorsan megtörténik, és a szerver valójában nem töltődik be. Ez a sebesség és teljesítmény lehetővé teszi nagy mennyiségű információ nagyon gyors átvitelét a felhasználótól a szerverre és fordítva.

Ennek a technológiának az információkkal való munkavégzéshez való megvalósításához számos programozási nyelv létezik. Ide tartozik például az Oracle PL/SQL, az InterBase és Firebird rendszerek PSQL-je, valamint a klasszikus Microsoft Transact-SQL. Mindegyiket tárolt eljárások létrehozására és végrehajtására tervezték, ami lehetővé teszi a nagy adatbázis-feldolgozók számára, hogy saját algoritmusaikat használják. Erre azért is van szükség, hogy az ilyen információkat kezelők minden objektumot megvédhessenek harmadik felek illetéktelen hozzáférésétől, és ennek megfelelően bizonyos adatok létrehozásától, módosításától vagy törlésétől.

Termelékenység

Ezek az adatbázis-objektumok többféleképpen programozhatók. Ez lehetővé teszi a felhasználók számára, hogy kiválasszák a legmegfelelőbb módszert, így erőfeszítést és időt takarítanak meg. Ezenkívül az eljárás maga is feldolgozásra kerül, így elkerülhető a szerver és a felhasználó közötti kommunikációra fordított hatalmas idő. Ezenkívül a modul bármikor újraprogramozható és a kívánt irányba változtatható. Különösen érdemes figyelni az SQL tárolt eljárás elindításának sebességére: ez a folyamat gyorsabban megy végbe, mint a többi hasonló, ami kényelmessé és univerzálissá teszi.

Biztonság

Ez a fajta információfeldolgozás abban különbözik a hasonló folyamatoktól, hogy garantálja fokozott biztonság. Ezt az a tény biztosítja, hogy teljesen kizárható más felhasználók hozzáférése az eljárásokhoz. Ez lehetővé teszi az adminisztrátor számára, hogy önállóan végezzen műveleteket velük, anélkül, hogy félne az információk elfogásától vagy az adatbázishoz való jogosulatlan hozzáféréstől.

Adatátvitel

Az SQL tárolt eljárás és a kliens alkalmazás közötti kapcsolat a paraméterek és a visszatérési értékek használata. Ez utóbbinak nem kell átadnia az adatokat a tárolt eljárásnak, hanem ezeket az információkat (főleg a felhasználó kérésére) az SQL számára feldolgozzák. Miután a tárolt eljárás befejezte a munkáját, visszaküldi az adatcsomagokat (de ismételten opcionálisan) az őt hívó alkalmazásnak, különféle módszerek, melynek segítségével egy tárolt SQL-eljárás hívása és egy visszatérés is végrehajtható, pl.

Adatátvitel Output típusú paraméter használatával;

Adattovábbítás a return operátor segítségével;

Adattovábbítás a kiválasztási operátor segítségével.

Most nézzük meg, hogyan néz ki ez a folyamat belülről.

1. Hozzon létre egy EXEC tárolt eljárást SQL-ben

Eljárást létrehozhat az MS SQL-ben (Managment Studio). Az eljárás létrehozása után megjelenik az adatbázis programozható csomópontjában, amelyben a létrehozási eljárást az operátor végrehajtja. Az SQL tárolt eljárások végrehajtásához egy EXEC folyamatot használnak, amely magának az objektumnak a nevét tartalmazza.

Amikor létrehoz egy eljárást, először annak neve jelenik meg, majd egy vagy több hozzárendelt paraméter. A paraméterek opcionálisak lehetnek. A paraméter(ek), azaz az eljárás törzsének felírása után néhány szükséges műveletet el kell végezni.

A lényeg az, hogy egy testben lokális változók helyezkedhetnek el, és ezek a változók az eljárásokhoz képest is lokálisak. Más szóval, csak a Microsoft SQL Server eljárás törzsében tekinthetők meg. A tárolt eljárások ebben az esetben helyinek minősülnek.

Tehát egy eljárás létrehozásához szükségünk van az eljárás nevére és legalább egy paraméterre, mint az eljárás törzsére. Ne feledje, hogy ebben az esetben nagyszerű lehetőség egy eljárás létrehozása és végrehajtása az osztályozóban szereplő sémanévvel.

Az eljárás törzse bármilyen lehet, például tábla létrehozása, tábla egy vagy több sorának beszúrása, az adatbázis típusának és jellegének megállapítása stb. Az eljárási szerv azonban korlátozza bizonyos műveletek végrehajtását. Az alábbiakban felsorolunk néhány fontos korlátozást:

A szervezet nem hozhat létre más tárolt eljárást;

A test nem kelthet hamis benyomást a tárgyról;

A testnek nem szabad kiváltót létrehoznia.

2. Változó beállítása az eljárás törzsében

Lokális változókat állíthat be az eljárás törzséhez, és akkor azok kizárólag az eljárás törzsében lesznek. Célszerű a tárolt eljárástörzs elején változókat létrehozni. De az adott objektum törzsében bárhol beállíthat változókat.

Néha észreveheti, hogy egy sorban több változó van beállítva, és minden változó paramétert vessző választ el. Vegye figyelembe azt is, hogy a változó előtt @ előtag van. Az eljárás törzsében beállíthatja a változót, ahol csak akarja. Például a @NAME1 változó deklarálható az eljárástörzs végén. Egy deklarált változó értékének hozzárendeléséhez személyes adatok halmazát kell használni. Ellentétben azzal a helyzettel, amikor egynél több változót deklarálnak ugyanabban a sorban, ebben a helyzetben csak egy személyes adatkészlet kerül felhasználásra.

A felhasználók gyakran felteszik a kérdést: "Hogyan lehet több értéket hozzárendelni egy utasításhoz egy eljárás törzsében?" Jól. Érdekes kérdés, de sokkal könnyebb megtenni, mint gondolnád. Válasz: Olyan párok használata, mint például "Select Var = érték". Ezeket a párokat vesszővel elválasztva használhatja.

A legtöbbben különféle példák az emberek egy egyszerű tárolt eljárás létrehozását és végrehajtását mutatják be. Egy eljárás azonban képes olyan paramétereket elfogadni, hogy az őt hívó folyamat értékei közel állnak hozzá (de nem mindig). Ha egybeesnek, akkor megfelelő folyamatok indulnak el a testben. Például, ha létrehoz egy eljárást, amely elfogad egy várost és régiót a hívótól, és adatokat ad vissza arról, hogy hány szerző tartozik a megfelelő városhoz és régióhoz. Az eljárás lekérdezi az adatbázis szerzői tábláit, például a Pubs-t, hogy végrehajtsa ezt a szerzői számlálást. Például ezeknek az adatbázisoknak a beszerzéséhez a Google letölti az SQL-szkriptet az SQL2005 oldalról.

Az előző példában az eljárás két paramétert vesz fel, amelyek angol nyelv feltételesen @State és @City lesz a neve. Az adattípus megegyezik az alkalmazásban meghatározott típussal. Az eljárás törzsében @TotalAuthors belső változók találhatók, és ez a változó a szerzők számának megjelenítésére szolgál. Következik a lekérdezés kiválasztása szakasz, amely mindent kiszámít. Végül a számított érték kinyomtatásra kerül a kimeneti ablakban a print utasítás segítségével.

Hogyan hajtsunk végre egy tárolt eljárást SQL-ben

Az eljárás kétféleképpen hajtható végre. Az első mód paraméterek átadásával azt mutatja meg, hogy a vesszővel tagolt lista hogyan kerül végrehajtásra az eljárás neve után. Tegyük fel, hogy két értékünk van (mint az előző példában). Ezeket az értékeket a @State és @City eljárásparaméter-változók segítségével gyűjtjük össze. Ebben a paraméterátadási módszerben a sorrend fontos. Ezt a módszert ordinális argumentum átadásnak nevezzük. A második módszernél a paraméterek már közvetlenül hozzá vannak rendelve, és ebben az esetben a sorrend nem fontos. Ez a második módszer nevesített argumentumok átadásaként ismert.

Az eljárás kissé eltérhet a szokásostól. Minden ugyanaz, mint az előző példában, de csak itt vannak eltolva a paraméterek. Ez azt jelenti, hogy először a @City paraméter kerül tárolásra, a @State pedig az alapértelmezett érték mellé. Az alapértelmezett paraméter általában külön kiemelve van. Az SQL tárolt eljárások csak paraméterként kerülnek átadásra. Ebben az esetben, feltéve, hogy az "UT" paraméter helyettesíti az alapértelmezett "CA" értéket. A második végrehajtás során csak egy argumentumérték kerül átadásra a @City paraméterhez, és a @State paraméter az alapértelmezett "CA" értéket veszi fel. A tapasztalt programozók azt tanácsolják, hogy alapértelmezés szerint minden változónak a paraméterlista végén kell lennie. Ellenkező esetben a végrehajtás nem lehetséges, és akkor megnevezett argumentumok átadásával kell dolgozni, ami hosszabb és összetettebb.

4. SQL Server tárolt eljárások: Visszaküldési módszerek

Három fontos módja van az adatok küldésének egy úgynevezett tárolt eljárásban. Az alábbiakban felsoroljuk őket:

Egy tárolt eljárás értékét adja vissza;

Tárolt eljárási paraméter kimenet;

A tárolt eljárások egyikének kiválasztása.

4.1 Értékek visszaadása SQL tárolt eljárásokból

Ebben a technikában egy eljárás értéket rendel egy helyi változóhoz, és visszaadja azt. Egy eljárás közvetlenül is visszaadhat egy állandó értéket. A következő példában létrehoztunk egy eljárást, amely a szerzők teljes számát adja vissza. Ha összehasonlítja ezt az eljárást az előzőekkel, láthatja, hogy a nyomtatási érték megfordul.

Most nézzük meg, hogyan kell végrehajtani egy eljárást és kinyomtatni a visszatérési értékét. Az eljárás végrehajtásához egy változó beállítására és nyomtatásra van szükség, amely a teljes folyamat után megtörténik. Ne feledje, hogy a print utasítás helyett használhat Select utasítást, például Select @RetValue vagy OutputValue parancsot.

4.2 SQL tárolt eljárási paraméterek kimenete

A válaszérték egyetlen változó visszaadására használható, amit az előző példában láttunk. Az Output paraméter használata lehetővé teszi, hogy egy eljárás egy vagy több változó értéket küldjön a hívónak. A kimeneti paramétert pontosan ez az „Output” kulcsszó jelöli ki az eljárás létrehozásakor. Ha egy paramétert kimeneti paraméterként adunk meg, akkor az eljárásobjektumnak értéket kell hozzárendelnie. Az SQL-ben tárolt eljárások, amelyekre az alábbiakban példákat láthatunk, ebben az esetben összefoglaló információkkal térnek vissza.

Példánkban két kimeneti név lesz: @TotalAuthors és @TotalNoContract. Ezek a paraméterek listájában vannak feltüntetve. Ezek a változók értékeket rendelnek hozzá az eljárás törzsén belül. Amikor kimeneti paramétereket használunk, a hívó az eljárás törzsében láthatja a beállított értéket.

Ezenkívül az előző forgatókönyvben két változót deklarálnak az MS SQL Server által a kimeneti paraméterben tárolt eljárások által tárolt értékek megtekintéséhez. Ezután az eljárás a „CA” paraméter normál értékének megadásával történik. A következő paraméterek kimeneti paraméterek, ezért a deklarált változók a megadott sorrendben kerülnek átadásra. Vegye figyelembe, hogy változók átadásakor a kimeneti kulcsszó is itt van beállítva. Az eljárás sikeres befejezése után a kimeneti paraméterek által visszaadott értékek megjelennek az üzenetablakban.

4.3 Az SQL tárolt eljárások egyikének kiválasztása

Ezt a technikát arra használják, hogy egy értékkészletet adattáblaként (RecordSet) küldjenek vissza a hívó tárolt eljáráshoz. Ebben a példában az SQL tárolt eljárás @AuthID paraméterekkel lekérdezi a Szerzők táblát úgy, hogy szűri a visszaadott rekordokat az adott @AuthId paraméterrel. A Select utasítás dönti el, hogy mit küldjön vissza a tárolt eljárás hívójának. A tárolt eljárás végrehajtásakor az AuthId visszaadásra kerül. Ez az eljárás itt mindig csak egy rekordot ad vissza, vagy egyet sem. Egy tárolt eljárás azonban nem korlátozza egynél több rekord visszaküldését. Nem ritka, hogy olyan példákat látunk, amikor az adatok kiválasztott paraméterek felhasználásával kerülnek visszaadásra, kiszámított változókkal, több összeg megadásával.

Végül

A tárolt eljárás egy meglehetősen komoly programmodul, amely visszaad vagy átmegy, és a kliens alkalmazásnak köszönhetően beállítja a szükséges változókat is. Mivel a tárolt eljárás önmagában fut a szerveren, elkerülhető a hatalmas mennyiségű adatcsere a szerver és a kliens alkalmazás között (egyes számításoknál). Ez lehetővé teszi a terhelés csökkentését SQL szerver, ami természetesen előnyös a birtokosaiknak. Az egyik altípus a T SQL tárolt eljárások, de ezek tanulmányozása szükséges azok számára, akik lenyűgöző adatbázisokat készítenek. Nagy, sőt hatalmas számú árnyalat is hasznos lehet a tárolt eljárások tanulmányozásakor, de erre inkább azoknak van szükségük, akik a programozásba terveznek bekapcsolódni, akár szakmailag is.

1. Vegyen be egy sort az eljárásokba – SET NOCOUNT ON: Az SQL szerver minden DML kifejezésnél gondosan visszaküld egy üzenetet, amely tartalmazza a feldolgozott rekordok számát. Ez az információ Hasznos lehet számunkra a kód hibakeresése közben, de utána teljesen használhatatlan lesz. A SET NOCOUNT ON írásával letiltjuk ezt a funkciót. Több kifejezést vagy\vagy ciklust tartalmazó tárolt eljárásokhoz ezt az akciót jelentős teljesítménynövekedést adhat, mert a forgalom jelentősen csökkenni fog.

PROC LÉTREHOZÁSA dbo.ProcName
MINT
SET NOCOUNT ON;
-- Itt van az eljárás kódja
SELECT oszlop1. A dbo.TblTable1
-- Kapcsolja be a SET NOCOUNT kezdőállapotba
SET NO COUNT OFF;
MEGY

2. Használja a séma nevét az objektumnévvel együtt: Nos, szerintem egyértelmű. Ez a művelet megmondja a szervernek, hogy hol keresse az objektumokat, és ahelyett, hogy véletlenszerűen turkálna a tárolókban, azonnal tudni fogja, hová kell mennie és mit kell vinnie. A nagyszámú adatbázissal, táblázattal és tárolt eljárással jelentősen megspórolhatja az időnket és az idegeinket.

SELECT * FROM dbo.MyTable -- Ezt jó megtenni
-- Ahelyett
SELECT * FROM MyTable -- És ezt rossz
--Hívási eljárás
EXEC dbo.MyProc -- Ismét jó
--Ahelyett
EXEC MyProc -- Rossz!

3. Ne használja az „sp_” előtagot a tárolt eljárások nevében: Ha az eljárás neve "sp_"-val kezdődik, az SQL Server először a fő adatbázisában fog keresni. A tény az, hogy adott előtag privát belső szerveren tárolt eljárásokhoz használják. Ezért használata többletköltségekhez, sőt hibás eredményekhez vezethet, ha az Önével azonos nevű eljárás található az adatbázisában.

4. Használja az IF LÉTEZ (SELECT 1) lehetőséget az IF EXISTS (SELECT *) helyett: Egy másik táblában lévő rekord meglétének ellenőrzésére az IF EXISTS utasítást használjuk. Ez a kifejezés igazat ad vissza, ha legalább egy értéket ad vissza a belső kifejezés, nem számít „1”, minden oszlop vagy egy táblázat. A visszaküldött adatokat alapvetően semmilyen módon nem használjuk fel. Így az adatátvitel során a forgalom tömörítéséhez logikusabb az „1” használata, amint az alább látható:

HA LÉTEZIK (VÁLASSZON 1 A sysobjects közül
WHERE név = "MyTable" ÉS típus = "U")

5. Használja a TRY-Catch-et a hibák észleléséhez: 2005 előtt a szerverek minden kérés után hatalmas számú hibaellenőrzést írtak az eljárásban. A több kód mindig több erőforrást és több időt emészt fel. A 2005-ös SQL Serverrel egy korrektebb ill kényelmes módja megoldások erre a problémára:

KEZDJEN EL PRÓBÁLÁST
--kód
VÉGE A PRÓBÁZAT VÉGE
KEZDJE EL FOGÁST
--hiba a kód elkapásakor
VÉGE FOGÁS

Következtetés
Lényegében ennyi van mára. Még egyszer megismétlem, hogy itt csak azokat a technikákat soroljuk fel, amelyeket személyesen alkalmaztam a gyakorlatom során, és ezek hatékonyságát garantálhatom.

P.S.
Első hozzászólásom, ne ítélj túl szigorúan.