oracle pl sql case when case_not_found

来源：互联网发布：2016年淘宝注册用户数编辑：程序博客网时间：2024/06/11 15:19

先创建表：

CREATE TABLE TB_USER(    ID INTEGER PRIMARY KEY,    USER_NAME VARCHAR2(20) NOT NULL,    USER_AGE INTEGER NOT NULL);CREATE SEQUENCE SEQ_USERINCREMENT BY 1START WITH 1NOMAXVALUENOCYCLECACHE 10;CREATE OR REPLACE TRIGGER TR_USER BEFORE INSERT ON TB_USER FOR EACH ROWBEGIN    SELECT SEQ_USER.NEXTVAL INTO :NEW.ID FROM DUAL;END;

插入一些数据。

创建case when的存储过程：

CREATE OR REPLACE PROCEDURE PRO_CASE_WHEN(USER_ID IN INTEGER) ASV_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               80SQL>

执行：

CALL PRO_CASE_WHEN(505);

输出：

FOR_REVERSE_80, 80case not found

显示没有找到一条记录：

SQL> SELECT * FROM TB_USER WHERE ID=1;未选定行SQL>

执行：

CALL PRO_CASE_WHEN(1);

输出：

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

0 0