时间戳

首页 > 解决方案 > 我怎样才能制作动态pl/sql？

oracle - 我怎样才能制作动态pl/sql？

问题描述

我想在参数值中使用类型变量来制作动态 pl/sql。

参数值 > type = {'name + place', 'resno', 'hpno', 'telno'};

例如，如果我得到参数值（'resno'，'hpno'）需要两个组合查询（2个联合全部），这意味着查询的数量取决于参数值。

select * from (
--repeatation
select * from
(select a.custnm
     , 'name + place' as vtype
     , a.custnm || '-' || c.pjtcd || '-' || c.dong || '-' || c.ho as con
     , count(a.custid) as nodup 
from   custtable a
     , thng c 
where a.custid = c.custid(+)
group by a.custnm, c.pjtcd, c.dong, c.ho
having count(a.custid) > 1) x
--/repeatation
union all
--repeatation
select * from
(select a.custnm
     , 'resno' as vtype
     , a.resno as condup
     , count(a.custid) as nodup 
from   custtable a
group by a.custnm, a.resno
having count(a.custid) > 1) x2
--repeatation
union all
--repeatation
select * from
(select a.custnm
     , 'hpno' as vtype
     , a.hpno as condup
     , count(a.custid) as nodup 
from   custtable a
group by a.custnm, a.hpno
having count(a.custid) > 1) x3
--repeatation
union all
--repeatation
select * from
(select a.custnm
     , 'telno' as vtype
     , a.telno as condup
     , count(a.custid) as nodup 
from   custtable a
group by a.custnm, a.telno
having count(a.custid) > 1) x4
--repeatation
) 
order by decode(vtype, 'name +`enter code here` place', 1 ,'resno', 2 ,'hpno', 3,  'telno', 4), nodup desc

请让我知道如何使用参数值制作 plsql

标签： oracleplsqldynamic-queries

解决方案

这是一个SELECT声明。现在的写法，是 SQL，而不是 PL/SQL，我的建议是保持这种方式。与其编写可怕的 PL/SQL（动态 SQL 通常不好），不如根据该语句创建一个视图？

create or replace view v_my_view as
select * from (
--repeatation
select * from
(select a.custnm
     , 'name + place' as vtype
<snip>

一旦你这样做了，就可以在任何你想要的地方使用它（包括 PL/SQL）。

推荐阅读