SQL tárolt eljárások: létrehozás és használat. Tárolt eljárások Az Sql szerver által tárolt eljárások

A tárolt eljárások típusai

Az SQL Serverben többféle típus létezik tárolt eljárások.

• Szisztémás tárolt eljárások különféle adminisztratív tevékenységek elvégzésére tervezték. Szinte az összes szerveradminisztrációs művelet az ő segítségükkel történik. Azt mondhatjuk, hogy a rendszer tárolt eljárások olyan interfész, amely rendszertáblázatokkal való munkát biztosít, ami végső soron a felhasználói és rendszeradatbázisok rendszertáblázatainak megváltoztatásán, hozzáadásán, törlésén és az adatok visszakeresésén múlik. Szisztémás tárolt eljárások sp_ előtaggal vannak ellátva, a rendszer adatbázisában tárolódnak, és bármely más adatbázis kontextusában meghívhatók.
• Egyedi tárolt eljárások hajtson végre bizonyos intézkedéseket. Tárolt eljárások- egy teljes adatbázis objektum. Ennek eredményeként mindegyik tárolt eljárás egy adott adatbázisban található, ahol lefut.
• Ideiglenes tárolt eljárások csak rövid ideig léteznek, ezután a szerver automatikusan megsemmisíti őket. Helyi és globális csoportokra oszthatók. Helyi ideiglenes tárolt eljárások csak abból a kapcsolatból hívhatók meg, amelyben létrejöttek. Egy ilyen eljárás létrehozásakor olyan nevet kell adni, amely egyetlen # karakterrel kezdődik. Mint minden ideiglenes objektum, tárolt eljárások Ezek a típusok automatikusan törlődnek, amikor a felhasználó leválasztja, újraindítja vagy leállítja a szervert. Globális ideiglenes tárolt eljárások elérhető minden olyan szerverkapcsolathoz, amelyen ugyanaz az eljárás. Meghatározásához elegendő egy ## karakterekkel kezdődő nevet adni neki. Ezek az eljárások a kiszolgáló újraindításakor vagy leállításakor törlődnek, vagy ha a kapcsolat, amelynek környezetében létrejöttek, bezárul.

Tárolt eljárások létrehozása, módosítása és törlése

Teremtés tárolt eljárás a következő feladatok megoldását foglalja magában:

• típusának meghatározása tárolt eljárás: ideiglenes vagy egyedi. Ezenkívül létrehozhatja saját rendszerét tárolt eljárás, adjon neki egy nevet az sp_ előtaggal, és helyezze el a rendszer adatbázisában. Ez az eljárás elérhető lesz a helyi szerver bármely adatbázisában;
• hozzáférés tervezése. Alkotás közben tárolt eljárás ne feledje, hogy ugyanazokkal a hozzáférési jogokkal rendelkezik az adatbázis-objektumokhoz, mint az azt létrehozó felhasználó;
• meghatározás tárolt eljárási paraméterek. Mint a legtöbb programozási nyelvben megtalálható eljárások, tárolt eljárások lehetnek bemeneti és kimeneti paraméterei ;
• kód fejlesztés tárolt eljárás. Az eljáráskód bármilyen SQL-parancs sorozatát tartalmazhatja, beleértve mások meghívását is. tárolt eljárások.

Új létrehozása és egy meglévő módosítása tárolt eljárás a következő paranccsal történik:

<определение_процедуры>::= (CREATE | ALTER ) eljárásnév [;szám] [(@paraméter_neve adattípus ) [=alapértelmezett] ][,...n] AS sql_utasítás [...n]

Tekintsük ennek a parancsnak a paramétereit.

Az sp_ , # , ## előtagok használatával a létrehozott eljárás rendszerként vagy ideiglenes eljárásként definiálható. Ahogy a parancs szintaxisából is látszik, nem szabad megadni a tulajdonos nevét, akihez a létrehozott eljárás fog tartozni, valamint annak az adatbázisnak a nevét, ahová el kell helyezni. Így a teremtett befogadása érdekében tárolt eljárás egy adott adatbázisban a CREATE PROCEDURE parancsot az adott adatbázis környezetében kell futtatnia. Ha a testből kezelik tárolt eljárás Rövidített nevek használhatók az azonos adatbázisban lévő objektumokhoz, azaz az adatbázisnév megadása nélkül. Ha más adatbázisokban található objektumokra szeretne hivatkozni, meg kell adni az adatbázis nevét.

A névben szereplő szám az azonosító szám tárolt eljárás, amely egyedileg határozza meg egy eljárások csoportjában. Az eljárások kezelésének kényelme érdekében logikailag ugyanaz a típus tárolt eljárások azonos névvel, de eltérő azonosító számmal csoportosíthatók.

Bemeneti és kimeneti adatok átadása a létrehozottban tárolt eljárás paraméterek használhatók, amelyek nevének, akárcsak a helyi változók nevének, @ szimbólummal kell kezdődnie. Egy tárolt eljárás Több lehetőséget is megadhat vesszővel elválasztva. Az eljárás törzse nem használhat olyan helyi változókat, amelyek neve megegyezik az eljárás paramétereinek nevével.

A megfelelő adattípus meghatározásához tárolt eljárási paraméter, bármilyen SQL adattípus megfelelő, beleértve a felhasználó által meghatározottakat is. A CURSOR adattípus azonban csak mint kimeneti paraméter tárolt eljárás, azaz jelezve kulcsszó KIMENET.

