Uložené procedúry v databáze SQL. Zmena uloženej procedúry na T-SQL je príkaz ALTER PROCEDURE. Oprávnenia vykonávať procedúry

V spoločnosti Microsoft SQL Server implementovať a automatizovať svoje vlastné algoritmy ( výpočty) môžete použiť uložené procedúry, preto si dnes povieme, ako sa vytvárajú, upravujú a vymazávajú.

Najprv však trochu teórie, aby ste pochopili, čo sú uložené procedúry a na čo slúžia v T-SQL.

Poznámka! Pre začínajúcich programátorov odporúčam nasledujúce užitočné materiály na tému T-SQL:

Pre podrobnejšie štúdium jazyka T-SQL odporúčam prečítať si aj knihu - Cesta programátora T-SQL. Výukový program Transact-SQL.

Čo sú uložené procedúry v T-SQL?

Uložené procedúry- Ide o databázové objekty, v ktorých je algoritmus vložený vo forme sady SQL inštrukcií. Inými slovami, môžeme povedať, že uložené procedúry sú programy v databáze. Uložené procedúry sa používajú na ukladanie opätovne použiteľného kódu na serveri, napríklad ste napísali algoritmus, sekvenčný výpočet alebo viackrokový príkaz SQL, a nie na vykonanie všetkých inštrukcií zahrnutých v každom čase. tento algoritmus, Môžete ho usporiadať ako uloženú procedúru. V tomto prípade, keď vytvoríte procedúru SQL, server skompiluje kód a potom pri každom spustení tejto procedúry SQL Server už ho nebude znova zostavovať.

Pre spustenie uloženej procedúry v SQL Serveri je potrebné pred jej názov napísať príkaz EXECUTE, tento príkaz EXEC je možné napísať aj skrátene. Zavolajte uloženú procedúru napríklad v príkaze SELECT, pretože funkcia už nebude fungovať, t.j. procedúry prebiehajú oddelene.

V uložených procedúrach je už na rozdiel od funkcií možné vykonávať operácie úpravy údajov ako: UNSERT, UPDATE, DELETE. Môžete tiež použiť v postupoch SQL príkazy takmer akýkoľvek typ, napríklad CREATE TABLE na vytváranie tabuliek alebo EXECUTE, t.j. volanie iných postupov. Výnimkou je niekoľko typov inštrukcií, ako napríklad: vytváranie alebo zmena funkcií, pohľadov, spúšťačov, vytváranie schém a niekoľko ďalších podobných inštrukcií, napríklad tiež nie je možné prepnúť kontext pripojenia k databáze (USE) v uloženej procedúre .

Uložená procedúra môže mať vstupné parametre a výstupné parametre, môže vracať tabuľkové dáta, nemusí nič vrátiť, ale iba vykonávať inštrukcie v nej obsiahnuté.

Uložené procedúry sú veľmi užitočné, pomáhajú nám zautomatizovať alebo zjednodušiť mnohé operácie, napríklad neustále potrebujete generovať rôzne komplexné analytické zostavy pomocou kontingenčných tabuliek, t.j. Operátor PIVOT. Na zjednodušenie vytvárania dopytov s týmto operátorom ( ako viete, syntax PIVOT je dosť komplikovaná), môžete napísať procedúru, ktorá vám bude dynamicky generovať súhrnné zostavy, napríklad v materiáli „Dynamický PIVOT v T-SQL“ je uvedený príklad implementácie tejto funkcie vo forme uloženej procedúry.

Príklady práce s uloženými procedúrami v Microsoft SQL Server

Počiatočné údaje pre príklady

Všetky nižšie uvedené príklady budú spustené v Microsoft SQL Server 2016 Express. Aby sme ukázali, ako fungujú uložené procedúry s reálnymi dátami, potrebujeme tieto dáta, poďme si ich vytvoriť. Vytvorme si napríklad testovaciu tabuľku a pridáme do nej nejaké záznamy, povedzme, že to bude tabuľka obsahujúca zoznam produktov s ich cenou.

Príkaz na vytvorenie tabuľky CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Pridať dátový príkaz INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "Myš", 100), (1, "Klávesnica", 200), (2, "Telefón", 400) GO -- SELECT * FROM TestTable dotaz

Dáta sú, teraz prejdime k vytváraniu uložených procedúr.

Vytvorenie uloženej procedúry v T-SQL - príkaz CREATE PROCEDURE

Uložené procedúry sa vytvárajú pomocou príkazu VYTVORIŤ POSTUP, po tomto pokyne musíte napísať názov vašej procedúry, potom, ak je to potrebné, definovať vstupné a výstupné parametre v zátvorkách. Potom napíšete kľúčové slovo AS a otvoríte inštrukčný blok kľúčové slovo ZAČIAT, zavrieť tento blok slovo KONIEC. Do tohto bloku napíšete všetky inštrukcie, ktoré implementujú váš algoritmus alebo nejaký sekvenčný výpočet, inými slovami, programujete v T-SQL.

Napríklad napíšme uloženú procedúru, ktorá bude pridávať nový záznam, t.j. nová položka do našej testovacej tabuľky. Na tento účel zadefinujeme tri vstupné parametre: @CategoryId - ID kategórie produktu, @ProductName - názov produktu a @Price - cena produktu, daný parameter budeme mať nepovinnú, t.j. nemôže byť odovzdaný do konania ( napríklad cenu ešte nevieme), na tento účel nastavíme v jeho definícii predvolenú hodnotu. Tieto parametre sú v tele procedúry, t.j. v bloku BEGIN…END možno použiť rovnakým spôsobom ako bežné premenné ( ako viete, premenné sú označené znakom @). Ak potrebujete zadať výstupné parametre, potom za názvom parametra zadajte kľúčové slovo OUTPUT ( alebo skrátene OUT).

Do bloku BEGIN…END napíšeme inštrukciu na pridávanie údajov a na koniec procedúry aj príkaz SELECT, aby uložená procedúra vrátila tabuľkové údaje o produktoch v zadanej kategórii s prihliadnutím na nové, resp. práve pridaný produkt. Aj v tejto uloženej procedúre som pridal spracovanie prichádzajúceho parametra, konkrétne odstránenie nadbytočných medzier na začiatku a na konci textového reťazca, aby sa predišlo situáciám, keď bolo náhodne zadaných niekoľko medzier.

Tu je kód pre tento postup Aj som sa k tomu vyjadril).

