SQL uložené procedury: Vytvoření a použití

Ukládané procedury SQL jsou spustitelný programový modul, který lze uložit do souboru a databáze ve formě různých objektů. Jinými slovy je to objekt, který obsahuje příkazy SQL. Tyto uložené procedury lze provést v klientovi aplikace, aby se dosáhlo dobrého výkonu. Navíc takové objekty jsou často volány z jiných scénářů nebo dokonce z nějaké jiné části.

Úvod

Mnozí věří, že jsou podobné různým postupům jazyky programování na vysoké úrovni (s výjimkou MS SQL). Možná je to opravdu tak. Mají podobné parametry, mohou vytvářet podobné hodnoty. Navíc jsou v některých případech v kontaktu. Jsou například kombinovány s databázemi DDL a DML, stejně jako s uživatelskými funkcemi (kódové jméno - UDF).

Ve skutečnosti mají uložené procedury SQL širokou škálu výhod, které je odlišují od podobných procesů. Zabezpečení, variabilita programování, produktivita - to vše přitahuje uživatele, kteří pracují s databázemi, stále více a více. K vrcholu popularity postupů došlo v letech 2005-2010, kdy byl vydán program společnosti Microsoft nazvaný SQL Server Management Studio. S jeho pomocí se práce s databázemi stává mnohem jednodušší, praktičtější a pohodlnější. Rok od roku takový způsobu přenosu informací získala popularitu mezi programátory. Dnes MS SQL Server je absolutně známý program, který pro uživatele, kteří "komunikují" s databázemi, stál na stejné úrovni jako "Excel".

Při volání procedury je proces okamžitě zpracován samotným serverem bez zbytečných procesů a zásahu uživatele. Poté můžete provádět jakékoli akce s informacemi: odstranění, provedení, změna. Za všechno to odpovídá provozovatel DDL, který sám provádí nejsložitější zpracování objektů. A to vše se děje velmi rychle a server není skutečně nabitý. Tato rychlost a výkon vám umožní velmi rychle přenést velké množství informací od uživatele na server a naopak.

Pro implementaci této technologie s informacemi existuje několik programovacích jazyků. Patří sem například PL / SQL z systémy správy databází Oracle, PSQL v systémech InterBase a Firebird, stejně jako klasický "Microsoft" Transact-SQL. Všechny jsou navrženy tak, aby vytvářely a provádějí uložené procedury, které nám umožňují používat vlastní algoritmy v rozsáhlých databázových obsluhovačů. Je nezbytné, a aby zajistily, že ti, kteří vykonávají správu těchto informací lze chránit všechny objekty před neoprávněnými třetími stranami, a tudíž vytváření, úpravě nebo zrušení určitých údajů.

Produktivita

Tyto databázové objekty lze naprogramovat různými způsoby. To uživatelům umožňuje zvolit typ použité metody, která bude nejvhodnější, což šetří čas a úsilí. Dále je zpracován proces, který zabraňuje obrovskému času strávenému výměnou mezi serverem a uživatelem. Modul lze také kdykoli přeprogramovat a kdykoli změnit na požadovaný směr. Zvláště stojí za zmínku rychlost, s níž začíná procedura uložená v SQL: tento proces je rychlejší než jiné, podobně jako to, což je výhodné a všestranné.

Bezpečnost

Tento typ zpracování informací se liší od podobných procesů tím, že zaručuje vyšší bezpečnost. To je dáno skutečností, že přístup ostatních uživatelů k postupům lze zcela a zcela vyloučit. To umožní správci provádět operace s nimi nezávisle, bez obav z odposlechu 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 nepotřebuje přenášet data do uložené procedury, nicméně tyto informace (především na žádost uživatele) a zpracovány pro SQL. Po dokončení uložené procedury odešle datové pakety zpět (ale znovu, pokud je to požadováno) do aplikace, která ji nazvala, pomocí různých metod, které lze použít jako volání SQL uložené procedury, a vrátí se například:

- přenos dat pomocí výstupního parametru;

- přenos dat pomocí příkazu return;

- přenos dat pomocí operátora výběru.

Nyní uvidíme, jak tento proces vypadá zevnitř.

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

