MySQL存储过程入门教程
存储过程介绍
存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程可由应用程序通过一个调用来执行，而且允许用户声明变量 。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。
存储过程和函数的区别
函数往往作为公式使用，存储过程作为完成某种功能使用。
函数分为表值函数跟标量函数。表值函数是经过一些sql语句方法最后返回一张表，标量函数是经过一些sql语句方法最后返回一个值。
函数可以在select语句中直接使用，而过程不能。
存储过程的优点
作为存储过程，有以下这些优点：
(1) 减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。
(2) 执行速度更快。存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接中读取。
(3) 更强的安全性。存储过程是通过向用户授予权限(而不是基于表)，它们可以提供对特定数据的访问，提高代码安全，比如防止 SQL注入。
(4) 业务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高
当然存储过程也有一些缺点，比如：
(1) 可移植性方面：当从一种数据库迁移到另外一种数据库时，不少的存储过程的编写要进行部分修改。
(2) 存储过程需要花费一定的学习时间去学习，比如学习其语法等。
在MySQL中，推荐使用MySQL Query Browswer(http://dev.mysql.com/doc/query-browser/en/)这个工具去进行存储过程的开发和管理。下面分步骤来学习MYSQL中的存储过程。
1、定义存储过程的结束符
在存储过程中，通常要输入很多SQL语句，而SQL语句中每个语句以分号来结束，因此要告诉存储过程，什么位置是意味着整个存储过程结束，所以我们在编写存储过程前，先定义分隔符，我们这里定义//为分隔符，我们使用DELIMITER //这样的语法，就可以定义结束符了，当然你可以自己定义其他喜欢的符号。
2、如何创建存储过程
下面先看下一个简单的例子，代码如下：
复制代码
DELIMITER //
CREATEPROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
SELECT'Hello World !';
END//
复制代码
下面讲解下存储过程的组成部分：
1) 首先在定义好终结符后，使用CREATE PROCEDURE+存储过程名的方法创建存储过程，LANGUAGE选项指定了使用的语言，这里默认是使用SQL。
2) DETERMINISTIC关键词的作用是，当确定每次的存储过程的输入和输出都是相同的内容时，可以使用该关键词，否则默认为NOT DETERMINISTIC。
3) SQL SECURITY关键词，是表示调用时检查用户的权限。当值为INVOKER时，表示是用户调用该存储过程时检查，默认为DEFINER,即创建存储过程时检查。
4) COMMENT部分是存储过程的注释说明部分。
5) 在BEGIN END部分中，是存储过程的主体部分。
3、调用存储过程的方法
调用存储过程的方法很简单，只需要使用call命令即可，后面跟要调用存储过程的名称及输入的变量列表，比如：
CALL stored_procedure_name (param1, param2, ....)
CALL procedure1(10 , 'string parameter' , @parameter_var);
4、修改和删除存储过程
可以用ALTER的语法去修改存储过程的主要特征和参数，要修改其存储过程的主体部分的话，必须要先删除然后再重建。比如下面修改存储过程num_from_employee的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。代码执行如下：
ALTER PROCEDURE num_from_employee
MODIFIES SQL DATA SQL SECURITY INVOKER ;
而删除存储过程的语法为使用DROP关键词即可。如下
DROP PROCEDURE IF EXISTS p2;
5、存储过程的参数
下面来学习下存储过程中的参数，先看下存储过程中的参数形式，如下：
CREATE PROCEDURE proc1 () 这个存储过程中是空的参数列表
CREATE PROCEDURE proc1 (IN varname DATA-TYPE) 这个存储过程中有一个输出参数，名称为varname，后面是跟数据类型DATA-TYPE,IN参数是默认的，因此可以省略不写
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE) 这个存储过程中varname为输出参数
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) 这个存储过程中，varname既是输入参数也是输出参数
下面具体看个例子，首先是IN输入参数的例子，如下：
DELIMITER //
CREATE PROCEDURE `proc_IN` (IN var1 INT)
BEGIN
SELECT var1 + 2 AS result;
END//
输出OUT参数例子如下：
DELIMITER //
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
BEGIN
SET var1 = 'This is a test';
END //
IN-OUT的例子：
DELIMITER //
CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
BEGIN
SET var1 = var1 * 2;
END //
6、如何定义变量
下面讲解下MySQL 5存储过程中，如何定义变量。必须显式地在存储过程的一开始声明变量，并指出它们的数据类型，一但声明了变量后，就可以在存储过程中使用，定义变量的语法如下：
DECLARE varname DATA-TYPE DEFAULT defaultvalue
举例说明：
DECLARE a, b INT DEFAULT 5;
DECLARE str VARCHAR(50);
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT;
一旦定义好变量，就可以在存储过程中对其进行赋初值,并进行各类相关的操作，比如：
复制代码
DELIMITER //
CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))
BEGIN
DECLARE a, b INT DEFAULT 5;
DECLARE str VARCHAR(50);
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT;
INSERT INTO table1 VALUES (a);
SET str = 'I am a string';
SELECT CONCAT(str,paramstr), today FROM table2 WHERE b>=5;
END //
复制代码
7、MYSQL存储过程的语法结构
MYSQL存储过程中支持IF，CASE，ITERATE，LEAVE LOOP，WHILE和REPEAT等语法结构和语句，在本文中，着重介绍IF,CASE和WHILE语法，因为它们使用的最为广泛。
IF 语句
if语句使用的是if…then end if的语法结构，例子如下：
复制代码
DELIMITER //
CREATE PROCEDURE `proc_IF` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
IF variable1 = 0 THEN
SELECT variable1;
END IF;
IF param1 = 0 THEN
SELECT 'Parameter value = 0';
ELSE
SELECT 'Parameter value <= 0';
END IF;
END //
复制代码
CASE语句
当有很多IF语句时，就应该考虑使用CASE语句了，它是多分支选择语句，有两种写法：
第一种写法：
复制代码
DELIMITER //
CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
CASE variable1
WHEN 0 THEN
INSERT INTO table1 VALUES (param1);
WHEN 1 THEN
INSERT INTO table1 VALUES (variable1);
ELSE
INSERT INTO table1 VALUES (99);
END CASE;
END //
复制代码
另外一种写法：
复制代码
DELIMITER //
CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
CASE
WHEN variable1 = 0 THEN
INSERT INTO table1 VALUES (param1);
WHEN variable1 = 1 THEN
INSERT INTO table1 VALUES (variable1);
ELSE
INSERT INTO table1 VALUES (99);
END CASE;
END //
复制代码
WHILE语句
WHILE语句跟普通编程语言中的while语句差不多，例子如下：
复制代码
DELIMITER //
CREATE PROCEDURE `proc_WHILE` (IN param1 INT)
BEGIN
DECLARE variable1, variable2 INT;
SET variable1 = 0;
WHILE variable1
INSERT INTO table1 VALUES (param1);
SELECT COUNT(*) INTO variable2 FROM table1;
SET variable1 = variable1 + 1;
END WHILE;
END //
复制代码
8、MYSQL存储过程中的游标
MySQL中的游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。MySQL中的游标的语法如下：
DECLARE cursor-name CURSOR FOR SELECT ...; /* 声明一个游标，名称为cursor-name,并用CURSOR FOR SELECT*/
DECLARE CONTINUE HANDLER FOR NOT FOUND /*指定当遍历完结果集后，游标如何继续处理*/
OPEN cursor-name; /*打开游标 */
FETCH cursor-name INTO variable [, variable]; /* 将变量赋值给游标*/
CLOSE cursor-name; /*使用后关闭游标*/
一个具体的例子如下：
复制代码
DELIMITER //
CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
BEGIN
DECLARE a, b, c INT;
DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur1;
SET b = 0;
SET c = 0;
WHILE b = 0 DO
FETCH cur1 INTO a;
IF b = 0 THEN
SET c = c + a;
END IF;
END WHILE;
CLOSE cur1;
SET param1 = c;
END //
复制代码
其中，DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
表示将从table1表中选取col1列的内容放到游标curl中，即每次游标遍历的结果都放在curl中，要注意游标只能向前遍历，而不能向后，并且注意，游标不能更新，最后关闭游标。
代码示例:
CREATE PROCEDURE `PRO_OPERATION`()
BEGIN
DECLARE new_followers VARCHAR (20);
DECLARE cancel VARCHAR (20);
DECLARE total VARCHAR (20);
DECLARE invoice_total VARCHAR (20);
DECLARE data_time VARCHAR (20);
DECLARE visitor_count INT;
DECLARE error_msg VARCHAR(100);
/**定义异常SQLEXCEPTION **/
DECLARE
EXIT HANDLER FOR SQLEXCEPTION,
NOT FOUND
BEGIN
SET error_msg = 'SQLEXCEPTION';
INSERT INTO log_info (
pk_log,
pro_name,
log_msg,
create_time
)
VALUES
(
UUID_SHORT(),
'PRO_OPERATION',
error_msg,
SYSDATE()
);
END;
-- 查询新增关注人数、取关人数、累计关注人数
SELECT
wxc.new,
wxc.cancel ,
wxc.total,
wxc.create_time INTO new_followers,cancel,total,data_time
FROM
wx_concern wxc
WHERE
wxc.mp_type = '1'
AND
DATE_FORMAT(wxc.create_time, '%Y%m%d') = DATE_FORMAT(
date_sub(curdate(), INTERVAL 1 DAY),
'%Y%m%d'
);
/** 查询发票总数量 **/
SELECT yesterday.yesterday_total - vorgestern.total INTO invoice_total
FROM
(
SELECT
wn.jd_num + wn.gm_num + wn.mdl_num + wn.nojd_num as yesterday_total
FROM
wx_invoice_num wn
WHERE
wn.mp_type = '1'
AND DATE_FORMAT(wn.create_time, '%Y%m%d') = DATE_FORMAT(
date_sub(curdate(), INTERVAL 1 DAY),
'%Y%m%d')
)
yesterday ,
(
SELECT
wn.jd_num + wn.gm_num + wn.mdl_num + wn.nojd_num as total
FROM
wx_invoice_num wn
WHERE
wn.mp_type = '1'
AND DATE_FORMAT(wn.create_time, '%Y%m%d') = DATE_FORMAT(
date_sub(curdate(), INTERVAL 2 DAY),
'%Y%m%d')
)vorgestern WHERE
DATE_FORMAT(yesterday.create_time, '%Y%m%d') - DATE_FORMAT(vorgestern.create_time, '%Y%m%d') = '1' ;
/**
查询uv总数
**/
SELECT
MAX(et.visitor_count) INTO visitor_count
FROM
ele_baidu_toppage et
WHERE
et.url= "https://wdfp.5ifapiao.com/my-invoice/html/public/footer.html"
AND
DATE_FORMAT(et.count_time, '%Y%m%d') = DATE_FORMAT(
date_sub(curdate(), INTERVAL 1 DAY),
'%Y%m%d'
);
/** 插入今日数据 **/
INSERT INTO wx_operational (
pk_operational,
new_followers,
cancel,
total,
invoice_total,
visitor_count,
data_time,
create_time
)
VALUE
(
UUID_SHORT(),
new_followers,
cancel,
total,
invoice_total,
visitor_count,
data_time,
NOW()
);
COMMIT;
END
循环游标存储过程
注意事项:
这可能是Mysql的一个BUG，在游标范围内字段名称应该不能和变量名称重复，而Mysql是不区分大小写的，所以即使字段名称是大写，变量名称是小写，也会被认为是同一个字符串。
游标使用会多执行一个 需要提前定义一个判断
如果值为空的时候 将标识定义为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
--定义名称
CREATE PROCEDURE `PRO_INVOICE_TYPE`()
BEGIN
DECLARE invo_data_time VARCHAR (20);
DECLARE invo_kpxmsl int (12);
DECLARE invo_fpzl VARCHAR (5);
DECLARE error_msg VARCHAR(100);
DECLARE done INT DEFAULT 0; -- 自定义控制游标循环变量,默认0
/** 查询今日发票数据**/
declare inv_num cursor for(
SELECT
create_time AS data_time,
kpxmsl,
fpzl
FROM
`myinvoice-new`.invoice_info
WHERE
DATE_FORMAT(create_time, '%Y%m%d') = DATE_FORMAT(
date_sub(curdate(), INTERVAL 1 DAY),
'%Y%m%d'
) GROUP BY fpzl
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
/** 插入今日数据 **/
OPEN inv_num; -- 打开游标
myLoop: LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到
FETCH inv_num INTO invo_data_time,invo_kpxmsl,invo_fpzl;
IF done THEN -- 判断是否继续循环
LEAVE myLoop; -- 结束循环
END IF;
INSERT INTO `report`.`wx_invoice_type`
(
pk_invoice_type,
kpxmsl,
fpzl,
data_time,
create_time
)
VALUES
(
UUID(),
invo_kpxmsl,
invo_fpzl,
invo_data_time,
NOW()
);
COMMIT; -- 提交事务
END LOOP myLoop; -- 结束自定义循环体
CLOSE inv_num; -- 关闭游标
END
注意:触发器编写完成后,需要查看定时器是否执行
触发器 :
DROP EVENT IF EXISTS EVENT_PRO_INVOICE_TYPE;
CREATE EVENT EVENT_PRO_INVOICE_TYPE
--设置时间 多久执行一次 从什么时间开始
ON SCHEDULE EVERY 3 minute STARTS '2018-06-14 19:40:00'
-- 从什么时间开始 STARTS now()
DO CALL PRO_INVOICE_TYPE();-- 设置定时启动哪个存储过程 call后是存储过程名称
EVERY 后面跟
可以选 1 second秒，3 minute分，5 hour时，9 day天，1 month月，1 quarter（季度），1 year 年
触发器不执行/不允许执行
set global event_scheduler = on; 开启 OFF 关闭
show variables like 'event_scheduler'; 查看定时器是否允许执行