Vytvorte procedúru CREATE PROCEDURE TestProcedure (--Prichádzajúce parametre @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Inštrukcie, ktoré implementujú váš algoritmus --Spracovanie prichádzajúcich parametrov --Odstráňte nadbytočné medzery na začiatku a na konci textového reťazca SET @NázovProduktu = LTRIM(RTRIM(@NázovProduktu)); --Pridať novú položku INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Vráťte údaje SELECT * FROM TestTable WHERE CategoryId = @CategoryId KONIEC

Spustenie uloženej procedúry v T-SQL - príkaz EXECUTE

Ako som už poznamenal, uloženú procedúru môžete spustiť pomocou príkazu EXECUTE alebo EXEC. Prichádzajúce parametre sa odovzdávajú procedúram jednoduchým ich vymenovaním a špecifikovaním. zodpovedajúce hodnoty za názvom procedúry ( pre výstupné parametre musíte zadať aj príkaz OUTPUT). Názov parametrov však nemusí byť uvedený, no v tomto prípade je potrebné dodržať postupnosť uvádzania hodnôt, t.j. zadajte hodnoty v poradí, v akom sú definované vstupné parametre ( to platí aj pre výstupné parametre).

Parametre, ktoré majú predvolené hodnoty, nemusia byť špecifikované, ide o takzvané voliteľné parametre.

Tu je niekoľko rôznych, ale ekvivalentných spôsobov spúšťania uložených procedúr, konkrétne našej testovacej procedúry.

1. Vyvolajte procedúru bez zadania ceny EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test produkt 1" --2. Procedúru s cenou špecifikovanou EXEC TestProcedure nazývame @CategoryId = 1, @ProductName = "Testovací produkt 2", @Price = 300 --3. Procedúru voláme bez zadania názvu parametrov EXEC TestProcedure 1, "Test item 3", 400

Zmena uloženej procedúry na T-SQL - príkaz ALTER PROCEDURE

Pomocou pokynov môžete vykonať zmeny v algoritme postupu ZMENIŤ POSTUP. Inými slovami, ak chcete zmeniť už existujúci postup, stačí napísať ALTER PROCEDURE namiesto CREATE PROCEDURE a podľa potreby zmeniť všetko ostatné.

Povedzme, že potrebujeme vykonať zmeny v našom testovacom postupe, povedzme parameter @Price, t.j. cenu, urobíme to povinné, preto odstránime predvolenú hodnotu a predstavme si, že už nepotrebujeme získavať výsledný súbor údajov, na to jednoducho odstránime príkaz SELECT z uloženej procedúry.

Zmeňte procedúru ALTER PROCEDURE TestProcedure (--Vstupné parametre @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Inštrukcie, ktoré implementujú váš algoritmus --Spracovanie prichádzajúcich parametrov --Odstráňte nadbytočné medzery na začiatku a konci z textových riadkov SET @NázovProduktu = LTRIM(RTRIM(@NázovProduktu)); --Pridať nový záznam INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Vymazanie uloženej procedúry v T-SQL - príkaz DROP PROCEDURE

V prípade potreby môžete uloženú procedúru vymazať, to sa vykonáva pomocou príkazu PROCEDÚRA ODPADU.

Napríklad vymažeme testovaciu procedúru, ktorú sme vytvorili.

PROCEDÚRA PAPNUTIA TestProcedúra

Pri odstraňovaní uložených procedúr je potrebné pamätať na to, že ak na procedúru odkazujú iné procedúry alebo príkazy SQL, po jej odstránení zlyhajú s chybou, pretože procedúra, na ktorú odkazujú, už neexistuje.

Mám všetko, dúfam, že materiál bol pre vás zaujímavý a užitočný, ahoj!

V predchádzajúcom článku tejto série sme sa pozreli na to, ako môžete extrahovať údaje z tabuliek, upravovať ich štruktúru, vytvárať, upravovať a odstraňovať databázy a objekty v nich obsiahnuté. V tomto článku budeme hovoriť podrobnejšie o objektoch špecifických pre server DBMS: zobrazenia, spúšťače a uložené procedúry.

V prvom článku tejto série, uverejnenom v čísle 3'2000 nášho časopisu, sme si všimli, že väčšina moderných DBMS na strane servera podporuje zobrazenia, spúšťače a uložené procedúry. Pohľady podporujú aj mnohé desktopové DBMS, ako napríklad Access, dBase, Clipper.

Treba poznamenať, že spúšťače a uložené procedúry sú zvyčajne napísané v programovacích jazykoch, ktoré sú procedurálnymi rozšíreniami jazyka SQL. Tieto rozšírenia obsahujú príkazy, ktoré vám umožňujú opísať algoritmy, ako napríklad do...while, if...then...else, ktoré nie sú v samotnom jazyku SQL (ak si pamätáte, SQL je neprocedurálny jazyk, a môžete v ňom formulovať úlohu, ale nemôžete opísať algoritmy na jej vykonávanie). Na rozdiel od jazyka SQL, ktorý sa riadi štandardom, jeho procedurálne rozšírenia nie sú žiadnym spôsobom štandardizované a rôzne DBMS používajú rôzne syntaxe na implementáciu rovnakých algoritmických konštruktov, ale diskusia o rozdieloch v syntaxi rozšírení SQL pre rôzne DBMS je nad rámec možností. rozsah tohto článku.

Na ilustráciu toho, ako možno použiť zobrazenia, spúšťače a uložené procedúry, sme si vybrali Microsoft SQL Server 7.0 a databázu NorthWind, ktorá je súčasťou tejto databázy.

Skôr ako budete postupovať podľa príkladov, uvedomte si, že implementácia a ukladanie spúšťačov a uložených procedúr vo vašom DBMS sa môže líšiť od tých v tomto článku. Okrem toho musíte mať príslušné povolenia udelené administrátorom databázy na vytváranie objektov servera.

Všimnite si tiež, že niektoré ovládače ODBC nepodporujú volanie uložených procedúr z klientskych aplikácií, aj keď sú podporované samotným DBMS. V tomto prípade však uložené procedúry možno stále volať zo spúšťačov.

Začnime pohľadmi, potom si rozoberieme uložené procedúry a kapitolu ukončíme prehľadom spúšťačov.

zastupovanie

Pohľad je virtuálna tabuľka, ktorá zvyčajne obsahuje množinu stĺpcov z jednej alebo viacerých tabuliek. Pohľad v skutočnosti neobsahuje údaje, ale iba dotaz SELECT SQL, ktorý presne špecifikuje, aké údaje a z akých tabuliek sa majú vziať pri prístupe k tomuto pohľadu. Z tohto pohľadu je pohľad uloženým dotazom.

Vo väčšine prípadov sa zobrazenia používajú na zabezpečenie údajov. Napríklad niektoré kategórie používateľov môžu mať prístup k zobrazeniu, ale nie k tabuľkám, ktorých údaje ho tvoria; okrem toho môže SQL dotaz obsahovať parameter USER (meno, pod ktorým je používateľ prihlásený), v tomto prípade budú údaje dostupné pri prístupe k pohľadu závisieť od používateľského mena.

Hlavné charakteristiky pohľadov sú uvedené nižšie:

pohľady sa správajú ako tabuľky;
zobrazenia neobsahujú žiadne údaje;
zobrazenia môžu používať údaje z viac ako jednej tabuľky.

Na vytvorenie zobrazenia môžeme použiť príkaz CREATE VIEW SQL, na jeho úpravu príkaz ALTER VIEW a na jeho odstránenie pomocou príkazu DROP VIEW.

Začneme príkazom CREATE VIEW, ktorý vám umožňuje vytvoriť pohľad na aktuálnu databázu.

Klauzula CREATE VIEW

Syntax na vytvorenie zobrazenia je podobná príkazu SQL SELECT s niekoľkými kľúčovými slovami navyše. Nižšie je jeho zjednodušená syntax:

CREATE VIEW view_name AS select_statement

Argument názov_zobrazenia ukazuje na názov zobrazenia. Kľúčové slovo používané v Microsoft SQL Server na skrytie zdrojového textu CREATE doložky ZOBRAZIŤ v tabuľke systémových komentárov.

Kľúčové slovo AS určuje, ktorý dotaz SELECT sa skutočne vykoná pri prístupe k zobrazeniu. Upozorňujeme, že tento dotaz nemôže obsahovať kľúčové slová ORDER BY, COMPUTE alebo COMPUTE BY, INTO a nemôže odkazovať na dočasnú tabuľku.

Ak chcete upraviť predtým vytvorené zobrazenie, použite klauzulu ALTER VIEW, ktorá je stručne popísaná v ďalšej časti.

klauzula DROP VIEW

Táto klauzula sa používa na odstránenie pohľadu z databázy. Všimnite si, že keď sa tabuľka odstráni z databázy, odstránia sa aj všetky zobrazenia, ktoré na ňu odkazujú. Pomocou tejto klauzuly musíme zadať názov zobrazenia, ktoré sa má odstrániť. Po zrušení zobrazenia sa všetky informácie o ňom odstránia zo systémových tabuliek.

Ďalším prípadom, kedy je potrebné zobrazenie zrušiť, je situácia, keď sa štruktúra tabuliek, na ktorých je založený, od vytvorenia zobrazenia zmenila. V tomto prípade môžete pohľad odstrániť a potom ho znova vytvoriť pomocou klauzuly CREATE VIEW.

Vytváranie a používanie pohľadov

Klauzula CREATE VIEW sa používa na vytváranie zobrazení, ktoré vám umožňujú získať údaje, ktoré spĺňajú určité požiadavky. Pohľad sa vytvorí v aktuálnej databáze a uloží sa ako samostatný objekt.

Najlepší spôsob, ako vytvoriť zobrazenie, je vytvoriť dotaz SELECT a po jeho overení pridať chýbajúcu časť klauzuly CREATE VIEW. Pozrime sa na zdrojový kód pre zobrazenie Products by Category v databáze NorthWind (Výpis 1).

Prvý riadok tučným písmom je, ako sa príkaz SQL na vytvorenie zobrazenia líši od bežného príkazu SELECT, ktorý vykonáva prácu pri výbere údajov. Klauzula SELECT obsiahnutá v tomto zobrazení vyberá polia z dvoch tabuliek – pole CategoryName z tabuľky CATEGORIES a pole ProductName, QuantityPerUnit, UnitsInStock, Discontinued z tabuľky PRODUCTS. Potom sú údaje dvoch tabuliek prepojené poľom CategoryID a do výsledného súboru údajov sú zahrnuté iba tie produkty, ktoré sú stále na sklade (pozri kritérium za kľúčovým slovom WHERE). Výsledok prístupu k tomuto pohľadu je znázornený na obr. jeden .

Teraz vytvorte pohľad zobrazujúci všetky územia vo východnom regióne. Toto zobrazenie je založené na nasledujúcom dotaze (Výpis 2).

Po uistení, že klauzula SELECT vracia požadované výsledky, pridáme príkaz CREATE VIEW a pomenujeme pohľad, ktorý vytvoríme EASTTERR (Výpis 3).

Namiesto manuálneho vytvárania textu zobrazenia môžete použiť vizuálne nástroje, ktoré sú zvyčajne súčasťou DBMS. Na obr. Obrázok 2 ukazuje, ako je možné vytvoriť rovnaký pohľad pomocou nástroja View Designer, ktorý je súčasťou Enterprise Manager, ktorý je súčasťou Microsoft SQL Server.

Horná časť Návrhára pohľadov vám umožňuje určiť, ako spolu tabuľky súvisia a ktoré polia sa zobrazia v pohľade. Nižšie môžete špecifikovať aliasy tabuliek a polí, obmedzenia ich hodnôt, spôsob zobrazenia. Zdrojový text zobrazenia a výsledky jeho vykonania sú uvedené nižšie.

Než skončíme krátka recenzia pohľadov, povedzme si niečo o tom, ako o nich získať viac informácií. V Microsoft SQL Server 7.0 môžeme použiť nasledujúce systémové uložené procedúry:

Na získanie informácií o zobrazení môžete použiť systémovú uloženú procedúru sp_help. Napríklad sp_help EastTerr vráti informácie o novovytvorenom zobrazení;
na získanie zdrojový kód zobraziť, môžete použiť sp_helptext uloženú procedúru;
na nájdenie zoznamu tabuliek, od ktorých závisí zobrazenie, môžete použiť systémovú uloženú procedúru sp_depends;
Na premenovanie zobrazenia môžete použiť systémovú uloženú procedúru sp_rename.

AT túto sekciu pozreli sme sa na to, ako použiť zobrazenia na získanie údajov, ktoré spĺňajú určité kritériá. Vráťme sa však k poslednému príkladu. V databáze NorthWind sú štyri regióny a na získanie zoznamu území všetkých regiónov potrebujeme štyri rôzne zobrazenia. Túto úlohu by bolo možné zjednodušiť, ak by sme mohli zadať hodnotu RegionID ako parameter. Dá sa to urobiť pomocou uloženej procedúry, o ktorej budeme diskutovať v ďalšej časti.

Uložené procedúry

Uložená procedúra je skompilovaná množina príkazov SQL uložených v databáze ako pomenovaný objekt a vykonávaných ako jeden kus kódu. Uložené procedúry môžu prijímať a vracať parametre. Keď používateľ vytvorí uloženú procedúru, server ju skompiluje a umiestni do zdieľanej vyrovnávacej pamäte, po čom môže skompilovaný kód použiť viacero používateľov. Keď aplikácia používa uloženú procedúru, odovzdá jej parametre, ak nejaké existujú, a server vykoná procedúru bez rekompilácie.

Uložené procedúry zlepšujú výkon aplikácie. Po prvé, v porovnaní s bežnými SQL dotazmi odoslanými z klientskej aplikácie trvá príprava na vykonanie menej času, pretože sú už skompilované a uložené. Po druhé, sieťová prevádzka je v tomto prípade tiež nižšia ako v prípade dotazu SQL, pretože cez sieť sa prenáša menej údajov. Ryža. 3 znázorňuje volanie uloženej procedúry klientskou aplikáciou.

Uložené procedúry sa automaticky prekompilujú, ak sa vykonajú zmeny v objektoch, ktoré ovplyvňujú; inými slovami, sú vždy relevantné. Ako je uvedené vyššie, uložené procedúry môžu mať parametre, čo umožňuje rôznym aplikáciám používať rovnakú procedúru s rôznymi sadami vstupov.

Uložené procedúry sa bežne používajú na udržiavanie referenčnej integrity údajov a presadzovanie obchodných pravidiel. Ten poskytuje dodatočnú flexibilitu, pretože ak sa zmenia obchodné pravidlá, je možné zmeniť iba telo postupu bez zmeny klientskych aplikácií.

Na vytváranie, úpravu a mazanie procedúr existujú špeciálne SQL príkazy – CREATE PROCEDURE, ALTER PROCEDURE a DROP PROCEDURE. Pozrieme sa na ne v ďalšej časti.

klauzula CREATE PROCEDURE

Klauzula CREATE PROCEDURE sa používa na vytvorenie uloženej procedúry. Má nasledujúcu zjednodušenú syntax:

CREATE PROC proc_name [ (@parameter data_type) [= predvolené] ] [...] AS sql_statements

Argument proc_name nastavuje názov uloženej procedúry, ktorý musí byť jedinečný v rámci aktuálnej databázy. Argument @parameter určuje parameter procedúry. V klauzule CREATE PROCEDURE možno zadať jeden alebo viacero parametrov. Ak pre parameter neexistuje žiadna predvolená hodnota, musí ho odovzdať užívateľ (alebo klientska aplikácia) pri volaní procedúry. V Microsoft SQL Server 7.0 je počet parametrov uloženej procedúry obmedzený na 1024; v predvolenom nastavení môžu byť NULL.

Všimnite si však, že niektoré všeobecné mechanizmy prístupu k údajom môžu zaviesť dodatočné obmedzenia na počet parametrov uloženej procedúry. Napríklad ovládač BDE pre Oracle 8 môže pracovať iba s procedúrami s maximálne 10 parametrami.

Argument data_type určuje typ údajov pre parameter. Predvolené kľúčové slovo možno použiť na nastavenie predvolených hodnôt - môže to byť konštanta alebo NULL. Ak je zadaná predvolená hodnota, procedúru možno volať bez zadania hodnoty parametra. Ak procedúra používa parameter s kľúčovým slovom LIKE, jej predvolená hodnota môže obsahovať zástupné znaky (%, _ a [^]).

Kľúčové slovo OUTPUT označuje, že ide o návratový parameter.

Kľúčové slovo AS určuje akciu, ktorú má procedúra vykonať, vo forme ľubovoľného počtu príkazov SQL a príkazov v rozšírení procedúry SQL špecifickej pre server.

Procedúra vytvorená pomocou klauzuly CREATE PROCEDURE bude uložená v aktuálnej databáze. V Microsoft SQL Server sú názvy procedúr uložené v systémovej tabuľke sysobjects a zdrojový text je uložený v tabuľke syscomments.

Ak chcete upraviť predtým vytvorenú uloženú procedúru, použite klauzulu ALTER PROCEDURE, ktorá je stručne popísaná v nasledujúcej časti.

Ponuka DROP PROCEDURE

Táto klauzula sa používa na odstránenie uložených procedúr z databázy. Klauzula DROP PROCEDURE má jeden argument, názov procedúry, ktorá sa má zrušiť.

Po odstránení uloženej procedúry sa jej informácie odstránia zo systémových tabuliek sysobjects a syscomments.

Vytváranie a používanie uložených procedúr

V časti o zobrazeniach sme poznamenali, že by bolo vhodné, keby sme mohli odovzdať parameter do zobrazenia obsahujúceho hodnotu RegionID na výber jednej zo štyroch oblastí v databáze NorthWind. Pozrime sa ešte raz na dotaz, ktorý vráti zoznam území regiónu:

SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1

Ak chcete vybrať inú oblasť, musíme zmeniť podmienku v klauzule WHERE v poslednom riadku dotazu. Ak teda použijeme premennú (nazvime ju RegID), môžeme vybrať jeden zo štyroch regiónov bez toho, aby sme menili ostatné časti dotazu.

V databáze NorthWind sú štyri oblasti očíslované od 1 do 4. To znamená, že premenná RegID musí byť typu celé číslo. Kód uloženej procedúry je uvedený nižšie:

VYTVORIŤ POSTUP ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories VNÚTORNÉ PRIPOJENIE K regiónu ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID

Všimnite si, že takmer celý text dotazu SELECT sme ponechali nedotknutý (kurzívou) a pridali sme iba klauzulu CREATE PROCEDURE s názvom novovytvorenej uloženej procedúry (v prvom riadku), deklaráciu parametra (v druhom riadku) a kľúčové slovo AS označujúce na začiatku viet, ktoré skutočne vykonávajú akcie.

Výsledok vykonania vytvorenej procedúry v SQL Server Query Analyzer pre RegID =2 je znázornený na obr. 3.

Je zrejmé, že uložené procedúry môžeme použiť na viac ako len rozšírené pohľady alebo „inteligentné“ SELECT dotazy. Uložené procedúry poskytujú mechanizmy na automatizáciu mnohých rutinných úloh.

V Microsoft SQL Server 7.0 môžeme tiež použiť systémové uložené procedúry na prácu s bežnými uloženými procedúrami:

sp_stored_procedures – zobrazuje zoznam uložených procedúr.
sp_helptext - zobrazuje zdrojový text uloženej procedúry;
sp_depends – zobrazuje informácie o závislostiach uložených procedúr.
sp_procoption - Nastaví alebo nastaví možnosti uloženej procedúry;
sp_recompile - prekompiluje procedúru v čase jej ďalšieho volania;
sp_rename - Zmení názov procedúry.

Systémové uložené procedúry

Keďže hovoríme o Microsoft SQL Server, je potrebné poznamenať, že je v ňom implementované obrovské množstvo systémových uložených procedúr. Názvy systémových uložených procedúr začínajú SP_ alebo XP_ a sú uložené v hlavnej databáze. Niektoré bežne používané systémové uložené procedúry sme už opísali vyššie.

Upozorňujeme, že spúšťače by nemali používateľovi vracať údaje.

V klauzule CREATE TRIGGER možno použiť dve špeciálne tabuľky. Napríklad odstránené a vložené tabuľky majú rovnakú štruktúru ako tabuľka, na ktorej je definovaný spúšťač a obsahujú staré a nové hodnoty záznamov modifikovaných používateľom. Na nájdenie odstránených záznamov môžeme napríklad použiť nasledujúci príkaz SQL:

SELECT * FROM vymazané

V tabuľke. 3 zobrazuje obsah vymazaných a vložených tabuliek pre všetky možné zmeny údajov.

Ak chcete zmeniť existujúci spúšťač, použite klauzulu ALTER TRIGGER. Povieme si o tom v ďalšej časti.

Najprv musíme do tabuľky pridať dve nové polia, ktoré budú obsahovať tieto informácie. Nazvime ich UpdatedBy (meno manažéra, ktorý naposledy aktualizoval záznam) a UpdatedWhen (čas zmeny záznamu). Ďalej vytvoríme spúšťač s názvom KeepTrack. Tu je jeho kód:

VYTVORIŤ TRIGGER sledovanie pri zákazníkoch PRE VLOŽENIE, AKTUALIZÁCIU AKO AKTUALIZÁCIU NASTAVENIE zákazníkov Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM vložené, Zákazníci, KDE je vložené.CustomerID = Customers.CustomerID

Ako môžete vidieť zo zdrojového kódu spúšťača, spúšťa sa po každej operácii INSERT a UPDATE v tabuľke Zákazníci. Tento spúšťač uloží meno manažéra (používateľa databázy) do poľa Customers.UpdatedBy a dátum a čas zmeny do poľa Customers.UpdatedWhen. Tieto údaje sa získajú z vloženej dočasnej tabuľky.

Ako vidíte, tento spúšťač vám umožňuje sledovať zmeny a vkladať nové záznamy do tabuľky.

Skôr ako skončíme náš krátky prehľad spúšťačov, mali by sme vám povedať, kde nájdete informácie o dostupných spúšťačoch. Tabuľka sysobjects obsahuje informácie o spúšťačoch a ich typoch a tabuľka syscomments obsahuje ich zdrojový kód.

Záver

V tejto časti sme sa pozreli na niekoľko typov databázových objektov – uložené procedúry, pohľady a spúšťače. Dozvedeli sme sa nasledovné:

Pohľad je virtuálna tabuľka, zvyčajne vytvorená ako podmnožina stĺpcov jednej alebo viacerých tabuliek. Klauzula CREATE VIEW sa používa na vytvorenie zobrazenia, klauzula ALTER VIEW sa používa na jeho úpravu a klauzula DROP VIEW na jeho odstránenie.
Uložená procedúra je skompilovaná množina príkazov SQL uložených v databáze ako pomenovaný objekt a vykonávaných ako jeden kus kódu. CREATE PROCEDURE sa používa na vytvorenie uloženej procedúry, ALTER PROCEDURE sa používa na jej úpravu a DROP PROCEDURE sa používa na jej odstránenie.
Spúšťač je špeciálny typ uloženej procedúry, ktorá sa automaticky vyvolá, keď sa údaje v konkrétnej tabuľke pridajú, vymažú alebo upravia pomocou príkazu SQL INSERT, DELETE alebo UPDATE. Spúšťače sa vytvárajú pomocou klauzuly CREATE TRIGGER. Klauzula ALTER TRIGGER sa používa na úpravu spúšťača a klauzula DROP TRIGGER sa používa na jeho odstránenie.

ComputerPress 12"2000

Cieľ– naučiť sa vytvárať a používať uložené procedúry na databázovom serveri.

1. Prepracovanie všetkých príkladov, analýza výsledkov ich vykonania v nástroji SQL Server Management Studio. Kontrola, či vytvorené procedúry existujú v aktuálnej databáze.

2. Splnenie všetkých príkladov a úloh v priebehu laboratórnych prác.

3. Plnenie jednotlivých úloh podľa možností.

Pracovné vysvetlenia

Na zvládnutie programovania uložených procedúr nám slúži príklad databázy tzv DB_Books, ktorý vznikol v laboratórnej práci č.1. Pri vykonávaní príkladov a úloh dávajte pozor na súlad medzi názvami databázy, tabuliek a iných objektov projektu.

Uložené procedúry sú množinou príkazov pozostávajúcich z jedného alebo viacerých SQL príkazov alebo funkcií a uložených v databáze v kompilovanej forme.

Typy 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. Môžeme povedať, že systémové uložené procedúry sú rozhraním, ktoré zabezpečuje prácu so systémovými tabuľkami. 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ýsledkom je, že každá uložená procedúra sa nachádza v špecifickej 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é.

Vytváranie, úprava uložených procedúr

Vytvorenie uloženej procedúry zahŕňa riešenie nasledujúcich úloh: plánovanie prístupových práv. Pri vytváraní uloženej procedúry majte na pamäti, že bude mať rovnaké prístupové práva k databázovým objektom ako používateľ, ktorý ju vytvoril; definícia parametrov uloženej procedúry, uložené procedúry môžu mať vstupné a výstupné parametre; vývoj kódu uloženej procedúry. Kód procedúry môže obsahovať ľubovoľnú sekvenciu príkazov SQL vrátane volaní iných uložených procedúr.

Syntax na vytvorenie novej alebo úpravu existujúcej uloženej procedúry v notácii MS SQL Server je:

( CREATE | ALTER ) PROC[ EDURE] názov_procedúry [ ;číslo] [ ( @názov_parametra typ údajov ) [ VARYING ] [ = PREDCHOZIE ] [ VÝSTUP] ] [ ,... n] [ S ( ZNOVU KOMPILOVAŤ | ŠIFROVAŤ | ZNOVU KOMPILOVAŤ, ŠIFROVAŤ ) ] [ PRE REPLIKÁCIU] AKO príkaz_sql [ ... n]

Zvážte parametre tohto príkazu.

Pomocou predpôn sp_, #, ## možno vytvorenú procedúru definovať 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á. Preto, ak chcete umiestniť uloženú procedúru, ktorú vytvárate, do špecifickej databázy, musíte spustiť príkaz CREATE PROCEDURE v kontexte tejto databázy. Pri prístupe k objektom z rovnakej databázy z tela uloženej procedúry môžete použiť skrátené názvy, to znamená 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.

Na odovzdanie vstupných a výstupných údajov vo vygenerovanej uloženej procedúre musia názvy parametrov začínať znakom @. V jednej uloženej procedúre môžete zadať viacero parametrov oddelených čiarkami. Telo procedúry nesmie používať lokálne premenné, ktorých názvy sú rovnaké ako názvy parametrov procedúry. Akýkoľvek typ je vhodný na definovanie dátového typu parametrov uloženej procedúry. 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ženej procedúry, 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ženej procedúry. To však neznamená, že parameter nie je vhodný na odovzdávanie hodnôt do uloženej procedúry. Zadanie kľúčového slova OUTPUT dáva serveru pokyn, aby pri ukončení uloženej procedúry priradil aktuálnu hodnotu parametra lokálnej premennej, ktorá bola zadaná ako hodnota parametra pri volaní procedúry. 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 regulárne parametre nie sú povolené. Kľúčové slovo VARYING sa používa v spojení s parametrom OUTPUT typu CURSOR. Špecifikuje, že výstupným parametrom bude množina výsledkov.

Kľúčové slovo DEFAULT je hodnota, ktorú štandardne prevezme príslušný parameter. 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 pre uloženú procedúru pri každom jej volaní.

Možnosť FOR REPLICATION sa vyžaduje pri replikácii údajov a zahrnutí vygenerovanej uloženej procedúry ako článku v publikácii. Kľúčové slovo ENCRYPTION dáva serveru pokyn, aby zašifroval kód uloženej procedúry, čo môže poskytnúť ochranu pred použitím proprietárnych algoritmov, ktoré implementujú uloženú procedúru. Kľúčové slovo AS je umiestnené na začiatku samotného tela uloženej procedúry. V tele procedúry je možné použiť takmer všetky príkazy SQL, deklarovať transakcie, nastavovať zámky a volať ďalšie uložené procedúry. Uloženú procedúru môžete ukončiť príkazom RETURN.

Odstránenie uloženej procedúry

DROP PROCEDURE ( názov_procedúry) [ ,... n]

Vykonanie uloženej procedúry

Na vykonanie uloženej procedúry sa používa nasledujúci príkaz: [ [ EXEC [ UTE] názov_procedúry [ ;číslo] [ [ @názov_parametra= ] ( hodnota | @názov_premennej) [ VÝSTUP ] | [ VÝCHOZÍ ] ] [ ,...n]

Ak volanie uloženej procedúry nie je jediným príkazom v dávke, potom sa vyžaduje prítomnosť príkazu EXECUTE. 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 vo volaní procedúry je povolené len pre parametre, ktoré boli deklarované pri vytváraní procedúry s kľúčovým slovom OUTPUT.

Keď sa procedúra volá s kľúčovým slovom DEFAULT pre parameter, použije sa predvolená hodnota. Prirodzene, zadané 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 uvedené pri vytváraní procedúry. Nemôžete priradiť predvolenú hodnotu parametru tak, že ho jednoducho vynecháte v enumerácii. Ak chcete vynechať parametre, ktoré majú predvolenú hodnotu, stačí explicitne zadať názvy parametrov pri volaní uloženej procedúry. 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á.

Použitie RETURN v uloženej procedúre

Umožňuje vám ukončiť procedúru v ktoromkoľvek bode podľa zadanej podmienky a tiež vám umožňuje preniesť výsledok vykonania procedúry ako číslo, pomocou ktorého môžete posúdiť kvalitu a správnosť postupu. Príklad vytvorenia procedúry bez parametrov:

VYTVORIŤ POSTUP Count_Books AS SELECT COUNT (číselník) FROM Books GO

Cvičenie 1.

EXEC Count_Books

Skontrolujte výsledok.

Príklad vytvorenia procedúry so vstupným parametrom:

POSTUP VYTVORENIA Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GO

Úloha 2. Vytvorte túto procedúru v časti Uložené procedúry databázy DB_Books pomocou pomôcky SQL Server Management Studio. Spustite ho príkazom

EXEC Count_Books_Pages 100

Skontrolujte výsledok.

Príklad vytvorenia procedúry so vstupnými parametrami:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Úloha 3. Vytvorte túto procedúru v časti Uložené procedúry databázy DB_Books pomocou pomôcky SQL Server Management Studio. Spustite ho príkazom

EXEC Count_Books_Title 100 , "P%"

Skontrolujte výsledok.

Príklad vytvorenia procedúry so vstupnými parametrami a výstupným parametrom:

POSTUP VYTVORENIA Count_Books_Itogo @Count_pages INT , @Title CHAR (10) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Úloha 4. Vytvorte túto procedúru v časti Uložené procedúry databázy DB_Books pomocou pomôcky SQL Server Management Studio. Spustite pomocou sady príkazov:

Sql> Declare @q As int EXEC Count_Books_Itogo 100, "P%", @q output select @q

Skontrolujte výsledok.

Príklad vytvorenia procedúry so vstupnými parametrami a RETURN:

CREATE PROCEDURE kontrolné meno @param INT AS IF (SELECT Name_author FROM autorov WHERE Code_author = @param) = "Pushkin A.S." VRÁTIŤ 1 ELSE VRAŤ 2

Úloha 5. Vytvorte túto procedúru v časti Uložené procedúry databázy DB_Books pomocou pomôcky SQL Server Management Studio. Spustite ho pomocou príkazov:

DECLARE @return_status INT EXEC @return_status = kontrolné meno 1 VYBERTE "Stav vrátenia" = @return_status

Príklad vytvorenia procedúry bez parametrov na zdvojnásobenie hodnoty kľúčového poľa v tabuľke Nákupy:

CREATE PROC update_proc AS UPDATE Nákupy SET Code_purchase = Code_purchase* 2

Úloha 6. Vytvorte túto procedúru v časti Uložené procedúry databázy DB_Books pomocou pomôcky SQL Server Management Studio. Spustite ho príkazom

EXEC update_proc

Príklad procedúry so vstupným parametrom na získanie všetkých informácií o konkrétnom autorovi:

CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @k

Úloha 7.

EXEC select_author "Pushkin A.S." alebo select_author @k= "Pushkin A.S." alebo EXEC select_author @k= "Pushkin A.S."

Príklad vytvorenia procedúry so vstupným parametrom a predvolenou hodnotou na zvýšenie hodnoty kľúčového poľa v tabuľke Nákupy o určený počet (štandardne 2-krát):

CREATE PROC update_proc @p INT = 2 AKO AKTUALIZÁCIA nákupov SET Code_purchase = Code_purchase * @p

Procedúra nevracia žiadne údaje.

Úloha 8. Vytvorte túto procedúru v časti Uložené procedúry databázy DB_Books pomocou pomôcky SQL Server Management Studio. Spustite ho pomocou príkazov:

EXEC update_proc 4 alebo EXEC update_proc @p = 4 alebo EXEC update_proc -- použije sa predvolená hodnota.

Príklad vytvorenia procedúry so vstupnými a výstupnými parametrami. Vytvorte postup na určenie počtu objednávok dokončených počas určeného obdobia:

CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Nákupy WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@c, 0 )

Úloha 9. Vytvorte túto procedúru v časti Uložené procedúry databázy DB_Books pomocou pomôcky SQL Server Management Studio. Spustite ho pomocou príkazov:

DECLARE @c2 INT EXEC count_purchases '01- jún- 2006 ', '01- jul- 2006 ', @c2 OUTPUT SELECT @c2

Možnosti úloh pre laboratórne práce №4

Všeobecné ustanovenia. V SQL Server Management Studio vytvorte nová stránka pre kód (tlačidlo „Vytvoriť požiadavku“). Programovo aktivujte vytvorenú databázu DB_Books pomocou príkazu Use. Vytvorte uložené procedúry pomocou príkazov Create procedure a definujte názvy procedúr sami. Každá procedúra vykoná jeden SQL dotaz, ktorý bol vykonaný v druhom laboratóriu. Okrem toho musí byť kód SQL dotazov zmenený tak, aby mohli odovzdať hodnoty vyhľadávaných polí.

Napríklad pôvodná úloha a dotaz v laboratóriu č. 2:

/*Vyberte z adresára dodávateľov (tabuľka Dodávky) názvy spoločností, telefónne čísla a TIN (Fields Name_company, Phone and INN), ktorých názov spoločnosti (Field Name_company) je OAO MIR.

SELECT Name_company, Phone, INN FROM Delivery WHERE Name_company = "JSC MIR"

*/ – V tejto práci bude vytvorený postup:

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Dodávky WHERE Name_company = @comp

– Na spustenie postupu použite príkaz:

EXEC select_name_company "JSC MIR"

Zoznam úloh

Vytvorte nový program v SQL Server Management Studio. Programovo aktivujte individuálnu databázu vytvorenú v Lab #1 pomocou príkazu Use. Vytvorte uložené procedúry pomocou príkazov Create procedure a definujte názvy procedúr sami. Každá procedúra vykoná jeden SQL dotaz, ktorý je prezentovaný ako samostatné úlohy podľa možností.

možnosť 1

1. Zobrazte zoznam zamestnancov, ktorí majú aspoň jedno dieťa.

2. Zobrazte zoznam detí, ktoré dostali darčeky počas určeného obdobia.

3. Zobrazte zoznam rodičov, ktorí majú maloleté deti.

4. Zobrazte informácie o darčekoch s hodnotou väčšou ako zadané číslo, zoradené podľa dátumu.

Možnosť 2

1. Zobrazte zoznam zariadení so špecifikovaným typom.

2. Zobrazte počet opravených zariadení a celkovú cenu opráv od zadaného mastera.

3. Zobrazte zoznam vlastníkov zariadení a počet ich zásahov zoradených podľa počtu zásahov v zostupnom poradí.

4. Zobrazte informácie o majstroch s hodnosťou vyššou ako zadaný počet alebo s dátumom prenájmu menším ako zadaný dátum.

Možnosť 3

2. Zobrazte zoznam predajných kódov, za ktoré boli kvety predané za sumu vyššiu ako zadaný počet.

3. Zobrazte dátum predaja, množstvo, predajcu a kvet pre zadaný predajný kód.

4. Zobrazte zoznam farieb a zoraďte kvety s výškou väčšou ako zadaný počet alebo kvitnúce.

Možnosť 4

1. Zobrazte zoznam liekov s uvedenou indikáciou na použitie.

2. Zobrazte zoznam termínov dodania, na ktoré sa predalo viac ako zadané číslo lieku s rovnakým názvom.

3. Zobrazte dátum dodania, množstvo, celé meno vedúceho od dodávateľa a názov lieku o kód príjemky väčší ako zadané číslo.

Možnosť 5

2. Zobrazte zoznam vyradených zariadení zo zadaného dôvodu.

3. Zobrazte dátum prijatia, názov zariadenia, celé meno zodpovednej osoby a dátum vyradenia pre zariadenia vyradené z prevádzky počas určeného obdobia.

4. Zobrazte zoznam zariadení so zadaným typom alebo s dátumom príjmu väčším ako určitá hodnota

Možnosť 6

1. Zobrazte zoznam jedál s hmotnosťou väčšou ako zadané číslo.

2. Zobrazte zoznam produktov, ktorých názvy obsahujú zadaný fragment slova.

3. Zobrazte objem produktu, názov jedla, názov produktu s kódom jedla zo zadanej pôvodná hodnota na určitú koncovú hodnotu.

4. Zobrazte poradie varenia jedla a názov jedla s množstvom sacharidov väčším ako určitá hodnota alebo počtom kalórií väčším ako špecifikovaná hodnota.

Možnosť 7

1. Zobrazte zoznam zamestnancov so zadanou pozíciou.

3. Zobrazte dátum registrácie, typ dokumentu, celé meno registrátora a názov organizácie pri dokumentoch zaregistrovaných v uvedenom období.

4. Zobrazte zoznam evidovaných dokumentov s konkrétnym typom dokumentu alebo s dátumom registrácie väčším ako zadaná hodnota.

Možnosť 8

1. Zobrazte zoznam zamestnancov so zadaným dôvodom odchodu.

3. Zobrazte dátum registrácie, dôvod prepustenia, celé meno zamestnanca pri dokumentoch evidovaných v uvedenom období.

Možnosť 9

1. Zobrazte zoznam zamestnancov, ktorí čerpali dovolenku zadaného typu.

2. Zobrazte zoznam dokumentov s dátumom registrácie v zadanom období.

3. Pri dokladoch evidovaných v uvedenom období zobraziť dátum prihlásenia, druh dovolenky, celé meno zamestnanca.

4. Zobrazte zoznam evidovaných dokumentov s kódom dokumentu v zadanom rozsahu.

Možnosť 10

1. Zobrazte zoznam zamestnancov so zadanou pozíciou.

2. Zobrazte zoznam dokumentov obsahujúcich zadaný fragment slova.

3. Zobrazte dátum registrácie, typ dokumentu, celé meno odosielateľa a názov organizácie pri dokumentoch zaregistrovaných v uvedenom období.

4. Zobrazte zoznam evidovaných dokumentov so zadaným typom dokumentu alebo s kódom dokumentu menším ako určitá hodnota.

Možnosť 11

1. Zobrazte zoznam zamestnancov zaradených na zadanú pozíciu.

2. Zobrazte zoznam dokumentov s dátumom registrácie v zadanom období.

3. Zobrazte dátum registrácie, pozíciu, celé meno zamestnanca pri dokumentoch evidovaných v uvedenom období.

4. Zobrazte zoznam evidovaných dokumentov s kódom dokumentu v zadanom rozsahu.

Možnosť 12

3. Zobrazte zoznam ľudí, ktorí si prenajali vybavenie a počet ich požiadaviek, zoradený podľa počtu požiadaviek v zostupnom poradí.

Možnosť 13

1. Zobrazte zoznam zariadení so zadaným typom. 2. Zobrazte zoznam zariadení, ktoré určitý zamestnanec vyradil z prevádzky.

3. Zobrazte množstvo vyradeného zariadenia, zoskupené podľa typu zariadenia.

4. Zobrazte informácie o zamestnancoch s dátumom prijatia väčším ako určitý dátum.

Možnosť 14

1. Zobrazte zoznam kvetov so zadaným typom listu.

2. Zobrazte zoznam kódov účteniek, za ktoré boli kvety predané za sumy vyššie ako určitá hodnota.

3. Zobrazte dátum prijatia, množstvo, mená dodávateľa a farby pre konkrétny kód dodávateľa.

4. Zobrazte zoznam kvetov a zoradenie kvetov s výškou väčšou ako určitý počet alebo kvitnúcich.

Možnosť 15

1. Zobrazte zoznam klientov, ktorí sa v uvedenom období dostavili na izby.

2. Zobrazte celkovú výšku platieb za izby pre každého klienta.

3. Zobrazte dátum príchodu, typ izby, celé meno zákazníkov registrovaných v uvedenom období.

4. Zobrazte zoznam registrovaných klientov v izbách určitého typu.

Možnosť 16

1. Zobrazte zoznam zariadení so zadaným typom.

2. Zobrazte zoznam zariadení, ktoré si určitý klient prenajal.

3. Zobrazte zoznam ľudí, ktorí si prenajali vybavenie a počet ich požiadaviek, zoradený podľa počtu požiadaviek v zostupnom poradí.

4. Zobrazte informácie o klientoch zoradené podľa adries.

Možnosť 17

1. Zobrazte zoznam cenín s kúpnou cenou vyššou ako určitá hodnota alebo záručnou dobou vyššou ako určené číslo.

2. Zobrazte zoznam umiestnení hmotného majetku, v názve ktorých sa vyskytuje zadané slovo.

3. Zobrazte súčet ceny cenností s kódom v určenom rozsahu.

4. Zobrazte zoznam finančne zodpovedných osôb s dátumom vzniku pracovného pomeru v zadanom rozsahu.

Možnosť 18

1. Zobrazte zoznam opráv vykonaných konkrétnym majstrom.

2. Zobrazte zoznam pracovných etáp zahrnutých v práci, v názve ktorých sa zadané slovo vyskytuje.

3. Zobrazte súčet nákladov na etapy opravárenských prác pre práce s kódom v zadanom rozsahu.

4. Zobrazte zoznam majstrov s dátumom zamestnania v zadanom rozsahu.

Možnosť 19

1. Zobrazte zoznam liekov s konkrétnou indikáciou.

2. Zobrazte zoznam čísel účteniek, ktoré predali viac ako určitý počet liekov.

3. Na účtenku s uvedeným číslom zobrazte dátum predaja, sumu, celé meno pokladníka a liek.

4. Zobrazte zoznam liekov a merných jednotiek pre lieky s množstvom balenia väčším ako zadané číslo alebo kódom lieku menším ako určitá hodnota.

Možnosť 20

1. Zobrazte zoznam zamestnancov so zadanou pozíciou.

2. Zobrazte zoznam dokumentov obsahujúcich zadaný fragment slova.

3. Zobrazte dátum registrácie, typ dokumentu, celé meno exekútora a skutočnosť exekúcie pri dokumentoch evidovaných v uvedenom období.

4. Zobrazte zoznam evidovaných dokumentov so zadaným typom dokumentu alebo s kódom dokumentu v určitom rozsahu.

Uložená procedúra je špeciálny typ dávky príkazov Transact-SQL vytvorený pomocou jazyka SQL a procedurálnych rozšírení. Hlavný rozdiel medzi balíkom a uloženou procedúrou je v tom, že tá je uložená ako databázový objekt. Inými slovami, uložené procedúry sú uložené na strane servera, aby sa zlepšil výkon a konzistencia opakujúcich sa úloh.

Databázový stroj podporuje uložené procedúry a systémové procedúry. Uložené procedúry sa vytvárajú rovnakým spôsobom ako všetky ostatné databázové objekty, t.j. pomocou jazyka DDL. Systémové postupy sú poskytované databázovým strojom a možno ich použiť na prístup a úpravu informácií v systémovom katalógu.

Pri vytváraní uloženej procedúry môžete definovať voliteľný zoznam parametrov. Procedúra teda bude mať pri každom volaní príslušné argumenty. Uložené procedúry môžu vrátiť hodnotu obsahujúcu užívateľom definované informácie alebo v prípade chyby príslušné chybové hlásenie.

Uložená procedúra je predkompilovaná predtým, ako je uložená ako objekt v databáze. Predkompilovaná forma procedúry je uložená v databáze a používa sa pri každom jej volaní. Táto vlastnosť uložených procedúr poskytuje dôležitú výhodu eliminácie (takmer vo všetkých prípadoch) rekompilácií procedúr a získania zodpovedajúceho zlepšenia výkonu. Táto vlastnosť uložených procedúr má tiež pozitívny vplyv na množstvo dát vymieňaných medzi databázovým systémom a aplikáciami. Najmä volanie uloženej procedúry s veľkosťou niekoľkých tisíc bajtov môže vyžadovať menej ako 50 bajtov. Keď viacerí používatelia vykonávajú opakované úlohy pomocou uložených procedúr, kumulatívny efekt týchto úspor môže byť významný.

Uložené procedúry možno použiť aj na tieto účely:

na vytvorenie denníka protokolov o akciách s databázovými tabuľkami.

Použitie uložených procedúr poskytuje úroveň kontroly bezpečnosti, ktorá výrazne presahuje bezpečnosť poskytovanú použitím príkazov GRANT a REVOKE, ktoré používateľom udeľujú rôzne prístupové privilégiá. Je to možné, pretože autorizácia na vykonanie uloženej procedúry je nezávislá od autorizácie na úpravu objektov obsiahnutých v uloženej procedúre, ako je popísané v nasledujúcej časti.

Poskytujú uložené procedúry, ktoré vytvárajú protokoly operácií zápisu a/alebo čítania tabuľky dodatočná príležitosť bezpečnosť databázy. Pomocou takýchto postupov môže administrátor databázy sledovať zmeny vykonané v databáze používateľmi alebo aplikáciami.

Vytváranie a vykonávanie uložených procedúr

Uložené procedúry sa vytvárajú pomocou príkazu VYTVORIŤ POSTUP, ktorý má nasledujúcu syntax:

CREATE PROC proc_name [((@param1) type1 [ VARYING] [= default1] )] (, …) AS dávka | EXTERNAL NAME názov_metódy Konvencie syntaxe

Parameter schema_name určuje názov schémy, ktorý je priradený vlastníkom vygenerovanej uloženej procedúry. Parameter proc_name určuje názov uloženej procedúry. Parameter @param1 je parameter procedúry (formálny argument), ktorého typ údajov je špecifikovaný parametrom type1. Parametre procedúry sú lokálne v rámci procedúry, rovnako ako lokálne premenné sú lokálne v rámci balíka. Parametre procedúry sú hodnoty, ktoré volajúci odovzdá procedúre na použitie v nej. Parameter default1 určuje predvolenú hodnotu pre príslušný parameter procedúry. (Predvolená hodnota môže byť aj NULL.)

Možnosť OUTPUT určuje, že parameter procedúry je vratný a možno ho použiť na vrátenie hodnoty z uloženej procedúry do volajúcej procedúry alebo systému.

Ako už bolo spomenuté, predkompilovaná forma procedúry je uložená v databáze a používa sa pri každom jej volaní. Ak z nejakého dôvodu musí byť uložená procedúra skompilovaná pri každom jej volaní, používa sa deklarácia procedúry možnosť S PREKOMPILOVANÍM. Použitie možnosti WITH RECOMPILE neguje jednu z najdôležitejších výhod uložených procedúr: zlepšenie výkonu vďaka jedinej kompilácii. Voľba WITH RECOMPILE by sa preto mala používať len vtedy, ak dochádza k častým zmenám databázových objektov používaných uloženou procedúrou.

EXECUTE AS ponuku definuje bezpečnostný kontext, v ktorom musí byť uložená procedúra vykonaná po jej zavolaní. Nastavením tohto kontextu môže databázový stroj riadiť výber používateľských účtov na kontrolu prístupových oprávnení k objektom, na ktoré odkazuje táto uložená procedúra.

V predvolenom nastavení môžu príkaz CREATE PROCEDURE použiť iba členovia s pevnou rolou servera sysadmin a pevnou databázovou rolou db_owner alebo db_ddladmin. Členovia týchto rolí však môžu pomocou inštrukcie prideliť toto právo iným používateľom GRANTOVAŤ POSTUP VYTVORENIA.

Nižšie uvedený príklad ukazuje, ako vytvoriť jednoduchú uloženú procedúru na prácu s tabuľkou projektu:

USE SampleDb; PREJDITE POSTUP VYTVORENIA Zvýšiť rozpočet (@percent INT=5) AKO AKTUALIZOVAŤ NASTAVENIE projektu Rozpočet = Rozpočet + Rozpočet * @percento/100;

Ako už bolo spomenuté, na oddelenie dvoch balíkov sa používa GO pokyn. Príkaz CREATE PROCEDURE nie je možné kombinovať s inými príkazmi Transact-SQL v rovnakej dávke. Uložená procedúra Zvýšiť rozpočet zvyšuje rozpočty pre všetky projekty o určité percento určené parametrom @percent. Postup tiež definuje predvolenú percentuálnu hodnotu (5), ktorá sa použije, ak tento argument nie je prítomný počas vykonávania postupu.

Uložené procedúry môžu pristupovať k tabuľkám, ktoré neexistujú. Táto vlastnosť vám umožňuje ladiť kód procedúry bez predchádzajúceho vytvorenia príslušných tabuliek a dokonca bez pripojenia k cieľovému serveru.

Na rozdiel od základných uložených procedúr, ktoré sú vždy uložené v aktuálnej databáze, je možné vytvárať dočasné uložené procedúry, ktoré sú vždy umiestnené v dočasnej systémovej databáze tempdb. Jedným z dôvodov vytvárania dočasných uložených procedúr môže byť vyhnutie sa opakovanému vykonávaniu určitej skupiny príkazov pri pripájaní k databáze. Môžete vytvoriť lokálne alebo globálne dočasné procedúry. Na tento účel je názov lokálnej procedúry zadaný jedným znakom # (#názov_proc) a názov globálnej procedúry je zadaný dvojitým znakom (##názov_proc.).

Lokálnu dočasne uloženú procedúru môže spustiť iba používateľ, ktorý ju vytvoril, a to len počas pripojenia k databáze, v ktorej bola vytvorená. Globálnu dočasnú procedúru môžu vykonávať všetci užívatelia, ale len do ukončenia posledného pripojenia, na ktorom beží (zvyčajne spojenie tvorcu procedúry).

Životný cyklus uloženej procedúry pozostáva z dvoch fáz: jej vytvorenia a jej vykonania. Každá procedúra je vytvorená raz a vykonaná mnohokrát. Uložená procedúra sa vykoná pomocou EXECUTE príkazy užívateľ, ktorý vlastní procedúru alebo má právo VYKONAŤ prístup k procedúre. Príkaz EXECUTE má nasledujúcu syntax:

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] hodnota | [@parameter1=] @variable ] | DEFAULT).. Konvencie syntaxe

