パイプ関数テスト1
2283 ワード
CREATE OR REPLACE PACKAGE test_92570101 IS
TYPE datetime_range_record IS RECORD(
datetime_begin DATE,
datetime_end DATE);
TYPE datetime_range_list IS TABLE OF datetime_range_record;
v_g_datetime_range_list datetime_range_list;
TYPE tbl IS TABLE OF VARCHAR2(20);
v_tbl t_varray := NEW t_varray();
TYPE type_trade_flow IS TABLE OF trade_flow%ROWTYPE;
FUNCTION pipe_target(p_source_data IN SYS_REFCURSOR) RETURN type_trade_flow
PIPELINED;
PROCEDURE gettbldata(x_ret_data OUT SYS_REFCURSOR);
----------------------------------------------------------------------------
FUNCTION tbl_list_to_table RETURN t_varray
PIPELINED;
PROCEDURE load_target(x_ret_data OUT SYS_REFCURSOR);
END test_92570101;
--================================================================================
CREATE OR REPLACE PACKAGE BODY test_92570101 IS
FUNCTION datetime_range_list_to_table RETURN datetime_range_list
PIPELINED IS
BEGIN
FOR i IN 1 .. v_g_datetime_range_list.count
LOOP
PIPE ROW(v_g_datetime_range_list(i));
END LOOP;
END;
FUNCTION tbl_list_to_table RETURN t_varray
PIPELINED IS
BEGIN
FOR i IN 1 .. v_tbl.count
LOOP
PIPE ROW(v_tbl(i));
END LOOP;
END;
PROCEDURE gettbldata(x_ret_data OUT SYS_REFCURSOR) IS
BEGIN
FOR i IN 1 .. 5
LOOP
--v_g_datetime_range_list.extend(1);
--v_g_datetime_range_list.(i) := 'Test'||i;
v_tbl.extend(1);
v_tbl(i) := 'Test' || i;
END LOOP;
OPEN x_ret_data FOR
--SELECT SYSDATE FROM dual;
--SELECT * FROM TABLE(v_tbl);
SELECT * FROM TABLE(tbl_list_to_table);
END;
FUNCTION pipe_target(p_source_data IN SYS_REFCURSOR) RETURN type_trade_flow
PIPELINED IS
r_target_data type_trade_flow;
r_source_data trade_flow%ROWTYPE;
BEGIN
LOOP
FETCH p_source_data
INTO r_source_data;
EXIT WHEN p_source_data%NOTFOUND;
PIPE ROW(r_source_data);
END LOOP;
CLOSE p_source_data;
RETURN;
END;
PROCEDURE load_target(x_ret_data OUT SYS_REFCURSOR) IS
BEGIN
OPEN x_ret_data FOR
SELECT * FROM TABLE(pipe_target(CURSOR (SELECT * FROM trade_flow)));
--COMMIT;
END;
END test_92570101;