变量表名以及oracle是否可循环自加问题

Question

Create or Replace procedure sp_Rpt_countivr
(
     i_sBeginTime in varchar2,
   i_sEndTime in varchar2,
   i_sBeginMonth in varchar2,
   i_sEndMonth in varchar2   
)
AS
   sBeginTime date;
   sEndTime date;
   i NUMBER;
   sBeginMonth NUMBER;
   sEndMonth NUMBER;
   sIntfor NUMBER; --月份跨度，依据此来循环查询次数
   sTableName varchar2; --所查询的表名变量，"tbilllog + sMonth"
   sCallinNUM NUMBER; --呼入量BEGIN
sBeginTime := to_date(i_sBeginTime,'yyyymmddhh24miss');
sEndTime := to_date(i_sEndTime,'yyyymmddhh24miss');
sBeginMonth := to_number(i_sEndMonth);
sEndMonth := to_number(i_sBeginMonth);
sIntfor :=  sBeginMonth - sEndMonth;/* 循环计算表名及查询 */
i := 0;
Loop
i := i+1;
sTableName := "tbilllog" + to_char(sBeginMonth);
sBeginMonth := sBeginMonth + 1;
----count call in number----
execute immediate 
'
select count(*) into sCallinNUM
from sTableName
where CALLEND >= sBeginTime and CALLEND < sEndTime
  and
  CALLTYPE in (0,5,13);
';
----count call in number----
sCallinNUM := sCallinNUM + --循环自加？？？？？？
if i < sIntfor then
   exit;
end if;
end Loop;insert into 
   tRpt_CrmTmp(cBeginTime, cEndTime, cCallinNUM, cCalledNum, cCalllostNum, cCalledRATE, cCalllostRATE, cCallinivrNUM, cCalledivrNUM, cCalllostivrNUM, cCalledivrRATE, cCalllostivrRATE, cWaitavgTIME, cQuitavgTIME)
   values (sBeginTime, sEndTime, sCallinNUM, 10, 20, 100, 31, 42, 53, 64, 100, 77, 40, 25);END sp_Rpt_countivr; 以上的代码肯定有问题:-:生手且还没完成。仅是个引子。
存储过程原义：
    通过B/S页面传入存储过程参数：开始日期时间、结束日期时间、开始月份、结束月份，存储过程将数据转换，查询“tBillLog+开始月份”到“tBillLog+结束月份”的表，获取符合条件的记录总数。插入到另一个表里去。问题：
1、有一个存储过程需要自加，不知道在Oracle里怎么实现？
2、存在一个变量表名，如此循环取值、自加是否可行？

Answer 1

sTableName := "tbilllog" + to_char(sBeginMonth);
==>sTableName := 'tbilllog' ||to_char(sBeginMonth);
循环自加指的是？

Answer 2

不是有个变量表名么，循环查表，每次count后的记录数相加
sCallinNUM := sCallinNUM + --循环自加？？？？？？

Answer 3

看得不是很明白..
那个execute immediate语句写错了
你说的循环自加，应该再定义一个数值变量吧v_num
v_num:=v_num+sCallinNUM
要不你这个自加没有意义，每次运行execute的时候都会被重新赋值
还有那个loop，看起来好像是死循环..

Answer 4

上面已经说过了我说个设计问题，
tBillLog为什么不按月份建立分区表呢

Answer 5

good:)
我还真不是一般的笨

Answer 6

/* 循环计算表名及查询 */
i := 0;
Loop
    i := i+1;
    sTableName := "tbilllog" + to_char(sBeginMonth);
    sBeginMonth := sBeginMonth + 1;
----count call in number----
execute immediate 
'
select count(*) into sCallinNUM
from sTableName
where CALLEND >= sBeginTime and CALLEND < sEndTime
      and
      CALLTYPE in (0,5,13);
