Функции Multi-statement

Создание и изменение функций типа Multi-statement выполняется с помощью следующей команды:

<определение_мульти_функции>::={CREATE | ALTER }FUNCTION [владелец.] имя_функции( [ { @имя_параметра скаляр_тип_данных [=default]}[,...n]])RETURNS @имя_параметра TABLE <определение_таблицы> [WITH {ENCRYPTION | SCHEMABINDING} [,...n] ][AS]BEGIN<тело_функции>RETURN END

Использование большей части параметров рассматривалось при описании предыдущих функций.

Отметим, что функции данного типа, как и табличные, возвращают значение типа TABLE. Однако, в отличие от табличных функций, при создании функций Multi-statement необходимо явно задать структуру возвращаемого значения. Она указывается непосредственно после ключевого слова TABLE и, таким образом, является частью определения возвращаемого типа данных. Синтаксис конструкции <определение_таблицы> полностью соответствует одноименным структурам, используемым при создании обычных таблиц с помощью команды CREATE TABLE.

Набор возвращаемых данных должен формироваться с помощью команд INSERT, выполняемых в теле функции. Кроме того, в теле функции допускается использование различных конструкций языка SQL, которые могут контролировать значения, размещаемые в выходном наборе строк. При работе с командой INSERT требуется явно указать имя того объекта, куда необходимо вставить строки. Поэтому в функциях типа Multi-statement, в отличие от табличных, необходимо присвоить какое-то имя объекту с типом данных TABLE – оно и указывается как возвращаемое значение.

Завершение работы функции происходит в двух случаях: если возникают ошибки выполнения и если появляется ключевое слово RETURN. В отличие от функций скалярного типа, при использовании команды RETURN не нужно указывать возвращаемое значение. Сервер автоматически возвратит набор данных типа TABLE, имя и структура которого была указана после ключевого слова RETURNS. В теле функции может быть указано более одной команды RETURN.

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

Пример 11.3. Создать и применить функцию (типа multi-statement), которая для некоторого сотрудника выводит список всех его подчиненных (подчиненных как непосредственно ему, так и опосредствованно через других сотрудников).

Список сотрудников с указанием каждого руководителя представлен в таблице emp_mgr со следующей структурой:

CREATE TABLE emp_mgr(emp CHAR(2) PRIMARY KEY,-- сотрудникmgr CHAR(2)) -- руководитель

Пример данных в таблице emp_mgr показан ниже. Для упрощения иллюстрации имена сотрудников и их начальников представлены буквами латинского алфавита. У директора организации начальника нет (NULL).

emp mgr---------a NULLb ac ad ae ff bg bi ck d CREATE FUNCTION fn_findReports(@id_emp CHAR(2))RETURNS @report TABLE(empid CHAR(2) PRIMARY KEY, mgrid CHAR(2))ASBEGIN DECLARE @r INT DECLARE @t TABLE(empid CHAR(2) PRIMARY KEY, mgrid CHAR(2), pr INT DEFAULT 0)INSERT @t SELECT emp,mgr,0 FROM emp_mgr WHERE emp=@id_empSET @r=@@ROWCOUNTWHILE @r>0BEGIN UPDATE @t SET pr=1 WHERE pr=0 INSERT @t SELECT e.emp, e.mgr,0 FROM emp_mgr e, @t t WHERE e.mgr=t.empid AND t.pr=1 SET @r=@@ROWCOUNT UPDATE @t SET pr=2 WHERE pr=1ENDINSERT @report SELECT empid, mgrid FROM @tRETURN END

Пример 11.3. Создание функции, которая для некоторого сотрудника выводит список всех его подчиненных. (html, txt)

Применим созданную функцию для определения списка подчиненных сотрудника ‘b’:

SELECT * FROM fn_findReports('b')

Оператор возвращает следующие значения:

emp mgr-----------b a e f f b g b

Список подчиненных сотрудника ‘a’ создается с помощью оператора

SELECT * FROM fn_findReports('a') emp mgr---------a NULLb ac ad ae ff bg bi ck d

Другой оператор формирует список подчиненных сотрудника ‘e’:

SELECT * FROM fn_findReports('e')emp mgr--------e f

Список подчиненных сотрудника ‘c’ создает следующий оператор:

SELECT * FROM fn_findReports('c')emp mgr--------c ai c

Удаление любой функции осуществляется командой:

DROP FUNCTION {[ владелец.] имя_функции } [,...n]

<11 12 131415 16 17 >

Дата добавления: 2015-08-26; просмотров: 913;