Domov › Nastavení › SQL uložená procedura s parametry. Uložené procedury

SQL uložená procedura s parametry. Uložené procedury

Prohlášení o postupu

VYTVOŘIT POSTUP [({IN|OUT|INOUT} [,…])]
[DYNAMICKÁ SADA VÝSLEDKŮ ]
ZAČÍT [ATOMOVÝ]

KONEC

Klíčová slova
. IN (Input) – vstupní parametr
. OUT (Output) – výstupní parametr
. INOUT – vstup a výstup a také pole (bez parametrů)
. DYNAMIC RESULT SET označuje, že procedura může otevřít zadaný počet kurzorů, které zůstanou otevřené po návratu procedury

Poznámky
Nedoporučuje se používat mnoho parametrů v uložených procedurách (především velká čísla a znakové řetězce) kvůli přetížení sítě a zásobníku. V praxi mají stávající dialekty Transact-SQL, PL/SQL a Informix značné rozdíly od standardu, a to jak v deklaraci a použití parametrů, v deklaraci proměnných, tak ve volání podprogramů. Společnost Microsoft doporučuje k odhadu velikosti mezipaměti uložené procedury použít následující přiblížení:
=(maximální počet souběžných uživatelů)*(velikost největšího exekučního plánu)*1,25. Určení velikosti prováděcího plánu ve stránkách lze provést pomocí příkazu: DBCC MEMUSAGE.

Volání procedury

V mnoha existujících DBMS se uložené procedury volají pomocí operátoru:

PROVEĎTE POSTUP [(][)]

Poznámka: Volání uložených procedur lze provádět z aplikace, jiné uložené procedury nebo interaktivně.

Příklad deklarace procedury

CREATE PROCEDURE Proc1 AS //deklarujte proceduru
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //deklarujte kurzor
OPEN Cur1 //otevření kurzoru
FETCH NEXT FROM Cur1 //přečtení dat z kurzoru
WHILE @@Fetch_Status=0
ZAČÍT
NAČÍST DALŠÍ Z Cur1
KONEC
CLOSE Cur1 //zavření kurzoru
DEALOCATE Cur1
EXECUTE Proc1 //spusťte proceduru

Polymorfismus
Dva podprogramy se stejným názvem lze vytvořit ve stejném schématu, pokud se parametry těchto dvou podprogramů od sebe dostatečně liší, aby je bylo možné rozlišit. Aby bylo možné rozlišit mezi dvěma rutinami se stejným názvem ve stejném schématu, každá dostane alternativní a jedinečný název (specifický název). Takový název může být explicitně specifikován při definování podprogramu. Při volání podprogramů s několika stejnými názvy se určení požadovaného podprogramu provádí v několika krocích:
. Na začátku jsou definovány všechny procedury se zadaným názvem, a pokud žádné nejsou, pak všechny funkce s daným názvem.
. Pro další analýzu jsou zachovány pouze ty podprogramy, pro které má uživatel oprávnění EXECUTE.
. Pro ně jsou vybrány ty, jejichž počet parametrů odpovídá počtu argumentů volání. Kontrolují se zadané datové typy parametrů a jejich pozice.
. Pokud zbývá více než jeden podprogram, vybere se ten, jehož kvalifikační název je kratší.
V praxi je v Oracle polymorfismus podporován pro funkce deklarované pouze v balíčku, DB@ - v různých schématech a v Sybase a MS SQL Serveru je zakázáno přetěžování.

Mazání a změna postupů
Chcete-li odstranit proceduru, použijte operátor:

Chcete-li změnit postup, použijte operátor:

ZMĚNIT POSTUP [([{IN|OUT|INOUT}])]
ZAČÍT [ATOMOVÝ]

KONEC

Oprávnění provádět procedury

GRANT PROVÁDĚT DÁL NA |VEŘEJNOST [S MOŽNOSTÍ GRANTU]

Systémové postupy
Mnoho DBMS (včetně SQL Server) má specifickou sadu vestavěných systémových uložených procedur, které můžete použít pro své vlastní účely.

Uložené procedury SQL jsou spustitelné programové moduly, které mohou být uloženy ve formě různých objektů. Jinými slovy, je to objekt, který obsahuje příkazy SQL. Tyto uložené procedury lze spustit v aplikačním klientovi pro dosažení dobrého výkonu. Navíc jsou takové objekty často volány z jiných skriptů nebo dokonce z nějaké jiné sekce.

Zavedení

Mnoho lidí se domnívá, že jsou podobné různým postupům (respektive kromě MS SQL). Možná je to pravda. Mají podobné parametry a mohou produkovat podobné hodnoty. Navíc se v některých případech dotýkají. Jsou například kombinovány s databázemi DDL a DML a také s uživatelskými funkcemi (kódové označení UDF).

Ve skutečnosti mají uložené procedury SQL širokou škálu výhod, které je odlišují od podobných procesů. Bezpečnost, flexibilita programování, produktivita – to vše přitahuje stále více uživatelů pracujících s databázemi. Vrchol popularity procedur nastal v letech 2005-2010, kdy byl vydán program od Microsoftu s názvem „SQL Server Management Studio“. S jeho pomocí se práce s databázemi stala mnohem jednodušší, praktičtější a pohodlnější. Rok od roku si tento získával mezi programátory oblibu. Dnes je to naprosto známý program, který je pro uživatele „komunikující“ s databázemi na úrovni Excelu.

