oracle pl sql case when case_not_found

先创建表：

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;

插入一些数据。

创建case when的存储过程：

CREATE OR REPLACE PROCEDURE PRO_CASE_WHEN(USER_ID IN INTEGER) AS
V_AGE TB_USER.USER_AGE%TYPE;
V_NAME TB_USER.USER_NAME%TYPE;
BEGIN
  SELECT USER_NAME, USER_AGE INTO V_NAME, V_AGE FROM TB_USER WHERE ID=USER_ID;
  DBMS_OUTPUT.put_line(V_NAME || ', ' || V_AGE);
  CASE
    WHEN V_AGE < 18 THEN
      DBMS_OUTPUT.put_line(V_NAME || ' is a child.');
    WHEN V_AGE < 50 THEN
      DBMS_OUTPUT.put_line(V_NAME || ' is not a child.');
  END CASE;
  EXCEPTION
    WHEN CASE_NOT_FOUND THEN
      DBMS_OUTPUT.put_line('case not found');
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('ERROR CODE: ' || SQLCODE || CHR(10) || 'ERROR MSG:' || SQLERRM); 
END;

显示其中一条case_not_found的数据：

SQL> SELECT * FROM TB_USER WHERE ID=505;

        ID USER_NAME              USER_AGE
---------- -------------------- ----------
       505 FOR_REVERSE_80               80

SQL>

执行：

CALL PRO_CASE_WHEN(505);

输出：

FOR_REVERSE_80, 80
case not found

显示没有找到一条记录：

SQL> SELECT * FROM TB_USER WHERE ID=1;

未选定行

SQL>

执行：

CALL PRO_CASE_WHEN(1);

输出：

ERROR CODE: 100
ERROR MSG:ORA-01403: 未找到数据