本文参考自 《深入浅出Mysql》
mysql存储程序相关用法
- 存储函数
存储函数将向调用者返回结果
存储函数创建实例
mysql> delimiter $ //设置$为分隔符
mysql> create function get_carname(car_id int) //创建函数 参数为car_id
-> returns varchar(100) //函数返回值
-> reads sql data
-> begin
-> return (select name from car_info where id = car_id);
-> end$ //结束符
Query OK, 0 rows affected
mysql> delimiter ;
mysql> select get_carname(100); //调用函数getcarname(100);
+------------------------------------------------+
| get_carname(100) |
+------------------------------------------------+
| 起亚K2 2012款 两厢 1.6L AT Premium纪念版111122 |
+------------------------------------------------+
1 row in set
存储函数查看
show function status;
show create function get_carname;
show function status like ‘get_car%‘;
存储函数删除
drop function get_carname;
- 存储过程
存储过程创建实例
mysql> create procedure show_name(car_id int)
-> begin
-> select name from car_info where id = car_id;
-> update car_info set name = ‘123456‘ where id = car_id;
-> end$
1304 - PROCEDURE show_name already exists
mysql> create procedure show_carname(car_id int)
-> begin
-> select name from car_info where id = car_id;
-> update car_info set name = ‘123456‘ where id = car_id;
-> end$
Query OK, 0 rows affected
mysql> call show_carname(100);
-> $
+------------------------------------------------+
| name |
+------------------------------------------------+
| 起亚K2 2012款 两厢 1.6L AT Premium纪念版111122 |
+------------------------------------------------+
1 row in set
存储过程的参数类型
IN参数：调用者把IN参数传值给过程
OUT参数：过程把值赋值给OUT参数
mysql> create procedure get_carname(in car_id int,out car_name varchar(100))
-> begin
-> select name into car_name from car_info where id = car_id;
-> end$
Query OK, 0 rows affected
mysql> delimiter ;
mysql> call get_carname(100,@car_name);
Query OK, 0 rows affected
mysql> select @car_name;
+-----------+
| @car_name |
+-----------+
| 123456 |
+-----------+
1 row in set
存储过程特征值介绍
LANGUAGE SQL 默认，routine_boyd由SQL组成
[NOT]DETERMINISTIC 指明存储过程的执行结果是否是确定的，默认不确定
CONSTAINS SQL 子程序包含SQL，但不包含读写数据的语句，默认
NO SQL 子程序中不包含SQL语句
READS SQL DATA 子程序中包含读数据的语句
MODIFIES SQL DATA 子程序中包含了写数据的语句
SQLSECURITY {DEFINER|INVOKER} 指明谁有权限执行。
DEFINER 只有定义者自己才能够执行，默认
INVOKER 表示调用者可以执行
存储过程中变量的使用
变量定义：declare procedure_temp varchar(100);
变量赋值：set procedure_temp = ‘hello‘；
也可以将查询结果赋值给变量：
select name into procedure_temp ;
mysql> create procedure procedure_test()
-> begin
-> declare continue handler for sqlstate ‘23000‘ set @x1 = 1;
-> set @x2 = 2;
-> insert into web_car_brands(name) values(‘dazhong‘);
-> set @x2 = 3;
-> end$
红色部分表示mysql执行是遇到错误、异常、警告时采取的方式，目前支持continue 和exit两种方式
错误类别包括：sqlwarning，not found ，sqlstate
存储过程中流程控制语句的使用，待完善......
存储过程中光标的使用
create procedure curser_test()
-> begin
-> declare count int;
-> declare car_name varchar(100);
-> declare cur cursor for select name from car_info where id <10; //声明光标
-> declare exit handler for not found close cur;
->
-> set @x1 = 0;
-> set @x2 = 0;
->
-> open cur; /打开光标
->
-> repeat
-> fetch cur into car_name; // fetch 光标
-> if(car_name == ‘凯迪拉克‘)
-> then set @x1 = 1;
-> else
-> set @x2 = 1;
-> end if;
-> until 0 end repeat;
-> close cur; //关闭光标
-> end$
- 触发器
当某个数据表被INSERT、DELETE、UPDATE时，触发器将自动执行。
触发器创建实例
mysql> delimiter $
mysql> create trigger trigger_test //触发器名称为trigger_test
-> after insert on car_info for each row begin //在插入操作之后执行
-> insert into web_car_brands(name) values(‘dahzong‘); //要执行的动作
-> end;
-> $
触发器被触发的语句如下：INSERT、DELETE、UPDATE
查看触发器
show triggers;
删除触发器
drop trigger trigger_test;
触发器使用注意
触发器按照BEFORE触发器、行操作、AFTER触发器的顺序执行，其中任何一部发生错误的操作都不会继续执行剩下的操作。
对于事务操作，如果期间发生错误，整个事务回滚，对于非事务，如果发生错误，那么已经执行的部分将无法回滚。
- 视图
视图创建实例
mysql> create view view_test as //创建视图view_test
-> select name from car_info where id = 100;
Query OK, 0 rows affected
查看视图
不能通过show views方式查看视图，通过show tables 可以查看到所有的表和视图
show table status like ‘view%‘;
show create view view_test;
select * from information_schema.views where table_name = ‘view_test‘; //通过INFORMATION_SCHEMA.VIEWS 查看
视图删除
drop view view_test;
mysql视图若干限制：
- from关键字后不能包含子查询
- 以下类型视图补课更新：聚合函数（SUM、MIN、MAX、COUNT）、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL
- SELECT中包含子查询
- JION
- FORM一个补课更新的视图
使用视图的优点：
- 数据独立：可以屏蔽表结果变化对用户的影响，源表增加列对视图没有影响，源表修改列，只需修改视图对应列即可
- 安全：用户只能访问视图呈现的结果集，可以限制到某个行、某个列
- 简单：用户使用视图不许关心其背后对应的表结果、关联条件、筛选条件，直接呈现对应条件的结果集
- 事件
事件调度器可以安排数据库在预订的时间执行某个事件，默认情况下事件调度器不会开启，
查看事件调度器状态： show variables like ‘event_scheduler‘;
开启事件调度器：set global event_scheduler = ON;
事件创建实例
mysql> create event expire_web_session //创建事件expire_web_session
-> on schedule every 4 hour //每四小时执行一次
-> do
-> delete from car_info where publish_date < current_timestamp - interval 1 day; //要执行的事件
Query OK, 0 rows affected
停止某个事件
mysql> alter event expire_web_session disable;
开启某个事件
alter event expire_session enable;