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

1.

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

2.

Сценарий может состоять из одного или
нескольких пакетов.
2

3.

Пакет – это набор операторов SQL,
одновременно передаваемых SQL –серверу
и выполняемых как единая группа.
Пакеты выполняются интерактивно или в
составе сценария.
Пакет содержит одну или несколько команд
SQL. Команды пакета анализируются,
компилируются и выполняются как единая
группа.
Пакет отправляется на сервер с помощью
команды GO .
3

4.

SQL Server компилирует операторы пакета в
единую исполнимую единицу (план исполнения).
После этого по очереди выполняются операторы
этого плана.
Ошибка
при
компиляции,
например
синтаксическая,
останавливает
процесс
компиляции плана исполнения. В этом случае ни
один из операторов пакета исполнен не будет.
4

5.

5

6.

Пакеты чаще всего используются для
регламентации порядка следования
заданий.
6

7.

7

8.

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

9.

Работа такой схеме основывается на
использовании
так
называемого
серверного кода (server-side code) - любого
кода, выполняемого компьютером, на
котором установлена СУБД.
Ядро СУБД выполняет этот код в базе
данных и возвращает приложению только
результат. Например, это может быть
несколько колонок строки или вычисленное
значение.
9

10.

Использование серверного кода значительно
сокращает объем сетевого трафика, и тем самым
увеличивает производительность базы данных в
целом. Однако СУБД должна иметь встроенные
средства для распознавания и обработки такого
кода.
Многие фирмы-производители промышленных
СУБД, в том числе Oracle, Microsoft предлагают
процедурные расширения SQL, с помощью которых
можно выполнять построчную обработку данных,
использовать циклы, сложные вычисления и
операции управления данными.
10

11.

PL/SQL является таким расширением SQL в СУБД Oracle,
Transact SQL – в MS SQL Server.
Они позволяют создавать серверный код в виде объектов
реляционной базы данных, таких, как хранимые
процедуры, функции, пакеты и триггеры. Это дает
возможность создавать такие объекты с целью
сокращения сетевого трафика за счет
переноса
определенного объема обработки на сервер, особенно в
тех случаях, когда эта обработка выполняется очень
интенсивно.
Таким образом, разработка серверного кода сводится к
решению следующих подзадач:
•создание хранимых процедур;
•создание функций;
•создание триггеров.
11

12.

Процедурные расширения SQL описаны в
документе SQL/PSM стандарта SQL99.
Язык SQL расширен операторами управления
CASE, IF, WHILE, REPEAT, LOOP и FOR.
В этом стандарте вводится поддержка процедур
и функций, создаваемых операторами CREATE
PROCEDURE и CREATE FUNCTION. В язык SQL
введено
использование
переменных
и
применение обработчиков ошибок.
12

13.

Процедурные расширения
Поскольку SQL не является языком
программирования), вводимые разными
производителями расширения касались в
первую очередь процедурных расширений.
Практически в каждой СУБД применяется
свой процедурный язык. Стандарт для
процедурных
расширений
представлен
спецификацией SQL/PSM.
13

14.

Перечень процедурных расширений для
самых популярных СУБД
СУБД
Краткое Расшифровка
название
InterBase/Fire PSQL
bird
IBM DB2
Procedural SQL
SQL Procedural Language (расширяет SQL/PSM); также в
SQL
хранимые процедуры могут писаться на обычных
PL (англ.) DB2
языках программирования: Си, Java и т. д.
MS SQL Server TransactTransact-SQL
SQL
MySQL
Stored Module (соответствует стандарту
SQL/PSM SQL/Persistent
SQL:2003)
Oracle
PL/SQL
PostgreSQL
Language/PostgreSQL Structured
PL/pgSQL Procedural
Language (очень похож на Oracle PL/SQL)
Procedural Language/SQL (основан на языке Ada)
Query
14

15.

Операторы SQL могут выполняться
разными способами:
•по одному,
•как сценарии,
•пакетами,
•в составе хранимых процедур или
триггеров.
15

16.

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

17.

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

18.

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

19.

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

20.

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

21.

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

22.

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

23.

23

24.

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

25.

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

26.