Když je zavolána procedura, je okamžitě zpracována samotným serverem bez zbytečných procesů nebo zásahů uživatele. Poté můžete provádět jakékoli mazání, provádění nebo úpravy. Za to vše je zodpovědný operátor DDL, který jediný provádí ty nejsložitější akce ke zpracování objektů. Navíc se to vše děje velmi rychle a server se ve skutečnosti nenačte. Tato rychlost a výkon umožňuje velmi rychle přenášet velké množství informací od uživatele na server a naopak.

Pro implementaci této technologie pro práci s informacemi existuje několik programovacích jazyků. Patří mezi ně například PL/SQL od Oracle, PSQL v systémech InterBase a Firebird a také klasický Microsoft Transact-SQL. Všechny jsou navrženy pro vytváření a provádění uložených procedur, což umožňuje velkým databázovým procesorům používat jejich vlastní algoritmy. To je také nezbytné k tomu, aby ti, kdo takové informace spravují, mohli chránit všechny objekty před neoprávněným přístupem třetích stran, a tedy vytvářením, úpravou nebo mazáním určitých údajů.

Produktivita

Tyto databázové objekty lze programovat různými způsoby. To umožňuje uživatelům vybrat si typ použité metody, který je nejvhodnější, což šetří námahu a čas. Procedura se navíc zpracovává sama, čímž se vyhnete obrovskému času strávenému komunikací mezi serverem a uživatelem. Modul lze také kdykoli přeprogramovat a změnit v požadovaném směru. Zvláště stojí za zmínku rychlost, s jakou se spouští uložená procedura SQL: tento proces probíhá rychleji než jiné jemu podobné, což jej činí pohodlným a univerzálním.

Bezpečnost

Tento typ zpracování informací se od podobných procesů liší tím, že zaručuje zvýšenou bezpečnost. To je zajištěno tím, že přístup jiných uživatelů k procedurám může být zcela vyloučen. To umožní správci provádět s nimi operace nezávisle, bez obav ze zachycení informací nebo neoprávněného přístupu do databáze.

Přenos dat

Vztah mezi uloženou procedurou SQL a klientskou aplikací je použití parametrů a návratových hodnot. Ten nemusí data předat uložené proceduře, ale tyto informace (hlavně na žádost uživatele) zpracuje pro SQL. Poté, co uložená procedura dokončí svou práci, odešle datové pakety zpět (ale opět volitelně) aplikaci, která ji volala, pomocí různých metod, které lze použít buď k volání uložené procedury SQL, nebo k návratu, například:

Přenos dat pomocí parametru typu výstupu;

Předávání dat pomocí operátora návratu;

Předávání dat pomocí vybraného operátora.

Nyní pojďme zjistit, jak tento proces vypadá zevnitř.

1. Vytvořte uloženou proceduru EXEC v SQL

Proceduru můžete vytvořit v MS SQL (Management Studio). Po vytvoření bude procedura uvedena v programovatelném uzlu databáze, ve kterém je procedura vytvoření operátorem prováděna. K provedení používají uložené procedury SQL proces EXEC, který obsahuje název samotného objektu.

Když vytvoříte proceduru, nejprve se zobrazí její název a za ním jeden nebo více parametrů, které jsou k ní přiřazeny. Parametry mohou být volitelné. Po zapsání parametrů, tedy těla procedury, je třeba provést některé nezbytné operace.

Jde o to, že tělo v sobě může mít umístěny lokální proměnné a tyto proměnné jsou také lokální ve vztahu k procedurám. Jinými slovy, lze je zobrazit pouze v těle procedury Microsoft SQL Server. Uložené procedury jsou v tomto případě považovány za místní.

K vytvoření procedury tedy potřebujeme název procedury a alespoň jeden parametr jako tělo procedury. Všimněte si, že skvělou možností je v tomto případě vytvořit a spustit proceduru s názvem schématu v klasifikátoru.

Tělo procedury může být libovolného druhu, například vytvoření tabulky, vložení jednoho nebo více řádků tabulky, stanovení typu a povahy databáze a tak dále. Procesní orgán však omezuje provádění určitých operací v rámci něj. Některá z důležitých omezení jsou uvedena níže:

Tělo by nemělo vytvářet žádné jiné uložené procedury;

Tělo by nemělo vytvářet falešný dojem o předmětu;

Tělo by nemělo vytvářet žádné spouštěče.

2. Nastavení proměnné v těle procedury

Proměnné můžete nastavit jako lokální pro tělo procedury a pak budou umístěny výhradně v těle procedury. Je dobrým zvykem vytvořit proměnné na začátku těla uložené procedury. Ale můžete také nastavit proměnné kdekoli v těle daného objektu.

Někdy si můžete všimnout, že na jednom řádku je nastaveno několik proměnných a každý parametr proměnné je oddělen čárkou. Všimněte si také, že proměnná má předponu @. V těle procedury můžete nastavit proměnnou kamkoli chcete. Například proměnná @NAME1 může být deklarována blízko konce těla procedury. K přiřazení hodnoty deklarované proměnné se používá sada osobních údajů. Na rozdíl od situace, kdy je na stejném řádku deklarováno více než jedna proměnná, je v této situaci použita pouze jedna sada osobních údajů.

