MySQL: procedure, function, cursor, handler, cursorhandler

MySQL: procedure, function, cursor, handler, cursorhandler
Procedure & Function

Procedure Syntax:

CREATE    [DEFINER = { user | CURRENT_USER }]    PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_bodyproc_parameter:    [ IN | OUT | INOUT ] param_name typetype:    Any valid MySQL data typecharacteristic:    COMMENT 'string'  | LANGUAGE SQL  | [NOT] DETERMINISTIC  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER }begin    Valid SQL routine statementend;

 

Function Syntax:

CREATE    [DEFINER = { user | CURRENT_USER }]    FUNCTION sp_name ([func_parameter[,...]])    RETURNS type    [characteristic ...] routine_bodyfunc_parameter:    param_name typetype:    Any valid MySQL data typecharacteristic:    COMMENT 'string'  | LANGUAGE SQL  | [NOT] DETERMINISTIC  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER }begin    Valid SQL routine statementend;

Documents on procedure and function on the official website:

FAQ: http://dev.mysql.com/doc/refman/5.6/en/faqs-stored-procs.html

Syntax description: http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html

 

Cursor

Cursor official documentation: http://dev.mysql.com/doc/refman/5.6/en/cursors.html

In mysql, loop, while, repeat can be used. The deamo in loop mode is provided in the official document.

Note the following when using cursor:

1) Before declare cursor, you cannot perform any operations except declare, that is, you can only declare variables before.

2) there cannot be any variable declaration after declar cursor, and handler can be declared for exception handling.

3) cursor can only be in procedure and function.

4) fetch into var1, var2. Var name hereNoIt is the same as the column name in select when declare cursor is used. Fetch to NULL. For example, metric ==> m in deamon.

Other deamon: http://www.mysqltutorial.org/mysql-cursor/

 

Handler

Under what conditions, what kind of processing. For example, what to do when an exception occurs.

Related documents: http://dev.mysql.com/doc/refman/5.6/en/declare-handler.html

In the following deamon, declare continue handler not found and declare continue handler SQLSTATE are available.

 

Demo

 

 

 

Debugger Tool

Http://mydebugger.com/quick_start.php

 

 

 

When using debugger for debugging, you only need to write and directly call the two procedure above.