MySql存储过程、数据库函数创建、触发器创建

一、Mysql存储过程

1. 什么是存储过程

• 一组可编程的函数，完成特定功能的SQL语句集
• 经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数（需要时）来调用执行。
• 存储过程是具有名字的一段代码，用来完成一个特定的功能。

2.为什么使用存储过程

• 存储过程试讲重复性很高的一些操作，封装到一个存储过程中，简化对SQL的调用
• 存储过程可以进行批量处理，提升效率。
• 存储过程可以统一接口，确保数据安全。

3. 存储过程有哪些优点？

• 提高了SQL语句的重用性、共享性和可移植性

• 能够提高执行速度，减少网络的流量

• 存储过程和函数可以作为一中安全机制来利用

4.存储过程的注意事项

• 需要用户具有更高的技能和丰富的经验
• 在编写这些存储过程时，需要创建这些数据对象的权限。

5.创建存储过程

5.1 创建存储过程

5.2 查看存储过程状态信息

语法

SHOW PROCEDURE STATUS LIKE 'procedures';

5.3 查看存储过程定义信息

SHOW CREATE PROCEDURE procedures_name;

5.4 修改存储过程

ALTER PROCEDURE procedure_name [characteristic......];

注意：该语法用于修改存储过程的某些特征，比如读写权限，如要修改存储过程的内容，可以先删除改存储过程，再重新创建。

5.5 删除存储过程语句

DROP PROCEDURE procedure_name;

5.6 存储过程的语句结构

DELIMITER $
BEGIN
......
END $
DELIMITER ;

6.创建存储过程实例

创建一个存储过程，查询所有读者的信息。

DELIMITER $
CREATE PROCEDURE p1 ()
COMMENT '查询所有读者的信息'
	BEGIN
	SELECT * FROM T_READER;
	END $
DELIMITER ;

调用存储过程

CALL P1();

7. 创建参数的存储过程

7.1三种参数类型

• 输入参数：IN

  表示调用者向过程传入值（传入值可以是常量或变量）

• 输出参数：OUT

  表示过程向调用者传出值（可以返回多个值，传出值只能是变量）

• 输入输出参数：INOUT

  表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

7.2 输入参数实例

创建带输入参数的存储过程，查询某个读者ID对应的读者姓名。

DELIMITER $
CREATE PROCEDURE p_in(in pid CHAR(6))
COMMENT '查询某个ID对应的读者姓名'
BEGIN
SELECT reader_name
FROM t_reader
WHERE read_id = pid;
END $
DELIMITER ;

call p_in('0921-9274-0609');

7.3 输出参数实例

创建一个带输出参数的存储过程，查询图书的最高价格，最低价格和平均价格

DELIMITER $
CREATE PROCEDURE p_out
(out para_main float, out para_max float, out para_avg float)
COMMENT "查询图书的最高价格，最低价格，平均价格"
BEGIN
	SELECT min(book_price),
		   max(book_price),
		   avg(book_price)
		   into pare_main,pare_max,pare_avg FROM t_book;
     END $
DELIMITER ;

p_out函数调用

CALL p_out(@para_min, @para_max, @para_avg);
SELECT @para_min, @para_max, @para_avg;

7.4 输入输出参数实例

创建一个待输入输出参数的存储过程，对基本书的价格增加10元

DELIMITER $
CREATE PROCEDURE p_inout(inout price float)
COMMENT '对基本书的价格增加10元'
BEGIN
	SET price=price + 10;
END $
DELIMITER ;

调用p_inout存储过程

SET @cu_price=5.5;
CALL p_inout(@cu_price);
SELECT (@cu_price);

8. 创建带控制条件的存储过程

1. 循环条件案例
2. IF分支条件案例
3. CASE分支条件案例

8.1 while循环条件实例

循环条件

• while
• repeat
• loop

1.创建一个存储过程，计算100以内的所有整数之和。

DELILITER $
CREATE PROCEDURE p2(OUT total int)
BEGIN
DECLARE num int DEFAULT 0;              --初始化num为0
SET total = 0;                          --初始化total为0
WHERE num <= 100 DO                     --while循环
	SET num = num + 1;                  --自增
	SET total = total + num;
END WHILE;
END $

CALL p2(@sum);                           --会话变量
SELECT @sum;

8.2 IF分支语句实例

创建一个存储过程，比较两个读者的年龄大小

DELIMITER $
CREATE PROCEDURE compare_age
(OUT age1 INT, OUT age2 INT, IN name1 VARCHAR(50), IN name2 VARCHAR(50), OUT result CHAR(20))
BEGIN
	SELECT year INTO age1 FROM t_reader WHERE reader_name = name1;
	SELECT year INTO age2 FROM t_reader WHERE reader_name = name2;
	IF age1 > age2 THEN
		SET result = CONCAT(name1, '的年龄大于' ,name2);
	ELSEIF age1 < age2 THEN
		SET result = CONCAT(name1, '的年龄小于', name2);
	ELSE
		SET result = CONCAT(name1, '的年龄等于', name2)