Uživatelé si často kladou otázku: "Jak přiřadit více hodnot v jednom příkazu v těle procedury?" Dobře. Je to zajímavá otázka, ale je mnohem snazší, než si myslíte. Odpověď: Pomocí dvojic jako "Select Var = value". Tyto dvojice můžete použít tak, že je oddělíte čárkou.

Řada příkladů ukazuje, jak lidé vytvářejí jednoduchou uloženou proceduru a spouštějí ji. Procedura však může přijímat takové parametry, že proces, který ji volá, bude mít hodnoty blízké (ale ne vždy). Pokud se shodují, pak v těle začnou odpovídající procesy. Pokud například vytvoříte proceduru, která přijme město a region od volajícího a vrátí údaje o tom, kolik autorů patří do odpovídajícího města a regionu. Procedura bude dotazovat tabulky autorů databáze, jako jsou Pubs, aby provedla tento počet autorů. Chcete-li získat tyto databáze, například Google stáhne skript SQL ze stránky SQL2005.

V předchozím příkladu postup používá dva parametry, které se v angličtině běžně nazývají @State a @City. Datový typ odpovídá typu definovanému v aplikaci. Tělo procedury má interní proměnné @TotalAuthors a tato proměnná se používá k zobrazení počtu autorů. Následuje sekce výběru dotazu, která vše spočítá. Nakonec je vypočtená hodnota vytištěna ve výstupním okně pomocí tiskového příkazu.

Jak spustit uloženou proceduru v SQL

Postup lze provést dvěma způsoby. První způsob předáním parametrů ukazuje, jak se za názvem procedury provádí seznam oddělený čárkami. Řekněme, že máme dvě hodnoty (jako v předchozím příkladu). Tyto hodnoty se shromažďují pomocí proměnných parametrů procedury @State a @City. Při tomto způsobu předávání parametrů je důležité pořadí. Tato metoda se nazývá ordinální předávání argumentů. U druhého způsobu jsou parametry již přímo přiřazeny a pořadí v tomto případě není důležité. Tato druhá metoda je známá jako předávání pojmenovaných argumentů.

Postup se může mírně lišit od typického postupu. Vše je stejné jako v předchozím příkladu, ale pouze zde jsou parametry posunuty. To znamená, že @City je uložen jako první a @State je uložen vedle výchozí hodnoty. Výchozí parametr je obvykle zvýrazněn samostatně. Uložené procedury SQL jsou předávány pouze jako parametry. V tomto případě za předpokladu, že parametr "UT" nahrazuje výchozí hodnotu "CA". Při druhém spuštění je pro parametr @City předána pouze jedna hodnota argumentu a parametr @State převezme výchozí hodnotu "CA". Zkušení programátoři radí, že všechny proměnné by měly být ve výchozím nastavení umístěny na konci seznamu parametrů. Jinak není provádění možné a pak musíte pracovat s předáváním pojmenovaných argumentů, což je delší a složitější.

4. SQL Server Stored Procedures: Return Methods

Existují tři důležité způsoby, jak odesílat data v volané uložené proceduře. Jsou uvedeny níže:

Vrátí hodnotu uložené procedury;

Výstup parametru uložené procedury;

Výběr jedné z uložených procedur.

4.1 Vracení hodnot z uložených procedur SQL

V této technice procedura přiřadí hodnotu lokální proměnné a vrátí ji. Procedura může také přímo vracet konstantní hodnotu. V následujícím příkladu jsme vytvořili proceduru, která vrací celkový počet autorů. Pokud porovnáte tento postup s předchozími, uvidíte, že hodnota tisku je obrácená.

Nyní se podívejme, jak provést proceduru a vytisknout její návratovou hodnotu. Provedení procedury vyžaduje nastavení proměnné a tisk, který se provádí po celém tomto procesu. Všimněte si, že místo tiskového příkazu můžete použít příkaz Select, například Select @RetValue a také OutputValue.

4.2 Výstup parametru uložené procedury SQL

Hodnotu odezvy lze použít k vrácení jedné proměnné, což jsme viděli v předchozím příkladu. Použití parametru Output umožňuje proceduře odeslat jednu nebo více hodnot proměnných volajícímu. Výstupní parametr je při vytváření procedury určen právě tímto klíčovým slovem „Output“. Pokud je parametr zadán jako výstupní parametr, pak mu musí objekt procedury přiřadit hodnotu. Uložené procedury SQL, jejichž příklady jsou uvedeny níže, se v tomto případě vrátí se souhrnnými informacemi.

V našem příkladu budou dva názvy výstupů: @TotalAuthors a @TotalNoContract. Jsou uvedeny v seznamu parametrů. Tyto proměnné přiřazují hodnoty v těle procedury. Když použijeme výstupní parametry, může volající vidět hodnotu nastavenou v těle procedury.

V předchozím scénáři jsou také deklarovány dvě proměnné, aby se zobrazily hodnoty, které MS SQL Server uložené procedury nastavily ve výstupním parametru. Poté se postup provede zadáním normální hodnoty parametru „CA“. Následující parametry jsou výstupní parametry, a proto jsou deklarované proměnné předávány v určeném pořadí. Všimněte si, že při předávání proměnných se zde nastavuje i klíčové slovo output. Jakmile je postup úspěšný, hodnoty vrácené výstupními parametry se zobrazí v okně zprávy.

