navicat 连接oracle看不到表_使用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 isbeginreturn 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) iscursor cur_report(i_t1_monthday varchar2) isselect t.monthday,t.taskedid,t.sendtime,t.batchid,t.msgid,t.receiverinfo,t.taskdid_guid,t.errorcodefrom if_ms_media_task twhere 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);beginopen cur_report(i_t1_monthday);loopfetch cur_report bulk collectinto 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 loopv_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_sqlusing v_t_tab_task;exit when cur_report%notfound;end loop;close cur_report;end p_task_report;end pkg_report;