oracle拼接字符串处理，wm_contact函数使用

CREATE OR REPLACE VIEW NEWSOUTER_V AS
select t.ID,
       t.MAIN_TITLE,
       t.SUBTITLE,
       t.LEADSTITLE,
       t.ORGANIZE,
       t.IS_OUTER_NEWS,
       t.OUTER_TYPE,
       t.OUTR_SUBJECT,
       t.IS_INNERNEWS,
       t.INNER_TYPE,
       t.INNER_SUBJECT,
       t.SUMMARY,
       t.CONTENT,
       t.IS_FOUCS,
       t.KEYWORDS,
       t.CREATED_BY,
       t.CREATED_DATE,
       t.SENDED_BY,
       t.MODIFYED_DATE,
       t.STATE,
       t.FLOW_ID,
       t.COVER_IMAGE,
       t.EXPIRED_DATE,
       t.COMPANY_NAME,
       t.COMPANY_CODE,
       t.NEWS_VIEWS,
       t.NEWS_PUBLISHED_FLAG,
       t.NEWS_RESOURCE_URL,
       t.NEWS_FOCUS_SERIESNO,
       t.ORGANIZE_CODE,
       t.guid,
       (SELECT WM_CONCAT(NEWS_TYPE_NAME)
          FROM NEWS_TYPE D
         WHERE TO_CHAR(D.NEWS_TYPE_ID) IN
               (SELECT REGEXP_SUBSTR(T.OUTER_TYPE, '[^,]+', 1, LEVEL, 'i')
                  FROM DUAL
                CONNECT BY LEVEL <=
                           LENGTH(T.OUTER_TYPE) -
                           LENGTH(REGEXP_REPLACE(T.OUTER_TYPE, ',', '')) + 1))TYPE_NAME
  from NEWSOUTER t where t.outer_type != '10051' and t.state is null or t.state = 1 order by t.created_date desc;

上面sql实现的效果是：本来查询出来的outerType是这样的"1,2,3"，可能是多个，也可能是一个，通过上面的代码处理后，需要得到 "公司要闻,媒体动态,图片新闻"

1,2,3 -> 公司要闻,媒体动态,图片新闻

在分类表中，有typename和typeid

后面又需要实现这样一个需求，处理方式和上面的一样。 接口表中的outertype数据是这样的"aa,bb,cc" 需要匹配分类表中的typecode，得到typeid，然后还是以逗号分隔，返回

传入outerTypeCode，返回typeId  
      例如:调用portal_interface_package.getOuterType('BasinDevelopment,Specialreports')
           返回：10042,10055

函数代码如下：

function getOuterType(outerType varchar2) return varchar2 
      is
      outerTypeIds varchar2(255);
      begin 
      SELECT WM_CONCAT(NEWS_TYPE_ID)
      into outerTypeIds
          FROM news_type D
         WHERE TO_CHAR(D.News_Type_Code) IN
               (SELECT REGEXP_SUBSTR(outerType, '[^,]+', 1, LEVEL, 'i')
                  FROM DUAL
                CONNECT BY LEVEL <=
                           LENGTH(outerType) -
                           LENGTH(REGEXP_REPLACE(outerType, ',', '')) + 1);
             return outerTypeIds;
        end;

调用该函数就能查询出对应的id，多个以逗号分隔

select portal_interface_package.getOuterType('BasinDevelopment,Specialreports') from dual;