Co je uložená procedura sql. Zdrojová data pro příklady. Řešení shromažďování z webu "Jaký je rozdíl mezi uloženou procedurou a zobrazením?"
Uložené procedury - 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 podporuje v té či oné podobě. 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ě.
Za
Sdílení logiky s jinými aplikacemi. Uložené procedury zapouzdřují funkčnost; to poskytuje konektivitu pro přístup k datům a jejich správu 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. Podle předchozího bodu, 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. Abyste mohli psát uložené procedury, budete se muset naučit syntaxi výrazů MySQL.
Duplikujete svou aplikační logiku na dvou místech: kód serveru a kód pro uložené procedury, čímž se komplikuje proces manipulace s daty.
Migrace z jednoho DBMS do druhého (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 vám umožní volat složitý skript obsažený v uložené proceduře, aniž byste se museli posílat stovky příkazů po síti a zejména nutnost 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é procedury.
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 zápis uložených procedur jazyk Java. 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 balíčku, která specifikuje definici uložené procedury, a těla balíčku, který obsahuje její implementaci. 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 volitelné, 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;
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žena v konkrétní databázi. Přímé volání uložené procedury je možné pouze v případě, že je provedeno v kontextu databáze, kde je procedura umístěna.
Typy uložených procedur
SQL Server má několik typů uložených procedur.
Systémové uložené procedury jsou navrženy 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émové 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.
Vlastní uložené procedury implementují určité akce. Uložené procedury jsou plnohodnotným databázovým objektem. V důsledku toho je každá uložená procedura umístěna v konkrétní databázi, kde se provádí.
Dočasně uložené procedury existují pouze krátkou dobu, po které jsou serverem automaticky zničeny. Dělí se na lokální a globální. Místní dočasně uložené procedury lze volat pouze z připojení, ve kterém jsou vytvořeny. Při vytváření takové procedury jí musíte dát název začínající jedním znakem #. Stejně jako všechny dočasné objekty jsou uložené procedury tohoto typu 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.
Spouštěče
Spouštěče jsou typem uložené procedury. Jsou prováděny, když je na tabulce spuštěn operátor jazyka pro manipulaci s daty (DML). Spouštěče se používají ke kontrole integrity dat a také k vrácení transakcí.
Spoušť je zkompilovaná SQL procedura, jejíž provedení je podmíněno výskytem určitých událostí v rámci relační databáze. Použití spouštěčů je pro uživatele databáze z velké části velmi pohodlné. Přesto jejich použití často vyžaduje dodatečné náklady na zdroje pro I/O operace. Když lze stejných výsledků (s mnohem menší režií) dosáhnout pomocí uložených procedur nebo aplikačních programů, není použití spouštěčů praktické.
Spouštěče je speciální nástroj SQL serveru používaný k udržení integrity dat v databázi. Omezení integrity, pravidla a výchozí hodnoty nemusí vždy dosáhnout požadované úrovně funkčnosti. Často je nutné implementovat složité algoritmy ověřování dat, aby byla zajištěna jejich spolehlivost a reálnost. Kromě toho někdy potřebujete sledovat změny v hodnotách tabulky, aby bylo možné související data upravit podle potřeby. Spouštěče lze chápat jako jakési filtry, které vstoupí v platnost po dokončení všech operací v souladu s pravidly, standardními hodnotami atd.
Spoušť je speciální typ uložené procedury, která je automaticky spouštěna serverem při pokusu o změnu dat v tabulkách, ke kterým jsou přidruženy spouštěče. Každý Spoušť je vázán na konkrétní stůl. Všechny úpravy dat, které provede, jsou považovány za jednu transakci. Pokud je zjištěna chyba nebo porušení integrity dat, transakce je odvolána. Změny jsou proto zakázány. Všechny změny, které již spouštěč provedl, se také vrátí zpět.
Vytvoří spoušť pouze vlastník databáze. Toto omezení umožňuje vyhnout se náhodným změnám struktury tabulek, způsobů připojení dalších objektů k nim atd.
Spoušť Je to velmi užitečný a zároveň nebezpečný prostředek. Pokud je tedy logika jeho fungování nesprávná, můžete snadno zničit celou databázi, takže spouštěče musí být laděny velmi pečlivě.
Na rozdíl od běžného podprogramu spoušť je spuštěn implicitně, kdykoli nastane spouštěcí událost, a nemá žádné argumenty. Jeho aktivaci se někdy říká vystřelení spouště. Pomocí spouštěčů je dosaženo následujících cílů:
Ověřování správnosti zadaných dat a vynucování komplexních omezení integrity dat, která je obtížné, ne-li nemožné, udržovat pomocí omezení integrity nastavených v tabulce;
vydávání varování, která vám připomínají, abyste provedli určité akce při aktualizaci tabulky implementované určitým způsobem;
shromažďování informací o auditu zaznamenáváním informací o provedených změnách a osobách, které je provedly;
podpora replikace.
Základní formát příkazu CREATE TRIGGER je uveden níže:
<Определение_триггера>::=
CREATE TRIGGER název_spouštěče
PŘED | PO<триггерное_событие>
NA<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Spouštěcí události se skládají z vkládání, mazání a aktualizace řádků v tabulce. V druhém případě můžete zadat konkrétní názvy sloupců tabulky pro spouštěcí událost. Načasování spouštěče je určeno pomocí klíčových slov BEFORE ( Spoušť běží před provedením událostí s ním spojených) nebo AFTER (po jejich provedení).
Akce prováděné spouštěčem jsou určeny pro každý řádek (PRO KAŽDÝ ŘÁDEK), na který se vztahuje událost, nebo pouze jednou pro každou událost (PRO KAŽDÝ VÝKAZ).
Nesprávně zapsané spouštěče mohou vést k vážným problémům, jako jsou mrtvé zámky. Spouštěče mohou blokovat mnoho zdrojů na dlouhou dobu, takže byste jim měli věnovat pozornost Speciální pozornost minimalizovat konflikty přístupu.
Spoušť lze vytvořit pouze v aktuální databázi, ale je možné přistupovat k dalším databázím v rámci triggeru, včetně těch, které se nacházejí na vzdáleném serveru.
1. Zahrňte do svých postupů řádek – SET NOCOUNT ON: S každým DML výrazem nám SQL server pečlivě vrací zprávu obsahující počet zpracovaných záznamů. Tato informace Může se nám to hodit při ladění kódu, ale potom to bude úplně k ničemu. Zápisem SET NOCOUNT ON tuto funkci zakážeme. U uložených procedur obsahujících více výrazů nebo/nebo smyček může tato akce poskytnout významné zvýšení výkonu, protože se výrazně sníží objem provozu.
VYTVOŘIT PROC dbo.ProcName
TAK JAKO
SET NOCOUNT ON;
--Zde je kód postupu
VYBERTE sloupec1 Z dbo.TblTable1
--Přepněte SET NOCOUNT do výchozího stavu
SET NOCOUNT OFF;
JÍT
2. Použijte název schématu s názvem objektu: No, myslím, že je to jasné. Tato operace říká serveru, kde má hledat předměty, a místo náhodného prohrabávání se v popelnicích bude okamžitě vědět, kam má jít a co si vzít. S velkým množstvím databází, tabulek a uložených procedur nám může výrazně ušetřit čas a nervy.
SELECT * FROM dbo.MyTable --To je dobré udělat
-- Namísto
SELECT * FROM MyTable --A dělat to je špatné
--Výzva postupu
EXEC dbo.MyProc --Opět dobré
--Namísto
EXEC MyProc – Špatné!
3. Nepoužívejte předponu „sp_“ v názvu svých uložených procedur: Pokud název naší procedury začíná "sp_", SQL Server se nejprve podívá do své hlavní databáze. Faktem je, že tato předpona se používá pro osobní interní uložené procedury serveru. Proto může jeho použití vést k dodatečným nákladům a dokonce k nesprávným výsledkům, pokud je v jeho databázi nalezen postup se stejným názvem jako vy.
4. Použijte IF EXISTS (VYBRAT 1) místo IF EXISTS (SELECT *): Pro kontrolu existence záznamu v jiné tabulce použijeme příkaz IF EXISTS. Tento výraz vrátí hodnotu true, pokud je vrácena alespoň jedna hodnota z interního výrazu, nezáleží na „1“, na všech sloupcích nebo tabulce. Vrácená data se v podstatě nijak nevyužívají. Pro komprimaci provozu během přenosu dat je tedy logičtější použít „1“, jak je uvedeno níže:
POKUD EXISTUJE (VYBERTE 1 ZE sysobjects
WHERE name = "MyTable" AND type = "U")
5. Použijte TRY-Catch k zachycení chyb: Před rokem 2005 bylo na serverech po každém požadavku zapsáno velké množství kontrol chyb v proceduře. Více kódu vždy spotřebuje více zdrojů a více času. S 2005 SQL Server, správnější a pohodlný způsobřešení tohoto problému:
ZAČNĚTE VYZKOUŠET
--kód
KONEC POKUS
ZAČNĚTE ÚLOVAT
--chyba při zachycení kódu
KONEC ÚLOVKU
Závěr
V podstatě to je pro dnešek vše, co mám. Ještě jednou opakuji, že zde jsou pouze ty techniky, které jsem osobně používal ve své praxi a mohu ručit za jejich účinnost.
P.S.
Můj první příspěvek, nesuďte příliš přísně.
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 klientovi aplikační programy získat dobrý výkon. Navíc jsou takové objekty často volány z jiných skriptů nebo dokonce z nějaké jiné sekce.
Úvod
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ě) do aplikace, která ji zavolala, pomocí různé metody, s jehož pomocí lze provést jak volání uložené procedury SQL, tak návrat, 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?" Studna. 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 přijímá dva parametry, které anglický jazyk podmíněně se bude nazývat @Stát a @Město. 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 parametr @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í provedení 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á se 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í možnosti jsou výstupem a proto jsou deklarované proměnné předávány v předepsaném pořadí. Všimněte si, že při průchodu proměnnými výstup klíčové slovo ptal se zde také. Po úspěšném dokončení postupu se v okně zprávy zobrazí hodnoty vrácené výstupními parametry.
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ů.
Konečně
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ž jejich držitelům samozřejmě přináší výhody. 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ě.
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 pouze ty podprogramy ve vztahu ke kterým tohoto uživatele má oprávnění k provádě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.