关于oracle PL/SQL存储过程 PLS-00905 object is invalid,statement ignored问题的解决,存储过程oracle
昨天在学习oracle存储过程的时候,写了一个存储过程的demo,语句是这样的:
CREATE OR REPLACE PROCEDURE RAISESALARY(PNAME IN VARCHAR2(20))ASpsssal 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 tableTESTDELETE
(
TESTIDNUMBER,
TESTNAMEVARCHAR2(20)
)
将存储过程调用,出现结果如下:
Connected to:
OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProductionWith the Partitioning, OLAP, Data Mining and RealApplication 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 withcompilation errors.SET SERVEROUTPUT ON;BEGINRAISESALARY('name2091');COMMIT;END;
SQL> 2 3 4 5 /RAISESALARY('name2091');*ERROR at line2:
ORA-06550: line 2, column 5:
PLS-00905: object TEST.RAISESALARY isinvalid
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 /
Procedurecreated.BEGINRAISESALARY('name2091');3 COMMIT;4 END;5 /The original salary2091 After the raise3091
PL/SQL proceduresuccessfully completed.
SQL>
改完之后明确看到已经没有warning了,出现的是 Procedure created.
运行成功!问题解决。
http://www.dengb.com/oracle/1378048.htmlwww.dengb.comtruehttp://www.dengb.com/oracle/1378048.htmlTechArticle关于oracle PL/SQL存储过程 PLS-00905 object is invalid,statement ignored问题的解决,存储过程oracle 昨天在学习oracle存储过程的时候,写了一个存储过...