PL/SQL在存储过程中嵌入DDL

[小孔大胖]

create or replace procedure sp_report_sitediffdistance(p_starttime number,
p_distance_h number,
p_distance_x number,
p_distance_r number,
p_distance_m number,
cur_out out sys_refcursor) is

v_sql varchar2(1000);
v_where varchar2(500);
begin

execute immediate 'truncate table JL_SITEDIFF_detail_now';

v_sql := 'insert into JL_SITEDIFF_detail_now select * ';
v_where := 'from JL_SITEDIFF_DETAIL where first_result=to_date(' || '''' ||
p_starttime || '''' || ',''yyyy-mm-dd'') and (1=2 ';
if p_distance_h > 0 then
v_where := v_where || 'or (sitetype=''H'' and distance<' ||
p_distance_h || ') ';
end if;
if p_distance_x > 0 then
v_where := v_where || 'or (sitetype=''X'' and distance<' ||
p_distance_x || ') ';
end if;
if p_distance_r > 0 then
v_where := v_where || 'or (sitetype=''R'' and distance<' ||
p_distance_h || ') ';
end if;
if p_distance_m > 0 then
v_where := v_where || 'or (sitetype=''M'' and distance<' ||
p_distance_m || ') ';
end if;
v_sql := v_sql || v_where || ')';

--执行汇总
execute immediate v_sql;
commit;
open cur_out for
select distinct order_id,
first_result,
company,
sum(case
when sitetype = 'H' then
1
else
0
end) h,
sum(case
when sitetype = 'R' then
1
else
0
end) r,
sum(case
when sitetype = 'X' then
1
else
0
end) x

,
sum(case
when sitetype = 'M' then
1
else
0
end) m,
count(*) as sum
from jl_sitediff_detail_now
group by first_result, order_id, company
union all
select 10,
first_result,
'吉林省',
sum(case
when sitetype = 'H' then
1
else
0
end) h,
sum(case
when sitetype = 'R' then
1
else
0
end) r,
sum(case
when sitetype = 'X' then
1
else
0
end) x,
sum(case
when sitetype = 'M' then
1
else
0
end) m,
count(*) as sum
from jl_sitediff_detail_now
group by first_result
order by order_id;
--执行去重
--select distinct
--case when sitecode1>=sitecode2 then sitecode2 else sitecode1 end sitecode1,
--case when sitecode1>=sitecode2 then sitecode1 else sitecode2 end sitecode2
-- from JL_SITEDIFF_DETAIL_NOW t
-- where exists(select 1 from JL_SITEDIFF_DETAIL_NOW where sitecode1 =t.sitecode2 and sitecode2=t.sitecode1)
--union all
--select * from JL_SITEDIFF_DETAIL_NOW t where not exists (select 1 from JL_SITEDIFF_DETAIL_NOW t where sitecode1=t.sitecode2 and sitecode2=t.sitecode1)

end sp_report_sitediffdistance;

注释部分是想要嵌入到存储过程中的。。
整个功能是这样未注释的代码指的是根据查询条件显示计数
注释部分的意思是，去除重复数据和交叉相等的数据，只留一个，
跪求各位大神。。
在线等。

[小孔大胖]

create or replace procedure sp_report_sitediffdistance(p_starttime  number,
                                                       p_distance_h number,
                                                       p_distance_x number,
                                                       p_distance_r number,
                                                       p_distance_m number,
                                                       cur_out      out sys_refcursor) is

  v_sql   varchar2(1000);
  v_where varchar2(500);
begin

  execute immediate 'truncate  table JL_SITEDIFF_detail_now';

  v_sql   := 'insert into JL_SITEDIFF_detail_now select * ';
  v_where := 'from JL_SITEDIFF_DETAIL where first_result=to_date(' || '''' ||
             p_starttime || '''' || ',''yyyy-mm-dd'') and (1=2 ';
  if p_distance_h > 0 then
    v_where := v_where || 'or (sitetype=''H'' and distance<' ||
               p_distance_h || ') ';
  end if;
  if p_distance_x > 0 then
    v_where := v_where || 'or (sitetype=''X'' and distance<' ||
               p_distance_x || ') ';
  end if;
  if p_distance_r > 0 then
    v_where := v_where || 'or (sitetype=''R'' and distance<' ||
               p_distance_h || ') ';
  end if;
  if p_distance_m > 0 then
    v_where := v_where || 'or (sitetype=''M'' and distance<' ||
               p_distance_m || ') ';
  end if;
  v_sql := v_sql || v_where || ')';

  --执行汇总
  execute immediate v_sql;
  commit;
  open cur_out for
    select distinct order_id,
           first_result,
           company,
           sum(case
                 when sitetype = 'H' then
                  1
                 else
                  0
               end) h,
           sum(case
                 when sitetype = 'R' then
                  1
                 else
                  0
               end) r,
           sum(case
                 when sitetype = 'X' then
                  1
                 else
                  0
               end) x

          ,
           sum(case
                 when sitetype = 'M' then
                  1
                 else
                  0
               end) m,
           count(*) as sum
      from jl_sitediff_detail_now
     group by first_result, order_id, company
    union all
    select 10,
           first_result,
           '吉林省',
           sum(case
                 when sitetype = 'H' then
                  1
                 else
                  0
               end) h,
           sum(case
                 when sitetype = 'R' then
                  1
                 else
                  0
               end) r,
           sum(case
                 when sitetype = 'X' then
                  1
                 else
                  0
               end) x,
           sum(case
                 when sitetype = 'M' then
                  1
                 else
                  0
               end) m,
           count(*) as sum
      from jl_sitediff_detail_now
     group by first_result
     order by order_id;
 --执行去重
 --select distinct
 --case when sitecode1>=sitecode2 then sitecode2 else  sitecode1 end sitecode1,
 --case when sitecode1>=sitecode2 then sitecode1 else  sitecode2 end sitecode2
 -- from JL_SITEDIFF_DETAIL_NOW t
 -- where exists(select 1 from JL_SITEDIFF_DETAIL_NOW where sitecode1 =t.sitecode2 and sitecode2=t.sitecode1)
 --union all
 --select * from JL_SITEDIFF_DETAIL_NOW t where not exists (select 1 from JL_SITEDIFF_DETAIL_NOW t where sitecode1=t.sitecode2 and sitecode2=t.sitecode1)

end sp_report_sitediffdistance;

格式化了一下

[碧水幽幽泉]

注释部分的意思是，去除重复数据和交叉相等的数据，只留一个

1.注释部分的union all语句明显有问题。
2.只保留一个什么？

[碧水幽幽泉]

提问的时候注意方式：复杂问题简单化，这也是聪明人的做法。
比如数据如下：
code1 code2
1 2
1 2
2 3

数据是否要求达到如下效果？
code1 code2
1 2
2 3

或者数据如下：
code1 code2
1 2
2 1
2 3

数据是否要求达到如下效果？
code1 code2
1 2
2 3

[小孔大胖]

或者数据如下： code1 code2 1 2 2 1 2 3 数据是否要求达到如下效果？ code1 code2 1 2 2 3 要这个效果

[碧水幽幽泉]

有这种数据吗？
code1 code2
1 2
1 2
2 3

[碧水幽幽泉]

交叉去重的一般方法

select distinct s1,s2
  from(select sitecode1 as s1,sitecode2 as s2
		 from jl_sitediff_detail_now
		union all
	   select sitecode2,sitecode1
		 from jl_sitediff_detail_now
		 union all
       )		 

[小孔大胖]

有这种数据。。 就要这种查询结果 注释里面的东西我运行过 行得通啊。

[小孔大胖]

能支持一下。。。怎么嵌入到上面的存储过程里面吗。

[碧水幽幽泉]

刚才的SQL多了个union all

select distinct s1,s2
  from(select sitecode1 as s1,sitecode2 as s2
		 from jl_sitediff_detail_now
		union all
	   select sitecode2,sitecode1
		 from jl_sitediff_detail_now
       )		 

[小孔大胖]

把这些加在哪里。。

[chengccy]

引用 10 楼 qq_40109328 的回复:

把这些加在哪里。。

你的需求是什么？ 将这个去重后的结果集以游标方式返回出去还是更新掉原有的表？ 如果是后者的话， 需要用delete

[小孔大胖]

就是不动数据。。。。 只改变查询的方式