示例一 plsql中使用绑定变量
[oracle@Oracle11g 2016]$ cat 021801.sql 修改前的sql语句
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1..1000 loop
open l_rc for
'select object_name from all_objects where object_id='||i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||'seconds...');
end;
/
[oracle@Oracle11g 2016]$ cat 021802.sql 修改后的sql语句
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1..1000 loop
open l_rc for
'select object_name from all_objects where object_id=:x' using i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||'seconds...');
end;
/
示例二 简单sql语句中使用绑定变量
定义变量
SQL> var a number;
给变量赋值
SQL> exec :a := 7803;
PL/SQL procedure successfully completed.
使用绑定变量
SQL> select object_id,object_name from all_objects where object_id=:a;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
7803 V$DIAG_IPS_PACKAGE_INCIDENT
示例三 存储过程中调用 绑定变量
create or replace procedure test_bind ( obj_id in number,obj_name out varchar2 )asv_sql varchar2(10000) := null;beginv_sql := 'select object_name from all_objects where object_id= :1'; --此处绑定变量 可以随便写EXECUTE IMMEDIATE v_sql into obj_name using obj_id; --注意此处的using子句，就是调用上面定义的绑定变量dbms_output.put_line(obj_id ||' '|| obj_name); end;
绑定变量 基本熟悉
转载于:https://www.cnblogs.com/iyoume2008/p/5201497.html
相关资源：JAVA上百实例源码以及开源项目