关于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。
后来在*看到了这个,原文链接如下:
https://*.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.
运行成功!问题解决。
上一篇: 杠精