存 储 过 程
为什么使用存储过程
业务流程复杂:业务复杂时,SQL语句相互依赖, 顺序执行;
频繁访问数据库:每条SQL语句都需单独连接和访 问数据库;
先编译后执行:SQL语句的执行需要先编译。
什么是存储过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存 储在数据库中,经过第一次编译后,再次调用不需要重复编译,用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数)来执行它。
SQL语句集+预编译+特定功能
创建存储过程
语法:
CREATE PROCEDURE
( [ [IN |OUT |INOUT ] 参数名 数据类型…])
BEGIN
DECLARE 变量 变量类型
END
说明:
• '[ ]'内容不是必须的;
• in:表示入参;
• out:表示返回值;
• inout:表示即是入参又是返回值。
# 存储过程
-- 带参数的存储过程
delimiter $$
create procedure proc_test2(
in a int, -- in 只入参(值传递)
out b int, -- out 只出参(无)
inout c int -- inout 出入参(引用传递)
)
BEGIN
set a=a+1;
SET b=b+100;
set c=c+1000;
END $$
delimiter;
-- 环境变量 @ 局部环境变量 @@ 全局环境变量
set @x=10; -- 10
set @y=20; -- null
set @z=30; -- 1030
select @x,@y,@z;
call proc_test2(@x,@y,@z)
select @x, @y, @z;
存储过程的操作
调用存储过程: CALL 存储过程名 [参数名]
查看存储过程: SELECT * FROM information_schema.ROUTINES
WHERE routine_schema= ‘库名’
删除存储过程: DROP PROCEDURE 存储过程名;
call proc_test2(@x,@y,@z)
SELECT * FROM information_schema.ROUTINES
WHERE routine_schema = 'myschool'
drop procedure proc_stuPage;
面试题: 写一个分页的存储过程:
-- 面试题
-- 分页
-- 删除存储过程
drop procedure proc_stuPage;
delimiter $$
create procedure proc_stuPage(
in curpage int,
in sizepage int,
out stucount int,
out pagecount int
)
begin
declare cp int; -- 定义变量
set cp = (curpage-1)*sizepage;
select count(*) from student into stucount; -- 把值放入变量里,类型必须一样
set pagecount = ceiling(stucount / sizepage);
select * from student limit cp,sizepage;
end $$
delimiter ;
set @a = 0;
set @b = 0;
call proc_stuPage(2,3,@a,@b);
select @a,@b
存储过程与函数的区别
语法: 关键字不同,存储过程是procedure, 函数是function;
执行: 存储过程可以独立执行,函数必须依 赖表达式的调用;
返回值: 存储过程可以定义多个返回结果, 函数只有一个返回值;
功能: 函数不易做复杂的业务逻辑,但是存 储过程可以。
存储过程的缺陷
维护性 : 存储过程的维护成本高,修 改调试较为麻烦。
移植性 : 大多数关系型数据库的存储过程 存在细微差异。
协作性 : 没有相关的版本控制或者IDE,团 队中对于存储过程的使用大多是 依赖文档。
触 发 器
什么是触发器
触发器是数据库中针对数据库表操作触发的 特殊的存储过程。
创建触发器
语法:
CREATE TRIGGER 数据库名.触发器名
BEFORE/AFTER -- 触发顺序
INSERT/UPDATE/DELETE – 触发事件
ON 数据库.表名 -- 事件表
FOR EACH ROW
BEGIN
触发器内容 -- 事件出发后要写的语句
END$$
说明:
• 触发器触发时间分为Before和After两种;
• 主要针对表的增删改操作,可单独指定,也可全部指定。
• 查看所有的触发器 SELECT DISTINCT EVENT_OBJECT_TABLE FROM information_schema.`TRIGGERS` WHERE EVENT_OBJECT_SCHEMA=‘数据库名'
# 触发器
-- 创建触发器
-- delimiter $$
-- create trigger 触发名【trug_xxx】
-- before/after insert/update /delete
-- on 表名 for each row
-- begin
-- 触发后执行的一组sql语句
-- end $$
-- delimiter;
-- 删除学生 sid 为 1 在此之前把学生成绩删除
delimiter $$
create trigger trig_delstu_delsc
before delete on student for each row
begin
-- old 已经存在的数据 、 new 还不存在的数据
delete from sc where sid = old.sid;
end $$
delimiter ;
delete from student where sid = 1;
select * from student;
select * from sc;
[SQL] delete from student where sid = 2;
受影响的行: 1
时间: 0.008ms
触发器的查看
查看所有的触发器:
SELECT * FROM information_schema.`TRIGGERS`
WHERE trigger_schema = ‘库名'
删除触发器:
DROP TRIGGER 触发器名
SELECT * FROM information_schema.`TRIGGERS`
WHERE trigger_schema = 'myschool'
drop trigger trig_delstu_delsc
存储过程和触发器的区别
语法 关键字不同,存储 过程是procedure, 触发器是trigger
执行 存储过程需要调用才执 行,触发器自动执行;
返回值 存储过程可以定义返回值, 但是触发器没有返回值;
功能 存储过程是一组特定功能的 SQL语句,触发器则是SQL语 句前后执行,本身不影 响原功能。