 |
Задание для индивидуальной работы 6
|
|
|
|
Создайте несколько (не менее 3) представлений для вашей базы данных. Будут ли они обновляемыми или нет? Проверьте.
Хранимые процедуры
Хранимые процедуры – это объекты базы данных, которые представляют собой программы, манипулирующие данными и выполняемые на сервере. Эти программы, кроме команд языка SQL, могут использовать немногочисленные управляющие команды.
Структура хранимой процедуры следующая:
CREATE PROC[EDURE] имя_процедуры [параметры]
AS
Код процедуры
Локальные переменные и параметры в процедуре начинаются с символа @.
Глобальные переменные начинаются с символов @@. Есть довольно много системных глобальных переменных с полезной информацией. Некоторые из них мы будем использовать в следующих темах.
Объявление переменных имеет вид
DECLARE имя_переменной тип_переменной [(длина)]
Блок операторов заключается в команды BEGIN … END
Оператор присвоения выгладит довольно странно:
SELECT переменная=значение
Зато с помощью такого синтаксиса при выполнении команды SELECT можно сохранять значения в переменных.
Альтернативный формат оператора присвоения:
SET переменная=значение
Условный оператор выглядит так:
IF условие
Оператор1
[ELSE
Оператор2]
Цикл по счетчику отсутствует, есть только цикл по условию
WHILE условие
Оператор
Для прерывания цикла используется команда BREAK.
Для прерывания итерации цикла используется команда CONTINUE.
Оператор печати имеет вид PRINT выражение
Выход из процедуры: RETURN [код_завершения]
Это первый способ возвращения значения из процедуры – таким образом можно возвращать только целочисленное значение.
Команда
RAISERROR сообщение, уровень_опасности, код_состояния
|
|
|
применяется для вывода сообщений об ошибках и прочих предупреждений в стандартной для SQL server форме.
Выражение CASE применяется для выбора на основании нескольких опций:
CASE выражение
WHEN вариант1 THEN выражение1
WHEN вариант2 THEN выражение2
…
ELSE выражениеN
END
Создадим процедуру, которая в качестве параметра получает фамилию сотрудника и печатает список всех договоров, которые он курирует.
Это второй способ возвращения значений из процедуры – печать результата выполнения команды SELECT.
CREATE PROCEDURE show_contracts @name Varchar(30)
AS
SELECT contract_num, contract_date, contract_type
FROM k_contract c JOIN k_staff s ON c.staff_num=s.staff_num
WHERE s.staff_name=@name
GO
Для запуска этой процедуры нужно выполнить, например, команду
EXEC show_contracts 'Иванов'
В результате получим:
contract_num contract_date contract_type
------------ --------------------------- -------------
1 2011-11-01 00:00:00.000 A
3 2011-09-01 00:00:00.000 C
6 2011-07-15 00:00:00.000 C
7 2011-11-12 00:00:00.000 A
Создадим процедуру «Распродажа», которая находит самый непродаваемый (по количеству) товар и уценивает его на заданный процент (по умолчанию задается 10 процентов).
CREATE PROCEDURE clearance @percent Int = 10
AS
DECLARE @p Int
IF @percent > 0 AND @percent < 100
BEGIN
SELECT @p=price_num FROM k_protokol
GROUP BY price_num
HAVING SUM(kolvo)<=ALL
(SELECT SUM(kolvo) FROM k_protokol
GROUP BY price_num)
UPDATE k_price
SET price_sum=price_sum*(100-@percent)/100
WHERE price_num=@p
END
GO
Содержимое таблицы "Прайс-лист" до выполнения процедуры:
price_num price_name price_sum
-------------------------------------------------
1 Материализация духов 1000.00
2 Раздача слонов 100.00
3 Слоновий бивень 3000.00
4 Моржовый клык 1500.00
5 Копыто Пегаса 5000.00
Для запуска этой процедуры нужно выполнить, например, команду
EXEC clearance 10
Содержимое таблицы "Прайс-лист" после выполнения процедуры:
price_num price_name price_sum
------------------------------------------
1 Материализация духов 1000.00
2 Раздача слонов 100.00
3 Слоновий бивень 3000.00
4 Моржовый клык 1350.00
5 Копыто Пегаса 5000.00
Как видим, товар с номером 4 в прайс-листе уценен на 10%.
В том случае, если из хранимой процедуры нужно вернуть значение переменной, нужно объявить эту переменную как выходной (OUTPUT) параметр процедуры в двух местах: в описании процедуры и в вызове процедуры. Это третий способ возвращения значений из процедуры – выходные параметры.
|
|
|
Пусть, например, в предыдущей процедуре мы хотим не только уценить товар, но и возвратить его номер. Описание процедуры будет выглядеть следующим образом:
CREATE PROCEDURE clearance @percent Int, @p Int OUTPUT
AS
IF @percent > 0 AND @percent < 100
BEGIN
SELECT @p=price_num FROM k_protokol
GROUP BY price_num
HAVING SUM(kolvo)<=ALL
(SELECT SUM(kolvo) FROM k_protokol
GROUP BY price_num)
UPDATE k_price
SET price_sum=price_sum*(100-@percent)/100
WHERE price_num=@p
END
А вызов процедуры будет выглядеть следующим образом (все три команды должны выполняться вместе):
DECLARE @num NUMERIC(6)
EXEC clearance 1, @num OUTPUT
PRINT 'Уценили товар с номером '+STR(@num)
И еще один пример. Предположим, у нас есть таблица для хранения списка счетов:
CREATE TABLE bill_list
(name VARCHAR(20), dat DATETIME, summa NUMERIC(9,2))
Мы хотим сформировать список выставленных за месяц счетов с названиями предприятий и с итогами по дням. Рассмотрите этот пример самостоятельно. Месяц и год передаются в процедуру в качестве параметров. Функция DATEDIFF здесь вычисляет разность между двумя датами в днях.
CREATE PROCEDURE calc_bill_list @mon Int, @year Int
AS
DECLARE @day Int, @end Int, @date DateTime
SET @day=1
IF @mon=2
IF @year%4=0
SET @end=29
ELSE
SET @end=28
ELSE IF @mon=4 OR @mon=6 OR @mon=9 OR @mon=11
SET @end=30
ELSE
SET @end=31
DELETE FROM bill_list
WHILE (@day<=@end)
BEGIN
SET @date=CONVERT(CHAR(2),@mon)+'/'+
CONVERT(CHAR(2),@day)+'/'+
CONVERT(CHAR(4),@year)
INSERT INTO bill_list (name, dat, summa)
SELECT firm_name, bill_date, bill_sum
FROM k_firm, k_contract, k_bill
WHERE k_firm.firm_num=k_contract.firm_num
AND
k_contract.contract_num=k_bill.contract_num
AND
DATEDIFF(day, k_bill.bill_date, @date)=0
INSERT INTO bill_list (name, dat, summa)
SELECT ' ИТОГО ЗА:', @date,
ISNULL(SUM(bill_sum),0) FROM k_bill
WHERE
DATEDIFF(day, k_bill.bill_date, @date)=0
SET @day=@day+1
END
Обратите внимание на формат команды INSERT. В таблицу bill_list добавляются строки, являющиеся результатом выполнения команды SELECT.
Для удаления хранимой процедуры используется команда:
DROP PROCEDURE имя_процедуры
|
|
|
II Письменное задание
II. Письменное задание
II. Письменное задание
III. Домашнее задание.
S_PEXT: Задание параметров и запуск таймера «Импульс с памятью»
XX. Дополнительное задание
Алгоритм работы управляющего автомата
Вопрос 15. Задание движения точки в полярных координатах.
Все студенты и ученики с ограниченными возможностями имеют право на разработку индивидуальной программы образования.
Все, что необходимо прописать в индивидуальной программе образования.