4.3 Výběr jedné z uložených procedur SQL

Tato technika se používá k vrácení sady hodnot jako datové tabulky (RecordSet) do volající uložené procedury. V tomto příkladu uložená procedura SQL s parametry @AuthID dotazuje tabulku Autoři filtrováním záznamů vrácených pomocí tohoto parametru @AuthId. Příkaz Select rozhoduje o tom, co má být vráceno volajícímu uložené procedury. Po provedení uložené procedury je AuthId předán zpět. Tento postup zde vždy vrátí pouze jeden záznam nebo žádný. Ale uložená procedura nemá žádná omezení na vracení více než jednoho záznamu. Není neobvyklé vidět příklady, kdy jsou data vrácena pomocí vybraných parametrů zahrnujících vypočítané proměnné poskytnutím více součtů.

Na závěr

Uložená procedura je poměrně seriózní programový modul, který se vrací nebo předává a také nastavuje potřebné proměnné díky klientské aplikaci. Protože uložená procedura běží sama na serveru, lze se vyhnout výměně velkého množství dat mezi serverem a klientskou aplikací (pro některé výpočty). To vám umožní snížit zatížení SQL serverů, což samozřejmě přináší výhody jejich majitelům. Jedním z podtypů jsou uložené procedury T SQL, ale jejich studium je nezbytné pro ty, kteří vytvářejí působivé databáze. Existuje také velké, dokonce obrovské množství nuancí, které mohou být užitečné při studiu uložených procedur, ale to je potřeba spíše pro ty, kteří se plánují zapojit do programování, včetně profesionálně.

uložená procedura je možné pouze v případě, že se provádí v kontextu databáze, kde se postup nachází.

Typy uložených procedur

SQL Server má několik typů uložené procedury.

Systém uložené procedury určené k provádění různých administrativních akcí. Téměř všechny činnosti správy serveru jsou prováděny s jejich pomocí. Dá se říci, že systémové uložené procedury jsou rozhraním, které zajišťuje práci se systémovými tabulkami, což v konečném důsledku spočívá ve změně, přidávání, mazání a načítání dat ze systémových tabulek uživatelských i systémových databází. Systém uložené procedury mají předponu sp_, jsou uloženy v systémové databázi a lze je volat v kontextu jakékoli jiné databáze.
Zvyk uložené procedury provádět určité akce. Uložené procedury– plnohodnotný databázový objekt. V důsledku toho každý uložená procedura se nachází ve specifické databázi, kde se provádí.
Dočasný uložené procedury existují pouze chvíli, poté jsou serverem automaticky zničeny. Dělí se na lokální a globální. Místní dočasné uložené procedury lze volat pouze ze spojení, ve kterém byly vytvořeny. Při vytváření takové procedury jí musíte dát název, který začíná jedním znakem #. Jako všechny dočasné předměty, uložené procedury tohoto typu jsou automaticky odstraněny, když se uživatel odpojí nebo restartuje nebo zastaví server. Globální dočasné uložené procedury jsou dostupné pro všechna připojení ze serveru, který má stejný postup. Chcete-li jej definovat, stačí jej pojmenovat začínající znaky ## . Tyto procedury jsou odstraněny při restartování nebo zastavení serveru nebo po uzavření připojení v kontextu, ve kterém byly vytvořeny.

Vytvářejte, upravujte a mažte uložené procedury

Stvoření uložená procedura zahrnuje řešení následujících problémů:

určení typu vytvořeného uložená procedura: dočasné nebo vlastní. Navíc si můžete vytvořit svůj vlastní systém uložená procedura, dáte mu název s předponou sp_ a umístíte jej do systémové databáze. Tento postup bude dostupný v kontextu jakékoli lokální databáze serveru;
plánování přístupových práv. Při tvorbě uložená procedura je třeba vzít v úvahu, že bude mít stejná přístupová práva k databázovým objektům jako uživatel, který jej vytvořil;
definice parametry uložené procedury. Podobně jako postupy obsažené ve většině programovacích jazyků, uložené procedury může mít vstupní a výstupní parametry;
vývoj kódu uložená procedura. Kód procedury může obsahovat sekvenci libovolných příkazů SQL, včetně volání jiných uložené procedury.

Vytvoření nového a změna stávajícího uložená procedura provede se pomocí následujícího příkazu:

<определение_процедуры>::= (CREATE | ALTER ) název_procedury [;číslo] [(@název_parametru datový_typ ) [=výchozí] ][,...n] AS sql_operátor [...n]

Podívejme se na parametry tohoto příkazu.

Pomocí prefixů sp_ , # , ## lze vytvořenou proceduru definovat jako systémovou nebo dočasnou. Jak je patrné ze syntaxe příkazu, není dovoleno uvádět jméno vlastníka, který bude vlastnit vytvořenou proceduru, a také název databáze, kde se má nacházet. Tedy za účelem umístění vytvořeného uložená procedura v konkrétní databázi musíte zadat příkaz CREATE PROCEDURE v kontextu této databáze. Při otáčení od těla uložená procedura zkrácené názvy lze použít pro objekty stejné databáze, tj. bez zadání názvu databáze. Pokud potřebujete přistupovat k objektům umístěným v jiných databázích, je zadání názvu databáze povinné.

