Mysql流程控制语句和存储过程
一、流程控制语句
条件控制语句
1)IF(a,b,c):
a为布尔表达式，b、c为表达式语句，返回a为true时返回b的结果，a为false时返回c的结果。
eg：select id,name,if(gen=1,'男','女') as gen from user;
2)CASE WHEN [condition] THEN [result] END:
语法：
CASE
WHEN [condition1] THEN [result1]
...
WHEN [conditionn] THEN [resultn]
ELSE result(n+1)
END;
3）if else条件语句：
语法：
if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list]
end if
循环语句
4）while...end while
先检验条件，再执行循环体
while 条件 do
--循环体
end while
5） repeat ... end repeat
先执行一遍循环体，再检验条件
repeat
--循环体
until 循环条件
end repeat
6）loop ·····endloop:
loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环
LABLES 标号：
标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。
二、存储过程
2.1简介
存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象
存储过程是为了完成特定功能的sql语句集，经编译创建并保存在数据库中，用户可以通过指定存储过程的名称并给定参数来调用
优点：
存储过程可以封装，并隐藏复杂的商业逻辑
存储过程可以回传值并可以接受参数
存储过程无法使用select指令来执行，因为它是子程序，与查看表、数据表或用户定义函数不同
存储过程可以用在数据校验，强制执行商业逻辑等
确定：
存储过程往往定制化于特定的数据库上，因支持的编程语言不同，当切换到其他厂商的数据库系统时，需要重写原有存储过程
存储过程的性能调校与编写，受限于各种数据库系统
2.2创建存储过程
CREATE
[DEFINER={user | current_user}]
PROCEDURE sp_name([proc_paramter[,...]])
[characteristic ...] routine_body
proc_paramter:
[IN | OUT | INOUT] param_name type
characteristic:
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
[begin_label:] BEGIN
[statement_list]
...
END [end_label]
2.3参数
MySQL存储过程的参数有以下三种：
IN 输入参数，表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数厄值不被返回
OUT 输出参数：改制在存储过程内部被改变，并可返回
INOUT 输入输出参数：调用时指定，并可以被改变和返回
IN参数例子
OUT参数例子
INOUT参数例子
2.4变量
变量定义：
局部变量的定义一定要放在存储过程体的开始：
DECLARE variable_name [,variable_name2..] datatype [DEFAULT value];
eg: DECLARE p_int int default 4000;
变量赋值:
SET 变量名=表达式[,variable_name=expression...]
用户变量：
用户变量一般以@开头，滥用用户变量将使得sql难以管理
在MySQL客户端使用用户变量：
在存储过程使用用户变量：
三、存储过程示例
3.1 存储过程实现分页查询功能
DROP PROCEDURE IF EXISTS pr_pager;
DELIMITER //
CREATE PROCEDURE pr_pager(
IN p_table_name VARCHAR(128),
IN p_fields VARCHAR(1024),
IN p_page_size INT,
IN p_page_now INT,
IN p_order_string VARCHAR(128),
IN p_where_string VARCHAR(1024),
OUT p_out_rows INT
)
BEGIN
DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_strig CHAR(64);
SET m_begin_row = (p_page_now - 1) * p_page_size;
SET m_limit_strig = CONCAT(' LIMIT ',m_begin_row,', ',p_page_size);
SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ',p_table_name,' ',p_where_string);
SET @MAIN_STRING = CONCAT('SELECT ',p_fields,' FROM ',p_table_name,' ',p_where_string,' ',p_order_string,m_limit_strig);
PREPARE coun_stmt FROM @COUNT_STRING;
EXECUTE coun_stmt;
DEALLOCATE PREPARE coun_stmt;
SET p_out_rows = @ROWS_TOTAL;
PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;
END;
//
DELIMITER ;
PREPARE语句准备好一条SQL语句，并分配给这条SQL语句一个名字供之后调用。准备好的SQL语句通过EXECUTE命令执行，通过DEALLOCATE PREPARE命令释放掉。
3.2动态创建表
DROP PROCEDURE IF EXISTS pr_create_tb;
DELIMITER //
CREATE PROCEDURE pr_create_tb()
BEGIN
SET @sql_create_table = CONCAT(
'CREATE TABLE IF NOT EXISTS operrecord_', DATE_FORMAT(CURDATE(),'%y%m%d'),
'(',
'`oper_id` int(10) NOT NULL AUTO_INCREMENT,',
'`oper_role` int(11) NOT NULL,',
"`oper_desc` varchar(30) NOT NULL DEFAULT '',",
'PRIMARY KEY (`oper_id`)',
') ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8');
PREPARE sql_create_table FROM @sql_create_table;
EXECUTE sql_create_table;
END;
//
DELIMITER ;
动态创建表拼接sql时特别要注意引号问题。
