哪位大神可以帮我看看这个oracle语句怎么转化为sql??谢谢!!

Sncsdn 2015-09-09 11:39:27
declare vsdw varchar2(36) := 0;
vfrow sys_refcursor;
sqls varchar2(32767);
begin
open vfrow for 'select lsbzdw_dwbh from lsbzdw where LSBZDW_MX='1'';
loop
fetch vfrow into vsdw;
exit when vfrow%NOTFOUND;

sqls := sqls || ' select '''||vsdw||'02'' as id,'||vsdw||' as centitycode,''Detail_02'' as CASSTYPECODE , lsbzdw_dwbh as bh,lsbzdw_dwmc as mc,lsbzdw_sjgs as cparentcode from lsbzdw where LSBZDW_MX='1'
union all
select '''||vsdw||'03'' as id,'||vsdw||' as centitycode,''Detail_03'' as CASSTYPECODE , lswbzd_bzbh as bh,lswbzd_bzmc as mc from lswbzd
union all
select '''||vsdw||'05'' as id,'||vsdw||' as centitycode,''Detail_05'' as CASSTYPECODE , lsbmzd_bmbh as bh,lsbmzd_bmmc as mc from lsbmzd where lsbmzd_dwbh='''||vsdw||'''
union all
select '''||vsdw||'08'' as id,'||vsdw||' as centitycode,''Detail_08'' as CASSTYPECODE , lshsxm_xmnm as bh,lshsxm_xmmc as mc,SUBSTRING(lshsxm_xmnm,1,2) as cparentcode from lshsxm2015 where lshsxm_dwbh='''||vsdw||'''LSHSXM_LBID='0ed7914c-ed75-4e2e-bf75-d8d6d715924d'
union all
select '''||vsdw||'09'' as id,'||vsdw||' as centitycode,''Detail_09'' as CASSTYPECODE , LSWLDW_wldwbh as bh,LSWLDW_dwmc as mc from lswldw where LSWLDW_DWLB='01' and LSWLDW_WLDWBH not in (select LSBZDW_DWBH from LSBZDW)
union all
select '''||vsdw||'10'' as id,'||vsdw||' as centitycode,''Detail_10'' as CASSTYPECODE , LSWLDW_wldwbh as bh,LSWLDW_dwmc as mc from lswldw where hgLSWLDW_DWLB='02'
union all
select '''||vsdw||'11'' as id,'||vsdw||' as centitycode,''Detail_11'' as CASSTYPECODE , LSWLDW_wldwbh as bh,LSWLDW_dwmc as mc from lswldw where LSWLDW_DWLB='02'
union all
select '''||vsdw||'13'' as id,'||vsdw||' as centitycode,''Detail_13'' as CASSTYPECODE , lshsxm_xmnm as bh,lshsxm_xmmc as mc ,
Case when length(lshsxm_xmnm) == 4 then lshsxm_xmnm
when length(lshsxm_xmnm) == 6 then lshsxm_xmnm
when length(lshsxm_xmnm) > 7 then substring(lshsxm_xmnm,1,length(lshsxm_xmnm)-2) end as cparentcode from lshsxm where LSHSXM_LBID='997fb97f-f0bc-42ce-9713-3abaef5192c4' and lshsxm_dwbh='''||vsdw||'''
union all
';
end loop;
execute immediate('create or replace view VW_T_BASIC_ASSCODE
as '||sqls || 'select '''' as id,'''' as centitycode,'''' as CASSTYPECODE , '''' as bh,'''' as mc ,'''' as cparentcode from dual');
end;
...全文
341 1 打赏 收藏 转发到动态 举报
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
lisong770818 2015-10-28
  • 打赏
  • 举报
回复
为了建个视图,费了这么大的劲啊。

3,490

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