lightdb22.3 plorasql 错误处理兼容

plorasql错误处理

错误处理使用的语法：

EXCEPTION
  WHEN ex_name_1 THEN statements_1                 -- Exception handler
  WHEN ex_name_2 OR ex_name_3 THEN statements_2  -- Exception handler
  WHEN OTHERS THEN statements_3                      -- Exception handler
END;

错误处理分类

预定义异常

预定义异常是 PLoraSQL 给定名称的内部定义异常。

内部定义异常

运行时系统隐式（自动）引发内部定义的异常。

自定义异常

可以在任何 PLoraSQL 匿名块、子程序或包的声明部分中声明自己的异常。

目前仅支持预定义中较为常用的异常处理

预定义异常

测试内容涉及到存储过程的包、函数、存储过程、匿名块。

一、INVALID_CURSOR

(1）显示游标

(1.1）close游标之后，fetch;

create table employees(id int, name varchar2(100), job varchar2(100));
insert into employees values (22091401, 'sunli', 'actress');
insert into employees values (22091402, 'dengchao', 'actor');
insert into employees values (22091403, 'lishizhen', 'doctor');
insert into employees values (22091404, 'zhangsanfeng', 'martial artist');
create or replace function invalid_cursor_f return int as
   cursor c1 is select * from  employees;
   emp_cur employees%rowtype;
begin
    open c1;
    loop
      fetch c1 into emp_cur;
      exit when c1%notfound;
      close c1;
    end loop;
    close c1;
EXCEPTION
  WHEN invalid_cursor THEN
    DBMS_OUTPUT.PUT_LINE('invalid cursor');
    return 1;
end;
/
select invalid_cursor_f();

(1.2) 未打开游标后，使用游标或者游标属性（%isopen除外）

create or replace function invalid_cursor_f return int as
  cursor c1 is select * from  employees;
  emp_cur employees%rowtype;
  ora_found boolean := false;
begin
  ora_found := c1%found;
EXCEPTION
 WHEN invalid_cursor THEN
   DBMS_OUTPUT.PUT_LINE('invalid cursor');
  return 1;
end;
/
select invalid_cursor_f();

(2）游标变量

(2.1）close游标之后，fetch;

create or replace function invalid_cursor_f return int as
   c1 sys_refcursor;
   emp_cur employees%rowtype;
begin
    open c1 for select * from  employees;
    loop
      fetch c1 into emp_cur;
      exit when c1%notfound;
      close c1;
    end loop;
    close c1;
EXCEPTION
  WHEN invalid_cursor THEN
    DBMS_OUTPUT.PUT_LINE('invalid cursor');
    return 1;
end;
/
select invalid_cursor_f();

(2.2) 未打开游标后，使用游标或者游标属性（%isopen除外）

create or replace function invalid_cursor_f return int as
  c1 sys_refcursor;
  emp_cur employees%rowtype;
  ora_isopen boolean := false;
begin
 ora_isopen := c1%isopen;
 return 1;
 exception 
   when INVALID_CURSOR then
     dbms_output.put_line('this is a normal error');
end;
/
select invalid_cursor_f();

二、CASE_NOT_FOUND

case语句时，如果在when子句中没有包含必须的条件分支，就会触发
分别为simple_case_statement和search_case_statment

simple_case_statemen

create or replace function case_not_found_f return int as
  grade CHAR(1);
BEGIN
  grade := 'G';
  
  CASE grade
    WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
  END CASE;
EXCEPTION
  WHEN CASE_NOT_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such grade');
  return 1;
END;
/

search_case_statment

create or replace function case_not_found_f return int as
  grade CHAR(1);
BEGIN
  grade := 'G';
  
  CASE
    WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
    WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
    WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
    WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
    WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
  END CASE;
EXCEPTION
  WHEN CASE_NOT_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such grade');
  return 1;
END;
/
select case_not_found_f();

三、DUP_VAL_ON_INDEX

在唯一索引所对应的列上插入重复的值时,就会触发

CREATE TABLE emp_name AS SELECT id, name FROM employees;
CREATE UNIQUE INDEX empname_ix ON emp_name (id);
create or replace procedure dup_val_on_index_p as
  emp_id        NUMBER(10);
  emp_name  VARCHAR2(100);
BEGIN
  SELECT id, name  INTO emp_id, emp_name FROM employees WHERE id = 22091401;
   INSERT INTO emp_name (id, name) VALUES (emp_id, emp_name);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Inserts were rolled back');
END;
/
call dup_val_on_index_p();

四、NOT_DATA_FOUND

当执行select into没有返回行，就会触发

create or replace function no_data_found_f return int as
  v_emp int;
begin
  select id into v_emp from employees where id = 22091501;
EXCEPTION
  WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE('No data found');
  return 1;
end;
/
select no_data_found_f();

五、TOO_MANY_ROWS

当执行select into语句时，如果返回超过了一行，则会触发

create or replace function too_many_rows_f return int as
  v_emp int;
begin
  select id into v_emp from employees;
  EXCEPTION
  WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE('No data found');
  WHEN too_many_rows THEN
    DBMS_OUTPUT.PUT_LINE('Too Many Rows');
      return 1;
end;
/
select too_many_rows_f();

六、ZERO_DIVIDE

当一个非零的数除以0事件发生后，就会触发

create or replace function login_denied_f return int as
begin
  raise login_denied;
exception
  when login_denied then
    dbms_output.put_line('this is a login_denied message.. good luck the next time');
  return 1;
end;
/
select login_denied_f();

七、LOGIN_DENIED(不在plorasql中触发，而是在lightdb中触发)

可用raise抛出该预定义异常,处理异常之后的问题。

create or replace function zero_divide_f return int as
    a int := 10;
    b int := 0;
begin
    a := a / b;
    exception 
      when zero_divide then
        dbms_output.put_line('this is a normal error');
    return 1;
end;
/

注意

一、错误处理不支持处理问题总结：（与pg内核机制有关）

（1）匿名块中的动态SQL中含有commit和rollback的匿名块，不支持；
（2）存储过程中的动态SQL中含有commit和rollback的匿名块，不支持；
(3) 包中存储过程、匿名块中的动态SQL中含有commit和rollback的匿名块，不支持。

二、包中函数、存储过程、匿名块执行顺序有问题。

三、目前plorasql不支持savepoint