【SQL】绑定变量bind variables

最新推荐文章于 2023-07-09 18:56:09 发布

Aluphami

最新推荐文章于 2023-07-09 18:56:09 发布

阅读量2.2k

点赞数

分类专栏： SQL 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/u012778985/article/details/113390316

版权

SQL 专栏收录该内容

27 篇文章 1 订阅

订阅专栏

定义

绑定变量指的是在sql语句中的条件使用变量而不是常量

例如如下条sql

select empno,ename,sal from emp where empno=7788;
select empno,ename,sal from emp where empno=7900;
select empno,ename,sal from emp where empno=7844;

对于oracle来说这三条sql完全不同，在执行的时候都需要硬解析。因为oracle会根据sql的文本来计算在内存里的哈希值(hash)，虽然这些sql只有最后几个字符不一样，但是计算出的hash值有天差地别，如果将sql改成如下

select empno,ename,sal from emp where empno=：i;

用&i来取代后面的值，这样的好处是在执行sql的时候只会对带有变量的sql进行一次硬解析(hard parse)之后只进行软解析(soft prase)。仅三条sql可能不会有什么变化，但是数量级如果是十万或者百万，这样的话节省的消耗可想而之。

硬解析

测试

查看目前数据库解析统计

SQL> select a.*,b.name from v$sesstat a , v$statname b where a.statistic#=b.statistic# and a.sid=(select distinct sid from v$mystat) and b.name like '%parse%';

       SID STATISTIC#      VALUE     CON_ID NAME
---------- ---------- ---------- ---------- ------------------------------
        52        466          0          0 ADG parselock X get attempts
        52        467          0          0 ADG parselock X get successes
        52       1699          8          0 parse time cpu
        52       1700          8          0 parse time elapsed
        52       1701         53          0 parse count (total)
        52       1702         16          0 parse count (hard)
        52       1703          0          0 parse count (failures)
        52       1704          0          0 parse count (describe)

8 rows selected.

执行sql

SQL> select empno,ename,sal from emp where empno=7788;

EMPNO ENAME        SAL
----- ---------- -----
7788 SCOTT       3000

SQL> select empno,ename,sal from emp where empno=7900;

EMPNO ENAME        SAL
----- ---------- -----
7900 JAMES        950

SQL> select empno,ename,sal from emp where empno=7844;

EMPNO ENAME        SAL
----- ---------- -----
7844 TURNER      1500

在v$sql中查看执行情况

SQL> select SQL_TEXT,SQL_ID,HASH_VALUE,PARSE_CALLS,EXECUTIONS,LOADS from v$sql where sql_text like 'select empno%';

SQL_TEXT                                           SQL_ID          HASH_VALUE PARSE_CALLS EXECUTIONS      LOADS
-------------------------------------------------- --------------- ---------- ----------- ---------- ----------
select empno,ename,sal from emp where empno=7788   c2whtkr7d6yym   3469966291           1          1          1
select empno,ename,sal from emp where empno=7900   bawvr8ndmgkdh    456640944           1          1          1
select empno,ename,sal from emp where empno=7844   7zkd8rxsk7t78   1898177768           1          1          1

果然，这三条sql被当作不同的语句被分别执行硬解析其中LOADS为硬解析次数

软解析

如果再分别执行一次

SQL> select SQL_TEXT,SQL_ID,HASH_VALUE,PARSE_CALLS,EXECUTIONS,LOADS from v$sql where sql_text like 'select empno%';

SQL_TEXT                                           SQL_ID          HASH_VALUE PARSE_CALLS EXECUTIONS      LOADS
-------------------------------------------------- --------------- ---------- ----------- ---------- ----------
select empno,ename,sal from emp where empno=7788   c2whtkr7d6yym   3469966291           2          2          1
select empno,ename,sal from emp where empno=7900   bawvr8ndmgkdh    456640944           2          2          1
select empno,ename,sal from emp where empno=7844   7zkd8rxsk7t78   1898177768           2          2          1

再看硬解析统计

SQL> select a.*,b.name from v$sesstat a , v$statname b where a.statistic#=b.statistic# and a.sid=(select distinct sid from v$mystat) and b.name like '%parse%';

       SID STATISTIC#      VALUE     CON_ID NAME
---------- ---------- ---------- ---------- ------------------------------
        45        466          0          0 ADG parselock X get attempts
        45        467          0          0 ADG parselock X get successes
        45       1699         12          0 parse time cpu
        45       1700         12          0 parse time elapsed
        45       1701         74          0 parse count (total)
        45       1702         23          0 parse count (hard)
        45       1703          0          0 parse count (failures)
        45       1704          0          0 parse count (describe)

再次执行parse_calls和executions各自增加1，一次oracle没有再执行硬解析

绑定变量的使用

使用绑定变量可以减少相同或类似语句的重复编译，减少不必要的I/O，提高效率

使用var声明变量

var i number;
SQL> select empno,ename,sal from emp where empno=:i;

no rows selected

因为变量没有值因此什么都没有查出来

给变量赋值

SQL> exec :i :=7369

PL/SQL procedure successfully completed.

SQL> select empno,ename,sal from emp where empno=:i;

     EMPNO ENAME                                 SAL
---------- ------------------------------ ----------
      7369 SMITH                                 800

SQL> exec :i :=7499

PL/SQL procedure successfully completed.

SQL> select empno,ename,sal from emp where empno=:i;

     EMPNO ENAME                                 SAL
---------- ------------------------------ ----------
      7499 ALLEN                                1600

SQL> exec :i :=7521

PL/SQL procedure successfully completed.

SQL> select empno,ename,sal from emp where empno=:i;

     EMPNO ENAME                                 SAL
---------- ------------------------------ ----------
      7521 WARD                                 1250

