SQL - Урок 15. Хранимые процедуры. Часть 1.

Как правило, мы в работе с БД используем одни и те же запросы, либо набор последовательных запросов. Хранимые процедуры позволяют объединить последовательность запросов и сохранить их на сервере. Это очень удобный инструмент, и сейчас вы в этом убедитесь. Начнем с синтаксиса:

CREATE PROCEDURE имя_процедуры (параметры) begin операторы end

Параметры это те данные, которые мы будем передавать процедуре при ее вызове, а операторы - это собственно запросы. Давайте напишем свою первую процедуру и убедимся в ее удобстве. В уроке 10 , когда мы добавляли новые записи в БД shop, мы использовали стандартный запрос на добавление вида:

INSERT INTO customers (name, email) VALUE ("Иванов Сергей", "[email protected]");

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

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) begin insert into customers (name, email) value (n, e); end

Обратите внимание, как задаются параметры: необходимо дать имя параметру и указать его тип, а в теле процедуры мы уже используем имена параметров. Один нюанс. Как вы помните, точка с запятой означает конец запроса и отправляет его на выполнение, что в данном случае неприемлемо. Поэтому, прежде, чем написать процедуру необходимо переопределить разделитель с; на "//", чтобы запрос не отправлялся раньше времени. Делается это с помощью оператора DELIMITER // :

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

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) begin insert into customers (name, email) value (n, e); end //

Итак, процедура создана. Теперь, когда нам понадобится ввести нового покупателя нам достаточно ее вызвать, указав необходимые параметры. Для вызова хранимой процедуры используется оператор CALL , после которого указывается имя процедуры и ее параметры. Давайте добавим нового покупателя в нашу таблицу Покупатели (customers):

call ins_cust("Сычов Валерий", "[email protected]")//

Согласитесь, что так гораздо проще, чем писать каждый раз полный запрос. Проверим, работает ли процедура, посмотрев, появился ли новый покупатель в таблице Покупатели (customers):

Появился, процедура работает, и будет работать всегда, пока мы ее не удалим с помощью оператора DROP PROCEDURE название_процедуры .

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

Казалось бы, проще всего взять уже написанные в уроке 11 представление и запрос к нему, объединить в хранимую процедуру и сделать идентификатор поставщика (id_vendor) входным параметром, вот так:

CREATE PROCEDURE sum_vendor(i INT) begin CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=i); SELECT SUM(summa) FROM report_vendor; end //

Но так процедура работать не будет. Все дело в том, что в представлениях не могут использоваться параметры . Поэтому нам придется несколько изменить последовательность запросов. Сначала мы создадим представление, которое будет выводить идентификатор поставщика (id_vendor), идентификатор продукта (id_product), количество (quantity), цену (price) и сумму (summa) из трех таблиц Поставки (incoming), Журнал поставок (magazine_incoming), Цены (prices):

CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming;

А потом создадим запрос, который просуммирует суммы поставок интересующего нас поставщика, например, с id_vendor=2:

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

CREATE PROCEDURE sum_vendor(i INT) begin CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

Проверим работу процедуры, с разными входными параметрами:

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

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

DROP PROCEDURE sum_vendor// DROP VIEW report_vendor// CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming// CREATE PROCEDURE sum_vendor(i INT) begin SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

Проверяем работу:

call sum_vendor(1)// call sum_vendor(2)// call sum_vendor(3)//

Второй вариант - прямо в процедуре дописать команду, которая будет удалять представление, если оно существует:

CREATE PROCEDURE sum_vendor(i INT) begin DROP VIEW IF EXISTS report_vendor; CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

Перед использованием этого варианта не забудьте удалить процедуру sum_vendor, а затем проверить работу:

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

Цель работы – научиться создавать и использовать хранимые процедуры на сервере БД.

1. Проработка всех примеров, анализ результатов их выполнения в утилите SQL Server Management Studio. Проверка наличия созданных про- цедур в текущей БД.

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

3. Выполнение индивидуальных заданий по вариантам.

Пояснения к выполнению работы

Для освоения программирования хранимых процедур используем при- мер базы данных c названием DB_Books , которая была создана в лабора- торной работе №1. При выполнении примеров и заданий обращайте вни- мание на соответствие названий БД, таблиц и других объектов проекта.

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

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

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

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

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

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

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

