存储过程和函数和触发器

存储过程

创建无参存储过程

mysql> delimiter $ -- 这里将语句结束符设置为 $
mysql> create procedure test1()
    -> begin
    ->   create table t_test(
    ->      id bigint,
    ->      name varchar(20)
    ->   );
    -> end $ -- 结束存储过程语句
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ; -- 将语句的结束符号恢复为分号

执行存储过程

mysql> call test1();
Query OK, 0 rows affected (0.01 sec)

创建带 in 参数的存储过程

mysql> delimiter ;
mysql> call test2();
Query OK, 3 rows affected (0.00 sec)

mysql>  delimiter $
mysql>  create procedure test3(in myId bigint)
    ->  begin
    ->     select * from t_test where id = myId;
    -> end $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> call test3(2);
+------+----------+
| id   | name     |
+------+----------+
|    2 | xiaohong |
+------+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

创建带 in 和 out 参数的存储过程

mysql> delimiter $
mysql> create procedure test4(in myId bigint, out myName varchar(20))
    -> begin
    ->    select name into myName from t_test where id = myId;
    -> end $
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> call test4(1, @myName);
Query OK, 1 row affected (0.00 sec)

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

创建 inout 参数的存储过程

mysql> delimiter $
mysql> create procedure test5(inout myName varchar(20))
    -> begin
    ->  select name into myName from t_test where name = myName;
    -> end $
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
mysql> set @myName = 'xiaoming';
Query OK, 0 rows affected (0.00 sec)

mysql> call test5(@myName);
Query OK, 1 row affected (0.00 sec)

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

查看存储过程

mysql> show create procedure test1;

删除存储过程

mysql> drop procedure test5;

函数

创建无参函数

mysql> delimiter $
mysql> create function fun_test1() returns int
    -> begin
    ->   declare a int default 0;
    ->   select count(1) into a from t_test;
    ->   return a;
    -> end $
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> select fun_test1();
+-------------+
| fun_test1() |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

创建有参函数

mysql> delimiter $
mysql> create function fun_test2(myName varchar(20)) returns int
    -> begin
    -> declare a int default 0;
    -> select id into a from t_test where name = myName;
    -> return a;
    -> end $
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql> select fun_test2('xiaogang');
+-----------------------+
| fun_test2('xiaogang') |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set (0.00 sec)

查看函数

mysql> show create function fun_test2;

删除函数

mysql> drop function fun_test2;

触发器

触发器语法

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中：

trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、 UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、 UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句 SQL 语句，或者用 BEGIN 和 END 包含的多条语句。

由此可见，可以建立 6 种触发器，即：

BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE
AFTER INSERT
AFTER UPDATE
AFTER DELETE

另外有一个限制是不能同时在一个表上建立 2 个相同类型的触发器，因此在一个表上最多建立 6 个触发器

创建触发器

mysql> delimiter $
mysql> create trigger tri_test1
    -> before insert
    -> on t_test for each row
    -> begin
    ->   declare c int;
    ->   set c = (select max(id) from t_test);
    ->   set new.id = c + 1;
    -> end $
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> delimiter ;

查看触发器

mysql> show create trigger tri_test1;

删除触发器

mysql> drop trigger tri_test1;

执行操作，触动触发器

mysql> insert into t_test(id, name) values(1, 'lisi');