那解析情况是什么样的呢

SQL_TEXT                                           SQL_ID          HASH_VALUE PARSE_CALLS EXECUTIONS      LOADS
-------------------------------------------------- --------------- ---------- ----------- ---------- ----------
select empno,ename,sal from emp where empno=:i     0dahk7wvkwqs9    925784841           4          4          1
select empno,ename,sal from emp where empno=7788   c2whtkr7d6yym   3469966291           2          2          1
select empno,ename,sal from emp where empno=7900   bawvr8ndmgkdh    456640944           2          2          1
select empno,ename,sal from emp where empno=7844   7zkd8rxsk7t78   1898177768           2          2          1

执行次数4，解析一次

SQL> select a.*,b.name from v$sesstat a , v$statname b where a.statistic#=b.statistic# and a.sid=(select distinct sid from v$mystat) and b.name like '%parse%';

       SID STATISTIC#      VALUE     CON_ID NAME
---------- ---------- ---------- ---------- ------------------------------
        45        466          0          0 ADG parselock X get attempts
        45        467          0          0 ADG parselock X get successes
        45       1699         14          0 parse time cpu
        45       1700         13          0 parse time elapsed
        45       1701         80          0 parse count (total)
        45       1702         25          0 parse count (hard)
        45       1703          0          0 parse count (failures)
        45       1704          0          0 parse count (describe)

替换变量

&每次都需要重新定义变量

SQL> conn scott/tiger;
Connected.
SQL> select &a,&b from emp where &a=7934;
Enter value for a: empno
Enter value for b: ename
Enter value for a: empno
old   1: select &a,&b from emp where &a=7934
new   1: select empno,ename from emp where empno=7934

     EMPNO ENAME
---------- ------------------------------
      7934 MILLER

一条sql内如果一个变量需要输入两次的时候可以使用

SQL> select &a,&b from emp where &a=7934;
Enter value for a: deptno
Enter value for b: ename
Enter value for a: empno
old   1: select &a,&b from emp where &a=7934
new   1: select deptno,ename from emp where empno=7934

    DEPTNO ENAME
---------- ------------------------------
        10 MILLER

这个例子可以看出&a取了不同的列名

&&在同一会话中仅需赋一次变量

SQL> select &&a,&b from emp where &a=7934;
Enter value for a: empno
Enter value for b: ename
old   1: select &&a,&b from emp where &a=7934
new   1: select empno,ename from emp where empno=7934

     EMPNO ENAME
---------- ------------------------------
      7934 MILLER

可以看出即便是后面是&a，由于前面的&&a已经给a赋值，因此第二个&a并没有询问

define

它的作用和&&a差不多，只是不需要询问赋什么值了

SQL> define c=empno
SQL> define b=ename
SQL> select &c,&b from emp where &c=7934;
old   1: select &c,&b from emp where &c=7934
new   1: select empno,ename from emp where empno=7934

     EMPNO ENAME
---------- ------------------------------
      7934 MILLER

可以查看define的定义

SQL> define
DEFINE _DATE           = "2021-01-29 15:06:15" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE      = "1202000100" (CHAR)
DEFINE A               = "empno" (CHAR)
DEFINE C               = "empno" (CHAR)
DEFINE B               = "ename" (CHAR)

如果想取消赋值可以用undefine

SQL> undefine a
SQL> undefine b
SQL> undefine c
SQL> define
DEFINE _DATE           = "2021-01-29 15:07:24" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SCOTT" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE      = "1202000100" (CHAR)

cursor_sharing参数

如果在sql或者前台代码中没有使用绑定变量，可以用该参数在数据库层面上进行处理，但是不推荐

该参数有个值

exact

sql完全一样的时候，不重新编译

similar

sql部分一样时，数据库自动时间变量绑定

fcorce

sql不一样时，强制使用绑定变量

修改该参数(也可使用(alter system修改，但是不推荐)

SQL> alter session set cursor_sharing=similar;

Session altered.

然后在用替换变量

SQL> select empno,ename,sal from emp where empno=&h;
Enter value for h: 7521
old   1: select empno,ename,sal from emp where empno=&h
new   1: select empno,ename,sal from emp where empno=7521

     EMPNO ENAME                                 SAL
---------- ------------------------------ ----------
      7521 WARD                                 1250

SQL> select empno,ename,sal from emp where empno=&h;
Enter value for h: 7698
old   1: select empno,ename,sal from emp where empno=&h
new   1: select empno,ename,sal from emp where empno=7698

     EMPNO ENAME                                 SAL
---------- ------------------------------ ----------
      7698 BLAKE                                2850

查看解析情况

SQL_TEXT                                               SQL_ID          HASH_VALUE PARSE_CALLS EXECUTIONS      LOADS
------------------------------------------------------ --------------- ---------- ----------- ---------- ----------
select empno,ename from emp where empno=7934           5y4gcq87d4yvj    248675185           4          4          2
select empno,ename,sal from emp where empno=:"SYS_B_0" 08yqzr3jhx383   3809381635           2          2          1
select empno,ename from emp where empno=7394           c7w21tmt359wv   4063405979           2          2          1

可见oracle自动对变量部分进行了绑定，两次查询仅用一次硬解析

Aluphami

关注

0
点赞
踩
13

收藏

觉得还不错? 一键收藏
打赏
0
评论
【SQL】绑定变量bind variables

目录定义硬解析软解析再看硬解析统计绑定变量的使用使用var声明变量替换变量&每次都需要重新定义变量&&在同一会话中仅需赋一次变量definecursor_sharing参数exactsimilarfcorce定义绑定变量指的是在sql语句中的条件使用变量而不是常量例如如下条sql select empno,ename,sal from emp where empno=7788;select empn...
复制链接

扫一扫