Az OUTPUT kulcsszó jelenléte azt jelenti, hogy a megfelelő paraméter adatokat küld vissza tárolt eljárás. Ez azonban egyáltalán nem jelenti azt, hogy a paraméter nem alkalmas az értékek átadására tárolt eljárás. Az OUTPUT kulcsszó megadása arra utasítja a kiszolgálót, hogy lépjen ki tárolt eljárás rendelje hozzá a paraméter aktuális értékét ahhoz a helyi változóhoz, amelyet az eljárás meghívásakor a paraméter értékeként adtunk meg. Vegye figyelembe, hogy az OUTPUT kulcsszó megadásakor az eljárás hívásakor a megfelelő paraméter értéke csak helyi változó segítségével állítható be. A normál paraméterekhez engedélyezett kifejezések vagy állandók nem megengedettek.

A VARYING kulcsszó a következővel együtt használatos

Utolsó frissítés: 2017.08.14

Az adatműveletek gyakran olyan utasítások halmazát jelentik, amelyeket végre kell hajtani bizonyos sorrend. Például egy termék vásárlásának hozzáadásakor adatokat kell megadni a rendelési táblázatban. Előtte azonban ellenőriznie kell, hogy a megvásárolt termék raktáron van-e. Talán ebben az esetben több további feltételt is ellenőrizni kell. Valójában egy termék megvásárlásának folyamata több műveletet is tartalmaz, amelyeket egy bizonyos sorrendben kell végrehajtani. És ebben az esetben optimálisabb lenne ezeket a műveleteket egy objektumba foglalni - tárolt eljárás(tárolt eljárás).

Azaz lényegében a tárolt eljárások egyetlen egységként végrehajtott utasítások halmaza. Így a tárolt eljárások lehetővé teszik az összetett műveletek egyszerűsítését és egyetlen objektumba hozását. Az áruvásárlás folyamata megváltozik, ennek megfelelően elegendő az eljárás kódjának megváltoztatása. Vagyis az eljárás a kódkezelést is leegyszerűsíti.

Ezenkívül a tárolt eljárások lehetővé teszik a táblázatokban lévő adatokhoz való hozzáférés korlátozását, és ezáltal csökkentik az adatokkal kapcsolatos szándékos vagy öntudatlan nemkívánatos cselekvések valószínűségét.

És egy másik fontos szempont a teljesítmény. A tárolt eljárások általában gyorsabban futnak, mint a hagyományos SQL utasítások. Ennek az az oka, hogy az eljáráskód az első futtatáskor kerül lefordításra, majd lefordított formában mentésre kerül.

Tárolt eljárás létrehozásához használja a CREATE PROCEDURE vagy a CREATE PROC parancsot.

Így a tárolt eljárásnak három fő jellemzője van: a kód egyszerűsítése, biztonsága és teljesítménye.

Tegyük fel például, hogy van egy táblázat az adatbázisban, amely a termékekre vonatkozó adatokat tárolja:

TÁBLÁZAT LÉTREHOZÁSA Termékek (ID INT IDENTITY PRIMARY KEY, Product Name NVARCHAR(30) NOT NULL, Gyártó NVARCHAR(20) NOT NULL, ProductCount INT ALAPÉRTELMEZETT 0, Price MONEY NOT NULL);

Hozzon létre egy tárolt eljárást az adatok lekéréséhez ebből a táblázatból:

USE productsdb; ELJÁRÁS LÉTREHOZÁSA ProductSummary AS SELECT Product Name AS Termék, Gyártó, Ár a termékektől

Mivel a CREATE PROCEDURE parancsot külön csomagban kell meghívni, a GO parancs az aktuális adatbázist beállító USE parancs után egy új csomag meghatározásához használatos.

Az eljárás nevét az AS kulcsszónak kell követnie.

Az eljárás törzsének a szkript többi részétől való elkülönítéséhez az eljáráskódot gyakran egy BEGIN...END blokkba helyezik:

USE productsdb; FOGYÁS ELJÁRÁS LÉTREHOZÁSA ProductSummary AS BEGIN SELECT Product Name AS Termék, Gyártó, Ár Termékek VÉGE;

Az eljárás hozzáadása után az SQL Server Management Studio adatbázis-csomópontjában láthatjuk az alcsomópontban Programozhatóság -> Tárolt eljárások:

Az eljárást pedig egy vizuális felületen keresztül is kezelhetjük.

Eljárás végrehajtása

Egy tárolt eljárás végrehajtásához az EXEC vagy EXECUTE parancsot hívják:

EXEC termékösszefoglaló

Eljárás törlése

Egy eljárás eldobásához használja a DROP PROCEDURE parancsot:

HAJTÁS ELJÁRÁSA Product Summary

A MySQL 5 számos új funkcióval rendelkezik, amelyek közül az egyik legjelentősebb a tárolt eljárások létrehozása. Ebben az oktatóanyagban arról fogok beszélni, hogy mik ezek, és hogyan könnyíthetik meg az életét.

Bevezetés

A tárolt eljárás az ismétlődő műveletek beágyazásának módja. A tárolt eljárások használhatók változók deklarálására, adatáramlás kezelésére és egyéb programozási technikák használatára.

