我在网上下了个wmsys.wm_concat的定义执行成功了,但是创建的视图依然报错说这个函数是无效的标识符,函数及视图代码下:
--一:创建type头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
--终止聚集函数的处理,返回聚集函数处理的结果.
);
--创建type具体
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 := self.sum_string || value;
end if;
return ODCICONST.Success;
/* 最大值 */
if self.sum_string<value then
self.sum_string:=value;
end if;
/* 最小值 */
if self.sum_string>value then
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;
/* 最大值 */
if self.sum_string<v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if; /* 最小值 */
if self.sum_string>v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
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 wm_concat(value Varchar2) return Varchar2
parallel_enable aggregate using string_sum_obj;--二:先创建这个类型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;我创建的视图如下:
CREATE OR REPLACE VIEW ZC_BI_IMPORT_JB AS
SELECT "ZC_BI_NO","CO_CODE","PROJECT_CODE_ZHU","PROJECT_CODE","PROJECT_NAME","ZC_CATALOGUE_CODE","ZC_CONSOLE","ZC_MODEL","ORIGIN_CODE",
"ZC_CONTROL_SUM","ZC_CONTROL_NUM","ZC_BI_SUM","ZC_BI_USED_SUM","ND","STOCK_NO"
FROM (SELECT PROJECT_CODE ZC_BI_NO,
CO_CODE, --单位代码
project_code_zhu,--主项目
PROJECT_CODE, --明细预算项目编号
wmsys.wm_concat(DISTINCT(PROJECT_NAME)) PROJECT_NAME, --明细预算项目名称
D_ATTR1 ZC_CATALOGUE_CODE, --采购目录编号
'1' ZC_CONSOLE, --采购平台
/*STOCK_TYPE ZC_MODEL, --采购模式*/
'01' ZC_MODEL, --采购模式
ORIGIN_CODE, --资金来源
max(stock_price*10000) ZC_CONTROL_SUM, --单价
SUM(STOCK_NUMBER) ZC_CONTROL_NUM, --数量
SUM(AM_MONEY) ZC_BI_SUM, --总价
sum(ZC_BI_USED_SUM) ZC_BI_USED_SUM,--明细项目已用金额
ND, --年度
'' stock_no
FROM (SELECT A.CO_CODE,
a.project_code project_code_zhu,--主项目代码
a.project_code||'-'||A.D_ATTR1 PROJECT_CODE,--明细项目代码
D.PD_PROJECT_NAME PROJECT_NAME,
D_ATTR1,
A.BI_MONEY AM_MONEY,
a.am_money ZC_BI_USED_SUM,
C.STOCK_PRICE,
C.STOCK_TYPE,
A.ORIGIN_CODE,
C.STOCK_NUMBER,
A.ND
FROM BI_BALANCE A,
(select pd_project_code,max(pd_project_name) pd_project_name from GK_XMZC_MX_V
/*WHERE nd='2011' and pd_version_id ='N2011'*/ GROUP BY PD_PROJECT_CODE) D,
(SELECT B.TARGET_BALANCE_ID,
STOCK_TYPE,
MAX(NVL(B.STOCK_PRICE, 0)) STOCK_PRICE,
SUM(NVL(B.STOCK_NUMBER, 0)) STOCK_NUMBER,
ND
FROM BI_TRACK B
WHERE B.IS_STOCK = '1'
and b.stock_type='0'
AND B.MANAGE_CODE = '02'
GROUP BY B.TARGET_BALANCE_ID, STOCK_TYPE,
ND) C,
zc_b_catalogue zc
WHERE C.TARGET_BALANCE_ID = A.BI_BALANCE_ID
AND A.ND = C.ND
AND A.PROJECT_DETAIL_CODE NOT IN ('2011-1242011-001057','2011-1242011-001052')--特殊处理维护单号Z158
AND A.PROJECT_DETAIL_CODE = D.PD_PROJECT_CODE(+)
and a.d_attr1=zc.zc_catalogue_code and a.nd=zc.zc_year
and zc.zc_default_console<>'1'
)
GROUP BY CO_CODE,
project_code_zhu,
PROJECT_CODE,
D_ATTR1,
/* STOCK_TYPE,*/
ORIGIN_CODE,
ND
union all
SELECT PROJECT_CODE ZC_BI_NO,
CO_CODE, --单位代码
project_code_zhu,--主项目
PROJECT_CODE, --明细预算项目编号
wmsys.wm_concat(DISTINCT(PROJECT_NAME)) PROJECT_NAME, --明细预算项目名称
D_ATTR1 ZC_CATALOGUE_CODE, --采购目录编号
'1' ZC_CONSOLE, --采购平台
/*STOCK_TYPE ZC_MODEL, --采购模式*/
'01' ZC_MODEL, --采购模式
ORIGIN_CODE, --资金来源
max(stock_price*10000) ZC_CONTROL_SUM, --单价
SUM(STOCK_NUMBER) ZC_CONTROL_NUM, --数量
SUM(AM_MONEY) ZC_BI_SUM, --总价
sum(ZC_BI_USED_SUM) ZC_BI_USED_SUM,--明细项目已用金额
ND, --年度
stock_no
FROM (SELECT A.CO_CODE,
a.project_code project_code_zhu,--主项目代码
a.project_detail_code PROJECT_CODE,--明细项目代码
D.PD_PROJECT_NAME PROJECT_NAME,
D_ATTR1,
A.BI_MONEY AM_MONEY,
a.am_money ZC_BI_USED_SUM,
C.STOCK_PRICE,
C.STOCK_TYPE,
A.ORIGIN_CODE,
C.STOCK_NUMBER,
A.ND,
c.stock_no
FROM BI_BALANCE A,
(select pd_project_code,max(pd_project_name) pd_project_name from GK_XMZC_MX_V
/*WHERE nd='2011' and pd_version_id ='N2011'*/ GROUP BY PD_PROJECT_CODE) D,
(SELECT B.TARGET_BALANCE_ID,b.stock_no stock_no,
STOCK_TYPE,
MAX(NVL(B.STOCK_PRICE, 0)) STOCK_PRICE,
SUM(NVL(B.STOCK_NUMBER, 0)) STOCK_NUMBER,
ND
FROM BI_TRACK B
WHERE B.IS_STOCK = '1'
and b.stock_type='0'
AND B.MANAGE_CODE = '02'
GROUP BY B.TARGET_BALANCE_ID,
b.stock_no,
STOCK_TYPE,
ND) C,
zc_b_catalogue zc
WHERE C.TARGET_BALANCE_ID = A.BI_BALANCE_ID
AND A.ND = C.ND
AND A.PROJECT_DETAIL_CODE NOT IN ('2011-1242011-001057','2011-1242011-001052')--特殊处理维护单号Z158
AND A.PROJECT_DETAIL_CODE = D.PD_PROJECT_CODE(+)
and a.d_attr1=zc.zc_catalogue_code and a.nd=zc.zc_year
and zc.zc_default_console='1'
)
GROUP BY CO_CODE,
project_code_zhu,
PROJECT_CODE,
D_ATTR1,
/* STOCK_TYPE,*/
ORIGIN_CODE,
ND,
stock_no
)
WHERE ZC_BI_SUM > 0
ORDER BY CO_CODE, PROJECT_CODE, ZC_BI_SUM--select * from zc_p_bi_detail
--一:创建type头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
--终止聚集函数的处理,返回聚集函数处理的结果.
);
--创建type具体
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 := self.sum_string || value;
end if;
return ODCICONST.Success;
/* 最大值 */
if self.sum_string<value then
self.sum_string:=value;
end if;
/* 最小值 */
if self.sum_string>value then
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;
/* 最大值 */
if self.sum_string<v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if; /* 最小值 */
if self.sum_string>v_next.sum_string then
self.sum_string:=v_next.sum_string;
end if;
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 wm_concat(value Varchar2) return Varchar2
parallel_enable aggregate using string_sum_obj;--二:先创建这个类型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;我创建的视图如下:
CREATE OR REPLACE VIEW ZC_BI_IMPORT_JB AS
SELECT "ZC_BI_NO","CO_CODE","PROJECT_CODE_ZHU","PROJECT_CODE","PROJECT_NAME","ZC_CATALOGUE_CODE","ZC_CONSOLE","ZC_MODEL","ORIGIN_CODE",
"ZC_CONTROL_SUM","ZC_CONTROL_NUM","ZC_BI_SUM","ZC_BI_USED_SUM","ND","STOCK_NO"
FROM (SELECT PROJECT_CODE ZC_BI_NO,
CO_CODE, --单位代码
project_code_zhu,--主项目
PROJECT_CODE, --明细预算项目编号
wmsys.wm_concat(DISTINCT(PROJECT_NAME)) PROJECT_NAME, --明细预算项目名称
D_ATTR1 ZC_CATALOGUE_CODE, --采购目录编号
'1' ZC_CONSOLE, --采购平台
/*STOCK_TYPE ZC_MODEL, --采购模式*/
'01' ZC_MODEL, --采购模式
ORIGIN_CODE, --资金来源
max(stock_price*10000) ZC_CONTROL_SUM, --单价
SUM(STOCK_NUMBER) ZC_CONTROL_NUM, --数量
SUM(AM_MONEY) ZC_BI_SUM, --总价
sum(ZC_BI_USED_SUM) ZC_BI_USED_SUM,--明细项目已用金额
ND, --年度
'' stock_no
FROM (SELECT A.CO_CODE,
a.project_code project_code_zhu,--主项目代码
a.project_code||'-'||A.D_ATTR1 PROJECT_CODE,--明细项目代码
D.PD_PROJECT_NAME PROJECT_NAME,
D_ATTR1,
A.BI_MONEY AM_MONEY,
a.am_money ZC_BI_USED_SUM,
C.STOCK_PRICE,
C.STOCK_TYPE,
A.ORIGIN_CODE,
C.STOCK_NUMBER,
A.ND
FROM BI_BALANCE A,
(select pd_project_code,max(pd_project_name) pd_project_name from GK_XMZC_MX_V
/*WHERE nd='2011' and pd_version_id ='N2011'*/ GROUP BY PD_PROJECT_CODE) D,
(SELECT B.TARGET_BALANCE_ID,
STOCK_TYPE,
MAX(NVL(B.STOCK_PRICE, 0)) STOCK_PRICE,
SUM(NVL(B.STOCK_NUMBER, 0)) STOCK_NUMBER,
ND
FROM BI_TRACK B
WHERE B.IS_STOCK = '1'
and b.stock_type='0'
AND B.MANAGE_CODE = '02'
GROUP BY B.TARGET_BALANCE_ID, STOCK_TYPE,
ND) C,
zc_b_catalogue zc
WHERE C.TARGET_BALANCE_ID = A.BI_BALANCE_ID
AND A.ND = C.ND
AND A.PROJECT_DETAIL_CODE NOT IN ('2011-1242011-001057','2011-1242011-001052')--特殊处理维护单号Z158
AND A.PROJECT_DETAIL_CODE = D.PD_PROJECT_CODE(+)
and a.d_attr1=zc.zc_catalogue_code and a.nd=zc.zc_year
and zc.zc_default_console<>'1'
)
GROUP BY CO_CODE,
project_code_zhu,
PROJECT_CODE,
D_ATTR1,
/* STOCK_TYPE,*/
ORIGIN_CODE,
ND
union all
SELECT PROJECT_CODE ZC_BI_NO,
CO_CODE, --单位代码
project_code_zhu,--主项目
PROJECT_CODE, --明细预算项目编号
wmsys.wm_concat(DISTINCT(PROJECT_NAME)) PROJECT_NAME, --明细预算项目名称
D_ATTR1 ZC_CATALOGUE_CODE, --采购目录编号
'1' ZC_CONSOLE, --采购平台
/*STOCK_TYPE ZC_MODEL, --采购模式*/
'01' ZC_MODEL, --采购模式
ORIGIN_CODE, --资金来源
max(stock_price*10000) ZC_CONTROL_SUM, --单价
SUM(STOCK_NUMBER) ZC_CONTROL_NUM, --数量
SUM(AM_MONEY) ZC_BI_SUM, --总价
sum(ZC_BI_USED_SUM) ZC_BI_USED_SUM,--明细项目已用金额
ND, --年度
stock_no
FROM (SELECT A.CO_CODE,
a.project_code project_code_zhu,--主项目代码
a.project_detail_code PROJECT_CODE,--明细项目代码
D.PD_PROJECT_NAME PROJECT_NAME,
D_ATTR1,
A.BI_MONEY AM_MONEY,
a.am_money ZC_BI_USED_SUM,
C.STOCK_PRICE,
C.STOCK_TYPE,
A.ORIGIN_CODE,
C.STOCK_NUMBER,
A.ND,
c.stock_no
FROM BI_BALANCE A,
(select pd_project_code,max(pd_project_name) pd_project_name from GK_XMZC_MX_V
/*WHERE nd='2011' and pd_version_id ='N2011'*/ GROUP BY PD_PROJECT_CODE) D,
(SELECT B.TARGET_BALANCE_ID,b.stock_no stock_no,
STOCK_TYPE,
MAX(NVL(B.STOCK_PRICE, 0)) STOCK_PRICE,
SUM(NVL(B.STOCK_NUMBER, 0)) STOCK_NUMBER,
ND
FROM BI_TRACK B
WHERE B.IS_STOCK = '1'
and b.stock_type='0'
AND B.MANAGE_CODE = '02'
GROUP BY B.TARGET_BALANCE_ID,
b.stock_no,
STOCK_TYPE,
ND) C,
zc_b_catalogue zc
WHERE C.TARGET_BALANCE_ID = A.BI_BALANCE_ID
AND A.ND = C.ND
AND A.PROJECT_DETAIL_CODE NOT IN ('2011-1242011-001057','2011-1242011-001052')--特殊处理维护单号Z158
AND A.PROJECT_DETAIL_CODE = D.PD_PROJECT_CODE(+)
and a.d_attr1=zc.zc_catalogue_code and a.nd=zc.zc_year
and zc.zc_default_console='1'
)
GROUP BY CO_CODE,
project_code_zhu,
PROJECT_CODE,
D_ATTR1,
/* STOCK_TYPE,*/
ORIGIN_CODE,
ND,
stock_no
)
WHERE ZC_BI_SUM > 0
ORDER BY CO_CODE, PROJECT_CODE, ZC_BI_SUM--select * from zc_p_bi_detail
http://topic.csdn.net/u/20100201/12/e926f235-0efa-4469-9188-ebe85d3afd92.html