Domov internet Na čo sa používajú uložené procedúry? uložené procedúry. Príklad vytvorenia procedúry so vstupným parametrom

Na čo sa používajú uložené procedúry? uložené procedúry. Príklad vytvorenia procedúry so vstupným parametrom

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é vrátia 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.

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 uložených procedúr, čím komplikujete 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

Po vytvorení uloženej procedúry ju môžete kedykoľvek zavolať, č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 jazyk SQL alebo jeho konkrétnu implementáciu 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 ľubovoľnom jazyku .NET a v budúcnosti sa plánuje opustenie rozšírených uložených procedúr. Oracle DBMS zase umožňuje písanie uložených procedúr v jazyku 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álnym SQL rozšírenie bol do tohto DBMS pridaný až v dosť neskorých verziách, 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. Takto vám Oracle umožňuje oddeliť rozhranie programový kód od jeho realizá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äť. Zmeny sú preto 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, preto by ste mali venovať osobitnú pozornosť minimalizácii konfliktov 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.

Uložená procedúra uložená procedúra) je pomenovaný objekt databázového programu. SQL Server má niekoľko typov uložených procedúr.

Systémové uložené procedúry uložená procedúra systému) dodávajú vývojári DBMS a používajú sa na vykonávanie akcií v systémovom katalógu alebo na získanie systémových informácií. Ich mená zvyčajne začínajú predponou "sp_". Uložené procedúry všetkých typov sa spúšťajú pomocou príkazu EXECUTE, ktorý je možné skrátiť na EXEC. Napríklad uložená procedúra sp_helplogins, spustená bez parametrov, generuje dve zostavy názvov účtov (Angličtina) prihlásenia) a ich zodpovedajúcich používateľov v každej databáze (Angličtina) používatelia).

EXEC sp_helplogins;

Pre predstavu o akciách vykonaných pomocou systémových uložených procedúr v tabuľke. 10.6 uvádza niekoľko príkladov. Celkovo je v SQL Serveri viac ako tisíc systémových uložených procedúr.

Tabuľka 10.6

Príklady uložených procedúr systému SQL Server

Užívateľ môže vytvárať uložené procedúry v užívateľských databázach a v databáze pre dočasné objekty. V druhom prípade by bola uložená procedúra časový. Rovnako ako pri dočasných tabuľkách, názov dočasne uloženej procedúry musí začínať predponou "#", ak ide o lokálnu dočasne uloženú procedúru, alebo "##", ak ide o globálnu procedúru. Lokálnu dočasnú procedúru je možné použiť len v rámci spojenia, v ktorom bola vytvorená, globálnu možno použiť aj v rámci iných spojení.

Programovateľné objekty SQL Server môžu byť vytvorené buď pomocou nástrojov Transact-SQL alebo pomocou zostáv (Angličtina) Assembly) v prostredí CRL (Common Language Runtime) prostredia Microsoft .Net Framework. V tomto návode sa bude brať do úvahy iba prvá metóda.

Na vytvorenie uložených procedúr použite príkaz CREATE PROCEDURE (môže byť skrátený na PROC), ktorého formát je uvedený nižšie:

VYTVORIŤ (POSTUP PROC I) názov_proc [ ; číslo]

[(gparameter data_type )

[“predvolené] |

[S [ ,...n ] ]

[ NA REPLIKÁCIU ]

AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )

Ak je uložená procedúra (alebo spúšťač, funkcia, pohľad) vytvorená s voľbou ENCRYPTION, jej kód sa skonvertuje tak, že text sa stane nečitateľným. Zároveň, ako je uvedené v , použitý algoritmus je prevzatý zo starších verzií servera SQL Server a nemožno ho považovať za spoľahlivý ochranný algoritmus - existujú nástroje, ktoré vám umožňujú rýchlo vykonať spätnú konverziu.