Létrehozásuk oka egyértelmű, és a gyakori használat is megerősíti. Másrészt, ha azokkal beszélgetsz, akik rendszeresen dolgoznak velük, akkor a vélemények két teljesen ellentétes oldalra oszlanak. Ne feledkezz meg róla.

Mögött

• Logika megosztása más alkalmazásokkal. A tárolt eljárások magukba foglalják a funkcionalitást; ez biztosítja az adathozzáférést és a felügyeleti kapcsolatot a különböző alkalmazások között.
• A felhasználók elkülönítése az 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édőmechanizmust biztosít. Az előző pont szerint, ha csak tárolt eljárásokon keresztül férhet hozzá az adatokhoz, akkor senki más nem törölheti az adatait az SQL DELETE paranccsal.
• Jobb teljesítmény a csökkent hálózati forgalom eredményeként. A tárolt eljárásokkal több lekérdezés kombinálható.

vs

• Megnövekedett terhelés az adatbázis-kiszolgálón, mivel 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ás logikáját: a kiszolgáló kódjában és a tárolt eljárások kódjában, ami megnehezíti az adatok manipulálásának folyamatát.
• Az egyik DBMS-ről a másikra (DB2, SQL Server stb.) történő átállás problémákhoz vezethet.

Az általam használt eszköz a MySQL Query Browser, amely meglehetősen szabványos az adatbázisokkal való interakcióhoz. Eszköz parancs sor A MySQL egy másik kiváló választás. Ezt azért mondom el, mert mindenki kedvenc phpMyAdminja nem támogatja a tárolt eljárások futtatását.

Egyébként az elemi táblázatszerkezetet használom, hogy könnyebben megértsd ezt a témát. Tárolt eljárásokról beszélek, és elég összetettek ahhoz, hogy a nehézkes táblaszerkezetben is elmélyüljünk.

1. lépés: Állítson be egy korlátozót

A határoló egy karakter vagy karaktersorozat, amely jelzi a MySQL-kliensnek, hogy befejezte az SQL utasítás megírását. Az elválasztójel hosszú idők óta pontosvessző volt. Problémák adódhatnak azonban, mert egy tárolt eljárásnak több kifejezése is lehet, amelyek mindegyikének pontosvesszővel kell végződnie. Ebben az oktatóanyagban a "//" karakterláncot használom elválasztóként.

2. lépés: Hogyan kell dolgozni a tárolt eljárásokkal

Hozzon létre egy tárolt eljárást

DELIMITER // ELJÁRÁS LÉTREHOZÁSA `p2` () NYELV SQL MEGHATÁROZOTT SQL BIZTONSÁGI MEGHATÁROZÓ MEGJEGYZÉS "Egy eljárás" BEGIN SELECT "Hello World!"; VÉGE//

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

A tárolt eljárások nevei megkülönböztetik a kis- és nagybetűket. Ezenkívül nem hozhat létre több eljárást ugyanazzal a névvel. Egy tárolt eljáráson belül nem lehetnek olyan kifejezések, amelyek magát az adatbázist módosítják.

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 hívás során a felhasználó jogosultságait 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

Tárolt eljárás hívásához írja be a CALL kulcsszót, majd az eljárás nevét, majd zárójelben a paramétereket (változókat vagy értékeket). Zárójelek megadása kötelező.

CALL tárolt_eljárás_neve (paraméter1, 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 módosítására, de ez csak bizonyos jellemzők módosítására alkalmas. Ha módosítania kell az eljárás paramétereit vagy törzsét, törölnie kell, és újra létre kell hoznia.

Tárolt eljárás törlése

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.

3. lépés: Beállítások

Nézzük meg, hogyan adhatunk át paramétereket egy tárolt eljárásnak.

• 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 (bejövő).
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): egy visszatérési paraméter.
• PROCEDŪRA LÉTREHOZÁSA proc1 (INOUT varname DATA-TYPE): egy paraméter, bemenet és kimenet egyaránt.

Természetesen több, különböző típusú paramétert is beállíthat.

IN paraméter példa

DELIMITER // ELJÁRÁS LÉTREHOZÁSA `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS eredmény; VÉGE//

OUT paraméter példa

DELIMITER // ELJÁRÁS LÉTREHOZÁSA `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Ez egy teszt"; VÉGE //

INOUT paraméter példa

DELIMITER // ELJÁRÁS LÉTREHOZÁSA `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; VÉGE //

4. lépés: Változók

Most megtanítom, hogyan hozhat létre változókat és tárolhatja azokat eljárásokban. Ezeket kifejezetten deklarálnia kell egy BEGIN/END blokk elején, az adattípusukkal együtt. Miután deklarált egy változót, ugyanazon a helyen használhatja, mint munkamenet-változók, literálok vagy oszlopnevek.

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

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

Deklaráljunk néhány változót:

DECLARE a, b INT ALAPÉRTELMEZETT 5; DECLARE str VARCHAR(50); NYILATKOZÁS ma TIMESTAMP ALAPÉRTELMEZETT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Munka változókkal

Miután deklaráltunk egy változót, beállíthatjuk az értékét a SET vagy a SELECT paranccsal:

DELIMITER // ELJÁRÁS LÉTREHOZÁSA `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT ALAPÉRTELMEZETT 5; DECLARE str VARCHAR(50); NYILATKOZÁS ma TIMESTAMP ALAPÉRTELMEZETT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES(a); SET str = "Én egy karakterlánc vagyok"; SELECT CONCAT(str,paramstr), ma FROM table2 WHERE b >=5; VÉGE //

