ORACLE的合并函数


 9i下:

  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;

  /

  使用方法:

  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

  10g下:

  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


« 
» 
快速导航

Copyright © 2016 phpStudy | 豫ICP备2021030365号-3