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=’CANDYWHERE 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;

这里写图片描述

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

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页