Číslo v názvu je identifikační číslo uložená procedura, který jej jednoznačně identifikuje ve skupině procedur. Pro usnadnění řízení jsou postupy logicky stejného typu uložené procedury lze seskupit tak, že jim dáte stejný název, ale různá identifikační čísla.

K přenosu vstupních a výstupních dat ve vytvořeném uložená procedura lze použít parametry, jejichž názvy, stejně jako názvy lokálních proměnných, musí začínat znakem @. V jednom uložená procedura Můžete zadat více parametrů oddělených čárkami. Tělo procedury by nemělo používat lokální proměnné, jejichž názvy se shodují s názvy parametrů této procedury.

Chcete-li určit datový typ, který odpovídá parametr uložené procedury, jsou vhodné jakékoli datové typy SQL, včetně uživatelsky definovaných. Datový typ CURSOR však lze použít pouze jako výstupní parametr uložená procedura, tj. zadáním klíčového slova OUTPUT.

Přítomnost klíčového slova OUTPUT znamená, že odpovídající parametr má vracet data uložená procedura. To však neznamená, že parametr není vhodný pro předávání hodnot uložená procedura. Zadáním klíčového slova OUTPUT dáváte serveru pokyn k ukončení uložená procedura přiřaďte aktuální hodnotu parametru lokální proměnné, která byla zadána při volání procedury jako hodnota parametru. Všimněte si, že při zadávání klíčového slova OUTPUT lze hodnotu odpovídajícího parametru při volání procedury nastavit pouze pomocí lokální proměnné. Jakékoli výrazy nebo konstanty, které jsou povoleny pro běžné parametry, nejsou povoleny.

Klíčové slovo VARYING se používá ve spojení s

Uložená procedura - databázový objekt, což je sada instrukcí SQL, která je jednou zkompilována a uložena na serveru. Uložené procedury jsou velmi podobné běžným procedurám v jazyce vyšší úrovně, mohou mít vstupní a výstupní parametry a lokální proměnné, mohou provádět numerické výpočty a operace se znakovými daty, jejichž výsledky lze přiřadit proměnným a parametrům. Uložené procedury mohou provádět standardní databázové operace (jak DDL, tak DML). Uložené procedury navíc umožňují smyčky a větvení, to znamená, že mohou používat instrukce k řízení procesu provádění.

Uložené procedury jsou podobné uživatelským funkcím (UDF). Hlavní rozdíl je v tom, že uživatelsky definované funkce lze použít jako jakýkoli jiný výraz v příkazu SQL, zatímco uložené procedury je nutné volat pomocí funkce CALL:

Postup VOLÁNÍ (…)

EXECUTE postup (…)

Uložené procedury mohou vracet více výsledků, tj. výsledky dotazu SELECT. Takové sady výsledků lze zpracovat pomocí kurzorů, jiných uložených procedur, které vracejí ukazatel sady výsledků, nebo aplikací. Uložené procedury mohou také obsahovat deklarované proměnné pro zpracování dat a kurzory, které umožňují procházet více řádky v tabulce. Standard SQL poskytuje IF, LOOP, REPEAT, CASE a mnoho dalších pro práci. Uložené procedury mohou přijímat proměnné, vracet výsledky nebo měnit proměnné a vracet je v závislosti na tom, kde je proměnná deklarována.

Implementace uložených procedur se u jednotlivých DBMS liší. Většina velkých dodavatelů databází je v té či oné formě podporuje. V závislosti na DBMS mohou být uložené procedury implementovány v různých programovacích jazycích, jako je SQL, Java, C nebo C++. Uložené procedury, které nejsou napsány v SQL, mohou nebo nemusí provádět dotazy SQL samy o sobě.

Pro

Sdílení logiky s jinými aplikacemi. Uložené procedury zapouzdřují funkčnost;

to poskytuje konektivitu pro přístup a správu dat napříč různými aplikacemi.

Izolace uživatelů z databázových tabulek. To vám umožní poskytnout přístup k uloženým procedurám, ale ne k samotným datům tabulky.

Poskytuje ochranný mechanismus. Stejně jako v předchozím bodě, pokud máte přístup k datům pouze prostřednictvím uložených procedur, nikdo jiný nemůže vymazat vaše data pomocí příkazu SQL DELETE.

Vylepšené provádění v důsledku sníženého síťového provozu. Pomocí uložených procedur lze kombinovat více dotazů.

Proti

Zvýšené zatížení databázového serveru kvůli skutečnosti, že většina práce se provádí na straně serveru a méně na straně klienta.

Budete se muset hodně učit. Budete se muset naučit syntaxi výrazů MySQL, abyste mohli psát své uložené procedury.

Migrace z jednoho DBMS na jiný (DB2, SQL Server atd.) může vést k problémům.

Účel a výhody uložených procedur

Uložené procedury zlepšují výkon, rozšiřují možnosti programování a podporují funkce zabezpečení dat.

Namísto ukládání často používaného dotazu mohou klienti odkazovat na odpovídající uloženou proceduru. Při volání uložené procedury je její obsah serverem okamžitě zpracován.

