四、 MySQL的存储过程

目录

 存储过程优缺点分析

1、格式

2、 局部变量

3、用户变量

 4、系统变量

5、参数

6、流程控制

6、1  if

6、2    case

6、3  循环

 repeat

 loop

7、 存储过程管理

7、1 查询存储过程

7、2 修改存储过程

7.3 删除存储过程 

7.3 删除存储过程 

存储过程练习案例

 存储过程优缺点分析

存储过程优点：

1. SQL指令⽆需客户端编写，通过⽹络传送，可以节省⽹络开销，同时避免SQL指令在⽹络 传输过程中被恶意篡改保证安全性；

2. 存储过程经过编译创建并保存在数据库中的，执⾏过程⽆需重复的进⾏编译操作，对SQL 指令的执⾏过程进⾏了性能提升；

3. 存储过程中多个SQL指令之间存在逻辑关系，⽀持流程控制语句（分⽀、循环），可以实 现更为复杂的业务;

存储过程的缺点：

1. 存储过程是根据不同的数据库进⾏编译、创建并存储在数据库中；当我们需要切换到其 他的数据库产品时，需要重写编写针对于新数据库的存储过程；

2. 存储过程受限于数据库产品，如果需要⾼性能的优化会成为⼀个问题；

3. 在互联⽹项⽬中，如果需要数据库的⾼（连接）并发访问，使⽤存储过程会增加数据库 的连接执⾏时间（因为我们将复杂的业务交给了数据库进⾏处理）

1、格式

/*
delimiter 自定义结束封号
create procedure 储存名([ in,out,inout] 参数名 数据类型、、)
begin
  sql 语句
end 自定义的结束符号 delimiter；
*/

 
delimiter $$
create procedure proc01()
begin
    select empno,ename from emp;
end $$
delimiter ;

-- 调用存储过程
call proc01();

2、 局部变量

/*
语法1：声明变量 declare var_name type [default var_value];
语法2：
select col_name [、、、]into var_name[...]
from table_name where condition
*/

delimiter $$
create procedure proc02()
begin
    declare var_name01 varchar(20) default 'aaa';-- 声明/定义变量
    set var_name01='zhangsan';-- 给变量
    select var_name01;-- 输出变量值
    end $$
    delimiter ;
    
call proc02;

    
-- 还可以使用select.. into语句    

delimiter $$
create procedure proc03()
begin
    declare my_ename varchar(20) ;-- 声明/定义变量
    select ename into my_ename from emp where empno=7782;-- 给变量赋值
    select my_ename;-- 输出变量值
    end $$
    delimiter ;
    
call proc03;

3、用户变量

delimiter $$
create procedure proc04()
begin
 set @var_name01='beijing';
 select @var_name01;
 end $$
 delimiter ;
 
 call proc04;
 select @var_name01;-- 也可以使用用户变量
 
 

 4、系统变量

 
系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（global）、会话变量（session）。

全局变量
 查看系统变量
 show [ session | global ] variables ; -- 查看所有系统变量
 show [ session | global ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
select @@[session | global] 系统变量名; -- 查看指定变量的值
2). 设置系统变量
set [ session | global ] 系统变量名 = 值 ;
set @@[session | global].系统变量名 = 值 ;

5、参数

参数的类型，主要分为以下三种：IN、OUT、INOUT。 具体的含义如下：

类型	含义	备注
in	该类参数作为输入，也就是需要调用时传入值	默认
out	该类参数作为输出，也就是该参数可以作为返回值
inout	既可以作为输入参数，也可以作为输出参数

用法：

delimiter $$

create procedure 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])

begin

        -- SQL语句

 end $$

delimiter ;

案例一 :

根据传入参数score，判定当前分数对应的分数等级，并返回。 score >= 85分，等级为优秀。 score >= 60分 且 score < 85分，等级为及格。 score < 60分，等级为不及格。

delimiter $$

create procedure p4(in score int, out result varchar(10))

begin

        if score >= 85 then

                set result := '优秀';

        elseif score >= 60 then 

                set result := '及格';

           else                                                                           

                set result := '不及格';

            end if;                                                                                                                                                                                                                                                                          

 end $$

delimiter ;

-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明

call p4(18, @result);

select @result;

案例二 :将传入的200分制的分数，进行换算，换算成百分制，然后返回。 

delimiter $$

create procedure proc5(inout score double)
begin
    set score= score * 0.5;
 end $$

delimiter ;

     set @score = 198;

     call p5(@score);

     select @score;

6、流程控制

6、1  if

介绍

 if 用于做条件判断，具体的语法结构为：

if 条件1 then

        .....

elseif条件2 then        -- 可选

         .....

ELSE                        -- 可选

         .....

END IF;

在if条件判断的结构中，else if 结构可以有多个， else结构可以有，也可以没有。

2). 案例 根据定义的分数score变量，判定当前分数对应的分数等级。

score >= 85分，等级为优秀。

score >= 60分 且 score < 85分，等级为及格。

score < 60分，等级为不及格。

delimiter $$
create procedure proc6(in score int)
begin
 if score<60
        then
            select '不及格';
 elseif score >=60 and score <80
        then 
            select '及格';
    elseif score >=80 and score <90
        then 
            select '良好';
    elseif score >=90 and score <100
        then 
            select '优秀';    
    else         select '成绩错误';
    end if;
end $$
delimiter ;

set @score=55;
call proc6(@score);

6、2    case

格式1

 -- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为when_value2时， 执行statement_list2， 否则就执行 statement_list