';
----count call in number----
    sCallinNUM := sCallinNUM + --循环自加？？？？？？
    if i < sIntfor then
       exit;
    end if;
end Loop;
好像真有问题
可能sBeginMonth := sBeginMonth + 1;存在的位置有问题。
而且没有考虑当结束月份等于开始月份的情况

Answer 7

那个跨度应该用endmonth-beginmonth吧，而且实际上这样也不对。你要的跨度是sbegintime到sendtime的月数吗
还是怎么
你能不能具体说下需求，还有那个execute immediate里的表名是怎么回事
我来帮你写一个试试

Answer 8

你看看这个是否符合你的意思
对你的原码稍作修改
Create or Replace procedure sp_Rpt_countivr
(
        i_sBeginTime in varchar2,
       i_sEndTime in varchar2,
       i_sBeginMonth in varchar2,
       i_sEndMonth in varchar2   
)
AS
       sBeginTime date;
       sEndTime date;
       i NUMBER;
       sBeginMonth NUMBER;
       sEndMonth NUMBER;
       sIntfor NUMBER;        --月份跨度，依据此来循环查询次数
       sTableName varchar2(20);    --所查询的表名变量，"tbilllog + sMonth"
       sCallinNUM NUMBER;    --呼入量
       v_num number:=0;
       sqlstr varchar2(500);
BEGIN
sBeginTime := to_date(i_sBeginTime,'yyyymmddhh24miss');
sEndTime := to_date(i_sEndTime,'yyyymmddhh24miss');
sBeginMonth := to_number(i_sEndMonth);
sEndMonth := to_number(i_sBeginMonth);
sIntfor :=  sBeginMonth - sEndMonth;/* 循环计算表名及查询 */
i := 0;
Loop
   i := i+1;
    sTableName := 'tbilllog'|| sBeginMonth;
    sBeginMonth := sBeginMonth + 1;
----count call in number----
sqlstr:= '
select count(1) 
from '||sTableName||'
where CALLEND >= to_date('''||to_char(sBeginTime,'YYYYMMDDHH24MISS')||
  ''',''YYYYMMDDHH24MISS'') and CALLEND <to_date( '''||
  to_char(sEndTime,'YYYYMMDDHH24MISS')||''',''YYYYMMDDHH24MISS'')
      and
      CALLTYPE in (0,5,13)
' ;execute immediate sqlstr into sCallinNUM;
----count call in number----
   v_num :=v_num+sCallinNUM; 
    if i > sIntfor then
       exit;
    end if;
end Loop;insert into 
       tRpt_CrmTmp(cBeginTime, cEndTime, cCallinNUM, cCalledNum, cCalllostNum, cCalledRATE, cCalllostRATE, cCallinivrNUM, cCalledivrNUM, cCalllostivrNUM, cCalledivrRATE, cCalllostivrRATE, cWaitavgTIME, cQuitavgTIME)
       values (sBeginTime, sEndTime, sCallinNUM, 10, 20, 100, 31, 42, 53, 64, 100, 77, 40, 25);END sp_Rpt_countivr;

Answer 9

有些冗余，更正下
Create or Replace procedure sp_Rpt_countivr
(
        i_sBeginTime in varchar2,
       i_sEndTime in varchar2,
       i_sBeginMonth in varchar2,
       i_sEndMonth in varchar2   
)
AS
       sBeginTime date;
       sEndTime date;
       i NUMBER;
       sBeginMonth NUMBER;
       sEndMonth NUMBER;
       sIntfor NUMBER;        --月份跨度，依据此来循环查询次数
       sTableName varchar2(20);    --所查询的表名变量，"tbilllog + sMonth"
       sCallinNUM NUMBER;    --呼入量
       v_num number:=0;
       sqlstr varchar2(500);
