logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

DECLARE HANDLER

功能描述

Vastbase G100在MySQL兼容模式下支持使用DECLARE HANDLER语句来处理函数或存储过程中的异常。

注意事项

  • 该功能仅在数据库兼容模式为MySQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='B'）。
  • 该语法仅在函数或存储过程中支持。
  • 同时使用declare handler和exception when异常处理会报错。
  • 在Vastbase中将mysql_error_code视为sqlstate。

语法格式

DECLARE EXIT|CONTINUE HANDLER
	FOR  condition _value[,condition_value]...
	statement

其中confition_value包括

{
mysql_error_code
|SQLSTATE[VALUE] sqlstate_value
|condition_name
|NOT FOUND
|SQLWARNING
|SQLEXCEPTION
}

参数说明

  • EXIT

    处理类型，表明当程序终止，即退出当前DECLARE所在的BEGIN…END块。可以在声明块中多次声明EXIT HANDLER。

  • CONTINUE

    处理类型，表明当程序终止，继续执行当前DECLARE所在的BEGIN…END块。可以在声明块中多次声明CONTINUE HANDLER。

  • condition_value

    说明HANDLER被何种条件触发。单次DECLARE HANDLER时可以包含多个condition_value的语法。

  • Error_code

    异常捕获方法，捕获mysql_error_code值。

  • SQLSTATE

    错误码。

  • condition_name

    表示异常的名称。

    • NOT FOUND

      无数据。异常捕获方法，使用NOT FOUND捕获异常。DECLARE CONTINUE HANDLER FOR NOT FOUND ...，该用法中，DECLARE CONTINUE HANDLER语句用于定义一个异常处理程序，当游标没有更多行可供检索时，即”NOT FOUND”状态，可以触发一个特定的处理程序，而不是立即退出游标循环。这种机制常用于处理数据集的末尾或执行迭代操作的场景。

  • SQLWARNING

    警告。异常捕获方法，使用SQLWARNING捕获异常。

  • SQLEXCEPTION

    异常。异常捕获方法，使用SQLEXCEPTION捕获异常。

  • statement

    当触发HANDLER条件时，执行statement语句。

示例

示例1：在存储过程中定义好触发异常处理语句。

1、创建测试表。

CREATE TABLE article_testtb(
article_id INT,
tag_id INT,
PRIMARY KEY(article_id,tag_id)
);

2、创建声明异常的存储过程。

CREATE OR REPLACE PROCEDURE insert_article_testtb(IN article_id INT, IN tag_id INT) AS
DECLARE result VARCHAR;
BEGIN

DECLARE EXIT HANDLER FOR sqlexception
begin
RAISE NOTICE 'SQLSTATE = %,SQLCODE = %,SQLERROR=%',SQLSTATE,returned_sqlstate,message_text;
end;

IF article_id <= 10 THEN
-- insert a new record into article_tags
INSERT INTO article_testtb(article_id,tag_id)
VALUES(article_id,tag_id);

-- return tag count for the article
SELECT COUNT(*) FROM article_testtb into result;
RAISE INFO 'count: %', result;

ELSE
CREATE USER pi_user_independent WITH INDEPENDENT IDENTIFIED BY "1234@abe";
END IF;

END;
/

3、调用存储过程

（a）正常插入数据。

CALL insert_article_testtb(1,1);
CALL insert_article_testtb(1,2);
CALL insert_article_testtb(1,3);

返回结果为：

INFO:  count: 1
 insert_article_testtb
-----------------------

(1 row)

INFO:  count: 2
 insert_article_testtb
-----------------------

(1 row)

INFO:  count: 3
 insert_article_testtb
-----------------------

(1 row)

（b）主键重复，触发异常处理。

CALL insert_article_testtb(1,3);

返回结果为：

NOTICE:  SQLSTATE = 23505,SQLCODE = 83906754,SQLERROR=duplicate key value violates unique constraint "article_testtb_pkey"
 insert_article_testtb
-----------------------

(1 row)

4、清理测试数据。

DROP TABLE article_testtb;
DROP PROCEDURE insert_article_testtb;

示例2：同时声明多个mysql_error_code。

1、创建测试表。

CREATE TABLE article_testtb2(
article_id INT,
tag_id INT,
PRIMARY KEY(article_id,tag_id)
);

2、创建声明异常的存储过程。

CREATE OR REPLACE PROCEDURE test_pro(IN article_id INT, IN tag_id INT) AS
DECLARE result VARCHAR;
BEGIN

--22012分母为0异常
DECLARE EXIT HANDLER FOR 22012
begin
RAISE NOTICE 'SQLSTATE = %,SQLCODE = %,SQLERROR=%',SQLSTATE,returned_sqlstate,message_text;
end;

--23505主键重复的异常
DECLARE EXIT HANDLER FOR 23505
begin
RAISE NOTICE 'SQLSTATE = %,SQLCODE = %,SQLERROR=%',SQLSTATE,returned_sqlstate,message_text;
end;

--用户已存在
DECLARE EXIT HANDLER FOR 42710
begin
RAISE NOTICE 'SQLSTATE = %,SQLCODE = %,SQLERROR=%',SQLSTATE,returned_sqlstate,message_text;
end;

