投稿
  1. 极客之音首页
  2. 技术随笔

【MySQL基础】06：存储过程与触发器

飞熊 • • 技术随笔 • 阅读 253

如果你不相信努力和时光，那么成果就会是第一个选择辜负你的。不要去否定你自己的过去，也不要用你的过去牵扯你现在的努力和对未来的展望。不是因为拥有希望你才去努力，而是去努力了，你才有可能看到希望的光芒。【MySQL基础】06：存储过程与触发器，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

🏷️当前篇为【MySQL基础】06：存储过程与触发器 🤖

🏷️更多内容：MySQL基础教程，带你零基础上手MySQL数据库！💪

🏷️欢迎点赞 👍 收藏 🌟 关注 ❤️ 留言 ✍️

存储过程与触发器

存储过程

什么是存储过程

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

存储过程的优点

  1. SQL指令无需客户端编写后通过网络传输给数据库，可以节省网络开销，同时避免了SQL指令在网络传输过程中被恶意篡改保证安全性。
  2. 存储过程经过编译存储在数据库中，调用过程时无需再次编译，提升了SQL执行的效率。
  3. 存储过程支持流程控制语句（分支，循环），可以实现更复杂的业务。

存储过程的缺点

  1. 存储过程的性能调校与撰写，受限于各种数据库系统。
  2. 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
  3. 在互联网项目中，如果需要数据库的高（连接）并发访问，使用存储过程会增加数据库的连接执行时间，造成连接开销大。

创建存储过程

创建存储过程的语法

-- IN 输入参数 OUT 输出参数 INOUT 输入输出参数
create procedure <procedureName>(IN <value_1> <dataType>,OUT <value_2> <dataType>,INOUT <value_3> <dataType>)
begin
	-- 声明变量 需要声明在存储过程的最前
    DECLARE <valueName> <dataType> default <defaultValue>;
	-- 赋值
	SET <value_3> = <value_1>;
	select <columnName> INTO <value_2> from <tableName>;
	-- 
end;

-- sample sql
create procedure computed(IN a int,IN b int,OUT c int)
begin
	declare temp int default 0;
	SET temp = a + b;
	SET c = temp * temp;
end;

调用存储过程

SET @value = <defalutValue>;
-- OUT @value
call procedureName(inValue,@value);
-- 显示变量值
select @value from dual;

set @a = 1;
set @b = 2;
set @c = 0;
call computed(@a,@b,@c);
select @c from dual;

存储过程的参数

CREATE PROCEDURE procedureName([[IN|OUT|INOUT] <valueName> <dataType>]) begin ... end;

  • IN输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

  • OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

  • INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

  • 如果过程没有参数，也必须在过程名后面写上小括号

  • 确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

-- sample sql for IN
create procedure dome1(IN a int)
begin
	select a;
end;
call dome1(1);

-- sample sql for OUT
create procedure dome2(OUT b int)
begin
	declare temp int default 10;
	SET b = temp;
end;
SET @b = 0;
call dome2(@b);
select @b from dual;

-- sample sql for INOUT
create procedure dome3(INOUT c int)
begin
	declare temp int default 10;
	select c;
	SET c = temp;
end;
SET @c = 0;
call dome3(@c);
select @c from dual;

变量

变量定义

局部变量声明一定要放在存储过程体的开始：

-- datatype 为 MySQL 的数据类型，如: int, float, date,varchar(length)
create procedure <procedureName>([[IN|OUT|INOUT] <paramName> <dataType>])
begin
	DECLARE l_int int unsigned default 4000000;  
    DECLARE l_numeric number(8,2) DEFAULT 9.95;  
    DECLARE l_date date DEFAULT '1999-12-31';  
    DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';  
    DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
end;

变量赋值

create procedure <procedureName>([[IN|OUT|INOUT] <paramName> <dataType>])
begin
	SET <paramName> = <expr>;
end;

用户变量

在MySQL客户端使用用户变量
select <defaultValue> into @a;
set @a = <defaultValue>;
select @a;
在存储过程中使用用户变量
create procedure <procedureName>()
begin
	select @a;
end;
set @a = <defaultValue>;
call <procedureName>;
在存储过程间传递全局范围的用户变量
create procedure <procedureName_1>()
begin
	select @a;
	SET @a = 'Hello';
