PLSQL绑定变量用法小结.doc

1、之前对 ORACLE 中的变量一直没个太清楚的认识，比如说使用：、 2. 3. ID NAME 4. - - 5. 1 test 6. 7. SQL select * from tt where id=2; 8. 9. ID NAME 10. - - 11. 2 test 12. 13. SQL variable i number; 14. SQL exec :i :=1; 15. 16. PL/SQL 过程已成功完成。 17. 18. SQL select *from tt where id=:i; 19. 20. ID NAME 21. - - 22. 1 test 23. 24. SQL

2、 exec :i :=2; 25. 26. PL/SQL 过程已成功完成。 27. 28. SQL select *from tt where id=:i; 29. 30. ID NAME 31. - - 32. 2 test 33. 34. SQL print i; 35. 36. I 37. - 38. 2 39. 40. SQL select sql_text,parse_calls from v$sql where sql_text like select * from t 41. t where id=%; 42. 43. SQL_TEXT PARSE_CALLS 44. - - 4

3、5. select * from tt where id=2 1 46. select * from tt where id=1 1 47. select * from tt where id=:i 2 48. SQL SQL select * from tt where id=1;ID NAME- -1 testSQL select * from tt where id=2;ID NAME- -2 testSQL variable i number;SQL exec :i :=1;PL/SQL 过 程 已 成 功 完 成 。SQL select *from tt where id=:i;从上

4、面试验发现绑定变量 i 的使用使查询 id=1 和 id=2 的 sqlselect *from tt where id=:i 得以重复使用，从而避免了 hard parse，这里的 PARSE_CALLS2 包括了一次 soft parse2.前两天看到有人在 pub 上问在 sqlplus 中通过 define 和 variable 定义的变量的区别。其实 define 定义的我理解不是变量而是字符常量，通过 define 定义之后，在通过 21. ID NAME 22. - - 23. 1 a 24. 2 a 25. 3 “abc“ 26. SQL define a 27. SP2-01

5、35: 符号 a 未定义 28. SQL define a=1 29. SQL define 30. DEFINE _DATE = “01-4 月 -08“ (CHAR) 31. DEFINE _CONNECT_IDENTIFIER = “db11“ (CHAR) 32. DEFINE _USER = “XYS“ (CHAR) 33. DEFINE _PRIVILEGE = “ (CHAR) 34. DEFINE _SQLPLUS_RELEASE = “1101000600“ (CHAR) 35. DEFINE _EDITOR = “Notepad“ (CHAR) 36. DEFINE _O_

6、VERSION = “Oracle Database 11g Enterprise Edition Release 11.1.0. 37. 6.0 - Production 38. With the Partitioning, OLAP, Data Mining and Real Application Testing options“ (CHAR) 39. DEFINE _O_RELEASE = “1101000600“ (CHAR) 40. DEFINE A = “1“ (CHAR) 41. -通过上面显示 define 定义的应该是字符（串）常量。 42. SQL select * fr

7、om tt where id= 43. 原值 1: select * from tt where id= 49. 原值 1: select * from tt where id= 55. SQL define 56. DEFINE _DATE = “01-4 月 -08“ (CHAR) 57. DEFINE _CONNECT_IDENTIFIER = “db11“ (CHAR) 58. DEFINE _USER = “XYS“ (CHAR) 59. DEFINE _PRIVILEGE = “ (CHAR) 60. DEFINE _SQLPLUS_RELEASE = “1101000600“ (

8、CHAR) 61. DEFINE _EDITOR = “Notepad“ (CHAR) 62. DEFINE _O_VERSION = “Oracle Database 11g Enterprise Edition Release 11.1.0. 63. 6.0 - Production 64. With the Partitioning, OLAP, Data Mining and Real Application Testing options“ ( 65. CHAR) 66. DEFINE _O_RELEASE = “1101000600“ (CHAR) 67. DEFINE A = “

9、1“ (CHAR) 68. DEFINE B = “a“ (CHAR) 69. 70. -如果是字符类型那么在引用时别忘了加上单引号，另外通过 define 定义之后在引用时不需要输入了。 71. SQL select * from tt where name= 72. 原值 1: select * from tt where name= 80. 原值 1: select * from tt where name= 87. 原值 1: select * from tt where name= 96. SQL_TEXT 97. - 98. select * from tt where name=

10、1 99. select * from tt where name=a 100. SQL C:sqlplus xys/managerSQL*Plus: Release 11.1.0.6.0 - Production on 星 期 二Copyright (c) 1982, 2007, Oracle. All rights reserv连 接 到 :Oracle Database 11g Enterprise Edition Release 11.1With the Partitioning, OLAP, Data Mining and Real ASQL defineDEFINE _DATE =

