[Feature] plsql support show procedure stmt

[xinyiZzz]

Search before asking

• [X] I had searched in the issues and found no similar issues.

Description

Goal

Compatible with Mysql grammar in new optimizer Nereids, support grammar and impl:

1. SHOW PROCEDURE STATUS
2. SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'sys'

only support grammar but do nothing:

1. SHOW FUNCTION STATUS

TODO:

1. SHOW CREATE PROCEDURE

Current Behavior

SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS will fall back to old parser using cup and ShowProcedureStmt does nothing, so it returns empty.

sql_parser.cup

show_param ::=
    KW_WHITELIST
    procedure_or_function KW_STATUS opt_wild_where
    {:
        RESULT = new ShowProcedureStmt();
    :}

Expected

1. use mysql client, refer to Mysql:

mysql> SHOW PROCEDURE STATUS;
|Db|Name|Type|Definer|Modified|Created|Security_type|Comment|character_set_client|collation_connection|DatabaseCollation|
| sys  | create_synonym_db   | PROCEDURE | mysql.sys@localhost | 2022-12-17 18:01:31 | 2022-12-17 18:01:31 | INVOKER       |
Description
...
 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |

mysql> SELECT * FROM information_schema.routines WHERE routine_type = 'PROCEDURE' limit 1;
|SPECIFIC_NAME|ROUTINE_CATALOG|ROUTINE_SCHEMA|ROUTINE_NAME|ROUTINE_TYPE|DATA_TYPE|CHARACTER_MAXIMUM_LENGTH|CHARACTER_OCTET_LENGTH|NUMERIC_PRECISION|NUMERIC_SCALE|DATETIME_PRECISION|CHARACTER_SET_NAME|COLLATION_NAME|DTD_IDENTIFIER|ROUTINE_BODY|ROUTINE_DEFINITION|EXTERNAL_NAME|EXTERNAL_LANGUAGE|PARAMETER_STYLE|IS_DETERMINISTIC|SQL_DATA_ACCESS|SQL_PATH|SECURITY_TYPE|CREATED|LAST_ALTERED|SQL_MODE|ROUTINE_COMMENT|DEFINER|CHARACTER_SET_CLIENT|COLLATION_CONNECTION|DATABASE_COLLATION|
| create_synonym_db | def             | sys            | create_synonym_db | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | BEGIN
    DECLARE v_done bool DEFAULT FALSE;
    DECLARE v_db_name_check VARCHAR(64);
    ...
    SELECT CONCAT(
        'Created ', v_views_created, ' view',
        IF(v_views_created != 1, 's', ''), ' in the ',
        sys.quote_identifier(in_synonym), ' database'
    ) AS summary;
END |          NULL | SQL               | SQL             | NO               | MODIFIES SQL DATA |     NULL | INVOKER       | 2022-12-17 18:01:31 | 2022-12-17 18:01:31 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
Description
...
 | mysql.sys@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |

2. use Navicat, refer to Mysql:

In Mysql log:

In Doris FE audit log:

References

Mysql: list all stored procedure:

1. SHOW PROCEDURE STATUS;
2. SELECT * FROM information_schema.routines WHERE routine_type = 'PROCEDURE' limit 1;
show a procedure by name:
3. SHOW CREATE PROCEDURE Command

refer to: https://database.guide/2-ways-to-list-all-stored-procedures-in-mysql/ https://dev.mysql.com/doc/refman/8.0/en/show-create-procedure.html https://dev.mysql.com/doc/refman/8.0/en/show-procedure-status.html https://dev.mysql.com/doc/refman/8.0/en/show-procedure-code.html https://soft-builder.com/how-to-list-stored-procedures-and-functions-in-mysql-database/

Oracle: list all stored procedure:

1. SELECT object_name FROM user_procedures WHERE object_type = 'PROCEDURE';
2. SELECT owner, object_name FROM all_procedures
3. SELECT owner, object_name FROM dba_procedures WHERE object_type = 'PROCEDURE';
4. SELECT * FROM USER_OBJECTS WHERE object_type = 'PROCEDURE' AND object_name = 'MY_STORED_PROCEDURE'

refer to: https://database.guide/how-to-list-all-stored-procedures-in-oracle-database/

PostgreSQL: list all stored procedure:

1. information_schema.routines View
	specific_catalog | specific_schema | specific_name | routine_catalog | routine_schema | routine_name | routine_type | module_catalog | module_schema | module_name | udt_catalog | udt_schema | udt_name | data_type | character_maximum_length | character_octet_length | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | type_udt_catalog | type_udt_schema | type_udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | routine_body | routine_definition | external_name | external_language | parameter_style | is_deterministic | sql_data_access | is_null_call | sql_path | schema_level_routine | max_dynamic_result_sets | is_user_defined_cast | is_implicitly_invocable | security_type | to_sql_specific_catalog | to_sql_specific_schema | to_sql_specific_name | as_locator | created | last_altered | new_savepoint_level | is_udt_dependent | result_cast_from_data_type | result_cast_as_locator | result_cast_char_max_length | result_cast_char_octet_length | result_cast_char_set_catalog | result_cast_char_set_schema | result_cast_char_set_name | result_cast_collation_catalog | result_cast_collation_schema | result_cast_collation_name | result_cast_numeric_precision | result_cast_numeric_precision_radix | result_cast_numeric_scale | result_cast_datetime_precision | result_cast_interval_type | result_cast_interval_precision | result_cast_type_udt_catalog | result_cast_type_udt_schema | result_cast_type_udt_name | result_cast_scope_catalog | result_cast_scope_schema | result_cast_scope_name | result_cast_maximum_cardinality | result_cast_dtd_identifier
2. pg_proc Catalog
3. \df Command

refer to: https://database.guide/3-ways-to-list-all-stored-procedures-in-a-postgresql-database/ https://database.guide/3-ways-to-list-all-stored-procedures-that-reference-a-table-in-postgresql/ https://soft-builder.com/how-to-list-procedures-in-postgresql-database/

Use case

No response

Related issues

No response

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[Vallishp]

please assign to me