Common command statement records for MySQL database-stored procedure statements

Create event creates execution events

CREATE

[definer = {User | Current_User}]

EVENT

[IF not EXISTS]

Event_Name

On SCHEDULE SCHEDULE

[on completion [NOT] preserve]--whether the server retains time when the event is completed. Default is not reserved

[ENABLE | DISABLE | DISABLE on SLAVE]--

[COMMENT ' COMMENT ']--annotate events

Do event_body;

Schedule

at timestamp [+ INTERVAL INTERVAL] ...

| Every interval

[Starts timestamp [+ INTERVAL INTERVAL] ...]

[ENDS timestamp [+ INTERVAL INTERVAL] ...]

Interval

Quantity {Year | QUARTER | MONTH | Day | HOUR | MINUTE |

WEEK | SECOND | Year_month | Day_hour | Day_minute |

Day_second | Hour_minute | Hour_second | Minute_second}

Exmple

CREATE EVENT MyEvent

On SCHEDULE at Current_timestamp + INTERVAL 1 HOUR

Do

UPDATE myschema.mytable SET MyCol = MyCol + 1;

ALTER EVENT

Alter

[definer = {User | Current_User}]

EVENT Event_Name

[on SCHEDULE SCHEDULE]

[on completion [NOT] PRESERVE]

[RENAME to New_event_name]

[ENABLE | DISABLE | DISABLE on SLAVE]

[COMMENT ' COMMENT ']

[Do Event_body]

DROP Event [IF EXISTS] Event;

Create procedure/function Creating stored procedures/functions

CREATE

[definer = {User | Current_User}]

PROCEDURE sp_name ([proc_parameter[,...]])

[Characteristic ...] Routine_body

CREATE

[definer = {User | Current_User}]

FUNCTION sp_name ([func_parameter[,...])

RETURNS type

[Characteristic ...] Routine_body

Proc_parameter:

[In | Out | INOUT] Param_name Type

Func_parameter:

Param_name type

Type

Any valid MySQL data type

Characteristic:

COMMENT ' String '

| LANGUAGE SQL

| [NOT] Deterministic

| {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}

| SQL SECURITY {definer | INVOKER}

Exmple

Delimiter//

Mysql> CREATE PROCEDURE Simpleproc (out param1 INT)

BEGIN

SELECT COUNT (*) into param1 from T;

end//

CREATE FUNCTION Hello (S CHAR (20))

RETURNS CHAR (deterministic)

RETURN CONCAT (' Hello, ', S, '! ');

Procedure,function But when you need to update the execution content, you need to drop the Create

ALTER PROCEDURE

ALTER PROCEDURE proc_name [characteristic ...]

Characteristic:

COMMENT ' String '

| LANGUAGE SQL

| {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}

| SQL SECURITY {definer | INVOKER}

ALTER FUNCTION

ALTER FUNCTION func_name [characteristic ...]

Characteristic:

COMMENT ' String '

| LANGUAGE SQL

| {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}

| SQL SECURITY {definer | INVOKER}

DROP function/function [IF EXISTS] Function/procedure

CREATE TRIGGER Trigger

CREATE

[definer = {User | Current_User}]

TRIGGER trigger_name

Trigger_time trigger_event

On Tbl_name for each ROW

Trigger_body

Trigger_time: {before | After}

Trigger_event: {INSERT | UPDATE | DELETE}

DECLARE Defining variables

DECLARE CONDITION

DECLARE condition_name Condition for Condition_value

Condition_value:

Mysql_error_code

| SQLSTATE [VALUE] Sqlstate_value

DECLARE Cursor Definition Cursors

DECLARE cursor_name Cursor for select_statement

OPEN CURSOR;

CLOSE CURSOR;

FETCH cursor into variable[,...]

DECLARE VARIABLE

DECLARE Var_name [, Var_name] ... type [DEFAULT value]

SHOW CREATE Event event;

SHOW CREATE function function;

SHOW CREATE PROCEDURE PROCEDURE;

SHOW EVENTS [from database] [like ' pattern ' | WHERE expression];

SHOW function CODE function;

SHOW FUNCTION STATUS [like ' pattern ' | WHERE expression];

SHOW PROCEDURE CODE stroe_procedure;

SHOW PROCEDURE STATUS [like ' pattern ' | WHERE expression];

SHOW TRIGGERS [from database] [like ' pattern ' | WHERE expression];

Delimiter set Terminator

Common command statement records for MySQL database-stored procedure statements