请教在procedure中调用 execute immediate 的问题
例如，在过程中如下使用是可以的
a table1%rowtype;
b varchar2(1);
begin
select * into a from table1 where emp = p_emp;
b := a.sch_h20; --sch_h20是表table1的一个字段
end;
但是换成这样就不行，为何呢？
a table1%rowtype;
b varchar2(1);
sqlstr varchar2(2000);
begin
select * into a from table1 where emp = p_emp;
sqlstr := 'select a.sch_h20 from dual ';
execute immediate sqlstr into b;
end;
将后2句换成
sqlstr := 'select a.sch_h20 into b from dual ';
execute immediate sqlstr;
也一样是错的。
[解决办法]
substr= 'select '||a.sch_h20|| ' from dual ';
[解决办法]
a.sch_h20是参数，执行sql。
[解决办法]
sqlstr := 'select '||a.sch_h20|| ' into '||b|| ' from dual ';
execute immediate sqlstr;
[解决办法]
declare
a table1%rowtype;
b varchar2(1);
sqlstr varchar2(2000);
begin
select col_name into a from table1 where emp = p_emp; --这里a不能接收结果集,只能是单条记录
sqlstr := 'select a.sch_h20 from dual ';
execute immediate sqlstr into b;
end;
[解决办法]
sqlstr := 'select a.sch_h20 from dual ';
应该是：
substr= 'select '||a.sch_h20|| ' from dual ';
