实际返回的行数超出请求的行数，這条语句改成用游标，该怎么写？

Question

create or replace procedure imms_pk_reportSend(fromDate varchar2,toDate varchar2)
as
/* 
  Function:系統效能統計
*/
    v_DRsuccess   number;
    v_DRfail      number;
    v_updateSum   number;
    v_failsum     number;
    v_datdeal     varchar2(20);
  BEGIN
       execute immediate 'truncate table imms_report_send';
        --dbms_output.put_line(fromDate||' '||toDate);
   for comuser in
           (select distinct rs.vc2campaignid,rs.vc2source,rs.vc2servicecode
           from imms_realsend rs,imms_realsendstatus iss
            where iss.numrealsendguid=rs.numrealsendguid
            and trunc(rs.datcreate) >= to_date(fromDate,'yyyy-mm-dd')
            and trunc(rs.datcreate) <=to_date(toDate,'yyyy-mm-dd'))
       loop
           begin
          --jiqu update 20080819
             v_DRsuccess := 0;
             select to_char(ir.datcreate, 'yyyymmdd') ,
             count(*)
             into v_datdeal,v_DRsuccess
             from imms_realsendstatus iss,imms_realsend ir
             where iss.numrealsendguid=ir.numrealsendguid
             and trunc(ir.datcreate) >= to_date(fromDate,'yyyy-mm-dd')
             and trunc(ir.datcreate) <=to_date(toDate,'yyyy-mm-dd')
             and iss.vc2reportstatus='S'--DR回覆筆數
             group by to_char(ir.datcreate, 'yyyymmdd');
           --jiaqu update 20080819
           exception
                when no_data_found then
                v_DRsuccess   := 0;           end;
           
           begin
             v_DRfail := 0;
             select to_char(ir.datcreate, 'yyyymmdd') ,
             count(*)
             into v_datdeal,v_DRfail
             from imms_realsendstatus iss,imms_realsend ir
             where iss.numrealsendguid=ir.numrealsendguid
             and trunc(ir.datcreate) >= to_date(fromDate,'yyyy-mm-dd')
             and trunc(ir.datcreate) <=to_date(toDate,'yyyy-mm-dd')
             and iss.vc2reportstatus='F'--DR未回覆筆數
             group by to_char(ir.datcreate, 'yyyymmdd');           exception
               when no_data_found then
                    v_DRfail := 0;
           end;
          
          begin
            v_updateSum :=0;
             select to_char(rs.datcreate, 'yyyymmdd') ,
             count(*)
             into v_datdeal,v_updateSum
             from imms_realsendstatus iss,imms_realsend rs
             where iss.numrealsendguid=rs.numrealsendguid
             and trunc(rs.datcreate) >= to_date(fromDate,'yyyy-mm-dd')
             and trunc(rs.datcreate) <=to_date(toDate,'yyyy-mm-dd')
             group by to_char(rs.datcreate, 'yyyymmdd');--report update總筆數
            exception 
                when no_data_found then
                      v_updateSum :=0;
          end;
          
           begin
            v_failsum :=0;
            select to_char(rs.datcreate, 'yyyymmdd') ,
             count(*)
             into v_datdeal,v_failsum
             from imms_realsend rs,imms_realsendstatus iss
             where iss.numrealsendguid=rs.numrealsendguid
             and trunc(rs.datcreate)>= to_date(fromDate,'yyyy-mm-dd')
             and trunc(rs.datcreate)<=to_date(toDate,'yyyy-mm-dd')
             and iss.vc2reportstatus='F'
             group by to_char(rs.datcreate, 'yyyymmdd');--失敗總筆數
            exception 
                when no_data_found then
                      v_failsum :=0;
          end;
          
           insert into imms_report_send
                 (numrsguid, vc2campaignid, datcreate, numsuccesssum, numfailsum,numtotalsum, vc2source,numcontentfailed,numreportupdate,vc2servicecode)
           values
                 (seq_reportsend.nextval,comuser.vc2campaignid, to_date(v_datdeal,'yyyymmdd'),v_DRsuccess,v_DRfail,v_DRsuccess+v_DRfail ,comuser.vc2source, v_updateSum,v_failsum+v_DRfail,comuser.vc2servicecode);
           COMMIT;       end loop; 
END;
因為會返回多條記錄，但是都要插入imms_report_send表。
請問用遊標該怎么做？
遊標不會用。
謝謝了。各位。

Answer 1

