求教一个存储过程(动态游标)

Question

存储过程输入参数有3个，objectid，startDate,endDate,其中startDate,endDate有可能同月,也有可能跨月
首先存储过程根据startDate和endDate，判断到底会有几个月的数据
然后循环月份，把下面的字符串中的$1$替换成日期，日期会作为表名后缀
select a.datadate,sum(a.total) total,sum(b.businesstotal) businesstotal,c.status 
      from  Total$1$ a,Business$1$ b, Status$1$ b,Object d 
        where  d.objectid = 传入的objectid
        and  d.objectid = a.objectid 
        and  d.objectid= b.objectid 
        and  d.objectid = c.objectid 
        and  a.datadate>=to_date('$1$01','yyyy-mm-dd')  
        and  a.datadate <=to_date('$1$31','yyyy-mm-dd')    
        group by a.datadate,c.status 
 然后把这些相应的串UNION ALL在一起得到总结果集，最后用动态游标查询出来

Answer 1

有四个表 汇总月表Total a ,业务月表Business b ,状态月表Status c ,对象表Object d,前三个表都有时间戳做结尾，统计当月的总数我这样写的： 
select a.datadate,sum(a.total) total,sum(b.businesstotal) businesstotal,c.status 
      from  Total200901 a,Business200901 b, Status200901 b,Object d 
        where  d.objectid = 传入的objectid
        and  d.objectid = a.objectid 
        and  d.objectid= b.objectid 
        and  d.objectid = c.objectid 
        and  a.datadate>=to_date('20090101','yyyy-mm-dd')  
        and  a.datadate <=to_date('20090131','yyyy-mm-dd')    
        group by a.datadate,c.status 

Answer 2

select a.datadate,sum(a.total) total,sum(b.businesstotal) businesstotal,c.status 
      from  Total200901 a,Business200901 b, Status200901 b,Object d 
        where  d.objectid = 传入的objectid 
        and  d.objectid = a.objectid(+)
        and  d.objectid= b.objectid(+)
        and  d.objectid = c.objectid(+)
        and  a.datadate>=to_date('20090101','yyyy-mm-dd')  
        and  a.datadate <=to_date('20090131','yyyy-mm-dd')    
        group by a.datadate,c.status 
--这样子OK,你的意思是跨月还是?

Answer 3

select a.datadate,sum(a.total) total,sum(b.businesstotal) businesstotal,c.status 
      from  Total200901 a,Business200901 b, Status200901 b,Object d 
        where  d.objectid = &objectid 
        and  d.objectid = a.objectid(+)
        and  d.objectid= b.objectid(+)
        and  d.objectid = c.objectid(+)
        and  a.datadate>=--startdate
        and  a.datadate <=--enddate
        group by a.datadate,c.status 

Answer 4

楼上没搞清楚我的意思，表Total a,Business b, Status也是动态变化的，后面会加月份时间戳，赋到表名后面，例如查询开始时间是20090101至20090201,判断到底会有几个月的数据 
然后循环月份，循环过程中，例如表Total的名字会动态改变Total200901及Total200902，然后UNION 这两个月的数据，用游标返回

Answer 5

在动态SQL中UNION, 拼好的SQL大概这样：
select a.datadate,sum(a.total) total,sum(b.businesstotal) businesstotal,c.status 
      from  (SELECT * FROM Total200901
                UNION ALL SELECT * FROM Total200902) a
              ,(SELECT * FROM Business200901 UNION ALL 
                SELECT * FROM Business200902) b
              ,(SELECT * FROM  Status200901 
                UNION ALL
                SELECT * FROM  Status200902 ) C,Object d 
        where  d.objectid = 传入的objectid
        and  d.objectid = a.objectid 
        and  d.objectid= b.objectid 
        and  d.objectid = c.objectid 
        and  a.datadate>=to_date('20090101','yyyy-mm-dd')  
        and  a.datadate <=to_date('20090228','yyyy-mm-dd')    
        group by a.datadate,c.status

Answer 6

大致思路如下，没有经过验证的：create or replace procedure p_test
(
    pi_objectid    in varchar2,
    pi_begin_date  in varchar2,
    pi_end_date    in varchar2
)
as
    v_begin_date  date;
    v_end_date    date;
    v_sql         varchar2(4000) := '';
    v_sql1        varchar2(4000) := '';
    v_sql2        varchar2(4000) := '';
    v_sql3        varchar2(4000) := '';
    type cur is ref cursor;
    v_cur cur;
