昨天在學習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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsCREATE 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 invalidORA-06550: line 2, column 5:PL/SQL: Statement ignoredSQL>
出現錯誤 ：該存盤程序無效，
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 raise3091PL/SQL procedure successfully completed.SQL>
改完之后明確看到已經沒有warning了，出現的是 Procedure created.
運行成功！問題解決，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/34205.html
標籤：Oracle