什么是高级？这就叫高级—openGauss（210）

#openGauss #入门 #安装 #数据库 #开源

知识来源：docs-opengauss.osinfra.cn/zh/

示例

create table declare_handler_t_continue (i INT PRIMARY KEY, j INT);
create table declare_handler_t_exit (i INT PRIMARY KEY, j INT);

CREATE OR REPLACE PROCEDURE proc_ex()  IS
BEGIN
    DECLARE EXIT HANDLER FOR unique_violation
        RAISE NOTICE 'unique_violation HANDLER: SQLSTATE = %, SQLERRM = %', SQLSTATE, SQLERRM;

    INSERT INTO declare_handler_t_exit VALUES (1, 1);
    INSERT INTO declare_handler_t_exit VALUES (2, 1);
    INSERT INTO declare_handler_t_exit VALUES (1, 1); /* duplicate key */
    INSERT INTO declare_handler_t_exit VALUES (3, 1);
END;
/
call proc_ex();
NOTICE:  unique_violation HANDLER: SQLSTATE = 23505, SQLERRM = duplicate key value violates unique constraint "declare_handler_t_exit_pkey"
 proc_ex 
---------
 
(1 row)

SELECT * FROM declare_handler_t_exit ORDER BY i;
 i | j 
---+---
 1 | 1
 2 | 1
(2 rows)

CREATE OR REPLACE PROCEDURE proc_continue_sqlexception()  IS
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        RAISE NOTICE 'SQLEXCEPTION HANDLER: SQLSTATE = %, SQLERRM = %', SQLSTATE, SQLERRM;

    INSERT INTO declare_handler_t_continue VALUES (1, 1);
    INSERT INTO declare_handler_t_continue VALUES (2, 1);
    RAISE division_by_zero;
    INSERT INTO declare_handler_t_continue VALUES (1, 1);
    INSERT INTO declare_handler_t_continue VALUES (3, 1);
END;
/
call proc_continue_sqlexception();
NOTICE:  SQLEXCEPTION HANDLER: SQLSTATE = 22012, SQLERRM = division_by_zero
NOTICE:  SQLEXCEPTION HANDLER: SQLSTATE = 23505, SQLERRM = duplicate key value violates unique constraint "declare_handler_t_continue_pkey"
 proc_continue_sqlexception 
----------------------------
 
(1 row)

SELECT * FROM declare_handler_t_continue ORDER BY i;
 i | j 
---+---
 1 | 1
 2 | 1
 3 | 1
(3 rows)

#openGauss #入门 #安装 #数据库 #开源

知识来源：docs-opengauss.osinfra.cn/zh/