MySQL学习笔记九：存储过程，存储函数，触发器

存储过程

1.存储过程由一组特定功能的SQL语句组成，对于大型应用程序优势较大，相对不使用存储过程，具有以下优点：

　　a.性能提高，因为存储过程是预编译的，只需编译一次，以后调用就不须再编译

　　b.重用性提高，可以“一次编写，随便调用”

　　c.安全性提高，可以一定程度上防止SQL注入，还可以使用权限控制

　　d.减少网络流量，提高网站访问速度

2.存储过程的建立，使用create procedure语句，语法如下

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

示例：

use test;
delimiter $$　　//改变语句分隔符
create procedure test(in a varchar(10),out b int)  //in 输入参数 out 输出 inout 输入输出
begin

select concat('hello ',a);
select count(1) into b from tb_6;

end$$

delimiter ;
call test('world',@c);  //使用call调用存储过程
select @c;

参数为INOUT示例

delimiter $$

create procedure test(inout b int)
begin

select b;
set b=10;
select b;
select count(1) into b from tb_5;
 
end$$

delimiter ;
set @b = 100;
call test(@b);
select @b;

--------------------------结果-----------------------
mysql> call test(@b);
+------+
| b    |
+------+
|   20 |
+------+
1 row in set (0.00 sec)

+------+
| b    |
+------+
|   10 |
+------+
1 row in set (0.01 sec)

Query OK, 1 row affected (0.04 sec)

mysql> select @b;
+--------+
| @b     |
+--------+
| 120832 |
+--------+
1 row in set (0.00 sec)

变量的定义，赋值，以及用户变量示例

delimiter $$

create procedure test()
begin

declare i int default 1;  --变量的定义
select i;
set i = 100;  --变量的赋值
select i;
 
end$$

delimiter ;
//结果
mysql> call test();
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| i    |
+------+
|  100 |
+------+
1 row in set (0.01 sec)

mysql> set @t='hehe';  --用户变量
Query OK, 0 rows affected (0.00 sec)

mysql> select @t;
+------+
| @t   |
+------+
| hehe |
+------+
1 row in set (0.00 sec)

3.存储过程的更改，删除

ALTER PROCEDURE proc_name [characteristic ...]

drop procedure proc_name

4.查看存储过程的信息

mysql> select name from mysql.proc where db='test';
+----------+
| name     |
+----------+
| add_user |
| partPage |
| test     |
+----------+
3 rows in set (0.00 sec)

--或者使用show create procedure proc_name

5.存储过程的控制结构

if-then-else-end if语句

delimiter $$
create procedure proc(in i int,out res varchar(30))
begin

declare j int;
set j = 20;
if i<j then
set res = 'i is smaller than j';
else
set res = 'i is bigger than j';
end if;

end
--结果
mysql> call proc(30,@ret);
Query OK, 0 rows affected (0.00 sec)

mysql> select @ret;
+--------------------+
| @ret               |
+--------------------+
| i is bigger than j |
+--------------------+
1 row in set (0.00 sec)

case-when-then-end case语句

delimiter $$
create procedure proc(in i int,out res varchar(30))
begin
case i
when 1 then
set res='星期一';
when 1 then
set res='星期二';
when 1 then
set res='星期三';
when 1 then
set res='星期四';
when 1 then
set res='星期五';
when 1 then
set res='星期六';
when 1 then
set res='星期日';
end case;
end$$
--结果
mysql> call proc(1,@ret);
Query OK, 0 rows affected (0.03 sec)

mysql> select @ret;
+--------+
| @ret   |
+--------+
| 星期一 |
+--------+
1 row in set (0.03 sec)

循环while-do-end while语句

DELIMITER $$

CREATE  PROCEDURE `add_user`()
begin
declare i int default 1;
while i<=50 do

insert into users(userName,email,passwd,registion_date) values(concat('zhumuxian',i),concat('zhumuxian',i,'@163.com'),sha1('1234567'),now());
set i=i+1;

end while;

end

示例，分页存储过程

CREATE  PROCEDURE `partPage`(in perPageNum int ,in currentPage int)
begin

declare perNum int default 10;
declare curPage int default 1;
declare start0 int ;

set perNum=perPageNum;
set curPage=currentPage;

set start0=(curPage-1)*perNum;
select user_id,userName,email,registion_date from users limit start0,perNum;

end

存储函数与存储过程在语法上有点类似，不同点：

　　存储函数需要返回一个类型，且函数体中必须要有一个有效的return语句。

　　它们调用的方式不同，过程使用call调用，函数使用select调用。

　　函数只能返回一个结果值，而过程可以返回一个或多个结果集，等等。

存储函数的创建，语法如下

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

创建示例

delimiter $$
create function getDate() 
returns varchar(255)
begin
declare str varchar(255) default '0000-00-00 00:00:00';
set str = date_format(now(),'%Y年%m月%d日 %H时%i分%S秒');
return str;
end $$
delimiter ;

--结果
mysql> select getDate();
+-----------------------------+
| getDate()                   |
+-----------------------------+
| 2015年04月16日 15时16分46秒 |
+-----------------------------+
1 row in set (0.08 sec)

如在创建函数时碰到ERROR 1418 (HY000)错误，可以设置log_bin_trust_function_creators=TRUE即可。

mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)

分支结构示例

delimiter $$
create function sub_str(str varchar(255),n int)
returns varchar(255)
begin
if isnull(str) then return '';
elseif char_length(str)<n then return str;
else return left(str,n);
end if;
end$$
delimiter ;

--结果
mysql> select sub_str('wozhidaole',1);
+-------------------------+
| sub_str('wozhidaole',1) |
+-------------------------+
| w                       |
+-------------------------+
1 row in set (0.00 sec)

 触发器，是一种特殊的存储过程，在特定表执行更新，插入，删除操作时触发，具有更好的数据控制能力，创建语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body

示例

delimiter $$
create trigger pp after insert on t1 for each row
begin
insert into t2 values (new.id);
end$$
delimiter ;

删除触发器

mysql> drop trigger pp;
Query OK, 0 rows affected (0.00 sec)

查看所有触发器

mysql> show triggersG
*************************** 1. row ***************************
             Trigger: pp
               Event: INSERT
               Table: t1
           Statement: begin
insert into t2 values (new.id);
end
              Timing: AFTER
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

查看某一个触发器信息

mysql> show create trigger ppG
*************************** 1. row ***************************
               Trigger: pp
              sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger pp after insert on t1 for each row
begin
insert into t2 values (new.id);
end
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> select * from information_schema.triggers where trigger_name='pp'G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: pp
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: t1
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: begin
insert into t2 values (new.id);
end
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: NULL
                  SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
1 row in set (0.09 sec)