BEGIN
sBeginTime := i_sBeginTime;
sEndTime :=i_sEndTime;
sBeginMonth := to_number(i_sEndMonth);
sEndMonth := to_number(i_sBeginMonth);
sIntfor :=  sBeginMonth - sEndMonth;/* 循环计算表名及查询 */
i := 0;
Loop
   i := i+1;
    sTableName := 'tbilllog'|| sBeginMonth;
    sBeginMonth := sBeginMonth + 1;
----count call in number----
sqlstr:= '
select count(1) 
from '||sTableName||'
where CALLEND >= to_date('''||sBeginTime||
  ''',''YYYYMMDDHH24MISS'') and CALLEND <to_date( '''||
  sEndTime||''',''YYYYMMDDHH24MISS'')
      and
      CALLTYPE in (0,5,13)
' ;execute immediate sqlstr into sCallinNUM;
----count call in number----
   v_num :=v_num+sCallinNUM; 
    if i > sIntfor then
       exit;
    end if;
end Loop;insert into 
       tRpt_CrmTmp(cBeginTime, cEndTime, cCallinNUM, cCalledNum, cCalllostNum, cCalledRATE, cCalllostRATE, cCallinivrNUM, cCalledivrNUM, cCalllostivrNUM, cCalledivrRATE, cCalllostivrRATE, cWaitavgTIME, cQuitavgTIME)
       values (sBeginTime, sEndTime, sCallinNUM, 10, 20, 100, 31, 42, 53, 64, 100, 77, 40, 25);END sp_Rpt_countivr; 

Answer 10

execute immediate 
'
select count(*) into sCallinNUM
from sTableName
where CALLEND >= sBeginTime and CALLEND < sEndTime
      and
      CALLTYPE in (0,5,13);
';这样写是错误的
into应该在外面而且这里sTableName是一个静态部分，必须拼出来
sBeginTime， sEndTime是动态参数部分可以用using所以应该是
execute immediate 'select count(*) from '|| sTableName||' where CALLEND >=:1 and CALLEND <:2 and CALLTYPE in (0,5,13)' into sCallinNUM using sBeginTime, sEndTime;其他的地方就没有详细看了。

Answer 11

在这个基础上稍微修改了一下参数。还真得我的变量:=方式，创建procedure成功。但是尝试如下，即希望查表tBillLog7和表tBillLog8在两个时间内的记录数，获其相加的和exec SP_RPT_COUNTIVR('20090701000000','20090802000000','7','8');发现获取到的v_num和sCallinNUM都是第二个表的v_num :=v_num+sCallinNUM
迟点参考参考inthirties  10楼的兄弟成果。

Answer 12

你是说插入的结果吗
忘了改了，insert 括号里面的sCallinNum按你想得到的结果应该要改成v_num

Answer 13

你是说插入的结果吗
忘了改了，insert 括号里面的sCallinNum按你想得到的结果应该要改成v_num

Answer 14

该成v_num也一样。很奇怪。看下面改好的存储过程：Create or Replace procedure sp_Rpt_countivr
(
       i_sBeginTime in varchar2,
       i_sEndTime in varchar2,
       i_sBeginMonth in varchar2,
       i_sEndMonth in varchar2   
)
AS
       sBeginTime date;
       sEndTime date;
       i NUMBER;
       sBeginMonth NUMBER;
       sEndMonth NUMBER;
       sIntfor NUMBER;          --月份跨度，依据此来循环查询次数
       sTableName varchar2(20);     --所查询的表名变量，"tbilllog + sMonth"
       sCallinNUM NUMBER;     --呼入量
       v_num number:=0;
       sqlstr varchar2(500);
BEGIN
sBeginTime := to_date(i_sBeginTime, 'yyyymmddhh24miss');
sEndTime := to_date(i_sEndTime, 'yyyymmddhh24miss');
sBeginMonth := to_number(i_sEndMonth,'99');
sEndMonth := to_number(i_sBeginMonth,'99');
sIntfor :=  sEndMonth - sBeginMonth + 1;  --加1或不加1，对7-8月都一样是8月的结果；对7-7月却是7月表和8月表的和/* 循环计算表名及查询 */
i := 0;
Loop
   i := i+1;
    sTableName := 'tbilllog'|| to_char(sBeginMonth);