Системные хранимые процедуры
хранятся в базе данных master, их
имена начинаются с префикса sp_
(sp_helpconstraint, sp_helpindex и
т.д).
Системные хранимые процедуры предназначены для
выполнения
различных
административных
действий.
Практически все действия по администрированию сервера
выполняются с их помощью
.
26

27.

Запускаются хранимые процедуры всех типов
с помощью команды EXECUTE, которую
можно сократить до ЕХЕС.
Например,
хранимая
процедура
sp_helplogins формирует два отчета об
именах учетных записей и соответствующих
им в каждой БД пользователях
EXEC sp_helplogins;
27

28.

Временные хранимые процедуры хранятся
в базе данных tempdb и удаляются
автоматически
28

29.

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

30.

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

31.

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

32.

Для просмотра кода хранимой
процедуры служит системная
процедура sp_helptext.
32

33.

33

34.

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

35.

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

36.

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

37.

• Хранимые
процедуры
можно
рассматривать
как
важный
компонент системы безопасности
базы данных.
Если все клиенты осуществляют
доступ к данным с помощью
хранимых процедур, то прямой
доступ к таблицам может быть
запрещён,
и
все
действия
пользователей будут находиться под
контролем.
37

38.

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

39.

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

40.

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

41.

Для создания хранимой процедуры используется оператор
SQL CREATE PROCEDURE.
Синтаксис:
CREATE PROCEDURE <имя процедуры>
(@<имя перем1> <тип данных>, @<имя
перем2> <тип данных>…)
[@параметр тип данных[=значение по
умолчанию] [OUTPUT]
[WITH
{ RECOMPILE
|ENCRYPTION
|RECOMPILE,ENCRYPTION}]
AS <тело процедуры>
41

42.

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

43.

Ключевое слово ENCRYPTION определяет
режим, при котором исходный текст хранимой
процедуры не сохраняется в БД.
Кроме имени все остальные
являются необязательными.
параметры
Каждая хранимая процедура компилируется
при первом выполнении. Описание процедуры
совместно с планом ее работы хранится в
системных таблицах.
43

44.

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

45.

Выполнение
осуществляется
команды
с
процедуры
помощью
EXEC[UTE]
имя
процедуры
[@параметр = значение]
45

46.

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

47.

Передача значений без ссылок на имена
параметров
(передача значений по
позиции).
Если указываются только значения,
их следует перечислять в порядке
определения в операторе CREATE
PROC.
47

48.

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

49.

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

50.

Возврат значений при помощи
параметров вывода
Для использования параметра вывода
ключевое слово OUTPUT должно
указываться как в операторе CREATE
PROC, так и в операторе EXEC.
Вызывающий
оператор
должен
указать для параметра вывода имя
переменной.
50

51.

Процедурные расширения
Поскольку SQL не является языком
программирования), вводимые разными
производителями расширения касались в
первую очередь процедурных расширений.
Практически в каждой СУБД применяется
свой процедурный язык. Стандарт для
процедурных
расширений
представлен
спецификацией SQL/PSM.
51

52.

52

53.

53

54.

54

55.

55

56.

56

57.

57

58.

58

59.

59

60.

60

61.

61

62.

Элементы Transact SQL
для управления ходом
выполнения
62

63.

Операторы управления выполнением
программы:
В процедурах могут использоваться
следующие операторы управления:
1. Оператор условия
IF <выражение>
BEGIN
<операторы>
END
[ELSE]
[IF <выражение>]
BEGIN
<операторы>
END
63

64.

IF (SELECT COUNT(*)
FROM STAFF WHERE Position = 'менеджер' ) < 3
PRINT 'Недостаточно менеджеров '
ELSE
BEGIN
PRINT ' В компании достаточное количество
менеджеров'
SELECT fname FROM STAFF WHERE Position =
'менеджер'
END
go
64

65.

2. Выбор одного из нескольких
значений
CASE <переменная>
WHEN <условие1> THEN <оператор1>
WHEN <условие2> THEN <оператор2>
WHEN <условие3> THEN <оператор3>
…
ELSE <оператор>
END
65

66.

