The ALTER PROCEDURE statement explicitly recompiles a standalone stored procedure.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL-compatible mode.
To recompile a stored procedure that is part of a package, use the ALTER PACKAGE statement.
Prerequisites
If the stored procedure is in the SYS schema, you must connect as SYSDBA. Otherwise, the stored procedure must be in the current user's schema, or the user must have the ALTER ANY PROCEDURE system privilege.
Syntax
The syntax of alter_procedure is as follows:
ALTER PROCEDURE [ schema. ] procedure_name
{ procedure_compile_clause} ;
where procedure_compile_clause is specified as follows:
COMPILE [ DEBUG ] [ compiler_parameters_clause ... ] [ REUSE SETTINGS ]
Semantics
| Syntax | Keywords or syntax nodes | Description |
|---|---|---|
| alter_procedure | schema | The name of the schema in which the stored procedure is located. The default value is the current user's schema. |
| procedure_name | The name of the stored procedure to be recompiled. | |
| procedure_compile_clause | -- | Recompiles the stored procedure. |
Examples
The following example explicitly recompiles the userlogin stored procedure owned by the sys user.
ALTER PROCEDURE sys.userlogin COMPILE;
If no compilation errors occur during the recompilation of userlogin, the stored procedure becomes valid. The database can then execute it without recompiling it at runtime.
If the recompilation of userlogin results in compilation errors, the database returns an error and the stored procedure remains invalid.
The database also invalidates all objects that depend on userlogin. These objects include any stored procedures, functions, and package bodies that call userlogin. If these objects are subsequently referenced without being explicitly recompiled, the database implicitly recompiles them at runtime.