问题如下:
实际测试条件:
- --业务类别对应表
- 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;
测试数据如下:
- SELECT G.PRODUCT_ID,strcat(G.PC_ID) FROM G_PRODUCT_REL G GROUP BY G.PRODUCT_ID; --每个业务属于哪些类别
例2:
- SELECT PC_ID,STRCAT(G.PRODUCT_ID) FROM G_PRODUCT_REL G GROUP BY G.PC_ID; --每个类别下都有那些业务
以下三个sql创建此函数:
- ❶CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) 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,
- member function ODCIAggregateTerminate(self IN strcat_type,returnValue OUT VARCHAR2, flags IN number) return number,
- member function ODCIAggregateMerge(self IN OUT strcat_type,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
- 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
- begin
- returnValue := self.currentstr;
- return ODCIConst.Success;
- end;
- member function ODCIAggregateMerge(self IN OUT strcat_type, 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;
以上的函数是我们的DBA写的,不过现在10g以上的数据库已经新增 wm_concat(column)函数实现字段合并了,哈哈
转载于:https://blog.51cto.com/programmer/1130086