create or replace procedure transbal(tyear in number, tmonth in number, coid in varchar2) is v_tabname varchar2(20); begin delete from qq ; select A into v_tabname from B where coid=coid and intyear=tyear and intmonth=tmonth ; execute immediate ' insert into qq select * from '||v_tabname; commit; end transbal; 现在我编译无错误,但我测试时,总报select那行有错误,但是我在哪张表用同样的参数传过去,结果只有一张表,没有错,这是为什么?
ora-01422:实际返回的行数超出请求的行数 ora-06512:在"ttt.transbal",Line 5 ora-06512:在line3我参数依次用:2004,1,B002-01试的但我在b表中写 select A from b where coid='B002-01' and intyear=2004 and intmonth=1 这样查询出来就是一条数据,请帮忙看一下。
你单步调试的是不是到那里就报错,在那里调试时把当时的值取出来然后再查询一下,我感觉有可能参数有问题,要不不会报这样的错误的!肯定是有多行数据或者没有数据! 你在前面加一个判断看一下就知道了! select count(*) into v_cnt from b where where coid=coid and intyear=tyear and intmonth=tmonth ; if v_cnt=1 then select A into v_tabname from B where coid=coid and intyear=tyear and intmonth=tmonth ; end if;这样试一下就知道了!
select A into v_tabname from B where coid=coid and intyear=tyear and intmonth=tmonth and rownum=1;
因为你的表名是动态的!
v_tabname varchar2(20);
begin
delete from qq ;
select A into v_tabname from B where coid=coid and intyear=tyear and intmonth=tmonth ;
execute immediate ' insert into qq select * from '||v_tabname;
commit;
end transbal;
现在我编译无错误,但我测试时,总报select那行有错误,但是我在哪张表用同样的参数传过去,结果只有一张表,没有错,这是为什么?
ora-06512:在"ttt.transbal",Line 5
ora-06512:在line3我参数依次用:2004,1,B002-01试的但我在b表中写
select A from b where coid='B002-01' and intyear=2004 and intmonth=1
这样查询出来就是一条数据,请帮忙看一下。
你在前面加一个判断看一下就知道了!
select count(*) into v_cnt from b where where coid=coid and intyear=tyear and intmonth=tmonth ;
if v_cnt=1 then
select A into v_tabname from B where coid=coid and intyear=tyear and intmonth=tmonth ;
end if;这样试一下就知道了!
and rownum=1;