END IF;
END $
DELIMITER ;

调用函数

CALL compare_age(@age1, @age2, '小花', '小明', @result);
SELECT @age1,@age2,@result;

8.3 CASE分支语句

创建一个存储过程，判断书的价格。

DELIMITER $
CREATE PROCEDURE proc_price
(IN b_name VARCHAR(50), OUT price FLOAT, OUT result VARCHAR(10))
BEGIN
	SELECT book_price INTO price
	FROM t_book
	WHERE book_name = bname;
	CASE
		WHEN price>=100 THEN SET result='昂贵'；
		WHEN price<=50 THEN SET result='便宜'；
		WHEN price is null THEN SET result='无此书籍';
		ELSE SET result='中等'；
	END CASE;
END $
DEMILITER;

调用函数

CALL proc_price('云计算书籍', @price, @b);
SELECT @price, @b;

9.存储过程主要关键字

英文	中文	英文	创建
CREATE	创建	IN	输入
PROCEDURE	存储过程	OUT	输出
CALL	调用	INOUT	输入输出
ALTER	修改	DROP	删除

二、创建函数

在MySql中，当系统提供的函数不能或不方便满足要求时，我们是可以自己创建自定义函数的。

1、创建函数的语法格式

1.1 语法

create function func_name(parameters)  --parameters为函数的参数列表
returns type                           --返回的数据类型
body					             --函数体

2、案例讲解

创建一个函数，功能室根据图书的ID查找该书的副本量；

delimiter $
create function func_bookcopy(bookid CHAR(17))
returns int(3)
begin
	return(
    select book_copy from t_book where isbn = bookid
    );
end $
delimiter ;

select func_bookcopy('978-7-04-034406')

三、触发器（trigger）操作

1、触发器概述

1.1 什么是触发器

在数据表中发生了某个事件，这件事通常指的是插入操作，删除操作或者是更新操作，MySql数据库系统会自动触发预先编写好的若干条SL语句

1.2 触发器的特点及作用

1.2.1 特点

具有原子性，即所有的SQL语句作为一个整体，要么全部执行，要么全部不执行。

1.2.2 作用

保证数据的完整性，起到约束的作用。

2、触发器的类型

2.1 插入触发器

2.2 删除触发器

2.3 更新触发器

3、触发器的SQL语句

3.1 创建触发器的语句

语法

create trigger trigger_name trigger_time
trigger_event
on table_name for each row [trigger_order]
trigger_body

trigger_time 指的是触发器的执行时间 after befor

trigger_event是指的触发器的触发事件

For each row表示，在表中对任意一条记录进行操作都会触发

table_name表示出发事件作用在哪张表上

trigger_body表示触发器要执行的SQL语句

insert ——插入触发器 update——更新触发器 delete——删除触发器

3.2 查看触发器

语法

show triggers;
show create trigger trigger_name;

show triggers;——查看所有触发器

show create trigger 后面跟trigger_name表示对一个具体的触发器进行详细的查看

4.触发器的工作原理

4.1 触发器主要依赖于MySql数据库系统中提供的两张临时表

NEW

OLD

引用触发器中发生变化的记录内容

1、在insert类型的触发器中，用NEW来临时存储插入的新数据。

2、在delete类型的触发器中，用old来临时存储被删除的源数据

3、在update触发器中，用old表来保存修改之前的原数据，用new表保存修改之后的数据。

5. 删除触发器

delete trigger trigger_name;

6. 触发器术语

英文	中文	英文	中文
CREATE	创建	INSERT	插入
TRIGGER	触发器	UPDATE	输出
DROP	删除	DELETE	删除
BEFORE	在。。。。之前	AFTER	在。。。之后

7.触发器使用案例

7.1 插入触发器

7.1.1

每次想图书表t_book表中插入一条记录后，自动向日志表t_log表中插入一条记录

create table t_log(
logno int auto_increment primary key,
tname varchar(20),
logtime datetime
);

create trigger trigger_log
after insert            --表示插入后出发触发器
on t_book1 for each row
insert into t_log(tname, logtime) values('t_book', now())

--向表t_book1中插入数据
insert into t_book1(isbn, book_name) values ('978-7-115', 'mysql数据开发与管理');
--查看日志表内容
select * from t_log;

7.1.2

创建一个t_bor_record表的插入触发器，当向t_bor_record表中插入一条数据时，该记录对应的图书的库存量自动-1，对应的读者借阅量自动+1.

create trigger trigger_insert2
after insert
on  t_bor_record for each row
begin
	update t_reader set reader_borrowtotal = reader_borrowtotal + 1
	where reader_id = new.reader_id;
	update t_book set book_inventory = book_inventory-1
	where isbn = new.isbn;
	end
	--执行一条插入语句，测试
	insert into t_bor_record(reader_id, isbn) values ('101103', '978-7-115');