S výnimkou parametra return_status majú všetky parametre príkazu EXECUTE rovnakú boolovskú hodnotu ako parametre príkazu CREATE PROCEDURE s rovnakým názvom. Parameter return_status definuje celočíselnú premennú, ktorá ukladá návratový stav procedúry. Hodnota môže byť priradená parametru buď pomocou konštanty (value) alebo lokálnej premennej (@variable). Poradie pomenovaných hodnôt parametrov nie je dôležité, ale nepomenované hodnoty parametrov musia byť zadané v poradí, v akom sú definované v príkaze CREATE PROCEDURE.

klauzula DEFAULT poskytuje predvolené hodnoty pre parameter procedúry, ktorý bol zadaný v definícii procedúry. Keď procedúra očakáva hodnotu pre parameter, pre ktorý nebola definovaná žiadna predvolená hodnota a parameter chýba, alebo je zadané kľúčové slovo DEFAULT, dôjde k chybe.

Keď je príkaz EXECUTE prvým príkazom v dávke, kľúčové slovo EXECUTE možno vynechať. Bezpečnejšie je však zahrnúť toto slovo do každého balenia. Použitie príkazu EXECUTE je znázornené v príklade nižšie:

USE SampleDb; VYKONAŤ Zvýšenie rozpočtu 10;

