[20240607]PL/SQL中sql语句的注解.txt

[20240607]PL/SQL中sql语句的注解.txt

--//别人测试遇到的问题,重复测试说明问题.

1.环境:
SCOTT@test01p> @ ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

$ cat m2.sql
DECLARE
    l_count PLS_INTEGER;
BEGIN
    FOR i IN 1..&&1
    LOOP
        SELECT /*+ find_me */ COUNT(*) INTO L_COUNT FROM DEPT WHERE DEPTNO=10;
    END LOOP;
END;
/

2.测试1:
--//session 1:
SCOTT@test01p> set timing on
SCOTT@test01p> @ m2.sql 10000
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.47

--//session 2:
SYS@test> select sql_id ,sql_text,executions from v$sql where sql_text like '%find_me%' and executions>=1e4;
SQL_ID        SQL_TEXT                                                     EXECUTIONS
------------- ------------------------------------------------------------ ----------
b744uv7rtsf8t SELECT /*+ find_me */ COUNT(*) FROM DEPT WHERE DEPTNO=10          10000

3.继续:
$ cat m3.sql
DECLARE
    l_count PLS_INTEGER;
BEGIN
    FOR i IN 1..&&1
    LOOP
        SELECT /* find_me */ COUNT(*) INTO L_COUNT FROM DEPT WHERE DEPTNO=10;
    END LOOP;
END;
/
--//仅仅注解部分没有+加号.
--//退出会话刷新共享池:
SYS@test> alter system flush shared_pool;
System altered.

--//session 1:
SCOTT@test01p> @ m3.sql 30000
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.51

--//session 2:
SYS@test> select sql_id ,sql_text,executions from v$sql where sql_text like '%find_me%' and executions>=1e4;
no rows selected.
--//没有发现包含find_me字符串的sql语句.

--//而实际上匿名PL SQL做了特殊处理,删除注解部分,真正执行的是:
--// SELECT COUNT(*) FROM DEPT WHERE DEPTNO=10
SYS@test> select sql_id ,sql_text,executions from v$sql where  sql_id='9y1zzt1h1f3pz';
SQL_ID        SQL_TEXT                                                     EXECUTIONS
------------- ------------------------------------------------------------ ----------
9y1zzt1h1f3pz SELECT COUNT(*) FROM DEPT WHERE DEPTNO=10                         30000

--//简单验证:
--//session 1:
SCOTT@test01p> @ m3.sql 9999
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39

--//session 2:
SYS@test> select sql_id ,sql_text,executions from v$sql where  sql_id='9y1zzt1h1f3pz';
SQL_ID        SQL_TEXT                                                     EXECUTIONS
------------- ------------------------------------------------------------ ----------
9y1zzt1h1f3pz SELECT COUNT(*) FROM DEPT WHERE DEPTNO=10                         39999

--//如果注解部分没有+,PL/SQL对于里面的sql语句会作出特殊处理,取消注解,另外注意PL/SQL会格式化sql语句,这些在我以前blog提到.