自学内容网 自学内容网
自学内容网 > 正文

存 储 过 程 及 触 发 器

🕗 发布于 2024-07-26 20:51 数据库  

存 储 过 程

为什么使用存储过程

业务流程复杂：业务复杂时，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语 句前后执行，本身不影                  响原功能。


原文地址：https://blog.csdn.net/m0_74002833/article/details/140658618

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024