Príkaz EXECUTE v tomto príklade spustí uloženú procedúru Zvýšenie rozpočtu, ktorá zvýši rozpočet všetkých projektov o 10 %.

Nasledujúci príklad ukazuje, ako vytvoriť uloženú procedúru na spracovanie údajov v tabuľkách Employee a Works_on:

Procedúra ModifyEmpId v príklade ilustruje použitie uložených procedúr ako súčasť procesu referenčnej integrity (v tomto prípade medzi tabuľkami Employee a Works_on). Takáto uložená procedúra môže byť použitá vo vnútri definície spúšťača, ktorá v skutočnosti vynucuje referenčnú integritu.

Nasledujúci príklad ukazuje použitie klauzuly OUTPUT v uloženej procedúre:

Táto uložená procedúra môže byť vykonaná pomocou nasledujúcich príkazov:

DECLARE @quantityDeleteEmployee INT; VYKONAŤ DeleteEmployee @empId=18316, @ [e-mail chránený] VÝKON; PRINT N"Zamestnanci vymazaní: " + convert(nvarchar(30), @quantityDeleteEmployee);

Tento postup spočíta počet projektov, na ktorých pracuje zamestnanec s osobným číslom @empId a výslednú hodnotu priradí parametru ©counter. Po vymazaní všetkých riadkov pre dané osobné číslo z tabuliek Zamestnanec a Works_on sa vypočítaná hodnota priradí k premennej @quantityDeleteEmployee.