Uložené procedury umožňují kromě skutečného provádění dotazu také provádět výpočty a manipulovat s daty – měnit, mazat, spouštět příkazy DDL (ne ve všech DBMS!) a volat další uložené procedury a provádět komplexní transakční logiku. Jediný příkaz umožňuje volat složitý skript, který je obsažen v uložené proceduře, a vyhnout se tak odesílání stovek příkazů po síti a zejména nutnosti přenášet velké množství dat z klienta na server.

Ve většině DBMS je uložená procedura při prvním spuštění zkompilována (analyzována a je vygenerován plán přístupu k datům). V budoucnu je jeho zpracování rychlejší. Oracle DBMS interpretuje uložený procedurální kód uložený v datovém slovníku. Počínaje Oracle 10g je podporována tzv. nativní kompilace uloženého procedurálního kódu v C a následně do strojového kódu cílového stroje, načež se při volání uložené procedury přímo provede její zkompilovaný objektový kód.

Možnosti programování

Vytvořenou uloženou proceduru lze volat kdykoli, což poskytuje modularitu a podporuje opětovné použití kódu. To usnadňuje údržbu databáze, protože se izoluje od měnících se obchodních pravidel. Uloženou proceduru můžete kdykoli upravit v souladu s novými pravidly. Poté budou všechny aplikace, které jej využívají, automaticky bez přímé úpravy v souladu s novými obchodními pravidly.

Bezpečnost

Použití uložených procedur umožňuje omezit nebo zcela eliminovat přímý uživatelský přístup k databázovým tabulkám a ponechává uživatelům pouze oprávnění ke spouštění uložených procedur, které poskytují nepřímý a přísně regulovaný přístup k datům. Některé DBMS navíc podporují textové šifrování (zabalení) uložených procedur.

Tyto bezpečnostní funkce umožňují izolovat strukturu databáze od uživatele a zajistit integritu a spolehlivost databáze.

Pravděpodobnost akcí, jako je SQL injection, je snížena, protože dobře napsané uložené procedury navíc kontrolují vstupní parametry před předáním dotazu do DBMS.

Implementace uložených procedur

Uložené procedury jsou obvykle vytvářeny pomocí jazyka SQL nebo jeho specifické implementace ve vybraném DBMS. Například pro tyto účely je v DBMS Microsoft SQL Server jazyk Transact-SQL, v Oracle - PL/SQL, v InterBase a Firebird - PSQL, v PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, v IBM DB2 - SQL/PL (anglicky), v Informixu - SPL. MySQL se poměrně úzce řídí standardem SQL:2003, jeho jazyk je podobný SQL/PL.

Některé DBMS umožňují použití uložených procedur napsaných v jakémkoli programovacím jazyce, který může vytvářet nezávislé spustitelné soubory, například C++ nebo Delphi. V terminologii Microsoft SQL Server se takové procedury nazývají rozšířené uložené procedury a jsou jednoduše funkcemi obsaženými v Win32 DLL. A například v Interbase a Firebirdu mají funkce volané z DLL/SO jiný název – UDF (User Defined Function). MS SQL 2005 zavedl možnost psát uložené procedury v libovolném jazyce .NET a v budoucnu se plánuje opuštění rozšířených uložených procedur. Oracle DBMS zase umožňuje psát uložené procedury v Javě. V IBM DB2 je psaní uložených procedur a funkcí v konvenčních programovacích jazycích tradičním způsobem podporovaným od samého počátku a procedurální rozšíření SQL bylo do tohoto DBMS přidáno až v poměrně pozdních verzích, po jeho zařazení do standardu ANSI. Informix také podporuje procedury v Javě a C.

V Oracle DBMS lze uložené procedury kombinovat do tzv. balíčků. Balíček se skládá ze dvou částí - specifikace (anglicky package Specification), která specifikuje definici uložené procedury, a těla (anglicky package body), kde se nachází její implementace. Oracle tak umožňuje oddělit rozhraní programového kódu od jeho implementace.

V IBM DB2 DBMS lze uložené procedury kombinovat do modulů.

Syntax

VYTVOŘIT POSTUP `p2`()

SQL DEFINER ZABEZPEČENÍ

KOMENTÁŘ "Postup"

SELECT "Ahoj světe!";

První část kódu vytvoří uloženou proceduru. Další obsahuje volitelné parametry. Poté přichází na řadu název a nakonec samotné tělo procedury.

4 vlastnosti uložené procedury:

Jazyk: Pro účely přenositelnosti je výchozí SQL.

Deterministický: pokud procedura vždy vrací stejný výsledek a bere stejné vstupní parametry. Toto je pro proces replikace a registrace. Výchozí hodnota je NOT DETERMINISTIC.

Zabezpečení SQL: uživatelská práva se kontrolují během hovoru. INVOKER je uživatel, který volá uloženou proceduru. DEFINER je „tvůrcem“ procedury. Výchozí hodnota je DEFINER.

Komentář: Pro účely dokumentace je výchozí hodnota ""

Volání uložené procedury

CALL uložený_název_procedury (param1, param2, ....)

CALL procedure1(10 , "parametr řetězce" , @parameter_var);

Úprava uložené procedury

MySQL má příkaz ALTER PROCEDURE pro změnu procedur, ale je vhodný pouze pro změnu určitých charakteristik. Pokud potřebujete změnit parametry nebo tělo procedury, měli byste ji odstranit a znovu vytvořit.

