oracle <value error>,oracle pl sql VALUE_ERROR

先创建表：

CREATE TABLE TB_USER

(

ID INTEGER PRIMARY KEY,

USER_NAME VARCHAR2(20) NOT NULL,

USER_AGE INTEGER NOT NULL

);

CREATE SEQUENCE SEQ_USER

INCREMENT BY 1

START WITH 1

NOMAXVALUE

NOCYCLE

CACHE 10;

CREATE OR REPLACE TRIGGER TR_USER BEFORE INSERT ON TB_USER FOR EACH ROW

BEGIN

SELECT SEQ_USER.NEXTVAL INTO :NEW.ID FROM DUAL;

END;

插入一些数据。

创建VALUE_ERROR的代码：

CREATE OR REPLACE PROCEDURE PRO_VALUE_ERROR(USER_ID IN INTEGER) AS

V_AGE INTEGER;

V_NAME VARCHAR2(5);

BEGIN

SELECT USER_NAME, USER_AGE INTO V_NAME, V_AGE FROM TB_USER WHERE ID=USER_ID;

EXCEPTION

WHEN VALUE_ERROR THEN

DBMS_OUTPUT.put_line('v_name is too short');

END;

会报VALUE_ERROR的记录：

SQL> SELECT * FROM TB_USER WHERE ID=505;

ID USER_NAME USER_AGE

---------- -------------------- ----------

505 FOR_REVERSE_80 80

上面的USER_NAME长度大于5，于是会报VALUE_ERROR。

执行：

CALL PRO_VALUE_ERROR(505);

输出：

v_name is too short