Oracle 动态sql小例子

错误写法：

create or replace procedure testproce20130228
is
sqlstr varchar2(8000);
date1 varchar2(10);
begin
select sysdate into date1 from dual;
sqlstr:= 'insert into testtbl values(''test2222'','''||(select to_char(date1,'yyyy-mm-dd')  from dual)||''')';

execute immediate sqlstr;

commit;
end;

 

正确写法：

create or replace procedure testproce20130228
is
sqlstr varchar2(8000);
date1 varchar2(10);
begin
select to_char(sysdate,'yyyy-mm-dd') into date1 from dual;
sqlstr:= 'insert into testtbl values(''test2222'','''||date1||''')';

execute immediate sqlstr;

commit;
end;