mysql 存储过程学习

存储过程介绍

1.1 SQL指令执行过程

从SQL执行的流程中我们分析存在的问题:
1.如果我们需要重复多次执行相同的SQL，SQL执行都需要通过连接传递到MySQL，并且需要经过编译和执行的步骤;

2.如果我们需要执行多个SQL指令，并且第二个SQL指令需要使用第一个SQL指令执行的结果作为参数

1.2 存储过程执行过程

什么是存储过程:
将能够完成特定功能的SQL指令进行封装 (SQL指令集)，编译之后存储在数据库服务器上，并且为之取一个名字，客户端可以通过名字直接调用这个SQL指令集，获取执行结果。

存储过程优点:
1.SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意篡改保证安全性;
2.存储过程经过编译创建并保存在数据库中的，执行过程无需重复的进行编译操作，对SQL指令的执行过程进行了性能提升;
3.存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句 (分支、循环)可以实现更为复杂的业务;
存储过程的缺点:
1.存储过程是根据不同的数据库进行编译、创建并存储在数据库中;当我们需要切换到其他的数据库产品时，需要重新编写针对于新数据库的存储过程;
2.存储过程受限于数据库产品，如果需要高性能的优化会成为一个问题;
3.在互联网项目中，如果需要数据库的高 (连接)并发访问，使用存情过程会增加数据库的连接执行时间 (因为我们将复杂的业务交给了数据库进行处理)

1.3 创建存储过程语法：

create procedure <proc name>([IN/OUT args])

begin
-- SQl
end;

-- 创建一个存储过程实现加法运算:Java语法中，方法是有参数和返回值的存储过程中，是有输入参数 和 输出参数的
create procedure proc_test1(IN a int,IN b int,OUT c int)
begin
  SET c = a+b;
end;

1.4 调用存储过程 

-- 调用存储过程
-- 定义变量@m
set @m = 0;
-- 调用存储过程，将3传递给a，将2传递给b，将@m传递给c
call proc_test1(3,2,@m);
-- 显示变量值
select @m from dual;

1.5 存储过程中变量的使用：存储过程中的变量分为两种:局部变量和 用户变量

1.6 局部变量

局部变量: 定义在存储过程中的变量，只能在存储过程内部使用。

局部变量定义语法:局部变量要定义在存储过程中，而且必须定义在存储过程开始

declare <attr_name> <type> [default value];

局部变量定义示例:

create procedure proc_test2(IN a int,OUT r int)
begin
    declare x int default 0; -- 定义x int 类型，默认值为0
    declare y int default 1; -- 定义y
    set x = a*a;
    set y = a/2;
    set r = x+y;
end ;

1.7 用户变量

用户变量: 相当于全局变量，定义的用户变量可以通过 select @attrName from dual进行查询
用户变量会存储在mysql数据库的数据字典中(dual)
用户变量定义使用set关键字直接定义，变量名要以@开头
set @n=1 ;

1.8 给变量设置值

无论是局部变量还是用户变量，都是使用 set 关键字修改值

set @n=1 ;
call proc_test2(6,@n);
select @n from dual;

1.9 将查询结果赋值给变量

在存储过程中使用select..into..给变量赋值

查询学生数量--
create procedure proc_test3(OUT c int)
begin
    select count(stu_num) INTO c from students;-- 将查询到学生数量赋值给c
end;

-- 调用存储过程
call proc_test3(@n);
select @n from dual:

1.10 用户变量使用注意事项

 因为用户变量相当于全局变量，可以在SOL指令以及多个存储过程中共享，在开发中建议尽量少使用用户变量，用户变量过多会导致程序不易理解、难以维护。

1.11 存储过程的参数

MySQL存储过程的参数一共有三种: IN \ OUT \ INOUT

1.11.1  IN输入参数
  输入参数：在调用存储过程中传递数据给存储过程的参数(在调用的过程必须为具有实际值的变量 或者 字面值）

-- 创建存储过程: 添加学生信息
create procedure proc_test4(IN snum char(8),IN sname varchar(20), IN gender char(2), IN ageint，IN cid int，IN remark varchar(255))
begin
    insert into students stu_num, stu_name, stu_gender,stu_age, cid, remark)values( snum, sname, gender , age, cid, remark ) ;
end ;
-- 调用存储过程
call proc_test4('20210108'，小丽'，女',20,1,'aaa');

1.11.2  OUT输出参数

输出参数:将存储过程中产生的数据返回给过程调用者，相当于Java方法的返回值，但不同的是一个存储过程可以有多个输出参数
 

-- 创建存储过程，根据学生学号，查询学生姓名
create procedure proc_test5(IN snum char(8),0UT sname varchar(20))
begin
    select stu_name INTO sname from students where stu_num = snum;
end ;

set @name=''
call proc_test5('20210188',@name);
select @name from dual;

1.11.12 INOUT输入输出参数
 

create procedure proc_test6(INOUT str varchar(20))
begin 
    select stu_name INTO str from students where stu_num=str;
end ;

set @name= '20210108';
call proc_test6(@name);
select @name from dual;

2.1 存储过程中流程控制

在存储过程中支持流程控制语句用于实现逻辑的控制

2.1.1 分支语句

• if-then-else

-- 单分支: 如果条件成立，则执行SQL
if conditions then
-- SQL
end if;
-- 如果参数a的值为1，则添加一条班级信息
create procedure proc_test7(IN a int)
begin
    if a=1 then
        insert into classes(class_name, remark) values( 'Java2109','test');
    end if;
end ;

-- 双分支:如果条件成立则执行SQL1，否则执行SQL2
if conditions then
    -- SQL1
else
    -- SQL2
end if;
-- 如果参数a的值为1，则添加一条班级信息，否则添加一条学生信息
create procedure proc_test7(IN a int)
begin
    if a=1 then
        insert into classes(class_name, remark) values('Java2109','test');
    else
        insert into students (stu_num, stu_name, stu_gender , stu_age, cid, remarkvalues('20210110'，小花'，女',19,1,'...');
    end if;
end ;

•  case

-- case
create procedure proc_test8(IN a int)
begin
    case a
      when 1 then
           -- SOL1 如果a的值为1 则执行SQL1
       insert into classes(class_name, remark) values( ' Java2110', 'wahaha');
      when 2 then
           -- SOL2 如果a的值为2 则执行SQL2
       insert into students(stu_num, stu_name, stu_gender, stu_age, cid, remark)values( '20210111','小刚','男',21,2,'...');
    else
           -- SQL (如果变量的值和所有when的值都不匹配，则执行else中的这个SQL)
       update students set stu_age = 18 where stu_num ='20210119' 
   end case ;
end ;

2.2  循环语句

• while

-- while
create procedure proc_test9(IN num int)
begin
  declare i int;
  set i = 0;
  while i < num do
    -- SQL 
   insert into classes(class_name,remark) values(CONCAT('Java',i) ,'....');
    set i = i+1;
  end while;
end;

call proc test9(4);

• repeat

- repeat
create procedure proc_test10(IN num int)
begin
  declare i int;
  set i =0;
  repeat 
  -- SOL
    insert into classes(class_name, remark) values( CONCAT('Python',i) , '....');
    set i = i+1;
  until i > num end repeat;
end;

• loop 

-- loop
create procedure proc_test11(IN num int)
begin
 declare i int ;
 set i = 1;
 myloop: loop
   -- SOL
  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;