存储过程Stored Procedure

1、前言

 

存储过程(Stored Procedure),是一组为了完成特定功能的SQL 语句，集经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数，如果该存储过程带有参数来执行。

简单的说就是专门干一件事一段sql语句。可以由数据库自己去调用，也可以由程序去调用。

 

存储过程的优点：

 

• 存储过程和函数是数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。
• 存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器的数据传输。
• 存储过程和存储函数一样，都是由SQL语句和过程式语句所组成的代码片段，并且可以被应用程序和其它SQL语句调用。

特点：

• 存储函数不能拥有输出参数，因为存储函数自身就是输出参数；而存储过程可以拥有输出参数。
• 存储函数可以直接对存储函数进行调用，而不需要使用call语句；而对存储过程的调用，则需要使用call语句。
• 存储函数中必须包含一条return语句，但是却不准包含在存储过程中。

 

2、存储过程基本用法

 

2.1、创建存储过程

MySQL中，创建存储过程的基本形式如下：

?

1 2 3 4

CREATE PROCEDURE  存储过程名 (参数列表) BEGIN     SQL语句代码块 END

其中参数列表的形式如下：

[IN|OUT|INOUT] param_name type

 

其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MYSQL数据库中的任意类型。

 

例子：下面的语句创建一个查询tb_user表全部数据的存储过程

?

1 2 3 4 5 6 7

DROP PROCEDURE IF EXISTS sp_test; DELIMITER // CREATE PROCEDURE sp_test() BEGIN     SELECT * FROM tb_user; END // DELIMITER ;

 

2.2、删除存储过程

 

?

1 2 3 4

语法： 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关键字来声明光标。其语法的基本形式如下：

?

1	DECLARE cursor_name CURSOR FOR select_statement ;

其中，cursor_name参数表示光标的名称；select_statement参数表示SELECT语句的内容，返回一个用于创建光标的结果集。

 

【示例】下面声明一个名为cur_employee的光标。代码如下：

?

1	DECLARE cur_employee CURSOR FOR SELECT name, age FROM employee ;

上面的示例中，光标的名称为cur_employee；SELECT语句部分是从employee表中查询出name和age字段的值。

 

（2）打开光标

MySQL中使用OPEN关键字来打开光标。其语法的基本形式如下：

?

1	OPEN  cursor_name ;

其中，cursor_name参数表示光标的名称。

 

【示例】下面打开一个名为cur_employee的光标，代码如下：

?

1	OPEN  cur_employee ;

 

（3）使用光标 

MySQL中使用FETCH关键字来使用光标。其语法的基本形式如下：

?

1	FETCH cursor_name INTO var_name[,var_name…] ;

其中，cursor_name参数表示光标的名称；var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。

 

【示例】下面使用一个名为cur_employee的光标。将查询出来的数据存入emp_name和emp_age这两个变量中，代码如下：

?

1	FETCH  cur_employee INTO emp_name, emp_age ;

上面的示例中，将光标cur_employee中SELECT语句查询出来的信息存入emp_name和emp_age中。emp_name和emp_age必须在前面已经定义。

 

（4）关闭光标

MySQL中使用CLOSE关键字来关闭光标。其语法的基本形式如下：

?

1	CLOSE  cursor_name ;

其中，cursor_name参数表示光标的名称。

 

【示例】 下面关闭一个名为cur_employee的光标。代码如下：

?

1	CLOSE  cur_employee ;

上面的示例中，关闭了这个名称为cur_employee的光标。关闭之后就不能使用FETCH来使用光标了。

 

注意：MYSQL中，光标只能在存储过程和函数中使用！！

 

 

4、存储过程实例

 

4.1、mysql通用分页存储过程

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

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表示测试出输出的参数

?

1 2	CALL pr_pager('t_user','id,username,birthday,sex,address',1,5,NULL,NULL,@totalcount); SELECT @totalcount;

 

（2）带查询条件和排序

?

1 2	CALL pr_pager('t_user','id,username,birthday,sex,address',1,5,'username like \'小%\'','id asc',@totalcount); SELECT @totalcount;