V MS SQL (Managment Studio) můžete vytvořit proceduru. Po vytvoření procedury bude tento program přenesen do uzlu programovatelné databáze, ve kterém je operace vytváření prováděna operátorem. Chcete-li provést procedury SQL uložené, použijte proces EXEC, který obsahuje název samotného objektu.

Při vytváření postupu se nejprve objeví jeho název, po němž se vytvoří jeden nebo více parametrů. Parametry mohou být volitelné. Po zapsání parametru (parametrů), tj. Souboru postupu, je třeba provést některé nezbytné operace.

Záležitost spočívá v tom, že tělo může mít v něm lokální proměnné a tyto proměnné jsou lokální i ve vztahu k procedurám. Jinými slovy, mohou být zobrazeny pouze v těle procedury Microsoft SQL Server. Uložené procedury se pak považují za lokální.

Abychom proto vytvořili proceduru, potřebujeme název postupu a alespoň jeden parametr jako tělo procedury. Všimněte si, že v tomto případě je skvělou volbou vytvořit a provést proceduru s názvem schématu v klasifikátoru.

Tělo procedury může mít jakýkoli druh Příkazy SQL, jako je vytvoření tabulky, vkládání jednoho nebo více řádků tabulky, nastavení typu a povahy databáze atd. Nicméně tělo řízení omezuje výkon určitých operací v něm. Některá z důležitých omezení jsou uvedena níže:

- tělo by nemělo vytvořit jinou uloženou proceduru;

- Tělo by nemělo vytvořit falešný obraz objektu;

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

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

Prováděné proměnné můžete provést do tělního postupu a pak se budou nacházet výlučně uvnitř těla postupu. Dobrou praxí je vytvořit proměnné na začátku těla uložené procedury. Ale také můžete nastavit proměnné kdekoli v těle tohoto objektu.

Někdy zjistíte, že několik proměnných je nastaveno v jednom řádku a každý proměnný parametr je oddělen čárkou. Mějte také na paměti, že proměnná má předponu @. V těle postupu můžete nastavit proměnnou, kterou chcete. Například proměnná @ NAME1 může být deklarována blíže ke konci těla procedury. Za účelem přiřazení hodnoty deklarované proměnné se používá soubor osobních údajů. Na rozdíl od situace, kdy je více než jedna proměnná deklarována v jednom řádku, je v této situaci použita pouze jedna sada osobních údajů.

Uživatelé často kladou otázku: "Jak přiřadit více hodnot do jednoho operátora v těle postupu?" No. Otázka je zajímavá, ale je mnohem jednodušší, než si myslíte. Odpověď: pomocí dvojic jako "Select Var = value". Tyto dvojice můžete použít a oddělovat je čárkou.

3. Vytvoření uloženého postupu SQL

V řadě příkladů lidé ukážou, jak vytvořit jednoduchou uloženou proceduru a spustit ji. Postup však může mít takové parametry, že proces volání bude mít hodnoty, které jsou k němu blízké (ale ne vždy). Pokud se shodují, pak se v těle začnou odpovídající procesy. Například pokud vytvoříte postup, který od volajícího zařadí město a oblast a vrátí data o tom, kolik autorů souvisí s relevantním městem a regionem. Postup bude dotazovat tabulky autorů databáze, například Pubs, k provedení tohoto počtu autorů. Chcete-li získat tyto databáze, například Google načte skript SQL ze stránky SQL2005.

V předchozím příkladu má tento postup 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 (všechny autoři) a tato proměnná se používá k zobrazení jejich čísla. Dále se zobrazí sekce pro výběr dotazu, který se počítá. Konečně vypočtená hodnota je vyvedena ve výstupním okně pomocí operátora tisku.

Jak spustit uloženou proceduru v SQL

Existují dva způsoby provedení tohoto postupu. Při předávání parametrů se zobrazí první cesta, protože se za názvem procedury provádí seznam oddělených čárkami. Předpokládejme, ž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. Tímto způsobem přenosu parametrů je objednávka důležitá. Tato metoda se nazývá postupný převod argumentů. Ve druhé metodě jsou parametry již přímo přiřazeny a v tomto případě není pořadí důležité. Tato druhá metoda je známá jako přenos pojmenovaných argumentů.

