绑定变量学习示例

示例一 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