--正常执行的命令
raise info '%', 'begin';

IF article_id < 10 THEN
-- insert a new record into article_tags
INSERT INTO article_testtb2(article_id,tag_id)
VALUES(article_id,tag_id);
ELSEIF article_id < 20 THEN

--22012分母为0异常
result := article_id / tag_id;
RAISE INFO 'result: %', result;
ELSE
create user test_user_1131524 identified by 'Aa123456';
END IF;

--正常执行的命令
raise info '%', 'end';
END;
/

3、调用存储过程。

（a）正常调用。

CALL test_pro(1,1);

返回结果为：

INFO:  begin
INFO:  end
 test_pro
----------

(1 row)

（b）触发主键重复异常。

CALL test_pro(1,1);

返回结果为：

INFO:  begin
NOTICE:  SQLSTATE = 23505,SQLCODE = 83906754,SQLERROR=duplicate key value violates unique constraint "article_testtb2_pkey"
 test_pro
----------

(1 row)

（c）分母为0触发异常。

CALL test_pro(11,0);

返回结果为：

INFO:  begin
NOTICE:  SQLSTATE = 22012,SQLCODE = 33816706,SQLERROR=division by zero
 test_pro
----------

(1 row)

（d）触发用户重复异常。

CALL test_pro(21,21);
CALL test_pro(22,22);

返回结果为：

INFO:  begin
INFO:  end
 test_pro
----------

(1 row)

INFO:  begin
ERROR:  role "test_user_1131524" already exists
CONTEXT:  SQL statement "create user test_user_1131524 identified by '********'"
PL/pgSQL function public.test_pro(integer,integer) line 36 at SQL statement

4、清理测试数据。

DROP TABLE article_testtb2;
DROP PROCEDURE test_pro;
DROP USER test_user_1131524;

示例3： 使用DECLARE CONTINUE创建触发器。

1、创建表和触发表。

CREATE TABLE tb(id int,col1 text);
CREATE TABLE trig(id int PRIMARY KEY);

2、创建触发器函数。

CREATE OR REPLACE FUNCTION tri_func() return trigger as
BEGIN
DECLARE CONTINUE handler FOR 23505
BEGIN
RAISE NOTICE 'SQLSTATE = %,SQLCODE = %,SQLERROR=%',SQLSTATE,returned_sqlstate,message_text;
END;
INSERT INTO trig VALUES(new.id);
raise info 'end';
RETURN new;
END;
/

3、创建插入触发器。

CREATE TRIGGER insert_trigger
AFTER INSERT ON tb
FOR each row
EXECUTE PROCEDURE tri_func();

4、插入数据，第一次插入正常，第二次插入主键重复报错。

INSERT INTO tb VALUES(1, 'lili'),(2, 'lolo');
INSERT INTO tb VALUES(1, 'cici');

返回结果为：

INFO:  end
INFO:  end
INSERT 0 2

NOTICE:  SQLSTATE = 23505,SQLCODE = 83906754,SQLERROR=duplicate key value violates unique constraint "trig_pkey"
INFO:  end
INSERT 0 1

5、查询表tb中数据。

select * from tb order by 1;

返回结果为：

 id | col1
----+------
  1 | lili
  1 | cici
  2 | lolo
(3 rows)

6、查询触发表trig中数据，插入数据调用触发器成功。

select * from trig order by 1;

返回结果为：

 id
----
  1
  2
(2 rows)

7、清理测试数据。

DROP TABLE tb;
DROP TABLE trig;
DROP FUNCTION tri_func;

示例4： 使用DECLARE CONTINUE HANDLER FOR NOT FOUND语句创建存储过程。

1、创建测试表并插入测试数据。

create table employees(employee_id int,employee_name varchar(20),department_id int);
insert into employees select 1,'aa',1;
insert into employees select 2,'bb' ,1;
insert into employees select 3,'cc',2;

2、创建存储过程，设置满足NOT FOUND条件时返回No employees found for the given department ID.

DELIMITER //
CREATE OR REPLACE PROCEDURE get_employees_by_department(IN dept_id INT)
AS
DECLARE emp_name VARCHAR(255);
DECLARE emp_id INT;
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE emp_cursor CURSOR FOR
SELECT employee_id, employee_name FROM employees WHERE department_id = dept_id;

BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET done = TRUE;
END;

OPEN emp_cursor;

read_loop: LOOP
-- 读取游标数据
FETCH emp_cursor INTO emp_id, emp_name;

IF done THEN
LEAVE read_loop;
END IF;

SELECT CONCAT('Employee ID: ', emp_id, ', Employee Name: ', emp_name) AS Employee_Info;
END LOOP;

CLOSE emp_cursor;

IF done THEN
raise notice 'No employees found for the given department ID.' ;
END IF;

END//
DELIMITER ;

3、调用存储过程。

call get_employees_by_department(10);

返回结果为：

NOTICE:  No employees found for the given department ID.
 get_employees_by_department
-----------------------------

(1 row)

4、删除测试数据。

DROP TABLE employees;
DROP PROCEDURE get_employees_by_department;