11、 “01-4月 -08“ (CHAR)DEFINE _CONNECT_IDENTIFIER = “db11“ (CHAR)DEFINE _USER = “XYS“ (CHAR)DEFINE _PRIVILEGE = “ (CHAR)DEFINE _SQLPLUS_RELEASE = “1101000600“ (CHAR)DEFINE _EDITOR = “Notepad“ (CHAR)DEFINE _O_VERSION = “Oracle Database 11g Enterprise6.0 - ProductionWith the Partitioning, OLAP, Data Minin

12、g and Real ACHAR)3.oracle 在解析 sql 时会把 plsql 中定义的变量转为为绑定变量c-sharp view plaincopyprint?1. SQL create table tt(id int , name varchar2(10); 2. 3. 表已创建。 4. 5. SQL alter session set sql_trace=true; 6. 7. 会话已更改。 8. 9. SQL declare 10. 2 begin 11. 3 for i in 1100 loop 12. 4 insert into tt values(i,test); 13.

13、 5 end loop; 14. 6 commit; 15. 7 end; 16. 8 / 17. 18. PL/SQL 过程已成功完成。 19. 20. SQL alter session set sql_trace=false; 21. -trace file: 22. = 23. PARSING IN CURSOR #3 len=90 dep=0 uid=31 oct=47 lid=31 tim=7109565004 hv=962259239 24. 25. ad=668ec528 26. declare 27. begin 28. for i in 1100 loop 29. inse

14、rt into tt values(i,test); 30. end loop; 31. commit; 32. end; 33. END OF STMT 34. PARSE #3:c=15625,e=5678,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,tim=7109564996 35. = 36. PARSING IN CURSOR #5 len=34 dep=1 uid=31 oct=2 lid=31 tim=7109565520 hv=1299226876 37. 38. ad=66869934 39. INSERT INTO TT VALUES(:B1 ,

15、test) 40. END OF STMT 41. PARSE #5:c=0,e=226,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7109565513 42. = SQL create table tt(id int , name varchar2(10);表 已 创 建 。SQL alter session set sql_trace=true;会 话 已 更 改 。SQL declare2 begin3 for i in 1100 loop4 insert into tt values(i,test);5 end loop;6 commit;7 end

16、;8 /PL/SQL 过 程 已 成 功 完 成 。另外从 hard parse 的数据量上其实也可以大致猜测 oracle 会把 plsql 中定义的变量转换为绑定变量处理c-sharp view plaincopyprint?1. SQL connect /as sysdba 2. 已连接。 3. SQL shutdown immediate 4. 数据库已经关闭。 5. 已经卸载数据库。 6. ORACLE 例程已经关闭。 7. SQL startup 8. ORACLE 例程已经启动。 9. 10. Total System Global Area 167772160 bytes 11

