1、前言 存储过程(Stored Procedure),是一组为了完成特定功能的SQL 语句,集经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数,如果该存储过程带有参数来执行。 简单的说就是专门干一件事一段sql语句。可以由数据库自己去调用,也可以由程序去调用。 存储过程的优点:
- 存储过程和函数是数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。
- 存储过程和函数是在MySQL服务器中存储和执行的,可以减少客户端和服务器的数据传输。
- 存储过程和存储函数一样,都是由SQL语句和过程式语句所组成的代码片段,并且可以被应用程序和其它SQL语句调用。
- 存储函数不能拥有输出参数,因为存储函数自身就是输出参数;而存储过程可以拥有输出参数。
- 存储函数可以直接对存储函数进行调用,而不需要使用call语句;而对存储过程的调用,则需要使用call语句。
- 存储函数中必须包含一条return语句,但是却不准包含在存储过程中。
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;
标签:语句,存储,name,Stored,Procedure,employee,过程,光标 From: https://www.cnblogs.com/liujiarui/p/17670725.html