我有一个查询,因为某些字段是否做比较对象需要由参数决定,所以使用动态游标,但是发现速度特别慢,大概要20分钟(3万条检索结果)。后来把sql语句固定,用静态游标执行,速度减到2分钟左右。
我想知道,如何把动态游标改成静态的?或者各位有没有其他好的办法。select * from table_name
where field1 = para1 and field2 = para2
如果参数para1或para2没有值,字段field1或field2就不作为条件。
我想知道,如何把动态游标改成静态的?或者各位有没有其他好的办法。select * from table_name
where field1 = para1 and field2 = para2
如果参数para1或para2没有值,字段field1或field2就不作为条件。
V_para1 变量1类型;
V_para2 变量2类型;
Cursor Test_cur IS
select * from table_name
where field1 = V_para1 and field2 = V_para2;
begin
V_para1 := para1;
V_para2 := para2;
Open Test;
Fetch Test ......
end Test;
type type_cur is ref cursor;
cur type_cur;
begin
if para1 is null and para1 is null then
open cur for 'select * from table_name';
elsif para1 is not null and para2 is null then
open cur for 'select * from table_name where field1=:para1' using para1;
elsif para1 is null and para2 is not null then
open cur for 'select * from table_name where field2=:para2' using para2;
else
open cur for 'select * from table_name where field1=:para1 and field2=:para2' using para1,para2;
end if;
Fetch cur into ...;
...;
close cur;
end Test;
放在前端做,可以简化数据库的操作