MySQL management and optimization (9) _ MySQL

MySQL management and optimization (9) stored procedures and functions

• Stored procedures and functions are a collection of SQL statements compiled and stored in the database.

Create and modify stored procedures or functions

• Related syntax

CREATE    [DEFINER = { user | CURRENT_USER }]    PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_bodyCREATE    [DEFINER = { user | CURRENT_USER }]    FUNCTION sp_name ([func_parameter[,...]])    RETURNS type    [characteristic ...] routine_bodyproc_parameter:    [ IN | OUT | INOUT ] param_name typefunc_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 }routine_body:    Valid SQL routine statement

• Example

DELIMITER // -- CREATE a stored PROCEDURE mysql> create procedure cityname_by_id (IN cid INT, OUT total INT)-> reads SQL DATA-> BEGIN-> SELECT id, city FROM city WHERE id = cid;-> SELECT FOUND_ROWS () INTO total;-> END // Query OK, 0 rows affected (0.06 sec) -- CALL the stored procedure mysql> CALL cityname_by_id (2, @ res ); + ---- + ---------- + | id | city | + ---- + ---------- + | 2 | NeiJiang | + ---- + ---------- + 1 row in set (0.00 sec) Query OK, 1 row affected (0.01 sec) mysql> SELECT @ res; + ------ + | @ res | + ------ + | 1 | + ------ + 1 row in set (0.00 sec)

Delete stored procedures or functions

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

Query stored procedures or functions

Mysql> show procedure status like 'cityname _ by_id '/G *************************** 1. row ************************** Db: mysqltest Name: cityname_by_id Type: PROCEDURE Definer: root @ localhost Modified: 15:22:11 Created: 15:22:11 Security_type: DEFINER Comment: character_set_client: utf8collation_connection: utf8_general_ci Database Collation: rows in set (0.01 sec) -- view the definition of the stored PROCEDURE or function mysql> show create procedure cityname_by_id/G ************************ * ** 1. row ************************* Procedure: Rule: STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTIONCreate Procedure: create definer = 'root' @ 'localhost' PROCEDURE 'cityname _ by_id '(IN cid INT, OUT total INT) reads SQL DATA BEGIN SELECT id, city FROM city WHERE id = cid; SELECT FOUND_ROWS () INTO total; ENDcharacter_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci1 row in set (0.00 sec)

You can also query the system table information_schema.routines:

mysql> SELECT * FROM information_schema.routines WHERE ROUTINE_NAME='cityname_by_id'/G

Use of variables

• Variable definition: only in the BEGIN... END block. syntax:

DECLARE var_name[,...] type [DEFAULT_VALUE]DECLARE last_month_start DATE;

• Variable assignment: values can be directly assigned or queried.

SET var_name = expr [, var_name = expr]... # expression value SET last_month_start = DATE_SUB (CURRENT_DATE (), INTERVAL 1 MONTH) # SELECT distinct SELECT... FROM... INTO var_name

• Define conditions and processing

-- CONDITION definition DECLARE condition_name condition for condition_value: SQLSTATE [VALUE] sqlstate_value | mysql_error_code -- CONDITION processing DECLARE handler_type handler for condition_value [,...] sp_statementhandler_type: CONTINUE | EXIT | UNDOcondition_value: SQLSTATE [VALUE] condition_name | SQLWARNING | not found | SQLEXCEPTION | mysql_error_code

Example:

-- CREATE a stored PROCEDURE mysql> create procedure city_insert ()-> BEGIN-> insert into city VALUES (200, 'Beijing');-> insert into city VALUES (200, 'Beijing');-> END;-> // Query OK, 0 rows affected (0.00 sec) -- CALL the stored procedure. in the second sentence, the mysql> CALL city_insert () error is returned () // ERROR 1062 (23000): Duplicate entry '000000' for key 'primary' -- modify the stored PROCEDURE and support exception handling drop procedure if exists city_insertmysql> create procedure city_insert () -> BEGIN-> declare continue handler for sqlstate '000000' SET @ x = 1;-> insert into city VALUES (23000, 'Shanghai '); -> insert into city VALUES (300, 'Shanghai');-> END;-> // Query OK, 0 rows affected (0.00 sec) -- call again, mysql> CALL city_insert () // Query OK, 0 rows affected, 1 warning (0.09 sec)

Use of the cursor

• In stored procedures and functions, you can use the cursor to process the result set cyclically.

-- DECLARE the cursor declare cursor_name cursor for select_statement -- open cursor open cursor_name -- fetch cursor fetch cursor_name INTO var_name [, var_name] -- CLOSE the cursor close cursor_name

• Example

-- Define the stored PROCEDURE mysql> create procedure city_stat ()-> BEGIN-> DECLARE cid INT;-> DECLARE cname VARCHAR (20);-> DECLARE cur_city cursor for select * FROM city; -> declare exit handler for not found close cur_city;-> SET @ x1 = 0;-> SET @ x2 = 0;-> OPEN cur_city; -> REPEAT-> FETCH cur_city INTO cid, cname;-> IF cid <= 4 THEN-> SET @ x1 = @ x1 + cid; -> ELSE-> SET @ x2 = @ x2 + cid * 2;-> end if;-> UNTIL 0 end repeat;-> CLOSE cur_city; -> END;-> // Query OK, 0 rows affected (0.06 sec) -- execute the stored procedure mysql> SELECT * FROM city; + ----- + ---------- + | id | city | + ----- + ---------- + | 2 | NeiJiang | 3 | HangZhou | 10 | ChengDu | 200 | Beijing | 300 | ShangHai | + ----- + ---------- + 5 rows in set (0.00 sec) mysql> CALL city_stat (); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SELECT @ x1, @ x2; + ------ + | @ x1 | @ x2 | + ------ + | 5 | 1020 | + ------ + 1 row in set (0.00 sec)

• Variables, conditions, handlers, and cursor declarations are ordered. variables and conditions must be declared at the beginning, then the declaration of the cursor, and finally the life of the handler.

Process control

• Specific process control statements include: IF, CASE, LOOP, LEAVE, ITERATE, REPEAT, WHILE.
• Their specific usage can refer to: http://dev.mysql.com/doc/refman/5.7/en/flow-control-statements.html

For details, refer:

Http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

No.