ओरेकल रिटर्निंग ए टेबल में कार्य

जो लोग MSSQL से Oracle में आए थे, वे (मेरे जैसे) बहुत आश्चर्यचकित हुए होंगे।

create function Foo1 (param1 nvarchar, param2 decimal(18,2))
return table (
id number,
nn nvarchar(50)
)
as
...

यह परिचित है, है ना? यदि ऐसा फ़ंक्शन पूरी तरह से एमएस एसक्यूएल से एडीओ रिकॉर्डसेट लौटाता है, तो ओरेकल के पास ऐसा फ्रीबी नहीं है। हालाँकि, ADO के माध्यम से फ़ंक्शन से डेटा सेट प्राप्त करना आवश्यक है, यदि हम ऑब्जेक्ट मॉडल के सक्षम ढांचे का पालन करना चाहते हैं।

चलो दो टेबल बनाते हैं - कर्मचारी और विभाग।

--
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;

हमारा लक्ष्य एक फ़ंक्शन लिखना है जो विभाग के कर्मचारियों की सूची लौटाएगा, जिनकी आईडी एक पैरामीटर के रूप में पारित की गई है।
सबसे पहले, हमें तालिका द्वारा लौटाए गए डेटा प्रकार का वर्णन करना होगा।

-- , GetEmployees
type rowGetEmployees is record(
l_empinfo ALEX.T_EMPLOYEES.EMPINFO%TYPE, -- empinfo
l_depinfo ALEX.T_DEPARTMENTS.DEPINFO%TYPE
);

यह पंक्ति डेटा प्रकार है। TYPE विशेषता वैरिएबल के लिए निर्दिष्ट फ़ील्ड के समान एक प्रकार घोषित करती है। दूसरा प्रकार बनाएं:

type tblGetEmployees is table of rowGetEmployees;

यह एक प्रकार की पंक्तियों की एक तालिका है। इस प्रकार का एक वेरिएबल हमारे फंक्शन द्वारा लौटाया जाएगा:

function GetEmployees
(prm_depID number default null)
return tblGetEmployees
pipelined;

यदि पैरामीटर पारित नहीं हुआ है, तो हम सभी कर्मचारियों की एक सूची वापस करेंगे। Pipelined विशेषता का अर्थ है कि फ़ंक्शन pipelined है, परिणाम ग्राहक को तुरंत दिया जाता है जब पाइप पंक्ति निर्देश कहा जाता है, इसलिए रिटर्न स्टेटमेंट वैकल्पिक है। वास्तव में, कर्सर फ़ंक्शन के शरीर में क्वेरी से निर्धारित परिणाम से गुजरता है, जो प्रत्येक पुनरावृत्ति में वर्तमान लाइन को रिकॉर्ड में जोड़ता है।
पैकेज में डेटा प्रकार और फ़ंक्शन डालें। हमारे पास से बाहर निकलने पर

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
---------------------------------------------------
-----------------
-----------------