Что такое код drop procedure


Содержание

DROP PROCEDURE

Use the DROP PROCEDURE statement to remove a standalone stored procedure from the database. Do not use this statement to remove a procedure that is part of a package. Instead, either drop the entire package using the DROP PACKAGE statement, or redefine the package without the procedure using the CREATE PACKAGE statement with the OR REPLACE clause.

CREATE PROCEDURE and ALTER PROCEDURE for information on creating and modifying a procedure

The procedure must be in your own schema or you must have the DROP ANY PROCEDURE system privilege.

Specify the schema containing the procedure. If you omit schema , then Oracle Database assumes the procedure is in your own schema.

Specify the name of the procedure to be dropped.

When you drop a procedure, Oracle Database invalidates any local objects that depend upon the dropped procedure. If you subsequently reference one of these objects, then the database tries to recompile the object and returns an error message if you have not re-created the dropped procedure.

Oracle Database Concepts for information on how Oracle Database maintains dependencies among schema objects, including remote objects

Dropping a Procedure: Example The following statement drops the procedure remove_emp owned by the user hr and invalidates all objects that depend upon remove_emp :

DROP PROCEDURE

DROP PROCedure [owner.]procedure_name [, [owner.]procedure_name. ]
Уничтожает процедуру в текущей базе данных. Если процедура является пользовательской системнрй процедурой, т.е. начинается с символов sp_, то она ищется в базе данных master. Если процедура с указанным имененем не найдена в текущей базе данных — она опять-таки ищется в базе данных master.
Группа процедур (т.е. процедуры с одинаковым именем, но с разным параметром ;number могут быть удалены одним оператором DROP
Пример

DROP PROCEDURE

English-Russian SQL Server dictionary . 2013 .

Смотреть что такое «DROP PROCEDURE» в других словарях:

Drop shipping — is a supply chain management technique in which the retailer does not keep goods in stock, but instead transfers customer orders and shipment details to either the manufacturer or a wholesaler, who then ships the goods directly to the customer.… … Wikipedia

drop of a sail — Depth Depth (s[e^]pth), n. [From ; akin to D. diepte, Icel. d[=y]pt, d[=y]p[eth], Goth. diupi[thorn]a.] 1. The quality of being deep; deepness; perpendicular measurement downward from the surface, or horizontal measurement backward from the … The Collaborative International Dictionary of English

high-velocity drop — A drop procedure in which the drop velocity is greater than 30 ft per second (i.e., a low velocity drop, and lower than a free drop velocity). In a high velocity drop, conventional parachutes cannot be used, and other methods of retarding and… … Aviation dictionary

high velocity drop — A drop procedure in which the drop velocity is greater than 30 feet per second (low velocity drop) and lower than free drop velocity. See also airdrop … Military dictionary

low velocity drop — A drop procedure in which the drop velocity does not exceed 30 feet per second … Military dictionary

Oil drop experiment — Millikan s setup for the oil drop experiment The oil drop experiment was an experiment performed by Robert Millikan and Harvey Fletcher in 1909 to measure the elementary electric charge (the charge of the electron). The experiment entailed… … Wikipedia

Java stored procedure — A Java stored procedure is a set of SQL statements, written in the Java programming language, grouped together as an executable unit.A stored procedure is a program that is kept and executed within a database server. The procedure is called from… … Wikipedia

Oil-drop experiment — The purpose of Robert Millikan and Harvey Fletcher s oil drop experiment (1909) was to measure the electric charge of the electron. They d >Wikipedia

Fontan procedure — Interventions infobox Name = PAGENAME Caption = ICD10 = ICD9 = 35.94 Mesh >Wikipedia

Stop, drop and roll — is a simple fire safety technique taught to children, emergency services personnel and industrial workers as a component of health and safety training.Primarily, it is a method to extinguish a fire on a person s clothes or hair without, or in… … Wikipedia

Saison 1 de Drop Dead Diva — Cet article présente le gu >Wikipédia en Français

Хранимые процедуры MySQL

Хранимая процедура MySQL представляет собой подпрограмму, хранящуюся в базе данных. Она содержит имя, список параметров и операторы SQL . Все популярные системы управления базами данных поддерживают хранимые процедуры. Они были введены в MySQL 5 .

Существует два вида подпрограмм: хранимые процедуры и функции, возвращающие значения, которые используются в других операторах SQL ( например, pi() ).

Основное отличие заключается в том, что функции могут использоваться, как любое другое выражение в операторах SQL , а хранимые процедуры должны вызываться с помощью оператора CALL .

В чем преимущество хранимых процедур?

  • Хранимые процедуры работают быстро. Преимущество сервера MySQL заключается в том, что он использует кэширование, а также заранее заданные операторы. Основной прирост скорости дает сокращение сетевого трафика. Если есть повторяющиеся задачи, которые требуют проверки, обработки циклов, нескольких операторов, и при этом не требуют взаимодействия с пользователем, это можно реализовать с помощью одного вызова процедуры, которая хранится на сервере;
  • MySQL хранимые процедуры являются универсальными. При написании хранимой процедуры на SQL она будет работать на любой платформе, которая использует MySQL . В этом преимущество SQL над другими языками, такими как Java , C или PHP ;
  • Исходный код хранимых процедур всегда доступен в базе данных. Это эффективная практика связать данные с процессами, которые их обрабатывают.

Создание процедуры

По умолчанию процедура связана с базой данных, используемой в данный момент. Чтобы связать процедуру с конкретной базой данных, укажите ее создании хранимой процедуры: имя_базы_данных.имя_хранимой_процедуры . Полный синтаксис:

Перед тем, как осуществить MySQL вызов хранимой процедуры , необходимо получить определенную информацию.

Проверка версии MySQL

Следующая команда выводит версию MySQL :

Проверка привилегий текущего пользователя

Для команд CREATE PROCEDURE и CREATE FUNCTION требуются привилегия пользователя CREATE ROUTINE . Также может потребоваться привилегия SUPER , это зависит от значения DEFINER , которое будет описано далее. Если включен бинарный лог для CREATE FUNCTION , то может потребоваться привилегия SUPER . По умолчанию MySQL автоматически предоставляет для создателя подпрограммы привилегии ALTER ROUTINE и EXECUTE . Такое поведение можно изменить, отключив системную переменную automatic_sp_privileges :

Выбор базы данных

Перед тем создать процедуру MySQL , нужно выбрать базу данных. Давайте просмотрим список баз данных и выберем одну из них:

Теперь выберите базу данных « hr » и выведите список таблиц:

Выбор разделителя

Разделитель — символ или строка символов, которая используется для закрытия оператора SQL . По умолчанию в качестве разделителя используется точка с запятой ( ; ). Но это вызывает проблемы в хранимых процедурах и триггерах MySQL , поскольку она может иметь много операторов, и каждый должен заканчиваться точкой с запятой. Поэтому в качестве разделителя будем использовать двойной знак доллара — $$ . Чтобы позже снова использовать в качестве разделителя « ; » выполните команду « DELIMITER ; $$ «. Ниже приведен код для смены разделителя:

Теперь DELIMITER по умолчанию — « $$ «. Выполним простую команду SQL :

Теперь выполните следующую команду, чтобы снова установить « ; » в качестве разделителя:

Пример процедуры MySQL

Мы создадим простую MySQL процедуру под названием job_data , при выполнении она будет выводить все данные из таблицы « jobs «:

  • Команда CREATE PROCEDURE создает хранимую процедуру;
  • Следующая часть — это имя процедуры « job_data «;
  • Имена процедур не чувствительны к регистру, поэтому job_data равносильно JOB_DATA ;
  • Нельзя использовать две процедуры с одним именем в одной и той же базе данных;
  • Можно использовать имена в формате « имя-процедуры.имя-базы-данных «, например, « hr.job_data «;
  • Имена процедур могут быть разделены. Если имя разделено, оно может содержать пробелы;
  • Максимальная длина имени процедуры составляет 64 символа;
  • Избегайте использования имен встроенных функций MySQL ;
  • Последняя часть « CREATE PROCEDURE » — это пара скобок содержит список параметров. Поскольку эта процедура не имеет никаких параметров, список пуст;
  • Следующая часть SELECT * FROM JOBS; $$ — это последний оператор в синтаксисе хранимых процедур MySQL . Точка с запятой ( ; ) здесь не является обязательной, так как реальным окончанием оператора является $$ .

Инструменты для создания процедур MySQL

Можно написать процедуру с помощью инструмента командной строки MySQL или с помощью MySQL Workbench .

Инструмент командной строки MySQL :

Выберите из меню « Пуск » « Клиент командной строки MySQL »:

Вы увидите на экране следующее окно:

После авторизации можно будет получить доступ к командной строке MySQL :

Теперь можно писать и запускать собственные процедуры, смотрите следующий пример:

MySQL Workbench (5.3 CE) :

Выберите в меню « Пуск » « MySQL Workbench »:

После этого вы увидите на экране следующее окно:

Введите свои учетные данные:

После авторизации на экране появится новое окно, и с помощью панели просмотра объектов можно будет выбрать базу данных:

После этого кликните правой кнопкой мыши по пункту « Routines » и на экране появится новое всплывающее окно:

После этого на экране появится следующее окно, в котором можно создать собственную процедуру MySQL :

После того, как напишете процедуру, нажмите кнопку « Apply » и на экране появится следующее окно:

В этом окне можно просмотреть скрипт и применить его в базе данных:

Теперь нажмите на кнопку « Finish » и запустите процедуру:

Вызов процедуры

Оператор CALL используется для вызова процедуры, которая хранится в базе данных. Синтаксис следующий:

MySQL хранимые процедуры, которые не принимают аргументов, могут вызываться без скобок. Поэтому CALL job_data() равносильно CALL job_data .

Давайте выполним процедуру:

SHOW CREATE PROCEDURE

Этот оператор является расширением MySQL . Он возвращает точную строку, которая может быть использована, чтобы воссоздать указанную хранимую процедуру. Синтаксис следующий:

Давайте осуществим MySQL вызов хранимой процедуры:

MySQL: блоки характеристик

В синтаксисе оператора CREATE PROCEDURE допустимо использование блоков, которые описывают характеристики процедуры. Блоки указываются после скобок, но перед телом процедуры. Эти блоки являются необязательными.

COMMENT

Характеристика COMMENT — это расширение MySQL . Она используется для описания хранимой подпрограммы, и данная информация отображается с помощью оператора SHOW CREATE PROCEDURE .

LANGUAGE

Характеристика LANGUAGE указывает на то, что тело процедуры написано на SQL .

NOT DETERMINISTIC

Это информационная характеристика. Процедура считается « детерминированной «, если она всегда дает тот же результат для одних и тех же входных параметров, иначе она является « не детерминированной «.

CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA

CONTAINS SQL означает, что в хранимой процедуре MySQL нет никаких заявлений, которые считывают или записывают данные. Например, заявления SET @x = 1 или DO RELEASE_LOCK(‘abc’) , они выполняются, но не считывают и не записывают данные. Это значение по умолчанию, если не указано другое значение характеристики.

NO SQL означает, что процедура не содержит операторов SQL .

READS SQL DATA — процедура содержит операторы, которые считывают данные ( например, SELECT ), но не содержит операторов, которые записывают данные.

MODIFIES SQL DATA-означает , что подпрограмма содержит операторы, которые могут записывать данные ( например, INSERT или DELETE ).

Значение SQL SECURITY может быть определено либо как SQL SECURITY DEFINER , либо как SQL SECURITY INVOKER . Оно указывает, выполняется ли подпрограмма с использованием привилегий аккаунта, указанного в условии DEFINER , или аккаунта пользователя, который осуществляют MySQL вызов хранимой процедуры. Этот аккаунт должен иметь разрешение на доступ к базе данных, с которой связана подпрограмма. Значение по умолчанию DEFINER . Пользователь, который запускает процедуру, должен иметь привилегию EXECUTE , если процедура выполняется в контексте безопасности DEFINER .

Все перечисленные блоки характеристик имеют значения по умолчанию. Следующие два оператора дают одинаковый результат:

то же самое, что:

Прежде, чем перейти к параметрам MySQL , рассмотрим несколько составных операторов MySQL .

MySQL: составные операторы

Составной оператор представляет собой блок, который может содержать другие блоки: объявления переменных, обработчиков состояний и курсоров, конструкции управления потоками данных, циклы и условные тесты. В версии MySQL 5.6 существуют следующие составные операторы:

  • Составной оператор BEGIN … END ;
  • Метки операторов;
  • DECLARE ;
  • Переменные в хранимых программах;
  • Операторы контроля потока данных;
  • Курсоры;
  • Обработчики условий.

В этом разделе мы рассмотрим первые четыре оператора, связанные с параметрами оператора CREATE PROCEDURE .

Синтаксис составного оператора BEGIN … END

Он используется, когда нужно разместить в пределах подпрограммы ( например, хранимой процедуры MySQL , функции, триггера или события ) более одного оператора. Синтаксис следующий:

список_операторов: один или несколько операторов, завершающихся точкой с запятой ( ; ). Сам по себе список операторов не является обязательным, поэтому пустой оператор BEGIN END является действительным.

Метки операторов

Метки — это разрешения на выполнение для блоков BEGIN … END и операторов цикла REPEAT и WHILE . Синтаксис следующий:

При применении меток применяются следующие правила:

  • метка_начала должна закрываться двоеточием;
  • метка_начала может использоваться без метки_конца. Если метка_конца присутствует, она должна принадлежать тому же блоку, что и метка_начала;
  • метка_конца не может использоваться без метки_начала;
  • метки, принадлежащие к одному вложенному уровню, должны быть разделены;
  • метки могут иметь длину не более 16 символов.

Оператор DECLARE

Используется для определения различных локальных элементов при MySQL создании хранимой процедуры. Например, локальных переменных, условий, обработчиков, курсоров. DECLARE используется только внутри составного оператора BEGIN … END и должен находиться в его начале перед всеми остальными операторами.

Для объявлений существуют следующие правила:


  • Объявления курсоров должны размещаться перед объявлениями обработчиков;
  • Объявления переменных и условий должны размещаться перед объявлениями курсоров или обработчиков.

Переменные в хранимых программах

Хранимые программы используют оператор DECLARE для определения локальных переменных. Процедуры и функции могут при объявлении принимать параметры, которые обмениваются значениями между подпрограммой и вызывающим ее агентом.

Чтобы предоставить значение для переменной по умолчанию, используется блок DEFAULT . Значение может быть задано как выражение; это не обязательно должна быть константа. Если блок DEFAULT отсутствует, начальное значение равно NULL .

Пример: Локальные переменные

Локальные переменные объявляются внутри хранимых процедур MySQL . Они действительны только в пределах блока END … BEGIN , в котором они объявлены. Локальные переменные могут содержать любой тип данных SQL . В следующем примере показано использование локальных переменных в хранимой процедуре:

Теперь выполните процедуру:

Пример: пользовательские переменные

В хранимых процедурах MySQL обращение к пользовательским переменным происходит через символ амперсанда (@) перед именем пользовательской переменной ( например, @x и @y ). В следующем примере показано использование пользовательских переменных внутри хранимой процедуры:

MySQL: параметры процедуры

Ниже приводится синтаксис CREATE PROCEDURE для параметров:

  1. CREATE PROCEDURE имя_процедуры () …
  2. CREATE PROCEDURE имя_процедуры ( [IN] имя_параметра type )…
  3. CREATE PROCEDURE имя_процедуры ( [OUT] имя_параметра type )…
  4. CREATE PROCEDURE имя_процедуры ( [INOUT] имя_параметра type )…
  • В первом примере список параметров пуст.
  • Во втором примере параметр IN передает значение в процедуру. Эта процедура может изменить значение. Но, когда процедура возвращает значение, оно не будет видно для вызывающего агента.
  • В третьем примере параметр OUT передает значение из процедуры обратно вызывающему агенту. Его начальное значение в процедуре NULL , и, когда процедура возвращает значение, оно видно вызывающему агенту.
  • В четвертом примере параметр INOUT инициализируется вызывающим агентом, он может быть изменен процедурой, и когда процедура возвращает значение, любые изменения, произведенные MySQL хранимой процедурой, будут видны вызывающему агенту.

В процедуре каждый параметр по умолчанию является параметром IN . Чтобы изменить, это используйте перед именем параметра ключевое слово OUT или INOUT .

Процедура MySQL: пример параметра IN

В следующей процедуре использован параметр IN « var1 » ( тип целое число ), который принимает число от пользователя. В теле процедуры есть оператор SELECT , который выбирает строки из таблицы « jobs «. Количество строк указывается пользователем. Ниже приводится процедура:

Чтобы выбрать первые две строки из таблицы « jobs » выполните следующую команду:

Теперь выберите первые пять строк из таблицы « jobs «:

Процедура MySQL: пример параметра OUT

Дальше представлен MySQL хранимой процедуры пример, в котором используется параметр OUT . В рамках процедуры MySQL функция MAX() извлекает максимальную зарплату из столбца MAX_SALARY таблицы « jobs «:

В теле процедуры параметр получает самую высокую зарплату из столбца MAX_SALARY . После вызова процедуры слово OUT сообщает СУБД , что значение исходит от процедуры. highest_salary — это имя выходного параметра и в операторе CALL мы передали его значение переменной сеанса с именем @M :

Процедура MySQL: Пример параметра INOUT

В следующем примере показана простая хранимая процедура MySQL , которая использует параметр INOUT и параметр IN . Пользователь предоставляет ‘ M ‘ или ‘ F ‘ через параметр IN ( emp_gender ) для подсчета количества сотрудников мужского или женского пола из таблицы user_details . Параметр INOUT ( mfgender ) возвращает результат пользователю. Вот код и результат выполнения процедуры:

Теперь проверяем количество сотрудников мужского и женского пола в указанной таблице:

MySQL: Операторы управления потоком

MySQL поддерживает конструкции для управления потоком данных в хранимых программах IF , CASE , ITERATE , LEAVE , LOOP , WHILE и REPEAT . Также поддерживается RETURN внутри хранимых процедур MySQL .

MySQL: Оператор IF

Оператор IF реализует базовую конструкцию условия, он должен заканчиваться точкой с запятой. Существует также функция IF(), которая отличается от оператора IF . Вот синтаксис оператора IF :

Если условие выполняется, выполняются операторы соответствующих блоков THEN или ELSE IF .

Если условие не удовлетворяется, выполняются операторы блока ELSE . Каждый оператор состоит из одного или нескольких операторов SQL ; пустые операторы не допускается.

В следующем примере мы передаем через параметр IN user_id , чтобы получить имя пользователя. В рамках процедуры мы использовали операторы IF ELSE IF и ELSE , чтобы получить имя пользователя из множества идентификаторов пользователей. Имя пользователя будет храниться в параметре user_name INOUT :

Осуществите MySQL вызов хранимой процедуры:

MySQL: Оператор CASE

Оператор CASE используется для создания внутри хранимой процедуры MySQL сложной условной конструкции. Оператор CASE не может содержать блок ELSE NULL и должен закрываться END CASE , а не END . Синтаксис:

Пояснение: первый синтаксис

Значение — это выражение, которое сравнивается со значением в каждом блоке WHEN , пока они не будут равны. При найденном соответствии значений выполняется список_операторов соответствующего блока THEN .

Илон Маск рекомендует:  Шаблон сайта дома HTML, CSS, 1 страница

Если значения не равны, тогда выполняется список_операторов блока ELSE , ( если таковой имеется ).

Пояснение: второй синтаксис

Каждое выражение блока условие_поиска оценивается , пока одно из них не будет истинно. В этот момент выполняется список_операторов соответствующего блока THEN .

Если ни одно из выражений условие_поиска не истинно, тогда выполняется список_операторов блока ELSE , если таковой имеется. Каждый список_операторов состоит из одного или нескольких операторов SQL ; пустой список_операторов не допускается.

У нас есть таблица под названием ‘ jobs ‘ со следующими записями:

Подсчитаем количество сотрудников, удовлетворяющих следующим условиям:

  • MIN_SALARY > 10000
  • MIN_SALARY
  • MIN_SALARY = 10000

Для этого мы используем следующую процедуру ( MySQL хранимой процедуры пример создан в MySQL Workbench 5.2 CE ):

В приведенной выше процедуре мы передаем переменную salary через параметр IN . Есть оператор CASE с двумя блоками WHEN и ELSE , который проверяет условия и возвращает значение счетчика в no_employees. Выполним процедуру через командную строку MySQL .

Количество сотрудников, чья зарплата превышает 10000:

Количество сотрудников, чья зарплата меньше, чем 10000:

Количество сотрудников, чья зарплата равна 10000:

MySQL: оператор ITERATE

ITERATE означает « запустить цикл снова «. ITERATE может использоваться только в операторах LOOP , REPEAT и WHILE . Синтаксис следующий:

MySQL: оператор LEAVE

Используется для выхода из конструкции управления потоком, который имеет заданную метку. Если метка задана для самого последнего блока хранимой процедуры MySQL , LEAVE выходит из программы.

LEAVE может использоваться в BEGIN … END или конструкциях цикла ( LOOP , REPEAT , WHILE ). Синтаксис следующий:

MySQL: оператор LOOP

Используется, чтобы задать повторное выполнение списка операторов. Синтаксис следующий:

список_операторов состоит из одного или нескольких операторов, каждый из которых заканчивается точкой с запятой ( ; ). Операторы внутри цикла повторяются до тех пор, пока цикл не будет завершен. Обычно для выхода из конструкции цикла используется LEAVE .

Также может использоваться оператор RETURN . Оператор LOOP может иметь метки.

В приведенной ниже процедуре строки вставляются в таблицу ‘ number ‘ до тех пор, пока х меньше, чем num ( число заданное пользователем через параметр IN ). Каждый раз сохраняется случайное число:

Теперь выполните MySQL хранимую процедуру:

MySQL: оператор REPEAT

REPEAT исполняет операторы до тех пор, пока выполняется условие. Условие проверяется каждый раз, когда достигается конец оператора:

список_операторов — список из одного или нескольких операторов, каждый из которых разделяется точкой с запятой ( ; ).
условие_поиска — выражение.

Оператор REPEAT может иметь метки.

Четные числа — это числа, которые могут быть разделены на 2 без остатка. В следующей процедуре пользователь задает число через параметр IN и получает сумму четных чисел от 1 до установленного числа:

Теперь выполните хранимую процедуру MySQL :

MySQL: оператор RETURN

Оператор RETURN завершает выполнение хранимой процедуры MySQL и возвращает агенту, вызвавшему функцию, значение expr . В хранимой функции должен содержаться, по крайней мере, один оператор RETURN . Если функция имеет несколько точек выхода, она может содержать больше одного RETURN . Синтаксис следующий:

Этот оператор не используется в хранимых процедурах или триггерах. Вместо него применяется оператор LEAVE .

MySQL: оператор WHILE

Оператор WHILE выполняет операторы до тех пор, пока выполняется условие. Условие проверяется каждый раз, когда достигается конец цикла. Каждый оператор заканчивается точкой с запятой ( ; ). Синтаксис следующий:

Оператор WHILE может иметь метки.

Нечетные числа — это числа, которые не делятся на 2 без остатка. В следующей процедуре пользователь передает через параметр IN число и получает сумму нечетных чисел от 1 до заданного числа:

Теперь выполните MySQL хранимую процедуру:

MySQL: ALTER PROCEDURE

Используется для изменения характеристик хранимой процедуры MySQL . В операторе ALTER PROCEDURE может быть указано более одного значения для изменения. Но с его помощью нельзя изменить параметры или тело хранимой процедуры. Чтобы внести такие изменения, необходимо удалить и заново создать процедуру с помощью операторов DROP PROCEDURE и CREATE PROCEDURE . Синтаксис следующий:

Для выполнения этого оператора нужно иметь привилегию ALTER ROUTINE . По умолчанию эта привилегия автоматически предоставляется создателю процедуры. В предыдущей процедуре « my_proc_WHILE » раздел комментариев был пуст. Для ввода нового комментария или изменения предыдущего используйте следующую команду:

Можно проверить результат с помощью команды SHOW CREATE PROCEDURE , которую мы рассматривали ранее.

MySQL: DROP PROCEDURE

Используется для сброса в MySQL вызванной хранимой процедуры или функции. После чего указанная подпрограмма удаляется с сервера. Для этого нужно иметь привилегию ALTER ROUTINE . Если системная переменная automatic_sp_privileges включена, эта привилегия и привилегия EXECUTE автоматически предоставляются во время создания подпрограммы и сбрасываются во время удаления подпрограммы:

Блок IF EXISTS — это расширение MySQL . Он предотвращает возникновение ошибки, если процедура или функция не существует. Создается предупреждение, которое можно просмотреть с помощью SHOW WARNINGS . Например:

Можно проверить результат с помощью команды SHOW CREATE PROCEDURE , которую мы рассматривали ранее.

MySQL: курсоры

Курсор представляет собой структуру управления, которая позволяет обрабатывать записи в базе данных. Курсоры используются для обработки отдельных строк, возвращаемых в ответ на запросы системой базы данных. Курсор включает строки в набор результатов, чтобы последовательно их обработать.

В процедурах SQL курсор позволяет определить результирующий набор ( набор строк данных ) и выполнить сложную логику построчно. Используя те же механизмы, процедура SQL также может определить набор результатов и вернуть его непосредственно вызывающему агенту или в клиентское приложение.

MySQL хранимые процедуры поддерживают курсоры. Синтаксис тот же, что и для встроенного SQL . Курсоры имеют следующие свойства:

  • Asensitive: сервер может или не может создавать копию таблицы результатов;
  • Read only: не обновляемые;
  • Nonscrollable: обработка может производиться только в одном направлении, при этом пропуск строк не допускается.

Чтобы использовать курсор в процедурах MySQL , нужно сделать следующее:

  • Объявить курсор;
  • Открыть курсор;
  • Извлечь данные в переменные;
  • Закрыть курсор.

Объявление курсора

Следующий оператор объявляет курсор и связывает его с оператором SELECT . Он извлекает строки, которые будут перемещаться с помощью курсора:

Открытие курсора

После объявления мы открываем объявленный курсор:

Выборка данных в переменные

FETCH выбирает строки для оператора SELECT , связанного с указанным курсором ( который должен быть открыт ), и перемещает указатель курсора. Если строка существует, то выбранные столбцы сохраняются в указанных переменных. Число столбцов извлекаемых SELECT должно соответствовать количеству выходных переменных, указанных в FETCH :

Закрытие курсора

Этот оператор закрывает ранее открытый курсор. Если курсор не открыт, возникает ошибка:

Хранимая процедура MySQL начинается с объявления трех переменных. При этом порядок имеет значение. Первыми объявляются переменные. После этого объявляются условия, затем – курсоры и обработчики. Если вы поместите их в неправильном порядке, то получите сообщение об ошибке:

Теперь выполните процедуру:

Управление доступом для встроенных программ


Для хранимых процедур MySQL и представлений с помощью привилегий задаются правила использования и выполнения. Эти привилегии управляются атрибутом DEFINER , и, если таковая имеется, характеристикой SQL SECURITY .

Все процедуры, функции, триггеры и представления могут иметь атрибут DEFINER , который указывает на аккаунт MySQL . Если атрибут DEFINER в определении отсутствует, учетной записью по умолчанию является пользователь, который создает объект.

MySQL использует следующие правила для управления атрибутом объекта DEFINER :

  • Можно указать в качестве значения DEFINER другого пользователя, только если у вас есть привилегия SUPER ;
  • Если вы не имеете привилегии SUPER , единственное допустимое значение — это ваша учетная запись, указанная явно с помощью CURRENT_USER . Нельзя указать для DEFINER другого пользователя;
  • Для подпрограммы или представления используйте в определении объекта SQL SECURITY INVOKER , чтобы они могли использоваться только пользователями с соответствующими правами;
  • Если вы создаете хранимую процедуру MySQL с включением DEFINER через пользовательскую запись с привилегией SUPER , то задавайте атрибут DEFINER , указывающий на пользователя с привилегиями, необходимыми для операций, выполняемых с объектом. Указывайте в DEFINER аккаунт с более широкими привилегиями только, когда это абсолютно необходимо;
  • Администраторы могут лишить пользователей права указывать более привилегированные пользовательские записи в DEFINER , не предоставляя им привилегию SUPER ;
  • Объект с DEFINER-контекстом должен создаваться с учетом того, что он может получить доступ к данным, на которые вызывающий пользователь не имеет никаких привилегий. В некоторых случаях можно не допустить ссылки на эти объекты, не предоставляя неавторизованным пользователям определенные привилегии;
  • MySQL хранимая процедура или функция не может быть связана с пользователем, который не имеет привилегию EXECUTE ;
  • Представление не может быть связано с пользователем, который не имеет соответствующих привилегией ( SELECT на выборку данных, INSERT для вставки данных и так далее ).

Данная публикация представляет собой перевод статьи « MySQL Stored Procedure » , подготовленной дружной командой проекта Интернет-технологии.ру

5.2.3. Синтаксис DROP PROCEDURE и DROP FUNCTION

5.2.3. Синтаксис DROP PROCEDURE и DROP FUNCTION

Эта инструкция используется, чтобы удалить сохраненную процедуру или функцию. То есть определенная подпрограмма будет удалена с сервера. Начиная с MySQL 5.0.3, Вы должны иметь привилегию ALTER ROUTINE для подпрограммы. Такая привилегия предоставляется автоматически создателю.

Предложение IF EXISTS является расширением MySQL. Это предотвращает ошибку, если процедура или функция не существует. Будет произведено предупреждение, которое может просматриваться с помощью команды SHOW WARNINGS.

Похожие главы из других книг

6.5.3. Действие DROP

6.5.3. Действие DROP Данное действие просто «сбрасывает» пакет и iptables «забывает» о его существовании. «Сброшенные» пакеты прекращают свое движение полностью, т.е. они не передаются в другие таблицы, как это происходит в случае с действием ACCEPT. Следует помнить, что данное

Удаление таблиц и индексов с помощью предложения DROP

Удаление таблиц и индексов с помощью предложения DROP Удалять элементы базы данных можно с помощью предложения DROP. Например, чтобы удалить таблицу, используйте приведенную ниже команду SQL.DROP TABLE tblRegionС помощью предложения DROP можно также удалить индекс в таблице, как

Drop Shadow (Отбрасывать тень)

Drop Shadow (Отбрасывать тень) Drop Shadow (Отбрасывать тень) – эффект, позволяющий имитировать тень, отбрасываемую элементом, находящимся на текущем слое. Выберите данный эффект в списке эффектов, после чего появляется окно настройки тени (рис. 8.2), где мы можем задать такие

5.2.1. Синтаксис CREATE PROCEDURE и CREATE FUNCTION

5.2.2. Синтаксис ALTER PROCEDURE и ALTER FUNCTION

6.2. Синтаксис DROP TRIGGER

6.2. Синтаксис DROP TRIGGER DROP TRIGGER [ schema_name .] trigger_nameЭто уничтожает триггер. Имя базы данных опционально. Если оно не задано, триггер удаляется из заданной по умолчанию базы данных, Вызов DROP TRIGGER был добавлен в MySQL 5.0.2. Использование требует привилегии SUPER.Обратите внимание: До MySQL

7.3. Синтаксис DROP VIEW

7.3. Синтаксис DROP VIEW DROP VIEW [IF EXISTS]view_name [, view_name] …[RESTRICT | CASCADE]DROP VIEW удаляет один или большее количество view. Вы должны иметь привилегию DROP для каждого view. Если любой из view, именованных в списке параметров не существует, MySQL возвращает индикацию ошибки с именем, которые не

8.2.3. Синтаксис DROP EVENT

Функция function-available

Процедуры типа Function

Процедуры типа Function Процедура типа Function, в принципе, работает так же, как и процедура типа Sub, но в данном случае ее главная задача — вычисление некоторого значения. Когда процедура типа Function завершит свою работу, она возвратит это значение в вызывающую процедуру, которая

Отображение тени («Drop Shadow»)

Отображение тени («Drop Shadow») С помощью этого эффекта мы можем добавить к выделенному фрагменту тень. Правда, она будет статичной, не анимированной.Чтобы применить этот эффект к выделенному фрагменту изображения, нужно выбрать пункт Drop Shadow подменю Effects подменю Timeline Effects меню

DROP TABLE

DROP TABLE Используйте оператор DROP TABLE для удаления таблицы и ее данных из базы данных.Это полное удаление, оно не может быть отменено после подтверждения транзакции.DROP TABLE имя;Следующий оператор удаляет таблицу PERSONNEL:DROP TABLE

EXTERNAL FUNCTION DIRECTORY

EXTERNAL FUNCTION DIRECTORY Параметры в ibconfig EXTERNAL_FUNCTION_DIRECTORY EXTERN

Объект Function

Объект Function С помощью объекта Function можно манипулировать функцией как объектом. Этот объект содержит ряд специфичных методов и свойств (табл. 11.21 и 11.22).Таблица 11.21. Свойства объекта Function Таблица 11.22. Методы объекта Function Синтаксис определения объекта Function выглядит

Глава 9. Технология «drag-and-drop»

Глава 9. Технология «drag-and-drop» Технология «drag-and-drop» («перетащить и отпустить») является современным и интуитивным способом передачи информации внутри одного приложения или между разными приложениями. Она часто является дополнением к операциям с буфером обмена по

Обеспечение поддержки технологии «drag-and-drop»

Обеспечение поддержки технологии «drag-and-drop» Технология «drag-and-drop» состоит из двух действий: перетаскивание «захваченных» объектов и их «освобождение». Виджеты в Qt могут использоваться в качестве переносимых объектов, в качестве места отпускания этих объектов или в обоих

Введение в хранимые процедуры MySQL 5

В MySQL 5 есть много новых функций, одной из самых весомых из которых является создание хранимых процедур. В этом уроке я расскажу о том, что они из себя представляют, а также о том, как они могут облегчить вам жизнь.

Введение

Хранимая процедура — это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.

Причина их создания ясна и подтверждается частым использованием. С другой стороны, если вы поговорите с теми, кто работает с ними нерегулярно, то мнения разделятся на два совершенно противоположных фланга. Не забывайте об этом.

  • Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
  • Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
  • Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
  • Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

Против

  • Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая — на клиентской.
  • Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
  • Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
  • Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL — это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.

Кстати, я использую элементарную структуру таблиц, чтобы вам было легче разобраться в этой теме. Я ведь рассказываю о хранимых процедурах, а они достаточно сложны, чтобы вникать еще и в громоздкую структуру таблиц.

Шаг 1: Ставим ограничитель

Ограничитель — это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.

Шаг 2: Как работать с хранимыми процедурами

Создание хранимой процедуры

Первая часть кода создает хранимую процедуру. Следующая — содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.

Названия хранимых процедур чувствительны к регистру. Вам также нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.

4 характеристики хранимой процедуры:

  • Language: в целях обеспечения переносимости, по умолчанию указан SQL.
  • Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию — NOT DETERMINISTIC.
  • SQL Security: во время вызова идет проверка прав пользователя. INVOKER — это пользователь, вызывающий хранимую процедуру. DEFINER — это “создатель” процедуры. Значение по умолчанию — DEFINER.
  • Comment: в целях документирования, значение по умолчанию — «»

Вызов хранимой процедуры

Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.

Изменение хранимой процедуры

В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.

Удаление хранимой процедуры

Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.

Шаг 3: Параметры

Давайте посмотрим, как можно передавать в хранимую процедуру параметры.

  • 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): один параметр, одновременно входящий и возвращаемый.

