动态sql
declare
str_sql varchar2(200);
c_1 varchar2(20);
begin
c_1:=....//给变量赋值
str_sql:='select para_id from t_parameter where para_code= '||c_1;
execute immediate str_sql;
end;
/
declare
str_sql varchar2(200);
c_1 varchar2(20);
begin
c_1:=....//给变量赋值
str_sql:='select para_id from t_parameter where para_code= '||c_1;
execute immediate str_sql;
end;
/
那么str_sql的值是
select para_id from t_parameter where para_code=123
但是我想得到的结果是
select para_id from t_parameter where para_code='123'
请问该怎么办
as
tmpstr varchar(200);
begin
tmpstr := 'select para_id from t_parameter where para_code= '+ str;
execute immediate str_sql;
end;
pls-00103:encountered the symbol "select" when expecting one of following :.[*@....] at in is mod not rem........
而是光标
declare
cursor test_cur(mypara varchar2) is
select para_id from t_parameter
where para_code=mypara;
begin
mypara:='123';
open test_cur(mypara);
……
close test_cur;
end;
其实oracle建立不要过多使用动态sql。
str_sql varchar2(200);
c_1 varchar2(20);
begin
c_1:=....//给变量赋值
str_sql:='select para_id from t_parameter where para_code=:c_l ';
execute immediate str_sql into ...using c_1;
end;
但 penitent(只取一瓢) 做法更合适一些,尤其是返回多行时