Hodnota parametra sa vráti do volajúcej procedúry len vtedy, ak je zadaná voľba OUTPUT. Vo vyššie uvedenom príklade postup DeleteEmployee odovzdá parameter @counter volajúcej procedúre, takže uložená procedúra vráti hodnotu do systému. Preto musí byť parameter @counter špecifikovaný ako vo voľbe OUTPUT pri deklarovaní procedúry, tak aj v príkaze EXECUTE pri jej volaní.

WITH RESULTS SETS klauzula príkazu EXECUTE

V SQL Server 2012 je zadaný príkaz EXECUTE S VÝSLEDKMI klauzula A, ktoré za určitých podmienok môže zmeniť tvar sady výsledkov uloženej procedúry.

Nasledujúce dva príklady pomôžu vysvetliť túto vetu. Prvý príklad je úvodný príklad, ktorý ukazuje, ako môže vyzerať výsledok, keď sa vynechá klauzula WITH RESULTS SETS:

Procedúra EmployeesInDept je jednoduchý postup, ktorý zobrazuje personálne čísla a priezviská všetkých zamestnancov pracujúcich na konkrétnom oddelení. Číslo oddelenia je parameter procedúry a musí byť špecifikovaný pri volaní procedúry. Vykonaním tejto procedúry sa vypíše tabuľka s dvoma stĺpcami, ktorých hlavičky sa zhodujú s názvami zodpovedajúcich stĺpcov v databázovej tabuľke, t.j. id a priezvisko. Ak chcete zmeniť hlavičky stĺpcov výsledkov (ako aj ich typ údajov), SQL Server 2012 používa novú klauzulu WITH RESULTS SETS. Aplikácia tohto ustanovenia je znázornená v príklade nižšie:

