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)
其它元数据信息，请参考