Mathmech wiki

17 Процедуры и пользовательские функции

6 min read

Процедура

Процедуры

Синтаксис

Принимает набор данных (параметры), что-то делает, завершается

Нельзя выполнить в SELECT

Функции

Можно вставлять в SELECT

Скалярные функции

Возвращает единичное значение

Синтаксис

Табличные функции

Возвращает таблицу

Синтаксис

Конспект программиста-нефора Хранимые процедура

source

типы процедур

Сервер SQL поддерживает 5 типов встроенных процедур:

  • системные хранимые процедуры – хранятся в базе данных master. Система хранит процедуры (определяющиеся по префиксу sp_) предоставляющие эффективные методы получения информации из системных таблиц. Они позволяют системному администратору выполнять администраторские задачи над базой данных, которые обновляют необходимые таблицы напрямую. Системные встроенные процедуры могут быть выполнены из любой базы данных;
  • локальные хранимые процедуры – создаются в определенных пользовательских таблицах;
  • временные хранимые процедуры – могут быть локальными с именами, начинающимися с единичного знака. По аналогии с временными таблицами
  • удаленные хранимые процедуры – устаревшая технология MS SQL Server. На данные момент эту задачу решают распределенные запросы;
  • расширенные встроенные процедуры (содержат в имени префикс xp_) – разрабатываются в виде DLL (Dynamic Link Library, динамически подгружаемая библиотека) и выполняются вне окружения SQL Server. Обычно такие процедуры идентифицируются по префиксу xp_.

Возможности процедур

В процедуре вы можете:

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

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

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

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

синтаксис

CREATE PROC [ EDURE ] procedure_name [ ; number ] 
    [ { @parameter data_type } 
        [ VARYING ] [ = default ] [ OUTPUT ] 
    ] [ ,...n ] 
 
[ WITH 
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] 
 
[ FOR REPLICATION ] 
 
AS sql_statement [ ...n ]

Для имен процедур лучше всего выбрать префикс, который будет указывать, что эта процедура создана именно вами. Только не используйте для этого префикс sp_, чтобы не было конфликтов с системными процедурами.

Создание простейшей процедуры

CREATE PROCEDURE GetPhones
AS
SELECT pl.vcFamil, pl.vcName, pl.vcSurName, 
  dDateBirthDay, vcPhoneNumber
FROM tbPeoples pl, tbPhoneNumbers pn
WHERE pn.idPeoples=*pl.idPeoples

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

EXEC GetPhones

Использование параметров

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

CREATE PROCEDURE GetPhones 
   @Famil varchar(50)
AS
SELECT pl.vcFamil, pl.vcName, pl.vcSurName, 
  dDateBirthDay, vcPhoneNumber
FROM tbPeoples pl, tbPhoneNumbers pn
WHERE pn.idPeoples=*pl.idPeoples
  AND vcFamil=@Famil

Параметры перечисляются через запятую после имени процедуры в виде имя тип

Выполнение процедуры с параметром

EXECUTE GetPhones 'ВАСИЛЬКОВ'

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

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

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

  • Убираем дублирование кода
  • разделение логики приложений с другими, таким образом, гарантируется совместимый доступ и модификация данных
  • Сохранение целостности данных (дать возможность менять таблицы только через заранее определенные процедуры)
  • предоставляет механизм защиты
  • повышение производительности (план выполнения процедуры сохраняется в кэше после первого вызова)
  • Уменьшение сетевого трафика

Изменение процедур

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

Опции

  1. RECOMPILE – указывает на то, что MS SQL Server не должен сохранять план выполнения, компиляция будет происходит при каждом выполнении;
  2. ENCRYPTION – запись в таблице syscomments с текстом процедуры должна шифроваться Пример создания процедуры с опцией
CREATE PROCEDURE GetPhones
WITH ENCRYPTION
AS
SELECT pl.vcFamil, pl.vcName, pl.vcSurName, 
  dDateBirthDay, vcPhoneNumber
FROM tbPeoples pl, tbPhoneNumbers pn
WHERE pn.idPeoples=*pl.idPeoples

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

Просмотр текста (тела) незашифрованной продедуры

SELECT *
FROM sysobjects so, syscomments sc
WHERE name='GetPhones'
 AND so.id=sc.id

Хранимые функции