USE SampleDb; EXEC EmployeesInDept "d1" SO SADAMI VÝSLEDKOV (( INT NOT NULL, [Priezvisko] CHAR(20) NOT NULL));

Výsledok vykonania takto volanej uloženej procedúry bude nasledujúci:

Ako vidíte, spustenie uloženej procedúry pomocou klauzuly WITH RESULT SETS v príkaze EXECUTE vám umožňuje zmeniť názvy a typ údajov stĺpcov sady výsledkov vytvorených procedúrou. Táto nová funkcia teda poskytuje väčšiu flexibilitu pri vykonávaní uložených procedúr a umiestňovaní ich výsledkov do novej tabuľky.

Zmena štruktúry uložených procedúr

Databázový stroj tiež podporuje vyhlásenie ZMENIŤ POSTUP na úpravu štruktúry uložených procedúr. Príkaz ALTER PROCEDURE sa zvyčajne používa na úpravu príkazov Transact-SQL v rámci procedúry. Všetky parametre príkazu ALTER PROCEDURE majú rovnaký význam ako parametre príkazu CREATE PROCEDURE s rovnakým názvom. Primárnym účelom použitia tohto príkazu je vyhnúť sa prepísaniu existujúcich povolení uloženej procedúry.

Databázový stroj podporuje Typ údajov CURSOR. Tento typ údajov sa používa na deklarovanie kurzorov v uložených procedúrach. Kurzor je programová konštrukcia používaná na ukladanie výsledkov dotazu (zvyčajne sady riadkov) a na umožnenie používateľom zobraziť tento výsledok riadok po riadku.

