Overview

In OceanBase Database in MySQL mode, procedural language (PL) stored programs include stored procedures, stored functions, triggers, and events.

OceanBase Database in MySQL mode supports PL stored objects of the following types:

• Stored procedure: A stored procedure is created by using the CREATE PROCEDURE statement and called by using the CALL statement. A stored procedure has no return value. However, it can have output parameters and generate a result set to return to the client program.

• Stored function: A stored function is created by using the CREATE FUNCTION statement and is used in a similar way to a built-in function. You can call a stored function in an expression to return a value during expression evaluation.

• Trigger: A trigger is created by using the CREATE TRIGGER statement and is associated with a table. Operations such as insert or update on a table can fire triggers.

• Event: An event is created by using the CREATE EVENT statement. An event is a predefined SQL task that can be automatically executed at the specified time or at a specified interval. Each event consists of the trigger condition (namely, scheduling plan) and SQL statement or stored procedure to execute. Event definitions are stored in the database and maintained together with the database to ensure that the events can be executed as scheduled even after the server is restarted.

Stored procedures and stored functions are stored routines, and stored routines and triggers are stored programs.

Each stored program has a body that consists of multiple SQL statements separated with semicolons (;). The body of a stored procedure begins with the BEGIN keyword and ends with the END; keyword.