存储过程Stored Procedure
1、前言
存储过程(Stored Procedure),是一组为了完成特定功能的SQL 语句，集经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数，如果该存储过程带有参数来执行。
简单的说就是专门干一件事一段sql语句。可以由数据库自己去调用，也可以由程序去调用。
存储过程的优点：
-
存储过程和函数是数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。
-
存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器的数据传输。
-
存储过程和存储函数一样，都是由SQL语句和过程式语句所组成的代码片段，并且可以被应用程序和其它SQL语句调用。
特点：
-
存储函数不能拥有输出参数，因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。
-
存储函数可以直接对存储函数进行调用，而不需要使用call语句；而对存储过程的调用，则需要使用call语句。
-
存储函数中必须包含一条return语句，但是却不准包含在存储过程中。
2、存储过程基本用法
2.1、创建存储过程
MySQL中，创建存储过程的基本形式如下：
CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN
SQL语句代码块
END
其中参数列表的形式如下：
[IN|OUT|INOUT] param_name type
其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MYSQL数据库中的任意类型。
例子：下面的语句创建一个查询tb_user表全部数据的存储过程
DROP PROCEDURE IF EXISTS sp_test;
DELIMITER //
CREATE PROCEDURE sp_test()
BEGIN
SELECT * FROM tb_user;
END //
DELIMITER ;
2.2、删除存储过程
语法： DROP PROCEDURE IF EXISTS 存储过程名; eg: DROP PROCEDURE IF EXISTS proc_employee;
这个语句被用来移除一个存储程序。不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程。
2.3、调用存储过程
语法：call demo();
CALL 存储过程名(参数列表);
注：
（1）CALL语句是用来调用一个先前用CREATE PROCEDURE创建的存储过程。
（2）CALL语句可以用声明为OUT或INOUT参数的参数给它的调用者传回值。
（3）存储过程名称后面必须加括号，哪怕该存储过程没有参数传递。
3、光标
MYSQL里叫光标，SQLSERVER里叫游标，实际上一样的。
查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。
光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。
（1）声明光标
MySQL中使用DECLARE关键字来声明光标。其语法的基本形式如下：
DECLARE cursor_name CURSOR FOR select_statement ;
其中，cursor_name参数表示光标的名称；select_statement参数表示SELECT语句的内容，返回一个用于创建光标的结果集。
【示例】下面声明一个名为cur_employee的光标。代码如下：
DECLARE cur_employee CURSOR FOR SELECT name, age FROM employee ;
上面的示例中，光标的名称为cur_employee；SELECT语句部分是从employee表中查询出name和age字段的值。
（2）打开光标
MySQL中使用OPEN关键字来打开光标。其语法的基本形式如下：
OPEN cursor_name ;
其中，cursor_name参数表示光标的名称。
【示例】下面打开一个名为cur_employee的光标，代码如下：
OPEN cur_employee ;
（3）使用光标
MySQL中使用FETCH关键字来使用光标。其语法的基本形式如下：
FETCH cursor_name INTO var_name[,var_name…] ;
其中，cursor_name参数表示光标的名称；var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。
【示例】下面使用一个名为cur_employee的光标。将查询出来的数据存入emp_name和emp_age这两个变量中，代码如下：
FETCH cur_employee INTO emp_name, emp_age ;
上面的示例中，将光标cur_employee中SELECT语句查询出来的信息存入emp_name和emp_age中。emp_name和emp_age必须在前面已经定义。
（4）关闭光标
MySQL中使用CLOSE关键字来关闭光标。其语法的基本形式如下：
CLOSE cursor_name ;
其中，cursor_name参数表示光标的名称。
【示例】 下面关闭一个名为cur_employee的光标。代码如下：
CLOSE cur_employee ;
上面的示例中，关闭了这个名称为cur_employee的光标。关闭之后就不能使用FETCH来使用光标了。
注意：MYSQL中，光标只能在存储过程和函数中使用！！
4、存储过程实例
4.1、mysql通用分页存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS pr_pager;
CREATE PROCEDURE pr_pager(
IN p_table_name VARCHAR(100), -- 表名称
IN p_fields VARCHAR(500), -- 要显示的字段
IN pagecurrent INT, -- 当前页
IN pagesize INT, -- 每页显示的记录数
IN p_where VARCHAR(500) CHARSET utf8, -- 查询条件
IN p_order VARCHAR(100), -- 排序
OUT totalcount INT -- 总记录数
)
BEGIN
IF pagesize <= 1 THEN
SET pagesize = 20;
END IF;
IF pagecurrent THEN
SET pagecurrent = 1;
END IF;
SET @startIndex = (pagecurrent-1)*pagesize;
SET @endIndex = pagesize;
SET @strsql = CONCAT('select ',p_fields,' from ',p_table_name,
CASE IFNULL(p_where,'') WHEN '' THEN '' ELSE CONCAT(' where ',p_where) END,
CASE IFNULL(p_order,'') WHEN '' THEN '' ELSE CONCAT(' order by ',p_order) END,
' limit ',@startIndex,',',@endIndex);
-- 预定义一个语句，并将它赋给stmtsql
PREPARE stmtsql FROM @strsql;
EXECUTE stmtsql;
-- 释放一个预定义语句的资源
DEALLOCATE PREPARE stmtsql;
SET @strsqlcount = CONCAT('select count(*) into @Rows_Total from ',p_table_name,
CASE IFNULL(p_where,'') WHEN '' THEN '' ELSE CONCAT(' where ',p_where) END);
PREPARE stmtsqlcount FROM @strsqlcount;
EXECUTE stmtsqlcount;
DEALLOCATE PREPARE stmtsqlcount;
SET totalcount = @Rows_Total;
-- 计算总数也可以是下面这种方法
-- SELECT COUNT(*) INTO totalcount FROM tb_user;
END //
DELIMITER ;
4.2、存储过程调用
（1）不带查询条件和排序
@totalcount表示测试出输出的参数
CALL pr_pager('t_user','id,username,birthday,sex,address',1,5,NULL,NULL,@totalcount);
SELECT @totalcount;
（2）带查询条件和排序
CALL pr_pager('t_user','id,username,birthday,sex,address',1,5,'username like \'小%\'','id asc',@totalcount);
SELECT @totalcount;