3. Циклическое выполнение операций
WHILE <логическое выражение>
BEGIN
<операторы>
END
В этом операторе можно также использовать
операторы BREAK и CONTINUE, которые
позволяют прервать выполнение этого цикла.
66

67.

67

68.

Оператор
RETURN
выполняет
безусловный выход из процедуры.
68

69.

В программу
комментарии.
можно
включать
Однострочные
комментарии
отделяются от оператора двумя
дефисами (--).
Многострочные
комментарии
заключаются в символы: (/*…*/).
69

70.

Для объявления переменных, которые
используются в процедуре, служит
директива DECLARE.
Идентификаторы
начинаются с символа @.
переменных
Если необходимо присвоить переменной
какое-либо
значение,
используется
ключевое слово SET или SELECT.
70

71.

71

72.

Неправильное использование SET:
72

73.

73

74.

74

75.

1)
DECLARE @T INTEGER
SELECT @T= COUNT(*) FROM
Property
SELECT @T
2)
DECLARE @T INTEGER
SET @T= (SELECT COUNT(*) FROM
Property)
SELECT @T
75

76.

Обработка ошибок
Глобальные переменные
SQL Server позволяет обрабатывать ошибки, используя
глобальную переменную @@ERROR. Если генерируется
ошибка, то переменная @@ERROR содержит соответствующий
номер ошибки, в случае же успеха она возвращает 0.
Хранящееся значение номера ошибки может быть
положительным, отрицательным, или равным 0. Значение
переменной @@ERROR непостоянно, т.е. оно изменяется при
выполнении каждой команды. Рассмотрим следующий
пример:
76

77.

77

78.

Недостатки подхода с использованием @@Error
Значение переменной @@Error должно быть проверено
сразу после выполнения запроса/команды.
Так как @@Error постоянно меняется, то мы вынуждены
заводить отдельную переменную для сохранения и
вывода кода ошибки.
78

79.

Другим возможным способом обработки ошибок является
использование глобальных переменных @@rowcount и
@@trancount.
•@@TRANCOUNT - число незавершённых
транзакций
IF (@@TRANCOUNT>0)
BEGIN
RAISERROR (‘задание не может быть выполнено в
транзакции’, 16,1)
RETURN
END
79

80.

• @@ROWCOUNT возвращает количество
строк,
затронутых
(возвращённых,
удалённых,
изменённых)
последним
запросом.
Например:
UPDATE STAFF SET LastName = ‘Иванков’
WHERE LastName = ‘Иванов’
IF(@@ROWCOUNT=0)
BEGIN
PRINT ‘Внимание : Ни одна запись не была
изменена’
RETURN
80

81.

Инструкция
RAISERROR
возвращает
пользовательское сообщение об ошибке,
позволяет пользователям получать записи
из системной таблицы sysmessages или
создавать сообщение динамически, на
основе заданного уровня и кода ошибки.
Синтаксис:
RAISERROR({код ошибки|символьная
строка} уровень важности ошибки,
состояние).
81

82.

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

83.

Система обозначений уровней важности ошибок
охватывает широкий спектр сообщений об
ошибках, включая те, которые являются
• информационными
(со
значениями
уровней важности 1-18),
• относящимися к системному уровню (1925),
•рассматриваются как катастрофические
(20-25).
При возникновении ошибок с уровнем важности
20 и выше автоматически завершается работа
пользовательских соединений.
83

84.

Если
необходимо
завершить
выполнение
процедуры
и
активировать
в
клиентской
программе сообщение, как правило,
указывается уровень важности 16.
84

85.

Уровень важности ошибки от 0 до
18
может
указать
любой
пользователь. Уровни важности
от 19 до 25 могут быть указаны
только
членами
предопределенной роли сервера
sysadmin.
85

86.

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

87.

Сообщение
об
ошибке
можно
стандартизировать
и
использовать
многократно. Для этого сообщение следует
определить в БД. Синтаксис включения
сообщения в БД:
Sp_addmessage код сообщения, код
важности, текст
сообщения[,язык[FALSE|TRUE],REPLACE]]]
87

88.

Флаг FALSE|TRUE определяет, должно
ли сообщение записываться в журнал
ошибок.
REPLACE заменяет существующее
сообщение с указанным кодом.
88

