先创建表:
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