關於oracle PL/SQL存儲過程 PLS-00905 object is invalid，statement ignored問題的解決

昨天在學習oracle存儲過程的時候，寫了一個存儲過程的demo，語句是這樣的：

CREATE OR REPLACE PROCEDURE RAISESALARY(PNAME IN VARCHAR2(20))
AS
    psssal TESTDELETE.TESTID%TYPE;
BEGIN
    SELECT TESTID INTO psssal FROM TESTDELETE WHERE TESTNAME=PNAME;
    UPDATE TESTDELETE SET TESTID=(TESTID+10000) WHERE TESTNAME=PNAME;
    DBMS_OUTPUT.PUT_LINE('The original salary'||psssal||'    After the raise'||(psssal+1000));
end;
/

想法是通過表的varchar(20)類型欄位找到number類型欄位，然後更改number類型的欄位。表結構如下：

create table TESTDELETE
(
    TESTID   NUMBER,
    TESTNAME VARCHAR2(20)
)

將存儲過程調用，出現結果如下：

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CREATE OR REPLACE PROCEDURE RAISESALARY(PNAME IN VARCHAR2(20))
  2  AS
  3      psssal TESTDELETE.TESTID%TYPE;
  4  BEGIN
  5      SELECT TESTID INTO psssal FROM TESTDELETE WHERE TESTNAME=PNAME;
    UPDATE TESTDELETE SET TESTID=(TESTID+10000) WHERE TESTNAME=PNAME;
    DBMS_OUTPUT.PUT_LINE('The original salary'||psssal||'    After the raise'||(psssal+1000));
  8  end;
  9  /

Warning: Procedure created with compilation errors.

SET SERVEROUTPUT ON;
BEGIN
    RAISESALARY('name2091');
    COMMIT;
END;
SQL>   2    3    4    5  /
    RAISESALARY('name2091');
    *
ERROR at line 2:
ORA-06550: line 2, column 5:
PLS-00905: object TEST.RAISESALARY is invalid
ORA-06550: line 2, column 5:
PL/SQL: Statement ignored


SQL> 

出現錯誤 ：該存儲過程無效。

what？明明剛剛建立的存儲過程啊。然後我就翻到上面創建完之後的一句話，Warning: Procedure created with compilation errors. 翻譯過來應該是：創建的過程帶有編譯的錯誤。

也就是說創建存儲過程有錯誤，那麼OK，找找存儲過程看看哪錯了。

emmmm，回頭看了半天，就這麼幾行，死活沒看出到底哪出問題了，沒辦法，baidu，google。

後來在StackOverFlow看到了這個，原文鏈接如下：

https://stackoverflow.com/questions/48497140/oracle-sql-stored-procedure-object-invalid

樓主在裡面發現了這麼一句話：

You can’t give a size or precision restriction for the data type of a formal parameter to a function or procedure, so NUMBER(10,0) should just be NUMBER;

也就是說，你不能給函數和存儲過程的參數指定數據的大小或者精度。OK，回頭看看我這個參數，懂了，varchar2(20)是明顯的給參數的類型指定精度了。需要改成varchar2這種類型。

或者直接寫   表名.欄位%TYPE 也是可以的（親測） 。

更改完之後運行如下：

CREATE OR REPLACE PROCEDURE RAISESALARY(PNAME IN VARCHAR)
  2  AS
  3      psssal TESTDELETE.TESTID%TYPE;
  4  BEGIN
  5      SELECT TESTID INTO psssal FROM TESTDELETE WHERE TESTNAME=PNAME;
    UPDATE TESTDELETE SET TESTID=(TESTID+10000) WHERE TESTNAME=PNAME;
    DBMS_OUTPUT.PUT_LINE('The original salary'||psssal||'    After the raise'||(psssal+1000));
  8  end;
  9  /

Procedure created.

BEGIN
    RAISESALARY('name2091');
  3      COMMIT;
  4  END;
  5  /
The original salary2091    After the raise3091

PL/SQL procedure successfully completed.

SQL> 

改完之後明確看到已經沒有warning了，出現的是   Procedure created.

運行成功！問題解決。