使用ORACLE表函数连接2个大表做查询

使用ORACLE表函数连接2个大表做查询

create or replace type t_t1_task as object
(
MONTHDAY CHAR(4),
TASKEDID VARCHAR2(20),
SENDTIME DATE,
BATCHID VARCHAR2(20),
MSGID VARCHAR2(40),
RECEIVERINFO VARCHAR2(20),
TASKDID_GUID VARCHAR2(40),
ERRORCODE VARCHAR2(10)
);

create or replace type t_tab_task as table of t_t1_task;

create or replace package pkg_report is
--[1]表函数,供主过程调用
function f_task_report(v_t_tab_task in t_tab_task) return t_tab_task;
--[主过程]返回数据
--供调用,做联接
procedure p_task_report(i_count in integer,
o_data out sys_refcursor,
i_t2_monthday in varchar2,
i_t1_monthday in varchar2,
i_t2_vasid in varchar2);
end pkg_report;

create or replace package body pkg_report is
--全局变量,在主过程和表函数之间传递数据
v_t_tab_task t_tab_task := t_tab_task();
--[1]--[1]表函数,供主过程调用
function f_task_report(v_t_tab_task in t_tab_task) return t_tab_task is
begin
return v_t_tab_task;
end f_task_report;
--[2] [主过程]返回数据
--供调用,做联接
procedure p_task_report(i_count in integer,
o_data out sys_refcursor,
i_t2_monthday in varchar2,
i_t1_monthday in varchar2,
i_t2_vasid in varchar2) is
cursor cur_report(i_t1_monthday varchar2) is
select t.monthday,
t.taskedid,
t.sendtime,
t.batchid,
t.msgid,
t.receiverinfo,
t.taskdid_guid,
t.errorcode
from if_ms_media_task t
where t.monthday = i_t1_monthday;
type t_sendtime is table of if_ms_media_task.sendtime%type;
v_t_sendtime t_sendtime;
type t_msgid is table of if_ms_media_task.msgid%type;
v_t_msgid t_msgid;
type t_taskid_guid is table of if_ms_media_task.taskdid_guid%type;
v_t_taskid_guid t_taskid_guid;
type t_errorcode is table of if_ms_media_task.errorcode%type;
v_t_errorcode t_errorcode;
type t_taskid is table of if_ms_media_task.taskedid%type;
v_t_taskid t_taskid;
type t_receifo is table of if_ms_media_task.receiverinfo%type;
v_t_receifo t_receifo;
type t_month is table of if_ms_media_task.monthday%type;
v_t_month t_month;
type t_batchid is table of if_ms_media_task.batchid%type;
v_t_batchid t_batchid;
v_result_sql varchar2(3000);
begin
open cur_report(i_t1_monthday);
loop
fetch cur_report bulk collect
into v_t_month, v_t_taskid, v_t_sendtime, v_t_batchid, v_t_msgid, v_t_receifo, v_t_taskid_guid, v_t_errorcode limit i_count;

for ii_count in 1 .. v_t_month.count loop
v_t_tab_task.extend();
v_t_tab_task(ii_count) := t_t1_task(v_t_month(ii_count),
v_t_taskid(ii_count),
v_t_sendtime(ii_count),
v_t_batchid(ii_count),
v_t_msgid(ii_count),
v_t_receifo(ii_count),
v_t_taskid_guid(ii_count),
v_t_errorcode(ii_count));
end loop;
v_result_sql := 'SELECT /*+ leading(t1) */ DISTINCT t2.MONTHDAY,t2.MEDIATASKID,t2.MEDIATYPE,' ||
't2.PRIORITY,t2.SENDNO,t2.RECEIVERINFO,t2.CONTENT,t2.CONTENTTYPE,t2.ACCESSORYINFO,' ||
't2.STATUSFLAG,t2.SERVICEID,t2.FEETYPE,t2.FEEADDR,t2.FEE,t2.CREATETIME,t2.RESERVE3,' ||
't2.RESERVE4,t1.SENDTIME,t1.MSGID,t1.TASKDID_GUID,t1.ERRORCODE FROM ' ||
'table(pkg_report.f_task_report(:v_t_tab_task))' ||
-- ' IF_MS_MEDIA_TASK ' ||
' t1 left join T_MS_MEDIA_TASK_TMP t2 on t1.taskedid = t2.mediataskid and t1.receiverinfo = t2.receiverinfo WHERE t2.monthday = ' ||
i_t2_monthday || ' and t2.vasid in (' || i_t2_vasid || ')';

open o_data for v_result_sql
using v_t_tab_task;
exit when cur_report%notfound;
end loop;
close cur_report;
end p_task_report;
end pkg_report;

POSTS