begin
    v_begin_date := to_date(pi_begin_date, 'yyyymm');
    v_end_date := to_date(pi_end_date, 'yyyymm');
    
    v_sql := 
        'select a.datadate,sum(a.total) total,sum(b.businesstotal) businesstotal,c.status 
         from 
        ';
    while v_end_date >= v_begin_date loop
        v_sql1 := v_sql1 || 'select * from Total' || to_char(v_begin_date, 'yyyymm') || ' union all ';
        v_sql1 := v_sql1 || 'select * from Business' || to_char(v_begin_date, 'yyyymm') || ' union all ';
        v_sql1 := v_sql1 || 'select * from Status' || to_char(v_begin_date, 'yyyymm') || ' union all ';
        
        v_begin_date := add_months(v_begin_date, 1);
    end loop;    
        
    v_sql1 := rtrim(v_sql1, ' union all ') || ' a, ';
    v_sql2 := rtrim(v_sql2, ' union all ') || ' b, ';
    v_sql3 := rtrim(v_sql3, ' union all ') || ' c, ';
    
    v_sql := 
        v_sql || v_sql1 || v_sql2 || v_sql3 || 'Object d 
        where  d.objectid = ''' || pi_objectid || ''' 
        and  d.objectid = a.objectid 
        and  d.objectid= b.objectid 
        and  d.objectid = c.objectid 
        and  a.datadate>=to_date(''' || substr(pi_begin_date, 1, 6) ||'01'', ''yyyy-mm-dd'')  
        and  a.datadate <=to_date(''' || substr(pi_end_date, 1, 6) || '31'',''yyyy-mm-dd'')    
        group by a.datadate,c.status';
        
    open v_cur for v_sql;
    loop
        //取游标语句
    end loop; 
    close v_cur;
end p_test;

Answer 7

create or replace procedure p1(objectid varchar2,
                               startDate varchar2,
                               endDate varchar2,
                               p_out out SYS_REFCURSOR) as   v_sql     varchar2(32767);
  v_sql1    varchar2(32767);
  v_sql2    varchar2(32767);
  v_sql3    varchar2(32767);
  v_int     int;
  v_c_str1  varchar2(6);
  v_c_str2  varchar2(6);
begin
  execute immediate 'select MONTHS_BETWEEN(TRUNC(to_date(''' || endDate ||
                    ''', ''yyyymm''), ''month''),
                        TRUNC(to_date(''' || startDate ||
                    ''', ''yyyymm''), ''month'')) from dual'
    into v_int;
  for i in 0 .. v_Int loop
    v_c_str1 := to_char(add_months(TRUNC(to_date(startDate, 'yyyymm'),
                                         'month'),
                                   i),
                        'yyyymm');
    v_c_str2 := to_char(add_months(TRUNC(to_date(startDate, 'yyyymm'),
                                         'month'),
                                   i + 1),
                        'yyyymm');
    if length(v_sql1) > 0 then
      v_sql1 := v_sql1 || chr(10) || ' union all ' || chr(10) ||
                'select * from Total' || v_c_str1;
    else
      v_sql1 := '(select * from Total' || v_c_str1;
    end if;
  
    if length(v_sql2) > 0 then
      v_sql2 := v_sql2 || chr(10) || ' union all ' || chr(10) ||
                'select * from Business' || v_c_str1;
    else
      v_sql2 := '(select * from Business' || v_c_str1;
    end if;
  
    if length(v_sql3) > 0 then
      v_sql3 := v_sql3 || chr(10) || ' union all ' || chr(10) ||
                'select * from Status' || v_c_str1;
    else
      v_sql3 := '(select * from Status' || v_c_str1;
    end if;
  
  end loop;
  v_sql1 := v_sql1 || ')';
  v_sql2 := v_sql2 || ')';
  v_sql3 := v_sql3 || ')';
  v_sql  := 'select a.datadate,
       sum(a.total) total,
       sum(b.businesstotal) businesstotal,
       c.status
  from ' || v_sql1 || ' a, ' || v_sql2 || ' b, ' || v_sql3 ||
            ' c, Object d
 where d.objectid = ' || objectid || '
   and d.objectid = a.objectid
   and d.objectid = b.objectid
   and d.objectid = c.objectid
   and a.datadate >= to_date(''' || startDate ||
            '01'', ''yyyymmdd'')
   and a.datadate <  to_date(''' || v_c_str2 ||
            '01'', ''yyyymmdd'')
 group by a.datadate, c.status';
  --dbms_output.put_line(v_sql);
  open p_out for v_sql;
exception
  when others then
    dbms_output.put_line('ERROR!');
end;
/--测试输出如下sql:
  objectid  int := 123;
  startDate varchar2(6) := '200811';
  endDate   varchar2(6) := '200902';select a.datadate,
       sum(a.total) total,
       sum(b.businesstotal) businesstotal,
       c.status
  from (select *
          from Total200811
        union all
        select *
          from Total200812
        union all
        select *
          from Total200901
        union all
        select * from Total200902) a,
       (select *
          from Business200811
        union all
        select *
          from Business200812
        union all
        select *
          from Business200901
        union all
        select * from Business200902) b,
       (select *
          from Status200811
        union all
        select *
          from Status200812
        union all
        select *
          from Status200901
        union all
        select * from Status200902) c,
       Object d
 where d.objectid = 123
   and d.objectid = a.objectid
   and d.objectid = b.objectid
   and d.objectid = c.objectid
   and a.datadate >= to_date('20081101', 'yyyymmdd')
   and a.datadate < to_date('20090301', 'yyyymmdd')
 group by a.datadate, c.status

Answer 8

支持下楼上
楼主掌握下循环拼接和execute immediate的用法，自己也可以写的
楼上开头的MONTHS_BETWEEN(TRUNC(to_date(''' || endDate ||
                    ''', ''yyyymm''), ''month''),
                        TRUNC(to_date(''' || startDate ||
                    ''', ''yyyymm''), ''month''))
可以简化，不需要execute immediate:
而且传入的就是年月，没必要再truncv_int:=MONTHS_BETWEEN(to_date(  endDate 
                    , 'yyyymm'),
                        to_date(startDate 
                    , 'yyyymm'));
这样就行了。
拼接好的字符串长度很容易超出限制，注意去掉不必要的空格节省长度