Mysql的存储过程.触发器.视图

概念及优缺点

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

需要连续执行多个SQL指令，并且第二个SQL指令需要使用第一个SQL指令执行的结果作为参数，那么存储过程会非常适用

优点

存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。

使用

dual说明

存储变量的一个虚拟表

concat_ws

concat_ws(’~’,aa,bb)
拼接参数aa和bb并用~连接

局部变量及用户变量都是用set定义

用户变量相当于全局变量，可以在SQL指令以及多个存储过程中共享，在开发中建议尽呈少使用用户变量，用户变量过多会导致程序不易理解、难以维护。

局部变量

定义在存储过程中（begin和end中间）的变量，只能在存储过程内部使用

定义 declare <attr_name> < type> [default value] ;

例子

declare x int default 0;
定义的default可有可无

用户变量（全局变量）

定义的用户变量可以通过select @attrName from dual进行查询

变量的简单使用

--创建一个存储过程:计算输入参数的平方与输入参数/2的和
create procedure proc_test2(IN a int,OUT r int)
begin
declare x int default 0;  --定义x   int类型，默认值为0
declare y int default 1;
set x = a*a;
set y = a/2;
set r = x+y;
end;


set @n=1;
call proc_test2(6,@n);
select @n from dual;

这时候@n就是输出的r，然后从dual里查询@n，结果为

逻辑语句

if - then -else

if 条件 then
	-- sql
else
	-- sql
end if;

case

begin
	case a
		when 1 then
			sql
		when 2 then
			sql
		else
			sql
	end case;
end

使用存储过程实现添加

首先建了一个简单的表
这时候为这个表添加一个叫www的函数

if EXISTS是是否存在的意思 不存在就添加
in name1 VARCHAR(255),in age1 INTEGER(0) 里面 in是入参的意思

CREATE PROCEDURE www (in name1 VARCHAR(255),in age1 INTEGER(0))
	INSERT INTO test1 (`name`,`age`) VALUES (name1,age1);

运行完之后

call www ('小说','23')

表就会添加一行数据 name=小说 age=23

使用存储过程实现查询

不带参查询

先添加一个叫jia的函数

CREATE PROCEDURE jia()
	select * from test1;
	

然后直接调用

call jia()

就会查出test1表的所有

查询人数 （out）

创建存储过程并把结果赋值给c
查询学生数量

create procedure proc_test3(out	c int)
begin
select count(name) INTO c from test1;
end;

调用存储过程

call proc_test3 (@n);
select @n from dual;

带参查询人数(in,out)

–创建存储过程,根据id，查询姓名

create procedure proc_test5(in sid int(0),out sname varchar(255))
begin
select name INTO sname from test1 where id=sid;
end;

调用存储过程

set @name='';
call proc_test5(1,@name);
select @name from dual;

存储过程实例

表如下
student

books

records

业务分析

哪个学⽣借哪本书，借了多少本？
操作：
保存借书记录
修改图书库存
条件：
判断学⽣是否存在？
判断图书是否存在、库存是否充⾜？

实现业务

-- 实现借书业务：
-- 参数1： a 输⼊参数 学号
-- 参数2： b 输⼊参数 图书编号
-- 参数3： m 输⼊参数 借书的数量
-- 参数4： state 输出参数 借书的状态（1 借书成功，2 学号不存在，3 图书不存在， 4
库存不⾜）
create procedure proc_borrow_book(IN a char(4),IN b int, IN m int,OUTstate int)
begin
 declare stu_count int default 0;
 declare book_count int default 0;
 declare stock int default 0;
 -- 判断学号是否存在：根据参数 a 到学⽣信息表查询是否有stu_num=a的记录
 select count(stu_num) INTO stu_count from students where stu_num=a;
  if stu_count>0 then
  -- 学号存在
  -- 判断图书ID是否存在：根据参数b 查询图书记录总数
   select count(book_id) INTO book_count from books where book_id=b;
   if book_count >0 then
   -- 图书存在
   -- 判断图书库存是否充⾜：查询当前图书库存，然后和参数m进⾏⽐较
    select book_stock INTO stock from books where book_id=b;
    if stock >= m then
    -- 执⾏借书
    -- 操作1：在借书记录表中添加记录
     insert into records(snum,bid,borrow_num,is_return,borrow_date)values(a,b,m,0,sysdate());
     -- 操作2：修改图书库存
     update books set book_stock=stock-m where book_id=b;
     -- 借书成功
     set state=1;
  else
  -- 库存不⾜
  set state=4;
  end if; 
  else
  -- 图书不存在
  set state = 3;
  end if;
  else
   -- 不存在
   set state = 2;
  end if;
end;

– 调⽤存储过程借书

set @state=0;
call proc_borrow_book('1001',1,2,@state);
select @state from dual;

存储过程的管理

查询存储过程

方法一

show procedure status where db= ' 数据库名';

方法二

查询存储过程的创建细节

show create procedure db_test2.proc_test1;

删除存储过程

 DROP PROCEDURE if EXISTS jia;

游标

声明 定义(DECLARE) 自己取个游标名字(cursor_name) 定义为游标(CURSOR) for 语句

DECLARE cursor_name CURSOR FOR select.....

实例

declare mycursor cursor for select book_name,book_author,book_price from books;

