绑定变量是相对于动态sql的固定变量而言的,如: declare l_sql varchar2(2000); l_count number; l_param1 varchar2(100); l_param2 varchar2(100); begin l_param1:='a'; l_param2:='b'; select count(*) into l_count from table1 where col_1=l_param1 and col_2=l_param2; dbms_output.put_line(l_count); end; / 其中,oracle会在编译是自动为sql语句绑定变量declare l_sql varchar2(2000); l_count number; l_param1 varchar2(100); l_param2 varchar2(100); begin l_param1:='a'; l_param2:='b'; l_sql:='select count(*) into :x from table1 where col_1='||l_param1||' and col_2='||l_param2; Execute Immediate l_sql into l_count; dbms_output.put_line(l_count); end; / 其中,执行时,每次执行都会动态构造语句,不会绑定变量;declare l_sql varchar2(2000); l_count number; l_param1 varchar2(100); l_param2 varchar2(100); begin l_param1:='a'; l_param2:='b'; l_sql:='select count(*) into :x from table1 where col_1=:y and col_2=:z '; Execute Immediate l_sql into l_count using l_param1,l_param2; dbms_output.put_line(l_count); end; / 其中,如果有多个循环,oracle的执行计划中,只会一次编译动态sql语句,然后每次执行时绑定变量:x,:y,:z;楼主可以根据自己的需要来决定是否使用绑定变量的方法
to:楼主 如果 A 表中有很多和B表匹配的记录,你用哪条记录去更改B 表呢?
UPDATE B表 SET c=(SELECT MAX(c) FROM A表 WHERE A.a=B.a AND A.b=B.b) WHERE (B.a,B.b) IN ( SELECT DISTINCT A.a, A.b FROM A表 )
是呀!update B set b.c=(select a.c from a where a.b=b.b);先选择后修改,这样可以节省不少时间呀!
declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
select count(*) into l_count from table1 where col_1=l_param1 and col_2=l_param2;
dbms_output.put_line(l_count);
end;
/
其中,oracle会在编译是自动为sql语句绑定变量declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
l_sql:='select count(*) into :x from table1 where col_1='||l_param1||' and col_2='||l_param2;
Execute Immediate l_sql into l_count;
dbms_output.put_line(l_count);
end;
/
其中,执行时,每次执行都会动态构造语句,不会绑定变量;declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
l_sql:='select count(*) into :x from table1 where col_1=:y and col_2=:z ';
Execute Immediate l_sql into l_count using l_param1,l_param2;
dbms_output.put_line(l_count);
end;
/ 其中,如果有多个循环,oracle的执行计划中,只会一次编译动态sql语句,然后每次执行时绑定变量:x,:y,:z;楼主可以根据自己的需要来决定是否使用绑定变量的方法
如果 A 表中有很多和B表匹配的记录,你用哪条记录去更改B 表呢?
SET c=(SELECT MAX(c) FROM A表 WHERE A.a=B.a AND A.b=B.b)
WHERE (B.a,B.b) IN
(
SELECT DISTINCT A.a, A.b
FROM A表
)