Ak chcete odstrániť jednu alebo skupinu uložených procedúr, použite vyhlásenie DROP PROCEDURE. Uloženú procedúru môže odstrániť iba vlastník uloženej procedúry alebo členovia pevných rolí db_owner a sysadmin.

Uložené procedúry a spoločný jazykový modul runtime

SQL Server podporuje Common Language Runtime (CLR), ktorý vám umožňuje vyvíjať rôzne databázové objekty (uložené procedúry, užívateľom definované funkcie, spúšťače, užívateľom definované agregáty a užívateľom definované dátové typy) pomocou C# a Visual Basic. Spoločný jazykový modul runtime tiež umožňuje spúšťanie týchto objektov pomocou spoločného systému runtime.

Spoločný jazykový modul runtime sa zapína a vypína prostredníctvom možnosti clr_enabled systémový postup sp_configure, ktorý sa spustí na vykonanie inštrukciou PREKONFIGURÁCIA. Nasledujúci príklad ukazuje, ako môžete povoliť spoločný jazykový modul runtime pomocou systémovej procedúry sp_configure:

USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE

Vytvorenie, kompilácia a uloženie procedúry pomocou CLR vyžaduje nasledujúcu postupnosť krokov v uvedenom poradí:

Vytvorte uloženú procedúru v jazyku C# alebo Visual Basic a potom ju skompilujte pomocou vhodného kompilátora.