Voľba PREKOMPILOVAŤ určuje, že pri každom volaní procedúry systém prekompiluje text. V obvyklom prípade je postup zostavený pri prvom spustení uložený vo vyrovnávacej pamäti, čo umožňuje zvýšiť výkon.

EXECUTE AS definuje bezpečnostný kontext, v ktorom sa má procedúra vykonať. Ďalej jedna z hodnôt f CALLER | SEBA | VLASTNÍK | "používateľské_meno"). CALLER je predvolená hodnota a znamená, že kód bude vykonaný v bezpečnostnom kontexte užívateľa volajúceho tento modul. V súlade s tým musí mať používateľ povolenia nielen pre samotný programovateľný objekt, ale aj pre ostatné databázové objekty, ktoré sú ním ovplyvnené. VYKONAŤ SAMO znamená použitie kontextu používateľa, ktorý vytvára alebo upravuje programovateľný objekt. OWNER určuje, že kód bude vykonaný v kontexte aktuálneho vlastníka procedúry. Ak preň nie je definovaný vlastník, predpokladá sa vlastník schémy, do ktorej patrí. EXECUTE AS "user_name" vám umožňuje explicitne špecifikovať používateľské meno (v jednoduchých úvodzovkách).

Pre postup je možné zadať parametre. Toto sú lokálne premenné používané na odovzdávanie hodnôt do procedúry. Ak je parameter deklarovaný s kľúčové slovo OUTPUT (alebo skrátene OUT), je to výstup: hodnotu, ktorá mu bola pridelená v procedúre po jej dokončení, môže použiť program, ktorý procedúru vyvolal. Kľúčové slovo READONLY znamená, že hodnotu parametra nemožno v uloženej procedúre zmeniť.

Parametrom je možné priradiť predvolené hodnoty, ktoré sa použijú, ak pri volaní procedúry nie je explicitne špecifikovaná hodnota parametra. Zvážte príklad:

CREATE PROC surma (@a int, @b int=0,

©result int OUTPUT) AS

SET @výsledok=0a+0b

Vytvorili sme procedúru s tromi parametrami, kde parameter @b má predvolenú hodnotu 0 a parameter @result je výstupným parametrom: prostredníctvom neho sa hodnota vracia volajúcemu programu. Vykonané akcie sú pomerne jednoduché - výstupný parameter dostane hodnotu súčtu dvoch vstupov.

Pri práci v SQL správa serverov Uložená procedúra vytvorená štúdiom sa nachádza v sekcii programovateľné objekty DB (Angličtina) Programovateľnosť) v časti pre uložené procedúry (obrázok 10.2).

Pri volaní procedúry možno ako vstupné parametre použiť premenné aj konštanty. Uvažujme o dvoch príkladoch. V prvom sú vstupné parametre procedúry explicitne nastavené konštantami, pre výstupný parameter vo volaní je uvedené kľúčové slovo OUTPUT. V druhej možnosti je hodnota premennej použitá ako prvý vstupný parameter a druhý parameter je špecifikovaný pomocou kľúčového slova DEFAULT, že by sa mala použiť predvolená hodnota:

Ryža. 10.2.

DECLARE @with int;

EXEC summa 10,5,@c VÝSTUP;

PRINT0c; - Zobrazí sa 15

DECLARE Gi int = 5;

- pri volaní použiť predvolenú hodnotu

EXEC summa Gi,DEFAULT , 0s OUTPUT;

PRINT0c; - Zobrazí sa 5

Zvážte teraz príklad s analýzou návratového kódu, ktorým sa procedúra končí. Nech je potrebné vypočítať, koľko kníh v tabuľke Bookl vyšlo v danom rozmedzí rokov. V tomto prípade, ak je počiatočný rok väčší ako koncový rok, procedúra vráti „1“ a nepočíta sa, inak spočítame počet kníh a vrátime 0:

CREATE PROC dbo.rownum(0FirsYear int, GLastYear int, 0result int OUTPUT) AS

AK 0PrvýRok>0MinulýRok NÁVRAT 1

SET @výsledok= (VYBERTE POČET (*) Z dbo.Bookl

KDE MEDZI 0Prvým rokom A 0Minulým rokom);

Zvážte variant volania tejto procedúry, v ktorom je návratový kód uložený v celočíselnej premennej 0ret, po ktorej sa analyzuje jeho hodnota (v tomto prípade to bude 1). používané v operátorovi Funkcia PRINT CAST sa používa na prevod hodnoty celočíselnej premennej Gres na typ reťazca:

VYHLÁSIŤ 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT "Počiatočný rok je väčší ako koncový rok"

VYTLAČIŤ "Počet kníh "+ CAST(Gres ako varchar(20))

Uložené procedúry dokážu nielen čítať údaje z tabuľky, ale aj upravovať údaje a dokonca vytvárať tabuľky a množstvo ďalších databázových objektov.

Schémy, funkcie, spúšťače, procedúry a zobrazenia však nemožno vytvoriť z uloženej procedúry.

Nasledujúci príklad ilustruje tieto možnosti a problémy súvisiace s rozsahom dočasných objektov. Nasledujúca uložená procedúra kontroluje existenciu dočasnej tabuľky #Tab2; ak táto tabuľka neexistuje, vytvorí ju. Potom sa hodnoty dvoch stĺpcov zadajú do tabuľky #Tab2 a obsah tabuľky sa zobrazí príkazom SELECT:

CREATE PROC My_Procl (@id int, @name varchar(30))

IF OBJECT_ID("tempdb.dbo.#Tab21) JE NULL

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

VYBERTE * Z dbo. #Tab2 -#1

Pred prvým volaním uloženej procedúry si vytvorte dočasnú tabuľku #Tab2, ktorá sa v nej používa. Venujte pozornosť operátorovi EXEC. V predchádzajúcich príkladoch boli parametre odovzdané procedúre "podľa pozície", ale v tomto prípade sa používa iný formát na odovzdávanie parametrov - "podľa názvu", názov parametra a jeho hodnota sú výslovne uvedené:

CREATE TABLE dbo.#Tab2 (id int, názov varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; –#2

Vo vyššie uvedenom príklade sa príkaz SELECT vykoná dvakrát: prvýkrát - vo vnútri procedúry, druhýkrát - z fragmentu volajúceho kódu (označeného komentárom "č. 2").

Pred druhým volaním procedúry vymažeme dočasnú tabuľku #Tab2. Potom sa z uloženej procedúry vytvorí dočasná tabuľka s rovnakým názvom:

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo.#Tab2; –#2

V tomto prípade iba príkaz SELECT v procedúre (s komentárom "Xa 1") zobrazí údaje. Vykonanie SELECT "#2" bude mať za následok chybu, pretože dočasná tabuľka vytvorená v uloženej procedúre už bude vymazaná z databázy tempdb, keď sa procedúra vráti.

Uloženú procedúru môžete zrušiť pomocou príkazu DROP PROCEDURE. Jeho formát je uvedený nižšie. Pomocou jedného príkazu môžete odstrániť niekoľko uložených procedúr tak, že ich uvediete oddelené čiarkami:

ZAPNÚŤ (POSTUP PROC I) ( postup ) [

Napríklad odstránime predtým vytvorenú procedúru summa:

DROP PROC summa;

Môžete vykonať zmeny v existujúcej procedúre (v skutočnosti ju prepísať) pomocou príkazu ALTER PROCEDURE (povoliť

skratka PROC). S výnimkou kľúčového slova ALTER je formát príkazu takmer rovnaký ako formát CREATE PROCEDURE. Zmeňme napríklad postup dbo. rownum nastavením na vykonanie v kontexte zabezpečenia vlastníka:

ALTER PROC dbo.rownum(SFirsYear int,

SLastYear int, Sresult int OUTPUT)

S VYKONANÍM AKO vlastníka - možnosť nastavenia

AK 0PrvýRok>0MinulýRok NÁVRAT 1 ELŠIE ZAČNITE

SET 0výsledok= (VYBERTE POČET(*) Z dbo.Bookl

KDE MEDZI SFirsYear A SLastYear);

V niektorých prípadoch môže byť potrebné dynamicky vygenerovať príkaz a vykonať ho na databázovom serveri. Túto úlohu je možné vyriešiť aj pomocou operátora EXEC. Nasledujúci príklad vyberá záznamy z tabuľky Bookl na základe podmienky, že atribút Year sa rovná hodnote špecifikovanej premennou:

DECLARE 0y int = 2000;

EXEC("SELECT * FROM dbo.Bookl WHERE = " [e-mail chránený]) ;

Vykonávanie dynamicky generovaných inštrukcií vytvára predpoklady na implementáciu počítačových útokov, ako je "SQL injection" (Angličtina) SQL injekcia). Podstatou útoku je, že páchateľ vloží svoj vlastný SQL kód do dynamicky generovaného dotazu. Zvyčajne sa to stane, keď sa z výsledkov používateľského vstupu prevezmú vložené parametre.

Trochu zmeníme predchádzajúci príklad:

DECLARE 0y varchar(100);

SET 0y="2OOO"; - toto sme dostali od používateľa

Ak predpokladáme, že sme od používateľa dostali hodnotu reťazca priradenú v príkaze SET (nech akokoľvek, napríklad cez webovú aplikáciu), tak príklad ilustruje „bežné“ správanie nášho kódu.

DECLARE 0y varchar(100);

SET 0y="2000; DELETE FROM dbo.Book2"; - injekcia

EXEC("SELECT * FROM dbo.Book2 WHERE="+0y);

V ňom sa odporúča, ak je to možné, použiť v takýchto prípadoch systémovú uloženú procedúru sp_executcsql, ktorá umožňuje kontrolovať typ parametrov, čo je jednou z prekážok SQL injekcia. Bez toho, aby sme podrobne zvážili jeho formát, analyzujeme príklad podobný tomu, ktorý bol uvedený vyššie:

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y",

Toto explicitne špecifikuje typ parametra použitého v dotaze a SQL Server ho bude kontrolovať počas vykonávania. Písmeno "N" pred úvodzovkami znamená, že ide o doslovnú konštantu Unicode, ako to vyžaduje postup. Parameter môže byť priradená nielen konštantná hodnota, ale aj hodnota inej premennej.

Definuje sa pojem uložených procedúr. Uvádzajú sa príklady vytvárania, úpravy a používania uložených procedúr s parametrami. Uvedená je definícia vstupných a výstupných parametrov. Uvádzajú sa príklady vytvárania a volania uložených procedúr.

Koncept uloženej procedúry

Uložené procedúry sú skupiny vzájomne prepojených SQL príkazy, ktorého použitie uľahčuje a spružňuje prácu programátora, keďže vykonávať uložená procedúra je často oveľa jednoduchšia ako postupnosť jednotlivých príkazov SQL. Uložené procedúry sú množinou príkazov, ktoré pozostávajú z jedného alebo viacerých SQL príkazov alebo funkcií a sú uložené v databáze v kompilovanej forme. Spustenie v databáze uložené procedúry Namiesto jednotlivých príkazov SQL poskytuje používateľovi nasledujúce výhody:

potrební operátori sú už v databáze;
všetci prešli javiskom parsovanie a sú v spustiteľnom formáte; predtým vykonanie uloženej procedúry SQL Server preň vygeneruje plán vykonávania, optimalizuje ho a skompiluje;
uložené procedúry podpora modulárne programovanie, pretože vám umožňujú rozdeliť veľké úlohy na samostatné, menšie a ľahko spravovateľné časti;
uložené procedúry môže spôsobiť iným uložené procedúry a funkcie;
uložené procedúry možno volať z iných typov aplikačných programov;
zvyčajne, uložené procedúry sú vykonávané rýchlejšie ako postupnosť jednotlivých príkazov;
uložené procedúry jednoduchšie použitie: môžu pozostávať z desiatok a stoviek príkazov, ale na ich spustenie stačí zadať len názov požadovaného uložená procedúra. To vám umožňuje znížiť veľkosť požiadavky odoslanej z klienta na server, a tým aj zaťaženie siete.

Ukladanie procedúr na rovnaké miesto, kde sa vykonávajú, znižuje množstvo dát prenášaných cez sieť a zlepšuje celkový výkon systému. Aplikácia uložené procedúry zjednodušuje údržbu softvérové systémy a vykonávať v nich zmeny. Zvyčajne sú všetky obmedzenia integrity vo forme pravidiel a algoritmov spracovania údajov implementované na databázovom serveri a sú dostupné koncovej aplikácii ako súbor. uložené procedúry, ktoré predstavujú rozhranie na spracovanie údajov. Aby sa zabezpečila integrita údajov, ako aj z bezpečnostných dôvodov, aplikácia zvyčajne nezíska priamy prístup k údajom - všetka práca s nimi prebieha volaním jedného alebo druhého uložené procedúry.

Tento prístup veľmi uľahčuje úpravu algoritmov spracovania údajov, ktoré sú okamžite dostupné pre všetkých používateľov siete, a poskytuje možnosť rozšíriť systém bez vykonania zmien v samotnej aplikácii: stačí zmeniť uložená procedúra na databázovom serveri. Vývojár nemusí aplikáciu prekompilovať, vytvárať jej kópie a tiež poučovať používateľov o potrebe práce s novou verziou. Používatelia si možno ani neuvedomujú, že v systéme boli vykonané zmeny.

Uložené procedúry existujú nezávisle od tabuliek alebo iných databázových objektov. Volá ich klientsky program, iný uložená procedúra alebo spúšťač. Vývojár môže spravovať prístupové práva k uložená procedúra, ktorým sa povolí alebo zakáže jeho vykonanie. Zmeňte kód uložená procedúra povolené iba jej vlastníkom alebo členom pevnej databázy. V prípade potreby môžete previesť jeho vlastníctvo z jedného používateľa na druhého.

Uložené procedúry v prostredí MS SQL Server

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. Priamy hovor uložená procedúra je možné iba vtedy, ak sa vykonáva v kontexte databázy, v ktorej sa postup nachádza.

Typy uložených procedúr

SQL Server má niekoľko typov uložené procedúry.

Systémové uložené procedúry určené na vykonávanie rôznych administratívnych úkonov. Takmer všetky akcie správy servera sa vykonávajú s ich pomocou. Môžeme povedať, že systém uložené procedúry sú rozhranie, 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 uží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 vykonávať určité akcie. Uložené procedúry- kompletný databázový objekt. V dôsledku toho každý uložená procedúra sa nachádza v konkrétnej databáze, kde sa vykonáva.
Dočasné uložené procedúry existujú len krátky čas, po ktorom ich server automaticky zničí. Delia sa na lokálne a globálne. Miestne dočasné uložené procedúry možno volať len zo spojenia, v ktorom sú vytvorené. Pri vytváraní takejto procedúry musí dostať názov, ktorý začína jedným znakom #. Ako všetky dočasné predmety, uložené procedúry tohto typu sa automaticky vymažú, keď používateľ odpojí, reštartuje alebo zastaví server. Globálne dočasné uložené procedúry dostupné pre všetky serverové pripojenia, ktoré majú rovnaký postup. 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é.

Vytváranie, úprava a odstraňovanie uložených procedúr

Tvorba uložená procedúra zahŕňa riešenie nasledujúcich úloh:

definovanie typu uložená procedúra: dočasné alebo vlastné. Okrem toho si môžete vytvoriť svoj vlastný systém uložená procedúra, pričom mu dáte názov s predponou sp_ a umiestnite ho do systémová základňaúdajov. Takýto postup bude dostupný v kontexte akejkoľvek databázy na lokálnom serveri;
plánovanie prístupu. Pri tvorbe uložená procedúra majte na pamäti, že bude mať rovnaké prístupové práva k databázovým objektom ako používateľ, ktorý ho vytvoril;
definícia parametre uloženej procedúry. Rovnako ako postupy zahrnuté vo väčšine programovacích jazykov, uložené procedúry môže mať vstupné a výstupné parametre;
vývoj kódu uložená procedúra. Kód procedúry môže obsahovať sekvenciu ľubovoľných príkazov SQL vrátane volania iných. uložené procedúry.

Vytvorenie nového a úprava existujúceho uložená procedúra sa vykonáva pomocou nasledujúceho príkazu:

<определение_процедуры>::= (CREATE | ALTER ) PROC názov_procedúry [;číslo] [(@názov_parametra typ údajov ) [=predvolené] ][,...n] AS príkaz sql [...n]

Zvážte parametre tohto príkazu.

Pomocou predpôn sp_ , # , ## môže byť vytvorená procedúra definovaná ako systémová alebo dočasná. Ako je zrejmé zo syntaxe príkazu, nie je dovolené zadať meno vlastníka, ktorému bude vytvorená procedúra patriť, ako aj názov databázy, kde má byť umiestnená. Teda s cieľom vyhovieť vytvorenému uložená procedúra v konkrétnej databáze musíte spustiť príkaz CREATE PROCEDURE v kontexte tejto databázy. Pri manipulácii z tela uložená procedúra Skrátené názvy možno použiť pre objekty v rovnakej databáze, t.j. bez zadania názvu databázy. Ak chcete odkazovať na objekty nachádzajúce sa v iných databázach, je potrebné zadať názov databázy.

Číslo v názve je identifikačné číslo uložená procedúra, ktorý ho jednoznačne definuje v skupine postupov. Pre pohodlie riadenia procedúr logicky rovnakého typu uložené procedúry môžu byť zoskupené tak, že im dáte rovnaký názov, ale rôzne identifikačné čísla.

Ak chcete odovzdať vstupné a výstupné údaje vo vytvorenom uložená procedúra možno použiť parametre, ktorých názvy, podobne ako názvy lokálnych premenných, musia začínať symbolom @. Jeden uložená procedúra Môžete zadať viacero možností oddelených čiarkami. Telo procedúry nesmie používať lokálne premenné, ktorých názvy sú rovnaké ako názvy parametrov procedúry.

Na určenie typu údajov, ktoré zodpovedajú parameter uloženej procedúry, vhodný je akýkoľvek typ SQL dáta vrátane užívateľom definovaných. Dátový typ CURSOR je však možné použiť len ako výstupný parameter uložená procedúra, t.j. s kľúčovým slovom VÝSTUP .

Prítomnosť kľúčového slova OUTPUT znamená, že príslušný parameter je určený na vrátenie údajov z uložená procedúra. To však vôbec neznamená, že parameter nie je vhodný na odovzdávanie hodnôt uložená procedúra. Zadanie kľúčového slova OUTPUT dáva serveru pokyn na ukončenie uložená procedúra priraďte aktuálnu hodnotu parametra lokálnej premennej, ktorá bola zadaná pri volaní procedúry ako hodnota parametra. Všimnite si, že pri zadávaní kľúčového slova OUTPUT je možné hodnotu zodpovedajúceho parametra pri volaní procedúry nastaviť len pomocou lokálnej premennej. Akékoľvek výrazy alebo konštanty povolené pre normálne parametre nie sú povolené.

Kľúčové slovo VARYING sa používa v spojení s parametrom OUTPUT, ktorý je typu CURSOR . To definuje výstupný parameter bude výsledný súbor.

Kľúčové slovo DEFAULT je hodnota, ktorej zodpovedá predvolené nastavenie. Preto pri volaní procedúry nemôžete explicitne špecifikovať hodnotu zodpovedajúceho parametra.

Keďže server ukladá do vyrovnávacej pamäte plán vykonávania dotazu a skompilovaný kód, pri ďalšom volaní procedúry sa použijú už pripravené hodnoty. V niektorých prípadoch je však stále potrebné prekompilovať kód procedúry. Zadanie kľúčového slova RECOMPILE inštruuje systém, aby vytvoril plán vykonávania uložená procedúra zakaždým, keď sa volá.

Parameter FOR REPLICATION sa vyžaduje pri replikácii údajov a zahrnutí vytvorených údajov uložená procedúra ako článok v publikácii.

Kľúčové slovo ENCRYPTION dáva serveru pokyn na zašifrovanie kódu uložená procedúra, ktorý môže poskytnúť ochranu pred použitím algoritmov autorských práv, ktoré implementujú dielo uložená procedúra.

Kľúčové slovo AS je umiestnené na začiatku skutočného tela uložená procedúra, t.j. súbor SQL príkazov, pomocou ktorých sa bude realizovať tá či oná akcia. V tele procedúry je možné použiť takmer všetky SQL príkazy, deklarovať transakcie, nastavovať zámky a volať ďalšie. uložené procedúry. výstup z uložená procedúra možno vykonať pomocou príkazu RETURN.

Odstránenie uloženej procedúry vykonávané príkazom:

ZAKÁZAŤ PROCEDURE (názov_procedúry) [,...n]

Vykonanie uloženej procedúry

Pre vykonanie uloženej procedúry používa sa príkaz:

[[ EXEC [ UTE] názov_procedúry [;číslo] [[@názov_parametra=](hodnota | @názov_premennej) |][,...n]

Ak hovor uložená procedúra nie je jediným príkazom v balíku, potom je prítomnosť príkazu EXECUTE povinná. Okrem toho je tento príkaz potrebný na volanie procedúry z tela inej procedúry alebo spúšťača.

Použitie kľúčového slova OUTPUT pri volaní procedúry je povolené len pre parametre, ktoré boli deklarované kedy vytvorenie postupu s kľúčovým slovom OUTPUT.

Keď je pre volanie procedúry zadané kľúčové slovo DEFAULT, použije sa kľúčové slovo DEFAULT. predvolená hodnota. Prirodzene, špecifikované slovo DEFAULT je povolené len pre tie parametre, pre ktoré je definované predvolená hodnota.

Zo syntaxe príkazu EXECUTE môžete vidieť, že názvy parametrov možno pri volaní procedúry vynechať. V tomto prípade však musí používateľ zadať hodnoty parametrov v rovnakom poradí, v akom boli kedy uvedené vytvorenie postupu. Priradiť k parametru predvolená hodnota, jednoducho ho preskočiť, keď nie je možné vymenovať. Ak je potrebné vynechať parametre, pre ktoré je predvolená hodnota, stačí pri volaní explicitne špecifikovať názvy parametrov uložená procedúra. Navyše týmto spôsobom môžete uviesť parametre a ich hodnoty v ľubovoľnom poradí.

Všimnite si, že pri volaní procedúry sú zadané buď názvy parametrov s hodnotami, alebo iba hodnoty bez názvu parametra. Ich kombinácia nie je povolená.

Príklad 12.1. Postup bez parametrov. Vypracujte postup na získanie názvov a cien tovaru zakúpeného Ivanovom.

CREATE PROC my_proc1 AS SELECT Item.Name, Item.Price*Obchod.Množstvo AS Cost, Customer.LastName FROM Customer INNER JOIN (Item INNER JOIN Obchod ON Item.ItemId=Trade.ItemId) ON Client.CustomerId=Trade.CustomerId WHERE .Priezvisko='Ivanov' Príklad 12.1. Postup pri získavaní názvov a cien tovaru zakúpeného spoločnosťou Ivanov.