select count (A.id) from A,B --第一条
where A.id=B.id
and A.temp > var1 and A.temp < var2
and B.col > var3 and B.col < var4select count (A.id) from A,B --第二条
where A.id=B.id
and A.temp > var1 and A.temp < var2
and B.col > var3 and B.col < var4........
........ --第五条共有5次查询,每次的查询语句都是一样,就是查询的参数(var1,var2,var3,var4)不同,请问怎样做会使得查询最节省时间??(不用考虑单条语句的条件排列问题)
注:每个查询的数据量都很大,单个查询耗时近10秒。我现在用的是定义CURSOR的方法来移动获得,但好像时间并没节省多少!!Scount integer := 0;
CURSOR c1(v1,v2,v3,v4) is
select count (A.id) from A,B
where A.id=B.id
and A.temp > v1 and A.temp < v2
and B.col > v3 and B.col < v4BEGIN
open c1(.....);
FETCH c1 INTO Scount; --取得第一个查询的条数
close c1;
open c1(.....);
FETCH c1 INTO Scount; --取得第二个查询的条数
.....
..... --共移动5次分别获得5个查询的条数
where A.id=B.id
and A.temp > var1 and A.temp < var2
and B.col > var3 and B.col < var4select count (A.id) from A,B --第二条
where A.id=B.id
and A.temp > var1 and A.temp < var2
and B.col > var3 and B.col < var4........
........ --第五条共有5次查询,每次的查询语句都是一样,就是查询的参数(var1,var2,var3,var4)不同,请问怎样做会使得查询最节省时间??(不用考虑单条语句的条件排列问题)
注:每个查询的数据量都很大,单个查询耗时近10秒。我现在用的是定义CURSOR的方法来移动获得,但好像时间并没节省多少!!Scount integer := 0;
CURSOR c1(v1,v2,v3,v4) is
select count (A.id) from A,B
where A.id=B.id
and A.temp > v1 and A.temp < v2
and B.col > v3 and B.col < v4BEGIN
open c1(.....);
FETCH c1 INTO Scount; --取得第一个查询的条数
close c1;
open c1(.....);
FETCH c1 INTO Scount; --取得第二个查询的条数
.....
..... --共移动5次分别获得5个查询的条数
exeute immediate 'select count (A.id) from A,B where A.id=B.id and A.temp between :var1 and : var2 and B.col between :var3 and :var4' using var1_value,var2_value,var3_value,var4_value;
SP2-0734: 未知的命令开头 "1,3,TO_dat..." - 忽略了剩余的行。难道我写错了?
execute immediate 'select count (A.id) from apply A,process B where A.id=B.id and A.status between :var1 and : var2 and B.curdate between :var3 and :var4' using 1,3,TO_date('2001-08-17','YYYY-MM-DD'),TO_date('2001-09-17','YYYY-MM-DD');
如果呢还要把结果存在变量中,可以用
execute immediate 'select count (A.id) from apply A,process B where A.id=B.id and A.status between :var1 and : var2 and B.curdate between :var3 and :var4'
into varaaa
using 1,3,TO_date('2001-08-17','YYYY-MM-DD'),TO_date('2001-09-17','YYYY-MM-DD');varaaa是一个声明过的变量
在sqlplus中运行
create table tsttbl (a int,b date);
insert into tsttbl values(1,to_date('2003-12-01','yyyy-mm-dd'));
insert into tsttbl values(2,to_date('2003-01-01','yyyy-mm-dd'));
commit;set serveroutput on;declare
aa int;
begin
execute immediate 'select count(*) from tsttbl where b>:var2'
into aa using TO_date('1999-8-17','YYYY-MM-DD');
dbms_output.put_line(aa);
execute immediate 'select count(*) from tsttbl where b>:var2'
into aa using TO_date('2003-1-2','YYYY-MM-DD');
dbms_output.put_line(aa);
end;
/
结果是
2
1请问你是用什么来执行的?