存储过程和函数

相比Oracle, MySQL中很少使用存储过程。

1. 概述

1. 存储在数据库端的一组SQL语句集
2. 用户可以通过存储过程名和传参多次调用的程序模块
3. 存储过程的特点:

• 使用灵活，可以使用流控制语句、自定义变量等完成复杂的业务逻辑
• 提高数据安全性，屏蔽应用程序直接对表的操作，易于进行审计
• 减少网络传输
• 但是提高代码维护的复杂度，实际使用中要评估场景是否适合

1. 存储过程之流控制

流程控制	语法
if	IF search _condition THEN statement list [ELSElF search condition THEN statement list] [ELSE statement list] END IF
case	CASE case__value WHEN when_value THEN statement list [ELSE statement_list] END CASE
while	WHILE search_condition DO statement list END WHILE
repeat	REPEAT statement listUNTlL search condition END REPEAT

2. 存储过程之基本语法

3. 存储过程之实操

3.1 创建存储过程

DELIMITER //
create procedure sql_trains.proc_test1(in total int, out res int)
begin
	declare i int;
    set i=1;
    set res = 1;
    if total <= 0 then 
		set total = 1;
	end if;
    while i <= total do
		set res = res * i;
        insert into sql_trains.tbl_proc_test values (res);
        set i=i+1;
	end while;
    select max(num) from sql_trains.tbl_proc_test;
end; //
delimiter ;

其中使用delimiter定义语句结束符不再是;结尾，避免语句块录入时被误认为是单个sql。

call sql_trains.proc_test1(10, @a);
-- 获取结果
select @a;

3.2 查询存储过程

-- 查询具体存储过程状态
show procedure status like '%proc%';
-- 查询具体存储过程信息
select * from information_schema.routines where routine_schema='sql_trains';
-- 在mysql5.7之前，存储过程信息存放在 mysql.proc 表中
SELECT * FROM mysql.proc WHERE db = 'sql_trains;

4. 自定义函数

必须指明返回值。

DELIMITER //
create function sql_trains.func_test1 (total int)
returns bigint
-- 开启二进制日志需要加上这一行
DETERMINISTIC
begin 
	declare i int;
    declare res bigint;
    set i = 1;
    set res = 1;
    if total <= 0 then 
		set total = 1;
	end if;
    while i <= total do
		set res = res * i;
        set i = i + 1;
	end while;
    return res;
end; //

delimiter ;

调用函数：

select sql_trains.func_test1(3);

4. 触发器

特点是一触即发，当表上出现特定的事件时触发该程序执行，触发器类型有UPDATE/DELETE/INSERT。

4.1 语法

4.2 触发的操作

需要注意的是：

1. replace操作如果是数据已存在还会触发删除触发器
2. drop和truncate操作不会触发删除触发器

create table sql_trains.stu(
name varchar(50),
course varchar(50),
score int,
primary key (name)
);

DELIMITER //
create trigger sql_trains.trg_upd_score
before update on stu 
for each row 
begin
	if NEW.score<0 then 
		set NEW.score=0;
	elseif NEW.score>100 then 
		set NEW.score=100;
	end if;
end; //

4.3 触发器总结

1. 触发器对性能有损耗，应当非常慎重使用
2. 对于事务表，触发器执行失败则整个语句回滚
3. Row格式主从复制，触发器不会在从库上执行
4. 使用触发器时应防止递归执行

4.4 触发器高级使用

所谓物化视图：

1. 不是基于基表的虚表
2. 根据基表实际存在的实表
3. 预先计算并保存多表的链接(JOIN)或聚集(GROUPBY)等耗时较多的SQL操作结果

-- 创建物化视图
CREATE TABLE sql_trains.Orders_MV(
product name VARCHAR(30) NOT NULL,
price_sum DECIMAL(8,2) NOT NULL,
amount_sum INT NOT NULL,
price_aVg FLOAT NOT NULL,
orders_cnt INT NOT NULL,
UNIQUE INDEX (product_name)
);
-- 创建普通视图
CREATE VIEW sql_trains.v_orders AS SELECT
product_name, sum(price), sum(amount), avg(price), coynt(1) 
FROM Orders
GROUP BY product_name;

CREATE TRIGGER tgr_Orders_insert 
AFTER INSERT ON Orders 
FOR EACH ROW
BEGIN
	SET @old_price_sum = 0;
	SET @old_amount_sum = 0;
	SET @old_price_avg = 0;
	SET @old_orders_cnt = 0;
	SELECT 
		IFNULL(price_sum, 0),IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(orders_cnt, 0)
	FROM Orders_MV
	WHERE product_name = NEW.product_name
	INTO @old_price_sum, @old_amount_sum,@old_price_avg, @old_orders_cnt;

	SET @new_price_sum=@old_price_sum + NEW.price;
	SET @new_amount_sum= @old_amount_sum + NEW.amount;
	SET @new_orders_cnt = @old_orders_cnt + 1;
	SET @new_price_avg = @new_price_sum/@new_orders_cnt ;
	REPLACE INTO Orders_Mv 
    VALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt );
end;