-- 存储过程

-- 相当于java方法,javascript的function
-- 含义,一组预先编译好的sql语句的集合
-- 意义:
-- 1.提高代码复用性
-- 2.简化操作
-- 3.减少了编译次数并且减少了和数据库服务器的链接次数,提高了效率

-- 创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的sql语句)
END

注意:参数列表包含三部分
1.参数模式(IN,OUT和INOUT)
2.参数名
3.参数类型
举例:IN stuname VARCHAR(20)

-- IN 该参数需要调用方传入值
-- OUT 该参数可以输出,也就是返回值
-- INOUT 又输入又输出

-- 如果存储过程只有一句话,BEGIN 和 END 可以省略
-- 存储过程体中的每条语句要求必须以 ; 结尾
-- 存储过程的结尾可以使用 DELIMITER 重写

mysql> select * from user;
+----+-----------+---------+----------+
| id | name      | fund    | phone    |
+----+-----------+---------+----------+
|  1 | 张三      |   18000 | 12345678 |
|  2 | 李四      |    6000 | 22345678 |
|  3 | 周润发    | 2000000 | 32345678 |
|  5 | 周星星    | 8880000 | 42345678 |
+----+-----------+---------+----------+
4 rows in set (0.00 sec)

-- 创建无参存储过程

DELIMITER  ^  -- 修改sql结束符号 
CREATE PROCEDURE myp1() 
BEGIN
    INSERT INTO user (name,fund,phone) VALUES('张学友','5699999','52345678'),
    ('郭富城','23532999','52345678'),
    ('刘德华','53374299','52345678'),
    ('陈奕迅','12356999','52345678'),
    ('张家辉','324526999','52345678');
END ^
DELIMITER ;  -- 修改回来

-- 调用存储过程

mysql> call myp1();
Query OK, 5 rows affected (0.05 sec)

mysql> select * from user;
+----+-----------+-----------+----------+
| id | name      | fund      | phone    |
+----+-----------+-----------+----------+
|  1 | 张三      |     18000 | 12345678 |
|  2 | 李四      |      6000 | 22345678 |
|  3 | 周润发    |   2000000 | 32345678 |
|  5 | 周星星    |   8880000 | 42345678 |
|  6 | 张学友    |   5699999 | 52345678 |
|  7 | 郭富城    |  23532999 | 52345678 |
|  8 | 刘德华    |  53374299 | 52345678 |
|  9 | 陈奕迅    |  12356999 | 52345678 |
| 10 | 张家辉    | 324526999 | 52345678 |
+----+-----------+-----------+----------+
9 rows in set (0.00 sec)

-- 创建带 in 的存储过程

mysql> DELIMITER $
mysql> CREATE PROCEDURE myp2(IN user_name VARCHAR(20))
    -> BEGIN
    ->      select * from user where name = user_name;
    -> END $
Query OK, 0 rows affected (0.10 sec)
mysql> DELIMITER ;

mysql> call myp2('周润发');
+----+-----------+---------+----------+
| id | name      | fund    | phone    |
+----+-----------+---------+----------+
|  3 | 周润发    | 2000000 | 32345678 |
+----+-----------+---------+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 创建带 out 的存储过程

DELIMITER ^
CREATE PROCEDURE myp3(IN user_name VARCHAR(20),OUT user_fund INT)
BEGIN
    select fund INTO user_fund from user where name =user_name;
END ^
DELIMITER ;

mysql> call myp3('周润发',@u_fund);
Query OK, 1 row affected, 1 warning (0.00 sec)

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

-- 创建带 inout 的存储过程

mysql> DELIMITER ^
mysql> CREATE PROCEDURE myp4(INOUT a INT,INOUT b INT)
    -> BEGIN
    -> SET a = a * 2;
    -> SET b = b * 2;
    -> END $
Query OK, 0 rows affected (0.44 sec)

mysql> DELIMITER ;
mysql> SET @m = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @n = 20;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL myp4(@m,@n);
Query OK, 0 rows affected (0.00 sec)

mysql> select @m,@n;
+------+------+
| @m   | @n   |
+------+------+
|   20 |   40 |
+------+------+
1 row in set (0.00 sec)

-- 删除存储过程

DROP PROCEDURE 存储过程名;
mysql> DROP PROCEDURE myp1;
Query OK, 0 rows affected (0.07 sec)

-- 查看存储过程信息

