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

2023-08-04 16:18:40

存储过程

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(''),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 triggers\G

*************************** 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 pp\G

*************************** 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)
上一篇：Docker核心实现技术(命名空间&控制组&联合文件系统&Linux网络虚拟化支持)


下一篇：在Sublime Text 3中配置Python3的开发环境/Build System