GBase 8a创建存储过程、存储函数未保留注释解决方案

问题来源

在项目现场，业务员反馈在创建存储过程、存储函数时，注释内容没有存进系统的元数据库，导致show create命令无法显示出注释。

问题复现

创建存储过程并用show命令显示，在创建的过程中加入注释

gccli -uroot -Dtest

GBase client 9.5.3.27.88ef4e28. Copyright (c) 2004-2022, GBase.  All Rights Reserved.

gbase> delimiter //
gbase> drop procedure if exists test_1 // 
Query OK, 0 rows affected (Elapsed: 00:00:00.12)

gbase> -- explanatory note 1
gbase> create procedure test_1 ()
    -> /* 
   /*> --explanatory note 2
   /*> --explanatory note 3
   /*> */
    -> begin
    -> -- explanatory note 1
    -> /* 
   /*> explanatory note 2
   /*> explanatory note 3
   /*> */
    -> 
    -> #this is test_sql
    -> select 'This is test' ;
    -> end //
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> delimiter ; 

在插入注释时，采用了“--”，“#”，“/**/”三中风格的注释。

采用show create命令显示

base> show create procedure test_1\G;
*************************** 1. row ***************************
           Procedure: test_1
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH
    Create Procedure: CREATE DEFINER="root"@"%" PROCEDURE "test_1"()
begin

select 'This is test' ;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (Elapsed: 00:00:00.00)

可以看出并没有显示注释内容

解决方案

在采用gccli命令行登录集群时，加上 -c（小写）即可显示注释。-c参数为hint优化参数。

注意：-c参数只对函数体生效，即begin后的内容生效，begin之前的内容需要增加COMMENT关键字

示例：

创建存储过程

gccli -uroot -Dtest -c
GBase client 9.5.3.27.88ef4e28. Copyright (c) 2004-2022, GBase.  All Rights Reserved.

gbase> delimiter //
gbase> drop procedure if exists test_1 // 
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> -- explanatory note 1
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> create procedure test_1 ()
    ->  
    -> COMMENT '--explanatory note 2'
    -> begin
    -> -- explanatory note 1
    -> /* 
   /*> explanatory note 2
   /*> explanatory note 3
   /*> */
    -> 
    -> #this is test_sql
    -> select 'This is test' ;
    -> end //
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> delimiter ; 

显示存储过程

gbase> show create procedure test_1\G;                                                                                  
*************************** 1. row ***************************
           Procedure: test_1
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH
    Create Procedure: CREATE DEFINER="root"@"%" PROCEDURE "test_1"()
    COMMENT '--explanatory note 2'
begin
-- explanatory note 1
/* 
explanatory note 2
explanatory note 3
*/

#this is test_sql
select 'This is test' ;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (Elapsed: 00:00:00.00