mysql> SHOW CREATE PROCEDURE myp2;
+-----------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure                                                                                                                       | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| myp2      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `myp2`(IN user_name VARCHAR(20))
BEGIN
     select * from user where name = user_name;
END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-----------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

-- 触发器相当于js事件

-- 在进行插入,更新和删除之前或之后触发

-- 触发器内自带俩个属性 NEW和OLD

-- 触发器类型 :INSERT型触发器; UPDATE型触发器; DELETE型触发器;

-- 语法结构

CREATE TRIGGER trigger_name
before/after insert/update/delete
on table_name
for each row
BEGIN
触发的sql语句;
END

-- 使用情景之一:修改记录日志
-- 先创建个日志表

mysql> CREATE TABLE user_update_logs(
    -> id int(11) primary key auto_increment,
    -> operation VARCHAR(20) not null,
    -> operation_time DATETIME not null,
    -> operation_id int(11) not null,
    -> operation_params VARCHAR(500)
    -> );
Query OK, 0 rows affected, 2 warnings (0.28 sec)

mysql> desc user_update_logs;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int          | NO   | PRI | NULL    | auto_increment |
| operation        | varchar(20)  | NO   |     | NULL    |                |
| operation_time   | datetime     | NO   |     | NULL    |                |
| operation_id     | int          | NO   |     | NULL    |                |
| operation_params | varchar(500) | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

-- 创建一个插入后触发的触发器

DELIMITER ^
CREATE TRIGGER insert_trigger
after insert
on user
for each row
BEGIN
    INSERT INTO user_update_logs(operation,operation_time,operation_id,operation_params) 
    VALUES('INSERT',now(),new.id,concat('新增了一名id为',new.id,'名字为',new.name,'资产为',new.fund,'电话号码为',new.phone,'的用户'));
END ^

mysql>INSERT INTO user VALUES(6,'古天乐',300000000,72345678)$

mysql> select * from user$
   
+----+-----------+-----------+----------+
| id | name      | fund      | phone    |
+----+-----------+-----------+----------+
|  1 | 张三      |     18000 | 12345678 |
|  2 | 李四      |      6000 | 22345678 |
|  3 | 周润发    |   2000000 | 32345678 |
|  5 | 周星星    |   8880000 | 42345678 |
|  6 | 张学友    |   5699999 | 52345678 |
|  7 | 郭富城    |  23532999 | 52345678 |
|  8 | 刘德华    |  53374299 | 52345678 |
|  9 | 陈奕迅    |  12356999 | 52345678 |
| 10 | 张家辉    | 324526999 | 52345678 |
| 11 | 古天乐    | 300000000 | 72345678 |
+----+-----------+-----------+----------+
10 rows in set (0.00 sec)

mysql> select * from user_update_logs$
+----+-----------+---------------------+--------------+--------------------------------------------------------------------------------------------+
| id | operation | operation_time      | operation_id | operation_params                                                                           |
+----+-----------+---------------------+--------------+--------------------------------------------------------------------------------------------+
|  1 | INSERT    | 2020-10-17 16:09:58 |           11 | 新增了一名id为11名字为古天乐资产为300000000电话号码为72345678的用户                        |
+----+-----------+---------------------+--------------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 另外的 update 和 delete 触发器与上面这个大同小异,注意NEW和OLD的使用即可

-- 查看触发器

show triggers$ 
show triggers\G$  --格式化  看得更舒服

mysql> show triggers\G$
*************************** 1. row ***************************
             Trigger: insert_trigger
               Event: INSERT
               Table: user
           Statement: BEGIN
INSERT INTO user_update_logs(operation,operation_time,operation_id,operation_params)
VALUES('INSERT',now(),new.id,concat('新增了一名id为',new.id,'名字为',new.name,'资产为',new.fund,'电话号码为',new.phone,'的用户'));
END
              Timing: AFTER
             Created: 2020-10-17 16:08:12.02
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,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.00 sec)

-- 删除触发器

mysql> drop trigger insert_trigger$
Query OK, 0 rows affected (0.07 sec)

数据库基础(5)存储过程与触发器

数据库基础(5)存储过程与触发器

-- 存储过程

-- 创建语法

-- 触发器相当于js事件

数据库基础(5)存储过程与触发器

-- 存储过程

-- 创建语法

-- 触发器 相当于js事件

-- 触发器相当于js事件