mysql存储过程中如何加事务控制 mysql8.0存储过程

文章目录

• 什么是存储过程
• 创建存储过程
• 调用存储过程
• 删除存储过程
• 参数

• 默认参数

• 参数验证
• 输出参数
• 变量

• 用户定义变量
• 本地变量

• 函数

• 创建函数
• 使用函数
• 删除

什么是存储过程

存储过程是一个包含一堆SQL代码的数据库对象，可以在我们编写其他语言程序的时候回，我们可以调用这些过程来获取或保存数据；使用存储过程来存储和管理SQL代码还可以使得在其他语言的函数中不必编写SQL语句，使得代码更加简洁

还有就是存储在数据库中的存储过程的代码DBMS可以对其进行一定的优化，使得访问效率提高

当然还能增强安全性

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

优点：

创建存储过程

DELIMITER //（或者$$）
CREATE PROCEDURE 存储名(参数, ...)
BEGIN
	SQL语句主体（body）
	注意这里一般会有多条语句，要用分号隔开
END $$ //

修改默认分隔符DELIMETER有的sql是不需要的

使用这个目的相当于告诉MySQL，这整段都要进入存储过程里保存

注意，选择好你要存储的数据库进行创建

delimiter $$
create procedure get_invoices_with_balance()
begin
	select 
		*
	from invoices
    where (invoice_total - payment_total) > 0;
end $$

调用存储过程

CALL 名字(参数)

多数时候，我们用其他语言来调用存储过程，如果在sql里面，需要使用CALL

删除存储过程

DROP PROCEDURE 过程名

这种写法如果不存在这个过程的话是会报错的

所以 我们可以这么写

DROP PROCEDURE IF EXISTS 过程名

参数

例如，我们可以按地区查找数据库

delimiter $$
create procedure get_client_by_state(state char(2))
begin
	select * from clients c where c.state = state;
end $$

如果此时调用该存储过程不加参数是会报错的，下面介绍默认参数

默认参数

如果调用时里面不含任何参数，我们可以设置一个默认参数

delimiter $$
create procedure get_client_by_state(state char(2))
begin
	if state is null then
		set state = 'cA';
	end if;
	select * from clients c where c.state = state;
end $$

如果我们要求默认情况下返回所有参数

delimiter $$
create procedure get_client_by_state(state char(2))
begin
	if state is null then
		select * from clients;
	else
		select * from clients c where c.state = state;
	end if;
end $$

还有一种方式

delimiter $$
create procedure get_client_by_state(state char(2))
begin
	select * from clients c where c.state = IfNULL(state, c.state);
end $$

ifnull函数判断括号第一个值是不是空值，如果是则返回第二个

参数验证

有的时候我们要往数据库里插入一些数据，但是我们不能保证有的时候传入的数据一定是正确的，所以我们要进行一些判断，就是参数验证

delimiter $$
create procedure get_payments(client_id int, payment_method_id tinyint) -- 非必须参数
begin
	select *
    from payments p
    where p.client_id = ifnull(client_id, p.client_id)
		and p.payment_method_id = ifnull(payment_method_id, p.payment_method_id);
end $$

如果上面的client_id为负数怎么办？

我们可以写一个if函数

delimiter $$
create procedure get_payments(client_id int, payment_method_id tinyint) -- 非必须参数
begin
	if client_id < 0 then
		signal sqlstate '22003' set message_text = '123456'; -- singal相当于其他语言的抛出错误异常，这里就是抛出sql错误码
		-- 然后可以设置一下要抛出的错误信息
	end if;

	select *
    from payments p
    where p.client_id = ifnull(client_id, p.client_id)
		and p.payment_method_id = ifnull(payment_method_id, p.payment_method_id);
end $$

输出参数

在参数设置中，我们加入OUT关键词来标注这个参数最后是要输出的

delimiter $$
create procedure get_unpaid_invoices_for_client(
	client_id int,
    out invoices_count int,
    out invoices_total decimal(9, 2) -- 这里是小数的意思，保留两位小数，精度为9，也就是小数点前有7位
)

begin
	select 
		count(*),
        sum(invoice_total)
	into
		invoices_count,
        invoices_total -- 输出参数要一一对应
	from invoices i
    where i.client_id = client_id and payment_toatal = 0;
end $$

这时候调用的过程也发生了变化

set @invoices_total = 0; -- 定义两个用户定义变量
set @invoices_count = 0; -- 使用前缀@来定义
call sql_invoicing.get_unpaid_invoices_for_client(1, @invoices_count, @invoices_total);
-- 当调用这个过程的时候，我们要往里面传入这些变量用于接收
select @invoices_count, @invoices_total;

变量

通常我们会在调用有输出参数的存储过程时使用这些变量

用户定义变量

传递这些变量，来获取输出参数值，这些变量在整个客户会话股哟称重被保存，客户从MySQL断线时，这些变量又被清空

用set定义，@作为前缀

本地变量

这是我们可以在存储过程或者函数内定义的

这些变量不会在整个客户端会话过程中保存

一旦存储过程结束，变量就会清空

使用declare定义变量

declare a decimal(9, 2) default 0;

set a = 1.1; -- .....自己变换

函数

与存储过程的区别是函数只能返回单一值

创建函数

create function 函数名
(
	参数
)
returns 类型
函数属性(deterministic...可以有多个属性)
begin

return
end

函数属性

• deterministic：确定性，给定这个函数同样的一组值，它会永远返回一样的值
• read sql data：函数中会配置选择语句，用以读取一些数据
• modifies data：函数中有插入、更新、删除函数

使用函数

跟调用内置函数一样，就像是调用内置函数

删除

drop function if exists 函数名