Естественно, вы можете задавать несколько параметров разных типов.

Пример параметра IN

Пример параметра OUT

Пример параметра INOUT

Шаг 4: Переменные

Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.

Синтаксис объявления переменной выглядит так:

Давайте объявим несколько переменных:

Работа с переменными

Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:

Шаг 5: Структуры управления потоками

MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.

Конструкция IF

С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:

Конструкция CASE

CASE — это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.

Конструкция WHILE

Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Вот пример цикла:

Шаг 6: Курсоры

Курсоры используются для прохождения по набору строк, возвращенному запросом, а также обработки каждой строки.

MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.

В этом примере мы проведем кое-какие простые операции с использованием курсора:

У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:

  • Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
  • Доступен только для чтения: курсоры нельзя изменять.
  • Без перемотки: курсор способен проходить только в одном направлении — вперед, вы не сможете пропускать строки, не выбирая их.

Заключение

В этом уроке я ознакомил вас с основами работы с хранимыми процедурами и с некоторыми специфическими свойствами, связанными с ней. Конечно, вам нужно будет углубить знания в таких областях, как безопасность, выражения SQL и оптимизация, прежде чем стать настоящим гуру MySQL процедур.

Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.

Данный урок подготовлен для вас командой сайта ruseller.com
Источник урока: www.net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/
Перевел: Станислав Протасевич
Урок создан: 7 Июля 2011
Просмотров: 202327
Правила перепечатки

