加強型WM_CONCAT2函式（不受32767限制的CLOB型別）

3492zhang發表於2013-09-29
函式型別
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;
/
[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/345618/viewspace-1060944/，如需轉載，請註明出處，否則將追究法律責任。

相關文章