Odstraněníuloženypostupy

POSTUP VYPNĚNÍ, POKUD EXISTUJE p2;

Toto je jednoduchý příkaz. Příkaz IF EXISTS zachytí chybu, pokud takový postup neexistuje.

Možnosti

CREATE PROCEDURE proc1(): prázdný seznam parametrů

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): jeden vstupní parametr. Slovo IN je nepovinné, protože výchozí parametry jsou IN (in).

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): vrácen jeden parametr.

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): jeden parametr, vstupní i návratový.

Syntaxe deklarace proměnné vypadá takto:

DECLARE název_varianta DATA-TYPE DEFAULT defaultvalue;

Je definován koncept uložených procedur. Poskytuje příklady vytváření, úprav a používání uložených procedur s parametry. Je uvedena definice vstupních a výstupních parametrů.

Jsou uvedeny příklady vytváření a volání uložených procedur.

Uložené procedury Koncept uložené procedury uložená procedura jsou skupiny propojených SQL příkazů, jejichž použití usnadňuje a zpružňuje práci programátora, od uložené procedury je často mnohem jednodušší než sekvence jednotlivých příkazů SQL. Uložené procedury jsou sada příkazů skládající se z jednoho nebo více příkazů nebo funkcí SQL a uložených v kompilované podobě v databázi. Provedení v databázi

Místo jednotlivých příkazů SQL má uživatel následující výhody:
potřebné operátory jsou již obsaženy v databázi; všichni prošli jevištěm rozebrat provedení uložené procedury SQL Server pro něj vygeneruje plán provádění, provede jeho optimalizaci a kompilaci;
uložené procedury podpora modulární programování, protože umožňují rozdělit velké úkoly na samostatné, menší a snáze ovladatelné části;
uložené procedury může způsobit jiným uložené procedury a funkce;
uložené procedury lze volat z jiných typů aplikačních programů;
zpravidla, uložené procedury provádět rychleji než sekvence jednotlivých příkazů;
uložené procedury snadnější použití: mohou se skládat z desítek nebo stovek příkazů, ale pro jejich spuštění stačí zadat název požadovaného uložená procedura. To vám umožní snížit velikost požadavku odeslaného z klienta na server a tím i zatížení sítě.

Ukládání procedur na stejném místě, kde se provádějí, snižuje množství dat přenášených po síti a zlepšuje celkový výkon systému. Aplikace uložené procedury zjednodušuje údržbu softwarových systémů a provádění změn v nich. Obvykle jsou všechna omezení integrity ve formě pravidel a algoritmů zpracování dat implementována na databázovém serveru a jsou k dispozici koncové aplikaci jako sada uložené procedury, které představují rozhraní pro zpracování dat. Pro zajištění integrity dat a také z bezpečnostních důvodů aplikace obvykle nezískává přímý přístup k datům - veškerá práce s ní probíhá voláním určitých uložené procedury.

Tento přístup velmi usnadňuje úpravu algoritmů zpracování dat, které jsou okamžitě dostupné všem uživatelům sítě, a poskytuje možnost rozšířit systém bez provádění změn v samotné aplikaci: stačí změnit uložená procedura na databázovém serveru. Vývojář nemusí aplikaci znovu kompilovat, vytvářet její kopie ani instruovat uživatele, aby s novou verzí pracovali. Uživatelé si možná ani neuvědomují, že v systému byly provedeny změny.

Uložené procedury existují nezávisle na tabulkách nebo jiných databázových objektech. Jsou volány klientským programem, jiným uložená procedura nebo spoušť. Vývojář může spravovat přístupová práva k uložená procedura, povolující nebo zakazující její provedení. Změňte kód uložená procedura povoleno pouze jeho vlastníkem nebo členem pevné databázové role. V případě potřeby můžete převést jeho vlastnictví z jednoho uživatele na druhého.

Uložené procedury v prostředí MS SQL Server

Při práci se serverem SQL mohou uživatelé vytvářet vlastní procedury, které implementují určité akce. Uložené procedury jsou plnohodnotnými databázovými objekty, a proto je každý z nich uložen v konkrétní databázi. Přímé volání uložená procedura je možné pouze v případě, že se provádí v kontextu databáze, kde se postup nachází.

Typy uložených procedur

SQL Server má několik typů uložené procedury.

Systém uložené procedury určené k provádění různých administrativních akcí. Téměř všechny činnosti správy serveru jsou prováděny s jejich pomocí. Dá se říci, že systémové uložené procedury jsou rozhraním, které zajišťuje práci se systémovými tabulkami, což v konečném důsledku spočívá ve změně, přidávání, mazání a načítání dat ze systémových tabulek uživatelských i systémových databází. Systém uložené procedury mají předponu sp_, jsou uloženy v systémové databázi a lze je volat v kontextu jakékoli jiné databáze.
Zvyk uložené procedury provádět určité akce. Uložené procedury– plnohodnotný databázový objekt. V důsledku toho každý uložená procedura se nachází ve specifické databázi, kde se provádí.
Dočasný uložené procedury existují pouze chvíli, poté jsou serverem automaticky zničeny. Dělí se na lokální a globální. Místní dočasné uložené procedury lze volat pouze ze spojení, ve kterém byly vytvořeny. Při vytváření takové procedury jí musíte dát název, který začíná jedním znakem #. Jako všechny dočasné předměty, uložené procedury tohoto typu jsou automaticky odstraněny, když se uživatel odpojí nebo restartuje nebo zastaví server. Globální dočasné uložené procedury jsou dostupné pro všechna připojení ze serveru, který má stejný postup. Chcete-li jej definovat, stačí jej pojmenovat začínající znaky ## . Tyto procedury jsou odstraněny při restartování nebo zastavení serveru nebo po uzavření připojení v kontextu, ve kterém byly vytvořeny.

