GBase8a 集群查看哪些自定义函数和存储过程

GBase 8a数据库集群,提供了元数据表 gbase.proc 查看存储过程的情况, 也可以用show的快捷方式查看名字等。

gbase.proc 表结构

其中请关注

字段含义
name
specific_name
存储过程名字
db库名
type类型
procedure是存储过程
function 是自定义函数
ROUTINE_DEFINITION定义的主体
param_list参数列表
returns返回类型
body主体

存储过程查询样例


gbase> select * from gbase.proc where db='testdb' and name='executeSQL'\G;
*************************** 1. row ***************************
                  db: testdb
                name: executeSQL
                type: PROCEDURE
       specific_name: executeSQL
            language: SQL
     sql_data_access: CONTAINS_SQL
    is_deterministic: NO
       security_type: DEFINER
          param_list:
 S_SQL_TMP VARCHAR(21000)

             returns:
                body: BEGIN
    set @sql = S_SQL_TMP;
  PREPARE s1 FROM @sql;
 EXECUTE s1;
 DEALLOCATE PREPARE s1;
END
             definer: root@%
             created: 2020-09-03 18:59:45
            modified: 2020-09-03 18:59:45
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,。。。。。。
             comment:
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: utf8_general_ci
           body_utf8: BEGIN
    set @sql = S_SQL_TMP;
  PREPARE s1 FROM @sql;
 EXECUTE s1;
 DEALLOCATE PREPARE s1;
END
1 row in set (Elapsed: 00:00:00.00)

自定义函数查询样例

gbase> select * from proc where db='testdb' and name='f_test'\G;
*************************** 1. row ***************************
                  db: testdb
                name: f_test
                type: FUNCTION
       specific_name: f_test
            language: SQL
     sql_data_access: CONTAINS_SQL
    is_deterministic: NO
       security_type: DEFINER
          param_list: inDate datetime
             returns: datetime
                body: begin
  return date_add(inDate, interval 1 day);
end
             definer: root@%
             created: 2020-09-03 21:12:19
            modified: 2020-09-03 21:12:19
            sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE。。。。。
             comment:
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: utf8_general_ci
           body_utf8: begin
  return date_add(inDate, interval 1 day);
end
1 row in set (Elapsed: 00:00:00.00)

show 方式显示

show function status [where …]

gbase> show function status;
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| Db                                                               | Name                                                             | Type     | Definer                                                                       | Modified            | Created             | Security_type | Comment                                                          | character_set_client             | collation_connection             | Database Collation               |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| testdb                                                           | f_test                                                           | FUNCTION | root@%                                                                        | 2020-09-03 21:12:19 | 2020-09-03 21:12:19 | DEFINER       |                                                                  | utf8                             | utf8_general_ci                  | utf8_general_ci                  |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

带where的

gbase> show function status where name='f_test';
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| Db                                                               | Name                                                             | Type     | Definer                                                                       | Modified            | Created             | Security_type | Comment                                                          | character_set_client             | collation_connection             | Database Collation               |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| testdb                                                           | f_test                                                           | FUNCTION | root@%                                                                        | 2020-09-03 21:12:19 | 2020-09-03 21:12:19 | DEFINER       |                                                                  | utf8                             | utf8_general_ci                  | utf8_general_ci                  |
+------------------------------------------------------------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+

show procedure status [where …];

gbase> show procedure  status where name='ptest';
+------------------------------------------------------------------+------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| Db                                                               | Name                                                             | Type      | Definer                                                                       | Modified            | Created             | Security_type | Comment                                                          | character_set_client             | collation_connection             | Database Collation               |
+------------------------------------------------------------------+------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
| testdb                                                           | ptest                                                            | PROCEDURE | root@%                                                                        | 2020-07-13 10:45:54 | 2020-07-13 10:45:54 | DEFINER       |                                                                  | utf8                             | utf8_general_ci                  | utf8_general_ci                  |
+------------------------------------------------------------------+------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------+---------------------+---------------------+---------------+------------------------------------------------------------------+----------------------------------+----------------------------------+----------------------------------+
1 row in set (Elapsed: 00:00:00.00)

其它元数据信息,请参考

http://www.gbase8.cn/?tag=%e5%85%83%e6%95%b0%e6%8d%ae

发表评论

您的电子邮箱地址不会被公开。