end;

create procedure <procedureName_2>()
begin
	declare temp varchar(32) default '';
	select @a;
	select concat(@a,' World');
end;

-- sample sql
create procedure p1()
begin
	SET @a = 'Hello';
	select @a;
end;

create procedure p2()
begin
	declare temp varchar(32) default '';
	select @a;
	select concat(@a,' World');
end;
call p1();
call p2();

MySQL存储过程的控制语句

条件语句

IF … THEN … ELSE
create procedure <procedureName>()
begin
	if ... then
		-- SQL
	elseif ... then
		-- SQL
	else
		-- SQL
	end if;
end;

-- sample sql
drop procedure if exists dome1;

create procedure dome1(IN a int,IN b int ,OUT c varchar(100))
begin
	declare result int default 0;
	select a - b into result;
	if result > 0 then
		set c = 'a > b';
	elseif result < 0 then
		set c = 'a < b';
	else
		set c = 'a = b';
	end if;
end;

SET @a = 1;
SET @b = 2;
SET @r = '';
CALL dome1 ( @a, @b, @r );
SELECT @r;
CASE
create procedure <procedureName>()
begin
	declare <value_1> <dataType> default <defaultValue>;
	case <param>
	when <value_1> then
		-- SQL
	when <value_2> then
		-- SQL
	else
		-- SQL
	end case;
end;

-- sample sql
drop procedure if exists dome2;

create procedure dome2(IN a int)
begin
	case a
	when 1 then
		select 'case 1' as result;
	when 2 then
		select 'case 2' as result;
	else
		select 'case else' as result;
	end case;
end;

SET @a = 1;
CALL dome2(@a);

循环语句

while … end while
create procedure <procedureName>()
begin
	declare <value_1> <dataType> default <defaultValue>;
	while <expr> do
		-- SQL
	end while;
end;

-- sample sql
drop PROCEDURE if exists dome1;
create procedure dome1()
begin
	declare num int default 10;
	declare result varchar(200) default '';
	while num>0 do
		select CONCAT_WS('-->',result,num) INTO result;
		set num = num-1;
	end while;
	select result;
end;

call dome1();
REPEAT … END REPEAT
create procedure <procedureName>()
begin
	repeat
		-- SQL
	until <expr> -- until <expr> 为终止条件
	end repeat;
end;

-- sample sql
drop procedure if exists dome2;
create procedure dome2()
begin
	declare num int default 10;
	declare result varchar(200) default '';
	repeat
		select CONCAT_WS('-->',result,num) INTO result;
		set num = num-1;
	until num < 0
	end repeat;
	select result;
end;

call dome2();
LOOP
create procedure <procedureName>()
begin
	<loopName>:loop
		-- SQL
		if <expr> then
			leave <loopName>;
		end if;
	end loop;
end;

-- sample sql
drop procedure if exists dome3;

create procedure dome3()
begin
	declare num int default 10;
	declare result varchar(200) default '';	
	domeloop:loop
		select CONCAT_WS('-->',result,num) INTO result;
		set num = num-1;
		if num < 0 then
			select result;
			leave domeloop;
		end if;
	end loop;
end;

call dome3();

存储过程：游标

游标的概念

游标可以依次取出查询结果集中的每一条数据，也就是逐条读取查询结果集中的数据。

游标的使用步骤

声明游标（DECLARE） —— 打开游标（OPEN） —— 提取游标数据（FETCH） —— 关闭游标（CLOSE）

-- 声明游标
declare <cursorName> cursor for select ... from ...;
-- 打开游标
open <cursorName>;
-- 提取游标数据，提取后游标自动下移
fetch <cursorName> into <param_1>,...,<param_n>;
-- 关闭游标
close <cursorName>;

-- sample sql
drop table if exists `classes`;
create table `classes`(
	id int primary key auto_increment,
    classes varchar(32)
);
insert into `classes`(classes) values('Java'),('C++'),('C'),('C#'),('PHP'),('Python'),('Go'),('Vue'),('Node');

