#openGauss #入门 #安装 #数据库 #开源
知识来源：docs-opengauss.osinfra.cn/zh/
示例 ：
-- 在标准SQL中使用GET DIAGNOSTICS语法
set enable_set_variable_b_format = on;
set b_format_behavior_compat_options = 'diagnostics';
set @class_origin='',@subclass_origin='',@returned_sqlstate='',@message_text= '',@mysql_errno='',@constraint_catalog='',@constraint_schema='',@constraint_name='',@catalog_name='',@schema_name='',@table_name='',@column_name='',@cursor_name='';
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics @num = NUMBER, @row = ROW_COUNT;
GET diagnostics condition @num @class_origin=CLASS_ORIGIN,@subclass_origin=SUBCLASS_ORIGIN,@returned_sqlstate=RETURNED_SQLSTATE,@message_text= MESSAGE_TEXT,@mysql_errno=MYSQL_ERRNO,@constraint_catalog=CONSTRAINT_CATALOG,@constraint_schema=CONSTRAINT_SCHEMA,@constraint_name=CONSTRAINT_NAME,@catalog_name=CATALOG_NAME,@schema_name=SCHEMA_NAME,@table_name=TABLE_NAME,@column_name=COLUMN_NAME,@cursor_name=CURSOR_NAME;
GET diagnostics condition 2 @class_origin=CLASS_ORIGIN;
show errors;
level | code | message
-------+-------+---------------------------
Error | 42P01 | table "xx" does not exist
Error | 35000 | Invalid condition number
(2 rows)
select @num, @row;
@num | @row
------+------
1 | -1
(1 row)
select @class_origin,@subclass_origin,@returned_sqlstate,@message_text,@mysql_errno,@constraint_catalog,@constraint_schema,@constraint_name,@catalog_name,@schema_name,@table_name,@column_name,@cursor_name;
@class_origin | @subclass_origin | @returned_sqlstate | @message_text | @mysql_errno | @constraint_catalog | @constraint_schema | @constraint_name | @catalog_name | @schema_name | @table_name | @column_name | @cursor_name
---------------+------------------+--------------------+---------------------------+--------------+---------------------+--------------------+------------------+---------------+--------------+-------------+--------------+--------------
ISO 9075 | ISO 9075 | 42P01 | table "xx" does not exist | 42P01 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null)
(1 row)
GET stacked diagnostics @num = NUMBER, @row = ROW_COUNT;
ERROR: GET STACKED DIAGNOSTICS when handler not active.
--在存储过程中使用GET DIAGNOSTICS语法
DROP TABLE IF EXISTS t1 ;
NOTICE: table "t1" does not exist, skipping
CREATE TABLE t1(c1 TEXT NOT NULL);
CREATE OR REPLACE PROCEDURE prc()
AS
DECLARE num INT;
DECLARE errcount INT;
DECLARE errno INT;
DECLARE msg TEXT;
BEGIN
DECLARE CONTINUE HANDLER FOR 23502
BEGIN
-- Here the current DA is nonempty because no prior statements
-- executing within the handler have cleared it
GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
RAISE NOTICE 'current DA before mapped insert , error = % , msg = %', errno, msg;
GET STACKED DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
RAISE NOTICE 'stacked DA before mapped insert , error = % , msg = %', errno, msg;
INSERT INTO t1 (c1) VALUES(0),(1),(2);
GET CURRENT DIAGNOSTICS num=NUMBER,errcount = ROW_COUNT;
RAISE NOTICE 'current DA before mapped insert , num = % , errcount = %', num, errcount ;
GET STACKED DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
RAISE NOTICE 'stacked DA before mapped insert , error = % , msg = %', errno, msg;
END;
GET CURRENT DIAGNOSTICS num=NUMBER,errcount = ROW_COUNT;
RAISE NOTICE 'current DA before mapped insert , num = % , errcount = %', num, errcount ;
INSERT INTO t1 (c1) VALUES(NULL);
GET CURRENT DIAGNOSTICS num=NUMBER,errcount = ROW_COUNT;
RAISE NOTICE 'current DA before mapped insert , num = % , errcount = %', num, errcount ;
GET STACKED DIAGNOSTICS num=NUMBER,errcount = ROW_COUNT;
END;
/
call prc();
NOTICE: current DA before mapped insert , num = 0 , errcount = -1
NOTICE: current DA before mapped insert , error = 23502 , msg = null value in column "c1" violates not-null constraint
NOTICE: stacked DA before mapped insert , error = 23502 , msg = null value in column "c1" violates not-null constraint
NOTICE: current DA before mapped insert , num = 0 , errcount = 3
NOTICE: stacked DA before mapped insert , error = 23502 , msg = null value in column "c1" violates not-null constraint
NOTICE: current DA before mapped insert , num = 2 , errcount = 3
ERROR: GET STACKED DIAGNOSTICS cannot be used outside an exception handler
CONTEXT: PL/pgSQL function prc() line 30 at GET DIAGNOSTICS
show errors;
level | code | message
-------+------+---------
(0 rows)
-- condition number
set @retSqlstate = '', @msg = '';
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics condition 1.1 @retSqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
select @retSqlstate, @msg;
@retsqlstate | @msg
--------------+---------------------------
42P01 | table "xx" does not exist
(1 row)
set @retSqlstate = '', @msg = '';
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics condition 2 @retSqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
show errors;
level | code | message
-------+-------+---------------------------
Error | 42P01 | table "xx" does not exist
Error | 35000 | Invalid condition number
(2 rows)
select @retSqlstate, @msg;
@retsqlstate | @msg
--------------+------
|
(1 row)
set @retSqlstate = '', @msg = '';
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics condition 1.8 @retSqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
show errors;
level | code | message
-------+-------+---------------------------
Error | 42P01 | table "xx" does not exist
Error | 35000 | Invalid condition number
(2 rows)
select @retSqlstate, @msg;
@retsqlstate | @msg
--------------+------
|
(1 row)
set @retSqlstate = '', @msg = '';
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics condition '1' @retSqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
select @retSqlstate, @msg;
@retsqlstate | @msg
--------------+---------------------------
42P01 | table "xx" does not exist
(1 row)
set @retSqlstate = '', @msg = '';
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics condition "1" @retSqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
select @retSqlstate, @msg;
@retsqlstate | @msg
--------------+---------------------------
42P01 | table "xx" does not exist
(1 row)
set @retSqlstate = '', @msg = '';
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics condition B'1' @retSqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
select @retSqlstate, @msg;
@retsqlstate | @msg
--------------+---------------------------
42P01 | table "xx" does not exist
(1 row)
set @retSqlstate = '', @msg = '';
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics condition B'10' @retSqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
show warnings;
level | code | message
-------+-------+---------------------------
Error | 42P01 | table "xx" does not exist
Error | 35000 | Invalid condition number
(2 rows)
select @retSqlstate, @msg;
@retsqlstate | @msg
--------------+------
|
(1 row)
set @retSqlstate = '', @msg = '';
set @con=1;
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics condition @con @retSqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
select @retSqlstate, @msg;
@retsqlstate | @msg
--------------+---------------------------
42P01 | table "xx" does not exist
(1 row)
set @retSqlstate = '', @msg = '';
set @con=1.1;
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics condition @con @retSqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
select @retSqlstate, @msg;
@retsqlstate | @msg
--------------+---------------------------
42P01 | table "xx" does not exist
(1 row)
set @retSqlstate = '', @msg = '';
set @con='1';
drop table xx;
ERROR: table "xx" does not exist
GET diagnostics condition @con @retSqlstate = RETURNED_SQLSTATE, @msg = MESSAGE_TEXT;
select @retSqlstate, @msg;
@retsqlstate | @msg
--------------+---------------------------
42P01 | table "xx" does not exist
(1 row)
#openGauss #入门 #安装 #数据库 #开源
