Информационное обеспечение управляющих систем реального времени
..pdfСоздание встроенной функции с табличным значением
Результатом следующего примера является встроенная функция, возвращающая табличное значение в базу данных AdventureWorks2012. Для каждого из товаров, проданных в магазине, она возвращает три столбца: ProductID, Name и статистику с начала года по магазину – YTD Total.
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP |
|
FUNCTION |
Sales.ufn_SalesByStore; |
|
|
GO |
|
FUNCTION |
CREATE |
|
|
Sales.ufn_SalesByStore (@storeid int) |
||
RETURNS TABLE |
|
|
AS |
|
|
RETURN |
|
|
( |
P.ProductID, |
P.Name, |
SELECT |
||
SUM(SD.LineTotal) AS 'Total' |
|
|
FROM Production.Product AS P |
AS SD ON |
|
JOIN Sales.SalesOrderDetail |
SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid GROUP BY P.ProductID, P.Name
); GO
При вызове этой функции выполняется следующий запрос:
SELECT * FROM Sales.ufn_SalesByStore (602);
221
Создание функции с табличным значением из нескольких инструкций
В следующем примере создается функция с табличным значением fn_FindReports(InEmpID) в базе данных AdventureWorks2012. Если ей передать допустимый идентификатор сотрудника, она вернет таблицу, где содержатся имена всех сотрудников, которые прямо или опосредованно перед ним отчитываются. В функции для построения иерархического списка сотрудников используется рекурсивное обобщенное табличное выражение (CTE).
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufn_FindReports; GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
EmployeeID int primary key NOT NULL, FirstName nvarchar(255) NOT NULL, LastName nvarchar(255) NOT NULL, JobTitle nvarchar(50) NOT NULL, RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
AS (
222
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
FROM HumanResources.Employee e INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @InEmpID UNION ALL
SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
FROM HumanResources.Employee e INNER JOIN EMP_cte
ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
)
--copy the required columns to the result of the function
INSERT @retFindReports
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM EMP_cte RETURN
END; GO
--Example invocation
SELECT EmployeeID, FirstName,
LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);
GO
223
Функции, определяемые пользователем в Oracle
В примере представлена функция, возвращающая квадрат значения original:
DECLARE
FUNCTION square(original NUMBER) RETURN NUMBER AS original_squared
NUMBER; BEGIN
original_squared:= original * original;
RETURN original_squared; END;
BEGIN DBMS_OUTPUT.PUT_LINE(square(100));
END;
/
2.31. Создание пакета Oracle
Пакет представляет собой объект схемы, в котором логически связаны PL/SQL-типы, переменные и подпрограммы. Пакеты состоят из двух частей: спецификации (spec) и тела (body). Спецификация представляет интерфейс к пакету. Здесь объявляются типы, переменные, константы, исключения, курсоры и подпрограммы, на которые есть ссылки вне пакета. Тело определяет запросы для курсоров и код подпрограммы. Возможно запустить тело пакета и отредактировать без изменения spec пакета.
Чтобы создать spec пакета, используется выражение
CREATE PACKAGE. Выражение CREATE PACK-AGE BODY определяет тело пакета.
Пример Package and Package Body:
CREATE OR REPLACE PACKAGE emp_actions AS - - package specification
224
PROCEDURE hire_employee (employee_id NUMBER, last_name VARCHAR2,
first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2,
hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER,
manager_id NUMBER, department_id NUMBER); PROCEDURE fire_employee (emp_id NUMBER); FUNCTION num_above_salary (emp_id NUMBER)
RETURN NUMBER; END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS -- package body
-- code for procedure hire_employee PROCEDURE hire_employee (employee_id
NUMBER, last_name VARCHAR2,
first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2,
hire_date DATE, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER,
manager_id NUMBER, department_id NUMBER)
IS |
|
|
|
BEGIN |
INTO |
employees |
VALUES |
INSERT |
|||
(employee_id, last_name, first_name, |
|||
|
email,phone_number, |
hire_date, |
|
|
job_id, salary, commission_pct, |
||
|
manager_id,department_id); |
END hire_employee;
-- code for procedure fire_employee PROCEDURE fire_employee (emp_id NUMBER)
IS BEGIN
DELETE FROM employees WHERE employee_id = emp_id;
END fire_employee;
-- code for function num_above salary
225
FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS
emp_sal NUMBER(8,2); num_count NUMBER; BEGIN
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT COUNT(*) INTO num_count FROM employees WHERE salary > emp_sal;
RETURN num_count;
END num_above_salary; END emp_actions;
/
Вызов процедуры в теле пакета
Для вызова подпрограмм в пакете Oracle нужно знать их имена и параметры. Для вызова процедуры из пакета emp_actions можно выполнить инструкции в блоке BEGIN.. END или с SQL-оператор CALL. Обратите внимание на использование имени пакета в качестве префикса к имени процедуры [12].
Пример вызова процедуре в пакете:
CALL emp_actions.hire_employee(300, 'Belden', 'Enrique', 'EBELDEN',
'555.111.2222', '31-AUG-04', 'AC_MGR', 9000,.1, 101, 110);
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Number of employees with higher salary: ' ||
TO_CHAR(emp_actions.num_above_salary(120)) );
emp_actions.fire_employee(300); END;
/
226
В примере для передачи значений используются пара-
метры emp_id, emp_lname и emp_fname.
BEGIN emp_actions.hire_employee(:emp_id,:em
p_lname,:emp_fname);
Другой пример пакета:
create or replace package DeptAPI is type t_deptcursor is ref cursor
return dept%rowtype;
procedure SelectRecords(p_cursor in out t_deptcursor,
p_order varchar2);
procedure LockRecord(p_deptno dept.deptno%type);
procedure InsertRecord(p_deptno in
out dept.deptno%type, |
|
|
|
||
p_dname dept.dname%type, |
|
||||
p_loc dept.loc%type); |
|
|
|||
procedure |
UpdateRecord(p_olddeptno |
||||
dept.deptno%type, |
|
|
|
|
|
p_deptno dept.deptno%type, |
|
||||
p_dname dept.dname%type, |
|
||||
p_loc dept.loc%type); |
|
|
|||
procedure |
|
DeleteRecord(p_deptno |
|||
dept.deptno%type); |
|
|
|
|
|
end DeptAPI; |
|
|
|
|
|
/ |
|
|
|
|
|
create |
or |
replace |
package |
body |
|
DeptAPI is |
|
|
|
|
|
-- |
Open |
a |
cursor |
to |
select |
departments in a particular order
227
procedure SelectRecords(p_cursor in out t_deptcursor,
p_order varchar2) |
is |
begin |
= 'DNAME' |
if upper(p_order) |
|
then |
select * from dept |
open p_cursor for |
order by dname, loc;
elsif upper(p_order) = 'DEPTNO' then
open p_cursor for select * from dept
order by deptno; |
|
|
elsif upper(p_order) = 'LOC' |
|
|
then |
|
dept |
open p_cursor for select * from |
||
order by loc, dname; |
|
|
else |
for select * |
from |
open p_cursor |
||
dept; |
|
|
end if; |
|
|
end; |
|
|
-- Lock a department record |
|
|
procedure |
LockRecord(p_deptno |
|
dept.deptno%type) is |
|
|
dummy varchar2(1); begin
select 'x' into dummy from dept where deptno = p_deptno for update
nowait; exception
when timeout_on_resource then raise_application_error(-20000,
'This department is locked'); when no_data_found then
raise_application_error(-20000, 'This department was deleted');
228
end;
-- Insert a new department
procedure InsertRecord(p_deptno in out dept.deptno%type,
p_dname dept.dname%type, p_loc dept.loc%type) is begin
--Determine the maximum department number if necessary
if p_deptno is null then
select nvl(max(deptno), 0) + 1 into p_deptno from dept;
end if;
--Insert the new record
insert into dept (deptno, dname,
Loc)
values (p_deptno, p_dname, p_loc); end;
-- Update a department
procedure UpdateRecord(p_olddeptno dept.deptno%type,
p_deptno dept.deptno%type, p_dname dept.dname%type, p_loc dept.loc%type) is begin
update dept
set deptno = p_deptno, dname = p_dname,
loc = p_loc
where deptno = p_olddeptno; exception when dup_val_on_index then raise_application_error(-20000,
'This department already exists'); end;
229
-- Delete a department
procedure DeleteRecord(p_deptno dept.deptno%type) is
begin
delete from dept where deptno = p_deptno;
end;
end DeptAPI;
/
Использование курсора в пакете:
CREATE PACKAGE emp_stuff AS
CURSOR c1 RETURN employees%ROWTYPE; -- declare cursor spec
END emp_stuff;
/
CREATE PACKAGE BODY emp_stuff AS CURSOR c1 RETURN employees%ROWTYPE
IS
SELECT * FROM employees WHERE salary > 2500; -- define cursor
END emp_stuff;
/
Пример использования packaged cursor с пакетом Oracle:
DECLARE emp_rec employees%ROWTYPE; BEGIN
OPEN emp_stuff.c1; LOOP
FETCH emp_stuff.c1 INTO emp_rec; -- do processing here...
EXIT WHEN emp_stuff.c1%NOTFOUND; END LOOP;
CLOSE emp_stuff.c1;
230