С SQL Server вы можете создавать ваши собственные функции, добавляющие и расширяющие функции, предоставляемые системой. Функции могут получать 0 или более параметров. Входные параметры могут быть любого типа, исключая timestamp, cursor, table

Сервер SQL поддерживает три типа функций определенных пользователем:

  • Скалярные функции – похожи на встроенные функции;
  • Функция, возвращающая таблицу - возвращает результат единичного оператора SELECT. Он похож на объект просмотра, но имеет большую эластичность благодаря использованию параметров.
  • Многооператорная функция не было на лекциях- возвращает таблицу созданную одним или несколькими операторами Transact-SQL, чем напоминает хранимые процедуры. В отличие от процедур, на такие функции можно ссылаться в WHERE как на объект просмотра.

Создание хранимой функции

синтаксис

скалярная функция

синтаксис

CREATE  FUNCTION [ owner_name. ] function_name 
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } 
      [ ,...n ] ] ) 
 
RETURNS scalar_return_data_type
 
[ WITH < function_option> [ [,] ...n] ] 
 
[ AS ]
 
BEGIN 
    function_body 
    RETURN scalar_expression
END

пример

CREATE FUNCTION GetSumm
 (@name varchar(50), @date datetime)
RETURNS numeric(10,2)
 BEGIN
  DECLARE @Summ numeric(10,2)
  SELECT @Summ = Цена*Количество
  FROM Товары
  WHERE [Название товара]=@name
    AND Дата=@date;
  RETURN @Summ
 END

Вызов функции (обязательно указание схемы*, в которой создана функция)

SELECT dbo.GetSumm('Картофель', '03.03.2005')

табличная функция

синтаксис

CREATE FUNCTION [ owner_name. ] function_name 
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } 
     [ ,...n ] ] ) 
 
RETURNS TABLE 
 
[ WITH < function_option > [ [,] ...n ] ] 
 
[ AS ] 
 
RETURN [ ( ] select-stmt [ ) ]

пример

CREATE FUNCTION GetPrice()
RETURNS TABLE
AS
 RETURN 
 (
  SELECT Дата, [Название товара], Цена, 
    Количество, Цена*Количество AS Сумма
  FROM Товары
 )

многооператорная функция

CREATE FUNCTION [ owner_name. ] function_name 
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } 
     [ ,...n ] ] ) 
 
RETURNS @return_variable TABLE < table_type_definition > 
 
[ WITH < function_option > [ [,] ...n ] ] 
 
[ AS ] 
 
BEGIN 
    function_body 
    RETURN
END
 
< function_option > ::= 
    { ENCRYPTION | SCHEMABINDING }
 
< table_type_definition > :: = 
    ( { column_definition | table_constraint } [ ,...n ] )

Упрощенный синтаксис

CREATE FUNCTION имя (параметры)
RETURNS  имя_переменной TABLE 
  (описание вида таблицы, 
   в которой будет представлен результат)
AS
 BEGIN
  Выполнение любого количества операций
  RETURN
 END

Пример создания

CREATE FUNCTION getFIO ()
RETURNS  @ret TABLE 
  (idPeoples int primary key,
   vcFIO varchar(100))
AS
 BEGIN
  INSERT @ret
  SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName
  FROM tbPeoples;
 
  RETURN
 END

Опции функций

  • SCHEMABINDING - объекты базы данных, на которые ссылается функция, не могут быть изменены (с использованием оператора ALTER) или удалены (с помощью оператора DROP)
  • SCHEMABINDING - было в опциях процедур пример SCHEMABINDING
CREATE FUNCTION GetPeoples2(@Famil varchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
 RETURN 
 (
  SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO
  FROM dbo.tbPeoples
  WHERE vcFamil=@Famil
 )

Функция связывается со схемой, если следующие ограничения истины:

  • все функции объявленные пользователем и просмотрщики на которые ссылается функция, также связаны со схемой с помощью опции SCHEMABINDING;
  • объекты, на которые ссылается функция, должны использовать имя из двух частей именования: owner.objectname. При создании функции GetPeoples2 ссылка на таблицу указана именно в таком формате – dbo.tbPeoples;
  • Функция и объекты должны быть расположены в одной базе данных;
  • Пользователь, который создает функцию, имеет право доступа ко всем объектам, на которые ссылается функция.

Изменение функций

Вы можете изменять функцию с помощью оператора ALTER FUNCTION

Recent Notes

Graph View

Backlinks