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 ([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  [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;