Файл: Министерство науки.docx

ХРАМИНЫЕ ПРОЦЕДУРЫ

1. 
   ЦЕЛЬ РАБОТЫ
   

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

В связи с этим задачами работы являются:

1. 
   Изучение принципов работы и создание хранимых процедур и пользовательских функций в среде MS SQL Server.
   
2. 
   Создание хранимых процедур и пользовательских функций в соответствии с заданием к лабораторной работе.
   
   2. 
      ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ
      

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

Наиболее распространенное средство реализации бизнес- логики – хранимые процедуры сервера.

Хранимые процедуры – это модули, хранящиеся непосредст- венно в базе данных в откомпилированном виде и которые могут запускаться пользователями или приложениями, работающими с базой данных. Хранимые процедуры обычно пишутся либо на спе- циальном процедурном расширении языка SQL (например, PL/SQL для ORACLE или Transact-SQL для MS SQL Server), или на некото- ром универсальном языке программирования, например, C++, с включением в код операторов SQL в соответствии со специальными правилами такого включения. Основное назначение хранимых про- цедур – реализация бизнес-процессов предметной области на сто- роне сервера.

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

---

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

CREATE PROCedure [владелец.]имя_процедуры [@параметр1],

[@параметр2] AS

Операторы_SQL
где параметр – @имя_параматра тип_данных [=default] [OUTPUT];

[=default] – значение по умолчанию присеваемое параметру, при отсутствии передаваемого значения;

[OUTPUT] – ключевое слово, указывающее, что при изменении параметра в ходе выполнения процедуры новое значение возвраща- ется в переменную, используемую для вызова этой процедуры.

Процедура может быть создана только в текущей базе данных, за исключением временных процедур, которые создаются в tempdb. Для создания временных процедур следует начинать ее имя с «#» или «##». Длина имени хранимой процедуры вместе с ## не может превышать 20 символов. Одна процедура может вызывать другую процедуру, уровень вложенности не может превышать 16, текущий уровень вложенности можно узнать из глобальной переменной @@NESTLEVEL

Пользователь может создавать свои системные процедуры; они начинаются с символов sp_. При попытке выполнения такой процедуры она сначала ищется в текущей базе данных, в случае же неудачи – в базе данных master. Таблицы, используемые в систем- ной процедуре, определяемой пользователем, также сначала оты- скиваются в текущей базе данных, и если это не удалось – в базе данных master.

Вызов хранимой процедуры осуществляется оператором EXEC, например:
EXEC имяПроцедуры [параметр]
В виде параметра может выступать как переменная, так и кон- станта.

Пример:

Константа:
EXEC

---

СводСотрудников «Инженер»
Использование переменной в качестве параметра:
DECLARE @Должность INT

SET @Должность = «Инженер» EXEC СводСотрудников @Должность

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

ALTER PROCedure [владелец.]имя_процедуры [@параметр1],

[@параметр2] AS

Операторы_SQL

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

DROP PROCEDURE имя_процедуры

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

Для использования переменных их необходимо объявить с по- мощью ключевого слова DECLARE
DECLARE имяПерем типДаных
где имяПерем – идентификатор переменной. Он должен быть без пробелов. В качестве первого символа должен использоваться сим- вол «@». В среде SQL server могут быть созданы глобальные пере- менные, видимые на уровне сервера, данные переменные начина- ются с двух символов «@@». Локальные переменные, начинаю- щиеся с одного символа «@» являются локальные и видимы только внутри одной транзакции.
Для присвоения значений переменным используется оператор Set или Select.

Формат операторов SET и Select при присвоении значений.

---

Преимущества использования процедур

Использовать хранимые процедуры целесообразнее, чем от- дельные операторы SQL по следующим причинам:

• 
  Операторы хранимой процедуры всегда находятся в базе данных.
  
• 
  Операторы хранимой процедуры уже проверены и находят- ся в готовом для использования виде.
  
• 
  Возможность использования процедур позволяет использо- вать модульное программирование.
  
• 
  Хранимые процедуры могут вызывать другие процедуры и функции.
  
• 
  Сохраненные процедуры могут вызываться любыми прило- жениями.
  
• 
  При использовании сохраненных процедур результат ответ от базы данных как правило получается быстрее.
  
• 
  Использование процедур принципиально упрощено в СУБД.
  

---

Пользовательские функции UDF (user-defined function)

Основное отличие UDF от хранимых процедур заключается в том, что функция обязательно должна вернуть хотя бы какое-то значение. К сожалению, UDF имеют некоторые ограничения. Нель- зя, например, изменять данные в таблицах БД, выводить данные с помощью команд print и select. Внутри UDF нельзя использовать недетерминированные функции типа GETDATE(). В SQL Server имеется 3 типа пользовательских функций: Scalar, Multi-statement, InLine.

Скалярная функция может возвращать значения любого ска- лярного типа данных, кроме данных типа text, image, table. Такая функция может объединять несколько команд языка T-SQL, нахо- дящихся в блоке BEGIN…END, например,
CREATE FUNCTION FunState

(@a varchar(20)) RETURNS varchar(20) as

BEGIN
IF @a IS NULL

SET @a = «Not Applicable» RETURN @a

END
Обратиться к такой функции можно с помощью конструкции select, указав вместо поля таблицы значение функции с параметром:
SELECT pub_name, City, dbo.FunState(state) AS State FROM dbo.publishers

---