mysql存储过程

一、存储过程的相关概念

1.1 存储过程的概念

完成特定功能的SQL语句的集合，把定义好的SQL集合在一个特定的SQL的函数当中。

每次执行调用函数即可，还可以实现传参的调用。

1.2 存储过程的作用

执行速度比SQL语句执行速度更快，执行效率也更高。

客户端可以随时调用发放，也可以随后修改。

可以对数据库做任何操作。

二、存储过程的语法

2.1 基础语法

#先创建示例用的库表
create database yang；
create table info1 (
id int(2) primary key,
name varchar(10),
high int(3),
address varchar(15)
);

#完整的存储过程示例
delimiter $$
#delimiter 开始和节数的语法，$$标志位，可以自定义，不要用汉字、数字、特殊字符开头
create procedure test1 ()
begin
	select * from info1;
end $$
delimiter;

查看存储过程

show procedure status where db='yang'\G;
show procedure status like '%test1%'\G;

调用存储过程

call test1;

declare ：声明变量类型

在存储过程中新建的变量需要用declare声明类型

set ：对变量赋值

set只是对变量进行赋值

2.2 存储过程传参

• IN ：传入参数：调用者向存储过程传入值
• OUT ：输出参数：存储过程向调用者传出值，可以是多个值
• INOUT ：输入输出，既可以表示存储过程向调用者传出值，也可以表示用户向存储过程传入值

2.2.1 IN：传入参数

#定义一个存储过程test2
delimiter $$
create procedure test2 (in uname char(20))
begin
	select * from info1 where name = uname;
end $$
delimiter;

call test2('user1');

2.2.2 OUT ：传出参数

#
delimiter $$
create procedure test3 (out num int)
begin
	set num = 100;
end $$
delimiter;

call test4(@num);
select @num;

create table info2 (id int(4));
insert into info2 value(@num);

例：使用OUT传出参数的方法插入数据到表中

delimiter $$
create PROCEDURE test4 (
out id int,
out name varchar(10),
out high int,
out address varchar(15)
)
BEGIN
	set id = 3;
	set name = '王心凌';
	set high = 167;
	set address = '我心里';
END $$
delimiter;

call test4(@id,@name,@high,@address);

insert into info1 VALUES(@id,@name,@high,@address);

2.2.3 INOUT ：传入&传出参数

注意，在存储过程中引用变量不需要@

delimiter $$
create procedure test5 (inout str varchar(10))
begin
	select str;
	set str=concat(str,'str_yang');
	#concat：把字符串进行拼接
	select str;
end $$
delimiter;

delimiter $$
create procedure test6 (inout ht int)
begin
set ht = ht + 10;
end $$
delimiter;
set @ht = 185;
call test6(@ht);
update info1 set high = @ht where id = 3;

2.3 存储过程的控制语句

在存储过程中也可以使用条件语句和循环语句，注意语句的格式

delimiter $$
create procedure test7 (inout num1 int)
begin
if num >= 10 then
	set num = num - 5;
else
	set num = num * 2;
end if;
end $$
delimiter;

set @num1 = 19;
call test7(@num1);
select $num1;

例：条件判断

#对输入的值进行判断
##范围85-100 		优秀
##范围60-84		及格
##范围0-60		不及格
##范围判断使用 between ... and ...

create table info3 (
	id int,
    score int,
    grade char(30)
);

delimiter $$
create procedure test8 (
	inout score int,
    out grade varchar(255)
)
begin
if score between 85 and 100 then
	set grade = '优秀';
elseif score between 60 and 84 then
	set grade = '及格';
else
	set grade = '不及格';
end if;
end $$
delimiter;
set @id = 0;
set @score = 88;
set @id = @id +1;
call test8(@score);
insert into info3 values(@id,@score,@grade);
select * from info3;

例：循环语句

delimiter $$
create procedure test9 (out result int)
begin
declare a int;
declare i int;
set a = 10;
set i = 1;
while i <= 10 do
set a = a + 10;
set i = i + 1;
end while;
set result = a;
end $$
delimiter;

call test9(@result)

再例：循环语句

#订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);
#订单货物表
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2)
);
#插入数据
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(1, 101, '2024-07-20', 0.00),
(2, 102, '2024-07-21', 0.00);

INSERT INTO order_items (order_item_id, order_id, product_id, quantity, price) VALUES
(1, 1, 1001, 2, 50.00),
(2, 1, 1002, 1, 30.00),
(3, 2, 1001, 1, 50.00),
(4, 2, 1003, 3, 20.00);
#根据货物表把对应的货物总额填入订单表

实现：

DELIMITER $$
CREATE PROCEDURE Update1 (IN orderid INT)
BEGIN
    DECLARE ordertotal DECIMAL(10, 2);
    
    -- 计算订单的总金额
    SELECT SUM(quantity * price) INTO ordertotal
    FROM order_items
    WHERE order_id = orderid;
    
    -- 更新订单的总金额
    UPDATE orders
    SET total_amount = ordertotal
    WHERE order_id = orderid;
END;
$$
DELIMITER;