5 последних уроков рубрики «Разное»

Как разместить свой сайт на хостинге? Правильно выбранный хороший хостинг — это будущее Ваших сайтов


Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.

Разработка веб-сайтов с помощью онлайн платформы Wrike

Создание вебсайта — процесс трудоёмкий, требующий слаженного взаимодействия между заказчиком и исполнителем, а также между всеми членами коллектива, вовлечёнными в проект. И в этом очень хорошее подспорье окажет онлайн платформа Wrike.

20 ресурсов для прототипирования

Подборка из нескольких десятков ресурсов для создания мокапов и прототипов.

Топ 10 бесплатных хостингов

Небольшая подборка провайдеров бесплатного хостинга с подробным описанием.

Быстрая заметка: массовый UPDATE в MySQL

Ни для кого не секрет как в MySQL реализовать массовый INSERT, а вот с UPDATE-ом могут возникнуть сложности. Чтобы не прибегать к манипуляциям события ON_DUPLICATE можно воспользоваться специальной конструкцией CASE … WHEN … THEN.

Илон Маск рекомендует:  Обработка строк в рнр

Как получить код хранимой процедуры

Как получить код хранимой процедуры

06.12.2011, 10:19

Как можно получить список параметров хранимой процедуры?
Добрый день! Хочу из приложения на Java вызвать любую хп, которая хранится на sql server (MSSQL.

Как вывести данные с использованием хранимой процедуры
Здравствуйте. Вот разбираю хранимые процедуры и не могу понять в чем ошибка. Есть такая процедура.

Как средствами хранимой процедуры вывести массив?
Осуществляю поиск по ключевому слову с помощью хранимой процедуры. Хранимая процедура должна будет.

Вернуть поле таблицы как лист с хранимой процедуры
Возможно ли вернуть с хранимой процедуры данные типа SELECT AS TABLE( nvarchar(20)) , .

Создание хранимой процедуры, которая создает хранимые процедуры
Написать хранимую процедуру SQL , которая будет создавать хранимые процедуры(любые) для БД.

Передача табличных данных из хранимой процедуры

Речь пойдет о методах получения результатов работы процедуры в виде таблиц для последующей работы с ними в SQL. Думаю, большинство здесь изложенного может пригодиться только в приложениях со сложной SQL логикой и объемными процедурами. Не берусь утверждать, что данные методы самые эффективные. Это всего лишь то, что я использую в своей работе. Всё это работает под Microsoft SQL Server 2008.
Тем, кто знаком с темой предлагаю пролистать пост до пятого метода.

Пусть процедура, из которой нам нужно получить данные будет такой:

1 Метод

Один из самых простых методов. Используем конструкцию insert . exec .

Плюсы и минусы:
  • Передаваемые поля перечисляются 2 раза (это внутренний select , внешнее создание таблицы и insert ). Плюс перечисление полей происходит при каждом новом аналогичном вызове. (Я добавляю данный критерий, т.к. при большом количестве правок и множестве мест вызова процедуры, процесс изменения выводимых данных становится очень трудоемким)
  • Имеет серьезное ограничение – мы можем получить только одну таблицу
  • Для работы процедуры в режиме простого вывода не требуются дополнительные действия, достаточно запустить exec Proc1 без insert

2 Метод

С помощью записи в ранее созданную таблицу. Здесь придется добавлять insert в процедуру:

По сути мы перенесли строку insert внутрь процедуры.

Плюсы и минусы:
  • Передаваемые поля перечисляются 2 раза, и еще по одному перечислению на каждое новое использование
  • Для работы процедуры в режиме простого вывода потребуется либо писать отдельную процедуру, выводящую принятые от Proc1 таблицы, либо определять, когда их выводить внутри Proc1 . Например, по признаку не существования таблицы для вставки:

Я не рассматриваю возможность передачи через постоянные таблицы, т.к. если это требуется, то задача не относиться к данной теме. Если же нет, то мы получаем лишние проблемы с блокировкой и идентификацией между сессиями.

3 Метод

По сути, является доработкой второго метода. Чтобы упростить поддержку создаем пользовательский тип таблицы. Выглядит это примерно так:

Плюсы и минусы:
  • Передаваемые поля перечисляются 2 раза, при этом каждое новое использование не добавляет сложности
  • Для организации непосредственного вывода результата также требуются дополнительные действия
  • Есть небольшие сложности с созданием индексов и ограничений, т.к. их мы не можем создать с помощю конструкции select . into

4 Метод

Усложнение третьего метода, позволяющее создавать таблицу с ограничениями и индексами. В отличии от предыдущего работает под Microsoft SQL Server 2005.

Однако обычно временная колонка delmy не используется, вместо неё таблица создается просто с одним первым столбцом (здесь с p1).

Плюсы и минусы:
  • Передаваемые поля перечисляются 2 раза, при этом каждое новое использование не добавляет сложности
  • Для непосредственного вывода результата также требуются дополнительные действия
  • Неожиданно обнаружилось, что иногда, по непонятным причинам, возникают блокировки на конструкции alter table #t1 , и процесс ожидает полного завершения Proc1 (не Proc1_AlterTable !) параллельного запроса. Если кто-нибудь знает, с чем это связанно — поделитесь, буду рад услышать:)

5 Метод

Этот метод использует предварительно созданные процедуры. Он основан на включении динамического SQL-запроса в запускаемую процедуру. Однако является достаточно простым в использовании.

Для его использования процедуры необходимо обработать следующим образом:

1. В начало процедуры включить строки:

2. Все выводящие select’ы процедуры переделать на создание временных таблиц начинающихся с #Output (Например into #Output , into #Output5 , into #OutputMySelect ). Если процедура не создает результирующего набора, то действие не требуется
3. В конец процедуры включить строку:

Для нашего примера мы получаем:

Запуск осуществляется так:

Поскольку генерируемый SQL это не всегда хорошо, то приведенный пример лучше подходит для небольших инструкций. Если кода достаточно много, то можно либо вынести его в отдельную процедуру и из динамической части осуществлять только exec вызов, либо перезалить данные в новые временные таблицы. В последнем случае, конечно, происходит ещё одно «лишнее» копирование, но часто бывает так, что на этом этапе мы можем предварительно сгруппировать результат и выбрать только нужные поля для дальнейшей обработки (например, если в каком-либо случае не требуется все возвращаемые данные).

Функции util.InclusionRun передаются 3 параметра:

  • @sql – SQL-скрипт, который выполниться внутри вызываемой процедуры
  • @notShowOutput – если = 1, то блокировать вывод таблиц, начинающихся с #Output
  • @replaceableTableName – (по умолчанию = ‘#Output’ ) задать префикс в имени таблиц используемых в @sql , для замены его на соответствующую #Output* таблицу в скрипте. Например, если задать #InclusionOutput , и в процедуре созданы две таблицы #Output55 и #Output0A , то в @sql можно обратиться к #Output55 как к #InclusionOutput1 , а к #Output0A как к #InclusionOutput2

Работа построена таким образом, что запуск Proc1 , без предварительного запуска util.InclusionRun приводит к естественной работе процедуры с выводом всех данных, которые она выводила до обработки.

Нюансы использования:
  • Накладывает ограничения на использование инструкции return в процедуре, т.к. перед ней необходим запуск util.InclusionEnd
  • Выводящие результат select’ы из запускаемых процедур выводят результат раньше, чем даже те #Output-таблицы, которые были созданы до их вызова (это логично, т.к. вывод происходит только в util.InclusionEnd )
Плюсы и минусы:
  • Передаваемые поля перечисляются один раз, при этом каждое новое использование не добавляет сложности
  • Для непосредственного вывода результата не требуется никаких действий
  • Необходимо помнить и учитывать нюансы использования
  • Из-за дополнительных процедур выполняется больше инструкций, что может снизить быстродействие при частых вызовах (я думаю, что при запуске реже одного раза в секунду этим можно пренебречь)
  • Возможно, может усложнить понимание кода для сотрудников не знакомых с данным методом: процедура приобретает два exec-вызова и неочевидность того, что все #Output -таблицы будут выведены
  • Позволяет легко организовать модульное тестирование без внешних инструментов

Введение в базы данных

Алексей Федоров, Наталия Елманова

В предыдущей статье данного цикла мы рассмотрели, как можно извлечь данные из таблиц, модифицировать их структуру, создавать, модифицировать и удалять базы данных и объекты, в них содержащиеся. В настоящей статье мы поговорим более подробно об объектах, характерных для серверных СУБД: представлениях, триггерах и хранимых процедурах.

В первой статье данного цикла, опубликованной в № 3’2000 нашего журнала, мы отмечали, что большинство современных серверных СУБД поддерживают представления, триггеры и хранимые процедуры. Представления также поддерживаются и многими настольными СУБД, например Access, dBase, Clipper.

Следует отметить, что триггеры и хранимые процедуры обычно пишутся на языках программирования, представляющих собой процедурные расширения языка SQL. Эти расширения содержат операторы, позволяющие описывать алгоритмы, например do…while, if…then…else, отсутствующие в самом языке SQL (если вы помните, SQL — непроцедурный язык, и на нем можно сформулировать задание, но нельзя описывать алгоритмы его выполнения). В отличие от языка SQL, подчиняющегося стандарту, его процедурные расширения никак не стандартизованы, и разные СУБД используют разные синтаксические конструкции для реализации одних и тех же алгоритмических конструкций, но обсуждение различий в синтаксисе расширений SQL для разных СУБД выходит за рамки данной статьи.

Для иллюстрации того, как можно использовать представления, триггеры и хранимые процедуры, мы выбрали Microsoft SQL Server 7.0 и базу данных NorthWind, входящую в комплект поставки этой СУБД.

Прежде чем выполнять примеры, обратите внимание на то, что реализация и способ хранения триггеров и хранимых процедур в используемой вами СУБД могут отличаться от приведенных в этой статье. Кроме того, для создания серверных объектов следует иметь соответствующие разрешения, предоставляемые администратором базы данных.

Отметим также, что некоторые ODBC-драйверы не поддерживают вызов хранимых процедур из клиентских приложений, даже если таковые поддерживаются самой СУБД. Однако в этом случае хранимые процедуры по-прежнему могут быть вызваны из триггеров.

Давайте начнем с представлений, затем обсудим хранимые процедуры и закончим главу обзором триггеров.

Представления

Представление — это виртуальная таблица, обычно содержащая набор колонок одной или нескольких таблиц. В действительности представление содержит не данные, а лишь SQL-запрос типа SELECT, указывающий, какие именно данные и из каких таблиц нужно взять при обращении к этому представлению. С этой точки зрения представление — это хранимый запрос.

В большинстве случаев представления используются для обеспечения безопасности данных. Например, некоторые категории пользователей могут иметь доступ к представлению, но не к таблицам, данные которых его формируют; кроме того, SQL-запрос может содержать параметр USER (имя, под которым зарегистрировался пользователь), и в этом случае данные, доступные при обращении к представлению, будут зависеть от имени пользователя.

Ниже перечислены основные характеристики представлений:

  • представления ведут себя подобно таблицам;
  • представления не содержат данных;
  • представления могут использовать данные более чем из одной таблицы.

Для создания представления мы можем использовать SQL-предложение CREATE VIEW, для его модификации — предложение ALTER VIEW, а для удаления его — предложение DROP VIEW.

Мы начнем с оператора CREATE VIEW, позволяющего создать представление для текущей базы данных.

Предложение CREATE VIEW

Синтаксис предложения для создания представления напоминает SQL-предложение SELECT с несколькими дополнительными ключевыми словами. Ниже приведен его упрощенный синтаксис:

Аргумент view_name указывает на имя представления. Ключевое слово [WITH ENCRYPTION], используемое в Microsoft SQL Server, позволяет скрыть исходный текст предложения CREATE VIEW в таблице syscomments.

Ключевое слово AS указывает, какой запрос SELECT реально будет выполняться при обращении к представлению. Обратите внимание на то, что этот запрос не может содержать ключевых слов ORDER BY, COMPUTE или COMPUTE BY, INTO и не может ссылаться на временную таблицу.

Для модификации созданного ранее представления следует использовать предложение ALTER VIEW, кратко описанное в следующем разделе.

Предложение ALTER VIEW

Предложение ALTER VIEW имеет тот же синтаксис, что и предложение CREATE VIEW. Его основное назначение — внести изменения в уже имеющееся представление.

Если представление нам больше не нужно, мы можем удалить его с помощью оператора DROP VIEW, описанного ниже.

Предложение DROP VIEW

Это предложение используется для удаления представления из базы данных. Обратите внимание на то, что при удалении таблицы из базы данных удаляются и все представления, ссылающиеся на нее. Используя это предложение, мы должны указать имя удаляемого представления. После того как представление удалено, вся информация о нем удаляется из системных таблиц.

Еще один случай, когда представление нужно удалить, может возникнуть при условии, что структура таблиц, на которых оно основано, изменилась после создания представления. В этом случае можно удалить представление и затем создать его заново с помощью предложения CREATE VIEW.

Создание и использование представлений

Предложение CREATE VIEW используется для создания представлений, позволяющих извлекать данные, удовлетворяющие определенным требованиям. Представление создается в текущей базе данных и хранится как ее отдельный объект.

Наилучший способ создать представление — создать запрос SELECT и, проверив его, добавить недостающую часть предложения CREATE VIEW. Давайте рассмотрим исходный текст представления Products by Category в базе данных NorthWind (листинг 1).

Первая строка, выделенная жирным шрифтом, представляет собой то, чем отличается SQL-предложение для создания представления от обычного запроса SELECT, выполняющего работу по выбору данных. Предложение SELECT, содержащееся в этом представлении, выбирает поля из двух таблиц — поле CategoryName из таблицы CATEGORIES и поля ProductName, QuantityPerUnit, UnitsInStock, Discontinued из таблицы PRODUCTS. После этого данные двух таблиц связываются по полю CategoryID, и только те продукты, которые еще имеются на складе (см. критерий после ключевого слова WHERE), включаются в результирующий набор данных. Результат обращения к этому представлению показан на рис. 1.

Теперь давайте создадим представление, показывающее все территории восточного региона. Это представление базируется на следующем запросе (листинг 2).

Убедившись в том, что предложение SELECT возвращает результаты, которые нам нужны, мы добавляем оператор CREATE VIEW и присваиваем создаваемому представлению имя EASTTERR (листинг 3).

Вместо создания текста представления вручную можно использовать визуальные инструменты, обычно входящие в состав СУБД. На рис. 2 показано, как то же самое представление может быть создано с помощью инструмента View Designer, который является составной частью Enterprise Manager, входящего в Microsoft SQL Server.

Верхняя часть View Designer позволяет указать, как связаны таблицы и какие поля будут отображаться в представлении. Ниже можно указать псевдонимы таблиц и полей, ограничения на их значения, способ отображения. Далее приведены исходный текст представления и результаты его выполнения.

Прежде чем мы закончим краткий обзор представлений, поговорим немного о том, как получить дополнительную информацию о них. В Microsoft SQL Server 7.0 мы можем использовать следующие системные хранимые процедуры:

  • для получения сведений о представлении можно использовать системную хранимую процедуру sp_help. Например, sp_help EastTerr вернет сведения о только что созданном представлении;
  • для получения исходного текста представления можно использовать хранимую процедуру sp_helptext;
  • для того чтобы найти список таблиц, от которого зависит представление, можно использовать системную хранимую процедуру sp_depends;
  • для переименования представления можно использовать системную хранимую процедуру sp_rename.

В данном разделе мы рассмотрели, как использовать представления для получения данных, удовлетворяющих тем или иным критериям. Однако вернемся к последнему примеру. В базе данных NorthWind имеется четыре региона, и для получения списка территорий всех регионов нам нужно четыре разных представления. Эту задачу можно было бы упростить, если бы мы могли передать значение RegionID в качестве параметра. Это можно сделать с помощью хранимой процедуры, о чем мы поговорим в следующем разделе.

Хранимые процедуры

Хранимая процедура — это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Хранимые процедуры могут принимать и возвращать параметры. Когда пользователь создает хранимую процедуру, сервер компилирует ее и помещает в разделяемый кэш, после чего скомпилированный код может быть применен несколькими пользователями. Когда приложение использует хранимую процедуру, оно передает ей параметры, если таковые требуются, и сервер выполняет процедуру без перекомпиляции.

Хранимые процедуры позволяют повысить производительность приложений. Во-первых, по сравнению с обычными SQL-запросами, посылаемыми из клиентского приложения, они требуют меньше времени для подготовки к выполнению, поскольку они уже скомпилированы и сохранены. Во-вторых, сетевой трафик в этом случае также меньше, чем в случае передачи SQL-запроса, так как по сети передается меньшее количество данных. Рис. 3 иллюстрирует вызов хранимой процедуры клиентским приложением.

Хранимые процедуры автоматически перекомпилируются, если с объектами, на которые они влияют, произведены какие-либо изменения; иными словами, они всегда актуальны. Как уже было сказано выше, хранимые процедуры могут принимать параметры, что позволяет разным приложениям использовать одну и ту же процедуру, применяя различные наборы входных данных.

Хранимые процедуры обычно используются для поддержки ссылочной целостности данных и реализации бизнес-правил. В последнем случае достигается дополнительная гибкость, поскольку, если бизнес-правила изменяются, можно изменить только текст процедуры, не изменяя клиентских приложений.

Для создания, изменения и удаления процедур существуют специальные SQL-предложения — CREATE PROCEDURE, ALTER PROCEDURE и DROP PROCEDURE. Мы рассмотрим их в следующем разделе.

Предложение CREATE PROCEDURE

Предложение CREATE PROCEDURE используется для создания хранимой процедуры. Оно имеет следующий упрощенный синтаксис:

Аргумент proc_name устанавливает имя хранимой процедуры, которое должно быть уникально в рамках текущей базы данных. Аргумент @parameter определяет параметр процедуры. В предложении CREATE PROCEDURE можно определить один или более параметров. Если для параметра нет значения по умолчанию, он должен быть передан пользователем (или клиентским приложением) при вызове процедуры. В Microsoft SQL Server 7.0 число параметров хранимой процедуры не должно превышать 1024; по умолчанию они могут иметь NULL-значения.

Отметим, однако, что некоторые универсальные механизмы доступа к данным могут накладывать дополнительные ограничения на число параметров хранимых процедур. Например, BDE-драйвер для Oracle 8 способен работать только с процедурами, число параметров которых не превышает 10.


Аргумент data_type указывает тип данных для параметра. Ключевое слово default может быть использовано для установки значений по умолчанию — это может быть константа или NULL. Если указано значение по умолчанию, процедура может быть вызвана без указания значения параметра. Если процедура использует параметр с ключевым словом LIKE, ее значение по умолчанию может содержать групповые символы (%, _, [] и [^]).

Ключевое слово OUTPUT показывает, что это возвращаемый параметр.

Ключевое слово AS указывает действие, которое процедура должна выполнить, в виде любого количества SQL-предложений и предложений на процедурном расширении SQL, характерном для данного сервера.

Процедура, созданная с помощью предложения CREATE PROCEDURE, будет сохранена в текущей базе данных. В Microsoft SQL Server имена процедур содержатся в системной таблице sysobjects, а исходный текст — в таблице syscomments.

Для изменения созданной ранее хранимой процедуры следует использовать предложение ALTER PROCEDURE, кратко описанное в следующем разделе.

Предложение ALTER PROCEDURE

Это SQL-предложение применяется для изменения уже существующей хранимой процедуры. Предложение ALTER PROCEDURE имеет тот же синтаксис, что и предложение CREATE PROCEDURE. Один из примеров, когда нужно изменить хранимую процедуру, — зашифровать ее исходный текст после того, как процедура отлажена, с целью скрыть ее исходный текст от других пользователей.

Если хранимая процедура больше не нужна, мы можем удалить ее с помощью предложения DROP PROCEDURE, которое будет обсуждаться ниже.

Предложение DROP PROCEDURE

Это предложение используется для удаления хранимых процедур из базы данных. Предложение DROP PROCEDURE принимает один аргумент — имя удаляемой процедуры.

При удалении хранимой процедуры сведения о ней удаляются из системных таблиц sysobjects и syscomments.

Создание и использование хранимых процедур

В разделе, посвященном представлениям, мы обращали внимание на то, что было бы удобно, если бы мы могли передать в представление параметр, содержащий значение RegionID для выбора одного из четырех регионов в базе данных NorthWind. Давайте еще раз рассмотрим запрос, возвращающий список территорий региона:

Чтобы выбрать другой регион, нам нужно изменить условие в предложении WHERE в последней строке запроса. Следовательно, если мы используем переменную (назовем ее RegID), мы сможем выбрать один из четырех регионов без изменения других частей запроса.

В базе данных NorthWind четыре региона с номерами от 1 до 4. Это означает, что переменная RegID должна быть целого типа. Код хранимой процедуры приведен ниже:

Обратите внимание на то, что мы оставили почти весь текст запроса SELECT нетронутым (он выделен курсивом) и только добавили предложение CREATE PROCEDURE с именем вновь созданной хранимой процедуры (в первой строке), объявление параметра (во второй строке) и ключевое слово AS, указывающее на начало предложений, реально выполняющих действия.

Результат выполнения созданной процедуры в SQL Server Query Analyzer для Reg >

Очевидно, что мы можем применять хранимые процедуры не только для реализации расширенных версий представлений или «интеллектуальных» запросов SELECT. Хранимые процедуры предоставляют механизмы, позволяющие автоматизировать многие рутинные задачи.

В Microsoft SQL Server 7.0 мы также можем использовать системные хранимые процедуры для работы с обычными хранимыми процедурами:

  • sp_stored_procedures — показывает список хранимых процедур;
  • sp_helptext — показывает исходный текст хранимой процедуры;
  • sp_depends — показывает сведения о зависимостях хранимых процедур;
  • sp_procoption — устанавливает опции хранимых процедур или устанавливает их;
  • sp_recompile — перекомпилирует процедуру в момент ее следующего вызова;
  • sp_rename — меняет имя процедуры.

Системные хранимые процедуры

Поскольку мы говорим о Microsoft SQL Server, следует отметить огромное количество системных хранимых процедур, реализованных в нем. Имена системных хранимых процедур начинаются с SP_ или XP_ и хранятся в базе данных master. Выше мы уже описывали некоторые из часто используемых системных хранимых процедур.

В табл. 1 приведен краткий список системных хранимых процедур.

Системные хранимые процедуры можно использовать в клиентских приложениях. Для этого следует обратиться к базе данных master и выбрать имя соответствующей процедуры.

Последний раздел в этой статье посвящен еще одному типу объектов в базах данных — триггерам.

Триггеры

В первой статье данного цикла мы кратко описывали, что такое триггеры и как они используются в базах данных. Дадим определение триггера. Tриггер — это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. В зависимости от того, какое из событий, связанных с изменением данных, инициирует запуск триггера, он называется insert trigger, delete trigger или update trigger.

Некоторые базы данных позволяют создавать разные триггеры для выполнения до и после вставки, удаления или изменения записей. Обратите также внимание на то, что большинство СУБД позволяют создавать несколько триггеров для одного и того же события. В этом случае слеует определить порядок, в котором они будут выполняться.

Триггеры часто используются для подержки ссылочной целостности, каскадного изменения или удаления данных (то есть изменения или удаления всех дочерних записей вместе с родительской), архивирования удаленных или измененных данных, отслеживания изменений или вызова пользователских или системных хранимых процедур.

Поскольку триггер вызывается автоматически самой СУБД (в нашем случае Microsoft SQL Server), его нельзя вызвать из клиентского приложения.

Триггеры могут опосредованно вызывать другие триггеры. Например, если триггер, выполняющийся в данный момент, содержит код, модифицирующий другую таблицу, имеющую собственный триггер, последний будет запущен. В свою очредь, он может вызвать следующий триггер и так далее. Такая последовательность тригеров называется термином nested triggers. Microsoft SQL Server поддерживает до 32 уровней вложения таких триггеров.

Как и многие другие объекты баз данных, триггеры создаются с помощью предложения CREATE. Чтобы изменить триггер, мы используем предложение ALTER, а для удаления триггера — предложение DROP. В последующих разделах мы рассмотрим синтаксис этих предложений и затем создадим триггер, который может быть использован для записи сведений о мдификациях данных.

Предложение CREATE TRIGGER

Это предложение создает новый триггер для определеной таблицы для любого из предложений INSERT, DELETE или UPDATE. Ниже приведен упрощенный синтаксис такого предложения:

Аргумент trigger_name указывает имя триггера. Это имя должно быть уникально для базы данных. Аргумент table_name — имя таблицы, при модификации которой выполняется данный триггер. Таблица должна быть «настоящей» — нельзя указать имя представления вместо таблицы.

Ключевые слова определяют, какие изменения данных активизируют триггер. Как минимум одно из ключевых слов этого списка обязательно должно присутствовать.

Необязательное ключевое слово [WITH ENCRYPTION] может быть использовано для сокрытия исходного кода триггера — в этом случае триггер не будет виден в таблице syscomments.

Ключевое слово AS указывает на действие, которое должен выполнить триггер, в виде любого количества SQL-предложений и предложений на процедурном расширении SQL, характерном для данного сервера. В них можно выполнять любые SQL-предложения, кроме перечисленных в табл. 2.

Обратите внимание на то, что триггеры не должны возвращать пользователю данные.

В предложении CREATE TRIGGER можно использовать две специальные таблицы. Например, таблицы deleted и inserted имеют ту же структуру, что и таблица, для которой определен триггер, и содержат старое и новое значения записей, измененных пользователем. Например, мы можем использовать следующее SQL-предложение для поиска удаленных записей:

SELECT * FROM deleted

В табл. 3 показано содержимое таблиц deleted и inserted для всех возможных изменений данных.

Для изменения имеющегося триггера следует использовать предложение ALTER TRIGGER. Мы поговорим о нем в следующем разделе.

Предложение ALTER TRIGGER

Данное предложение имеет тот же синтаксис, что и предложение CREATE TRIGGER, которое мы обсудим выше. Это предложение применяется для изменения текста имеющегося триггера. Например, если мы решили, что какой-либо триггер, определенный для всех трех типов модификаций данных, теперь не должен выполняться, когда записи удаляются, нам следует использовать тот же самый код, с помощью которого мы создавали триггер, но удалить ключевое слово UPDATE в предложении FOR.

Если триггер больше не нужен, мы можем удалить его из базы данных. Для этого применяется предложение DROP TRIGGER, которое мы рассмотрим ниже.

Предложение DROP TRIGGER

Это предложение удаляет триггер из базы данных. Используя это предложение, нужно указывать имя удаляемого триггера. Обратите внимание на то, что триггер автоматически удаляется при удалении таблицы, для которой он создан.

Создание и использование триггеров

Существует много примеров различных триггеров, поддерживающих ссылочную целостность, выполняющих каскадное удаление и другие задачи. Мы же создадим триггер, который будет использоваться для отслеживания изменений в таблице. Предположим, в компании имеется несколько менеджеров и каждый из них может добавлять, удалять и изменять данные о клиентах. Чтобы знать, кто и когда изменял эти данные, руководитель этих менеджеров должен иметь механизм для получения подобных сведений. Один их способов реализации такого механизма — создание триггера.

Для начала нам нужно добавить к таблице два новых поля, в которых будут содержаться эти сведения. Назовем их UpdatedBy (имя менеджера, обновившего запись последним) и UpdatedWhen (время, когда была изменена запись). Затем создадим триггер с именем KeepTrack. Вот его код:

Как видно из исходного текста триггера, он выполняется после каждой операции INSERT и UPDATE в таблице Customers. Этот триггер будет сохранять имя менеджера (пользователя базы данных) в поле Customers.UpdatedBy и дату и время изменения — в поле Customers.UpdatedWhen. Эти данные извлекаются из временной таблицы inserted.

Как видим, этот триггер позволяет следить за изменениями и вставкой новых записей в таблице.

Перед тем как закончить краткий обзор триггеров, мы должны сообщить, где можно найти сведения об имеющихся триггерах. Таблица sysobjects хранит сведения о триггерах и их типах, а таблица syscomments содержит их исходный текст.

Заключение

В этой части мы рассмотрели несколько типов объектов баз данных — хранимые процедуры, представления и триггеры. Мы узнали следующее:

  • Представление — это виртуальная таблица, обычно создаваемая как подмножество столбцов одной или нескольких таблиц. Для создания представления применяется предложение CREATE VIEW, для модификации — предложение ALTER VIEW, а для удаления — предложение DROP VIEW.
  • Хранимая процедура — это скомпилированный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Для создания хранимой процедуры применяется предложение CREATE PROCEDURE, для изменения — ALTER PROCEDURE, а для удаления — DROP PROCEDURE.
  • Tриггер — это специальный тип хранимой процедуры, которая автоматически вызывается, когда данные в определенной таблице добавляются, удаляются или изменяются с помощью SQL-предложений INSERT, DELETE или UPDATE. Триггеры создаются с помощью предложения CREATE TRIGGER. Для изменения триггера используется предложение ALTER TRIGGER, а для удаления — предложение DROP TRIGGER.

Хранимые процедуры

— это специальный тип пакета инструкций Transact-SQL, созданный, используя язык SQL и процедурные расширения. Основное различие между пакетом и хранимой процедурой состоит в том, что последняя сохраняется в виде объекта базы данных. Иными словами, хранимые процедуры сохраняются на стороне сервера, чтобы улучшить производительность и постоянство выполнения повторяемых задач.

Компонент Database Engine поддерживает хранимые процедуры и системные процедуры. Хранимые процедуры создаются таким же образом, как и все другие объекты баз данных, т.е. при помощи языка DDL. Системные процедуры предоставляются компонентом Database Engine и могут применяться для доступа к информации в системном каталоге и ее модификации.

При создании хранимой процедуры можно определить необязательный список параметров. Таким образом, процедура будет принимать соответствующие аргументы при каждом ее вызове. Хранимые процедуры могут возвращать значение, содержащее определенную пользователем информацию или, в случае ошибки, соответствующее сообщение об ошибке.

Хранимая процедура предварительно компилируется перед тем, как она сохраняется в виде объекта в базе данных. Предварительно компилированная форма процедуры сохраняется в базе данных и используется при каждом ее вызове. Это свойство хранимых процедур предоставляет важную выгоду, заключающуюся в устранении (почти во всех случаях) повторных компиляций процедуры и получении соответствующего улучшения производительности. Это свойство хранимых процедур также оказывает положительный эффект на объем данных, участвующих в обмене между системой баз данных и приложениями. В частности, для вызова хранимой процедуры объемом в несколько тысяч байтов может потребоваться меньше, чем 50 байт. Когда множественные пользователи выполняют повторяющиеся задачи с применением хранимых процедур, накопительный эффект такой экономии может быть довольно значительным.

Хранимые процедуры можно также использовать для следующих целей:

управления авторизацией доступа;

для создания журнала логов о действиях с таблицами баз данных.

Использование хранимых процедур предоставляет возможность управления безопасностью на уровне, значительно превышающем уровень безопасности, предоставляемый использованием инструкций GRANT и REVOKE, с помощью которых пользователям предоставляются разные привилегии доступа. Это возможно вследствие того, что авторизация на выполнение хранимой процедуры не зависит от авторизации на модифицирование объектов, содержащихся в данной хранимой процедуре, как это описано в следующем разделе.

Хранимые процедуры, которые создают логи операций записи и/или чтения таблиц, предоставляют дополнительную возможность обеспечения безопасности базы данных. Используя такие процедуры, администратор базы данных может отслеживать модификации, вносимые в базу данных пользователями или прикладными программами.

Создание и исполнение хранимых процедур

Хранимые процедуры создаются посредством инструкции CREATE PROCEDURE, которая имеет следующий синтаксис:

Параметр schema_name определяет имя схемы, которая назначается владельцем созданной хранимой процедуры. Параметр proc_name определяет имя хранимой процедуры. Параметр @param1 является параметром процедуры (формальным аргументом), чей тип данных определяется параметром type1. Параметры процедуры являются локальными в пределах процедуры, подобно тому, как локальные переменные являются локальными в пределах пакета. Параметры процедуры — это значения, которые передаются вызывающим объектом процедуре для использования в ней. Параметр default1 определяет значение по умолчанию для соответствующего параметра процедуры. (Значением по умолчанию также может быть NULL.)

Опция OUTPUT указывает, что параметр процедуры является возвращаемым, и с его помощью можно возвратить значение из хранимой процедуры вызывающей процедуре или системе.

Как уже упоминалось ранее, предварительно компилированная форма процедуры сохраняется в базе данных и используется при каждом ее вызове. Если же по каким-либо причинам хранимую процедуру требуется компилировать при каждом ее вызове, при объявлении процедуры используется опция WITH RECOMPILE. Использование опции WITH RECOMPILE сводит на нет одно из наиболее важных преимуществ хранимых процедур: улучшение производительности благодаря одной компиляции. Поэтому опцию WITH RECOMPILE следует использовать только при частых изменениях используемых хранимой процедурой объектов базы данных.

Предложение EXECUTE AS определяет контекст безопасности, в котором должна исполняться хранимая процедура после ее вызова. Задавая этот контекст, с помощью Database Engine можно управлять выбором учетных записей пользователей для проверки полномочий доступа к объектам, на которые ссылается данная хранимая процедура.

По умолчанию использовать инструкцию CREATE PROCEDURE могут только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присваивать это право другим пользователям с помощью инструкции GRANT CREATE PROCEDURE.

В примере ниже показано создание простой хранимой процедуры для работы с таблицей Project:

Как говорилось ранее, для разделения двух пакетов используется инструкция GO. Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQL в одном пакете. Хранимая процедура IncreaseBudget увеличивает бюджеты для всех проектов на определенное число процентов, определяемое посредством параметра @percent. В процедуре также определяется значение числа процентов по умолчанию (5), которое применяется, если во время выполнения процедуры этот аргумент отсутствует.

Хранимые процедуры могут обращаться к несуществующим таблицам. Это свойство позволяет выполнять отладку кода процедуры, не создавая сначала соответствующие таблицы и даже не подключаясь к конечному серверу.

В отличие от основных хранимых процедур, которые всегда сохраняются в текущей базе данных, возможно создание временных хранимых процедур, которые всегда помещаются во временную системную базу данных tempdb. Одним из поводов для создания временных хранимых процедур может быть желание избежать повторяющегося исполнения определенной группы инструкций при соединении с базой данных. Можно создавать локальные или глобальные временные процедуры. Для этого имя локальной процедуры задается с одинарным символом # (#proc_name), а имя глобальной процедуры — с двойным (##proc_name).

Локальную временную хранимую процедуру может выполнить только создавший ее пользователь и только в течение соединения с базой данных, в которой она была создана. Глобальную временную процедуру могут выполнять все пользователи, но только до тех пор, пока не завершится последнее соединение, в котором она выполняется (обычно это соединение создателя процедуры).

Жизненный цикл хранимой процедуры состоит из двух этапов: ее создания и ее выполнения. Каждая процедура создается один раз, а выполняется многократно. Хранимая процедура выполняется посредством инструкции EXECUTE пользователем, который является владельцем процедуры или обладает правом EXECUTE для доступа к этой процедуре. Инструкция EXECUTE имеет следующий синтаксис:

За исключением параметра return_status, все параметры инструкции EXECUTE имеют такое же логическое значение, как и одноименные параметры инструкции CREATE PROCEDURE. Параметр return_status определяет целочисленную переменную, в которой сохраняется состояние возврата процедуры. Значение параметру можно присвоить, используя или константу (value), или локальную переменную (@variable). Порядок значений именованных параметров не важен, но значения неименованных параметров должны предоставляться в том порядке, в каком они определены в инструкции CREATE PROCEDURE.

Предложение DEFAULT предоставляет значения по умолчанию для параметра процедуры, которое было указано в определении процедуры. Когда процедура ожидает значение для параметра, для которого не было определено значение по умолчанию и отсутствует параметр, либо указано ключевое слово DEFAULT, то происходит ошибка.

Когда инструкция EXECUTE является первой инструкцией пакета, ключевое слово EXECUTE можно опустить. Тем не менее будет надежнее включать это слово в каждый пакет. Использование инструкции EXECUTE показано в примере ниже:

Инструкция EXECUTE в этом примере выполняет хранимую процедуру IncreaseBudget, которая увеличивает бюджет всех проектов на 10%.

В примере ниже показано создание хранимой процедуры для обработки данных в таблицах Employee и Works_on:

Процедура ModifyEmpId в примере иллюстрирует использование хранимых процедур, как часть процесса обеспечения ссылочной целостности (в данном случае между таблицами Employee и Works_on). Подобную хранимую процедуру можно использовать внутри определения триггера, который собственно и обеспечивает ссылочную целостность.

В примере ниже показано использование в хранимой процедуре предложения OUTPUT:

Данную хранимую процедуру можно запустить на выполнение посредством следующих инструкций:

Эта процедура подсчитывает количество проектов, над которыми занят сотрудник с табельным номером @empId, и присваивает полученное значение параметру ©counter. После удаления всех строк для данного табельного номера из таблиц Employee и Works_on вычисленное значение присваивается переменной @quantityDeleteEmployee.

Значение параметра возвращается вызывающей процедуре только в том случае, если указана опция OUTPUT. В примере выше процедура DeleteEmployee передает вызывающей процедуре параметр @counter, следовательно, хранимая процедура возвращает значение системе. Поэтому параметр @counter необходимо указывать как в опции OUTPUT при объявлении процедуры, так и в инструкции EXECUTE при ее вызове.

Предложение WITH RESULTS SETS инструкции EXECUTE

В SQL Server 2012 для инструкции EXECUTE вводится предложение WITH RESULTS SETS, посредством которого при выполнении определенных условий можно изменять форму результирующего набора хранимой процедуры.

Следующие два примера помогут объяснить это предложение. Первый пример является вводным примером, который показывает, как может выглядеть результат, когда опущено предложение WITH RESULTS SETS:

Процедура EmployeesInDept — это простая процедура, которая отображает табельные номера и фамилии всех сотрудников, работающих в определенном отделе. Номер отдела является параметром процедуры, и его нужно указать при ее вызове. Выполнение этой процедуры выводит таблицу с двумя столбцами, заголовки которых совпадают с наименованиями соответствующих столбцов таблицы базы данных, т.е. Id и LastName. Чтобы изменить заголовки столбцов результата (а также их тип данных), в SQL Server 2012 применяется новое предложение WITH RESULTS SETS. Применение этого предложения показано в примере ниже:

Результат выполнения хранимой процедуры, вызванной таким способом, будет следующим:

Как можно видеть, запуск хранимой процедуры с использованием предложения WITH RESULT SETS в инструкции EXECUTE позволяет изменить наименования и тип данных столбцов результирующего набора, выдаваемого данной процедурой. Таким образом, эта новая функциональность предоставляет большую гибкость в исполнении хранимых процедур и помещении их результатов в новую таблицу.

Изменение структуры хранимых процедур

Компонент Database Engine также поддерживает инструкцию ALTER PROCEDURE для модификации структуры хранимых процедур. Инструкция ALTER PROCEDURE обычно применяется для изменения инструкций Transact-SQL внутри процедуры. Все параметры инструкции ALTER PROCEDURE имеют такое же значение, как и одноименные параметры инструкции CREATE PROCEDURE. Основной целью использования этой инструкции является избежание переопределения существующих прав хранимой процедуры.

Компонент Database Engine поддерживает тип данных CURSOR. Этот тип данных используется для объявления курсоров в хранимых процедурах. — это конструкция программирования, применяемая для хранения результатов запроса (обычно набора строк) и для предоставления пользователям возможности отображать этот результат построчно.

Для удаления одной или группы хранимых процедур используется инструкция DROP PROCEDURE. Удалить хранимую процедуру может только ее владелец или члены предопределенных ролей db_owner и sysadmin.

Хранимые процедуры и среда CLR

SQL Server поддерживает общеязыковую среду выполнения CLR (Common Language Runtime), которая позволяет разрабатывать различные объекты баз данных (хранимые процедуры, определяемые пользователем функции, триггеры, определяемые пользователем статистические функции и пользовательские типы данных), применяя языки C# и Visual Basic. Среда CLR также позволяет выполнять эти объекты, используя систему общей среды выполнения.

Среда CLR разрешается и запрещается посредством опции clr_enabled системной процедуры sp_configure, которая запускается на выполнение инструкцией RECONFIGURE. В примере ниже показано, как можно с помощью системной процедуры sp_configure разрешить использование среды CLR:

Для создания, компилирования и сохранения процедуры с помощью среды CLR требуется выполнить следующую последовательность шагов в указанном порядке:

Создать хранимую процедуру на языке C# или Visual Basic, а затем скомпилировать ее, используя соответствующий компилятор.

Используя инструкцию CREATE ASSEMBLY, создать соответствующий выполняемый файл.

Сохранить процедуру в виде объекта сервера, используя инструкцию CREATE PROCEDURE.

Выполнить процедуру, используя инструкцию EXECUTE.

На рисунке ниже показана графическая схема ранее изложенных шагов. Далее приводится более подробное описание этого процесса.

Сначала создайте требуемую программу в какой-либо среде разработки, например Visual Studio. Скомпилируйте готовую программу в объектный код, используя компилятор C# или Visual Basic. Этот код сохраняется в файле динамической библиотеки (.dll), который служит источником для инструкции CREATE ASSEMBLY, создающей промежуточный выполняемый код. Далее выполните инструкцию CREATE PROCEDURE, чтобы сохранить выполняемый код в виде объекта базы данных. Наконец, запустите процедуру на выполнение, используя уже знакомую нам инструкцию EXECUTE.

В примере ниже показан исходный код хранимой процедуры на языке C#:

В этой процедуре реализуется запрос для подсчета числа строк в таблице Employee. В директивах using в начале программы указываются пространства имен, требуемые для ее выполнения. Применение этих директив позволяет указывать в исходном коде имена классов без явного указания соответствующих пространств имен. Далее определяется класс StoredProcedures, для которого применяется атрибут SqlProcedure, который информирует компилятор о том, что этот класс является хранимой процедурой. Внутри кода класса определяется метод CountEmployees(). Соединение с системой баз данных устанавливается посредством экземпляра класса SqlConnection. Чтобы открыть соединение, применяется метод Open() этого экземпляра. А метод CreateCommand() позволяет обращаться к экземпляру класса SqlCommnd, которому передается нужная SQL-команда.

В следующем фрагменте кода:

используется инструкция SELECT для подсчета количества строк в таблице Employee и отображения результата. Текст команды указывается, присваивая свойству CommandText переменной cmd экземпляр, возвращаемый методом CreateCommand(). Далее вызывается метод ExecuteScalar() экземпляра SqlCommand. Этот метод возвращает скалярное значение, которое преобразовывается в целочисленный тип данных int и присваивается переменной rows.

Теперь вы можете скомпилировать этот код, используя среду Visual Studio. Я добавил этот класс в проект с именем CLRStoredProcedures, поэтому Visual Studio скомпилирует одноименную сборку с расширением *.dll. В примере ниже показан следующий шаг в создании хранимой процедуры: создание выполняемого кода. Прежде чем выполнять код в этом примере, необходимо узнать расположение скомпилированного dll-файла (обычно находится в папке Debug проекта).

Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, для которого можно создавать хранимые процедуры среды CLR, определяемые пользователем функции и триггеры. Эта инструкция имеет следующий синтаксис:

В параметре assembly_name указывается имя сборки. В необязательном предложении AUTHORIZATION указывается имя роли в качестве владельца этой сборки. В предложении FROM указывается путь, где находится загружаемая сборка.

Предложение WITH PERMISSION_SET является очень важным предложением инструкции CREATE ASSEMBLY и всегда должно указываться. В нем определяется набор прав доступа, предоставляемых коду сборки. Набор прав SAFE является наиболее ограничивающим. Код сборки, имеющий эти права, не может обращаться к внешним системным ресурсам, таким как файлы. Набор прав EXTERNAL_ACCESS позволяет коду сборки обращаться к определенным внешним системным ресурсам, а набор прав UNSAFE предоставляет неограниченный доступ к ресурсам, как внутри, так и вне системы базы данных.

Чтобы сохранить информацию о коде сборке, пользователь должен иметь возможность выполнить инструкцию CREATE ASSEMBLY. Владельцем сборки является пользователь (или роль), исполняющий эту инструкцию. Владельцем сборки можно сделать другого пользователя, используя предложение AUTHORIZATION инструкции CREATE SCHEMA.

Компонент Database Engine также поддерживает инструкции ALTER ASSEMBLY и DROP ASSEMBLY. Инструкция ALTER ASSEMBLY используется для обновления сборки до последней версии. Эта инструкция также добавляет или удаляет файлы, связанные с соответствующей сборкой. Инструкция DROP ASSEMBLY удаляет указанную сборку и все связанные с ней файлы из текущей базы данных.

В примере ниже показано создание хранимой процедуры на основе управляемого кода, реализованного ранее:

Инструкция CREATE PROCEDURE в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME. Этот параметр указывает, что код создается средой CLR. Имя в этом предложении состоит из трех частей:

assembly_name — указывает имя сборки;

class_name — указывает имя общего класса;

method_name — необязательная часть, указывает имя метода, который задается внутри класса.

Выполнение процедуры CountEmployees показано в примере ниже:

Инструкция PRINT возвращает текущее количество строк в таблице Employee.

Илон Маск рекомендует:  Структуры данных
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL