create function Foo1 (param1 nvarchar, param2 decimal(18,2))
return table (
id number,
nn nvarchar(50)
)
as
...
おなじみですよね？ そのような関数がMS SQLからADO Recordsetを完全に返した場合、Oracleにはそのような景品はありません。 ただし、オブジェクトモデルの適切な構造に準拠したい場合は、ADOを介して関数からデータセットを取得するだけです。
従業員と部署の2つのテーブルを作成しましょう。
--
create tablespace ALEX_DATA datafile 'C:\oracle\user_data\tblsp_alexdata.dat'
size 10M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 200M;
create tablespace ALEX_INDEX datafile 'C:\oracle\user_data\tblsp_alexix.dat'
size 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 200M;
/
--
create table ALEX.T_EMPLOYEES(
id number(5) not null,
id_department number(5) not null,
empinfo nvarchar2(50) not null
) tablespace ALEX_DATA;
create table ALEX.T_DEPARTMENTS(
id number(5) not null,
depinfo nvarchar2(50) not null
) tablespace ALEX_DATA;
/
--
create index IXPK_T_EMPLOYEES on ALEX.T_EMPLOYEES(id)
tablespace ALEX_INDEX;
create index IXPK_T_DEPARTMENTS on ALEX.T_DEPARTMENTS(id)
tablespace ALEX_INDEX;
/
--
alter table ALEX.T_DEPARTMENTS
add constraint PK_T_DEPARTMENTS primary key (ID) using index IXPK_T_DEPARTMENTS;
/
alter table ALEX.T_EMPLOYEES
add constraint PK_T_EMPLOYEES primary key (ID) using index IXPK_T_EMPLOYEES
add constraint FK_T_DEPARTMENTS foreign key (id_department)
references ALEX.T_DEPARTMENTS(id);
/
---
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (1, ' ');
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (2, ' ');
insert into ALEX.T_DEPARTMENTS (ID, DEPINFO)
values (3, '');
commit;
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(1, 1, '');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(2, 1, '');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(3, 2, '');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(4, 3, '');
insert into ALEX.T_EMPLOYEES (ID, ID_DEPARTMENT, EMPINFO)
values(5, 3, '');
commit;
私たちの目標は、 IDがパラメーターとして渡される部門の従業員のリストを返す関数を作成することです。
最初に、テーブルによって返されるデータ型を記述する必要があります。
-- , GetEmployees
type rowGetEmployees is record(
l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, -- empinfo
l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
);
これは行データ型です。 TYPE属性は、変数に指定されたフィールドと同一のタイプを宣言します。 2番目のタイプを作成します。
type tblGetEmployees is table of rowGetEmployees;
これは、 rowGetEmployeesタイプの行のテーブルです。 このタイプの変数は、関数によって返されます。
function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined;
パラメータが渡されない場合、すべての従業員のリストを返します。 パイプライン化された属性は、関数がパイプライン化されることを意味し、 パイプ行ディレクティブが呼び出されるとすぐに結果がクライアントに返されるため、 returnステートメントはオプションです。 実際、カーソルは関数の本体のクエリからの結果セットを通過し、各反復で現在の行をレコードに追加します。
データ型と関数をパッケージに入れます。 出口には
create or replace package ALEX.P_MY1 is
-- , GetEmployees
type rowGetEmployees is record(
l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, -- empinfo
l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
);
-- rowGetEmployees
type tblGetEmployees is table of rowGetEmployees;
--
function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined;
end P_MY1;
create or replace package body ALEX.P_MY1 is
function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined
is
begin
if prm_depID is null then
for curr in
(
select emp.empinfo, dep.depinfo
from ALEX.T_DEPARTMENTS dep inner join
ALEX.T_EMPLOYEES emp on dep.id = emp.id_department
) loop
pipe row (curr);
end loop;
else
for curr in
(
select emp.empinfo, dep.depinfo
from ALEX.T_DEPARTMENTS dep inner join
ALEX.T_EMPLOYEES emp on dep.id = emp.id_department
where dep.id = prm_depID
) loop
pipe row (curr);
end loop;
end if;
end GetEmployees;
end P_MY1;
電話をかける：
SQL> select * from TABLE(ALEX.P_MY1.GetEmployees);
L_EMPINFO L_DEPINFO
---------------- --------------------------------
-----------------
-----------------
-----------------
-----------------
-----------------
SQL> select * from TABLE(ALEX.P_MY1.GetEmployees(1));
L_EMPINFO L_DEPINFO
---------------------------------------------------
-----------------
-----------------