mysql 存储过程和函数
函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用call语句，而调用函数就直接引用函数名+参数即可
IN,OUT,INOUT三个参数前的关键词只适用于存储过程，对函数而言所有的参数默认都是输入参数
IN输入参数用于把数值传入到存储过程中；OUT输出参数将数值传递到调用者，初始值是NULL；INOUT输入输出参数把数据传入到存储过程，在存储过程中修改之后再传递到调用者
mysql> select * from students; +-----+--------+--------+---------+ | sid | sname | gender | dept_id | +-----+--------+--------+---------+ | 1 | Andrew | 0 | 1 | | 2 | Andy | 0 | 1 | | 3 | Bob | 0 | 1 | | 4 | Ruth | 1 | 2 | | 5 | Mike | 0 | 2 | | 6 | John | 0 | 3 | | 7 | Cindy | 1 | 3 | | 8 | Susan | 1 | 3 | +-----+--------+--------+---------+ 8 rows in set (0.00 sec) mysql> delimiter // mysql> create procedure simpleproc(IN param1 int,OUT param2 INT) -> BEGIN -> SELECT COUNT(*) INTO param2 FROM students where sid > param1; -> END // Query OK, 0 rows affected (0.42 sec) mysql> delimiter ; mysql> call simpleproc(1,@a); Query OK, 1 row affected (0.00 sec) mysql> select @a; +------+ | @a | +------+ | 7 | +------+ 1 row in set (0.00 sec) mysql> select count(*) from students where sid>1; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) mysql> call simpleproc(3,@a); Query OK, 1 row affected (0.00 sec) mysql> select @a; +------+ | @a | +------+ | 5 | +------+ 1 row in set (0.00 sec) mysql> delimiter // mysql> create procedure simpleproc2() -> BEGIN -> SELECT COUNT(*) FROM students where sid > 1; -> END // Query OK, 0 rows affected (0.07 sec) mysql> delimiter ; mysql> call simpleproc2(); +----------+ | COUNT(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show create procedure simpleproc2; +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`() BEGIN SELECT COUNT(*) FROM students where sid > 1; END | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec)
Delimiter命令是改变语句的结束符，MySQL默认的结束符为;号，由于procedure和function中的;号并不代表创建的结束，所以要替换成另外的结束符以便表示创建的结束
mysql> delimiter // mysql> CREATE FUNCTION hello (s CHAR(20)) -> RETURNS CHAR(50) -> RETURN CONCAT('Hello',s,'!'); -> // ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) mysql> delimiter ; mysql> show variables like '%trust%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin_trust_function_creators | OFF | +---------------------------------+-------+ 1 row in set (0.01 sec) mysql> set global log_bin_trust_function_creators=on; Query OK, 0 rows affected (0.00 sec) mysql> delimiter // mysql> CREATE FUNCTION hello (s CHAR(20)) -> RETURNS CHAR(50) -> RETURN CONCAT('Hello, ',s,'!'); -> // Query OK, 0 rows affected (0.06 sec) mysql> delimiter ; mysql> select hello('a'); +------------+ | hello('a') | +------------+ | Hello, a! | +------------+ 1 row in set (0.00 sec) mysql> select * from teacher; +----+-----------+---------+ | id | name | dept_id | +----+-----------+---------+ | 1 | Zhang san | 1 | | 2 | Li si | 1 | | 3 | Wang wu | 2 | | 4 | Liu liu | 3 | | 5 | Ding qi | 3 | +----+-----------+---------+ 5 rows in set (0.03 sec) mysql> select hello(name) from teacher; +-------------------+ | hello(name) | +-------------------+ | Hello, Zhang san! | | Hello, Li si! | | Hello, Wang wu! | | Hello, Liu liu! | | Hello, Ding qi! | +-------------------+ 5 rows in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update teacher set name=hello(name); Query OK, 5 rows affected (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from teacher; +----+-------------------+---------+ | id | name | dept_id | +----+-------------------+---------+ | 1 | Hello, Zhang san! | 1 | | 2 | Hello, Li si! | 1 | | 3 | Hello, Wang wu! | 2 | | 4 | Hello, Liu liu! | 3 | | 5 | Hello, Ding qi! | 3 | +----+-------------------+---------+ 5 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> delimiter // mysql> create procedure simpleproc2() -> SELECT COUNT(*) FROM students where sid > 1; -> // Query OK, 0 rows affected (0.05 sec) mysql> delimiter ;
通过begin…end将这多个SQL语句包含在一起，Begin…end语句通常出现在存储过程、函数和触发器中，其中可以包含一个或多个语句，每个语句用;号隔开
mysql> delimiter // mysql> create procedure simpleproc2() -> SELECT COUNT(*) FROM students where sid > 1; -> SELECT COUNT(*) FROM students where sid > 2; -> // Query OK, 0 rows affected (0.09 sec) +----------+ | COUNT(*) | +----------+ | 6 | +----------+ 1 row in set (0.09 sec) mysql> delimiter ; mysql> call simpleproc2(); +----------+ | COUNT(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show create procedure simpleproc2; +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`() SELECT COUNT(*) FROM students where sid > 1; | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) mysql> drop procedure simpleproc2; Query OK, 0 rows affected (0.13 sec) mysql> delimiter // mysql> create procedure simpleproc2() -> BEGIN -> SELECT COUNT(*) FROM students where sid > 1; -> SELECT COUNT(*) FROM students where sid > 2; -> END -> // Query OK, 0 rows affected (0.06 sec) mysql> delimiter ; mysql> call simpleproc2(); +----------+ | COUNT(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) +----------+ | COUNT(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show create procedure simpleproc2; +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`() BEGIN SELECT COUNT(*) FROM students where sid > 1; SELECT COUNT(*) FROM students where sid > 2; END | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) mysql> use information_schema ; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from routines where routine_schema='course' -> ; +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+ | SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED | LAST_ALTERED | SQL_MODE | ROUTINE_COMMENT | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION | +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+ | hello | def | course | hello | FUNCTION | char | 50 | 200 | NULL | NULL | NULL | utf8mb4 | utf8mb4_0900_ai_ci | char(50) | SQL | RETURN CONCAT('Hello, ',s,'!') | NULL | SQL | SQL | NO | CONTAINS SQL | NULL | DEFINER | 2019-04-01 10:24:44 | 2019-04-01 10:24:44 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | simpleproc | def | course | simpleproc | PROCEDURE | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | SQL | BEGIN SELECT COUNT(*) INTO param2 FROM students where sid > param1; END | NULL | SQL | SQL | NO | CONTAINS SQL | NULL | DEFINER | 2019-04-01 10:05:28 | 2019-04-01 10:05:28 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | simpleproc2 | def | course | simpleproc2 | PROCEDURE | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | SQL | BEGIN SELECT COUNT(*) FROM students where sid > 1; SELECT COUNT(*) FROM students where sid > 2; END | NULL | SQL | SQL | NO | CONTAINS SQL | NULL | DEFINER | 2019-04-01 10:38:17 | 2019-04-01 10:38:17 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.00 sec) mysql> select routine_name from routines where routine_schema='course' -> ; +--------------+ | ROUTINE_NAME | +--------------+ | hello | | simpleproc | | simpleproc2 | +--------------+ 3 rows in set (0.00 sec) mysql> select routine_name,routine_type from routines where routine_schema='course'; +--------------+--------------+ | ROUTINE_NAME | ROUTINE_TYPE | +--------------+--------------+ | hello | FUNCTION | | simpleproc | PROCEDURE | | simpleproc2 | PROCEDURE | +--------------+--------------+ 3 rows in set (0.00 sec) mysql> use course; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
标签label可以加在begin…end语句以及loop, repeat和while语句中通过iterate和leave来控制流程，iterate表示返回指定标签位置，leave表示跳出标签
Database changed mysql> delimiter // mysql> CREATE PROCEDURE doiterate(IN p1 INT, OUT p2 int) -> BEGIN -> label1: LOOP -> SET p1 = p1 + 1; -> IF p1 < 10 THEN ITERATE label1; END IF; -> LEAVE label1; -> END LOOP label1; -> set p2=p1; -> END; -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> delimiter ; mysql> call doiterate(1,@x); Query OK, 0 rows affected (0.00 sec) mysql> select @x; +------+ | @x | +------+ | 10 | +------+ 1 row in set (0.00 sec)
Drop procedure/function语句用来删除指定名称的存储过程或函数
mysql> drop procedure simpleproc;
Query OK, 0 rows affected (0.16 sec)
Declare语句通常用来声明本地变量、游标、条件或者handler
Declare语句只允许出现在begin … end语句中而且必须出现在第一行
Declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler
mysql> delimiter // mysql> create procedure simpleproc(OUT param2 INT) -> BEGIN -> declare n int default 10; #必须在第一行 -> SELECT COUNT(*) INTO param2 FROM students where sid > n; -> END // Query OK, 0 rows affected (0.03 sec) mysql> delimiter ; mysql> call simpleproc(@b); Query OK, 1 row affected (0.00 sec) mysql> select @b; +------+ | @b | +------+ | 0 | +------+ 1 row in set (0.00 sec) mysql> select n; ERROR 1054 (42S22): Unknown column 'n' in 'field list' mysql> desc students; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | sid | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(64) | YES | | NULL | | | gender | varchar(12) | YES | | NULL | | | dept_id | int(11) | YES | MUL | NULL | | +---------+-------------+------+-----+---------+----------------+ 4 rows in set (0.31 sec) mysql> select * from students; +-----+--------+--------+---------+ | sid | sname | gender | dept_id | +-----+--------+--------+---------+ | 1 | Andrew | 0 | 1 | | 2 | Andy | 0 | 1 | | 3 | Bob | 0 | 1 | | 4 | Ruth | 1 | 2 | | 5 | Mike | 0 | 2 | | 6 | John | 0 | 3 | | 7 | Cindy | 1 | 3 | | 8 | Susan | 1 | 3 | +-----+--------+--------+---------+ 8 rows in set (0.00 sec) mysql> delimiter // mysql> CREATE PROCEDURE sp1 (v_sid int) -> BEGIN -> DECLARE xname VARCHAR(64) DEFAULT 'bob'; -> DECLARE xgender INT; -> SELECT sname, gender INTO xname, xgender -> FROM students WHERE sid= v_sid; -> SELECT xname,xgender; -> END; -> // Query OK, 0 rows affected (0.04 sec) mysql> delimiter ; mysql> call sp1(1); +--------+---------+ | xname | xgender | +--------+---------+ | Andrew | 0 | +--------+---------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> call sp1(2); +-------+---------+ | xname | xgender | +-------+---------+ | Andy | 0 | +-------+---------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select case gender when 0 then 'male' when 1 then 'female' else 'unknown' end from students; +------------------------------------------------------------------------+ | case gender when 0 then 'male' when 1 then 'female' else 'unknown' end | +------------------------------------------------------------------------+ | male | | male | | male | | female | | male | | male | | female | | female | +------------------------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> select gender,case gender when 0 then 'male' when 1 then 'female' else 'unknown' end from students; +--------+------------------------------------------------------------------------+ | gender | case gender when 0 then 'male' when 1 then 'female' else 'unknown' end | +--------+------------------------------------------------------------------------+ | 0 | male | | 0 | male | | 0 | male | | 1 | female | | 0 | male | | 0 | male | | 1 | female | | 1 | female | +--------+------------------------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> select gender,case when gender>0 then 'male' when gender>1 then 'female' else 'unknown' end from students; +--------+-------------------------------------------------------------------------------+ | gender | case when gender>0 then 'male' when gender>1 then 'female' else 'unknown' end | +--------+-------------------------------------------------------------------------------+ | 0 | unknown | | 0 | unknown | | 0 | unknown | | 1 | male | | 0 | unknown | | 0 | unknown | | 1 | male | | 1 | male | +--------+-------------------------------------------------------------------------------+ 8 rows in set (0.00 sec)
本地变量可以通过declare语句进行声明
声明后的变量可以通过select … into var_list进行赋值，或者通过
set语句赋值，或者通过定义游标并使用fetch … into var_list赋值
声明的变量作用范围为被声明的begin … end语句块之间
声明的变量和被引用的数据表中的字段名要区分开来
第一个语句中case_value与后面各句的when_value依次做相等的对比，如果碰到相等的，则执行对应的后面的statement_list，否则接着对比，
如果都没有匹配，则执行else后面的statement_list
第二个语句中当search_condition满足true/1的结果时，则执行对应的statement_list，否则执行else对应的statement_list
mysql> delimiter // mysql> CREATE PROCEDURE exp_case(v_sid int) -> BEGIN -> DECLARE v INT DEFAULT 1; -> select gender into v from students where sid=v_sid; -> CASE v -> WHEN 0 THEN update students set gender=1 where sid=v_sid; -> WHEN 1 THEN update students set gender=0 where sid=v_sid; -> ELSE -> update students set gender=-1 where sid=v_sid; -> END CASE; -> END; -> // Query OK, 0 rows affected (0.33 sec) mysql> delimiter ; mysql> select * from students where sid in (1,2); +-----+--------+--------+---------+ | sid | sname | gender | dept_id | +-----+--------+--------+---------+ | 1 | Andrew | 0 | 1 | | 2 | Andy | 0 | 1 | +-----+--------+--------+---------+ 2 rows in set (0.00 sec) mysql> call exp_case(1); Query OK, 1 row affected (0.03 sec) mysql> call exp_case(2); Query OK, 1 row affected (0.04 sec) mysql> select * from students; +-----+--------+--------+---------+ | sid | sname | gender | dept_id | +-----+--------+--------+---------+ | 1 | Andrew | 1 | 1 | | 2 | Andy | 1 | 1 | | 3 | Bob | 0 | 1 | | 4 | Ruth | 1 | 2 | | 5 | Mike | 0 | 2 | | 6 | John | 0 | 3 | | 7 | Cindy | 1 | 3 | | 8 | Susan | 1 | 3 | +-----+--------+--------+---------+ 8 rows in set (0.00 sec) mysql> call exp_case(1); Query OK, 1 row affected (0.06 sec) mysql> call exp_case(2); Query OK, 1 row affected (0.02 sec) mysql> select * from students; +-----+--------+--------+---------+ | sid | sname | gender | dept_id | +-----+--------+--------+---------+ | 1 | Andrew | 0 | 1 | | 2 | Andy | 0 | 1 | | 3 | Bob | 0 | 1 | | 4 | Ruth | 1 | 2 | | 5 | Mike | 0 | 2 | | 6 | John | 0 | 3 | | 7 | Cindy | 1 | 3 | | 8 | Susan | 1 | 3 | +-----+--------+--------+---------+ 8 rows in set (0.00 sec) 另外的写法 delimiter // CREATE PROCEDURE exp_case2(v_sid int) BEGIN DECLARE v INT DEFAULT 1; select gender into v from students where sid=v_sid; CASE WHEN v=0 THEN update students set gender=1 where sid=v_sid; WHEN v=1 THEN update students set gender=0 where sid=v_sid; ELSE update students set gender=-1 where sid=v_sid; END CASE; END; // delimiter ;
MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的流程控制语句，另外return语句也是函数中的特定流程控制语句
IF语句在存储过程或函数中表明了基础的条件选择语句IF语句中如果search_condition满足true/1的条件，则执行对应的statement_list，否则再判断elseif中的search_condition是否满足
true/1的条件，如果都不满足则执行else中的statement_list语句
mysql> DELIMITER // mysql> CREATE FUNCTION SimpleCompare(n INT, m INT) -> RETURNS VARCHAR(20) -> BEGIN -> DECLARE s VARCHAR(20); -> IF n > m THEN SET s = '>'; -> ELSEIF n = m THEN SET s = '='; -> ELSE SET s = '<'; -> END IF; -> SET s = CONCAT(n, ' ', s, ' ', m); -> RETURN s; -> END // Query OK, 0 rows affected (0.07 sec) mysql> DELIMITER ; mysql> select SimpleCompare(1,2); +--------------------+ | SimpleCompare(1,2) | +--------------------+ | 1 < 2 | +--------------------+ 1 row in set (0.00 sec) mysql> select SimpleCompare(3,2); +--------------------+ | SimpleCompare(3,2) | +--------------------+ | 3 > 2 | +--------------------+ 1 row in set (0.00 sec) mysql> select SimpleCompare(3,3); +--------------------+ | SimpleCompare(3,3) | +--------------------+ | 3 = 3 | +--------------------+ 1 row in set (0.00 sec) mysql> DELIMITER // mysql> CREATE FUNCTION VerboseCompare (n INT, m INT) -> RETURNS VARCHAR(50) -> BEGIN -> DECLARE s VARCHAR(50); -> IF n = m THEN SET s = 'equals'; -> ELSE -> IF n > m THEN SET s = 'greater'; -> ELSE SET s = 'less'; -> END IF; -> SET s = CONCAT('is ', s, ' than'); -> END IF; -> SET s = CONCAT(n, ' ', s, ' ', m, '.'); -> RETURN s; -> END // Query OK, 0 rows affected (0.08 sec) mysql> DELIMITER ; mysql> select VerboseCompare(1,2); +---------------------+ | VerboseCompare(1,2) | +---------------------+ | 1 is less than 2. | +---------------------+ 1 row in set (0.00 sec) mysql> select VerboseCompare(2,2); +---------------------+ | VerboseCompare(2,2) | +---------------------+ | 2 equals 2. | +---------------------+ 1 row in set (0.00 sec)
repeat语句是存储过程或函数中表达循环执行的一种方式
Repeat语句中statement_list一直重复执行直到search_condition条件满足
Statement_list可以包含一个或多个SQL语句
mysql> delimiter // mysql> CREATE PROCEDURE dorepeat(p1 INT) -> BEGIN -> SET @x = 0; -> REPEAT -> SET @x = @x + 1; -> UNTIL @x > p1 END REPEAT; -> END -> // Query OK, 0 rows affected (0.04 sec) mysql> delimiter ; mysql> call dorepeat(10); Query OK, 0 rows affected (0.00 sec) mysql> select @x; +------+ | @x | +------+ | 11 | +------+ 1 row in set (0.01 sec)
while语句是存储过程或函数中表达循环执行的一种方式
当search_condition返回为true时，则循环执行statement_list中的语句，直到search_condition的结果返回为false
mysql> DELIMITER // mysql> CREATE PROCEDURE dowhile() -> BEGIN -> DECLARE v1 INT DEFAULT 5; -> WHILE v1 > 0 DO -> update students set gender=-1 where sid=v1; -> SET v1 = v1 - 1; -> END WHILE; -> END; -> // Query OK, 0 rows affected (0.07 sec) mysql> DELIMITER ; mysql> select * from students; +-----+--------+--------+---------+ | sid | sname | gender | dept_id | +-----+--------+--------+---------+ | 1 | Andrew | 0 | 1 | | 2 | Andy | 0 | 1 | | 3 | Bob | 0 | 1 | | 4 | Ruth | 1 | 2 | | 5 | Mike | 0 | 2 | | 6 | John | 0 | 3 | | 7 | Cindy | 1 | 3 | | 8 | Susan | 1 | 3 | +-----+--------+--------+---------+ 8 rows in set (0.01 sec) mysql> call dowhile(); Query OK, 1 row affected (0.02 sec) mysql> select * from students; +-----+--------+--------+---------+ | sid | sname | gender | dept_id | +-----+--------+--------+---------+ | 1 | Andrew | -1 | 1 | | 2 | Andy | -1 | 1 | | 3 | Bob | -1 | 1 | | 4 | Ruth | -1 | 2 | | 5 | Mike | -1 | 2 | | 6 | John | 0 | 3 | | 7 | Cindy | 1 | 3 | | 8 | Susan | 1 | 3 | +-----+--------+--------+---------+ 8 rows in set (0.00 sec) 在函数中必须要有至少一个return语句，当有多个return语句时则表明函数有多种退出的方式 mysql> delimiter // mysql> create function doreturn() -> returns int -> begin -> select gender into @a from students where sid=1; -> if @a=1 then return 1; -> elseif @a=0 then return 0; -> else return 999; -> end if; -> end; -> // Query OK, 0 rows affected (0.06 sec) mysql> delimiter ; mysql> select doreturn(); +------------+ | doreturn() | +------------+ | 999 | +------------+ 1 row in set (0.00 sec)