Postup se může poněkud lišit od typického. Vše je stejné jako v předchozím příkladu, ale pouze zde jsou parametry posunuty. To znamená, že parametr @City je nejdříve uložen a stav @State je uložen vedle výchozí hodnoty. Výchozí nastavení je obvykle odděleno. Uložené procedury SQL procházejí jako jednoduché parametry. V tomto případě za předpokladu, že parametr "UT" nahradí výchozí hodnotu "CA". V druhém provedení prochází pouze jeden argument hodnotu @City a @State výchozí, na „CA“. Zkušení programátoři doporučují, aby se všechny proměnné ve výchozím nastavení nacházely blíže ke konci seznamu parametrů. V opačném případě nelze provést spuštění a pak musíte pracovat s přenosem pojmenovaných argumentů, což je déle a složitější.

4. Uložené procedury SQL Server: Metody návratu

Existují tři důležité způsoby, jak odeslat data do uložené procedury, která se nazývá. Jsou uvedeny níže:

- vrátit hodnotu uložené procedury;

- výstup parametru uložené procedury;

- vyberte jednu z uložených procedur.

4.1 Vrácení hodnot uložených procedur SQL

V této proceduře postup přiřadí hodnotu místní proměnné a vrátí ji. Postup také může přímo vrátit konstantní hodnotu. V následujícím příkladu jsme vytvořili postup, který vrátí celkový počet autorů. Pokud porovnáte tento postup s předchozími, můžete zjistit, že hodnota pro tisk je nahrazena hodnotou pro tisk.

Teď se podíváme, jak postupovat a vygenerovat hodnotu, která byla vrácena. Provedení procedury vyžaduje nastavit proměnnou a tisk, která se provádí po tomto procesu. Všimněte si, že namísto příkazu pro tisk můžete použít operátor Select, například Select @RetValue a také OutputValue.

4.2 Ukončete parametr uložené procedury SQL

Hodnotu odezvy lze použít k návratu jedné proměnné, kterou jsme viděli v předchozím příkladu. Pomocí parametru Output (Výstup) lze postup odeslat jednu nebo více proměnných hodnot volajícímu. Výstupní parametr je při vytváření postupu označen stejným klíčovým slovem "Výstup". Pokud je parametr zadán jako výstupní parametr, musí objekt procedury přiřadit hodnotu. Uložené procedury SQL, jejichž příklady lze vidět níže, jsou vráceny souhrnnými informacemi.

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

Navíc v předchozím scénáři jsou deklarovány dvě proměnné, které zobrazují hodnoty, které nastavují uložené procedury MS SQL Server ve výstupním parametru. Potom se postup provádí dodáním normální hodnoty parametru "CA". Následující parametry jsou vyvedeny a proto jsou deklarované proměnné přenášeny ve stanoveném pořadí. Všimněte si, že při předávání proměnných je zde také uvedeno klíčové slovo výstupu. Po úspěšném postupu jsou hodnoty vrácené výstupními parametry vyvedeny do okna zpráv.

4.3 Vyberte jednu 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 SQL uložená procedura s parametry @AuthID dotazuje tabulku Autoři filtrováním vrácených záznamů pomocí tohoto parametru @AuthId. Operátor Select rozhodne, co má být vráceno do vyvolané uložené procedury. Při provádění uložené procedury je AuthId předána zpět. Takový postup zde vždy vrátí pouze jeden záznam nebo vůbec žádný. Uložená procedura však nemá žádné omezení pro návrat více než jednoho záznamu. Často je možné nalézt příklady, při kterých dochází k vrácení dat pomocí vybraných parametrů za účasti vypočtených proměnných tím, že se vytvoří několik souhrnných hodnot.

Na závěr

Uložená procedura je poměrně vážný softwarový modul, který se vrací nebo vysílá, a také nastavuje potřebné proměnné prostřednictvím klientské aplikace. Protože uložená procedura je spuštěna na samotném serveru, může být vyloučena výměna dat v obrovských svazcích mezi serverem a klientskou aplikací (pro některé výpočty). To vám umožní snížit zatížení serveru SQL, který samozřejmě jde do rukou držitelů. Jedním z poddruhů jsou uložené procedury T SQL, je však třeba je studovat i ti, kteří vytvářejí působivé databáze. Tam je také velký, dokonce i obrovský počet nuancí, které mohou být užitečné při studiu uložených procedur, ale je to spíše pro ty, kteří plánují zapojit se do programování, a to i profesionálně.