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`()BEGINSELECT 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  Waings: 0mysql> 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`()BEGINSELECT 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 namesYou can tu off this feature to get a quicker startup with -ADatabase changedmysql> 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  | BEGINSELECT 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  | BEGINSELECT 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 namesYou can tu off this feature to get a quicker startup with -A

标签label可以加在begin…end语句以及loop, repeat和while语句中通过iterate和leave来控制流程，iterate表示返回指定标签位置，leave表示跳出标签

Database changedmysql> 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)BEGINDECLARE v INT DEFAULT 1;select gender into v from students where sid=v_sid;CASEWHEN 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;ELSEupdate students set gender=-1 where sid=v_sid;END CASE;END;//delimiter ;

MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的流程控制语句，另外retu语句也是函数中的特定流程控制语句

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)在函数中必须要有至少一个retu语句，当有多个retu语句时则表明函数有多种退出的方式mysql> delimiter //mysql> create function doretu()-> retus int-> begin->  select gender into @a from students where sid=1;->  if @a=1 then retu 1;->  elseif @a=0 then retu 0;-> else retu 999;-> end if;-> end;-> //Query OK, 0 rows affected (0.06 sec)mysql> delimiter ;mysql> select doretu();+------------+| doretu() |+------------+|999 |+------------+1 row in set (0.00 sec)