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 nagymértékben hasonlítanak a magas szintű nyelvek közönséges eljárásaihoz, rendelkezhetnek bemeneti és kimeneti paraméterekkel, helyi változókkal, 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ásokban hurkok és elágazások is lehetségesek, azaz utasítások segítségével irányíthatják a végrehajtási folyamatot.
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 lekérdezések 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 eredményhalmazokat, azaz egy SELECT lekérdezés eredményeit adhatják vissza. Az ilyen eredményhalmazok feldolgozhatók kurzorok használatával, más 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 és a kurzorok kezelésére, amelyek lehetővé teszik, hogy egy táblázatban több soron át hurkoljon. Az SQL szabvány IF, LOOP, REPEAT, CASE és még sok más kifejezést biztosít a munkavégzéshez. A tárolt eljárások elfogadhatnak változókat, visszaadhatnak eredményeket, vagy módosíthatják és visszaadhatják azokat, attól függően, hogy a változót 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égrehajthatnak önmagukban SQL-lekérdezéseket, vagy nem.
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édelmi 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. Tárolt eljárásokkal több lekérdezés kombinálható.
Ellen
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.
A tárolt eljárások célja és előnyei
A tárolt eljárások javítják a teljesítményt, bővítik a programozási lehetősé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 manipulálá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, összetett tranzakciós logika végrehajtását. Egyetlen utasítás lehetővé teszi egy olyan összetett szkript meghívását, amely egy tárolt eljárásban található, így elkerülhető, hogy több száz parancsot küldjön 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 (elemzik, és adathozzáférési tervet állítanak elő). A jövőben a feldolgozása gyorsabb lesz. Az Oracle DBMS az adatszótárban tárolt, tárolt eljárási kódot értelmezi. Az Oracle 10g-től kezdve a tárolt procedurális kód C-ben, majd a célgép gépi kódjába történő úgynevezett natív fordítása (natív fordítása) támogatott, amely után a tárolt eljárás meghívásakor a lefordított objektumkód közvetlenül végrehajtva.
Programozási lehetőségek
Miután létrehozott egy tárolt eljárást, bármikor meghívhatja, 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 bármikor módosíthatja, hogy megfeleljen az új szabályoknak. 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, hogy korlátozza vagy teljesen kizárja a közvetlen felhasználói hozzáférést az adatbázistáblákhoz, í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 néhány DBMS támogatja 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 elszigetelését a felhasználótól, ami biztosítja az adatbázis integritását és megbízhatóságát.
Az olyan műveletek, mint például az "SQL-befecskendezés", kevésbé valószínűek, mivel a jól megírt tárolt eljárások emellett érvényesítik a bemeneti paramétereket, mielőtt átadnák a lekérdezést a DBMS-nek.
Tárolt eljárások végrehajtása
A tárolt eljárások általában az SQL nyelv vagy annak meghatározott DBMS-ben való megvalósítása használatával jönnek létre. Például ezekre a célokra a Microsoft SQL Server DBMS a Transact-SQL nyelvet, az Oracle PL/SQL, az InterBase és a Firebird PSQL, a PostgreSQL PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 - SQL / PL (angol), Informix nyelven - SPL. A MySQL eléggé követi az SQL:2003 szabványt, nyelve pedig hasonló az SQL/PL-hez.
Egyes DBMS-ekben lehetőség van bármilyen független futtatható fájlok létrehozására képes programozási nyelven írt tárolt eljárások használatára, például C++ vagy Delphi. A Microsoft SQL Server terminológiájában ezeket az 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 és a Firebird programban a DLL / SO-ból meghívott függvényekhez egy másik név van meghatározva - UDF (felhasználói függvény). Az MS SQL 2005-ben lehetővé vált a tárolt eljárások bármilyen .NET nyelven történő írása, a kiterjesztett tárolt eljárások a jövőben a tervek szerint megszűnnek. Az Oracle DBMS viszont lehetővé teszi a tárolt eljárások Java nyelven történő írását. Az IBM DB2-ben a tárolt eljárások és függvények hagyományos programozási nyelveken való írása a hagyományos, kezdettől fogva támogatott módszer, és az SQL procedurális kiterjesztés csak későn, az ANSI szabványba való bekerülése után került bele ebbe a DBMS-be. Az Informix támogatja a Java és C eljárásokat is.
Az Oracle DBMS-ben a tárolt eljárások úgynevezett csomagokká kombinálhatók. A csomag két részből áll - a specifikációból (angol nyelvű csomagspecifikáció), amely a tárolt eljárás definícióját adja meg, és a törzsből (angol csomagtörzs), ahol a megvalósítás található. Í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 rendszerben 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. 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á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
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 módosítására, de 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.
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.
Paraméterek
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, mind a bemenet, mind a kimenet.
A változó deklarálásának szintaxisa így néz ki:
DECLARE varname DATA-TYPE DEFAULT alapértelmezett érték;
Célkitűzés– megtanulják, hogyan lehet tárolt eljárásokat létrehozni és használni az adatbázis-kiszolgálón.
1. Az összes példa végigdolgozása, végrehajtásuk eredményeinek elemzése az SQL Server Management Studio segédprogramban. Annak ellenőrzése, hogy a létrehozott eljárások léteznek-e az aktuális adatbázisban.
2. Minden példa és feladat elvégzése a laboratóriumi munka során.
3. Egyéni feladatok elvégzése opciók szerint.
A tárolt eljárások programozásának elsajátításához egy példát használunk egy adatbázis ún DB_Books 1. számú laboratóriumi munkában jött létre. Példák és feladatok végrehajtása során ügyeljen az adatbázis, a táblák és a projekt egyéb objektumai közötti megfelelésre.
Tárolt eljárások egy vagy több SQL-utasításból vagy függvényből álló parancsok halmaza, amelyeket az adatbázisban lefordított formában tárolnak.
A rendszerben tárolt eljárások különféle adminisztratív műveletek végrehajtására szolgálnak. Szinte minden szerveradminisztrációs művelet az ő segítségükkel történik. Azt mondhatjuk, hogy a rendszerben tárolt eljárások egy olyan felület, amely rendszertáblákkal való munkát biztosít. A rendszerben tárolt eljárások sp_ előtaggal vannak ellátva, a rendszer adatbázisban tárolódnak, és bármely más adatbázis kontextusában meghívhatók.
Az egyedi tárolt eljárások bizonyos műveleteket hajtanak végre. A tárolt eljárások teljes adatbázis-objektumok. Ennek eredményeként minden tárolt eljárás egy adott adatbázisban található, ahol végrehajtják.
Az ideiglenesen tárolt eljárások csak rövid ideig léteznek, ezután a szerver automatikusan megsemmisíti azokat. Helyi és globális csoportokra oszthatók. A helyi ideiglenesen tárolt eljárások csak arról a kapcsolatról hívhatók meg, amelyen létrehozták őket. Egy ilyen eljárás létrehozásakor olyan nevet kell adni, amely egyetlen # karakterrel kezdődik. Mint minden ideiglenes objektum, az ilyen típusú tárolt eljárások is automatikusan törlődnek, amikor a felhasználó leválasztja, újraindítja vagy leállítja a szervert. Az ideiglenesen tárolt globális eljárások minden olyan kiszolgálón lévő kapcsolathoz elérhetők, amelyen ugyanaz az eljárás működik. 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.
A tárolt eljárás létrehozása a következő feladatok megoldását jelenti: hozzáférési jogok tervezése. Tárolt eljárás létrehozásakor ne feledje, hogy ugyanazokkal a hozzáférési jogokkal rendelkezik az adatbázis-objektumokhoz, mint az azt létrehozó felhasználó; tárolt eljárás paramétereinek meghatározása, a tárolt eljárásoknak lehetnek bemeneti és kimeneti paraméterei; tárolt eljáráskód fejlesztése. Az eljáráskód bármilyen SQL-parancssorozatot tartalmazhat, beleértve az egyéb tárolt eljárások hívását is.
Az MS SQL Server jelölésében új, vagy meglévő tárolt eljárás létrehozásának vagy módosításának szintaxisa a következő:
( LÉTREHOZÁS | MÓDOSÍTÁS ) PROC[ EDURE] eljárás_neve [ ;szám] [ ( @paraméter_neve adattípus ) [ VÁLTOZÓ ] [ = ALAPÉRTÉKELÉS ] [ KIMENET] ] [ ,... n] [ WITH (ÚJRACOMPILE | TITKOSÍTÁS | ÚJRACOMPILE, ENCRYPTION ) ] [ REPLIKÁLÁSHOZ] AS sql_statement [ ... n]Tekintsük ennek a parancsnak a paramétereit.
Az sp_, #, ## előtagok használatával a létrehozott eljárás rendszer- 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. Ezért a létrehozandó tárolt eljárás egy adott adatbázisba helyezéséhez futtassa a CREATE PROCEDURE parancsot az adott adatbázis környezetében. Ha ugyanabból az adatbázisból egy tárolt eljárás törzséből ér el objektumokat, akkor rövidített neveket használhat, vagyis az adatbázis nevének megadása nélkül. Ha más adatbázisokban található objektumokra kíván hivatkozni, az adatbázis nevének megadása kötelező.
A bemeneti és kimeneti adatok átadásához a generált tárolt eljárásban a paraméterneveknek @ karakterrel kell kezdődniük. Egy tárolt eljárásban több paramétert 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. Bármely SQL adattípus, beleértve a felhasználó által definiáltakat is, alkalmas a tárolt eljárási paraméterek adattípusának megadására. A CURSOR adattípus azonban csak tárolt eljárás kimeneti paramétereként használható, pl. az OUTPUT kulcsszóval.
Az OUTPUT kulcsszó jelenléte azt jelenti, hogy a megfelelő paraméter egy tárolt eljárás adatait adja vissza. Ez azonban nem jelenti azt, hogy a paraméter nem alkalmas értékek tárolt eljárásnak való átadására. Az OUTPUT kulcsszó megadása arra utasítja a szervert, hogy a tárolt eljárásból való kilépéskor a paraméter aktuális értékét rendelje hozzá ahhoz a helyi változóhoz, amely az eljárás hívásakor paraméterértékként volt megadva. 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 reguláris paraméterekhez engedélyezett kifejezések vagy állandók nem megengedettek. A VARYING kulcsszó a CURSOR típusú OUTPUT paraméterrel együtt használatos. Meghatározza, hogy a kimeneti paraméter az eredményhalmaz lesz.
A DEFAULT kulcsszó az az érték, amelyet a megfelelő paraméter alapértelmezés szerint felvesz. Így egy eljárás meghívásakor nem lehet kifejezetten megadni a megfelelő paraméter értékét.
Mivel a szerver gyorsítótárazza a lekérdezés végrehajtási tervet és a lefordított kódot, az eljárás legközelebbi meghívásakor a már előkészített értékek kerülnek felhasználásra. Bizonyos esetekben azonban továbbra is szükséges az eljáráskód újrafordítása. A RECOMPILE kulcsszó megadása arra utasítja a rendszert, hogy minden egyes meghívásakor hozzon létre egy végrehajtási tervet a tárolt eljáráshoz.
A FOR REPLICATION opcióra akkor van szükség, ha adatokat replikál, és a generált tárolt eljárást cikkként szerepelteti egy kiadványban. Az ENCRYPTION kulcsszó arra utasítja a szervert, hogy titkosítsa a tárolt eljáráskódot, amely védelmet nyújthat a tárolt eljárást megvalósító, védett algoritmusok használata ellen. Az AS kulcsszó magának a tárolt eljárástörzsnek az elejére kerül. Az eljárástörzsben szinte minden SQL parancs használható, tranzakciók deklarálhatók, zárolások állíthatók be, és egyéb tárolt eljárások is meghívhatók. A tárolt eljárásból a RETURN paranccsal lehet kilépni.
ELJÁRÁS HAJTÁSA ( eljárás_neve) [ ,... n]
A következő parancs a tárolt eljárás végrehajtására szolgál: [ [ EXEC [ UTE] eljárásnév [ ;szám] [ [ @paraméter_neve= ] ( érték | @változónév) [ OUTPUT ] | [ ALAPÉRTELMEZETT ] ] [ ,...n]
Ha nem a tárolt eljáráshívás az egyetlen parancs a kötegben, akkor az EXECUTE parancs megléte szükséges. Ezenkívül ez a parancs szükséges egy eljárás meghívásához egy másik eljárás vagy trigger törzséből.
Az OUTPUT kulcsszó használata eljáráshívásban csak azon paraméterek esetében megengedett, amelyeket az eljárás OUTPUT kulcsszóval létrehozásakor deklaráltak.
Ha egy eljárást a DEFAULT kulcsszóval hívunk meg egy paraméterhez, akkor a rendszer az alapértelmezett értéket fogja használni. Természetesen a megadott DEFAULT szó csak azoknál a paramétereknél megengedett, amelyekhez alapértelmezett érték van megadva.
Az EXECUTE parancs szintaxisából látható, hogy a paraméternevek elhagyhatók egy eljárás hívásakor. Ebben az esetben azonban a felhasználónak meg kell adnia a paraméterek értékeit abban a sorrendben, ahogyan azokat az eljárás létrehozásakor felsorolták. Nem rendelhet alapértelmezett értéket egy paraméterhez, ha egyszerűen kihagyja a felsorolásból. Ha ki akarunk hagyni olyan paramétereket, amelyeknek alapértelmezett értéke van, akkor elegendő a paraméternevek explicit megadása a tárolt eljárás hívásakor. Sőt, ily módon a paramétereket és azok értékeit bármilyen sorrendben felsorolhatja.
Vegye figyelembe, hogy egy eljárás meghívásakor vagy értékekkel rendelkező paraméternevek, vagy csak paraméternév nélküli értékek kerülnek megadásra. Kombinációjuk nem megengedett.
Lehetővé teszi, hogy a megadott feltételnek megfelelően bármely ponton kilépjen az eljárásból, valamint lehetővé teszi az eljárás végrehajtásának eredményének számként történő átvitelét is, amely alapján megítélheti az eljárás minőségét és helyességét. Példa paraméterek nélküli eljárás létrehozására:
ELJÁRÁS LÉTREHOZÁSA Count_Books AS SELECT COUNT (Kódkönyv) FROM Books GO1. Feladat.
EXEC Count_BooksEllenőrizze az eredményt.
Példa egy eljárás létrehozására bemeneti paraméterrel:
ELJÁRÁS LÉTREHOZÁSA Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Oldalak>= @Count_pages GO2. feladat. Hozza létre ezt az eljárást a DB_Books adatbázis Tárolt eljárások részében az SQL Server Management Studio segédprogrammal. Futtassa a paranccsal
EXEC Count_Books_Pages 100Ellenőrizze az eredményt.
Példa egy eljárás létrehozására bemeneti paraméterekkel:
ELJÁRÁS LÉTREHOZÁSA Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Oldalak>= @Count_pages AND Title_book LIKE @Title GO3. feladat. Hozza létre ezt az eljárást a DB_Books adatbázis Tárolt eljárások részében az SQL Server Management Studio segédprogrammal. Futtassa a paranccsal
EXEC Count_Books_Title 100 , "P%"Ellenőrizze az eredményt.
Példa egy eljárás létrehozására bemeneti és kimeneti paraméterekkel:
ELJÁRÁS LÉTREHOZÁSA Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Oldalak>= @Count_pages AND Title_book LIKE @Title GO4. feladat. Hozza létre ezt az eljárást a DB_Books adatbázis Tárolt eljárások részében az SQL Server Management Studio segédprogrammal. Futtassa parancskészlettel:
Sql> @q deklarálása mint int EXEC Count_Books_Itogo 100, "P%", @q kimenet kiválasztása @q
Ellenőrizze az eredményt.
Példa egy eljárás létrehozására bemeneti paraméterekkel és RETURN:
ELJÁRÁS LÉTREHOZÁSA checkname @param INT ASIF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Puskin A.S." VISSZA 1 EGYÉB VISSZA 25. feladat. Hozza létre ezt az eljárást a DB_Books adatbázis Tárolt eljárások részében az SQL Server Management Studio segédprogrammal. Futtassa a parancsokkal:
DECLARE @return_status INT EXEC @return_status = checkname 1 KIVÁLASZTÁS "Visszaküldési állapot" = @return_statusPélda paraméterek nélküli eljárás létrehozására a Vásárlások tábla kulcsmezőjének értékének megduplázására:
PROC LÉTREHOZÁSA update_proc AS UPDATE Vásárlások SET Code_purchase = Code_purchase* 26. feladat. Hozza létre ezt az eljárást a DB_Books adatbázis Tárolt eljárások részében az SQL Server Management Studio segédprogrammal. Futtassa a paranccsal
EXEC update_procPélda egy eljárásra egy bemeneti paraméterrel, amely az adott szerzőről szóló összes információt megkapja:
CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Szerzők WHERE név_szerző= @k7. feladat.
EXEC select_author "Pushkin A.S." vagy select_author @k= "Puskin A.S." vagy EXEC select_author @k= "Pushkin A.S."Példa egy olyan eljárás létrehozására bemeneti paraméterrel és alapértelmezett értékkel, amely a Vásárlások tábla kulcsmezőjének értékét meghatározott számú alkalommal (alapértelmezés szerint kétszeresére) növeli:
PROC LÉTREHOZÁSA update_proc @p INT = 2 AS UPDATE Vásárlások SET Code_purchase = Code_purchase * @pAz eljárás nem ad vissza semmilyen adatot.
8. feladat. Hozza létre ezt az eljárást a DB_Books adatbázis Tárolt eljárások részében az SQL Server Management Studio segédprogrammal. Futtassa a parancsokkal:
EXEC update_proc 4 vagy EXEC update_proc @p = 4 vagy EXEC update_proc -- az alapértelmezett érték kerül felhasználásra.Példa egy eljárás létrehozására bemeneti és kimeneti paraméterekkel. Hozzon létre egy eljárást az adott időszak alatt teljesített megrendelések számának meghatározásához:
CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Kód_vásárlás) FROM Vásárlások WHERE Date_order BETWEEN @d1 ÉS @d2 SET @c = ISNULL(@c, 0 )9. feladat. Hozza létre ezt az eljárást a DB_Books adatbázis Tárolt eljárások részében az SQL Server Management Studio segédprogrammal. Futtassa a parancsokkal:
DECLARE @c2 INT EXEC vásárlások száma '01- jún- 2006', '01- jul- 2006', @c2 OUTPUT SELECT @c2Laboratóriumi munka feladatváltozatai 4. sz
Általános rendelkezések. Az SQL Server Management Studio segédprogramban hozzon létre egy új oldalt a kód számára (Lekérdezés létrehozása gomb). Programozottan tegye aktívvá a létrehozott DB_Books adatbázist a Use utasítás használatával. Hozzon létre tárolt eljárásokat az Eljárások létrehozása utasításokkal, és határozza meg saját maga az eljárások nevét. Minden eljárás egy SQL-lekérdezést hajt végre, amelyet a második laborban hajtottak végre. Ezenkívül a lekérdezések SQL-kódját úgy kell módosítani, hogy azok át tudják adni a keresett mezők értékeit.
Például az eredeti feladat és lekérdezés a 2. laborban:
/*Válassza ki a beszállítói címtárból (Kézbesítési táblázat) azoknak a cégeknek a nevét, telefonszámait és TIN-jét (Fields Name_company, Phone and INN), amelyek cégneve (Field Name_company) OAO MIR.
SELECT Cégnév, Telefon, INN FROM Szállítások WHERE Név_vállalat = "JSC MIR"*/ – Ebben a munkában egy eljárás jön létre:
CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Név_vállalat, Telefon, INN FROM Szállítások WHERE Név_vállalat = @comp– Az eljárás elindításához használja a következő parancsot:
EXEC select_name_company "JSC MIR"Feladat lista
Hozzon létre egy új programot az SQL Server Management Studio-ban. Programozottan tegye aktívvá az 1. laborban létrehozott egyedi adatbázist a Use utasítással. Hozzon létre tárolt eljárásokat az Eljárások létrehozása utasításokkal, és határozza meg saját maga az eljárások nevét. Minden eljárás egy SQL-lekérdezést hajt végre, amelyeket az opciók külön feladatként jelenítenek meg.
1.opció
1. Jelenítse meg azon alkalmazottak listáját, akiknek legalább egy gyermekük van.
2. Jelenítse meg azoknak a gyermekeknek a listáját, akik ajándékot kaptak a megadott időszakban.
3. Jelenítse meg a kiskorú gyermekekkel rendelkező szülők listáját.
4. A megadott számnál nagyobb értékű ajándékok információinak megjelenítése dátum szerint rendezve.
2. lehetőség
1. Jelenítse meg a megadott típusú rögzítők listáját.
2. Jelenítse meg a javított eszközök számát és a javítások teljes költségét a megadott mestertől.
3. Jelenítse meg az eszköztulajdonosok listáját és a találatok számát, a találatok száma szerint rendezve, csökkenő sorrendben.
4. Információk megjelenítése azokról a mesterekről, akiknek a rangja nagyobb a megadott számnál, vagy akiknek a bérleti dátuma kisebb, mint a megadott dátum.
3. lehetőség
2. Jelenítse meg azoknak az értékesítési kódoknak a listáját, amelyeknél a virágot a megadott számnál nagyobb összegért értékesítették.
3. Jelenítse meg az eladás dátumát, az összeget, az eladót és a virágot a megadott értékesítési kódhoz.
4. Jelenítse meg a színek listáját, és rendezze a megadott számnál nagyobb magasságú vagy virágzó virágokat.
4. lehetőség
1. Jelenítse meg a gyógyszerek listáját a megadott használati javallattal.
2. Jelenítse meg azoknak a szállítási dátumoknak a listáját, amelyekre a megadott számnál több azonos nevű gyógyszert értékesítettek.
3. A megadott számnál nagyobb átvételi kóddal jelenítse meg a szállítás dátumát, az összeget, a szállító vezetőjének teljes nevét és a gyógyszer nevét.
5. lehetőség
2. Jelenítse meg a meghatározott ok miatt leállított berendezések listáját.
3. Jelenítse meg az átvétel dátumát, a berendezés nevét, a felelős teljes nevét és a leszerelés dátumát a meghatározott időszakban leszerelt berendezések esetében.
4. Jelenítse meg a megadott típusú vagy egy bizonyos értéknél nagyobb átvételi dátumú berendezések listáját
6. lehetőség
1. Jelenítse meg a megadott számnál nagyobb súlyú ételek listáját.
2. Jelenítse meg azon termékek listáját, amelyek neve tartalmazza a megadott szórészletet.
3. Jelenítse meg a termék térfogatát, az étel nevét, a termék nevét az étel kódjával a megadott kezdőértéktől egy bizonyos végértékig.
4. Jelenítse meg az étel elkészítési sorrendjét és annak az ételnek a nevét, amelynél a szénhidrát mennyisége nagyobb, mint egy bizonyos érték, vagy a megadott értéknél nagyobb a kalória száma.
7. lehetőség
1. Jelenítse meg a megadott beosztással rendelkező alkalmazottak listáját.
3. Jelenítse meg a regisztráció dátumát, a dokumentum típusát, az anyakönyvvezető teljes nevét és a szervezet nevét a megadott időszakban regisztrált dokumentumok esetén.
4. Jelenítse meg a regisztrált dokumentumok listáját egy adott típusú dokumentummal vagy a megadott értéknél nagyobb regisztrációs dátummal.
8. lehetőség
1. Jelenítse meg az alkalmazottak listáját a távozás meghatározott okával.
3. Jelenítse meg a regisztráció dátumát, az elbocsátás okát, a munkavállaló teljes nevét a megadott időszakban nyilvántartásba vett dokumentumok esetén.
9. lehetőség
1. Jelenítse meg azon alkalmazottak listáját, akik a megadott típusú szabadságot vették ki.
2. Jelenítse meg a dokumentumok listáját a regisztrációs dátummal a megadott időszakon belül.
3. Jelenítse meg a regisztráció dátumát, a szabadság típusát, a munkavállaló teljes nevét a megadott időszakban regisztrált dokumentumok esetén.
4. Jelenítse meg a regisztrált dokumentumok listáját a megadott tartományba eső dokumentumkóddal.
10. lehetőség
1. Jelenítse meg a megadott beosztással rendelkező alkalmazottak listáját.
2. Jelenítse meg a megadott szórészletet tartalmazó dokumentumok listáját.
3. Jelenítse meg a regisztráció dátumát, a dokumentum típusát, a feladó teljes nevét és a szervezet nevét a megadott időszakban regisztrált dokumentumok esetén.
4. Jelenítse meg a regisztrált bizonylatok listáját a megadott bizonylattípussal vagy egy bizonyos értéknél kisebb dokumentumkóddal.
11. lehetőség
1. Jelenítse meg a megadott pozícióhoz rendelt alkalmazottak listáját.
2. Jelenítse meg a dokumentumok listáját a regisztrációs dátummal a megadott időszakon belül.
3. Jelenítse meg a regisztráció dátumát, beosztását, a munkavállaló teljes nevét a megadott időszakban regisztrált dokumentumoknál.
4. Jelenítse meg a regisztrált dokumentumok listáját a megadott tartományba eső dokumentumkóddal.
12. lehetőség
3. Jelenítse meg azoknak a személyeknek a listáját, akik felszerelést béreltek, és kéréseik számát, a kérések száma szerint, csökkenő sorrendben.
13. lehetőség
1. Jelenítse meg a megadott típusú berendezések listáját. 2. Jelenítse meg azoknak a berendezéseknek a listáját, amelyeket egy adott alkalmazott leszerelt.
3. Jelenítse meg a leszerelt berendezések mennyiségét berendezés típusa szerint csoportosítva.
4. Információk megjelenítése azokról az alkalmazottakról, akiknek a felvételi dátuma egy bizonyos dátumnál nagyobb.
14. lehetőség
1. Jelenítse meg a megadott levéltípusú virágok listáját.
2. Jelenítse meg azon nyugtakódok listáját, amelyekért virágot adtak el egy bizonyos értéknél nagyobb összegért.
3. Egy adott szállítói kódhoz jelenítse meg az átvétel dátumát, az összeget, a szállító nevét és a színeket.
4. Jelenítse meg a virágok listáját és a bizonyos számnál nagyobb magasságú vagy virágzó virágok rendezését.
15. lehetőség
1. Jelenítse meg azon ügyfelek listáját, akik a megadott időszakban érkeztek a szobákba.
2. Jelenítse meg az egyes ügyfelek szobáiért fizetett teljes összeget.
3. Jelenítse meg az érkezés dátumát, szobatípust, a megadott időszakban regisztrált ügyfelek teljes nevét.
4. Jelenítse meg a regisztrált ügyfelek listáját egy bizonyos típusú szobákban.
16. lehetőség
1. Jelenítse meg a megadott típusú berendezések listáját.
2. Jelenítse meg azon felszerelések listáját, amelyeket egy adott ügyfél bérelt.
3. Jelenítse meg azoknak a személyeknek a listáját, akik felszerelést béreltek, és kéréseik számát, a kérések száma szerint, csökkenő sorrendben.
4. Információk megjelenítése az ügyfelekről címek szerint rendezve.
17. lehetőség
1. Jelenítse meg azoknak az értéktárgyaknak a listáját, amelyek vételára nagyobb, mint egy bizonyos érték, vagy a jótállási idő meghaladja a megadott számot.
2. Jelenítse meg az anyagi javak helyeinek listáját, amelyek nevében a megadott szó szerepel.
3. Jelenítse meg az értéktárgyak költségének összegét egy kóddal a megadott tartományba.
4. Jelenítse meg a pénzügyileg felelős személyek listáját a foglalkoztatás dátumával a megadott tartományban.
18. lehetőség
1. Jelenítse meg az adott mester által végzett javítások listáját.
2. Jelenítse meg a munkában szereplő munkaszakaszok listáját, amelyek címében a megadott szó szerepel.
3. Jelenítse meg a javítási munkaszakaszok költségének összegét a megadott tartományba eső kóddal rendelkező munkák esetén.
4. Jelenítse meg a mesterek listáját a megadott tartományba eső foglalkoztatási dátummal.
19. lehetőség
1. Jelenítse meg a gyógyszerek listáját egy adott indikációval.
2. Jelenítse meg azoknak a számlaszámoknak a listáját, amelyek egy bizonyos számú gyógyszernél többet adtak el.
3. A megadott számú bizonylathoz jelenítse meg az eladás dátumát, az összeget, a pénztáros teljes nevét és a gyógyszert.
4. Jelenítse meg a megadott számnál nagyobb kiszerelésű vagy egy bizonyos értéknél kisebb gyógyszerkóddal rendelkező gyógyszerek listáját és mértékegységeit.
20. lehetőség
1. Jelenítse meg a megadott beosztással rendelkező alkalmazottak listáját.
2. Jelenítse meg a megadott szórészletet tartalmazó dokumentumok listáját.
3. A megadott időszakban nyilvántartásba vett iratok esetében jelenítse meg az iktatás dátumát, az okirat típusát, a végrehajtó teljes nevét és a végrehajtás tényét.
4. Jelenítse meg a regisztrált bizonylatok listáját a megadott bizonylattípussal vagy egy bizonyos tartományba eső bizonylatkóddal.
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, 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 megköveteli a tárolt eljárások használatát.
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 adatkészletek 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, hogy a tárolt eljárások csupán meghatározott SQL-lekérdezések halmaza, 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 funkcionalitást egy kliens alkalmazásban, egy nagy szintű nyelv
Tehát pontosan mit tartalmaznak 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 szerver folyamaton belül fut le, é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).
A HP-ben rejlő hatékony tulajdonságok 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ételek 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 az SP-k sokkal kompaktabbak, mint a hagyományos nyelvek hasonló eljárásai.
Meg kell jegyezni, hogy a triggerekhez ugyanazt a programozási nyelvet használják, 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 haszná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és, vagyis a végrehajtás nyomon követése, a változók köztes értékeinek megtekintésével egyáltalán nem jöhet szóba. 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é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> =
(
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 bonyolultabbá váló 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, vesszővel elválasztva szerepelnek – jelezve a típusukat.
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.
A 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ási 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 lényegét, de egyelőre csak azt jegyezzük meg, hogy vissza kell adni a visszatérési paramétereket 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 ismeretes, 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 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ó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ővel, akkor hibaüzenetet fog visszaadni a váratlan, az értelmező szerint a parancs egy tárolt eljárás létrehozásához. Ha tárolt eljárásokat SQL szkriptfájlokkal hoz létre, speciális InterBase fejlesztői eszközök használata nélkül, akkor minden egyes CP létrehozási parancs előtt (ugyanez vonatkozik a triggerekre is) meg kell változtatnia a szkriptparancs elválasztóját egy másik karakterre, amely nem pontosvessző. küldje el a HP visszaállítását. Az SQL mondatelválasztó megváltoztatására szolgáló 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 elkészült, valahogy meg kell hívnunk, paramétereket kell átadnunk neki, és meg kell kapnunk a visszaadott eredményeket. Ezt nagyon könnyű megtenni – csak írjon egy SQL lekérdezést a következő formában:
KIVÁLASZT*
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 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 az ügyfélprogramokban, mind más SP-kben vagy triggerekben futnak le. 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 lekéré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 adnak vissza paramétereket, vagy csak egy készletet adnak vissza. A Returns részben felsorolt kimeneti paraméterek közül, 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 hogyan viselkedik a különböző típusú hívásoknál. 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 hívnak meg, egy végrehajtható eljárást pedig 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 adathalmaz a lekérdezés végrehajtása során. A szerver, miután megkapta a lekérdezést, meghatározza, hogy melyik 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ésnek megfelelő rekordokat, kiválasztja 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 egyes kiválasztott bejegyzésnél.
Mindezekre a kitérésre azért van szükség, hogy a kedves 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ás kedvéért készítsünk egy tárolt eljárást, ami pontosan úgy működik, mint a 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. Í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?” Ez a fejezet meglepetése, hogy tárolt eljárásokban is használhatunk változókat. XP-ben deklarálhatja a 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 beillesztenie 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 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 ezek a változók elküldésre kerülnek a p>eljárást a SUSPEND paranccsal meghívó kliensnek.
Í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ás úgy van megtervezve, hogy egy vagy több sort adjon vissza, amelyekhez a CP törzsében egy ciklust szerveznek, 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 típusú ciklus - 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ázatot kapunk, amelyben 1-től 99-ig terjedő egészek 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ó utasításhoz a 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.
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ébe írjuk.
ELJÁRÁS LÉTREHOZÁSA Növelje az árakat (
2. százalék DUPLA PONTOSSÁG növelése)
VISSZATÉRÉS (ID INTEGER, NÉV VARCHAR(SO), új_ár 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. Figyeljük meg, 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ó előtt kettőspont áll – a lekérdezésben használt mezőktől való megkülönböztetéshez.
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.
Kezdettő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 lekérésre 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éterekké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, és ezt megelőzően a kimeneti paramétereket normál változóként használhatjuk - 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, 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ással valósítjuk meg:
HA (új_ár > átlagár) AKKOR /*ha a jelenlegi ár nagyobb, mint az átlagár*/
KEZDŐDIK
/*majd állítson be egy új árat, amely egyenlő 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 az árat állítsa egyenlőnek az előző árral plusz a régi é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
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.
Az árnak a számított különbözetnek megfelelő módosításához az UPDATE utasítást használjuk, amely lehetővé teszi a meglévő rekordok - egy vagy több - módosítását. 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 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 CP-t hívták Az átvitel idejére az eljárás felfüggesztésre kerül, és amikor új rekord szükséges a CP, újra folytatódik – és ez addig folytatódik, amíg a FOR SELECT...DO nem iterálja végig a lekérdezésének ö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 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. Vagyis 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 segítségével. 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 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 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 egyenlő az átlagárral plusz egy fix százalékkal */
ú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 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 */
ú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 trigger nyelvi 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.
Legyen a következő problémameghatározásunk: van egy ilyen típusú hierarchikus szerkezetű árukatalógusunk:
Á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. Feladatunk, hogy a könyvtárból a "teljes név kibontásával" válogatást biztosítsunk minden 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 az intelligens kategória azonosítója, ID_PARENT_GOOD a kategória szülőfájának 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:
| ID_JÓ 1
|
ID_PARENT_JÓ 0
|
JÓ NÉV ÁRUK |
Most, hogy megvan a helyünk 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 "kibontott" formában jeleníti meg - például a "Háromkamrás" kategóriánál a termék teljes neve. 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_JÓ INTEGER)
MINT
CURR_CHILD_NAME VÁLTOZÓ DECLARE VARCHAR(80);
KEZDŐDIK
/*0Külső FOR SELECT ciklus szervezése 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_áru_neve
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 ez 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ív futtatása */
FOR
SELECT ID_CHILD_JÓ,teljes_árunév
FROM GETFULLNAME(:ID_CHILD_JÓ)
INTO:ID_CHILD_JÓ, :teljes_áru_neve
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:
Amint láthatja, egy rekurzív tárolt eljárás segítségével végignéztük a teljes kategóriafát, és kikövetkeztettü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 á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ő fejezet – "Az InterBase tárolt eljárásnyelv kiterjesztései" című fejezetben lesz szó, amely a kivételkezeléssel, a tárolt eljárások hibakezelésével és a tömbökkel végzett munkával foglalkozik.
Olyan helyzetet veszünk figyelembe, amikor a tárolt eljárások ronthatják a lekérdezés teljesítményét.
Az MS SQL Server 2000 rendszerben tárolt eljárások összeállításakor a tárolt eljárások az eljárás-gyorsítótárba kerülnek, ami elősegítheti a teljesítmény növelését végrehajtásuk során, mivel nincs szükség a tárolt eljáráskód elemzésére, optimalizálására és fordítására.
Másrészt vannak buktatók egy tárolt eljárás lefordított kódjának tárolásában, amelyek ellenkező hatást válthatnak ki.
A helyzet az, hogy egy tárolt eljárás összeállításakor az eljáráskódot alkotó utasítások végrehajtási terve összeállításra kerül, illetve ha a lefordított tárolt eljárás gyorsítótárazásra kerül, akkor annak végrehajtási terve is gyorsítótárazásra kerül, ezért a tárolt eljárás nem fog egy adott helyzetre és lekérdezési paraméterekre optimalizálható.
Egy kis kísérletet fog végezni ennek bemutatására.
1. LÉPÉS. Adatbázis létrehozása.
A kísérlethez hozzunk létre egy külön adatbázist.
ADATBÁZIS LÉTREHOZÁSA test_sp_perf
BE (NAME="teszt_adatok", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
BEJELENTKEZÉS (NAME="teszt_napló", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
2. LÉPÉS. Táblázat készítése.
TÁBLÁZAT LÉTREHOZÁSA sp_perf_test(oszlop1 int, oszlop2 karakter(5000))
3. LÉPÉS. A táblázat kitöltése tesztsorokkal. Az ismétlődő sorok szándékosan kerülnek hozzáadásra a táblázathoz. 10000 sor 1-től 10000-ig terjedő számokkal és 10000 sor 50000-es számokkal.
NYILATKOZAT @i int
SET @i=1
Miközben én<10000)
KEZDŐDIK
INSERT INTO sp_perf_test(oszlop1, oszlop2) VALUES(@i,"Tesztkarakterlánc #"+CAST(@i as char(8)))
INSERT INTO sp_perf_test(1. oszlop, 2. oszlop) VALUES(50000,"Tesztkarakterlánc #"+CAST(@i as char(8)))
SET @i= @i+1
VÉGE
SZÁM(*) KIVÁLASZTÁSA SP_perf_testből
MEGY
4. LÉPÉS. Hozzon létre egy nem fürtözött indexet. Mivel a végrehajtási terv az eljárással gyorsítótárban van, az indexet minden hívásban ugyanúgy használják.
NEM CLUSTERED INDEX LÉTREHOZÁSA CL_perf_test ON sp_perf_test(1. oszlop)
MEGY
5. LÉPÉS. Hozzon létre egy tárolt eljárást. Az eljárás egyszerűen végrehajtja a SELECT utasítást a feltétellel.
PROC proc1 LÉTREHOZÁSA (@paramint)
MINT
SELECT oszlop1, oszlop 2 FROM sp_perf_test WHERE [e-mail védett]
MEGY
6. LÉPÉS. Futtasson le egy tárolt eljárást. Sebezhető eljárás indításakor kifejezetten egy szelektív paramétert használnak. Az eljárás eredményeként 1 sort kapunk. A végrehajtási terv nem fürtözött index használatát jelzi, mint a lekérdezés szelektív, és ez a legjobb módja a karakterlánc kinyerésének. Az egyetlen sor lekérésére optimalizált eljárás az eljárás gyorsítótárában van tárolva.
EXEC proc1 1234
MEGY
7. LÉPÉS. Tárolt eljárás futtatása nem szelektív paraméterrel. Paraméterként az 50 000 értéket használjuk. Azok a sorok, amelyeknek az első oszlop értéke rendre 10 000, nem fürtözött indexet és könyvjelző keresési műveletet használnak, nem hatékonyak, de mivel a lefordított kód a végrehajtási tervvel a procedurálisban tárolódik. gyorsítótárat, azt használni fogják. A végrehajtási terv ezt mutatja, valamint azt, hogy a könyvjelzőkeresési művelet 9999 soron történt.
EXEC proc1 50000
MEGY
8. LÉPÉS. Olyan sorok kiválasztása, amelyek első mezője 50000. Külön lekérdezés végrehajtásakor a lekérdezés optimalizálva és az első oszlop meghatározott értékével kerül lefordításra. Ennek eredményeként a lekérdezésoptimalizáló megállapítja, hogy a mező sokszor megkettőződik, és a táblavizsgálati művelet mellett dönt, ami ebben az esetben sokkal hatékonyabb, mint egy nem fürtözött index használata.
SELECT oszlop1, oszlop2 FROM sp_perf_test WHERE oszlop1=50000
MEGY
Ebből arra következtethetünk, hogy a tárolt eljárások használata nem mindig javítja a lekérdezés teljesítményét. Nagyon óvatosnak kell lennie azokkal a tárolt eljárásokkal, amelyek változó sorszámú eredményekkel dolgoznak, és különböző végrehajtási terveket használnak.
A szkript segítségével megismételheti a kísérletet saját MS SQL szerverén.