drop procedure if exists dome4;
create procedure dome4()
begin
	declare temp varchar(32) default '';
	declare classList varchar(200) default 'Classes : ';
	-- 定义游标
	declare cursor_a cursor for select classes from classes;
	-- 游标中的内容执行完后将done设置为1
	declare done int default 0;
	declare continue handler for not found set done =1;
	-- 打开游标
	open cursor_a;
	-- 循环
	cursor_loop:loop
		-- 取游标的值
		fetch cursor_a into temp;
		select concat_ws(',',classList,temp) into classList;
		if done = 1 then
			leave cursor_loop;
		end if;
	end loop;
	close cursor_a;
	select classList;
end;

call dome4();

存储过程管理

创建存储过程

-- IN 输入参数 OUT 输出参数 INOUT 输入输出参数
create procedure <procedureName>(IN <value_1> <dataType>,OUT <value_2> <dataType>,INOUT <value_3> <dataType>)
begin
	-- 声明变量 需要声明在存储过程的最前
    DECLARE <valueName> <dataType> default <defaultValue>;
	-- 赋值
	SET <value_3> = <value_1>;
	select <columnName> INTO <value_2> from <tableName>;
	-- 
end;

查看存储过程

-- 更具数据名查看数据库中所有的存储过程
show procedure status where db=<databaseName>;
-- 查询存储过程创建细节
show create procedure <databaseName>.<procedureName>;

更新存储过程

更新存储过程指的是修改存储过程的特征

alter procedure <procedureName> <feature_1>,...,<feature_n>;

存储过程的特征参数：

  • NO SQL：表示子程序中不包含SQL语句。

  • CONTAINS SQL：表示子程序包含SQL语句，但不包含读或写数据的语句。

  • READS SQL DATA：表示子程序中包含读取数据的SQL。

  • MODIFIES SQL DATA：表示子程序中包含修改数据的SQL。

  • SQL SECURITY[DEFIER|INVOKER]：指明谁有权限来执行：

    • DEFINER表示只有定义者自己可以执行
    • INVOKER表示调用者可以执行

  • COMMENT '<str>'：表示注释信息

删除存储过程

drop procedure <procedureName>;

触发器

什么是触发器？

触发器是一种特殊的存储过程，触发器和存储过程一样是一个能够完成特定功能，存储在数据服务器上的SQL片段，但是触发器无需调用，当对数据表中的数据执行DML（insert|delete|update）操作时能够自动触发。

使用触发器

创建触发器

语法：

create trigger <triggerName>
<before|after> -- 触发时间
<insert|update|delete>	-- 触发操作
on <tableName>	-- 触发对象
for each row	-- 声明为行级触发器（每操作一条记录触发一次触发器）
sql_statement	-- 触发器操作

查看触发器

show triggers;

删除触发器

drop trigger <trigger_name>;

触发器中的关键字：NEW，OLD

触发器用于监听对数据表中数据的DML操作，在触发器中通常处理一些关联操作；

可以使用NEW和OLD关键字在触发器中获取触发这个触发器的DML操作的数据：

  • NEW：在触发器中用于获取insert操作添加的数据、update操作修改后的数据
  • OLD：在触发器中用于获取update操作前的数据、delete操作前的数据

NEW

  • insert操作中

    -- sample sql
create trigger trigger_dome1
after insert on worker
for each row
insert into worker_logs(log_text,create_time) values(concat('新增员工编号为',NEW.id),now());

  • update操作中

    -- sample sql
create trigger trigger_dome1
after update on worker
for each row
insert into worker_logs(log_text,create_time) values(concat('修改工作人员信息为：',concat_sw('-',NEW.id,NEW.name,NEW.job)),now());

OLD

  • update操作中

    -- sample sql
create trigger trigger_dome1
after update on worker
for each row
insert into worker_logs(log_text,create_time) values(concat('工作人员',OLD.id,'信息修改为：',concat_sw('-',NEW.id,NEW.name,NEW.job)),now());

  • delete操作中

    -- sample sql
create trigger trigger_dome1
after delete on worker
for each row
insert into worker_logs(log_text,create_time) values(concat('工作人员',OLD.id,'信息被删除'),now());

触发器小结

优点：

  • 触发器监听到响应的DML操作时自动触发
  • 触发器可以实现表中数据的关联操作，有利于保证数据完整性
  • 触发器可以对DML操作的数据进行更为复杂的合法性校验

缺点：

  • 使用触发器实现的业务逻辑出现问题难以定位，后期维护困难
  • 大量使用触发器，使程序与数据库程序耦合性更高

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/194652.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！