17、. Fixed Size 1247900 bytes 12. Variable Size 67110244 bytes 13. Database Buffers 96468992 bytes 14. Redo Buffers 2945024 bytes 15. 数据库装载完毕。 16. 数据库已经打开。 17. SQL connect xys/manager 18. 已连接。 19. SQL drop table tt; 20. 21. 表已删除。 22. 23. SQL create table tt(id int , name varchar2(10); 24. 25. 表已创建。 26.

18、 SQL col name format a30 27. SQL select a.*,b.name 28. 2 from v$sesstat a , v$statname b 29. 3 where a.statistic#=b.statistic# 30. 4 and a.sid=(select distinct sid from v$mystat) 31. 5 and b.name like %parse%; 32. 33. SID STATISTIC# VALUE NAME 34. - - - - 35. 159 328 39 parse time cpu 36. 159 329 74

19、 parse time elapsed 37. 159 330 339 parse count (total) 38. 159 331 165 parse count (hard) 39. 159 332 0 parse count (failures) 40. 41. SQL declare 42. 2 begin 43. 3 for i in 1100 loop 44. 4 insert into tt values(i,test); 45. 5 end loop; 46. 6 commit; 47. 7 end; 48. 8 / 49. 50. PL/SQL 过程已成功完成。 51. 5

20、2. SQL select a.*,b.name 53. 2 from v$sesstat a , v$statname b 54. 3 where a.statistic#=b.statistic# 55. 4 and a.sid=(select distinct sid from v$mystat) 56. 5 and b.name like %parse% 57. 6 / 58. 59. SID STATISTIC# VALUE NAME 60. - - - - 61. 159 328 39 parse time cpu 62. 159 329 74 parse time elapsed

21、 63. 159 330 345 parse count (total) 64. 159 331 167 parse count (hard) 65. 159 332 0 parse count (failures) 66. 67. SQL SQL connect /as sysdba已 连 接 。SQL shutdown immediate数 据 库 已 经 关 闭 。已 经 卸 载 数 据 库 。ORACLE 例 程 已 经 关 闭 。SQL startupORACLE 例 程 已 经 启 动 。Total System Global Area 167772160 bytesFixed S

22、ize 1247900 bytesVariable Size 67110244 bytesDatabase Buffers 96468992 bytesRedo Buffers 2945024 bytes数 据 库 装 载 完 毕 。数 据 库 已 经 打 开 。SQL connect xys/manager已 连 接 。这里发现 hard parse 只增加了 2，如果没有使用绑定变量的话，相信 hard parse会更多4.过程中的参数会自动转化为绑定变量c-sharp view plaincopyprint?1. SQL edit 2. 已写入 file afiedt.buf 3. 4.

23、 1 create or replace procedure proc_test(p_id int, p_name varchar2) 5. 2 is 6. 3 begin 7. 4 insert into tt values(p_id , p_name); 8. 5 commit; 9. 6* end; 10. SQL / 11. 12. 过程已创建。 13. 14. SQL alter session set sql_trace=true; 15. 16. 会话已更改。 17. 18. SQL exec proc_test(200,test); 19. 20. PL/SQL 过程已成功完成

24、。 21. 22. SQL alter session set sql_trace=false; 23. 24. 会话已更改。 25. -trace file： 26. alter session set sql_trace=true 27. END OF STMT 28. EXEC #3:c=0,e=749,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7393908487 29. = 30. PARSING IN CURSOR #1 len=35 dep=0 uid=31 oct=47 lid=31 tim=7403000735 hv=526484776 3

25、1. 32. ad=6687b0b8 33. BEGIN proc_test(200,test); END; 34. END OF STMT 35. PARSE #1:c=0,e=2584,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7403000727 36. = 37. PARSING IN CURSOR #6 len=33 dep=1 uid=31 oct=2 lid=31 tim=7403001293 hv=2874748229 38. 39. ad=668e9cd8 40. INSERT INTO TT VALUES(:B2 , :B1 ) 41.

26、END OF STMT 42. PARSE #6:c=0,e=246,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7403001286 43. = SQL edit已 写 入 file afiedt.buf1 create or replace procedure proc_test(p_id int, p2 is3 begin4 insert into tt values(p_id , p_name);5 commit;6* end;SQL /过 程 已 创 建 。SQL alter session set sql_trace=true;会 话 已 更 改

27、。SQL exec proc_test(200,test);另外也可以直观的观察：c-sharp view plaincopyprint?1. SQL exec proc_test(200,test); 2. 3. PL/SQL 过程已成功完成。 4. 5. SQL select sql_text from v$sql where sql_text like %proc_test%; 6. 7. SQL_TEXT 8. - 9. BEGIN proc_test(200,test); END; 10. 11. SQL SQL exec proc_test(200,test);PL/SQL 过 程

28、 已 成 功 完 成 。SQL select sql_text from v$sql where sql_text likeSQL_TEXT-BEGIN proc_test(200,test); END;SQL在 sqlplus 里执行过程不能观察出来下面在 plsql developer 执行一次过程之后再来看执行的情况c-sharp view plaincopyprint?1. SQL select sql_text from v$sql where sql_text like %proc_test%; 2. 3. SQL_TEXT 4. - 5. begin -Call the proc

29、edure proc_test(p_id =:p_id, p_name =:p_name); end; 6. 7. SQL SQL select sql_text from v$sql where sql_text likeSQL_TEXT-begin - Call the procedure proc_test(p_id =:p_id,SQL很显然 oracle 在执行过程时把参数转化为绑定变量了，其实从 plsql developer 中执行过程时的语法就能看出来：c-sharp view plaincopyprint?1. begin 2. - Call the procedure 3.

30、 proc_test(p_id = :p_id, 4. p_name = :p_name); 5. end; begin- Call the procedureproc_test(p_id = :p_id,p_name = :p_name);end;在输入参数列表框上面的执行语法就是这样的。5.在动态 sql 中使用绑定变量，动态 sql 中使用绑定变量非常明显也容易理解，下面给出 2 个简单的例子c-sharp view plaincopyprint?1. SQL set serveroutput on 2. SQL declare 3. 2 v_string varchar2(100);

31、4. 3 v_id tt.id%type ; 5. 4 v_name tt.name%type ; 6. 5 begin 7. 6 v_string:=select * from tt where id=:v_id; 8. 7 execute immediate v_string into v_id , v_name using 9. 8 dbms_output.put_line(v_id| |v_name) ; 10. 9 end; 11. 10 / 12. 输入 a 的值 : 1 13. 原值 7: execute immediate v_string into v_id , v_name

32、 using 14. 新值 7: execute immediate v_string into v_id , v_name using 1; 15. 1 test 16. 17. PL/SQL 过程已成功完成。 18. 19. SQL declare 20. 2 v_string varchar2(100); 21. 3 v_id tt.id%type; 22. 4 v_name tt.name%type ; 23. 5 begin 24. 6 v_string:=insert into tt values(:id,:name); 25. 7 execute immediate v_stri

33、ng using 26. 8 end; 27. 9 / 28. 输入 id 的值: 1000 29. 输入 name 的值: test 30. 原值 7: execute immediate v_string using 31. 新值 7: execute immediate v_string using 1000,test ; 32. 33. PL/SQL 过程已成功完成。 34. 35. SQL select * from tt where id=1000; 36. 37. ID NAME 38. - - 39. 1000 test 40. 41. SQL SQL set serverou

34、tput onSQL declare2 v_string varchar2(100); 3 v_id tt.id%type ;4 v_name tt.name%type ; 5 begin6 v_string:=select * from tt where id=:v_id; 7 execute immediate v_string into v_id , v_nam8 dbms_output.put_line(v_id| |v_name) ; 9 end;10 /输 入 a 的 值 : 1原 值 7: execute immediate v_string into v_id , v_na新

35、值 7: execute immediate v_string into v_id , v_na1 testPL/SQL 过 程 已 成 功 完 成 。=下面加上一些其他变量的使用方法= eg001（ 2. 输入 zym 的值: 计算机 3. 原值 1: select xh,xm from system.xs where zym= /*替换变量可以使用WHERE 子句；ORDER BY 子句；列表达式；表名；整个 SELECT语句*/ 53. 输入 cj 的值: 90 54. 原值 1: select * from system.xs_xkb where cj= 62. 输入 name 的值:

36、 xm 63. 输入 column 的值: cj 64. 原值 1: select xs.xh,输 入 zym 的 值 : 计 算 机原 值 1: select xh,xm from system.xs where zym= 6. 7. XH XM XB CSSJ ZXF 8. - - - - - 9. 061202 王林 男 29-10 月-85 40 10. 061210 李红庆 女 01-5 月 -85 44 11. 061201 王敏 男 10-6 月 -84 42 12. 061203 王玉民 男 26-3 月 -86 42 13. 061204 马林林 女 10-2 月 -84 4

37、2 14. 061206 李计 女 20-9 月 -85 42 15. 061216 孙祥欣 女 09-3 月 -84 42 16. 061218 孙研 男 09-10 月-86 42 17. 061220 吴薇华 女 18-3 月 -86 42 18. 061221 刘燕敏 女 12-11 月-85 42 19. 061241 罗林琳 女 30-1 月 -86 50 20. 21. 已选择 11 行。 SQL define specialty=通 信 工 程SQL define specialtyDEFINE SPECIALTY = “通 信 工 程 “ (CHAR)SQL select x

38、h,xm,xb,cssj,zxf from system.xs2 where zym=XH XM XB CSSJ ZXF- - - - -061202 王 林 男 29-10月 -85 40061210 李 红 庆 女 01-5月 -85 44061201 王 敏 男 10-6月 -84 42061203 王 玉 民 男 26-3月 -86 42061204 马 林 林 女 10-2月 -84 42061206 李 计 女 20-9月 -85 42061216 孙 祥 欣 女 09-3月 -84 42061218 孙 研 男 09-10月 -86 42061220 吴 薇 华 女 18-3月

39、-86 42061221 刘 燕 敏 女 12-11月 -85 42eg004ACCEPT variabledatatypeNUMBER|CHAR|DATEFORMAT formatPROMPT textHIDE/*variable：指定接收值的变量。该名称的变量不存在，那么 SQL 重建该变量；datatype:变量数据类型，默认为 CHAR*/c-sharp view plaincopyprint?1. SQL accept num prompt请输入课程号: 2. 请输入课程号:101 3. SQL set verify on 4. SQL 5. 1 select xh,kcm,cj f

40、rom system.xs_kc,system.kc 6. 2 where xs_kc.kch=kc.kch and kc.kch=&num 7. 3* order by cj 8. SQL / 9. 原值 2: where xs_kc.kch=kc.kch and kc.kch=&num 10. 新值 2: where xs_kc.kch=kc.kch and kc.kch=101 11. 12. XH KCM CJ 13. - - - 14. 061103 计算机基础 62 15. 061106 计算机基础 65 16. 061218 计算机基础 70 17. 061210 计算机基础 76 18. 061221 计算机基础 79 19. 061101 计算机基础 80 20. 061216 计算机基础 81 21. 061220 计算机基础 82 22. 061241 计算机基础 90 23. 061104 计算机基础 90 24. 061111 计算机基础 91 25. 26. XH KCM CJ 27. - - - 28. 061204 计算机基础 91 29. 061110 计算机基础 95 30. 31. 已选择 13 行。 32. 33. SQL