存储过程oracle 冒号,ORACLE 存储过程的创建以及ORA-06502:PL/SQL:数字或值错误的解决方法...

创建四个存储过程，分别是简单不带参数的存储过程、带两个输入参数IN的存储过程、带一个输入参数IN，一个输出参数OUT的存储过程和带两个输入输出参数IN OUT的存储过程。

创建一张全局临时emp表

CREATE GLOBAL TEMPORARY TABLE emp( empno NUMBER(4), ename VARCHAR2(8) );

添加数据

INSERT INTO emp VALUES(6500,'Bomb');

一、创建一个简单的存储过程update_emp,该过程用于将emp表中empno为6500的员工的姓名修改为CANDY，如下：

CREATE PROCEDURE update_emp AS BEGIN UPDATE emp SET ename=’CANDY’ WHERE empno =6500;

END update_emp;

/

提示：[END update_emp 中的update_emp可以不用写，但写出来会增加可读性 ]

调用存储过程

EXECUTE update_emp;

查看结果，是否被修改

SELECT ename FROM emp WHERE empno=6500;

结果是：ename被修改了。

二、创建两个带IN参数的存储过程update_emp2，为该过程设置两个IN参数，分别用于接受用户提供的empno与ename值，如下：

CREATE PROCEDURE update_emp2 ( emp_num IN NUMBER , emp_name IN VARCHAR2) AS BEGIN UPDATE emp SET ename=emp_name WHERE empno =emp_num;

END update_emp2;

/

调用存储过程，需要设置两个参数，有两种调用方式，如下：

① 不指定参数名

EXECUTE update_emp2 (6500,’XIAOQI’);

② 指定参数名

EXEC update_emp2(emp_name=>'XIAOQI',emp_num=>6500);

查看结果，是否被修改

SELECT ename FROM emp WHERE empno=6500;

同样结果是：ename被修改了。

三、创建存储过程select_emp，为该过程设置一个IN参数和一个OUT参数，其中IN参数接受用户提供的empno值，然后在过程体中将该empno对应的ename值传递给OUT参数，如下：

CREATE PROCEDURE select_emp (emp_num IN NUMBER, emp_name OUT VARCHAR2 ) AS BEGIN SELECT ename INTO emp_name FROM emp WHERE empno =emp_num;

END select_emp;

/

调用存储过程时，如果需要显示该过程中OUT参数的返回值，还需要事先使用VARIABLE语句声明对应的变量接受返回值，并在调用过程时绑定该变量，形式如下：

VARIABLE emp_name VARCHAR2;//这个是错误的写法

EXEC select_emp (6500, :emp_name);

出现了一个BUG

通过查找资料，大家都说是类型长度不匹配的问题，最后认真查找错误，发现的确是没在定义变量时设置长度而导致出现的BUG。解决方法：

VARIABLE emp_name VARCHAR2(8);

注意：这个8是和一开始创建表的时候设置字符长度的必须是一样的，否则会报错。这个是VARCHAR2类型所要求的，NUMBER类型不用。

注意：在EXECUTE 语句中，调用绑定变量时候，需要在变量名前添加冒号(：)

要想查看变量emp_name 中的值，就需要用PRINT 命令，如下：

PRINT emp_name;

四、创建存储过程exchange_value，通过该过程交换两个变量中的值，过程创建如下：

CREATE PROCEDURE exchange_value (value1 IN OUT NUMBER,value2 IN OUT NUMBER) AS temp1 NUMBER;

temp2 NUMBER;

BEGIN

temp1 := value1;

temp2 := value2;

value1 := temp2;

value2 := temp1;

END exchange_value;

/

调用exchange_value 过程，调用前声明为IN OUT 参数赋值的变量，调用后使用SELECT 语句输出交换值后的结果，如下：

声明变量

VARIABLE value1 NUMBER;

VARIABLE value2 NUMBER;

为声明变量赋值(赋值的符号 := )

EXEC :value1 :=2009;

EXEC :value2 :=9002;

执行存储过程

EXEC exchange_value(:value1,:value2);

查看交换值后的结果

SELECT :value1,:value2 FROM dual;

注意：使用EXEC 命令可以为变量赋值，并且绑定变量名前也需要添加冒号(：)

五、删除存储过程，删除上面创建的存储过程exchange_value

DROP PROCEDURE exchange_value;

至此，简单的存储过程创建就讲完了。