[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提到.