5. lépés: Áramlásszabályozó szerkezetek

A MySQL támogatja az IF, CASE, ITERATE, LEAVE LOOP, WHILE és REPEAT konstrukciókat az áramlásvezérléshez egy tárolt eljáráson belül. Megvizsgáljuk, hogyan kell használni az IF-et, a CASE-t és a WHILE-t, mivel ezek a leggyakrabban használtak.

HA építés

Az IF konstrukció segítségével feltételeket tartalmazó feladatokat hajthatunk végre:

DELIMITER // ELJÁRÁS LÉTREHOZÁSA `proc_IF` (IN param1 INT) BEGIN DECLARE változó1 INT; SET változó1 = param1 + 1; HA változó1 = 0 THEN SELECT változó1; VÉGE HA; HA param1 = 0, AKKOR VÁLASZTÁSA "Paraméterérték = 0"; ELSE SELECT "Paraméter értéke<>0"; END IF; END //

CASE építés

A CASE egy másik módszer a feltételek ellenőrzésére és a megfelelő megoldás kiválasztására. Ez egy nagyszerű módja annak, hogy sok IF-konstrukciót helyettesítsen. A konstrukció kétféleképpen írható le, rugalmasságot biztosítva több feltételes kifejezés kezeléséhez.

DELIMITER // ELJÁRÁS LÉTREHOZÁSA `proc_CASE` (IN param1 INT) BEGIN DECLARE változó1 INT; SET változó1 = param1 + 1; CASE változó1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES(változó1); ELSE INSERT INTO table1 VALUES(99); VÉGÜZ; VÉGE //

DELIMITER // ELJÁRÁS LÉTREHOZÁSA `proc_CASE` (IN param1 INT) BEGIN DECLARE változó1 INT; SET változó1 = param1 + 1; CASE WHEN változó1 = 0 THEN INSERT INTO table1 VALUES(param1); WHEN változó1 = 1 THEN INSERT INTO table1 VALUES(változó1); ELSE INSERT INTO table1 VALUES(99); VÉGÜZ; VÉGE //

építés közben

Technikailag háromféle hurok létezik: a WHILE ciklus, a LOOP ciklus és a REPEAT ciklus. Darth Vader programozási technikáját is végigcsinálhatja: GOTO utasításokat. Íme egy példa hurok:

DELIMITER // ELJÁRÁS LÉTREHOZÁSA `proc_WHILE` (IN param1 INT) BEGIN DECLARE változó1, változó2 INT; SET változó1 = 0; WHILE változó1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

6. lépés: Kurzorok

A kurzorok a lekérdezés által visszaadott sorok halmazának bejárására és az egyes sorok feldolgozására szolgálnak.

A MySQL támogatja a kurzorokat a tárolt eljárásokban. Íme egy rövid szintaxis a kurzor létrehozásához és használatához.

DECLARE kurzornév CURSOR FOR SELECT ...; /*Kurzor deklarálása és kitöltése */ CONTINUE HANDLER DECLARE FOR NOT FOUND /*Mi a teendő, ha nincs több rekord*/ OPEN kurzornév; /*Kurzor megnyitása*/ A kurzornév BEKERÜLÉSE VÁLTOZÓBA [, változó]; /*Érték hozzárendelése egy változóhoz, amely megegyezik az oszlop aktuális értékével*/ CLOSE kurzornév; /*kurzor bezárása*/

Ebben a példában meg fogunk tenni néhányat egyszerű műveletek kurzor segítségével:

DELIMITER // ELJÁRÁS LÉTREHOZÁSA `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; NYILATKOZAT FOLYTATÁS KEZELŐT A NEM TALÁLT HASZNÁLATHOZ b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; HA b = 0, AKKOR BEÁLLÍTJA c = c + a; VÉGE HA; END WHILE; CLOSE cur1; SET param1 = c; VÉGE //

A kurzoroknak három tulajdonsága van, amelyeket meg kell értenie a váratlan eredmények elkerülése érdekében:

• Érzéketlen: megnyitás után a kurzor nem tükrözi a táblázatban később bekövetkezett változásokat. Valójában a MySQL nem garantálja, hogy a kurzor frissül, ezért ne hagyatkozzon rá.
• Csak olvasható: A kurzorok nem módosíthatók.
• Nincs visszatekerés: a kurzor csak egy irányba mozoghat – előre, nem ugorhat át sorokat kijelölésük nélkül.

Következtetés

Ebben az oktatóanyagban bemutattam a tárolt eljárások alapjait és néhány, a hozzájuk kapcsolódó konkrét tulajdonságot. Természetesen elmélyítenie kell tudását olyan területeken, mint a biztonság, az SQL-kifejezések és az optimalizálás, mielőtt igazi MySQL-eljárási guruvá válna.

Ki kell számítania a tárolt eljárások használatának előnyeit konkrét alkalmazás, és csak ezután hozza létre a szükséges eljárásokat. Általában eljárásokat használok; véleményem szerint ezeket a biztonságuk, a kódkarbantartásuk és az általános kialakításuk miatt projektekben kellene megvalósítani. Ne feledje továbbá, hogy a MySQL-eljárások még folyamatban vannak. Javulás várható a funkcionalitás és fejlesztések terén. Kérem, ossza meg véleményét.

Vegye bele az eljárásokba a - SET NOCOUNT ON sort:

Az SQL szerver minden DML utasítá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 során, de utána teljesen használhatatlan lesz. A SET NOCOUNT ON írásával letiltjuk ezt a funkciót. Több kifejezést vagy\and 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.

Transzaktálj SQL-t

A sémanév használata az objektumnévvel:

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.

Transzaktálj SQL-t

SELECT * FROM dbo.MyTable --Ez jó dolog -- A SELECT * FROM MyTable helyett --Ez egy rossz dolog --Hívja meg az EXEC eljárást dbo.MyProc --Ismét jó --Az EXEC MyProc helyett --Rossz!

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, akkor az SQL Server először a fő adatbázisában fog keresni. A helyzet az, hogy ezt az előtagot a szerver személyes belső tárolt eljárásaihoz 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.

Használja az IF LÉTEZ (SELECT 1) lehetőséget az IF LÉTEZ (SELECT *) helyett:

Annak ellenőrzésére, hogy egy rekord létezik-e egy másik táblában, 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 adatok elvileg semmilyen módon nem kerülnek felhasználásra. Í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ó.

Tárolt eljárások

Ennek a fejezetnek a témája 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 tárolt eljárások (angol nyelvű, stoied eljárások) lehetővé teszik az alkalmazáslogika jelentős részének adatbázis szintű megvalósítását, és ezzel növelik a a teljes alkalmazás teljesítménye, az adatfeldolgozás központosítása és a feladatok elvégzéséhez szükséges kód mennyiségének csökkentése Szinte minden kellően összetett adatbázis-alkalmazás tárolt eljárások használatát igényli.
A tárolt eljárások használatának ezen jól ismert előnyei mellett, amelyek a legtöbb relációs adatbázis-rendszerre jellemzőek, az InterBase tárolt eljárások szinte teljes adathalmazok szerepét is betölthetik, ami lehetővé teszi az általuk visszaadott eredmények felhasználását a szokásos SQL-lekérdezésekben.
A kezdő fejlesztők gyakran úgy gondolják a tárolt eljárásokat, mint konkrét 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 egy kliens alkalmazásban, a nyelven megvalósítani. magas szint
Tehát pontosan mik 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 egy szerverfolyamaton belül fut le, és képes manipulálni az adatbázisban lévő adatokat, valamint visszaküldeni a végrehajtás eredményét az azt hívó kliensnek (azaz triggernek, HP-nek, alkalmazásnak).
A HP-ben rejlő erőteljes képességek alapja egy procedurális programozási nyelv, amely tartalmazza mind a módosított, normál SQL utasításokat, mint az INSERT, UPDATE és SELECT, valamint az elágazó és hurkolt eszközöket (IF, WHILE), valamint hibakezelő eszközöket. és kivételes helyzetek A tárolt eljárások nyelve 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 az SP-k sokkal kompaktabbak, 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 nyelve közötti különbségeket a Triggerek fejezet (1. rész) tárgyalja részletesen.

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

Ideje létrehozni az első tárolt eljárást, és példaként felhasználni a tárolt eljárások létrehozásának folyamatát. Először azonban érdemes néhány szót ejteni 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ére és hibakeresésére szolgáló eszközöket. Az InterBase dokumentációjában javasolt a CP szövegét tartalmazó SQL script fájlok felhasználásával eljárásokat létrehozni, amelyek az isql interpreterbe kerülnek, és ezzel létrehozni és módosítani a CP-t Hiba esetén az isql egy üzenetet jelenít meg, amelyen sorában történt a hiba. Javítsa ki a hibát, és ismételje meg újra. A szó modern értelmében vett hibakeresést, azaz a végrehajtás nyomon követését, a változók köztes értékeinek megtekintésével, egyáltalán nem tárgyaljuk. Nyilvánvaló, hogy 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ének szokásos 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 lehet, de valójában minden nagyon egyszerű.A szintaxis fokozatos elsajátítása érdekében nézzünk meg fokozatosan egyre összetettebb példákat.
Tehát itt van egy példa egy nagyon egyszerű tárolt eljárásra, amely két számot vesz fel bemenetként, ö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 az SP_Add, majd az XP bemeneti paraméterei - first_arg és second_arg - zárójelben szerepelnek vesszővel elválasztva – a típusukat megjelölve.
A bemeneti paraméterek listája a CREATE PROCEDURE utasítás nem kötelező része - vannak esetek, amikor az eljárás az eljárástö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 használnak tömböket és felhasználó által definiált típusokat - tartományok

Ezután következik a RETURNS kulcsszó, amely után zárójelben szerepelnek a visszatérési paraméterek, megjelölve a típusukat - jelen esetben csak egy - Eredményt.
Ha az eljárásnak nem kellene paramétereket visszaadnia, akkor hiányzik a RETURNS szó és a visszaadott paraméterek listája.
A RETURNSQ után az AS kulcsszó következik. Mielőtt az AS kulcsszó elmegy cím,és utána- techo eljárások.
Egy tárolt eljárás törzse a belső (lokális) változóinak deklarációinak listája (ha vannak, az alábbiakban részletesebben tárgyaljuk), pontosvesszővel (;) elválasztva, és egy utasításblokk, amelyet BEGIN END utasítás zárójelek közé zárnak. Ebben az esetben a CP 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 átadására van szükség oda, ahonnan a tárolt eljárást hívták.

