Какво е sql съхранена процедура. Изходни данни за примери. Решения Събиране от уеб на "Каква е разликата между съхранена процедура и изглед?"
съхранена процедура - обект на база данни, който е набор от SQL изрази, който се компилира веднъж и се съхранява на сървъра. Съхранените процедури са много подобни на обикновените процедури в езиците на високо ниво, те могат да имат входни и изходни параметри и локални променливи, могат да извършват числени изчисления и операции върху символни данни, резултатите от които могат да бъдат присвоени на променливи и параметри. Съхранените процедури могат да изпълняват стандартни операции с бази данни (както DDL, така и DML). Освен това в съхранените процедури са възможни цикли и разклонения, тоест те могат да използват инструкции за контрол на процеса на изпълнение.
Съхранените процедури са подобни на дефинираните от потребителя функции (UDF). Основната разлика е, че дефинираните от потребителя функции могат да се използват като всеки друг израз в SQL заявка, докато съхранените процедури трябва да се извикват с помощта на функцията CALL:
CALL процедура (...)
ИЗПЪЛНИТЕ процедура (...)
Съхранените процедури могат да връщат набори от резултати, тоест резултатите от заявка SELECT. Такива набори от резултати могат да се обработват с помощта на курсори, от други съхранени процедури, връщащи указател на набор от резултати, или от приложения. Съхранените процедури могат също да съдържат декларирани променливи за обработка на данни и курсори, които ви позволяват да преминавате през множество редове в таблица. Стандартът SQL предоставя IF, LOOP, REPEAT, CASE и много други изрази за работа. Съхранените процедури могат да приемат променливи, да връщат резултати или да променят променливи и да ги връщат, в зависимост от това къде е декларирана променливата.
Изпълнението на запомнените процедури варира от една СУБД до друга. Повечето големи доставчици на бази данни ги поддържат под една или друга форма. В зависимост от СУБД, запомнените процедури могат да бъдат реализирани на различни езици за програмиране като SQL, Java, C или C++. Съхранените процедури, написани на различен от SQL, могат или не могат да изпълняват SQL заявки сами.
пер
Споделяне на логика с други приложения. Съхранените процедури капсулират функционалност; това гарантира достъп до данни и свързаност на управление между различни приложения.
Изолирайте потребителите от таблиците на базата данни. Това ви позволява да дадете достъп до съхранени процедури, но не и до самите данни в таблицата.
Осигурява защитен механизъм. Според предишната точка, ако имате достъп до данни само чрез съхранени процедури, никой друг не може да изтрие вашите данни чрез командата SQL DELETE.
Подобрена производителност в резултат на намален мрежов трафик. При съхранените процедури могат да се комбинират множество заявки.
Против
Повишено натоварване на сървъра на базата данни поради факта, че по-голямата част от работата се извършва от страната на сървъра и по-малко от страната на клиента.
Трябва да научите много. Ще трябва да научите синтаксиса на MySQL израза, за да напишете вашите съхранени процедури.
Вие дублирате логиката на приложението си на две места: код на сървъраи код за съхранени процедури, като по този начин усложнява процеса на манипулиране на данни.
Миграция от една СУБД към друга (DB2, SQL сървъри т.н.) може да доведе до проблеми.
Цел и ползи от съхранените процедури
Съхранените процедури подобряват производителността, подобряват опциите за програмиране и поддържат функции за защита на данните.
Вместо да съхраняват често използвана заявка, клиентите могат да се обърнат към подходящата съхранена процедура. Когато се извика съхранена процедура, нейното съдържание незабавно се обработва от сървъра.
В допълнение към действителното изпълнение на заявката, съхранените процедури също ви позволяват да извършвате изчисления и да манипулирате данни - промяна, изтриване, изпълнение на DDL изрази (не във всички СУБД!) и извикване на други съхранени процедури, извършване на сложна транзакционна логика. Един оператор ви позволява да извикате сложен скрипт, който се съдържа в съхранена процедура, което избягва изпращането на стотици команди по мрежата и по-специално необходимостта от прехвърляне на големи количества данни от клиента към сървъра.
В повечето СУБД първия път, когато се изпълнява съхранена процедура, тя се компилира (разбира се и се генерира план за достъп до данни). В бъдеще обработката му е по-бърза. Oracle DBMS интерпретира съхранения процедурен код, съхранен в речника на данните. Започвайки с Oracle 10g, се поддържа така наречената естествена компилация (нативна компилация) на съхранен процедурен код в C и след това в машинния код на целевата машина, след което, когато се извика съхранената процедура, нейният компилиран обектен код е директно изпълнен.
Опции за програмиране
След като създадете съхранена процедура, можете да я извикате по всяко време, което осигурява модулност и насърчава повторното използване на кода. Последното улеснява поддържането на базата данни, тъй като тя се изолира от променящите се бизнес правила. Можете да промените съхранена процедура, за да отговаря на новите правила по всяко време. След това всички приложения, които го използват, автоматично ще отговарят на новите бизнес правила без директна промяна.
Безопасност
Използването на съхранени процедури ви позволява да ограничите или напълно изключите директния потребителски достъп до таблиците на базата данни, оставяйки на потребителите само разрешения за изпълнение на съхранени процедури, които осигуряват индиректен и строго регулиран достъп до данни. В допълнение, някои СУБД поддържат текстово криптиране (обвиване) на съхранена процедура.
Тези функции за сигурност ви позволяват да изолирате структурата на базата данни от потребителя, което гарантира целостта и надеждността на базата данни.
Действия като "SQL инжектиране" са по-малко вероятни, тъй като добре написаните съхранени процедури допълнително валидират входните параметри, преди да предадат заявката към СУБД.
Внедряване на съхранени процедури
Съхранените процедури обикновено се създават с помощта на езика SQL или неговата специфична реализация в избраната СУБД. Например за тези цели СУБД на Microsoft SQL Server има езика Transact-SQL, Oracle има PL/SQL, InterBase и Firebird имат PSQL, PostgreSQL има PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 - SQL / PL (английски), в Informix - SPL. MySQL следва стандарта SQL:2003 доста близо и неговият език е подобен на SQL/PL.
В някои СУБД е възможно да се използват запаметени процедури, написани на всеки език за програмиране, способен да създава независими изпълними файлове, като C++ или Delphi. В терминологията на Microsoft SQL Server тези процедури се наричат разширени съхранени процедури и са просто функции, съдържащи се в Win32-DLL. И например в Interbase и Firebird за функции, извикани от DLL / SO, е дефинирано друго име - UDF (User Defined Function). В MS SQL 2005 стана възможно да се пишат запомнени процедури на всеки .NET език и се планира разширените запомнени процедури да бъдат изоставени в бъдеще. Oracle DBMS от своя страна позволява писане на съхранени процедури език Java. В IBM DB2 писането на съхранени процедури и функции на конвенционалните езици за програмиране е традиционният начин, поддържан от самото начало, а SQL процедурното разширение беше добавено към тази СУБД доста късно, след като беше включено в стандарта ANSI. Informix също поддържа Java и C процедури.
В СУБД Oracle съхранените процедури могат да се комбинират в така наречените пакети. Пакетът се състои от две части – спецификация (англ. package specification), която уточнява дефиницията на съхранената процедура, и тяло (англ. package body), където се намира нейната имплементация. Така Oracle ви позволява да отделите интерфейса на програмния код от неговата реализация.
В IBM DB2 запомнените процедури могат да се комбинират в модули.
Синтаксис
СЪЗДАВАНЕ НА ПРОЦЕДУРА `p2`()
SQL ДЕФИНЕР ЗА СИГУРНОСТ
КОМЕНТАР "Процедура"
ИЗБЕРЕТЕ „Здравей свят!“;
Първата част от кода създава съхранена процедура. Next - съдържа незадължителни параметри. След това идва името и накрая тялото на самата процедура.
4 характеристики на съхранена процедура:
Език: За целите на преносимостта по подразбиране е SQL.
Детерминиран: Ако процедурата връща един и същ резултат през цялото време и приема едни и същи входни параметри. Това е за процеса на репликация и регистрация. Стойността по подразбиране НЕ е ДЕТЕРМИНИСТИЧНА.
SQL сигурност: по време на разговора се проверяват правата на потребителя. INVOKER е потребителят, който извиква съхранената процедура. DEFINER е "създателят" на процедурата. Стойността по подразбиране е DEFINER.
Коментар: за целите на документацията стойността по подразбиране е ""
Извикване на съхранена процедура
CALL име на_съхранена_процедура (param1, param2, ....)
CALL procedure1(10, "низов параметър", @parameter_var);
Промяна на съхранена процедура
MySQL има оператор ALTER PROCEDURE за модифициране на процедури, но той е подходящ само за модифициране на определени характеристики. Ако трябва да промените параметрите или тялото на процедурата, трябва да я изтриете и да я създадете отново.
Премахванесъхраненипроцедури
ОТПУСКАНЕ НА ПРОЦЕДУРА, АКО СЪЩЕСТВУВА p2;
Това е проста команда. Операторът IF EXISTS улавя грешка, ако не съществува такава процедура.
Настроики
CREATE PROCEDURE proc1(): празен списък с параметри
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): един входен параметър. Думата IN не е задължителна, тъй като параметрите по подразбиране са IN (входящи).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): един върнат параметър.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): един параметър, както вход, така и изход.
Синтаксисът за деклариране на променлива изглежда така:
DECLARE varname DATA-TYPE DEFAULT стойност по подразбиране;
Когато работят с SQL Server, потребителите могат да създават свои собствени процедури, които изпълняват определени действия. Съхранените процедури са пълноценни обекти на база данни и следователно всяка от тях се съхранява в конкретна база данни. Директното извикване на съхранена процедура е възможно само ако се изпълнява в контекста на базата данни, където се намира процедурата.
Видове съхранени процедури
SQL Server има няколко вида запаметени процедури.
Системните съхранени процедури са предназначени за извършване на различни административни действия. С тяхна помощ се извършват почти всички действия по администриране на сървъра. Може да се каже, че системните съхранени процедури са интерфейс, който осигурява работа със системни таблици, което в крайна сметка се свежда до промяна, добавяне, изтриване и извличане на данни от системни таблици на потребителски и системни бази данни. Системните съхранени процедури имат префикс sp_, съхраняват се в системната база данни и могат да бъдат извикани в контекста на всяка друга база данни.
Персонализираните съхранени процедури изпълняват определени действия. Съхранените процедури са пълен обект на база данни. В резултат на това всяка съхранена процедура се намира в определена база данни, където се изпълнява.
Временно съхранените процедури съществуват само за кратко време, след което автоматично се унищожават от сървъра. Те се делят на локални и глобални. Локалните временно съхранени процедури могат да бъдат извикани само от връзката, на която са създадени. Когато създавате такава процедура, тя трябва да получи име, което започва с един знак #. Както всички временни обекти, съхранените процедури от този тип се изтриват автоматично, когато потребителят прекъсне връзката, рестартира или спре сървъра. Глобалните временно съхранени процедури са налични за всяка връзка на сървър, който има същата процедура. За да го дефинирате, е достатъчно да му дадете име, което започва със знаците ##. Тези процедури се изтриват, когато сървърът се рестартира или спре, или когато връзката, в чийто контекст са създадени, се затвори.
задейства
задействаса една от разновидностите на съхранените процедури. Тяхното изпълнение става, когато оператор на език за манипулиране на данни (DML) се изпълни на таблицата. Тригерите се използват за проверка на целостта на данните, както и за връщане назад на транзакции.
Тригере компилирана SQL процедура, чието изпълнение се определя от настъпването на определени събития в рамките на релационна база данни. Използването на тригери в по-голямата си част е много удобно за потребителите на бази данни. И все пак тяхното използване често е свързано с допълнителни разходи за ресурси за I / O операции. Когато същите резултати (с много по-малко разходи за ресурси) могат да бъдат постигнати с помощта на съхранени процедури или приложения, тригерите са неподходящи.
задействае специален SQL сървърен инструмент, използван за поддържане на целостта на данните в база данни. Ограниченията за интегритет, правилата и настройките по подразбиране може не винаги да осигуряват желаното ниво на функционалност. Често е необходимо да се внедрят сложни алгоритми за валидиране на данни, за да се гарантира, че те са валидни и реални. Освен това понякога е необходимо да се проследяват промените в стойностите на таблицата, за да се променят свързаните данни, ако е необходимо. Тригерите могат да се разглеждат като вид филтри, които влизат в сила, след като всички операции са извършени съгласно правилата, стойностите по подразбиране и т.н.
Тригере специален тип съхранена процедура, която се изпълнява автоматично от сървъра, когато се опитва да промени данните в таблиците, с които са свързани тригерите. всеки Тригере свързано с конкретна таблица. Всички промени в данните, които прави, се третират като една транзакция. Ако бъде открита грешка или нарушение на целостта на данните, транзакцията се връща назад. Следователно промените са забранени. Всички промени, които вече са направени от тригера, също се отменят.
Създава спусъксамо собственикът на базата данни. Това ограничение ви позволява да избегнете случайни промени в структурата на таблиците, начините за свързване на други обекти с тях и т.н.
Тригере много полезен и в същото време опасен инструмент. Така че, с грешна логика на работата му, можете лесно да унищожите цялата база данни, така че тригерите трябва да бъдат отстранени много внимателно.
За разлика от обикновената подпрограма, спусъксе изпълнява имплицитно всеки път, когато възникне задействащото събитие, и няма аргументи. Активирането му понякога се нарича задействане на спусъка. Тригерите постигат следните цели:
валидиране на въведени данни и прилагане на сложни ограничения за целостта на данните, които е трудно, ако не и невъзможно, да се поддържат с ограниченията за цялостност, зададени на таблицата;
издаване на предупреждения, които ви напомнят за необходимостта от извършване на определени действия при актуализиране на таблица, реализирана по определен начин;
натрупване на одитна информация чрез фиксиране на информация за направените промени и лицата, които са ги извършили;
поддръжка на репликация.
Основният формат на командата CREATE TRIGGER е показан по-долу:
<Определение_триггера>::=
CREATE TRIGGER trigger_name
ПРЕДИ | СЛЕД<триггерное_событие>
НА<имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Тригерните събития се състоят от вмъкване, изтриване и актуализиране на редове в таблица. В последния случай за задействащото събитие могат да бъдат зададени имена на конкретни колони в таблицата. Началният час на тригера се определя с помощта на ключовите думи BEFORE ( Тригерпожари преди изпълнението на свързаните с него събития) или СЛЕД (след тяхното изпълнение).
Действията, извършвани от тригера, се задават за всеки ред (ЗА ВСЕКИ РЕД), обхванат от това събитие, или само веднъж за всяко събитие (ЗА ВСЕКИ ИЗПЪЛНЕНИЕ).
Неправилно написаните тригери могат да доведат до сериозни проблеми, като появата на "мъртви" ключалки. Тригерите могат да блокират много ресурси за дълго време, така че трябва да обърнете внимание Специално вниманиеза минимизиране на конфликтите за достъп.
Тригермогат да бъдат създадени само в текущата база данни, но други бази данни могат да бъдат достъпни вътре в тригера, включително тези, разположени на отдалечен сървър.
1. Включете във вашите процедури реда - SET NOCOUNT ON:С всеки DML оператор SQL сървърът внимателно ни връща съобщение, съдържащо броя на обработените записи. Тази информацияможе да ни бъде полезен, докато отстраняваме грешки в кода, но след това ще бъде напълно безполезен. Като напишем SET NOCOUNT ON, ние деактивираме тази функция. За съхранени процедури, съдържащи множество изрази или\и цикли, това действие може да даде значително увеличение на производителността, тъй като обемът на трафика ще бъде значително намален.
CREATE PROC dbo.ProcName
КАТО
SET NO COUNT ON;
--Процедурен код тук
ИЗБЕРЕТЕ колона1 ОТ dbo.TblTable1
--Превключете SET NOCOUNT в първоначално състояние
SET NO COUNT OFF;
ОТИВАМ
2. Използвайте името на схемата с името на обекта:Е, мисля, че е ясно. Тази операция казва на сървъра къде да търси обекти и вместо произволно да рови в кошчетата си, той веднага ще разбере къде трябва да отиде и какво да вземе. С голям брой бази данни, таблици и съхранени процедури може значително да ни спести време и нерви.
SELECT * FROM dbo.MyTable --Това е добър начин да го направите
-- Вместо
ИЗБЕРЕТЕ * ОТ MyTable --Това е лошо нещо
--Процедурно извикване
EXEC dbo.MyProc -- Отново добре
--Вместо
EXEC MyProc --Лошо!
3. Не използвайте префикса "sp_" в името на вашите съхранени процедури:Ако името на нашата процедура започва с "sp_", SQL Server ще търси първо в своята основна база данни. Факт е, че този префикс се използва за лични вътрешни съхранени процедури на сървъра. Поради това използването му може да доведе до допълнителни разходи и дори неправилни резултати, ако процедура със същото име като вашето бъде открита в неговата база данни.
4. Използвайте АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ 1) вместо АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ *):За да проверим дали даден запис съществува в друга таблица, използваме израза IF EXISTS. Този изразвръща true, ако поне една стойност е върната от вътрешния израз, няма значение „1“, всички колони или таблица. Върнатите данни по принцип не се използват по никакъв начин. По този начин, за да компресирате трафика по време на предаване на данни, е по-логично да използвате "1", както е показано по-долу:
АКО СЪЩЕСТВУВА (ИЗБЕРЕТЕ 1 ОТ sysobjects
WHERE име = "Моята таблица" И тип = "U")
5. Използвайте TRY-Catch за улавяне на грешки:Преди сървъра 2005 след всяка заявка в процедурата се записваха огромен брой проверки за грешки. Повече код винаги консумира повече ресурси и повече време. От 2005 г. SQL Server изглежда по-правилен и удобен начинрешения на този проблем:
ЗАПОЧНЕТЕ ОПИТВАЙТЕ
--кодът
КРАЙ НА ОПИТА
ЗАПОЧНЕТЕ УЛОВА
-- код за улавяне на грешка
КРАЙ ЗАХВАТ
Заключение
По принцип това е всичко, което имам за днес. Още веднъж повтарям, че тук са само онези техники, които лично съм използвал в практиката си и мога да гарантирам за тяхната ефективност.
P.S.
Първият ми пост, не съдете строго.
SQL съхранените процедури са изпълним програмен модул, който може да се съхранява в различни обекти. С други думи, това е обект, който съдържа SQL изрази. Тези съхранени процедури могат да бъдат изпълнени в клиента приложни програмиза да получите добро представяне. Също така не е необичайно такива обекти да се извикват от други скриптове или дори от някой друг раздел.
Въведение
Мнозина смятат, че те са подобни на различни процедури (съответно, с изключение на MS SQL). Може би това е вярно. Те имат подобни параметри, могат да произвеждат подобни стойности. Освен това в някои случаи те са в контакт. Например, те се комбинират с DDL и DML бази данни, както и с потребителски функции (с кодово име UDF).
Всъщност SQL съхранените процедури имат широк набор от предимства, които ги отличават от подобни процеси. Сигурност, гъвкавост на програмирането, производителност - всичко това привлича все повече потребители, работещи с бази данни. Пикът на популярност на процедурите дойде през 2005-2010 г., когато беше пусната програма от Microsoft, наречена SQL Server Management Studio. С негова помощ работата с бази данни стана много по-лесна, практична и удобна. От година на година този набираше популярност сред програмистите. Днес това е напълно позната програма, която за потребителите, които „комуникират“ с бази данни, се е изравнила с Excel.
Когато се извика дадена процедура, тя незабавно се обработва от самия сървър без излишни процеси и намеса на потребителя. След това можете да извършите всяко изтриване, изпълнение, промяна. За всичко това отговаря DDL операторът, който сам извършва най-сложните действия за обработка на обекти. Освен това всичко това се случва много бързо и сървърът всъщност не се зарежда. Тази скорост и производителност ви позволява много бързо да прехвърляте големи количества информация от потребителя към сървъра и обратно.
За да се приложи тази технология за работа с информация, има няколко езика за програмиране. Те включват например PL / SQL от Oracle, PSQL в системите InterBase и Firebird, както и класическия "Microsoft" Transact-SQL. Всички те са проектирани да създават и изпълняват съхранени процедури, което позволява на големите процесори на бази данни да използват свои собствени алгоритми. Това е необходимо и за да може управляващите такава информация да защитят всички обекти от неоторизиран достъп от трети страни и съответно от създаване, промяна или изтриване на определени данни.
Производителност
Тези обекти на база данни могат да бъдат програмирани по различни начини. Това позволява на потребителите да изберат вида на използвания метод, който е най-подходящ, спестявайки време и усилия. В допълнение, самата процедура се обработва, което избягва огромното време, изразходвано за обмен между сървъра и потребителя. Също така, модулът може да бъде препрограмиран и сменен в правилната посока абсолютно по всяко време. Особено си струва да се отбележи скоростта, с която се стартира SQL съхранената процедура: този процес е по-бърз от други подобни на него, което го прави удобен и гъвкав.
Безопасност
Този вид обработка на информацията се различава от подобни процеси по това, че гарантира повишена сигурност. Това се гарантира от факта, че достъпът на други потребители до процедурите може да бъде напълно изключен. Това ще позволи на администратора да извършва операции с тях независимо, без страх от прихващане на информация или неоторизиран достъп до базата данни.
Трансфер на данни
Връзката между SQL съхранената процедура и клиентското приложение е използването на параметри и връщани стойности. Последният не трябва да прехвърля данни към съхранена процедура, но тази информация (най-вече по искане на потребителя) се обработва за SQL. След като съхранената процедура приключи своята работа, тя изпраща пакети с данни обратно (но, отново, по избор) към приложението, което я е извикало, използвайки различни методи, с помощта на който може да се извърши както извикване на съхранена SQL процедура, така и връщане, например:
Предаване на данни с помощта на параметър тип изход;
Предаване на данни чрез оператор за връщане;
Предаване на данни чрез оператор select.
Сега нека видим как изглежда този процес отвътре.
1. Създайте EXEC съхранена процедура в SQL
Можете да създадете процедура в MS SQL (Managment Studio). След като процедурата бъде създадена, тя ще бъде посочена в програмируемия възел на база данни, в който процедурата за създаване се изпълнява от оператора. SQL съхранените процедури използват EXEC процес за изпълнение, който съдържа името на самия обект.
Когато се създаде процедура, нейното име се появява първо, последвано от един или повече параметри, присвоени към нея. Параметрите може да не са задължителни. След като параметрите, т.е. тялото на процедурата, са написани, трябва да се извършат някои необходими операции.
Въпросът е, че тялото може да има локални променливи, разположени в него, и тези променливи са локални и за процедурите. С други думи, те могат да се разглеждат само в рамките на тялото на процедура на Microsoft SQL Server. В този случай съхранените процедури се считат за локални.
По този начин, за да създадем процедура, имаме нужда от името на процедурата и поне един параметър като тяло на процедурата. Имайте предвид, че страхотна опция в този случай е да създадете и изпълните процедура с името на схемата в класификатора.
Тялото на процедурата може да бъде от всякакъв вид, като например създаване на таблица, вмъкване на един или повече редове от таблица, задаване на типа и характера на базата данни и т.н. Тялото на процедурата обаче ограничава изпълнението на някои операции в нея. Някои от важните ограничения са изброени по-долу:
Тялото не трябва да създава друга съхранена процедура;
Тялото не трябва да създава фалшиво впечатление за обекта;
Тялото не трябва да създава никакви задействания.
2. Задаване на променлива в тялото на процедурата
Можете да направите променливи локални към тялото на процедурата и тогава те ще бъдат изключително в тялото на процедурата. Добра практика е да създавате променливи в началото на тялото на съхранената процедура. Но можете също да задавате променливи навсякъде в тялото на даден обект.
Понякога може да забележите, че няколко променливи са зададени на един и същ ред и всеки параметър на променливата е разделен със запетая. Също така имайте предвид, че променливата е с префикс @. В тялото на процедурата можете да зададете променливата, където пожелаете. Например, променливата @NAME1 може да бъде декларирана към края на тялото на процедурата. За да се присвои стойност на декларирана променлива, се използва набор от лични данни. За разлика от ситуацията, при която повече от една променлива е декларирана на един и същ ред, в тази ситуация се използва само един набор от лични данни.
Често потребителите задават въпроса: "Как да присвоите няколко стойности в едно изявление в тялото на процедура?" Добре. Въпросът е интересен, но е много по-лесен за изпълнение, отколкото си мислите. Отговор: с двойки като "Избор на променлива = стойност". Можете да използвате тези двойки, като ги разделите със запетая.
В различни примери хората показват създаването на проста съхранена процедура и нейното изпълнение. Процедурата обаче може да приема параметри, така че извикващият процес да има стойности, близки до нея (но не винаги). Ако те съвпадат, тогава започват съответните процеси вътре в тялото. Например, ако създадете процедура, която ще вземе града и региона от повикващия и ще върне колко автори принадлежат към съответния град и регион. Процедурата ще направи запитване към таблиците на авторите на базата данни, напр. Pubs, за да извърши това преброяване на авторите. За да получи тези бази данни, например, Google изтегля SQL скрипта от страницата SQL2005.
В предишния пример процедурата приема два параметъра, които са английски езикусловно ще се нарича @State и @City. Типът данни съответства на типа, дефиниран в приложението. Тялото на процедурата има вътрешни променливи @TotalAuthors (общо автори) и тази променлива се използва за показване на техния брой. Следва секцията за избор на заявка, която отчита всичко. Накрая изчислената стойност се отпечатва в изходния прозорец с помощта на оператор за печат.
Как да изпълним съхранена процедура в SQL
Има два начина за извършване на процедурата. Първият начин показва, чрез предаване на параметри, как се изпълнява разделен със запетая списък след името на процедурата. Да кажем, че имаме две стойности (както в предишния пример). Тези стойности се събират с помощта на променливите на параметрите на процедурата @State и @City. Редът е важен при този начин на предаване на параметри. Този метод се нарича предаване на редни аргументи. При втория метод параметрите вече са директно зададени, като в този случай редът не е важен. Този втори метод е известен като предаване на именувани аргументи.
Процедурата може да се отклонява донякъде от обичайната. Всичко е същото като в предишния пример, но тук параметрите са изместени. Това означава, че параметърът @City се съхранява първи, а @State се съхранява до стойността по подразбиране. Параметърът по подразбиране обикновено се маркира отделно. SQL съхранените процедури се предават само като параметри. В този случай, при условие че параметърът "UT" замества стойността по подразбиране "CA". При второто изпълнение се предава само една стойност на аргумент за параметъра @City, а параметърът @State приема стойността по подразбиране "CA". Опитните програмисти съветват всички променливи по подразбиране да бъдат поставени в края на списъка с параметри. В противен случай изпълнението не е възможно и тогава трябва да работите с предаване на именувани аргументи, което е по-дълго и по-сложно.
4. Съхранени процедури на SQL Server: начини за връщане
Има три важни начина за изпращане на данни в извикана съхранена процедура. Те са изброени по-долу:
Връщане на стойността на съхранена процедура;
Изходен параметър на запомнени процедури;
Изберете една от съхранените процедури.
4.1 Връщане на стойности от SQL съхранени процедури
При тази техника процедурата присвоява стойност на локална променлива и я връща. Процедурата може също директно да върне постоянна стойност. В следващия пример създадохме процедура, която връща общ бройавтори. Ако сравните тази процедура с предишните, можете да видите, че стойността за печат е обърната.
Сега нека видим как да изпълним процедурата и да отпечатаме върнатата от нея стойност. Изпълнението на процедурата изисква настройка на променлива и печат, който се извършва след целия този процес. Обърнете внимание, че вместо оператор за печат можете да използвате оператор Select, като Select @RetValue, както и OutputValue.
4.2 SQL Съхранена процедура Параметър Изход
Върнатата стойност може да се използва за връщане на една променлива, както видяхме в предишния пример. Използването на параметъра Output позволява на процедурата да изпрати една или повече променливи стойности на повикващия. Изходният параметър се обозначава само с тази ключова дума „Изход“ при създаването на процедурата. Ако даден параметър е указан като изходен параметър, тогава обектът на процедурата трябва да му присвои стойност. След това SQL съхранените процедури, примери за които могат да се видят по-долу, се връщат с обобщена информация.
В нашия пример ще има две имена на изход: @TotalAuthors и @TotalNoContract. Те са посочени в списъка с параметри. Тези променливи присвояват стойности в тялото на процедурата. Когато използваме изходни параметри, повикващият може да види зададената стойност в тялото на процедурата.
Освен това в предишния скрипт се декларират две променливи, за да се видят стойностите, които съхранените процедури на MS SQL Server задават в изходния параметър. След това процедурата се изпълнява чрез подаване на нормалната стойност на параметъра "CA". Следните параметрисе извеждат и следователно декларираните променливи се предават в указания ред. Имайте предвид, че при предаване на променливи, изходът ключова думасъщо дадени тук. След като процедурата приключи успешно, стойностите, върнати от изходните параметри, се показват в поле за съобщения.
4.3 Избор на една от SQL съхранените процедури
Тази техника се използва за връщане на набор от стойности под формата на таблица с данни (RecordSet) към извикващата съхранена процедура. В този пример SQL съхранената процедура с параметри @AuthID отправя заявки към таблицата Authors, като филтрира записите, върнати с помощта на този параметър @AuthId. Операторът Select решава какво трябва да се върне на извикващия съхранената процедура. Когато съхранената процедура се изпълни, AuthId се предава обратно. Такава процедура тук винаги връща само един запис или нито един. Но съхранената процедура няма никакви ограничения за връщане на повече от един запис. Не е необичайно да видите примери, при които връщането на данни с помощта на избрани параметри, включващи изчислени променливи, се извършва чрез предоставяне на множество суми.
Накрая
Съхранената процедура е доста сериозна програмна единица, която връща или предава и задава необходимите променливи благодарение на клиентското приложение. Тъй като самата съхранена процедура се изпълнява на сървъра, може да се избегне огромен обмен на данни между сървъра и клиентското приложение (за някои изчисления). Това намалява натоварването върху SQL сървър, което, разбира се, отива в ръцете на притежателите им. Един от подвидовете са съхранените процедури T SQL, но тяхното изучаване е необходимо за тези, които създават впечатляващи бази данни. Има и голям, дори огромен брой нюанси, които могат да бъдат полезни при изучаване на съхранени процедури, но това е по-необходимо за тези, които планират да се включат в програмирането отблизо, включително професионално.
Процедурна декларация
СЪЗДАВАНЕ НА ПРОЦЕДУРА [({ВЪВ|ВЪВ|ВЪВ} [,…])]
[ДИНАМИЧЕН НАБОР РЕЗУЛТАТИ ]
НАЧАЛО [АТОМНА]
КРАЙ
Ключови думи
. IN (Input) – входен параметър
. OUT (Output) – изходен параметър
. INOUT - вход и изход, както и поле (без параметри)
. DYNAMIC RESULT SET показва, че процедурата може да отвори определения брой курсори, които ще останат отворени, след като процедурата се върне
Бележки
Не се препоръчва използването на много параметри в запомнените процедури (предимно големи числа и символни низове) поради претоварване на мрежата и стека. На практика съществуващите диалекти на Transact-SQL, PL/SQL и Informix показват значителни отклонения от стандарта, както в декларирането на параметри, така и в използването, декларирането на променливи и извикването на подпрограми. Microsoft препоръчва използването на следното приближение за оценка на размера на кеша на съхранените процедури:
=(максимален брой едновременни потребители)*(най-голям размер на плана за изпълнение)*1,25. Определянето на размера на плана за изпълнение в страници може да се извърши с помощта на командата: DBCC MEMUSAGE.
Извикване на процедура
В много съществуващи СУБД запомнените процедури се извикват с помощта на израза:
ИЗПЪЛНЕТЕ ПРОЦЕДУРА [(][)]
Забележка: Извикване на съхранени процедури може да се направи от приложение, друга съхранена процедура или интерактивно.
Пример за декларация на процедура
CREATE PROCEDURE Proc1 AS //деклариране на процедура
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Рейтинг>200 //деклариране на курсор
OPEN Cur1 // отваряне на курсора
FETCH NEXT FROM Cur1 //чете данни от курсора
WHILE @@Fetch_Status=0
НАЧАЛО
ИЗВЛЕЧЕ СЛЕДВАЩО ОТ Cur1
КРАЙ
CLOSE Cur1 //затваряне на курсора
РАЗПРЕДЕЛЕНИЕ Cur1
EXECUTE Proc1 //изпълнете процедурата
Полиморфизъм
Две подпрограми с едно и също име могат да бъдат създадени в една и съща схема, ако параметрите на двете подпрограми са достатъчно различни една от друга, за да могат да бъдат разграничени. За да се прави разлика между две процедури с едно и също име в една и съща схема, на всяка се дава алтернативно и уникално име (специфично име). Такова име може да бъде изрично указано, когато подпрограмата е дефинирана. При извикване на подпрограми с няколко еднакви имена, определянето на желаната подпрограма се извършва в няколко стъпки:
. Първоначално се дефинират всички процедури с посоченото име, а ако няма такива, всички функции с посоченото име.
. За по-нататъшен анализ са оставени само онези подпрограми, по отношение на които даден потребителима привилегията EXECUTE.
. За тях се избират тези, чийто брой параметри съответства на броя аргументи на извикването. Проверяват се посочените типове данни на параметрите и техните позиции.
. Ако има повече от една останала подпрограма, тогава се избира тази с по-кратко квалифициращо име.
На практика Oracle поддържа полиморфизъм за функции, декларирани само в пакет, [имейл защитен]- в различни схеми и претоварването е забранено в Sybase и MS SQL Server.
Премахване и промяна на процедурите
Следният израз се използва за изтриване на процедура:
За да промените процедура, използвайте израза:
ПРОМЯНА НА ПРОЦЕДУРАТА [([{ВЪВ|ВЪВ|ВЪВ}])]
НАЧАЛО [АТОМНА]
КРАЙ
Привилегии за изпълнение на процедури
ПРЕДОСТАВЯНЕ НА ИЗПЪЛНЕНИЕ НА ДА СЕ |ПУБЛИЧЕН [С ОПЦИЯ ЗА ОТПУСКАНЕ]
Системни процедури
Много СУБД (включително SQL Server) имат определен набор от вградени системни съхранени процедури, които можете да използвате за свои собствени цели.