Vytvářejte, upravujte a mažte uložené procedury

Stvoření uložená procedura zahrnuje řešení následujících problémů:

určení typu vytvořeného uložená procedura: dočasné nebo vlastní. Navíc si můžete vytvořit svůj vlastní systém uložená procedura, dáte mu název s předponou sp_ a umístíte jej do systémové databáze. Tento postup bude dostupný v kontextu jakékoli lokální databáze serveru;
plánování přístupových práv. Při tvorbě uložená procedura je třeba vzít v úvahu, že bude mít stejná přístupová práva k databázovým objektům jako uživatel, který jej vytvořil;
definice parametry uložené procedury. Podobně jako postupy obsažené ve většině programovacích jazyků, uložené procedury může mít vstupní a výstupní parametry;
vývoj kódu uložená procedura. Kód procedury může obsahovat sekvenci libovolných příkazů SQL, včetně volání jiných uložené procedury.

Vytvoření nového a změna stávajícího uložená procedura provede se pomocí následujícího příkazu:

<определение_процедуры>::= (CREATE | ALTER ) PROC název_procedury [;číslo] [(@název_parametru datový_typ ) [=výchozí] ][,...n] AS sql_operátor [...n]

Podívejme se na parametry tohoto příkazu.

Klíčové slovo VARYING se používá ve spojení s parametrem OUTPUT, který je typu CURSOR. To určuje výstupní parametr bude sada výsledků.

Klíčové slovo DEFAULT představuje odpovídající hodnotu výchozí parametr. Při volání procedury tedy nemusíte explicitně specifikovat hodnotu odpovídajícího parametru.

Vzhledem k tomu, že server ukládá do mezipaměti plán provádění dotazů a zkompilovaný kód, při příštím volání procedury se použijí hotové hodnoty. V některých případech je však stále nutné znovu zkompilovat kód procedury. Zadáním klíčového slova PŘEKOMPILOVAT dává systému pokyn k vytvoření plánu provádění uložená procedura pokaždé, když zavolá.

Parametr FOR REPLICATION je vyžadován při replikaci dat a povolení vytvořeného uložená procedura jako článek k publikaci.

Klíčové slovo ENCRYPTION dává serveru pokyn k zašifrování kódu uložená procedura, který může poskytnout ochranu proti použití proprietárních algoritmů, které implementují práci uložená procedura.

Klíčové slovo AS je umístěno na začátku samotného těla uložená procedura, tj. sada SQL příkazů, s jejichž pomocí bude realizována ta či ona akce. V těle procedury lze použít téměř všechny SQL příkazy, deklarovat transakce, nastavovat zámky a volat další. uložené procedury. Odejít z uložená procedura lze provést pomocí příkazu RETURN.

Odebrání uložené procedury provádí příkazem:

DROP PROCEDURE (název_procedury) [,...n]

Provedení uložené procedury

Pro provést uloženou proceduru Použitý příkaz je:

[[ EXEC [ UTE] název_procedury [;číslo] [[@název_parametru=](hodnota | @název_proměnné) |][,...n]

Pokud hovor uložená procedura není jediným příkazem v balíčku, je vyžadována přítomnost příkazu EXECUTE. Kromě toho je tento příkaz vyžadován k volání procedury z těla jiné procedury nebo spouštěče.

Použití klíčového slova OUTPUT při volání procedury je povoleno pouze pro parametry, které byly deklarovány kdy vytvoření postupu s klíčovým slovem OUTPUT.

Když je pro parametr při volání procedury zadáno klíčové slovo DEFAULT, bude použito výchozí hodnota. Zadané slovo DEFAULT je přirozeně povoleno pouze pro ty parametry, pro které je definováno výchozí hodnota.

Syntaxe příkazu EXECUTE ukazuje, že názvy parametrů lze při volání procedury vynechat. V tomto případě však musí uživatel zadat hodnoty parametrů ve stejném pořadí, v jakém byly uvedeny vytvoření postupu. Přiřadit k parametru výchozí hodnota, nemůžete ho při výpisu jen tak přeskočit. Pokud chcete vynechat parametry, pro které je definován výchozí hodnota, stačí při volání výslovně uvést názvy parametrů uložená procedura. Navíc tímto způsobem můžete vypsat parametry a jejich hodnoty v libovolném pořadí.

Všimněte si, že při volání procedury jsou zadány buď názvy parametrů s hodnotami, nebo pouze hodnoty bez názvu parametru. Jejich kombinování není povoleno.

Příklad 12.1. Postup bez parametrů. Vypracujte postup pro získání jmen a nákladů na zboží zakoupené Ivanovem.

CREATE PROC my_proc1 AS SELECT Product.Name, Product.Price*Transaction.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.WHEREomer Zákazník .Last name='Ivanov' Příklad 12.1.