----count call in number----
sqlstr:= '
select count(*) 
from '||sTableName||'
where CALLEND >= to_date('''||i_sBeginTime||''',''YYYYMMDDHH24MISS'') 
  and 
  CALLEND < to_date('''||i_sEndTime||''',''YYYYMMDDHH24MISS'')
      and
      CALLTYPE in (0,5,13)
' ;execute immediate sqlstr into sCallinNUM;
----count call in number----
    v_num := v_num + sCallinNUM; 
    sBeginMonth := sBeginMonth + 1;
    if i > sIntfor then
       exit;
    end if;
end Loop;
/* 删除tRpt_CrmTmp表或删除表数据 */
--drop table tRpt_CrmTmp;
delete from tRpt_CrmTmp;/* 创建表 */
/*
create TABLE tRpt_CrmTmp
   (
     cBeginTime date,
 cEndTime date,
 
 cCallinNUM number,
 cCalledNUM number,
 cCalllostNUM number,
 cCalledRATE number(12,4),
 cCalllostRATE number(12,4),
 
 cCallinivrNUM number,
 cCalledivrNUM number,
 cCalllostivrNUM number,
 cCalledivrRATE number(12,4),
 cCalllostivrRATE number(12,4),
 
 cWaitavgTIME number(12,4),
 cQuitavgTIME number(12,4)
 
   ); 
*//* 插入数据 */
insert into 
       tRpt_CrmTmp(cBeginTime, cEndTime, cCallinNUM, cCalledNum, cCalllostNum, cCalledRATE, cCalllostRATE, cCallinivrNUM, cCalledivrNUM, cCalllostivrNUM, cCalledivrRATE, cCalllostivrRATE, cWaitavgTIME, cQuitavgTIME)
       values (sBeginTime, sEndTime, v_num, 10, 20, 100, 31, 42, 53, 64, 100, 77, 40, 25);/* 查询数据 */
/*
select * from tRpt_CrmTmp;
*/END sp_Rpt_countivr; 
exec SP_RPT_COUNTIVR('20090701000000','20090802000000','7','8');
或
exec SP_RPT_COUNTIVR('20090701000000','20090802000000','7','7');

Answer 15

我测试过了，没问题。定义变量的地方有个格式忘记改过来
Create or Replace procedure sp_Rpt_countivr
(
        i_sBeginTime in varchar2,
       i_sEndTime in varchar2,
       i_sBeginMonth in varchar2,
       i_sEndMonth in varchar2   
)
AS
       sBeginTime varchar2(20);
       sEndTime varchar2(20);
       i NUMBER;
       sBeginMonth NUMBER;
       sEndMonth NUMBER;
       sIntfor NUMBER;        --月份跨度，依据此来循环查询次数
       sTableName varchar2(20);    --所查询的表名变量，"tbilllog + sMonth"
       sCallinNUM NUMBER;    --呼入量
       v_num number:=0;
       sqlstr varchar2(500);
BEGIN
sBeginTime := i_sBeginTime;
sEndTime :=i_sEndTime;
sBeginMonth := to_number(i_sEndMonth);
sEndMonth := to_number(i_sBeginMonth);
sIntfor :=  sBeginMonth - sEndMonth;/* 循环计算表名及查询 */
i := 0;
Loop
   i := i+1;
    sTableName := 'tbilllog'|| sBeginMonth;
    sBeginMonth := sBeginMonth + 1;
----count call in number----
sqlstr:= '
select count(1) 
from '||sTableName||'
where CALLEND >= to_date('''||sBeginTime||
  ''',''YYYYMMDDHH24MISS'') and CALLEND <to_date( '''||
  sEndTime||''',''YYYYMMDDHH24MISS'')
      and
      CALLTYPE in (0,5,13)
