CREATE OR REPLACE TYPE WM_CONCAT_IMPL2 AS OBJECT
-- AUTHID CURRENT_USER AS OBJECT
(
CURR_STR nclob, -- 原来为varchar2(32767)
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL2,
P1 IN nclob) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL2,
RETURNVALUE OUT nclob,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL2,
SCTX2 IN WM_CONCAT_IMPL2) RETURN NUMBER
)
;
--定义类型body:
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL2
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL2)
RETURN NUMBER
IS
BEGIN
SCTX := WM_CONCAT_IMPL2(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL2,
P1 IN nclob)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL2,
RETURNVALUE OUT nclob,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL2,
SCTX2 IN WM_CONCAT_IMPL2)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
--自定义行变列函数:
CREATE OR REPLACE FUNCTION wm_concat2(P1 nclob)
RETURN nclob AGGREGATE USING WM_CONCAT_IMPL2 ;
/
create or replace procedure a_test(mycol in varchar2,
mytab in varchar2,
mywhere in varchar2,
mysql out varchar2,
my_cursor2 out sys_refcursor) as
my_cursor sys_refcursor;
mysql99 clob;
begin
mysql := 'select (''select ''''''||replace(wm_concat2(to_char(' || mycol ||
')),'','','''''','''''')||'''''' from dual'') from ' || mytab||' where 1=1 '||mywhere;
OPEN my_cursor FOR mysql;
FETCH my_cursor into mysql99;
OPEN my_cursor2 FOR mysql99;
end;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/345618/viewspace-1060944/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
文件的压缩与解压缩zz--linux
下一篇：
Oracle 创建分区表
请登录后发表评论
登录
全部评论
