++原Oracle函数 CREATE OR REPLACE TYPE UI_FI.TYPE_LIST_AGG AS OBJECT
( TOTAL VARCHAR2(4000), STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT TYPE_LIST_AGG) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT TYPE_LIST_AGG, VALUE IN VARCHAR2) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN TYPE_LIST_AGG, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT TYPE_LIST_AGG, CTX2 IN TYPE_LIST_AGG) RETURN NUMBER ); / CREATE OR REPLACE TYPE BODY UI_FI.TYPE_LIST_AGG IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT TYPE_LIST_AGG) RETURN NUMBER IS BEGIN SCTX := TYPE_LIST_AGG(NULL); RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT TYPE_LIST_AGG, VALUE IN VARCHAR2) RETURN NUMBER IS BEGIN SELF.TOTAL := SELF.TOTAL || VALUE; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN TYPE_LIST_AGG, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURNVALUE := SUBSTR(SELF.TOTAL, 2); RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT TYPE_LIST_AGG, CTX2 IN TYPE_LIST_AGG) RETURN NUMBER IS BEGIN SELF.TOTAL := SELF.TOTAL || CTX2.TOTAL; RETURN ODCICONST.SUCCESS; END; END; /
CREATE OR REPLACE FUNCTION UI_FI.F_CHANGE_COL_TO_ROW(INPUT VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING UI_FI.TYPE_LIST_AGG; /
++ MogDB 改写的函数
CREATE OR REPLACE FUNCTION ui_fi.F_CHANGE_COL_TO_ROW_state_func (results VARCHAR2, val VARCHAR2) RETURNS VARCHAR2 LANGUAGE sql COST 50 IMMUTABLE AS $$ select results || val; $$;
CREATE OR REPLACE FUNCTION ui_fi.F_CHANGE_COL_TO_ROW_final_func (results VARCHAR2) RETURNS VARCHAR2 LANGUAGE sql COST 111 IMMUTABLE AS $$ select substr(results, 2)::VARCHAR2; $$;
create type ty_test_pipeline as object(c1 varchar2(20),c2 number); / create type tyt_test_pipeline as table of ty_test_pipeline; /
create or replace function f_test_pipeline(i number) return tyt_test_pipeline --table of 类型 PIPELINED is PRAGMA AUTONOMOUS_TRANSACTION; --自治事务 TYPE CUR1 IS REF CURSOR; MYCURSOR CUR1; SQL_1 VARCHAR2(4000); SQL_2 VARCHAR2(4000); OUT_REC ty_test_pipeline := ty_test_pipeline(NULL,NULL);--构造空对象 begin EXECUTE IMMEDIATE 'TRUNCATE TABLE t_test_pipeline'; --有truncate table 注意ddl 自动commit的行为 insert into t_test_pipeline values ('a',1); insert into t_test_pipeline values ('b',2); insert into t_test_pipeline values ('c',2);--模拟两行以上数据 insert into t_test_pipeline values ('d',2); commit; --有commit SQL_1:='SELECT * FROM t_test_pipeline WHERE C2=1'; SQL_2:='SELECT * FROM t_test_pipeline WHERE C2=2';
if i =1 then --判断逻辑，打开不同的游标 OPEN MYCURSOR FOR SQL_1; ELSE OPEN MYCURSOR FOR SQL_2; end if;
LOOP FETCH MYCURSOR INTO OUT_REC.C1, OUT_REC.C2; EXIT WHEN MYCURSOR%NOTFOUND; PIPE ROW(OUT_REC); END LOOP; CLOSE MYCURSOR; RETURN; end; /
create type ty_test_pipeline as object(c1 varchar2(20),c2 number); / create type tyt_test_pipeline as table of ty_test_pipeline; / create or replace function f_test_pipeline(i number) return tyt_test_pipeline --table of 类型 is PRAGMA AUTONOMOUS_TRANSACTION; --自治事务 TYPE CUR1 IS REF CURSOR; MYCURSOR CUR1; SQL_1 VARCHAR2(4000); SQL_2 VARCHAR2(4000); OUT_TABLE tyt_test_pipeline; begin EXECUTE IMMEDIATE 'TRUNCATE TABLE t_test_pipeline'; --有truncate table 注意ddl 自动commit的行为 insert into t_test_pipeline values ('a',1); insert into t_test_pipeline values ('b',1); insert into t_test_pipeline values ('c',2);--模拟两行以上数据 insert into t_test_pipeline values ('d',2); commit; --有commit SQL_1:='SELECT * FROM t_test_pipeline WHERE C2=1'; SQL_2:='SELECT * FROM t_test_pipeline WHERE C2=2';
if i =1 then --判断逻辑，打开不同的游标 OPEN MYCURSOR FOR SQL_1; ELSE OPEN MYCURSOR FOR SQL_2; end if;
fetch MYCURSOR bulk collect into OUT_TABLE;--将循环fetch改成了一次性fetch CLOSE MYCURSOR; return OUT_TABLE; end; / --调用 SELECT * FROM TABLE(f_test_pipeline(1));
