17,134
社区成员
发帖
与我相关
我的任务
分享存储过程中select...into / execute immediate ... into 赋值总是空
我从表table1中根据条件查出tmp赋给v_tmp,可结果总是空的。但是我用生成v_Sql是可以查到数据的，
其他地方也用过这样的查询没什么错的
select tmp into v_tmp from table1 where to_number(tno)=to_number(v_tno) and to_number(tnoid)=to_number(v_tnoid);
或者
v_Sql:='select tmp from table1 where to_number(tno)=to_number('||v_tno||') and to_number(tnoid)=to_number('||v_tnoid||')';
execute immediate v_Sql into v_tmp;
其他地方也用过这样的查询没什么错的
select tmp into v_tmp from table1 where to_number(tno)=to_number(v_tno) and to_number(tnoid)=to_number(v_tnoid);
或者
v_Sql:='select tmp from table1 where to_number(tno)=to_number('||v_tno||') and to_number(tnoid)=to_number('||v_tnoid||')';
execute immediate v_Sql into v_tmp;
...全文
请发表友善的回复…
发表回复
IceIsabel 2012-12-21
- 打赏
- 举报
我也遇到过这种问题，什么原因？
dic_1990 2012-12-20
- 打赏
- 举报
execute immediate 'select tmp from table1 where to_number(tno)=to_number(''||v_p2||'') and to_number(tnoid)=to_number(''||v_p3||'')' into v_tmp
应该可以取到值
dic_1990 2012-12-20
- 打赏
- 举报
直接改成这样：
dic_1990 2012-12-20
- 打赏
- 举报
execute immediate v_Sql into v_tmp;
貌似不能这样写把
isea点xin 2012-12-05
- 打赏
- 举报
代码较多，问题是这样的
create or replace procedure tmp_proc(
resultStr out char,
v_p1 VARCHAR2
) is
v_income_seq number;
v_sql varchar2(1000);
v_p2 table2.p2%type,
v_p3 table3.p2%type
cursor curtk IS
SELECT trim(p2),trim(p3) FROM table2 order by desc;
Begin
open curtk;
Fetch curtk into v_p2,p3;
while curtk%found loop
--v_p2,v_p3都有值
/*select tmp into v_tmp from table1 where to_number(tno)=to_number(v_p2) and to_number(tnoid)=to_number(v_p3);*/
v_Sql:='select tmp from table1 where to_number(tno)=to_number('||v_p2||') and to_number(tnoid)=to_number('||v_p3||')';
execute immediate v_Sql into v_tmp;
--生成的v_sql在数据库中是可以查到数据的，但是v_tmp就是没有值
ResultStr:='v_sql:'||v_sql||' v_tmp:'||v_tmp;
Return;
Fetch curtk into p2,p3;
end loop;
close curtk;
End;
如果select count(*) into v_tmp from ...可以赋值
但是select count(tmp) into v_tmp from ..v_tmp还是空的
软件钢琴师 2012-12-04
- 打赏
- 举报
把你的整个代码贴出来
