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;