Табличные функции
В SQL:2003 поддерживается механизм табличных функций, т.е. функций, вызываемых из SQL и возвращающих значение-“таблицу”. В связи с отсутствием поддержки в SQL “типа таблиц” типом результата табличной функции в действительности является тип мультимножеств (типом элементов которого является соответствующий анонимный строчный тип). Однако к результату табличной функции можно адресовать запросы таким же образом, как и к таблице.
Разработчики стандарта SQL:2003 полагают, что табличные функции полезны сами по себе, вне связи с поддержкой общего механизма мультимножеств. По этой причине стандарт не требует поддержки мультимножеств в качестве обязательного условия поддержки табличных функций.
Происхождение термина “табличная функция” очевидно. В синтаксических конструкциях определения и вызова табличных функций их близость с таблицами подчеркивается требованием наличия ключевого слова TABLE в различных местах выражений. Например, в разделе RETURN определения табличной функции указывается ключевое слово TABLE, вслед за которым перечисляются пары имя_столбца/тип_данных. На рис. 1 приведены определения двух табличных функций, первая из которых является внешней, а вторая содержит тело, определяемое на языке SQL.
CREATE FUNCTION timetable ()
RETURNS TABLE (
CINEMA_NAME VARCHAR (20),
MOVIE_NAME VARCHAR (4),
TIME_TABLE TIME )
NOT DETERMINISTIC
NO SQL
LANGUAGE C
EXTERNAL
PARAMETER STYLE SQL
(a) Определение внешней табличной функции
CREATE FUNCTION DEPTEMP (DEPTNO INTEGER)
RETURNS TABLE (
EMPNO INTEGER,
EMPNAME VARCHAR (20))
LANGUAGE SQL
READS SQL DATA
DETERMINISTIC
RETURN TABLE (
SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE EMP.DEPT_NO = DEPTEMP.DEPTNO )
(b) Определение табличной функции с телом, задаваемым на языке SQL
Рис. 1. Определения табличных функций
Внешние табличные функции позволяют запрашивать данные, которые не хранятся в базовых таблицах и являются внешними по отношению к базе данных. Например, табличная функция, определение которой приведено на рис. 1a, возвращает множество строк, представляющих кинотеатры, названия демонстрируемых в них фильмов и расписание сеансов.
В определении внешней функции может содержаться ряд опций, которые влияют на ожидаемое поведение функции. В нашем случае функция реализуется на языке C (для краткости, на рис. A.1a не приведен код тела функции). Об этом факте свидетельствует наличие раздела LANGUAGE C, который определяет конкретный способ передачи параметров. Наличие раздела NO SQL говорит о том, при выполнении функции не будут производиться обращения к SQL-процессору для обработки операторов SQL. Этот факт нужно учитывать при управлении транзакциями. Наличие раздела NOT DETERMINISTIC означает, что функция может возвращать разные результаты при разных вызовах с одним и тем же набором аргументов. Знание этого факта влияет на работу оптимизатора SQL-запросов. Наконец, наличие раздела PARAMETER STYLE SQL говорит о том, что аргументы и результаты функции могут являться неопределенными значениями.
С другой стороны, табличные функции с телом, специфицируемым на языке SQL, представляют собой параметризованные представления (parameterized views (обычные представления в SQL фиксируются в момент создания). Например, у табличной функции, определение которой приведено на рис. 1b, имеется один параметр – DEPTNO. Этот параметр используется в разделе WHERE
запроса в теле функции и определяет результирующее множество строк. Разные значения аргумента вызова функции приведут к разным возвращаемым ее множествам строк (строк служащих, работающих в указываемом отделе).
И в этом случае в определении функции могут содержаться необязательные разделы, влияющие на поведение функции. Наличие раздела LANGUAGE SQL говорит о том, что тело функции написано на языке SQL (в нашем случае тело состоит из единственного оператора RETURN). Раздел READS SQL DATA означает, что доступ к данным, хранимым в базе данных, будет производиться в режиме только чтения. Наличие раздела DETERMINISTIC свидетельствует о том, что функция возвращает один и тот же результат при наличии одних и тех же аргументов и одного и того же состояния базы данных.
Наиболее естественным местом вызова табличной функции является раздел FROM оператора выборки (хотя табличные функции могут вызываться и во многих других контекстах). В этом случае вызов функции предваряется ключевым словом TABLE и может встречаться везде, где может присутствовать ссылка на таблицу. Ниже приведен простой пример запроса, в котором используется вызов табличной функции (“Получить названия кинотеатров, в которых демонстрируется фильм «Властелин колец. Две крепости», и расписание сеансов):
SELECT CINEMA_NAME, TIME_TABLE
FROM TABLE(timetable ()) AS TT
WHERE TT.MOVIE_NAME = ‘The Lord of Rings: Two Towers’;
