17,082
社区成员
发帖
与我相关
我的任务
分享
--比如使用以下语句,返回的都是第一句SQL执行的结果,
--而不是根据日期参数返回。
SELECT * FROM TABLE(TAB_CDR(201604));
SELECT * FROM TABLE(TAB_CDR(201605));
......
SELECT * FROM TABLE(TAB_CDR(201612));
--1 创建集合类型
CREATE OR REPLACE TYPE type_event_o AS OBJECT (nbr VARCHAR2(32),
ms VARCHAR2(15),startdate DATE,send NUMBER(12),recv NUMBER(12),
duration NUMBER(12),CYCLE_id NUMBER(9));
CREATE OR REPLACE TYPE type_event_t AS TABLE OF type_event_o;
--2 创建函数
CREATE OR REPLACE FUNCTION TAB_CDR (cycle_id NUMBER)
RETURN type_event_t PIPELINED
AS
v_sql VARCHAR2(500);
cdr_row1 type_event_o;
BEGIN
v_sql := 'create or replace view v_tc_data as
select nbr,ms,startdate,send,recv,
duration,CYCLE_id from TAB_DATA_'|| CYCLE_ID ||
' WHERE CNT>0';
EXECUTE IMMEDIATE v_sql;
for cdr_rows IN (select * from v_tc_data )
LOOP
cdr_row1 := type_event_o(cdr_rows.nbr,cdr_rows.ms,cdr_rows.startdate,
cdr_rows.send,cdr_rows.recv,
cdr_rows.duration,cdr_rows.CYCLE_id);
PIPE ROW (cdr_row1);
END LOOP;
RETURN ;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('err');
END TAB_CDR;
--3 具体使用
SELECT * FROM TABLE(TAB_CDR(201606));