89.

Начиная с SQL Server 2005, появилась
возможность обработать исключения,
используя операторы try и catch.
CREATE PROCEDURE proc1
AS BEGIN
BEGIN TRY SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
END CATCH
END
Обнаружена ошибка: деление на ноль.
89

90.

Внутри блока CATCH доступны функции для
обработки исключений:
ERROR_NUMBER() — номер ошибки;
ERROR_SEVERITY() — уровень серьезности;
ERROR_MESSAGE() — полный текст
сообщения об ошибке.
90

91.

Конструкция TRY...CATCH перехватывает
все ошибки исполнения с кодом
серьезности, большим чем 10, которые
не закрывают подключение к базе
данных
91

92.

Ошибки, обнаруженные в блоке CATCH, не
передаются в вызывающее приложение. Если
какие-либо сведения об ошибке должны быть
возвращены в приложение, код в блоке CATCH
должен выполнить передачу этой ошибки,
используя любые доступные механизмы, такие
как результирующие наборы инструкции
SELECT либо инструкции RAISERROR и PRINT.
92

93.

CREATE PROCEDURE insert_data @a int, @b int
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO sometable(a, b) VALUES (@a, @b)
INSERT INTO sometable(a, b) VALUES (@b, @a)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN
END CATCH
93

94.

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

95.

Пример: создается хранимая процедура
для вычисления суммы 2 чисел.
USE <имя БД>
GO
CREATE PROCEDURE SUMMA
@A smallint,
@B smallint,
@S smallint OUTPUT
AS
SET @S= @A+@B
95

96.

DECLARE @S INT
EXEC SUMMA 2, 3, @S OUTPUT
SELECT @S
96

97.

Пример.
Создать
процедуру
для
начисления стипендии студентам
в зависимости от полученного
среднего балла. В процедуру
передаётся значение среднего
балла, в зависимости от которого
система устанавливает ставку
стипендии.
97

98.

CREATE PROCEDURE STIP
@N TINYINT --средний балл
AS
BEGIN
DECLARE @X REAL
SELECT @X=
CASE @N
WHEN 8 THEN 200
WHEN 9 THEN 300
WHEN 10 THEN 400
ELSE 0
END
PRINT ‘Установлена стипендия:’
PRINT @X
END
98

99.

Вызов процедуры:
EXEC STIP 10
99

100.

ALTER PROCEDURE [dbo].[STIP]
@N TINYINT, @X INT OUTPUT
AS
BEGIN
SELECT @X=
CASE @N
WHEN 8 THEN 200
WHEN 9 THEN 300
WHEN 10 THEN 400
ELSE 0
END
END
100

101.

Запуск процедуры:
DECLARE @Y INT
EXEC STIP 6, @Y OUTPUT
SELECT ‘Установлена
стипендия: ‘ + CAST (@Y AS
VARCHAR(3))
101

102.

Пример создания процедуры с
входным параметром и значением по умолчанию
для изменения значения цены (по умолчанию –
увеличения в 2 раза)
CREATE PROC UPDATE_ Price
@p INT = 2
AS UPDATE PRICES
SET price = price *@p
102

103.

EXEC UPDATE_Price 4
или
EXEC UPDATE_Price - будет
использовано значение по
умолчанию.
103

104.

Создать процедуру для
повышения заработной платы
сотрудников в зависимости от
количества объетов, за
которые они отвечают
104

105.

CREATE PROCEDURE [dbo].[INCREASE_SALARY] (@Staff_no
nchar(9), @percent decimal)
AS
DECLARE @S int
SELECT @S = COUNT (*) FROM PROPERTY WHERE Staff_no =
@Staff_no
UPDATE STAFF SET Salary =
CASE
WHEN ( @S>2) THEN Salary * (100 + 3*@percent) / 100
WHEN (@S>1) THEN Salary * (100 + 2*@percent) / 100
ELSE Salary * (100 + @percent) / 100
END
WHERE Staff_no = @Staff_no
105

106.