Použitie inštrukcie VYTVORIŤ MONTÁŽ, vytvorte príslušný spustiteľný súbor.

Vykonajte procedúru pomocou príkazu EXECUTE.

Obrázok nižšie ukazuje grafická schéma predtým načrtnuté kroky. Nasleduje viac Detailný popis tento proces.

Najprv vytvorte požadovaný program v nejakom vývojovom prostredí, napr vizuálne štúdio. Kompilujte hotový program do objektového kódu pomocou kompilátora C# alebo Visual Basic. Tento kód je uložený v súbore dynamickej knižnice (.dll), ktorý slúži ako zdroj pre príkaz CREATE ASSEMBLY, ktorý vytvára prechodný spustiteľný kód. Ďalej zadajte príkaz CREATE PROCEDURE na uloženie vykonávaného kódu ako databázový objekt. Nakoniec spustite procedúru pomocou známeho príkazu EXECUTE.

Nasledujúci príklad ukazuje zdrojový kód uloženej procedúry v C#:

Používanie System.Data.SqlClient; pomocou Microsoft.SqlServer.Server; verejná čiastočná trieda StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) ako "Počet zamestnancov" " + "od zamestnanca"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )

Tento postup implementuje dotaz na počítanie počtu riadkov v tabuľke Zamestnanec. Pomocou direktív na začiatku programu špecifikujte menné priestory potrebné na jeho vykonanie. Použitie týchto direktív vám umožňuje špecifikovať názvy tried v zdrojovom kóde bez explicitného špecifikovania zodpovedajúcich menných priestorov. Ďalej je definovaná trieda StoredProcedures, pre ktorú Atribút SqlProcedure, ktorý informuje kompilátor, že táto trieda je uložená procedúra. Vo vnútri kódu triedy je definovaná metóda CountEmployees(). Pripojenie k databázovému systému je vytvorené prostredníctvom inštancie triedy SqlConnection. Na otvorenie pripojenia sa používa metóda Open() tejto inštancie. ALE CreateCommand() metóda umožňuje prístup k inštancii triedy SqlCommnd, ktorému sa odovzdá požadovaný SQL príkaz.

V nasledujúcom útržku kódu:

Cmd.CommandText = "vyberte počet(*) ako "Počet zamestnancov" " + "od zamestnanca";

používa príkaz SELECT na spočítanie počtu riadkov v tabuľke Zamestnanec a zobrazenie výsledku. Text príkazu je určený nastavením vlastnosti CommandText premennej cmd na inštanciu vrátenú metódou CreateCommand(). Ďalej sa volá Metóda ExecuteScalar(). inštancia SqlCommand. Táto metóda vráti skalárnu hodnotu, ktorá sa skonvertuje na celočíselný typ údajov int a priradí sa k premennej riadkov.

Teraz môžete tento kód skompilovať pomocou Visual Studia. Túto triedu som pridal do projektu s názvom CLRStoredProcedures, takže Visual Studio zostaví zostavu s rovnakým názvom s príponou *.dll. Príklad nižšie ukazuje ďalší krok pri vytváraní uloženej procedúry: vytvorenie kódu na spustenie. Pred spustením kódu v tomto príklade musíte poznať umiestnenie skompilovaného súboru .dll (zvyčajne sa nachádza v priečinku Debug projektu).

USE SampleDb; PREJDITE VYTVORIŤ MONTÁŽ CLRStoredProcedures Z "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" S POVOLANÍM_SET = BEZPEČNÉ

Príkaz CREATE ASSEMBLY berie ako vstup riadený kód a vytvára vhodný objekt, pre ktorý môžete vytvoriť uložené procedúry CLR (Common Language Runtime), užívateľom definované funkcie a spúšťače. Táto inštrukcia má nasledujúcu syntax:

CREATE ASSEMBLY Assembly_name [ AUTORIZÁCIA vlastník_názov ] FROM (dll_file) Konvencie syntaxe

Parameter Assembly_name určuje názov zostavy. Voliteľná klauzula AUTHORIZATION určuje názov roly ako vlastníka tohto zhromaždenia. Klauzula FROM určuje cestu, kde sa nachádza zostava, ktorá sa má načítať.

S klauzulou PERMISSION_SET je veľmi dôležitá klauzula príkazu CREATE ASSEMBLY a mala by byť vždy špecifikovaná. Definuje množinu prístupových práv udelených kódu zostavy. Sada práv SAFE je najviac obmedzujúca. Kód zostavy, ktorý má tieto práva, nemôže pristupovať k externým systémovým prostriedkom, ako sú súbory. Sada práv EXTERNAL_ACCESS umožňuje kódu zostavy pristupovať k určitým externým systémovým zdrojom, zatiaľ čo sada práv UNSAFE poskytuje neobmedzený prístup k prostriedkom vo vnútri aj mimo databázového systému.

Na uloženie informácií o kóde zostavy musí byť používateľ schopný vydať príkaz CREATE ASSEMBLY. Zostavu vlastní používateľ (alebo rola), ktorý vykonáva príkaz. Vlastníka zostavy môžete zmeniť pomocou klauzuly AUTHORIZATION príkazu CREATE SCHEMA.

Databázový stroj podporuje aj príkazy ALTER ASSEMBLY a DROP ASSEMBLY. Vyhlásenie ALTER MONTÁŽE používa sa na aktualizáciu zostavy na najnovšiu verziu. Táto inštrukcia tiež pridáva alebo odstraňuje súbory spojené s príslušnou zostavou. Vyhlásenie DROP MONTÁŽ odstráni zadané zhromaždenie a všetky súvisiace súbory z aktuálnej databázy.

Nasledujúci príklad ukazuje, ako vytvoriť uloženú procedúru založenú na riadenom kóde implementovanom skôr:

USE SampleDb; GO CREATE PROCEDURE CountEmployees AKO EXTERNÝ NÁZOV CLRStoredProcedures.StoredProcedures.CountEmployees

Príkaz CREATE PROCEDURE v príklade sa líši od rovnakého príkazu v predchádzajúcich príkladoch tým, že obsahuje Parameter EXTERNÉHO NÁZOV. Táto možnosť určuje, že kód generuje CLR. Názov v tejto vete sa skladá z troch častí:

názov_zhromaždenia.názov_triedy.názov_metódy

názov_zhromaždenia - určuje názov zostavy;

class_name - určuje názov všeobecnej triedy;

názov_metódy - voliteľná časť, určuje názov metódy, ktorá je nastavená vo vnútri triedy.

Vykonanie postupu CountEmployees je znázornené v príklade nižšie:

USE SampleDb; VYHLÁSIŤ @počet INT VYKONAŤ @počet = PočetZamestnanci VYTLAČIŤ @počet -- Vrátenie 7

Príkaz PRINT vráti aktuálny počet riadkov v tabuľke Zamestnanec.

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íkazov, ktorých použitie uľahčuje a spružňuje prácu programátora, pretož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 príkazov alebo funkcií SQL 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ých systémov a vykonávanie zmien v nich. 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. Na zabezpečenie integrity ú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, povoľuje alebo zakazuje 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 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 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, pomenujte ho s predponou sp_ a umiestnite ho do systémovej databázy. 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, je v poriadku akýkoľvek dátový typ SQL, 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.

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.Cena*Obchod.Množstvo AS Cost, Customer.Last Name FROM Customer INNER JOIN (Transakcia Item INNER JOIN ON Item.ItemId=Obchod.ItemId) NA Customer.CustomerCode=Obchod.CustomerCode WHE Zákazník .Priezvisko='Ivanov' Príklad 12.1. Postup pri získavaní názvov a cien tovaru zakúpeného spoločnosťou Ivanov.