#openGauss #入门 #安装 #数据库 #开源
知识来源：docs-opengauss.osinfra.cn/zh/
示例 ：
-- 在非declare ... handler语句中调用signal的场景
CREATE OR REPLACE PROCEDURE p() IS
BEGIN
SIGNAL SQLSTATE '22012' SET MESSAGE_TEXT = 'this is error';
END;
/
call p();
ERROR: this is error
CONTEXT: PL/pgSQL function p() line 2 at SIGNAL
show warnings;
level | code | message
-------+-------+---------------
Error | 03000 | this is error
(1 row)
-- 在declare ... handler语句中调用signal的场景
DROP TABLE IF EXISTS t1;
CREATE OR REPLACE PROCEDURE p() IS
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42P01'
BEGIN
SIGNAL SQLSTATE '22012' SET MESSAGE_TEXT = 'this is error', MYSQL_ERRNO = 100;
END;
DROP TABLE t1;
END;
/
call p();
ERROR: this is error
CONTEXT: PL/pgSQL function p() line 4 at SIGNAL
show warnings;
level | code | message
-------+------+---------------
Error | 100 | this is error
(1 row)
-- 只有RESIGNAL的场景
DROP TABLE IF EXISTS t1;
CREATE OR REPLACE PROCEDURE p() IS
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42P01'
BEGIN
RESIGNAL;
END;
DROP TABLE t1;
END;
/
call p();
ERROR: table "t1" does not exist
CONTEXT: PL/pgSQL function p() line 4 at RESIGNAL
show warnings;
level | code | message
-------+-------+---------------------------
Error | 42P01 | table "t1" does not exist
(1 row)
-- RESIGNAL SET signal_information_item的场景
DROP TABLE IF EXISTS t1;
CREATE OR REPLACE PROCEDURE p() IS
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42P01'
BEGIN
RESIGNAL SET MESSAGE_TEXT = 'this is error', MYSQL_ERRNO = 100;
END;
DROP TABLE t1;
END;
/
call p();
ERROR: this is error
CONTEXT: PL/pgSQL function p() line 4 at RESIGNAL
show warnings;
level | code | message
-------+------+---------------
Error | 100 | this is error
(1 row)
-- RESIGNAL condition_value SET signal_information_item
DROP TABLE IF EXISTS t1;
CREATE OR REPLACE PROCEDURE p() IS
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42P01'
BEGIN
RESIGNAL SQLSTATE '22012' SET MESSAGE_TEXT = 'this is error', MYSQL_ERRNO = 100;
END;
DROP TABLE t1;
END;
/
call p();
ERROR: this is error
CONTEXT: PL/pgSQL function p() line 4 at RESIGNAL
show warnings;
level | code | message
-------+-------+---------------------------
Error | 42P01 | table "t1" does not exist
Error | 100 | this is error
(2 rows)
#openGauss #入门 #安装 #数据库 #开源
