20180417PLSQL中sql语句格式化与注解问题

最新推荐文章于 2024-04-10 15:44:45 发布

cuixun7780

最新推荐文章于 2024-04-10 15:44:45 发布

阅读量205

点赞数

[20180417]PLSQL中sql语句格式化与注解问题.txt

--//以前的测试:http://blog.itpub.net/267265/viewspace-748190/
--//今天发现一些注解也被过滤掉,还是通过例子来说明问题.

1.测试环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> select * from dept ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

2.建立测试例子:
create or replace procedure test_bind
as
v_deptno number;
v_dname   varchar2(14);
v_loc     varchar2(13);
v_deptno1 number;
v_dname1   varchar2(14);
v_loc1     varchar2(13);
cursor c_dept is select deptno,dname from dept order by deptno;
begin
open c_dept;
loop
fetch c_dept into v_deptno,v_dname;
exit when c_dept%NOTFOUND;
select loc into v_loc from dept where deptno=v_deptno and dname=v_dname;

Select loc into v_loc from dept
where deptno=v_deptno
and dname=v_dname;

v_deptno1 := v_deptno;
v_dname1 := v_dname;

Select loc into v_loc from dept where deptno=v_deptno1
and dname=v_dname;

Select loc
into v_loc from dept where deptno=v_deptno
and dname=v_dname1;

Select
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select
loc
--this is laji
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select --this is a test!!
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select /* this is a test!! */
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select --+rule
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select /*+ this is a test!! */
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

end loop;
end;
/

3.测试:
SCOTT@book> @ &r/10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.

SCOTT@book> exec test_bind
PL/SQL procedure successfully completed.

SCOTT@book> @ &r/10046off
Session altered.

4.使用tkprof:
$ tkprof /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_16419.trc
output = aa
TKPROF: Release 11.2.0.4.0 - Development on Tue Apr 17 10:41:12 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

--//分析输出文件aa.prf:
********************************************************************************

SQL ID: fq1jkwcmsx57d Plan Hash: 2852011669

SELECT LOC
FROM
DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        8      0.00       0.00          0          0          0           0
Execute     32      0.00       0.00          0          0          0           0
Fetch       32      0.00       0.00          0         64          0          32
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       72      0.00       0.00          0         64          0          32

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 83 (recursive depth: 1)
Number of plan statistics captured: 8

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=15 us cost=1 size=20 card=1)
1 1 1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=9 us cost=0 size=0 card=1)(object id 87107)

********************************************************************************
--//你可以发现分析8次,而执行了32次(循环4次).也就是每次循环执行8次这条语句.
--//很明显你可以发现plsql格式化了sql语句(变成了大写),并且绑定变量被换成了:B1 :B2.
--//一些注解像
--this is a test!!
--this is laji
/* this is a test!! */
--//也被过滤掉.

SELECT --+rule
LOC FROM DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0          8          0           4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        9      0.00       0.00          0          8          0           4

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 83 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=27 us)
1 1 1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=7 us)(object id 87107)

********************************************************************************

SQL ID: 88p9k1j3c3c71 Plan Hash: 2852011669

SELECT /*+ this is a test!! */ LOC
FROM
DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 83 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=16 us cost=1 size=20 card=1)
1 1 1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=7 us cost=0 size=0 card=1)(object id 87107)

********************************************************************************

--//再看看给脚本加注解的情况:
Select
loc
--this is laji
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select --this is a test!!
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select /* this is a test!! */
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

--//这3条语句实际上注解被过滤了.变成了
SELECT LOC
FROM
DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

--//而2种特殊的注解被保留下来,就是开头有加号的注解
SELECT --+rule
LOC FROM DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

SELECT /*+ this is a test!! */ LOC
FROM
DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

--//并且这个加号必须写成这样着/*+ 或者 --+ 才不会过滤.也就是这样写里面的提示才有效.
--//注:我补充测试如下写的情况,修改如下:
Select -- +rule
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

********************************************************************************

SQL ID: fq1jkwcmsx57d Plan Hash: 2852011669

SELECT LOC
FROM
DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        9      0.00       0.00          0          0          0           0
Execute     36      0.00       0.00          0          0          0           0
Fetch       36      0.00       0.00          0         72          0          36
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total       81      0.00       0.00          0         72          0          36

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 83 (recursive depth: 1)
Number of plan statistics captured: 9

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=14 us cost=1 size=20 card=1)
1 1 1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=8 us cost=0 size=0 card=1)(object id 87107)

********************************************************************************

--//这样sql_id=fq1jkwcmsx57d的分析次数变成9次.大家可以自行测试.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2153007/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2153007/