在jdbc中出现动态sql,比如 :
sql="select * from "+tablename+" where name=?"
或者
StringBuilder sql=new StringBuilder();
sql="select * from student where age='18'";
if(isByName)
sql.append(" and name='fengfei'");
这种情况下,如何转为存储过程呢?以上面的两个动态为例,在存储过程中实现如下:
create or replace procedure my_procedure1(tablename in varchar2,studentname in varchar2, my_cursor1 out
my_procedure_pkg.ref_cursor) as
sql varchar2(1000);
begin
sql :='select * from ' || my_procedure1.tablename || ' where name=' || my_procedure1.studentname;
open my_procedure1.my_cursor1 for sql;
end my_procedure1;
如果不是 select * 而是delete from ....那么存储过程需要稍作改变,相应部分改为
execute immediately sql;
上面第二个sql的存储过程如下:
create or replace procedure my_procedure2(ifByNmame in varchar2,my_cursor2 out my_procedure_pkg.ref_cursor) as
begin
if(ifByName="true") then
open my_cursor2 for
select * from student where age='18' and name='fengfei';
else
open my_cursor2 for
select * from student where age='18';
end my_procedure2;
代码都是临时手写的,所以可能有错误。