昨天在学习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.
运行成功！问题解决。