问题如下：
www.2cto.com
实际测试条件：
www.2cto.com
实际测试条件：
--业务类别对应表
create table G_PRODUCT_REL
(
PRODUCT_ID NUMBER not null, --业务id
PC_ID NUMBER not null --业务类别id
)
SELECT 'INSERT INTO G_PRODUCT_REL(PRODUCT_ID,PC_ID)
VALUES('||G.PRODUCT_ID||','||G.PC_ID||')' FROM G_PRODUCT_REL G;
VALUES('||G.PRODUCT_ID||','||G.PC_ID||')' FROM G_PRODUCT_REL G;
测试数据如下： www.2cto.com
例1：
SELECT G.PRODUCT_ID,strcat(G.PC_ID) FROM G_PRODUCT
_REL G GROUP BY G.PRODUCT_ID; --每个业务属于哪些类别 www.2cto.com
_REL G GROUP BY G.PRODUCT_ID; --每个业务属于哪些类别 www.2cto.com
例2：
SELECT PC_ID,STRCAT(G.PRODUCT_ID) FROM G_PRODUCT_
REL G GROUP BY G.PC_ID; --每个类别下都有那些业务 www.2cto.com
REL G GROUP BY G.PC_ID; --每个类别下都有那些业务 www.2cto.com
以下三个sql创建此函数：
CREATE OR REPLACE FUNCTION strcat (input VARCHAR2)
RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type;
RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type;
create or replace type strcat_type as object
(
currentstr varchar2(4000),
currentseprator varchar2(8),
static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number,
member function ODCIAggregateIterate(self IN OUT
strcat_type,value IN VARCHAR2) return number,
strcat_type,value IN VARCHAR2) return number,
member function ODCIAggregateTerminate(self IN strcat_
type,returnValue OUT VARCHAR2, flags IN number) return number,
type,returnValue OUT VARCHAR2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT strcat_type,
ctx2 IN strcat_type) return number
ctx2 IN strcat_type) return number
)
create or replace type body strcat_type is
static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is
begin
sctx := strcat_type('',',');
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT strcat_type,
value IN VARCHAR2) return number is
value IN VARCHAR2) return number is
begin
if self.currentstr is null then
self.currentstr := value;
else
self.currentstr := self.currentstr ||currentseprator || value;
end if;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN strcat_type,
returnValue OUT VARCHAR2, flags IN number) return number is
returnValue OUT VARCHAR2, flags IN number) return number is
begin
returnValue := self.currentstr;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT strcat_type,
ctx2 IN strcat_type) return number is
ctx2 IN strcat_type) return number is
begin
if ctx2.currentstr is null then
self.currentstr := self.currentstr;
elsif self.currentstr is null then
self.currentstr := ctx2.currentstr;
else
self.currentstr := self.currentstr || currentseprator || ctx2.currentstr;
end if;
return ODCIConst.Success;
end;
end;