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