Two types of Oracle merge functions

The following articles mainly introduce two types of Oracle merge functions, that is, Introduction to actual application code related to Oracle9i and Oracle10g, and description of the specific use scheme of Oracle9i, the following is an introduction to the specific content of the article. I hope you will have some gains.

9i of Oracle merge functions:

 
 

  
  
create 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 type body strcat_type is  
  
  
static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number  
  
  
is  
  
  
begin  
  
  
cs_ctx := strcat_type( null );  
  
  
return ODCIConst.Success;  
  
  
end;  
  
  
member function ODCIAggregateIterate(self IN OUT strcat_type,  
  
  
value IN varchar2 )  
  
  
return number  
  
  
is  
  
  
begin  
  
  
self.cat_string := self.cat_string || ','|| value;  
  
  
return ODCIConst.Success;  
  
  
end;  
  
  
member function ODCIAggregateTerminate(self IN Out strcat_type,  
  
  
returnValue OUT varchar2,  
  
  
flags IN number)  
  
  
return number  
  
  
is  
  
  
begin  
  
  
returnValue := ltrim(rtrim(self.cat_string,','),',');  
  
  
return ODCIConst.Success;  
  
  
end;  
  
  
member function ODCIAggregateMerge(self IN OUT strcat_type,  
  
  
ctx2 IN Out strcat_type)  
  
  
return number  
  
  
is  
  
  
begin  
  
  
self.cat_string := self.cat_string || ',' || ctx2.cat_string;  
  
  
return ODCIConst.Success;  
  
  
end;  
  
  
end;  
  
  
/  
  
  
CREATE or replace  
  
  
FUNCTION strcat(input varchar2 )  
  
  
RETURN varchar2  
  
  
PARALLEL_ENABLE AGGREGATE USING strcat_type;  
  
  
/  
 
 

Usage:

 
 

  
  
select t2.kdm_mdid_pk,t2.kdm_title,  
  
  
strcat(t3.subject_mc_content) message  
  
  
from t_knodoc_rel_subjects t1,  
  
  
T_KNO_DOC_METADATA t2,  
  
  
T_SUBJECT_MULTILINGUAL_CONTENT t3  
  
  
where t1.krs_kdm_mdid_fk=t2.kdm_mdid_pk  
  
  
and t1.krs_subid_fk=t3.subject_mc_id_pk  
  
  
group by t2.kdm_mdid_pk,t2.kdm_title  
 
 

10 Gb of Oracle merge functions:

 
 

  
  
select t2.kdm_mdid_pk,t2.kdm_title,WMSYS.WM_CONCAT(t3.subject_mc_content) message  
  
  
from t_knodoc_rel_subjects t1,  
  
  
T_KNO_DOC_METADATA t2,  
  
  
T_SUBJECT_MULTILINGUAL_CONTENT t3  
  
  
where t1.krs_kdm_mdid_fk=t2.kdm_mdid_pk  
  
  
and t1.krs_subid_fk=t3.subject_mc_id_pk  
  
  
group by t2.kdm_mdid_pk,t2.kdm_title   
 
 

The above content is an introduction to Oracle merge functions, and I hope you will get some benefits.