Информатика и выч. техника \ Базы данных

Основы языка Transact-SQL. Переменные. Встроенные функции. Скалярные функции, страница 6

Элементы синтаксиса соответствуют приведенным для скалярных функций. Синтаксис конструкции <определение_табличного_типа_данных> : <определение_табличного_типа_ данных> ::= ({<определение_столбца> | <ограничение_таблицы>} [, …n] ) где элементы <определение_столбца>, <ограничение_таблицы> полностью соответствуют аналогичным элементам, используемым при создании таблиц с помощью команды CREATE TABLE.

78

Набор данных, возвращаемых функцией, должен формироваться с помощью команды INSERT, выполняемой в теле функции. В теле функции допускаются различные конструкции T-SQL, контролирующие значения, размещаемые в наборе записей. При работе с командой INSERT явно указывается имя объекта, в который вставляются строки. Это имя указывается с помощью табличной переменной с именем возвращаемая_переменная.

79

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

80

  • Функции Multi-statement сочетают свойства:
    • скалярных функций - содержать сложный программный код;
    • функций Inline - возвращать табличный результат.
  • Эти функции: 1) создают табличную переменную и заполняют ее в теле функции; 2) возвращают таблицу-результат, которая может использоваться в инструкциях SELECT.
  • Преимущество функций Multi-statement - возможность формировать результат с помощью нескольких инструкций SQL (пакета инструкций),
  • а затем напрямую использовать этот набор данных в инструкциях SELECT.

81

Пример 1. Функция Multi-statement, которая выводит сведения о студентах (фамилия, имя) по номеру группы. CREATE FUNCTION Students(@Gr NCHAR(6)) RETURNS @report TABLE(Фамилия NCHAR(20), Имя NCHAR(20)) AS BEGIN INSERT INTO @report SELECT Фамилия, Имя FROM Студенты WHERE (Студенты.Группа=@Gr) RETURN END Пример вызова этой функции: SELECT * FROM Students('ИТ-31')

82

Пример 2. Функция Multi-statement, которая для некоторого сотрудника выводит список всех его подчиненных (как непосредственно ему, так и опосредствованно через других сотрудников). Список сотрудников с указанием каждого руководителя представлен в таблице emp_mgr со следующей структурой: CREATE TABLE emp_mgr (emp CHAR(2) PRIMARY KEY, -- сотрудник mgr CHAR(2)) -- руководитель

83

Пример данных в таблице emp_mgr. Для упрощения имена сотрудников и их начальников представлены буквами латинского алфавита. У директора организации начальника нет (NULL). emp mgr ---------------------- a NULL b a c a d a e f f b g b i c k d

84

CREATE FUNCTION List(@id_emp CHAR(2)) RETURNS @report TABLE(empid CHAR(2) PRIMARY KEY, mgrid CHAR(2)) AS BEGIN 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_emp SET @r=@@ROWCOUNT

85

WHILE @r>0 BEGIN 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=1 END INSERT @report SELECT empid, mgrid FROM @t RETURN END

86

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

Применим созданную функцию для определения списка подчиненных сотрудника ‘b’: SELECT * FROM List('b') Оператор возвращает следующие значения: emp mgr --------------- b a e f f b g b

87

Список подчиненных сотрудника ‘a’ создается с помощью оператора SELECT * FROM fn_findReports('a') emp mgr ----------------- a NULL b a c a d a e f f b g b i c k d

88

Скачать файл