Čo je uložená procedúra sql. Počiatočné údaje pre príklady. Zber riešení z webu "Aký je rozdiel medzi uloženou procedúrou a zobrazením?"
uložená procedúra - databázový objekt, ktorý je množinou SQL príkazov, ktoré sú raz skompilované a uložené na serveri. Uložené procedúry sú veľmi podobné bežným procedúram vo vyšších jazykoch, môžu mať vstupné a výstupné parametre a lokálne premenné, môžu vykonávať numerické výpočty a operácie so znakovými dátami, ktorých výsledky možno priradiť k premenným a parametrom. Uložené procedúry môžu vykonávať štandardné databázové operácie (DDL aj DML). Okrem toho sú v uložených procedúrach možné slučky a vetvy, to znamená, že môžu používať inštrukcie na riadenie procesu vykonávania.
Uložené procedúry sú podobné užívateľsky definovaným funkciám (UDF). Hlavný rozdiel je v tom, že užívateľom definované funkcie možno použiť ako akýkoľvek iný výraz v dotaze SQL, zatiaľ čo uložené procedúry je potrebné volať pomocou funkcie CALL:
Postup CALL(...)
VYKONAŤ postup (…)
Uložené procedúry môžu vrátiť množiny výsledkov, teda výsledky SELECT dotazu. Takéto sady výsledkov možno spracovať pomocou kurzorov, inými uloženými procedúrami, ktoré vracajú ukazovateľ sady výsledkov, alebo aplikáciami. Uložené procedúry môžu obsahovať aj deklarované premenné na spracovanie údajov a kurzorov, ktoré vám umožňujú prechádzať cez viacero riadkov v tabuľke. Štandard SQL poskytuje IF, LOOP, REPEAT, CASE a mnoho ďalších výrazov na prácu. Uložené procedúry môžu prijímať premenné, vracať výsledky alebo upravovať premenné a vracať ich v závislosti od toho, kde je premenná deklarovaná.
Implementácia uložených procedúr sa líši od jedného DBMS k druhému. Väčšina veľkých databázových predajcov ich podporuje v tej či onej forme. V závislosti od DBMS môžu byť uložené procedúry implementované v rôznych programovacích jazykoch, ako sú SQL, Java, C alebo C++. Uložené procedúry napísané v inom jazyku ako SQL môžu alebo nemusia vykonávať dotazy SQL samostatne.
Za
Zdieľanie logiky s inými aplikáciami. Uložené procedúry zapuzdrujú funkčnosť; to zabezpečuje prístup k dátam a konektivitu správy medzi rôznymi aplikáciami.
Izolujte používateľov z databázových tabuliek. To vám umožňuje poskytnúť prístup k uloženým procedúram, ale nie k samotným údajom tabuľky.
Poskytuje ochranný mechanizmus. Podľa predchádzajúceho bodu, ak máte prístup k údajom iba prostredníctvom uložených procedúr, nikto iný nemôže vymazať vaše údaje pomocou príkazu SQL DELETE.
Zlepšený výkon v dôsledku zníženej sieťovej prevádzky. S uloženými procedúrami je možné kombinovať viacero dotazov.
Proti
Zvýšené zaťaženie databázového servera v dôsledku skutočnosti, že väčšina práce sa vykonáva na strane servera a menej na strane klienta.
Musíte sa veľa učiť. Na písanie uložených procedúr sa budete musieť naučiť syntax výrazu MySQL.
Svoju aplikačnú logiku duplikujete na dvoch miestach: kód servera a kód pre uložené procedúry, čím sa komplikuje proces manipulácie s údajmi.
Migrácia z jedného DBMS do druhého (DB2, SQL Server atď.) môže viesť k problémom.
Účel a výhody uložených procedúr
Uložené procedúry zlepšujú výkon, zlepšujú možnosti programovania a podporujú funkcie zabezpečenia údajov.
Namiesto uloženia často používaného dotazu môžu klienti odkazovať na príslušnú uloženú procedúru. Keď sa zavolá uložená procedúra, jej obsah server okamžite spracuje.
Okrem samotného vykonávania dotazu vám uložené procedúry umožňujú aj vykonávať výpočty a manipulovať s údajmi – meniť, mazať, spúšťať príkazy DDL (nie vo všetkých DBMS!) a volať ďalšie uložené procedúry, vykonávať komplexnú transakčnú logiku. Jediný príkaz umožňuje zavolať zložitý skript, ktorý je obsiahnutý v uloženej procedúre, čím sa vyhnete posielaniu stoviek príkazov po sieti a najmä potrebe prenosu veľkého množstva dát z klienta na server.
Vo väčšine DBMS sa uložená procedúra pri prvom spustení skompiluje (analyzuje a vygeneruje sa plán prístupu k údajom). V budúcnosti je jeho spracovanie rýchlejšie. Oracle DBMS interpretuje uložený procedurálny kód uložený v dátovom slovníku. Počnúc Oracle 10g je podporovaná takzvaná natívna kompilácia (natívna kompilácia) kódu uloženej procedúry v C a následne do strojového kódu cieľového stroja, po ktorej sa po zavolaní uloženej procedúry vykoná jej skompilovaný objektový kód. priamo vykonaný.
Možnosti programovania
Vygenerovanú uloženú procedúru možno volať kedykoľvek, čo poskytuje modularitu a podporuje opätovné použitie kódu. Ten uľahčuje údržbu databázy, pretože sa stáva izolovanou od meniacich sa obchodných pravidiel. Uloženú procedúru môžete kedykoľvek upraviť tak, aby vyhovovala novým pravidlám. Potom budú všetky aplikácie, ktoré ho používajú, automaticky v súlade s novými obchodnými pravidlami bez priamej úpravy.
Bezpečnosť
Použitie uložených procedúr umožňuje obmedziť alebo úplne vylúčiť priamy prístup používateľov k databázovým tabuľkám, pričom používateľom ponecháva iba povolenia na vykonávanie uložených procedúr, ktoré poskytujú nepriamy a prísne regulovaný prístup k údajom. Niektoré DBMS navyše podporujú textové šifrovanie (zabalenie) uloženej procedúry.
Tieto funkcie zabezpečenia vám umožňujú izolovať štruktúru databázy od používateľa, čo zaisťuje integritu a spoľahlivosť databázy.
Akcie ako "vloženie SQL" sú menej pravdepodobné, pretože dobre napísané uložené procedúry dodatočne overujú vstupné parametre pred odoslaním dotazu do DBMS.
Implementácia uložených procedúr
Uložené procedúry sa zvyčajne vytvárajú pomocou jazyka SQL alebo jeho špecifickej implementácie vo vybranom DBMS. Napríklad na tieto účely má DBMS Microsoft SQL Server jazyk Transact-SQL, Oracle má PL/SQL, InterBase a Firebird má PSQL, PostgreSQL má PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 - SQL / PL (anglicky), v Informixe - SPL. MySQL sa pomerne presne riadi štandardom SQL:2003 a jeho jazyk je podobný SQL/PL.
V niektorých DBMS je možné použiť uložené procedúry napísané v akomkoľvek programovacom jazyku schopnom vytvárať nezávislé spustiteľné súbory, ako napríklad C++ alebo Delphi. V terminológii Microsoft SQL Server sa tieto procedúry nazývajú rozšírené uložené procedúry a sú to jednoducho funkcie obsiahnuté vo Win32-DLL. A napríklad v Interbase a Firebird pre funkcie volané z DLL / SO je definovaný iný názov - UDF (User Defined Function). V MS SQL 2005 bolo možné písať uložené procedúry v akomkoľvek jazyku .NET a plánuje sa, že rozšírené uložené procedúry budú v budúcnosti opustené. Oracle DBMS zase umožňuje zapisovanie uložených procedúr jazyk Java. V IBM DB2 je písanie uložených procedúr a funkcií v konvenčných programovacích jazykoch tradičným spôsobom podporovaným od začiatku a procedurálne rozšírenie SQL bolo pridané do tohto DBMS až pomerne neskoro, po jeho zaradení do štandardu ANSI. Informix podporuje aj procedúry Java a C.
V Oracle DBMS možno uložené procedúry kombinovať do takzvaných balíkov. Balík sa skladá z dvoch častí – špecifikácie (anglicky package Specification), ktorá špecifikuje definíciu uloženej procedúry, a tela (anglicky package body), kde sa nachádza jej implementácia. Oracle vám teda umožňuje oddeliť rozhranie programového kódu od jeho implementácie.
V IBM DB2 možno uložené procedúry kombinovať do modulov.
Syntax
VYTVORIŤ POSTUP `p2`()
SQL DEFINER BEZPEČNOSTI
KOMENTÁR "Postup"
SELECT "Ahoj Svet!";
Prvá časť kódu vytvára uloženú procedúru. Ďalej - obsahuje voliteľné parametre. Potom nasleduje názov a nakoniec telo samotnej procedúry.
4 charakteristiky uloženej procedúry:
Jazyk: Pre účely prenosnosti je predvolená hodnota SQL.
Deterministický: Ak procedúra vracia stále rovnaký výsledok a prijíma rovnaké vstupné parametre. Toto je pre proces replikácie a registrácie. Predvolená hodnota je NOT DETERMINISTIC.
Zabezpečenie SQL: počas hovoru sa kontrolujú práva užívateľa. INVOKER je používateľ, ktorý volá uloženú procedúru. DEFINER je „tvorcom“ postupu. Predvolená hodnota je DEFINER.
Komentár: na účely dokumentácie je predvolená hodnota ""
Volanie uloženej procedúry
CALL uložený_názov_procedúry (param1, param2, ....)
CALL procedure1(10 , "parameter reťazca" , @parameter_var);
Zmena uloženej procedúry
MySQL má príkaz ALTER PROCEDURE na úpravu procedúr, ale je vhodný len na úpravu určitých charakteristík. Ak potrebujete zmeniť parametre alebo telo procedúry, musíte ju vymazať a znova vytvoriť.
Odstránenieuloženépostupy
POSTUP VYPADANIA, AK EXISTUJE p2;
Toto je jednoduchý príkaz. Príkaz IF EXISTS zachytí chybu, ak takýto postup neexistuje.
možnosti
CREATE PROCEDURE proc1(): prázdny zoznam parametrov
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): jeden vstupný parameter. Slovo IN je voliteľné, pretože predvolené parametre sú IN (prichádzajúce).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): jeden návratový parameter.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): jeden parameter, vstupný aj výstupný.
Syntax na deklarovanie premennej vyzerá takto:
DECLARE názov premennej DATA-TYPE DEFAULT predvolená hodnota;
Pri práci so serverom SQL Server môžu používatelia vytvárať vlastné procedúry, ktoré implementujú určité akcie. Uložené procedúry sú plnohodnotné databázové objekty, a preto je každá z nich uložená v konkrétnej databáze. Priame volanie uloženej procedúry je možné len vtedy, ak sa vykonáva v kontexte databázy, kde sa procedúra nachádza.
Typy uložených procedúr
SQL Server má niekoľko typov uložených procedúr.
Systémové uložené procedúry sú určené na vykonávanie rôznych administratívnych akcií. Takmer všetky akcie správy servera sa vykonávajú s ich pomocou. Dá sa povedať, že systémové uložené procedúry sú rozhraním, ktoré poskytuje prácu so systémovými tabuľkami, čo v konečnom dôsledku spočíva v zmene, pridávaní, odstraňovaní a získavaní údajov zo systémových tabuliek používateľských aj systémových databáz. Systémové uložené procedúry majú predponu sp_, sú uložené v systémovej databáze a možno ich volať v kontexte akejkoľvek inej databázy.
Vlastné uložené procedúry implementujú určité akcie. Uložené procedúry sú úplný databázový objekt. V dôsledku toho sa každá uložená procedúra nachádza v konkrétnej databáze, kde sa vykonáva.
Dočasne uložené procedúry existujú len krátky čas, po ktorom ich server automaticky zničí. Delia sa na lokálne a globálne. Lokálne dočasne uložené procedúry možno volať len z pripojenia, na ktorom boli vytvorené. Pri vytváraní takejto procedúry musí dostať názov, ktorý začína jedným znakom #. Rovnako ako všetky dočasné objekty, uložené procedúry tohto typu sa automaticky vymažú, keď používateľ odpojí, reštartuje alebo zastaví server. Globálne dočasne uložené procedúry sú dostupné pre každé pripojenie na serveri, ktoré má rovnakú procedúru. Na jeho definovanie stačí dať mu názov začínajúci znakmi ##. Tieto procedúry sa vymažú, keď sa server reštartuje alebo zastaví, alebo keď sa zatvorí spojenie, v kontexte ktorého boli vytvorené.
spúšťače
spúšťače sú jednou z odrôd uložených procedúr. Ich vykonanie nastane, keď sa na stole vykoná príkaz jazyka manipulácie s údajmi (DML). Spúšťače sa používajú na kontrolu integrity údajov a tiež na vrátenie transakcií.
Spúšťač je zostavená SQL procedúra, ktorej vykonanie je určené výskytom určitých udalostí v rámci relačnej databázy. Použitie spúšťačov je z veľkej časti veľmi pohodlné pre používateľov databázy. Ich použitie je však často spojené s dodatočnými nákladmi na zdroje pre I/O operácie. Keď je možné dosiahnuť rovnaké výsledky (s oveľa menšou réžiou prostriedkov) pomocou uložených procedúr alebo aplikácií, spúšťače sú nevhodné.
spúšťače je špeciálny nástroj SQL servera, ktorý sa používa na udržiavanie integrity údajov v databáze. Obmedzenia integrity, pravidlá a predvolené hodnoty nemusia vždy poskytovať požadovanú úroveň funkčnosti. Často je potrebné implementovať komplexné algoritmy overovania údajov, aby sa zabezpečilo, že sú platné a skutočné. Okrem toho je niekedy potrebné sledovať zmeny hodnôt tabuľky, aby bolo možné podľa potreby zmeniť súvisiace údaje. Spúšťače si možno predstaviť ako druh filtrov, ktoré sa prejavia po vykonaní všetkých operácií podľa pravidiel, predvolených hodnôt atď.
Spúšťač je špeciálny typ uloženej procedúry, ktorú server automaticky spúšťa, keď sa pokúša zmeniť údaje v tabuľkách, ku ktorým sú priradené spúšťače. Každý Spúšťač je viazaná na konkrétny stôl. Všetky úpravy údajov, ktoré vykoná, sa považujú za jednu transakciu. Ak sa zistí chyba alebo porušenie integrity údajov, transakcia sa vráti späť. Preto sú zmeny zakázané. Všetky zmeny, ktoré už vykonal spúšťač, sa tiež vrátia späť.
Vytvára spúšťač iba vlastník databázy. Toto obmedzenie vám umožňuje vyhnúť sa náhodným zmenám v štruktúre tabuliek, spôsoboch prepojenia iných objektov s nimi atď.
Spúšťač je veľmi užitočný a zároveň nebezpečný nástroj. Takže s nesprávnou logikou jeho práce môžete ľahko zničiť celú databázu, takže spúšťače musia byť ladené veľmi opatrne.
Na rozdiel od bežného podprogramu, spúšťač sa vykoná implicitne vždy, keď nastane spúšťacia udalosť, a nemá žiadne argumenty. Jeho aktivácia sa niekedy označuje ako spustenie spúšte. Spúšťače dosahujú tieto ciele:
validácia zadaných údajov a implementácia komplexných obmedzení integrity údajov, ktoré je ťažké, ak nie nemožné, udržiavať s obmedzeniami integrity stanovenými v tabuľke;
vydávanie upozornení, ktoré vám pripomínajú potrebu vykonať určité akcie pri aktualizácii tabuľky implementovanej určitým spôsobom;
zhromažďovanie informácií o audite fixovaním informácií o vykonaných zmenách a osobách, ktoré ich vykonali;
podpora replikácie.
Základný formát príkazu CREATE TRIGGER je uvedený nižšie:
<Определение_триггера>::=
CREATE TRIGGER názov spúšťača
PRED | PO<триггерное_событие>
ON<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Spúšťacie udalosti pozostávajú z vkladania, odstraňovania a aktualizácie riadkov v tabuľke. V druhom prípade je možné zadať špecifické názvy stĺpcov tabuľky pre spúšťaciu udalosť. Čas začiatku spúšťača je definovaný pomocou kľúčových slov BEFORE ( Spúšťač požiare pred vykonaním súvisiacich udalostí) alebo PO (po ich vykonaní).
Akcie vykonávané spúšťačom sú nastavené pre každý riadok (PRE KAŽDÝ RIADOK), na ktorý sa vzťahuje táto udalosť, alebo len raz pre každú udalosť (PRE KAŽDÝ VÝKAZ).
Nesprávne napísané spúšťače môžu viesť k vážnym problémom, ako je napríklad vzhľad "mŕtvych" zámkov. Spúšťače môžu blokovať veľa zdrojov na dlhú dobu, takže by ste im mali venovať pozornosť Osobitná pozornosť minimalizovať konflikty prístupu.
Spúšťač možno vytvoriť iba v aktuálnej databáze, ale v rámci spúšťača je možné pristupovať k iným databázam vrátane tých, ktoré sa nachádzajú na vzdialenom serveri.
1. Zahrňte do svojich postupov riadok - SET NOCOUNT ON: S každým DML príkazom nám SQL server opatrne vracia správu s počtom spracovaných záznamov. Táto informácia môže byť pre nás užitočné pri ladení kódu, ale potom bude úplne zbytočné. Napísaním SET NOCOUNT ON túto funkciu deaktivujeme. Pre uložené procedúry obsahujúce viacero výrazov alebo slučiek môže táto akcia výrazne zvýšiť výkon, pretože objem prevádzky sa výrazne zníži.
VYTVORIŤ PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Kód postupu tu
VYBERTE stĺpec1 z dbo.TblTable1
--Prepnite SET NOCOUNT do počiatočného stavu
SET NOCOUNT OFF;
Ísť
2. Použite názov schémy s názvom objektu: No myslím, že je to jasné. Táto operácia povie serveru, kde má hľadať predmety, a namiesto náhodného prehrabávania sa v košoch bude okamžite vedieť, kam má ísť a čo si má vziať. S veľkým množstvom databáz, tabuliek a uložených procedúr nám môže výrazne ušetriť čas a nervy.
SELECT * FROM dbo.MyTable --Toto je dobrý spôsob, ako to urobiť
-- Namiesto
SELECT * FROM MyTable --To je zlá vec
--Výzva postupu
EXEC dbo.MyProc -- Opäť dobré
--Namiesto
EXEC MyProc – Zlý!
3. Nepoužívajte predponu "sp_" v názve vašich uložených procedúr: Ak názov našej procedúry začína na "sp_", SQL Server sa najprv pozrie do svojej hlavnej databázy. Faktom je, že táto predpona sa používa pre osobné interné uložené procedúry servera. Preto môže jeho použitie viesť k dodatočným nákladom a dokonca k nesprávnym výsledkom, ak sa v jeho databáze nájde postup s rovnakým názvom ako ten váš.
4. Použite IF EXISTS (SELECT 1) namiesto IF EXISTS (SELECT *): Na kontrolu, či záznam existuje v inej tabuľke, použijeme príkaz IF EXISTS. Tento výraz vráti hodnotu true, ak sa z vnútorného výrazu vráti aspoň jedna hodnota, nezáleží na "1", na všetkých stĺpcoch alebo tabuľke. Vrátené údaje sa v zásade žiadnym spôsobom nepoužívajú. Na kompresiu prevádzky počas prenosu údajov je teda logickejšie použiť „1“, ako je uvedené nižšie:
AK EXISTUJE (VYBERTE 1 ZO syobjektov
WHERE name = "MyTable" AND type = "U")
5. Použite TRY-Catch na zachytenie chýb: Pred serverom 2005 bolo po každej požiadavke v procedúre zapísaných veľké množstvo kontrol chýb. Viac kódu vždy spotrebuje viac zdrojov a viac času. Od roku 2005 sa SQL Server javil ako správnejší a pohodlný spôsob riešenia tohto problému:
ZAČAŤ SKÚŠAŤ
--kód
KONIEC POKUS
ZAČAŤ CHYTÁVAŤ
-- kód zachytenia chyby
KONIEC ÚLOVKU
Záver
V podstate to je všetko, čo mám na dnes. Ešte raz opakujem, že tu sú len tie techniky, ktoré som osobne použil vo svojej praxi a môžem ručiť za ich účinnosť.
P.S.
Môj prvý príspevok, nesúďte striktne.
Uložené procedúry SQL sú spustiteľný programový modul, ktorý možno uložiť do rôznych objektov. Inými slovami, je to objekt, ktorý obsahuje príkazy SQL. Tieto uložené procedúry je možné spustiť v klientovi aplikačné programy získať dobrý výkon. Tiež nie je nezvyčajné, že sa takéto objekty volajú z iných skriptov alebo dokonca z inej sekcie.
Úvod
Mnohí sa domnievajú, že sú podobné rôznym postupom (s výnimkou MS SQL). Možno je to pravda. Majú podobné parametre, môžu produkovať podobné hodnoty. Navyše v niektorých prípadoch sú v kontakte. Napríklad sú kombinované s databázami DDL a DML, ako aj používateľskými funkciami (kódové označenie UDF).
V skutočnosti majú uložené procedúry SQL širokú škálu výhod, vďaka ktorým sa odlišujú od podobných procesov. Bezpečnosť, flexibilita programovania, produktivita – to všetko láka stále viac používateľov pracujúcich s databázami. Vrchol popularity procedúr prišiel v rokoch 2005-2010, kedy bol vydaný program od Microsoftu s názvom SQL Server Management Studio. S jeho pomocou sa práca s databázami stala oveľa jednoduchšou, praktickejšou a pohodlnejšou. Z roka na rok si tento získaval medzi programátormi obľubu. Dnes je to absolútne známy program, ktorý sa pre používateľov „komunikujúcich“ s databázami vyrovnal Excelu.
Keď je volaná procedúra, je okamžite spracovaná samotným serverom bez zbytočných procesov a zásahov užívateľa. Potom môžete vykonať akékoľvek vymazanie, vykonanie, zmenu. Za toto všetko je zodpovedný operátor DDL, ktorý jediný vykonáva najzložitejšie úkony na spracovanie objektov. Navyše sa to všetko deje veľmi rýchlo a server sa v skutočnosti nenačíta. Táto rýchlosť a výkon vám umožňuje veľmi rýchlo prenášať veľké množstvo informácií od používateľa na server a naopak.
Na implementáciu tejto technológie práce s informáciami existuje niekoľko programovacích jazykov. Patria sem napríklad PL / SQL od Oracle, PSQL v systémoch InterBase a Firebird, ale aj klasický „Microsoft“ Transact-SQL. Všetky sú navrhnuté tak, aby vytvárali a spúšťali uložené procedúry, čo umožňuje veľkým databázovým procesorom používať ich vlastné algoritmy. Je to potrebné aj na to, aby tí, ktorí spravujú takéto informácie, mohli chrániť všetky objekty pred neoprávneným prístupom tretích strán, a teda aj vytváraním, upravovaním alebo vymazávaním určitých údajov.
Produktivita
Tieto databázové objekty je možné programovať rôznymi spôsobmi. To umožňuje používateľom vybrať si typ metódy, ktorá je najvhodnejšia, čo šetrí čas a námahu. Okrem toho je spracovaná samotná procedúra, čím sa vyhnete obrovskému času strávenému výmenou medzi serverom a používateľom. Modul je tiež možné kedykoľvek preprogramovať a zmeniť správnym smerom. Za zmienku stojí najmä rýchlosť, s akou sa uložená procedúra SQL spúšťa: tento proces je rýchlejší ako iné jemu podobné, vďaka čomu je pohodlný a všestranný.
Bezpečnosť
Tento typ spracovania informácií sa líši od podobných procesov tým, že zaručuje zvýšenú bezpečnosť. To je zabezpečené tým, že prístup iných používateľov k procedúram je možné úplne vylúčiť. To umožní správcovi vykonávať s nimi operácie nezávisle, bez obáv zo zachytenia informácií alebo neoprávneného prístupu do databázy.
Prenos dát
Vzťah medzi uloženou procedúrou SQL a klientskou aplikáciou je použitie parametrov a návratových hodnôt. Ten nemusí prenášať dáta do uloženej procedúry, ale tieto informácie (väčšinou na žiadosť užívateľa) sú spracované pre SQL. Keď uložená procedúra dokončí svoju prácu, odošle dátové pakety späť (ale opäť voliteľne) do aplikácie, ktorá ju zavolala, pomocou rôzne metódy, pomocou ktorého je možné vykonať volanie uloženej procedúry SQL aj návrat, napríklad:
Odovzdávanie údajov pomocou parametra typu výstupu;
Odovzdávanie údajov pomocou príkazu return;
Odovzdávanie údajov pomocou príkazu select.
Teraz sa pozrime, ako tento proces vyzerá zvnútra.
1. Vytvorte uloženú procedúru EXEC v SQL
Procedúru môžete vytvoriť v MS SQL (Managment Studio). Po vytvorení procedúry bude uvedená v uzle programovateľnej databázy, v ktorej procedúru vytvárania vykonáva operátor. Uložené procedúry SQL používajú na vykonanie proces EXEC, ktorý obsahuje názov samotného objektu.
Po vytvorení procedúry sa najprv zobrazí jej názov a za ním jeden alebo viac parametrov, ktoré sú k nej priradené. Parametre môžu byť voliteľné. Po napísaní parametra (parametrov), teda tela procedúry, je potrebné vykonať niektoré potrebné operácie.
Ide o to, že telo môže mať v sebe umiestnené lokálne premenné a tieto premenné sú lokálne aj pre procedúry. Inými slovami, možno ich zvážiť iba v rámci tela procedúry Microsoft SQL Server. Uložené procedúry sa v tomto prípade považujú za lokálne.
Na vytvorenie procedúry teda potrebujeme názov procedúry a aspoň jeden parameter ako telo procedúry. Všimnite si, že skvelou možnosťou je v tomto prípade vytvoriť a spustiť procedúru s názvom schémy v klasifikátore.
Telo procedúry môže byť akéhokoľvek druhu, napríklad vytvorenie tabuľky, vloženie jedného alebo viacerých riadkov tabuľky, nastavenie typu a povahy databázy atď. Telo postupu však obmedzuje vykonávanie niektorých operácií v ňom. Niektoré z dôležitých obmedzení sú uvedené nižšie:
Telo nesmie vytvárať žiadnu inú uloženú procedúru;
Telo nesmie vytvárať falošný dojem o predmete;
Telo by nemalo vytvárať žiadne spúšťače.
2. Nastavenie premennej v tele procedúry
Premenné môžete urobiť lokálne v tele procedúry a potom budú výlučne v tele procedúry. Je dobrým zvykom vytvárať premenné na začiatku tela uloženej procedúry. Ale môžete tiež nastaviť premenné kdekoľvek v tele daného objektu.
Niekedy si môžete všimnúť, že na rovnakom riadku je nastavených niekoľko premenných a každý parameter premennej je oddelený čiarkou. Všimnite si tiež, že premenná má predponu @. V tele procedúry môžete nastaviť premennú kdekoľvek chcete. Napríklad premenná @NAME1 môže byť deklarovaná na konci tela procedúry. Na priradenie hodnoty deklarovanej premennej sa používa súbor osobných údajov. Na rozdiel od situácie, keď je na rovnakom riadku deklarovaných viac ako jedna premenná, v tejto situácii sa používa iba jeden súbor osobných údajov.
Používatelia sa často pýtajú: "Ako priradiť viacero hodnôt v jednom príkaze v tele procedúry?" Dobre. Otázka je zaujímavá, ale je to oveľa jednoduchšie, ako si myslíte. Odpoveď: s pármi ako "Select Var = Value". Tieto dvojice môžete použiť tak, že ich oddelíte čiarkou.
V rôznych príkladoch ľudia ukazujú vytvorenie jednoduchej uloženej procedúry a jej spustenie. Procedúra však môže mať také parametre, že volajúci proces bude mať hodnoty blízko k nemu (ale nie vždy). Ak sa zhodujú, príslušné procesy začnú vo vnútri tela. Napríklad, ak vytvoríte procedúru, ktorá zoberie mesto a región od volajúceho a vráti, koľko autorov patrí do zodpovedajúceho mesta a regiónu. Procedúra požiada tabuľky autorov databázy, napr. Pubs, aby vykonala tento počet autorov. Na získanie týchto databáz si napríklad Google stiahne SQL skript zo stránky SQL2005.
V predchádzajúcom príklade má postup dva parametre, ktorými sú anglický jazyk konvenčne sa bude nazývať @State a @City. Typ údajov sa zhoduje s typom definovaným v aplikácii. Telo procedúry má interné premenné @TotalAuthors (celkový počet autorov) a táto premenná sa používa na zobrazenie ich počtu. Nasleduje sekcia výberu dotazu, ktorá počíta všetko. Nakoniec sa vypočítaná hodnota vytlačí do výstupného okna pomocou tlačového príkazu.
Ako vykonať uloženú procedúru v SQL
Existujú dva spôsoby vykonania postupu. Prvý spôsob ukazuje, zadaním parametrov, ako sa vykoná zoznam oddelený čiarkami za názvom procedúry. Povedzme, že máme dve hodnoty (ako v predchádzajúcom príklade). Tieto hodnoty sa zhromažďujú pomocou premenných parametrov procedúry @State a @City. Pri tomto spôsobe odovzdávania parametrov je dôležité poradie. Táto metóda sa nazýva odovzdávanie radových argumentov. Pri druhom spôsobe sú už parametre priamo priradené, poradie v tomto prípade nie je dôležité. Táto druhá metóda je známa ako odovzdávanie pomenovaných argumentov.
Postup sa môže trochu líšiť od bežného. Všetko je rovnaké ako v predchádzajúcom príklade, ale tu sú parametre posunuté. To znamená, že parameter @City sa uloží ako prvý a @State sa uloží vedľa predvolenej hodnoty. Predvolený parameter je zvyčajne zvýraznený samostatne. Uložené procedúry SQL sa odovzdávajú len ako parametre. V tomto prípade za predpokladu, že parameter "UT" nahradí predvolenú hodnotu "CA". Pri druhom spustení sa odovzdá iba jedna hodnota argumentu pre parameter @City a parameter @State nadobudne predvolenú hodnotu "CA". Skúsení programátori odporúčajú, aby sa všetky predvolené premenné umiestnili na koniec zoznamu parametrov. V opačnom prípade vykonanie nie je možné a potom musíte pracovať s odovzdávaním pomenovaných argumentov, čo je dlhšie a náročnejšie.
4. Uložené procedúry SQL Servera: Spôsoby návratu
Existujú tri dôležité spôsoby odosielania údajov vo volanej uloženej procedúre. Sú uvedené nižšie:
Vrátenie hodnoty uloženej procedúry;
Výstupný parameter uložených procedúr;
Vyberte jednu z uložených procedúr.
4.1 Vracanie hodnôt z uložených procedúr SQL
V tejto technike procedúra priradí hodnotu lokálnej premennej a vráti ju. Procedúra môže tiež priamo vrátiť konštantnú hodnotu. V nasledujúcom príklade sme vytvorili procedúru, ktorá sa vracia celkový počet autorov. Ak porovnáte tento postup s predchádzajúcimi, môžete vidieť, že tlačiteľná hodnota je obrátená.
Teraz sa pozrime, ako vykonať procedúru a vytlačiť hodnotu, ktorú vráti. Vykonanie procedúry vyžaduje nastavenie premennej a tlač, ktorá sa vykonáva po celom tomto procese. Všimnite si, že namiesto tlačového príkazu môžete použiť príkaz Select, napríklad Select @RetValue, ako aj OutputValue.
4.2 Koniec parametra uloženej procedúry SQL
Návratová hodnota môže byť použitá na vrátenie jednej premennej, ako sme videli v predchádzajúcom príklade. Použitie parametra Output umožňuje procedúre odoslať jednu alebo viac premenných hodnôt volajúcemu. Výstupný parameter je pri vytváraní procedúry určený práve týmto kľúčovým slovom „Output“. Ak je parameter zadaný ako výstupný parameter, potom mu musí objekt procedúry priradiť hodnotu. Uložené procedúry SQL, ktorých príklady môžete vidieť nižšie, sa potom vrátia so súhrnnými informáciami.
V našom príklade budú dva názvy výstupov: @TotalAuthors a @TotalNoContract. Sú uvedené v zozname parametrov. Tieto premenné priraďujú hodnoty v rámci tela procedúry. Keď použijeme výstupné parametre, volajúci môže vidieť hodnotu nastavenú v tele procedúry.
V predchádzajúcom skripte sú tiež deklarované dve premenné na zobrazenie hodnôt, ktoré MS SQL Server uložené procedúry nastavili vo výstupnom parametri. Postup sa potom vykoná zadaním normálnej hodnoty parametra "CA". Nasledujúce parametre sú výstupom, a preto sa deklarované premenné odovzdávajú v určenom poradí. Všimnite si, že pri odovzdávaní premenných sa výstup kľúčové slovo uvedené aj tu. Po úspešnom dokončení postupu sa v okne so správou zobrazia hodnoty vrátené výstupnými parametrami.
4.3 Výber jednej z uložených procedúr SQL
Táto technika sa používa na vrátenie množiny hodnôt vo forme dátovej tabuľky (RecordSet) do volajúcej uloženej procedúry. V tomto príklade uložená procedúra SQL s parametrami @AuthID dotazuje tabuľku Autori filtrovaním záznamov vrátených pomocou tohto parametra @AuthId. Príkaz Select rozhoduje o tom, čo sa má vrátiť volajúcemu uloženej procedúry. Po vykonaní uloženej procedúry sa AuthId vráti späť. Takýto postup tu vždy vráti iba jeden záznam alebo žiadny. Uložená procedúra však nemá žiadne obmedzenia na vrátenie viac ako jedného záznamu. Nie je nezvyčajné vidieť príklady, kde dochádza k vráteniu údajov pomocou vybraných parametrov zahŕňajúcich vypočítané premenné poskytnutím viacerých súčtov.
Konečne
Uložená procedúra je pomerne vážna programová jednotka, ktorá vracia alebo prechádza a nastavuje potrebné premenné vďaka klientskej aplikácii. Pretože samotná uložená procedúra beží na serveri, dá sa vyhnúť veľkému množstvu výmeny údajov medzi serverom a klientskou aplikáciou (pre niektoré výpočty). Tým sa znižuje zaťaženie SQL server, ktoré samozrejme putujú do rúk ich držiteľov. Jedným z poddruhov sú uložené procedúry T SQL, ale ich štúdium je nevyhnutné pre tých, ktorí vytvárajú pôsobivé databázy. Existuje tiež veľké, dokonca obrovské množstvo nuancií, ktoré môžu byť užitočné pri učení uložených procedúr, ale to je viac potrebné pre tých, ktorí sa plánujú úzko zapojiť do programovania, a to aj profesionálne.
Vyhlásenie o postupe
VYTVORIŤ POSTUP [({IN|OUT|INOUT} [,…])]
[SADA DYNAMICKÝCH VÝSLEDKOV ]
ZAČAŤ [ATOMIC]
KONIEC
Kľúčové slová
. IN (Input) – vstupný parameter
. OUT (Output) – výstupný parameter
. INOUT - vstup a výstup, ako aj pole (bez parametrov)
. DYNAMIC RESULT SET označuje, že procedúra môže otvoriť určený počet kurzorov, ktoré zostanú otvorené po návrate procedúry
Poznámky
Neodporúča sa používať veľa parametrov v uložených procedúrach (predovšetkým veľké čísla a reťazce znakov) kvôli preťaženiu siete a zásobníka. V praxi existujúce dialekty Transact-SQL, PL/SQL a Informix vykazujú významné odchýlky od štandardu, a to ako v deklarácii parametrov, tak aj v používaní, deklarácii premenných a vyvolaní podprogramu. Spoločnosť Microsoft odporúča použiť nasledujúcu aproximáciu na odhad veľkosti vyrovnávacej pamäte uložených procedúr:
=(maximálny počet súbežných používateľov)*(najväčšia veľkosť plánu vykonávania)*1,25. Určenie veľkosti vykonávacieho plánu na stránkach je možné vykonať pomocou príkazu: DBCC MEMUSAGE.
Výzva postupu
V mnohých existujúcich DBMS sa uložené procedúry volajú pomocou príkazu:
VYKONAŤ POSTUP [(][)]
Poznámka: Volanie uložených procedúr môže byť uskutočnené z aplikácie, inej uloženej procedúry alebo interaktívne.
Príklad deklarácie postupu
VYTVORIŤ POSTUP Proc1 AS //vyhlásenie postupu
DECLARE Cur1 CURSOR FOR SELECT Name, City FROM SalesPeople WHERE Hodnotenie>200 //deklarácia kurzora
OPEN Cur1 //otvorenie kurzora
FETCH NEXT FROM Cur1 //prečítanie údajov z kurzora
WHILE @@Fetch_Status=0
ZAČAŤ
NAČÍTAJTE ĎALEJ Z Cur1
KONIEC
CLOSE Cur1 //zatvorenie kurzora
DEALOCATE Cur1
EXECUTE Proc1 //spustite procedúru
Polymorfizmus
Dva podprogramy s rovnakým názvom môžu byť vytvorené v rovnakej schéme, ak sú parametre týchto dvoch podprogramov navzájom dostatočne odlišné, aby sa dali rozlíšiť. Aby bolo možné rozlíšiť medzi dvoma rutinami s rovnakým názvom v rovnakej schéme, každá dostane alternatívny a jedinečný názov (špecifický názov). Takýto názov môže byť explicitne špecifikovaný pri definovaní podprogramu. Pri volaní podprogramov s niekoľkými rovnakými názvami sa určenie požadovaného podprogramu vykonáva v niekoľkých krokoch:
. Na začiatku sú definované všetky procedúry so zadaným názvom a ak neexistujú žiadne, tak všetky funkcie so zadaným názvom.
. Pre ďalšiu analýzu sú ponechané len tie podprogramy, vo vzťahu ku ktorým daný používateľ má privilégium EXECUTE.
. Pre ne sa vyberú tie, ktorých počet parametrov zodpovedá počtu volacích argumentov. Kontrolujú sa špecifikované dátové typy parametrov a ich polohy.
. Ak zostáva viac ako jeden podprogram, vyberie sa ten s kratším kvalifikačným názvom.
V praxi Oracle podporuje polymorfizmus pre funkcie deklarované iba v balíku, [e-mail chránený]- v rôznych schémach a preťažovanie je v Sybase a MS SQL Server zakázané.
Odstránenie a zmena postupov
Na odstránenie procedúry sa používa nasledujúci príkaz:
Ak chcete upraviť postup, použite príkaz:
ZMENIŤ POSTUP [([{IN|OUT|INOUT}])]
ZAČAŤ [ATOMIC]
KONIEC
Oprávnenia vykonávať procedúry
GRANT VYKONAŤ TO |VEREJNOSŤ [S MOŽNOSŤOU GRANTU]
Systémové postupy
Mnoho DBMS (vrátane SQL Server) má určitú sadu vstavaných systémových uložených procedúr, ktoré môžete použiť na svoje vlastné účely.