' ;execute immediate sqlstr into sCallinNUM;
----count call in number----
   v_num :=v_num+sCallinNUM; 
    if i > sIntfor then
       exit;
    end if;
dbms_output.put_line('tbilllog'||sbeginmonth-1||
'  '||'v_num '||v_num||'scallinnum '||scallinnum);    
end Loop;
--insert into 
--       tRpt_CrmTmp(cBeginTime, cEndTime, cCallinNUM, cCalledNum, cCalllostNum, cCalledRATE, cCalllostRATE, cCallinivrNUM, cCalledivrNUM, cCalllostivrNUM, cCalledivrRATE, cCalllostivrRATE, cWaitavgTIME, cQuitavgTIME)
 --      values (sBeginTime, sEndTime, sCallinNUM, 10, 20, 100, 31, 42, 53, 64, 100, 77, 40, 25);END sp_Rpt_countivr; 
中间加了两个输出，你比较下两个v_num和scallinnum的值，看看是否一样
如果一样的话你检查下两个表里的数据

Answer 16

这个
Create or Replace procedure sp_Rpt_countivr
(
        i_sBeginTime in varchar2,
       i_sEndTime in varchar2,
       i_sBeginMonth in varchar2,
       i_sEndMonth in varchar2   
)
AS
       sBeginTime varchar2(20);
       sEndTime varchar2(20);
       i NUMBER;
       sBeginMonth NUMBER;
       sEndMonth NUMBER;
       sIntfor NUMBER;        --月份跨度，依据此来循环查询次数
       sTableName varchar2(20);    --所查询的表名变量，"tbilllog + sMonth"
       sCallinNUM NUMBER;    --呼入量
       v_num number:=0;
       sqlstr varchar2(500);
BEGIN
sBeginTime := i_sBeginTime;
sEndTime :=i_sEndTime;
sBeginMonth := to_number(i_sEndMonth);
sEndMonth := to_number(i_sBeginMonth);
sIntfor :=  sBeginMonth - sEndMonth;/* 循环计算表名及查询 */
i := 0;
Loop
   i := i+1;
    sTableName := 'tbilllog'|| sBeginMonth;
    sBeginMonth := sBeginMonth + 1;
----count call in number----
sqlstr:= '
select count(1) 
from '||sTableName||'
where CALLEND >= to_date('''||sBeginTime||
  ''',''YYYYMMDDHH24MISS'') and CALLEND <to_date( '''||
  sEndTime||''',''YYYYMMDDHH24MISS'')
      and
      CALLTYPE in (0,5,13)
' ;execute immediate sqlstr into sCallinNUM;
----count call in number----
   v_num :=v_num+sCallinNUM; 
    if i > sIntfor then
       exit;
    end if;
dbms_output.put_line('tbilllog'||to_char(sbeginmonth-1)||
' v_num '||v_num||' scallinnum '||scallinnum);    
end Loop;
--insert into 
--       tRpt_CrmTmp(cBeginTime, cEndTime, cCallinNUM, cCalledNum, cCalllostNum, cCalledRATE, cCalllostRATE, cCallinivrNUM, cCalledivrNUM, cCalllostivrNUM, cCalledivrRATE, cCalllostivrRATE, cWaitavgTIME, cQuitavgTIME)
 --      values (sBeginTime, sEndTime, sCallinNUM, 10, 20, 100, 31, 42, 53, 64, 100, 77, 40, 25);END sp_Rpt_countivr; 

Answer 17

--sBeginMonth := to_number(i_sEndMonth,'99');  --WRONG
--sEndMonth := to_number(i_sBeginMonth,'99');  --WRONG
sBeginMonth := to_number(i_sBeginMonth,'99');
sEndMonth := to_number(i_sEndMonth,'99');晕，问题出在这两句上，赋值调了个。。
谢谢各位。