一个SQL就可以搞定了INSERT INTO imms_report_send
(numrsguid, vc2campaignid, datcreate, numsuccesssum,
numfailsum,numtotalsum, vc2source,numcontentfailed,numreportupdate,vc2servicecode)
SELECT seq_reportsend.nextval, rs.vc2campaignid,
TRUNC(ir.datcreate) AS datdeal,
SUM(DECODE(iss.vc2reportstatus,'S',1,0)) AS DRsuccess,
SUM(DECODE(iss.vc2reportstatus,'F',1,0)) AS DRfail,
SUM(DECODE(iss.vc2reportstatus,'S',1,'F',1,0)) AS Total,
rs.vc2source, 
COUNT(*) AS updateSum,
SUM(DECODE(iss.vc2reportstatus,'F',1,0))*2 AS failsum,
rs.vc2servicecode
FROM imms_realsend rs,imms_realsendstatus iss
WHERE iss.numrealsendguid=rs.numrealsendguid
AND rs.datcreate >= to_date(fromDate,'yyyy-mm-dd')
AND rs.datcreate <=to_date(toDate,'yyyy-mm-dd')
GROUP BY rs.vc2campaignid, rs.vc2source, rs.vc2servicecode, TRUNC(ir.datcreate)
/

Answer 2

但是SELECT seq_reportsend.nextval, rs.vc2campaignid,
此處不允許序列(序號)，這個有辦法解決么？

Answer 3

sorry, 稍微改改就行了.INSERT INTO imms_report_send
    (numrsguid, vc2campaignid, datcreate, numsuccesssum,
    numfailsum,numtotalsum, vc2source,numcontentfailed,numreportupdate,vc2servicecode)
select seq_reportsend.nextval, a.*
from
(
SELECT rs.vc2campaignid,
    TRUNC(ir.datcreate) AS datdeal,
    SUM(DECODE(iss.vc2reportstatus,'S',1,0)) AS DRsuccess,
    SUM(DECODE(iss.vc2reportstatus,'F',1,0)) AS DRfail,
    SUM(DECODE(iss.vc2reportstatus,'S',1,'F',1,0)) AS Total,
    rs.vc2source, 
    COUNT(*) AS updateSum,
    SUM(DECODE(iss.vc2reportstatus,'F',1,0))*2 AS failsum,
    rs.vc2servicecode
FROM imms_realsend rs,imms_realsendstatus iss
WHERE iss.numrealsendguid=rs.numrealsendguid
AND rs.datcreate >= to_date(fromDate,'yyyy-mm-dd')
AND rs.datcreate <=to_date(toDate,'yyyy-mm-dd')
GROUP BY rs.vc2campaignid, rs.vc2source, rs.vc2servicecode, TRUNC(ir.datcreate)
) a
/

Answer 4

想要imms_realsend 表中的count(*) Total;
不是SUM(DECODE(iss.vc2reportstatus,'S',1,'F',1,0)) AS Total,并不需要關聯imms_realsendstatus 唉，囧。

Answer 5

改成外连接就可以了
INSERT INTO imms_report_send
    (numrsguid, vc2campaignid, datcreate, numsuccesssum,
    numfailsum,numtotalsum, vc2source,numcontentfailed,numreportupdate,vc2servicecode)
select seq_reportsend.nextval, a.*
from
(
SELECT rs.vc2campaignid,
    TRUNC(ir.datcreate) AS datdeal,
    SUM(DECODE(iss.vc2reportstatus,'S',1,0)) AS DRsuccess,
    SUM(DECODE(iss.vc2reportstatus,'F',1,0)) AS DRfail,
    COUNT(rs.rowid) AS Total,
    rs.vc2source,
    COUNT(*) AS updateSum,
    SUM(DECODE(iss.vc2reportstatus,'F',1,0))*2 AS failsum,
    rs.vc2servicecode
FROM imms_realsend rs,imms_realsendstatus iss
WHERE rs.numrealsendguid=iss.numrealsendguid(+)
AND rs.datcreate >= to_date(fromDate,'yyyy-mm-dd')
AND rs.datcreate <=to_date(toDate,'yyyy-mm-dd')
GROUP BY rs.vc2campaignid, rs.vc2source, rs.vc2servicecode, TRUNC(ir.datcreate)
) a
/

Answer 6

跟我想的一樣。
呵呵！
不過我是這么連的
WHERE rs.numrealsendguid(+)=iss.numrealsendguid

Answer 7

SELECT rs.vc2campaignid,
       TRUNC(rs.datcreate) AS datdeal,
       SUM(DECODE(iss.vc2reportstatus, 'S', 1, 0)) AS DRsuccess,
       SUM(DECODE(iss.vc2reportstatus, 'F', 1, 0)) AS DRfail,
      -- count(DECODE(rs.VC2SOURCE, '0', 1, '1', 1, 1)) AS Total,
       count(rs.numrealsendguid) total,
       COUNT(iss.numrealsendstatusguid) AS updateSum,
       SUM(DECODE(iss.vc2reportstatus, 'F', 1, 0)) +sum(DECODE(rs.vc2status, '2', 1, 0)) AS failsum,
       rs.vc2servicecode
  FROM imms_realsend rs, imms_realsendstatus iss
 WHERE iss.numrealsendguid(+) = rs.numrealsendguid
   AND rs.datcreate >= to_date('2008-02-02', 'yyyy-mm-dd')
   AND rs.datcreate <= to_date('2008-08-29', 'yyyy-mm-dd')
 GROUP BY rs.vc2campaignid,
          rs.vc2source,
          rs.vc2servicecode,
          TRUNC(rs.datcreate)