CASE case_value

        WHEN when_value1 THEN statement_list1

        [ WHEN when_value2 THEN statement_list2] ...

        [ ELSE statement_list ]

END CASE;

 格式2

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成 立时，执行statement_list2， 否则就执行 statement_list

CASE

        WHEN search_condition1 THEN statement_list1

        [WHEN search_condition2 THEN statement_list2] ...

        [ELSE statement_list]

END CASE; 

 案例

create procedure p6(in month int)

begin

        declare result varchar(10);

        case

                when month >= 1 and month <= 3 then

                        set result := '第一季度';

                when month >= 4 and month <= 6 then

                          set result := '第二季度';

                when month >= 7 and month <= 9 then

                        set result := '第三季度';

                  when month >= 10 and month <= 12 then

                        set result := '第四季度';

        else

                        set result := '非法参数';

end case ;

select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);

end;

call p6(16);  

6、3  循环

while：

 while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑

while 条件 do

         SQL逻辑...

end  while；

计算从1累加到n的值，n为传入的参数值。 

create procedure p7(in n int)
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;
        set n := n - 1;
end while;
select total;
end;
call p7(100); 

 repeat

-- repeat create procedure proc_test10(IN num int)

begin

declare i int;

         set i = 1;

repeat -- SQL

         insert into classes(class_name,remark) values( CONCAT('Python',i) ,'....');

        set i = i+1;

         until   i > num end repeat;

end;

call proc_test10(4); 

 loop

-- loop

create procedure proc_test11(IN num int)

begin

        declare i int ;

        set i =0;

        myloop:loop

                -- SQL

                insert into classes(class_name,remark) values( CONCAT('HTML',i,'....');

                set i = i+1;

                if i=num then

                        leave myloop;

                end if;

end loop;

end;

 call proc_test11(5);

7、 存储过程管理

7、1 查询存储过程

存储过程是属于某个数据库的，也就是说当我们将存储过程创建在某个数据库之后，只 能在当前数据库中调⽤此存储过程。 查询存储过程：查询某个数据库中有哪些存储过程

-- 根据数据库名，查询当前数据库中的存储过程

show procedure status where db='db_test2';

-- 查询存储过程的创建细节

show create procedure db_test2.proc_test1 

7、2 修改存储过程

修改存储过程指的是修改存储过程的特征/特性

1 alter procedure 特征1 [特征2 特征3 ....]  

7.3 删除存储过程 

-- 删除存储过程

-- drop 删除数据库中的对象 数据库、数据表、列、存储过程、视图、触发器、索引....

-- delete 删除数据表中的数据

drop procedure proc_test1; 

7.3 删除存储过程 

存储过程练习案例

使用存储过程解决企业 项目开发过程中的问题案例：

 使用存储过程完成借书操

数据准备
create database mydb_book;
use mydb_book;

create table books(
    book_id int primary key auto_increment, 
    book_name varchar(50) not null, 
    book_author varchar(20) not null, 
    book_price decimal(10,2) not null, 
    book_stock int not null,
    book_desc varchar(200)
);

-- 添加图书信息
insert into books(book_name,book_author,book_price,book_stock,book_desc)
values('Java程序设计','亮亮',38.80,12,'亮亮带你学Java');
insert into
books(book_name,book_author,book_price,book_stock,book_desc)
values('c语音','昊昊',44.40,9,'昊昊，c语音王者领路'); 

-- 创建学生信息表

create table mydb_book.students(
    stu_num char(4) primary key,
    stu_name varchar(20) not null,
    stu_gender char(2) not null,
    stu_age int not null 
    );

-- 添加学生信息
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1001','张三','男',20);
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1002','李四','男',20);
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1003','王五','男',20);

 业务分析

哪个学⽣借哪本书，借了多少本？

操作：

         保存借书记录

        修改图书库存

条件：

        判断学⽣是否存在？

        判断图书是否存在、库存是否充⾜？

-- 创建存储过程
-- 参数1： a 输入参数 学号
-- 参数2： b 输入参数 图书编号
-- 参数3： m 输入参数 借书的数量
-- 参数4： state 输入参数 借书的状态（1 借书成功，2 学号不存在，3 图书不存在，4库存不足）
create procedure proc_borrow_book(IN a char(4),IN b int, IN m int,OUT state int)
begin
 declare stu_count int default 0;
 declare book_count int default 0;
 declare stock int default 0;
 -- 判断学号是否存在：根据参数 a 到学⽣信息表查询是否有stu_num=a的记录
 select count(stu_num) INTO stu_count from students where stu_num=a;
 if stu_count>0 then
        -- 学号存在
        -- 判断图书ID是否存在：根据参数b 查询图书记录总数
        select count(book_id) INTO book_count from books where book_id=b;
        if book_count >0 then
                -- 图书存在
                -- 判断图书库存是否充⾜：查询当前图书库存，然后和参数m进⾏⽐较
                select book_stock INTO stock from books where book_id=b;
                if stock >= m then
                    -- 执⾏借书
                    -- 操作1：在借书记录表中添加记录
                    insert into records(snum,bid,borrow_num,is_return,borrow_date) values(a,b,m,0,sysdate());
                    -- 操作2：修改图书库存
                    update books set book_stock=stock-m where book_id=b;
                    -- 借书成功
                    set state=1;
                else
                    -- 库存不足
                        set state=4;
                end if;
        else
            -- 图书不存在
            set state = 3;
        end if;
 else
        -- 不存在
        set state = 2;
 end if;
end;

-- 调用存储过程借书
set @state=0;
call proc_borrow_book('1001',1,2,@state);
select @state from dual;