You can use the CREATE PROCEDURE statement to create or replace an independent stored procedure or a call specification.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
An independent stored procedure is a procedure stored in the database. It is a subprogram that executes specific operations.
Notice
A stored procedure created by using the CREATE PROCEDURE statement is different from a stored procedure declared or defined in a PL block or package.
Prerequisites
To create or replace an independent stored procedure in your own schema, you must have the CREATE PROCEDURE system privilege. To create or replace an independent stored procedure in the schema of other users, you must have the CREATE ANY PROCEDURE system privilege.
Syntax
The syntax of create_procedure is as follows:
CREATE [ OR REPLACE ]
PROCEDURE plsql_procedure_source
The syntax of plsql_procedure_source is as follows:
[ schema. ] procedure_name
[ ( parameter_declaration [, parameter_declaration ]... ) ] [ sharing_clause ]
[ ( default_collation_option | invoker_rights_clause | accessible_by_clause)... ]
{ IS | AS } { [ declare_section ] body | call_spec } ;
Semantics
| Syntax | Keyword or syntax node | Description |
|---|---|---|
| create_procedure | OR REPLACE | Re-creates this stored procedure (if any) and recompiles it. Before the stored procedure is redefined, users granted the access privilege can still access this stored procedure without the need to obtain the access privilege again. |
| plsql_procedure_source | schema | The name of the schema where the stored procedure is located. The default value is your schema. |
| plsql_procedure_source | procedure_name | The name of the stored procedure to be created. |
| plsql_procedure_source | body | The executable part and the exception-handling part of the stored procedure. The executable part is required and the exception-handling part is optional. |
| plsql_procedure_source | declare_section | The declarative part of the stored procedure. The declarative part is optional. Declarations are local to a stored procedure, and can be referenced in body. The declarations no longer exist after the stored procedure is executed. |
Examples
Create a stored procedure named userlogin.
CREATE TABLE loghistory
(userid VARCHAR2(20),
logdate DATE DEFAULT SYSDATE);
CREATE OR REPLACE PROCEDURE userlogin
IS
BEGIN
INSERT INTO loghistory (userid) VALUES (USER);
END;
/