Выражение IF EXISTS()
Выражение IF EXISTS() использует в
качестве условия наличие какой-либо
строки,
возвращённой
инструкцией
SELECT.
Этот метод работает быстрее, чем
проверка
условия
@@ROWCOUNT>0,
потому что не требуется подсчет общего
количества строк. Как только хотя бы одна
строка удовлетворяет условию IF EXISTS(),
запрос может продолжать выполнение.
106

107.

В следующем примере выражение IF EXISTS используется
для проверки наличия у сотрудника с кодом ‘BMO550262’
каких-либо объектов перед удалением его из базы. Если у
данного сотрудника имеются объекты, удаление не
производится.
IF EXISTS(SELECT * FROM PROPERTY WHERE
STAFF_NO = ‘BMO550262’)
PRINT 'Невозможно удалить сотрудника
поскольку в базе имеются связанные с ним
записи'
ELSE
DELETE * FROM STAFF WHERE STAFF_NO =
‘BMO550262’
107

108.

3. Процедура для повышения стипендии
студентам, прослушавшим спецкурс по
заданному предмету. В процедуру будут
передаваться
следующие
параметры:
@NOM_ZACH – номер зачетки, @PKOD – код
предмета и @IND – процент индексации
стипендии.
108

109.

CREATE PROC IND_STIP
(@NOM_ZACH int,
@PKOD smallint,
@IND real)
AS
IF EXISTS (SELECT PKOD, NOM_ZACH
FROM USP
WHERE NOM_ZACH = @NOM_ZACH
AND PKOD = @PKOD)
UPDATE STUDENTS SET STIP = STIP*@IND
WHERE NOM_ZACH = @NOM_ZACH
109

110.

Для запуска процедуры:
EXEC IND_STIP @NOM_ZACH = 1298,
@PKOD =1212, @IND=1.2
110

111.

4. Создать процедуру для повышения
заработной платы сотрудника только в
том случае, если за ним закреплён хотя
бы один объект собственности в таблице
Property (номер сотрудника и процент
повышения
заработной
платы
передаются
в
процедуру
как
параметры).
111

112.

CREATE PROC NEW_SALARY
(@Staff_no char(9),
@Procent decimal)
AS
IF EXISTS (SELECT property_no
FROM PROPERTY
WHERE Staff_No= @Staff_No )
UPDATE STAFF SET Salary=Salary*(100+ @Procent)/100
WHERE Staff_No= @STAFF_NO
Для запуска процедуры:
EXEC NEW_SALARY @Staff_No = ‘BMO5502601’, @PROCENT=10
112

113.

CREATE PROC NEW_SALARY
(@Staff_no char(9))
AS
IF (SELECT count(*)
FROM PROPERTY
WHERE Staff_No= @Staff_No )<3
begin
UPDATE STAFF SET Salary=Salary*2
WHERE Staff_No= @STAFF_NO
end
ELSE
begin
UPDATE STAFF SET Salary=Salary*4
WHERE Staff_No= @STAFF_NO
end
113

114.

Пример использования вложенных процедур.
Разработать процедуру для получения количества
сотрудников, получающих заработную плату выше
средней
CREATE PROC my_proc1
AS
BEGIN
DECLARE @m money
SELECT @m=AVG(SALARY) FROM STAFF
RETURN @m
END
DECLARE @AVSAL money
exec @AVSAL=my_proc1
select @AVSAL
go
114

115.

ALTER PROC my_proc2 @count_number INT
OUTPUT
AS
BEGIN
DECLARE @AVSAL money
EXEC @AVSAL = my_proc1
SELECT @count_number =COUNT(*) FROM STAFF
WHERE SALARY> @AVSAL
END
DECLARE @c INT
exec my_proc2 @c OUTPUT
select @c
115

116.

ALTER PROC my_proc1 @m money OUTPUT
AS
BEGIN
SELECT @m=AVG(SALARY) FROM STAFF
END
ALTER PROC my_proc2 @count_number INT OUTPUT
AS BEGIN
DECLARE @AVSAL money
EXEC my_proc1 @AVSAL OUTPUT
SELECT @count_number =COUNT(*) FROM STAFF WHERE
SALARY> @AVSAL
END
DECLARE @c INT
exec my_proc2 @c OUTPUT
select @c
116