游标使用案例

-- 游标使⽤案例
create procedure proc_test2(OUT result varchar(200))
begin
 declare bname varchar(20);
 declare bauthor varchar(20);
 declare bprice decimal(10,2);
 declare num int;
 declare i int;
 declare str varchar(50);
 -- 此查询语句执⾏之后返回的是⼀个结果集（多条记录），使⽤游标可以来遍历查询结果集
declare mycursor cursor for select book_name,book_author,book_price
from books;
 select count(1) INTO num from books;
 -- 打开游标
 open mycursor;
 
 -- 使⽤游标要结合循环语句
 set i=0;
 while i<num do
 -- 使⽤游标：提取游标当前指向的记录（提取之后，游标⾃动下移）
 FETCH mycursor INTO bname,bauthor,bprice;
 
 set i=i+1;
 -- set str=concat_ws('~',bname,bauthor,bprice);
 select concat_ws('~',bname,bauthor,bprice) INTO str;
 set result = concat_ws(',',result,str);
 end while;
 -- 关闭游标
 close mycursor;
end;
-- 案例测试
set @r = '';
call proc_test2(@r);
select @r from dual;

触发器

触发器，就是⼀种特殊的存储过程。触发器和存储过程⼀样是⼀个能够完成特定功能、存储
在数据库服务器上的SQL⽚段，但是触发器⽆需调⽤，当对数据表中的数据执⾏DML操作时
⾃动触发这个SQL⽚段的执⾏，⽆需⼿动调⽤。
在MySQL,只有执⾏insert\delete\update操作才能触发触发器的执⾏。

触发器语法及实例

语法

create trigger tri_name
<before|after> -- 定义触发时机
<insert|delete|update> -- 定义DML类型
ON <table_name>
for each row -- 声明为⾏级触发器（只要操作⼀条记录就触发触发器执⾏⼀次）
sql_statement -- 触发器操作（sql语句）

实例

-- 创建触发器：当学⽣信息表发⽣添加操作时，则向⽇志信息表中记录⼀条⽇志
create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添加',NEW.stu_num,'学⽣信息'));

查看触发器

show triggers;

删除触发器

drop trigger tri_test1;

NEW和OLD

触发器⽤于监听对数据表中数据的insert、delete、update操作，在触发器中通常处理⼀些DML的关联操作；我们可以使⽤ NEW 和 OLD 关键字在触发器中获取触发这个触发器的DML操作的数据

NEW : 在触发器中⽤于获取insert操作添加的数据、update操作修改后的记录
OLD：在触发器中⽤于获取delete操作删除前的数据、update操作修改前的数据

NEW
insert操作中：NEW表示添加的新记录

create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添
加',NEW.stu_num,'学⽣信息'));

update操作中：NEW 表示修改后的数据

-- 创建触发器 : 在监听update操作的触发器中，可以使⽤NEW获取修改后的数据
create trigger tri_test2
after update on students 
for each row
insert into stulogs(time,log_text) values(now(), concat('修改学⽣信息
为：',NEW.stu_num,NEW.stu_name));

OLD
delete操作中：OLD表示删除的记录

create trigger tri_test3
after delete on students 
for each row
insert into stulogs(time,log_text) values(now(), concat('删
除',OLD.stu_num,'学⽣信息'));

update操作中：OLD表示修改前的记录

create trigger tri_test2
after update on students 
for each row
insert into stulogs(time,log_text) values(now(), concat('将学⽣姓名从
【',OLD.stu_name,'】修改为【',NEW.stu_name,'】'));

触发器使用总结

优点

1. 触发器是⾃动执⾏的，当对触发器相关的表执⾏响应的DML操作时⽴即执⾏；
   2.触发器可以实现表中的数据的级联操作（关联操作），有利于保证数据的完整性；
   3.触发器可以对DML操作的数据进⾏更为复杂的合法性校验

缺点

1.使⽤触发器实现的业务逻辑如果出现问题将难以定位，后期维护困难；
2.⼤量使⽤触发器容易导致代码结构杂乱，增加了程序的复杂性；
3.当触发器操作的数据量⽐较⼤时，执⾏效率会⼤⼤降低；

视图

视图，就是 由数据库中⼀张表或者多张表根据特定的条件查询出得数据构造成得 虚拟表

语法

create view <view_name>
AS
select_statement

例子

-- 创建视图实例1：将学⽣表中性别为男的学⽣⽣成⼀个视图
create view view_test1
AS
select * from students where stu_gender='男';
-- 查询视图
select * from view_test1;

特性

1.查询操作：如果在数据表中添加了新的数据，⽽且这个数据满⾜创建视图时查询语句的条件，通过查询视图也可以查询出新增的数据；当删除原表中满⾜查询条件的数据时，也会从视图中删除。
2.新增数据：如果在视图中添加数据，数据会被添加到原数据表
3.删除数据：如果从视图删除数据，数据也将从原表中删除
4.修改操作：如果通过修改数据，则也将修改原数据表中的数据

操作

-- 查询视图结构
desc view_test2;

-- 删除视图
drop view view_test1;

-- 修改视图
-- ⽅式1
create OR REPLACE view view_test1
AS
select * from students where stu_gender='⼥';
-- ⽅式2
alter view view_test1
AS
select * from students where stu_gender='男';