-SQL, a felhasználó által definiált függvények
Programozási nyelvek általában kétféle rutin:
felhasználói függvények (UDF).
Amint azt az előző cikkben, tárolt eljárások állnak számos utasítást és nulla vagy több bemeneti paraméterek, de általában nem térnek vissza semmilyen paramétert. Ellentétben tárolt eljárásokat, függvényeket mindig vissza egyetlen érték. Ebben a részben megnézzük létrehozása és használata Felhasználói függvények (felhasználó által megadott függvények - UDF).
Létrehozása és futtatása Felhasználói függvények
felhasználó által definiált függvények létre CREATE FUNCTION utasítást. amely a következő szintaxist:
Sémanév paraméter határozza meg a nevét, a séma, amelyet az a tulajdonos a generált UDF és function_name paraméter határozza meg a függvény nevét. @param paraméter egy bemeneti paraméter a függvény (hivatalos érvelés), amelynek adattípust típusa határozza meg paramétert. Paraméterek - értékek, amelyeket át a hívó felhasználó által definiált függvényt kell használni azt. alapértelmezett paraméter határozza meg az alapértelmezett érték a megfelelő paraméter a függvény. (Az alapértelmezett érték is lehet NULL).
Visszaadja a javaslat meghatározza az adat értékek által visszaadott UDF. Ez lehet szinte bármilyen szabványos adattípust támogatja az adatbázis-rendszer, beleértve az adatok típusát TABLE. Az egyetlen adat, amit nem lehet megadni időbélyeg adattípus.
UDF lehetnek skalár vagy táblázatos. Skalárfüggvények visszaút atomi (skalár) érték. Ez azt jelenti, hogy a bevallások záradéka skalár függvény jelzi az egyik standard adattípusok. A funkció egy táblázatot, ha a hozamok nyilatkozatot vissza egy sor sorokat.
Egy alternatív javaslat SCHEMABINDING UDF kötődnek az adatbázis-objektumok, amelyek ezt a funkciót kell alkalmazni. Ezt követően minden olyan kísérletet, hogy módosítsa egy adatbázis-objektum, amely elérhető a funkció megy hiba. (Lock funkciót adatbázis objektumok, amelyekre vonatkozik, csak akkor törlik, ha megváltoztatja a funkciót, majd SCHEMABINDING opció többé nem.)
Ahhoz, hogy hozzon létre egy függvényt a használat során SCHEMABINDING ajánlatot, adatbázis-objektumokat a funkciót meg kell felelniük a következő feltételeknek:
minden előadások és egyéb UDF, amely fel van húzva, a funkciókat, amelyeket meg kell kötve egy rendszerbe;
Az összes adatbázis-objektumok (táblák, nézetek és UDF) kell lennie ugyanabban az adatbázisban definiált funkciót.
blokk paraméter határozza meg a blokk BEGIN / END tartalmazó végrehajtását a funkciót. Az utóbbi állítás blokk kell RETURN utasítás egy érv. (Az érték argumentum a visszatérési értéke egy függvény.) Belül BEGIN / END blokk megengedhető csak a következő utasításokat:
egy hozzárendelés nyilatkozatot, mint például a SET;
utasítások szabályozására végrehajtási sebesség, mint a while és IF;
utasítások DECLARE egy helyi változó;
SELECT utasítást tartalmazó minta listákat oszlopok kifejezést, amelynek értékei vannak rendelve a változók, amelyek a helyi, hogy egy adott funkció;
INSERT, UPDATE és DELETE, amelyek megváltoztatják a változók típusa táblázat adatait, amelyek a helyi a funkciót.
Alapértelmezésben CREATE FUNCTION utasítás csak akkor használható, tagjai a rendszergazda rögzített kiszolgáló és rögzített adatbázis szerepe db_owner vagy db_ddladmin. De a tagjai ezeket a szerepeket lehet rendelni ezt a jogot, hogy a többi felhasználó a GRANT CREATE FUNCTION utasítást.
A következő példa bemutatja, hogyan lehet létrehozni ComputeCosts funkciók:
ComputeCosts függvény származó többletköltségeket a növekedés a projekt költségvetésében. Az egyetlen bemeneti paraméter @percent, határozza meg a százalékos növekedés költségvetések. A BEGIN / END blokk első kijelenti két lokális változók: @addCosts és @sumBudget, majd a SELECT változó @sumBudget rendelt összesen költségvetéseket. Ezután a függvény az összes többletköltséget, a használati utasítást VISSZA adja vissza.
Hívás felhasználói függvény
Felhasználó által definiált függvények lehet hivatkozni keresztül-SQL utasításokat, mint például a SELECT, INSERT, UPDATE vagy DELETE. Egy funkció végzik, megjelölve a nevét egy pár zárójelben a végén, amelyben egy vagy több érv lehet beállítani. Érvek - értékek vagy kifejezések, amelyeket át a bemeneti paraméterek után azonnal meghatároztuk a függvény neve. Ha hívja a funkciót, amikor a paraméterek, hogy az alapértelmezett értékek nincsenek meghatározva az összes ezeket a paramétereket kell megadnia érvek ugyanabban a sorrendben, amelyben a paraméterek meghatározása a CREATE FUNCTION utasítást.
A következő példában egy hívás ComputeCosts SELECT funkció utasításokat:
A SELECT utasítás a példa mutatja a nevek és számok valamennyi projekt, amelynek költségvetését alacsonyabb, mint a teljes többletköltségeket minden projekt egy adott érték százalékos növekedés.
A Transact-SQL függvény nevét kell megadni a nevét, hogy két részből áll: a séma neve és a függvény neve, így a példában használt előtag dbo sémát.
Táblázat értékű függvény
Mint korábban említettük, a függvény egy asztalra értékes, ha javaslatát adja vissza a beállított sorok. Attól függően, hogy az adott szervezet funkcióit, hogy visszatérjen a függvény értékét táblázat minősülnek süllyesztett (inline) és mnogoinstruktsionnye (multistatement). Ha a hozamok kikötés megadott TABLE kulcsszó nélkül kísérő oszlop listán, ez a funkció be van építve. SELECT beépített függvény visszaad egy eredményhalmaz formájában változó adattípusok TABLE.
Mnogoinstruktsionnaya asztal értékű függvény tartalmazza a nevet, amely azonosítja a belső változó adattípus TÁBLÁZAT. Ez az adat típus megadva kulcsszó táblázat. amely követi a változó nevét. Ebben a változó vannak behelyezve a kiválasztott sor, és ez szolgál a visszatérési érték a függvény.
Egy táblázat értékű függvények látható az alábbi példát:
EmployeesInProject funkció megjeleníti a nevét, a dolgozók egy adott projektben, amelyek száma határozza meg egy bemeneti paramétere @projectNumber. Mivel a függvény visszatérési értéke általában egy sor sorok, a hozamok javaslat meghatározása ennek a funkciónak szerepel a kulcsszó TABLE utasítás, jelezve, hogy a függvény egy asztal értéket. (Megjegyezzük, hogy a példa a BEGIN / END blokk kell csökkenteni, és a return tartalmaz SELECT).
Használata Employees_in_Project funkció van megadva az alábbi példában:
Táblázat értékű függvények és utasítások
PÁLYÁZAT relációs utasítás lehetővé teszi, hogy hívja a táblázat értékű függvény minden egyes sort a táblázatban kifejezést. Ez az utasítás határozza meg a FROM záradék a SELECT esetben ugyanolyan módon, mint a JOIN nyilatkozatot. PÁLYÁZAT utasítás lehet kombinálni egy asztal funkciót, hogy megkapjuk az eredmény hasonló az eredmény halmaz műveletek csatlakozott két asztal. Két formája van PÁLYÁZAT utasításokat:
Kereszttag APPLY visszatér azokat a sorokat a belső (bal) táblázat kifejezés, amely egybeesik a külső (jobb oldali) táblázatban expresszió. Így logikus, a használati CROSS PÁLYÁZAT ugyanúgy működik, mint az utasítás inner join.
Utasítás KÜLSŐ PÁLYÁZAT visszatér az összes sort a belső (balra) asztal kifejezést. (Azoknak a soroknak, amelyeknél nincs egyezés a külső tábla kifejezést is tartalmaz, NULL értékek az oszlopok a külső tábla kifejezést.) Logikusan, használati KÜLSŐ PÁLYÁZAT egyenértékű LEFT JOIN KÜLSŐ nyilatkozatot.
Alkalmazás vonatkozik utasításokat látható az alábbi példákban:
GetJob () függvény egy sor sorok Works_on asztalra. Az alábbi példában, ez az eredmény halmaz „csatlakozik” a tartalmát a javaslat PÁLYÁZAT Alkalmazott táblázat:
Az eredmény a két funkciót lesz a következő két táblázat (megjelenik a kivégzés után a második funkció):
Az első példában a lekérdezés eredményeként meghatározott táblafüggvényt GetJob () „kapcsolt” a tartalmát az asztalra beosztási CROSS APPLY utasításokat. GetJob () függvény szerepét játssza a megfelelő bemeneti és Alkalmazott táblázat - maradt. jobb bemeneti kifejezés kiszámítjuk a bal bemeneti, és kapott vonal minden sorban kombinált létrehozni a végeredményt.
A második hasonló kérés az első (de használja az útmutató KÜLSŐ ellenében), amely logikailag megfelel a működését a külső kapcsolat a két tábla.
Táblázat értékű paraméterek
Táblázat segítségével-érték azt az alábbi példa:
Ebben a példában, először az kerül meghatározásra tábla típusa departmentType. Ez azt jelenti, hogy a típus a típusú adatokat táblázat, miáltal beillesztését teszi lehetővé a vonalak. A InsertProc eljárás nyilatkozik egy változó típusú @Moscow departmentType adatokat. (Ajánlat READONLY azt jelzi, hogy a tartalom ezen táblázatban nem változtatható.) A következő csomagot a táblázatban változó adatokat kerülnek beillesztésre, akkor az eljárás végrehajtásra kerül. Végrehajtása során az eljárás beszúr egy sort a táblázat változó az ideiglenes tábla #moscowTable. Beszúrva tartalma ideiglenes tábla a következő:
Táblázat használatával értékű paraméterek a következő előnyökkel jár:
egyszerűsített programozási modell rutinok;
Ez csökkenti a hívások száma a szerver és megkapja a megfelelő válaszokat;
eredménytáblázatban tetszőleges számú sorral.
Szerkezetének megváltoztatásával felhasználó által megadott utasítások
-SQL nyelvet támogat ALTER FUNCTION utasítást. amely módosítja a szerkezet a felhasználó által megadott utasítások (UDF). Ez az utasítás általában használják távolítsa el a kötési funkció az áramkört. Minden paraméter ALTER FUNCTION utasítást ugyanaz a jelentése, mint az azonos paraméter CREATE FUNCTION utasítást.
Törléséhez UDF használják használati drop funkció. Törlés funkció csak akkor lehet a tulajdonos vagy tagja egy előre meghatározott szerepet db_owner vagy rendszergazda.
felhasználó által definiált függvények, illetve a CLR
Egy korábbi cikkben megnéztük, hogyan lehet létrehozni egy tárolt eljárást menedzselt kód, a CLR a nyelv a C #. Ez a megközelítés lehet használni felhasználói függvények (UDF), azzal a különbséggel, hogy az UDF menteni egy adatbázis-objektum, a nyilatkozat CREATE FUNCTION helyett CREATE ELJÁRÁS. Ezen túlmenően, a felhasználó által definiált függvények is használják más összefüggésben, mint a tárolt eljárások az UDF-et mindig ad vissza értéket.
A következő példa mutatja a forráskódot felhasználói függvények (UDF) hajtják végre a nyelv a C #:
A forráskód, a felhasználó által definiált függvények a példában az új összeg a projekt számítják növelésével a régi költségvetés egy bizonyos százalékot. Használhatja a CREATE KÖZGYŰLÉS nyilatkozatot, hogy hozzon létre egy CLR szerelvényt az adatbázisban, amint arra korábban. Ha tanult a példákat az előző cikkben, és kiegészítette összeszerelés CLRStoredProcedures az adatbázisban, akkor frissítheti az összeszerelés után újrafordítás egy új osztályt (CLRStoredProcedures a neve az én projekt C # osztályok, ahol Hozzáteszem meghatározásának tárolt eljárások és függvények, akkor a szerelvény lehet különbözőképpen hívják):
CREATE FUNCTION utasítás az alábbi példa mutatja ComputeBudget eljárás takarít, mint egy adatbázis objektum, amely alkalmas lehet a használati utasításban az adatok manipulálására.
Segítségével egy ilyen utasítás, SELECT utasítás azt az alábbi példa:
Felhasználó által definiált függvények lehet helyezni a különböző helyeken a SELECT utasításban. A fenti példákban, lehívjuk a WHERE, és azokon a SELECT kiválasztáslistán.