oracle绑定变量使⽤⽅法总结
在Oracle中，对于⼀个提交的sql语句，存在两种可选的解析过程，硬解析和软解析。
⼀个硬解析需要经解析，制定执⾏路径，优化访问计划等步骤。硬解析不仅仅会耗费⼤量的cpu，更重要的是会占据重要的闩(latch)资源。唯⼀使得oracle能够重复利⽤执⾏计划的⽅法就是采⽤绑定变量。绑定变量的实质就是使⽤变量来代替sql语句中的常量。绑定变量能够使得每次提交的sql语句都完全⼀样。
1. sqlplus中使⽤variable来定义
1. SQL> select * from t where id=1;
2.
3. ID NAME
4. ---------- --------------------------------
5. 1 test
6.
7. SQL> select * from t where id=2;
8.
9. ID NAME
10. ---------- --------------------------------
11. 2 test2
12.
13. SQL> variable i number;
14. SQL> exec :i :=1;
15.
16. PL/SQL procedure successfully completed.
17.
18. SQL> select * from t where id=:i;
19.
20. ID NAME
21. ---------- --------------------------------
22. 1 test
23.
24. SQL> exec :i :=2;
oracle游标的使用25.
26. PL/SQL procedure successfully completed.
27.
28. SQL> select * from t where id=:i;
29.
30. ID NAME
31. ---------- --------------------------------
32. 2 test2
33. SQL> select sql_text,parse_calls from v$sql where sql_text like'select * from t where id=%';
34.
35. SQL_TEXT
36. --------------------------------------------------------------------------------
37. PARSE_CALLS
38. -----------
39. select * from t where id=2
40. 1
41.
42. select * from t where id=1
43. 1
44.
45. select * from t where id=:i --可以看到这条sql被调⽤了两次，这两次的使⽤就包括了⼀次soft parse
2. (误区)sqlplus中通过define定义的并不是变量，⽽只是字符常量，define定义之后，再通过&或&&引⽤的时候就不需要再输⼊了，oracle在执⾏的时候回⾃动⽤定义的值进⾏替换，仅此⽽已，并不是绑定变量。
1. SQL> define a=1
2. SQL> define
3. DEFINE _DATE = "30-OCT-16" (CHAR)
4. DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR)
5. DEFINE _USER = "SYS" (CHAR)
6. DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
7. DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
8. DEFINE _EDITOR = "ed" (CHAR)
9. DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
10. With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
11. DEFINE _O_RELEASE = "1102000400" (CHAR)
12. DEFINE A = "1" (CHAR)
13. SQL> select * from t where id=&a;
14. old 1: select * from t where id=&a
15. new 1: select * from t where id=1
16.
17. ID NAME
18. ---------- --------------------------------
19. 1 test
&&和&⼀样的功能，不过&&替代过⼀次之后就不需要再输⼊了，可以多次替代。
1. SQL> select * from t where id=&b;
2. Enter value for b: 2
3. old 1: select * from t where id=&b
4. new 1: select * from t where id=2
5.
6. ID NAME
7. ---------- --------------------------------
8. 2 test2
9.
10. SQL> select * from t where id=&b;
11. Enter value for b: 2
12. old 1: select * from t where id=&b
13. new 1: select * from t where id=2
14.
15. ID NAME
16. ---------- --------------------------------
17. 2 test2
18.
19. SQL> select * from t where id=&&b;
20. Enter value for b: 2
21. old 1: select * from t where id=&&b
22. new 1: select * from t where id=2
23.
24. ID NAME
25. ---------- --------------------------------
26. 2 test2
27.
28. SQL> select * from t where id=&&b;
29. old 1: select * from t where id=&&b
30. new 1: select * from t where id=2
31.
32. ID NAME
33. ---------- --------------------------------
34. 2 test2
另外，如果define定义的是字符类型，在引⽤时需要加上单引号
1. SQL> select * from t where name=&c;
2. old 1: select * from t where name=&c
3. new 1: select * from t where name=test
4. select * from t where name=test
5. *
6. ERROR at line 1:
7. ORA-00904: "TEST": invalid identifier
8.
9.
10. SQL> select * from t where name='&c';
11. old 1: select * from t where name='&c'
12. new 1: select * from t where name='test'
13.
14. ID NAME
15. ---------- --------------------------------
16. 1 test
可以看到，在执⾏sql的时候oracle⾃动进⾏了替换
[sql]
1. SQL> select sql_text from v$sql where sql_text like'select * from t where name=%';
2.
3. SQL_TEXT
4. --------------------------------------------------------------------------------
5. select * from t where name='test'
3. oracle在解析sql时会把plsql中定义的变量转为绑定变量
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论