每次执行strcat时总是提示错误:程序包或函数STRCAT处于无效状态
到底是什么情况,函数写得也没什么问题呀,请各位帮帮忙!
自定义的函数如下:
create or replace function strcat(input varchar2)
return varchar2
parallel_enable aggregate using strcat_type;
TYPE:
CREATE or replace type strcat_type as object (
cat_string varchar2(4000),
static function odciaggregateinitialize(cs_ctx in out strcat_type) return number,
member function odciaggregateiterate(self in out strcat_type,value in varchar2) return number,
member function odciaggregatemerge(self in out strcat_type,ctx2 in out strcat_type) return number,
member function odciaggregateterminate(self in out strcat_type,returnvalue out varchar2,flags in number) return number
)
到底是什么情况,函数写得也没什么问题呀,请各位帮帮忙!
自定义的函数如下:
create or replace function strcat(input varchar2)
return varchar2
parallel_enable aggregate using strcat_type;
TYPE:
CREATE or replace type strcat_type as object (
cat_string varchar2(4000),
static function odciaggregateinitialize(cs_ctx in out strcat_type) return number,
member function odciaggregateiterate(self in out strcat_type,value in varchar2) return number,
member function odciaggregatemerge(self in out strcat_type,ctx2 in out strcat_type) return number,
member function odciaggregateterminate(self in out strcat_type,returnvalue out varchar2,flags in number) return number
)
1. 类型的声明
2. 类型的实现
3. 函数的定义你这里只实现了第1步和第3步,缺少了第2步,也就是类型的实现也就是type body部分。类型声明CREATE OR REPLACE TYPE "T_LINK" AS OBJECT ( STR VARCHAR2(30000), STATIC FUNCTION ODCIAGGREGATEINITIALIZE (ACTX IN OUT T_LINK) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER)
类型实现CREATE OR REPLACE TYPE BODY T_LINK IS STATIC FUNCTION ODCIAGGREGATEINITIALIZE (ACTX IN OUT T_LINK) RETURN NUMBER IS BEGIN ACTX := T_LINK(NULL); RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS BEGIN
IF SELF.STR IS NULL THEN
SELF.STR := VALUE;
ELSE
SELF.STR := SELF.STR || ',' || VALUE;
END IF;
RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN RETURNVALUE := SELF.STR; RETURN ODCICONST.SUCCESS; END; MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS BEGIN NULL; RETURN ODCICONST.SUCCESS; END;END;
函数定义CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2 AGGREGATE USING T_LINK;
CREATE OR REPLACE TYPE string_sum_obj AS OBJECT (
--聚合函数的实质就是一个对象
sum_string VARCHAR2 (4000),
STATIC FUNCTION odciaggregateinitialize (v_self IN OUT string_sum_obj)
RETURN NUMBER,
--对象初始化
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT string_sum_obj,
VALUE IN VARCHAR2
)
RETURN NUMBER,
--聚合函数的迭代方法(这是最重要的方法)
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT string_sum_obj,
v_next IN string_sum_obj
)
RETURN NUMBER,
--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
MEMBER FUNCTION odciaggregateterminate (
SELF IN string_sum_obj,
return_value OUT VARCHAR2,
v_flags IN NUMBER
)
RETURN NUMBER
--终止聚集函数的处理,返回聚集函数处理的结果.
);
/CREATE OR REPLACE TYPE BODY string_sum_obj
IS
STATIC FUNCTION odciaggregateinitialize (v_self IN OUT string_sum_obj)
RETURN NUMBER
IS
BEGIN
v_self := string_sum_obj (NULL);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate (
SELF IN OUT string_sum_obj,
VALUE IN VARCHAR2
)
RETURN NUMBER
IS
BEGIN
/* 连接,解决逗号分隔第一个字母是逗号的问题 */
IF NOT SELF.sum_string IS NULL
THEN
SELF.sum_string := SELF.sum_string || ',' || VALUE;
ELSE
SELF.sum_string := VALUE;
END IF; RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatemerge (
SELF IN OUT string_sum_obj,
v_next IN string_sum_obj
)
RETURN NUMBER
IS
BEGIN
/* 连接 */
SELF.sum_string := SELF.sum_string || v_next.sum_string;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate (
SELF IN string_sum_obj,
return_value OUT VARCHAR2,
v_flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
return_value := SELF.sum_string;
RETURN odciconst.success;
END;
END;
/CREATE OR REPLACE FUNCTION fun_concat (VALUE VARCHAR2)
RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
USING string_sum_obj;
/
处于无效状态
1. 三部分代码需要按顺序执行,类型声明-->类型实现-->函数定义
2. 楼主你去看下你的函数F_LINK是否处于无效状态,如果是,执行下创建函数的SQL,看下是什么原因导致函数创建失败。
with t1 as(select 1 id,test1 value from dual union all select 1 id,test2 value from dual ) select id,fun_concat(value) value from t1