Синтаксис оператора создания новой или изменения имеющейся хранимой процедуры в обозначениях MS SQL Server:

{ CREATE | ALTER } PROC[ EDURE] имя_процедуры [ ;номер] [ { @имя_параметра тип_данных } [ VARYING ] [ = DEFAULT ] [ OUTPUT] ] [ ,... n] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION] AS sql_оператор [ ... n]

Рассмотрим параметры данной команды.

Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

Для передачи входных и выходных данных в создаваемой хранимой процедуре имена параметров должны начинаться с символа @. В одной хранимой процедуре можно задать множество параметров, разделенных запятыми. В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры. Для определения типа данных параметров хранимой процедуры подходят любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры, т.е. с указанием ключевого слова OUTPUT.

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

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

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

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию. Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры, что может обеспечить защиту от использования авторских алгоритмов, реализующих работу хранимой процедуры. Ключевое слово AS размещается в начале собственно тела хранимой процедуры. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры. Выход из хранимой процедуры можно осуществить посредством команды RETURN.

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

DROP PROCEDURE { имя_процедуры} [ ,... n]

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

Для выполнения хранимой процедуры используется команда: [ [ EXEC [ UTE] имя_процедуры [ ;номер] [ [ @имя_параметра= ] { значение | @имя_переменной} [ OUTPUT ] | [ DEFAULT ] ] [ ,... n]

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

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

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

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

Отметим, что при вызове процедуры указываются либо имена пара- метров со значениями, либо только значения без имени параметра. Их комбинирование не допускается.

Использование RETURN в хранимой процедуре

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

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Задание 1.

EXEC Count_Books

Проверьте результат.

Пример создания процедуры c входным параметром:

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

Задание 2 . Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC Count_Books_Pages 100

Проверьте результат.

Пример создания процедуры c входными параметрами:

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

Задание 3. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC Count_Books_Title 100 , "П%"

Проверьте результат.

Пример создания процедуры c входными параметрами и выходным параметром:

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

Задание 4. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите с помощью набора команд:

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

Проверьте результат.

Пример создания процедуры c входными параметрами и RETURN:

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Пушкин А.С." RETURN 1 ELSE RETURN 2

Задание 5. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_status

Пример создания процедуры без параметров для увеличения значения ключевого поля в таблице Purchases в 2 раза:

CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2

Задание 6. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC update_proc

Пример процедуры с входным параметром для получения всей ин- формации о конкретном авторе:

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

Задание 7.

EXEC select_author "Пушкин А.С." или select_author @k= "Пушкин А.С." или EXEC select_author @k= "Пушкин А.С."

Пример создания процедуры с входным параметром и значением по умолчанию для увеличения значения ключевого поля в таблице Purchases в заданное количество раза (по умолчанию в 2 раза):

CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @p

Процедура не возвращает никаких данных.

Задание 8. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

EXEC update_proc 4 или EXEC update_proc @p = 4 или EXEC update_proc --будет использовано значение по умолчанию.

Пример создания процедуры с входным и выходным параметрами. Создать процедуру для определения количества заказов, совершенных за указанный период:

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

Задание 9. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

DECLARE @c2 INT EXEC count_purchases ’01- jun- 2006 ’, ’01- jul- 2006 ’, @c2 OUTPUT SELECT @c2

Варианты заданий к лабораторной работе №4

Общие положения. В утилите SQL Server Management Studio создать новую страницу для кода (кнопка «Создать запрос»). Программно сделать активной созданную БД DB_Books с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые были выполнены во второй лабораторной работе. Причем код SQL запросов нужно изменить таким образом, чтобы в них можно было передавать значения полей, по которым осуществляется поиск.

Например, исходное задание и запрос в лабораторной работе №2:

/*Выбрать из справочника поставщиков (таблица Deliveries) названия ком- паний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) „ОАО МИР“.

SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "ОАО МИР"

*/ –В данной работе будет создана процедура:

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

–Для запуска процедуры используется команда:

EXEC select_name_company "ОАО МИР"

Список заданий

В утилите SQL Server Management Studio создать новую программу. Программно сделать активной индивидуальную БД, созданную в лабораторной работе №1, с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые представлены в виде отдельных заданий по вариантам.

Вариант 1

1. Вывести список сотрудников, у которых есть хотя бы один ребенок.

2. Вывести список детей, которым выдали подарки в указанный период.

3. Вывести список родителей, у которых есть несовершеннолетние дети.

4. Вывести информацию о подарках со стоимостью больше указанного числа, отсортированных по дате.

Вариант 2

1. Вывести список приборов с указанным типом.

2. Вывести количество отремонтированных приборов и общую стоимость ремонтов у указанного мастера.

3. Вывести список владельцев приборов и количество их обращений, отсортированный по количеству обращений по убыванию.

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

Вариант 3

2. Вывести список кодов продаж, по которым продано цветов на сумму больше указанного числа.

3. Вывести дату продажи, сумму, продавца и цветок по указанному коду продажи.

4. Вывести список цветов и сорт для цветов с высотой больше указанного числа или цветущий.

Вариант 4

1. Вывести список лекарств с указанным показанием к применению.

2. Вывести список дат поставок, по которым продано больше указанного числа одноименного лекарства.

3. Вывести дату поставки, сумму, ФИО руководителя от поставщика и название лекарства по коду поступления больше указанного числа.

Вариант 5

2. Вывести список списанного оборудования по указанной причине.

3. Вывести дату поступления, название оборудования, ФИО ответственного и дату списания для оборудования, списанного в указанный период.

4. Вывести список оборудования с указанным типом или с датой поступления больше определенного значения

Вариант 6

1. Вывести список блюд с весом больше указанного числа.

2. Вывести список продуктов, в названии которых встречается указанный фрагмент слова.

3. Вывести объем продукта, название блюда, название продукта с кодом блюда от указанного начального значения по определенному конечному значению.

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

Вариант 7

1. Вывести список сотрудников с указанной должностью.

3. Вывести дату регистрации, тип документа, ФИО регистратора и название организации для документов, зарегистрированных в указанный период.

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

Вариант 8

1. Вывести список сотрудников с указанной причиной увольнения.

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

Вариант 9

1. Вывести список сотрудников, бравших отпуск указанного типа.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, тип отпуска, ФИО сотрудника для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 10

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО отправителя и название организации для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа меньше определенного значения.

Вариант 11

1. Вывести список сотрудников, назначенных на указанную должность.

2. Вывести список документов с датой регистрации в указанный период.

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

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 12

3. Вывести список лиц, бравших оборудование в прокат и количество их обращений, отсортированный по количеству обращений по убыванию.

Вариант 13

1. Вывести список оборудования с указанным типом. 2. Вывести список оборудования, которое списал определенный сотрудник.

3. Вывести количество списанного оборудования, сгруппированного по типам оборудования.

4. Вывести информацию о сотрудниках с датой приема на работу больше определенной даты.

Вариант 14

1. Вывести список цветков с указанным типом листа.

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

3. Вывести дату поступления, сумму, названия поставщика и цветов по определенному коду поставщика.

4. Вывести список цветов и сорт для цветов с высотой больше определенного числа или цветущий.

Вариант 15

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

2. Вывести общую сумму оплат за номера для каждого клиента.

3. Вывести дату заезда, тип номера, ФИО клиентов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных клиентов в номерах определенного типа.

Вариант 16

1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный клиент.

3. Вывести список лиц, бравших оборудование в прокат и количество их обращений, отсортированных по количеству обращений по убыванию.

4. Вывести информацию о клиентах, отсортированных по адресам.

Вариант 17

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

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

3. Вывести сумму стоимости ценностей с кодом в указанном диапазоне.

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

Вариант 18

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

2. Вывести список этапов работ, входящих в работы, в названии которых встречается указанное слово.

3. Вывести сумму стоимости этапов ремонтных работ для работ с кодом в указанном диапазоне.

4. Вывести список мастеров с датой приема на работу в указанном диапазоне.

Вариант 19

1. Вывести список лекарств с определенным показанием.

2. Вывести список номеров чеков, по которым продано больше определенного числа лекарств.

3. Вывести дату продажи, сумму, ФИО кассира и лекарство по чеку с указанным номером.

4. Вывести список лекарств и единицы измерения для лекарств с количеством в упаковке больше указанного числа или кодом лекарства меньше определенного значения.

Вариант 20

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО исполнителя и факт исполнения для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа в определенном диапазоне.

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

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

CALL процедура(…)

EXECUTE процедура(…)

Хранимые процедуры могут возвращать множества результатов, то есть результаты запроса SELECT. Такие множества результатов могут обрабатываться, используя курсоры, другими хранимыми процедурами, возвращая указатель результирующего множества, либо же приложениями. Хранимые процедуры могут также содержать объявленные переменные для обработки данных и курсоров, которые позволяют организовать цикл по нескольким строкам в таблице. Стандарт SQL предоставляет для работы выражения IF, LOOP, REPEAT, CASE и многие другие. Хранимые процедуры могут принимать переменные, возвращать результаты или изменять переменные и возвращать их, в зависимости от того, где переменная объявлена.

Реализация хранимых процедур варьируется от одной СУБД к другой. Большинство крупных поставщиков баз данных поддерживают их в той или иной форме. В зависимости от СУБД, хранимые процедуры могут быть реализованы на различных языках программирования, таких, как SQL, Java, C или C++. Хранимые процедуры написанные не на SQL могут самостоятельно выполнять SQL-запросы, а могут и не выполнять.

За

Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.

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

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

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

Против

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

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

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

Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

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

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

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

Кроме собственно выполнения запроса, хранимые процедуры позволяют также производить вычисления и манипуляцию данными - изменение, удаление, выполнять DDL-операторы (не во всех СУБД!) и вызывать другие хранимые процедуры, выполнять сложную транзакционную логику. Один-единственный оператор позволяет вызвать сложный сценарий, который содержится в хранимой процедуре, что позволяет избежать пересылки через сеть сотен команд и, в особенности, необходимости передачи больших объёмов данных с клиента на сервер.

В большинстве СУБД при первом запуске хранимой процедуры она компилируется (выполняется синтаксический анализ и генерируется план доступа к данным). В дальнейшем её обработка осуществляется быстрее. В СУБД Oracle выполняется интерпретация хранимого процедурного кода, сохраняемого в словаре данных. Начиная с версии Oracle 10g поддерживается так называемая естественная компиляция (native compilation) хранимого процедурного кода в Си и затем в машинный код целевой машины, после чего при вызове хранимой процедуры происходит прямое выполнение её скомпилированного объектного кода.

Возможности программирования

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

Безопасность

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

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

Снижается вероятность таких действий как «внедрение SQL-кода», поскольку хорошо написанные хранимые процедуры дополнительно проверяют входные параметры перед тем, как передать запрос СУБД.

Реализация хранимых процедур

Хранимые процедуры обычно создаются с помощью языка SQL или конкретной его реализации в выбранной СУБД. Например, для этих целей в СУБД Microsoft SQL Server существует язык Transact-SQL, в Oracle - PL/SQL, в InterBase и Firebird - PSQL, в PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, в IBM DB2 - SQL/PL (англ.), в Informix - SPL. MySQL достаточно близко следует стандарту SQL:2003, её язык похож на SQL/PL.

В некоторых СУБД возможно использование хранимых процедур, написанных на любом языке программирования, способном создавать независимые исполняемые файлы, например, на C++ или Delphi. В терминологии Microsoft SQL Server такие процедуры называются расширенными хранимыми процедурами и являются просто функциями, содержащимися в Win32-DLL. А, например, в Interbase и Firebird для функций, вызываемых из DLL/SO, определено другое название - UDF (User Defined Function). В MS SQL 2005 появилась возможность написания хранимых процедур на любом языке.NET, а от расширенных хранимых процедур в будущем планируется отказаться. СУБД Oracle, в свою очередь, допускает написание хранимых процедур на языке Java. В IBM DB2 написание хранимых процедур и функций на обычных языках программирования является традиционным способом, поддерживаемым с самого начала, а процедурное расширение SQL было добавлено в эту СУБД только в достаточно поздних версиях, после его включения в стандарт ANSI. Также процедуры на Java и С поддерживает Informix.

В СУБД Oracle хранимые процедуры могут объединяться в так называемые пакеты (англ. packages). Пакет состоит из двух частей - спецификации (англ. package specification), в которой указывается определение хранимой процедуры, и тела (англ. package body), где находится её реализация. Таким образом Oracle позволяет отделить интерфейс программного кода от его реализации.

В СУБД IBM DB2 хранимые процедуры можно объединять в модули.

Синтаксис

CREATE PROCEDURE `p2` ()

SQL SECURITY DEFINER

COMMENT "A procedure"

SELECT "Hello World !";

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

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

Language: в целях обеспечения переносимости, по умолчанию указан SQL.

Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.

SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.

Comment: в целях документирования, значение по умолчанию - ""

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

CALL stored_procedure_name (param1, param2, ....)

CALL procedure1(10 , "string parameter" , @parameter_var);

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

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

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

DROP PROCEDURE IF EXISTS p2;

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

Параметры

CREATE PROCEDURE proc1 (): пустой список параметров

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один входящий параметр. Слово IN необязательно, потому что параметры по умолчанию - IN (входящие).

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один возвращаемый параметр.

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, одновременно входящий и возвращаемый.

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

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Объявление процедуры

CREATE PROCEDURE [({IN|OUT|INOUT } [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC ]

END

Ключевые слова
. IN (Input) – входной параметр
. OUT (Output) – выходной параметр
. INOUT – входной и выходной, а также поле (без параметров)
. DYNAMIC RESULT SET показывает, что процедура может открыть указанное число курсоров, которые останутся открытыми после возврата из процедуры

Примечания
Не рекомендуется использовать много параметров в хранимых процедурах (в первую очередь больших чисел и символьных строк) из-за перегрузки сети и стека. На практике в существующих диалектах Transact-SQL, PL/SQL и Informix наблюдается существенное отличие от стандарта, как в объявлении и использовании параметров, объявлении переменных, так и в вызове подпрограмм. Microsoft рекомендует применять следующую аппроксимацию для оценки размера КЭШа хранимых процедур:
=(максимальное количество одновременно работающих пользователей)*(размер самого большого плана выполнения)*1.25. Определение размера плана выполнения в страницах можно сделать с помощью команды: DBCC MEMUSAGE.

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

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

EXECUTE PROCEDURE [(][)]

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

Пример объявления процедуры

CREATE PROCEDURE Proc1 AS //объявляем процедуру
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //объявляем курсор
OPEN Cur1 //открываем курсор
FETCH NEXT FROM Cur1 //считываем данные из курсора
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 //закрываем курсор
DEALLOCATE Cur1
EXECUTE Proc1 //запускаем процедуру

Полиморфизм
Две подпрограммы с одним и тем же именем могут быть созданы в одной и той же схеме, если параметры этих двух подпрограмм являются в такое мере отличными друг от друга, чтобы их можно было различать. Для того, чтобы различать две подпрограммы с одним и тем же именем в одной схеме, каждой из них дается альтернативное и уникальное имя (specific name). Такое имя может быть явно указано, когда определяется подпрограмма. При вызове подпрограмм при наличии нескольких одинаковых имен определение нужной подпрограммы осуществляется в несколько шагов:
. Первоначально определяются все процедуры с указанным именем, а если таковых нет, то все функции с заданным именем.
. Для дальнейшего анализа оставляются только те подпрограммы, по отношению к которым данный пользователь обладает привилегией на исполнение (EXECUTE).
. Для них отбираются те, у которых число параметров соответствует числу аргументов вызова. Проверяются указанные типы данных у параметров и их позиции.
. Если осталось более одной подпрограммы, то выбирается та, квалификационное имя которой короче.
На практике в Oracle полиморфизм поддерживается для функций, объявленных только в пакете, DB@ — в разных схема, а в Sybase и MS SQL Server перегрузка запрещена.

Удаление и изменение процедур
Для удаления процедуры используется оператор:

Для изменения процедуры используется оператор:

ALTER PROCEDURE [([{IN|OUT|INOUT }])]
BEGIN [ATOMIC ]

END

Привилегии на выполнение процедур

GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION ]

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

Включай в свои процедуры строку - SET NOCOUNT ON:

С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.

Transact-SQL

Используй имя схемы с именем объекта:

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

Transact-SQL

SELECT * FROM dbo.MyTable --Вот так делать хорошо -- Вместо SELECT * FROM MyTable --А так делать плохо --Вызов процедуры EXEC dbo.MyProc --Опять же хорошо --Вместо EXEC MyProc --Плохо!

Не используй префикс «sp_» в имени своих хранимых процедур:

Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.

Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):

Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже.