關於all_procedures的問題分析

今天快下班的時候有一個同事問我一個儲存過程的許可權是否做過修改。我簡單看了下發現這個儲存過程已經是很久以前建立的了，一直沒有做過修改，所以就反饋給他了。但是他過了一會問我說，他通過資料字典檢視，沒有找到這個儲存過程，想讓我幫忙看看是不是因為許可權的原因，因為他們呼叫這個儲存過程有一些問題。
同事發過來的語句類似這樣的形式：
SQL> select *from all_procedures where procedure_name like 'insert%cn';
no rows selected
我一看這個語句肯定是查不出結果啊。因為在資料庫裡面顯示都是大寫的。所以改為了大寫繼續檢視，奇怪的是竟然顯示0條記錄。
為了更精確，我直接輸入了儲存過程的完整名字。但是奇怪的是竟然還是沒有任何結果。
SQL> select *from all_procedures where procedure_name='INSERT_BILL_CN';
no rows selected
確認了環境之後，我感覺這個問題一定哪個細節之處存在一些差別。
於是我使用了dba_procedures，但是奇怪的是結果依舊是返回0行
SQL> select *from dba_procedures where procedure_name='INSERT_BILL_CN';
no rows selected
如果對使用者的許可權存在疑問，我可以確定的是我使用的可是超級DBA SYS
SQL> show user
USER is "SYS"
那是不是儲存過程確實不存在呢,使用DESC可以迅速驗證我最開始的檢查是沒有問題的。
SQL> desc test.INSERT_BILL_CN
PROCEDURE test.INSERT_BILL_CN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SDATE                          VARCHAR2                IN
 EDATE                          VARCHAR2                IN
 FLAG                           NUMBER                  IN     DEFAULT
到這裡確實奇怪了，於是我這樣來推理，輸出使用者下所有的儲存過程
SQL> select procedure_name from all_procedures where owner='TEST';

PROCEDURE_NAME
------------------------------

。。。
但是奇怪的是儲存過程顯示都是為空，這可讓我有些疑惑了。我多輸出了一個object_name欄位。
SQL>SELECT OBJECT_NAME,PROCEDURE_NAME,OBJECT_ID,OBJECT_TYPE FROM DBA_PROCEDURES WHERE OWNER='TLBB'
OBJECT_NAME                    PROCEDURE_  OBJECT_ID OBJECT_TYPE
------------------------------ ---------- ---------- -------------
TEST_TURNCARD_STAT                               73993 PROCEDURE
INSERT_CONSUME_INFO_CN1                        72719 PROCEDURE
INSERT_BILL_CN1                         72600 PROCEDURE
INSERT_CN_TEST_TEST                     71176 PROCEDURE
...
這個時候我算是看明白了，procedure_name為空，但是object_name顯示的結果是我們期望之中的procedure_name
為什麼這麼蹊蹺呢。我開啟文件檢視是否對於欄位的理解存在一些偏差。

從欄位來看，感覺還是存在著偏差,all_procedures是會包括函式，儲存過程，包的資訊，這個檢視的定義還是不大清晰啊，尤其是欄位的含義，讓人看了有二義性。
那什麼時候procedure_name有值呢，可以看看這個簡單的例子。
我們通過all_procedures看看dbms_stats的結構
SQL>SELECT OBJECT_NAME,PROCEDURE_NAME,OBJECT_ID,OBJECT_TYPE FROM DBA_PROCEDURES WHERE PROCEDURE_NAME IS NOT NULL AND OBJECT_NAME LIKE 'DBMS_STATS' AND ROWNUM<10;
OBJECT_NAME                    PROCEDURE_NAME                  OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------
DBMS_STATS                     PREPARE_COLUMN_VALUES                4354 PACKAGE
DBMS_STATS                     PREPARE_COLUMN_VALUES                4354 PACKAGE
DBMS_STATS                     PREPARE_COLUMN_VALUES                4354 PACKAGE
DBMS_STATS                     PREPARE_COLUMN_VALUES                4354 PACKAGE
DBMS_STATS                     PREPARE_COLUMN_VALUES                4354 PACKAGE
DBMS_STATS                     PREPARE_COLUMN_VALUES                4354 PACKAGE
DBMS_STATS                     PREPARE_COLUMN_VALUES_NVARCHAR       4354 PACKAGE
可以看到這個時候儲存過程就有值了。這樣來理解這個檢視就會明白多了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2122085/，如需轉載，請註明出處，否則將追究法律責任。