PL allows you to view and drop stored procedures and functions.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
View stored procedures and functions
Stored procedures and functions are saved in the database dictionaries. The following are several common dictionary views:
DBA_SOURCE: displays all stored procedures and functions.ALL_SOURCE: displays all stored procedures and functions that can be accessed by the current user.USER_SOURCE: displays all stored procedures and functions owned by the current user.
Example: View all the stored procedures of the HR user in the DBA_SOURCE view.
obclient> SELECT OWNER,NAME,TYPE FROM DBA_SOURCE
WHERE OWNER='HR' AND TYPE='PROCEDURE';
+-------+-------------+-----------+
| OWNER | NAME | TYPE |
+-------+-------------+-----------+
| HR | AAA | PROCEDURE |
| HR | USERLOGIN | PROCEDURE |
| HR | LOG_MESSAGE | PROCEDURE |
+-------+-------------+-----------+
Example: View the content of the log_message stored procedure owned by the current user in the USER_SOURCE view.
obclient> SELECT TEXT FROM USER_SOURCE WHERE NAME='log_message' AND
TYPE='PROCEDURE'\G
*************************** 1. row ***************************
TEXT: PROCEDURE log_message(p_message varchar2)
AS
BEGIN
INSERT INTO logtable VALUES ( user, sysdate, p_message );
COMMIT;
END log_message
1 row in set
Drop stored procedures and functions
Syntax for dropping a stored procedure:
DROP PROCEDURE [user.]Procudure_name;
Syntax for dropping a function:
DROP FUNCTION [user.]Function_name;
Example:
obclient> DROP PROCEDURE log_message;
Query OK, 0 rows affected