一、存储过程

1.1、定义

• 存储过程是用户定义的一系列sql语句的集合，完成一系列的数据操作，从而节省网络传输所需要的时间。

1.2、优点

• 增强SQL语句的功能和灵活性实现较快的执行速度减少网络流量

1.3、语法

①、创建

CREATE
[ DEFINER={ user I CURRENT_USER}]
PROCEDURE sp_name([ proc_parameterL..….J])
[ characteristic..1 routine_body 

proc_parameter:
[ INIOUTIINOUT] param_name type

②、参数

• IN，表赤该参数的值必须在调用存储过程时指定

• OUT，表示该参数的值可以被存储过程改变，并且可以返回

• INOUT，表示该参数的调用时指定，并且可以被改变和返回

③、特性

• COMMENT：注释

• CONTAINS SQL：包含SQL语句，但不包含读或写数据的语句

• NO SQL：不包含SQL语句

• READS SQL DATA：包含读数据的语句

• MODIFIES SQL DATA：包含写数据的语句

• SQL SECURITY{DEFINER |INVOKER}指明谁有权限来执行

④、过程体

• 过程体由合法的SQL语句构成；

• 过程体可以是任意SQL语句；

• 过程体如果为复合结构则使用BEGIN...END语句；

• 复合结构可以包含声明，循环，控制结构；
  

⑤、创建不带参数的存储过程

CREATE _PROCEDURE Sp1() SELECT UERSION();

调用存储过程

CALL sp_name([ parameterL...J])
CALL sp_name[0]

CALL sp1;

CALL sp1();

⑥、创建带有 IN 类型参数的存储过程

DELIMITER//
CREATE PROCEDURE remoueUserById（IN id INT UNSIGNED）
BEGIN 
DELETE FROM test WHERE id=id；
END
//

DELIMITER;

CALL remoueUserById(3);

注：参数的名字不能和记录表中的名字相同

数据库执行 id=id 认为是两个字段。

命令中有一个 id=id，数据库区分不开你这些 ID 中的具体含义，它默认为该数据表的 id 字段。所以数据表中记录全部被删除

create procedure remoueUserById(IN p_id INT UNSIGNED)
BEGIN 
DELETE FROM test WHERE id=p_id; 
END
//

remoueUserById(24);

⑦、创建带有 IN 和 OUT 类型参数的存储过程

CREATE PROCEDURE remoueUserAndReturnUserNums(IN p_id INT UNSIGNED，OUT use rname INT UNSIGNED)
DELETE FROM test WHERE id=p_id;
SELECT count（id）FROM test INTO username;
;
//

CALL remoueUserAndReturnUserNums(25，@nums);

select @nums;

注：以 @ 符号开头的字符序列是指用户变量，也就是用户在 MySQL 客户端定义的变量，一般用在 begin end 块中，对存储过程传入的参数进行声明、定义等创建带有多个 OUT 类型参数的存储过程。

⑧、创建带有多个 OUT 类型参数的存储过程

CREATE PROCEDURE remoueUserByAgeAndReturnInfos（IN p_age SHALLINT UNSIGNED
，OUT delUsers INT UNSIGNED，OUT userCounts SHALLINT UNSIGNED）
BEGIN
DELETE FROM users WHERE age=p_age；
SELECT ROW_COUNT（）INTO delUsers；返回删除的用户数和剩余的用户数
SELECT COUNT（id）FROM test INTO userCounts；
END
//

CALL remoueUserByAgeAndReturnInfos(41，@a，@b);

⑨、修改存储过程

只能修改简单的特性，不能修改过程体。如果想修改过程体，只能删除存储过程再创建。

ALTER PROCEDURE sp_name [ characteristic...…J 
COMMENT 'string'
I{ CONTAINS SQL I NO SQL I READS SQL DATA I MODIFIES SQL DAT 
I SQL SECURITY { DEFINER IINVOKER}

⑩、删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

1.4、创建“事件”执行“存储过程”

①、创建表

create table test( 
    id int auto_increment not null, 
    endtime datetime, 
    primary key(id) 
);

②、插入数据

insert into test values("1","2016-6-7 14:20:52");

③、创建存储过程

CREATE PROCEDURE p_test () 
BEGIN 
update examinfo SET endtime = now() WHERE id = 1; 
END;

④、 创建事件 e_test

CREATE EVENT if not exists e_test 
on schedule every 30 second 
on completion preserve 
do call p_test();

⑤、开始事件

将事件计划开启: set global event_scheduler=1;

查看event是否开启: show variables like "%sche%"; 
关闭事件任务: alter event e_test ON COMPLETION PRESERVE DISABLE; 
开户事件任务: alter event e_test ON COMPLETION PRESERVE ENABLE;

⑥、运行查询结果即可出现想要的结果

二、自定义函数

2.1、定义

• 函数在语句中调用，一般用来完成一些特定的数据库本身没有的函数的功能。

• 用户自定义函数（user-defined function，UDF）是一种对 MySQL 扩展的途径，其用法与内置函数相同。

• 自定义函数的两个必要条件：（1）参数参数最多1024个（2）返回值

• 函数可以返回任意类型的值，同样可以接收这些类型的参数
  

2.2、语法

①、创建

CREATE FUNCTION function_name 
RETURNS 
{STRINGIINTEGERIREALIDECIMAL} 
routine_body

函数体：

• （1）函数体由合法的SQL语句构成；

• （2）函数体可以是简单的SELECT或INSERT语句；

• （3）函数体如果为复合结构则使用BEGIN..END语句；

• （4）复合结构可以包含声明，循环，控制结构

创建不带参数的自定义函数

CREATE FUNCTION f1（）RETURNS UARCHAR（30）
RETURN DATE_FORMAT（NO（），'年%m月2d日H点：i分%s秒'）;

SELECT f1();

创建带有参数的自定义函数

CREATE FUNCTION F2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2)UNSIGNED
RETURN(num1+num2)/2;

SELECT f2(10,25);

创建具有复合结函数的自定义函数

DELIMITER //
SELECT UERSION();
//

delimiter修改结束符号为：//

CREATE FUNCTION adduser（username UARCHAR（20））
RETURNS INT UNSIGNED
BEGIN
INSERT test（username）UALUES（username）；
RETURN LAST_INSERT_ID（）；
END

有两条语句需要执行，需要添加begin end

SELECT adduser('Rose)//

②、删除

DROP FUNCTION [IF EXISTS] function_name

三、存储过程和函数

• 存储过程实现的功能要复杂一些；而函数的针对性更强

• 存储过程可以返回多个值；函数只能有一个返回值

• 存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现。

3.1、参数和返回值

• 函数必须指定返回值，且参数默认为IN类型。

• 存储过程没返回值，参数可以是 IN,OUT,IN OUT类型，有的人可能会理解成OUT 也算是返回值。

3.2、调用方式

• 函数 select my_fun();

• 过程 call my_pro( ) ;

3.3、DEMO

DELIMITER $$
DROP FUNCTION IF EXISTS my_fun$$
CREATE
    FUNCTION my_fun(a INT(2),b INT(2))
    RETURNS INT(4)
    BEGIN
    DECLARE sum_ INT(2) DEFAULT 0;
    SET sum_ = a + b;
    RETURN sum_;
    END$$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS my_pro$$
CREATE
    PROCEDURE my_pro(IN a INT(2),IN b INT(2) ,OUT c INT(2))
    BEGIN
    SET c = a + b;
    END$$
DELIMITER ;

调用

mysql> call my_pro(1,2,@c);
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @c;
+------+
| @c   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)
 
mysql> select my_fun(1,2);
+-------------+
| my_fun(1,2) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

四、统计每日访问量

利用 mysql 的定时任务于零点自动统计当天访问人数，保存到另一张表visit_count

1、首先新建一张 visit 表

2、新建表 visit_count

3、创建 mysql 存储过程：saveVisitNum

CREATE DEFINER=`root`@`localhost` PROCEDURE `saveVistNum`()
BEGIN
 INSERT INTO visit_count (nums,created_time) VALUES((SELECT COUNT(id) from visit_day),
(select date_sub(curdate(),interval 1 day)));
 DELETE FROM visit_day;
 ALTER TABLE visit_day AUTO_INCREMENT =1;
END

注释：由于当天零点统计的是昨天的访问人数，所以时间必须为：select date_sub(curdate(),interval 1 day)

4、创建 mysql 事件 saveVisitNum 于每天零点执行步骤2中的函数

CREATE DEFINER=`root`@`localhost` EVENT `saveVistNum` ON SCHEDULE EVERY 1 DAY 
STARTS '2016-07-18 00:00:00' ON COMPLETION PRESERVE ENABLE DO CALL saveVistNum()

5、开启事件

（1）开启：set global event_scheduler=1;

（2）查看是否开启成功：show variables like '%sche%';