Elválasztó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ő a szabványos parancselválasztó az SQL-ben – ez egy jelzés az SQL értelmező számára, hogy a parancs szövegét teljes egészében beírták, és azt fel kell dolgozni. Nem derülne ki, hogy miután az SP közepén pontosvesszőt talált, az SQL értelmező úgy tekinti, 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 értelmetlen. Valójában, ha létrehoz egy fájlt, amelybe a fenti példát írja, hozzáad egy adatbázis-kapcsolati parancsot, és megpróbálja végrehajtani ezt az SQL-szkriptet az isql értelmező segítségével, akkor hibaüzenet jelenik meg a váratlan, az értelmező szerint a végéhez kapcsolódóan. a parancs egy tárolt eljárás létrehozásához. 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 a parancsfájl-parancselválasztót a szöveg után pontosvesszőtől eltérő karakterre kell módosítania. A HP visszaállítja azt. Az SQL mondatelvá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:

KIFEJEZÉS 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 elkészült, valahogy meg kell hívnunk, paramétereket kell átadnunk neki, és meg kell kapnunk a visszaadott eredményeket. Ezt nagyon egyszerű megtenni – csak írjon egy SQL lekérdezést a következő formában:

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

Ez a lekérdezés egy olyan sort ad vissza, amely csak egy Eredmény mezőt tartalmaz, amely a 181,35 és 23,09, azaz a 204,44 számok összegét tartalmazza.
Így eljárásunk a hétköznapokban is használható SQL lekérdezések, amely mind az ügyfélprogramokban, mind más HP-kban vagy triggerekben fut. Eljárásunknak ezt a használatát a SUSPEND parancs használata teszi lehetővé a tárolt eljárás végén.
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. E két típusú CP működésében az a különbség, hogy a kiválasztási 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, és a végrehajtható eljárások vagy egyáltalán nem, vagy csak egyet adnak vissza. a Returns-ben felsorolt ​​kimeneti paraméterek halmaza, ahol a paraméterek egy sora. 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ás ugyanazzal a létrehozási szintaxissal és formailag azonos, í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 egy bizonyos típusú hívás eljárásának kialakításában rejlik. Ez azt jelenti, hogy egy kiválasztási eljárást kifejezetten a SELECT lekérdezésből kell meghívni, és egy végrehajtható eljárást kifejezetten az EXECUTE PROCEDURE használatával hívható meg. Nézzük meg, mi a különbség a két HP típus kialakításában.
Ahhoz, hogy megértsük, hogyan működik a kiválasztási eljárás, egy kicsit mélyebbre kell menni az elméletben. Képzeljünk el egy normál SQL-lekérdezést, például SELECT ID, NAME FROM Table_example. Végrehajtása eredményeként a kimenetben egy két oszlopból (ID és NAME) és bizonyos számú sorból (a Table_example tábla sorainak számával megegyező) álló táblázatot kapunk. A lekérdezés eredményeként visszaadott táblát SQL adathalmaznak is nevezik. Gondoljuk végig, hogyan jön létre az adatkészlet ennek a lekérdezésnek a végrehajtása során A szerver, miután megkapta a lekérdezést, meghatározza, hogy mely táblákhoz tartozik, majd kideríti, hogy a lekérdezés melyik részhalmaza. a táblák rekordjait bele kell foglalni a lekérdezés eredményébe. Ezután a szerver beolvassa a lekérdezés eredményét kielégítő minden rekordot, kiválasztja belőle a szükséges mezőket (esetünkben ezek az ID és a 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 adathalmaz sorról sorra jön létre, így a tárolt eljárásokban is! A fő különbség a kiválasztott eljárások és a végrehajtható eljárások között az, hogy az előbbiek több sort, míg az utóbbiak csak egy sort adnak vissza. Ezért ezeket eltérően alkalmazzák: a kiválasztási eljárást a SELECT paranccsal hívják meg, amely "megköveteli", hogy az eljárás visszaadja az összes olyan rekordot, amelyet vissza tud adni. A végrehajtható eljárást az EXECUTE PROCEDURE segítségével hívják meg, amely csak egy sort "vesz ki" a CP-ből, a többit pedig figyelmen kívül hagyja (még ha létezik is!)
Nézzünk egy példát egy kiválasztási eljárásra, hogy világosabb legyen. A megbocsátáshoz hozzunk létre egy tárolt eljárást, amely pontosan úgy működik SELECT lekérdezés ID, NAME FROM Table_Example, azaz egyszerűen kiválasztja az ID és a NAME mezőket a teljes táblából. Íme 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

Vessünk egy pillantást a Simple_Select_SP nevű eljárás műveleteire. Amint láthatja, nincs bemeneti paramétere, és két kimeneti paramétere van - ID és NAME. A legérdekesebb 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áljon 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 sorhoz helyezze a kiválasztott értékeket a procID és procName változókba, majd hajtson végre valamilyen műveletet ezeken a változókon.
Meglepett arcot ölthet, és megkérdezheti: „Változók? Milyen más változók 9?” Ennek a fejezetnek egyfajta meglepetése, hogy változókat használhatunk tárolt eljárásokban. XP-ben 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 deklarációt az AS kulcsszó után és az első BEGIN szó elé kell helyezni. A helyi változó deklarációja í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 beszúrnia az AS és a BEGIN közé

DECLARE VARIABLE MyInt INTEGER;

Példánkban a változók kettősponttal kezdődnek. Ez azért történik, mert a FOR SELECT SQL parancson belül érhetők el, így a SELECT-ben használt táblák mezőinek és a változóknak a megkülönböztetéséhez kettőspontot kell előznie. Hiszen a változóknak pontosan ugyanaz a neve, mint a táblák mezőinek!
A változó neve 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ó kettőspont nélkül érhető el, például:

procName="Valamilyen név";

De térjünk vissza az eljárásunk lényegéhez. A FOR SELECT záradék nem táblázat - adathalmaz - formájában adja vissza az adatokat, hanem soronként. Minden visszaadott mezőt a saját változójában kell elhelyezni: ID => procID, NAME => procName. A DO részben ezeket a változókat a rendszer elküldi annak az ügyfélnek, amely a SUSPEND paranccsal meghívta a p>eljárást.
Í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 CP törzsében egy ciklus van szervezve, kitöltve a kapott változó paramétereket. És ennek a ciklusnak a törzsének végén van egy SUSPEND parancs, amely visszaadja a következő adatsort az ügyfélnek.

Hurok és elágazás operátorok

A FOR SELECT... DO parancson kívül, amely egy ciklust szervez egy bizonyos kijelölés rekordjain keresztül, létezik egy másik ciklustípus - a WHILE...DO, amely lehetővé teszi a ciklus megszervezését bármely feltétel ellenőrzése alapján. Íme egy példa egy HP WHILE..DO hurkot használó példájá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, amelyben 1 és 99 közötti egész számok négyzetei lesznek.
Amellett, hogy egy SQL lekérdezés és a klasszikus ciklus eredményeit iterálja, a tárolt eljárásnyelv az IF...THEN..ELSE utasítást használja, amely lehetővé teszi az elágazás megszervezését a \feltételek végrehajtásától függően. hasonló a legtöbb elágazási utasításhoz 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 árat az átlagárral és a megadott fix százalékos értékkel egyenlőnek állítja be.
3. Ha az aktuális ár kisebb vagy egyenlő, mint az átlagár, akkor az árat az előző árral, plusz a régi és az átlagár közötti különbség felével egyenlőnek állítja be.
4. Visszaadja a táblázat összes módosított sorát.

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

ELJÁRÁS LÉTREHOZÁSA IncreasePrices(
2. százalék DUPLA PONTOSSÁG növelése)
VISSZATÉRÉS (ID INTEGER, NÉV VARCHAR(SO), new_price DOUBLE
PRECISION AS

Az eljárás neve IncreasePrices lesz, egy Peiceni21nciease bemeneti paraméterrel rendelkezik, amely DOUBLE PRECISION típusú, és 3 kimeneti paraméterrel - ID, NAME és new_pnce. Megjegyzendő, hogy az első két kimeneti paraméternek ugyanaz a neve, mint a Table_example táblában, amellyel dolgozni fogunk, ezt a tárolt eljárásnyelv szabályai lehetővé teszik.
Most deklarálnunk kell egy helyi változót, amely az átlagérték tárolására szolgál. Az Ego 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. Nyissuk meg a CP törzsét kulcsszó BEGIN.
Először is végre kell hajtanunk az algoritmusunk első lépését - az átlagár kiszámítását. Ehhez a következő lekérdezést fogjuk használni:

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

Ez a lekérdezés egy AVG összesítő függvényt használ, amely a PRICE_1 mező átlagos értékét adja vissza a kiválasztott lekérdezési sorok között – esetünkben a PRICE_1 átlagos értékét a teljes Table_example táblában. A lekérdezé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.
Ennek a lekérdezésnek az a jellemzője, hogy mindig pontosan egy 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 a 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 ár átlagos értékét. Most át kell mennie a teljes táblázaton, össze kell hasonlítania az egyes rekordok árértékét az átlagárral, és meg kell tennie a megfelelő lépéseket.
A kezdetektől fogva minden rekord iterációjá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 oMinden rekord rendezése*/
VÉGE

Amikor ez a konstrukció végrehajtódik, az adatok soronként kerülnek lehívásra a Table_example táblából, és az egyes sorokban lévő mezőértékek az ID, NAME és new_pnce változókhoz lesznek hozzárendelve. Természetesen ne feledje, hogy ezek a változók kimeneti paraméterként vannak deklarálva, de nem kell aggódnia, hogy a kiválasztott adatok eredményként jelennek meg: az a tény, hogy a kimeneti paraméterekhez hozzá van rendelve valami, nem jelenti azt, hogy a HP-t hívó kliens azonnal megkapja ezeket az értékeket! A paraméterek csak a SUSPEND parancs végrehajtásakor kerülnek átadásra, előtte pedig normál változóként használhatjuk a kimeneti paramétereket - példánkban ezt tesszük a new_price paraméterrel.
Tehát a BEGIN... .END ciklus törzsén belül fel tudjuk dolgozni az egyes sorok értékeit. Mint emlékszik rá, meg 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 jelenlegi ár nagyobb, mint az átlagár*/
KEZDŐDIK
/*, majd telepítse új ár egyenlő az átlagárral, plusz egy fix százalék */
ú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ítsa be az árat az előző árral, plusz a régi és az átlagár közötti különbség felével */
ú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

Mint látható, egy meglehetősen nagy IF konstrukciót kaptunk, amit nehéz lenne megérteni, ha nem lennének /**/ karakterekbe zárt megjegyzések.
A kalkulált különbözet ​​szerinti árváltoztatáshoz az UPDATE utasítást használjuk, amivel módosíthatjuk 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 záradékban 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 tartalmazzák azokat az adatokat, amelyeket vissza kell adnunk 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 újra folytatódik - és így fog folytatódni mindaddig, amíg a FOR SELECT...DO végigmegy a lekérdezésének minden rekordján.
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 ritkán használják, mivel elsősorban a hurok megszakításához van szükség egy bizonyos feltétel elérésekor.
Ebben az esetben, amikor az eljárást a SELECT utasítás hívta meg, és az EXIT fejezte 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 sort, akkor a szekvenciát kell használnia

FÜGG.
KIJÁRAT;

Az EXIT fő célja egyedi adatkészletek beszerzése, paraméterek visszaadása az EXECUTE PROCEDURE-n keresztüli meghívással. Ebben az esetben a kimeneti paraméterek értékei be vannak állítva, de az SQL adatkészlet nem jön létre belőlük, és az eljárás véget ér.
Írjuk ki 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 IncreasePrices(
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 DUPLA PONTOSSÁG;
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
/*itt minden rekordot feldolgozunk*/
HA (új_pnce > avg_price) AKKOR /*ha a jelenlegi ár magasabb az átlagárnál*/
KEZDŐDIK
/*állítson be új árat, amely megegyezik az átlagárral plusz egy fix százalékos értékkel */
ú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 az árat úgy állítja be, mint az előző ár, plusz a régi é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ás példa az alapvető tárolt eljárások és triggernyelvi konstrukciók 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 egymásba ágyazási szintje megengedett, azonban ne feledje, hogy a kiszolgálón lévő szabad erőforrások elfogyhatnak a maximális HP-beágyazás elérése előtt.
A tárolt eljárások egyik gyakori felhasználási módja az adatbázisban tárolt fastruktúrák feldolgozása. A fákat gyakran használják anyagjegyzékben, raktárban, HR-ben és más általános alkalmazásokban.
Nézzünk egy példát egy tárolt eljárásra, amely egy bizonyos típusú összes terméket kiválasztja, egy bizonyos beágyazási szinttől kezdve.
Tegyük fel, hogy a következő problémamegjelenítésünk van: van egy árujegyzékünk hierarchikus struktúra ebből a fajtából:

Á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óriák jegyzékének ez a szerkezete különböző mélységű ágakat tartalmazhat. és idővel növekedni is. A mi feladatunk, hogy a könyvtárból a "teljes név kibontásával" válogatást biztosítsunk az összes véges elemből, bármelyik 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 - Frontal Classic
Mosógépek - Frontal Narrow

Határozzuk meg a termékkatalógusban tárolt információk tárolására szolgáló táblák szerkezetét. Egyszerűsített sémát használunk egy 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_JÓ));

Létrehozunk egy GoodsTree táblát, amelyben csak 3 mező van: ID_GOOD - intelligens kategória azonosító, ID_PARENT_GOOD - a kategória szülőfa azonosítója é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 az adott idegen kulcs ezt nyomon követi. 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.
Tegyük a táblázatunkba a következő adatokat:

Most, hogy megvan a helyünk az adatok tárolására, elkezdhetünk egy tárolt eljárást létrehozni, amely az összes „végső” termékkategóriát „kibontott” formában jeleníti meg – például a „háromkamrás” kategória esetében a kategória így fog kinézni: "Háztartási gépek Hűtőszekrények Háromkamrás".
A fastruktúrákat feldolgozó tárolt eljárások saját terminológiát fejlesztettek ki. A fa minden elemét csomópontnak nevezzük; az egymásra utaló 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.
A tárolt eljárásunk bemenete egy kategóriaazonosító lesz, amelyből kell kezdenünk a lefúrá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_GOOD nincs gyermeke, akkor az a fa "levele" és bekerül az eredmények közé * /
HA (NOT EXISTS(
SELECT * FROM GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
AZUTÁN
KEZDŐDIK
/* Adja át a fa "levelét" az eredményeknek */
FÜGG.
VÉGE
MÁS
/* Gyermekekkel rendelkező csomópontokhoz */
KEZDŐDIK
/*tárolja a szülőcsomópont nevét egy ideiglenes változóban */
CURR_CHILD_NAME=teljes_árunév;
/* rekurzívan futtassa ezt az eljárást */
FOR
SELECT ID_CHILD_JÓ,teljes_árunév
FROM GETFULLNAME(:ID_CHILD_JÓ)
INTO:ID_CHILD_JÓ, :teljes_árunév
KEZDJEN EL
/*a szülőcsomópont nevének hozzáadása a talált., gyermeknévhez a karakterlánc-összefűzési művelet használatával || */
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, a következőket kapjuk:

Mint látható, egy rekurzív tárolt eljárás segítségével végigjártuk a teljes kategóriafát, és kikövetkeztettük az ágak legvégén található "levél" kategóriák teljes nevét.

Következtetés

Ezzel befejeztük a tárolt eljárásnyelv főbb jellemzőinek áttekintésé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ó főbb fogalmakat. A HP leírt tervei és tervezési technikái 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ő – „Az InterBase tárolt eljárásnyelv kiterjesztett szolgáltatásai” című fejezetben lesz szó, amely a kivételkezelésnek, a tárolt eljárások hibakezelésének és a tömbökkel való munkának szentelődik.