Теория проектирования реляционных баз данных

END

BEGIN ATOMIC

MODIFIES SQL DATA

LANGUAGE SQL

END

BEGIN ATOMIC

DETERMINISTIC

NO EXTERNAL ACTION

MODIFIES SQL DATA

LANGUAGE SQL

DETERMINISTIC

NO EXTERNAL ACTION

READS SQL DATA

LANGUAGE SQL

END

BEGIN ATOMIC

CONTAINS SQL

NO EXTERNAL ACTION

DETERMINISTIC

DETERMINISTIC

NO EXTERNAL ACTION

CONTAINS SQL

LANGUAGE SQL

RETURNSIN(X)/COS(X)

Пример 2. Определить скалярную функцию, которая переписывает символы некоторой заданной строки в обратном порядке.

CREATE FUNCTIONREVERSE(Instr VARCHAR(4000))

RETURNSVARCHAR(4000)

DECLARERevstr, Reststr VARCHAR(4000) DEFAULT'';

DECLARELen INT;

IFInstr IS NULL

THEN RETURNNULL;

END IF;

SET(Reststr, Len) = (Instr, LENGTH(Instr));

WHILELen > 0 DO

SET(Revstr, Reststr, Len) = (SUBSTR(Reststr, 1, 1) || Revstr,

SUBSTR(Reststr, 2, Len - 1), Len - 1);

END WHILE;

RETURNRevstr;

Пример 3. Определить табличную функцию, которая возвращает список сотрудников некоторого отдела, заданного его номером.

CREATE FUNCTIONDEPTEMPLOYEES (DeptNo CHAR(3))

RETURNS TABLE(EmpNo CHAR(6), LastName VARCHAR(15), FirstName VARCHAR(12))

RETURN SELECTEmpNo, LastName, FirstName

FROMEMPLOYEE

WHEREEMPLOYEE.WorkDept = DEPTEMPLOYEES.DeptNo

Пример 4. Изменить табличную функцию из примера 3, добавив в нее регистрацию (в таблице AUDIT) всех обращений к этой функции.

CREATE FUNCTIONDEPTEMPLOYEES (DeptNo CHAR(3))

RETURNS TABLE(EmpNo CHAR(6), LastName VARCHAR(15), FirstName VARCHAR(12))

INSERT INTOAUDIT VALUES(USER,

'Table: EMPLOYEE Prd: DEPTNO = ' || DeptNo);

RETURN SELECTEmpNo, LastName, FirstName

FROMEMPLOYEE

WHEREEMPLOYEE.WorkDept = DEPTEMPLOYEES.DeptNo;

Пример 5. Определить табличную функцию, которая изменяет количество некоторого элемента, заданного своим номером (ItemNo), в таблице оборудования (INVENTORY), на некоторую заданную величину (Amount). Функция должна вернуть обновленные данные для указанного элемента.

CREATE FUNCTION UPDATEINV(ItemNo VARCHAR(20), Amount INTEGER)

RETURNS TABLE (ProductName VARCHAR(20), Quantity INTEGER)

UPDATE INVENTORY AS I

SET Quantity = Quantity + Amount WHERE I.ItemID = ItemNo;

RETURN SELECT I.ItemName, I.Quantity

FROM INVENTORY AS I WHERE I.ItemID = ItemNo;

Приведенную в примере функцию можно вызвать следующим образом:

SELECT ProductName, Quantity

FROM TABLE(UPDATEINV('ISBN-0-8021-3424-6', 5)) AS T

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

SELECT NEWITEM.Id, TF.ProductName, TF.Quantity

FROM (VALUES ('ISBN-0-8021-3424-6', 2),

('ISBN-0-8021-4612-1', 5)) AS NEWITEM(Id, Quantity),

TABLE(UPDATEINV(NEWITEM.Id, NEWITEM.Quantity)) AS TF

В данном примере в конструкции FROM предложения SELECT использована конструкция VALUES для генерации временной таблицы (NEWITEM), строки которой должны быть модифицированы функцией

UPDATEINV. Так как колонки таблицы NEWITEM указываются в качестве аргументов функции

UPDATEINV, имена колонок должны быть уточнены именем таблицы. Кроме того, вызов табличной функции указывается последним в конструкции FROM.

Пример 6. Табличную функцию можно вызывать и в общем табличном выражении, если требуется написать более сложные запросы, в которых вызов табличной функции должен быть размещен в подзапросе, или в запросе необходимо использовать несколько обращений к табличной функции. Например, необходимо написать запрос, возвращающий стоимость единицы оборудования и его общую стоимость для всех обновленных элементов. Общая стоимость вычисляется умножением стоимости единицы оборудования на измененное количество оборудования. Стоимость единицы оборудования выбирается из таблицы

PRICELIST.

WITH NEWINV(ItemNo, Quantity) AS

(

SELECT Id, TF.Quantity

FROM (VALUES ('ISBN-0-8021-3424-6', 5),

('ISBN-0-8021-4612-1', 10)) AS NEWITEM(Id, Q),

TABLE(UPDATEINV(NEWITEM.Id, NEWITEM.Q)) AS TF

)

SELECT ItemNo, Quantity, UnitPrice,

(Quantity * UnitPrice) AS TotalInvValue

FROM NEWINV, PRICELIST

WHERE ItemNo = PRICELIST.ItemID