Oracle存储过程中异常处理测试

总结如下:
1、如果SELECT … INTO …语句取不到数据,将触发NO_DATA_FOUND异常,判断SQL%NOTFOUND的语句不生效
2、EXCEPTION语句必须和BEGIN/END块一起使用

测试相关的表:

create table T_ERROR_TABLE
(
  TABLE_NAME VARCHAR2(30),
  ID         NUMBER
);

create table T_TABLE_HIST
(
  TABLE_NAME VARCHAR2(30),
  NUM_ROWS   NUMBER,
  UPD_DATE   DATE
)

测试的存储过程:

CREATE or REPLACE PROCEDURE sp_test_exception IS
    v_sql VARCHAR2(2000);
    v_count t_table_hist.num_rows%TYPE;
    v_hist t_table_hist%ROWTYPE;
    v_table VARCHAR2(30);
    v_id t_error_table.id%TYPE;
    v_rowid ROWID;
BEGIN
-- dbms_output默认输出2000 bytes,设置为null不限制
    dbms_output.enable(null);

    FOR j IN 1..10
    LOOP
-- 用单引号做转义字段
      v_sql := 'SELECT ''1'' FROM t1';
      BEGIN
          EXECUTE IMMEDIATE v_sql INTO v_count;
          dbms_output.put_line(v_count);
      EXCEPTION
          WHEN OTHERS THEN
              dbms_output.put_line(SQLERRM);
      END;
    END LOOP;

--使用RETURNING语句返回插入或修改的记录
    v_table := 'TEST-001';
    INSERT INTO t_error_table
        (id, table_name)
    VALUES(
        seq_error_table.nextval, v_table
    )
    RETURNING ROWID, ID
    INTO v_rowid, v_id;
    DBMS_OUTPUT.put_line('插入的记录:' || v_rowid || ' -- ' || v_id);
    DBMS_OUTPUT.put_line('插入记录数:' || SQL%ROWCOUNT);
    COMMIT;

--UPDATE/DELETE语句找不到符合条件的记录,SQL%FOUND为FALSE
    v_table := 'TEST-001-DEL';
    DELETE t_error_table
    WHERE table_name = v_table
    RETURNING id, table_name
    INTO v_id, v_table;
    IF SQL%NOTFOUND THEN
       DBMS_OUTPUT.put_line(v_table || ' does not exist...');
    END IF;
    DBMS_OUTPUT.put_line('删除记录数:' || SQL%ROWCOUNT); 

--使用 SQL$ROWCOUNT获取更新或删除的记录数
    v_table := 'TEST-001';
    DELETE t_error_table
    WHERE table_name = v_table
    RETURNING id, table_name
    INTO v_id, v_table;
    DBMS_OUTPUT.put_line('删除的记录:' || v_id || ' -- ' || v_table);
    DBMS_OUTPUT.put_line('删除记录数:' || SQL%ROWCOUNT);
    COMMIT;

--如果SELECT ... INTO ...语句取不到数据,将触发NO_DATA_FOUND异常
--判断SQL%NOTFOUND的语句不生效
    BEGIN
      SELECT NUM_ROWS INTO v_count
      FROM t_table_hist
      WHERE table_name = 'abc';
    EXCEPTION
        WHEN no_data_found THEN
            DBMS_OUTPUT.put_line('not found abc in t_table_hist');
    END;

    BEGIN
      v_table := 'ADR6_HIS';
      SELECT *
      INTO v_hist
      FROM t_table_hist
      WHERE TABLE_NAME = v_table;
      DBMS_OUTPUT.put_line(v_hist.table_name || ' processed...');
    EXCEPTION
        WHEN no_data_found THEN
            DBMS_OUTPUT.put_line('找不到表:' || v_table);
        WHEN too_many_rows THEN
            DBMS_OUTPUT.put_line('找到多个匹配记录:' || v_table);
    END;

    BEGIN
      FOR v_row IN (
          SELECT *
          FROM t_table_hist
  --用escape语句制定通配符的转义字符
          WHERE table_name LIKE '%\_HIS' ESCAPE '\' AND
              rownum <= 10
      ) LOOP
          DBMS_OUTPUT.put_line(v_row.table_name);
      END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
             DBMS_OUTPUT.put_line(SQLERRM);
    END;
EXCEPTION
--使用 SQLCODE/SQLERRM获取异常和异常信息
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('Global exception: ' || SQLCODE || '--' || substr(SQLERRM,1,200));
END SP_TEST_EXCEPTION;

Comments are closed.