MYSQL数据库进阶 存储过程

存储过程

1、定义：

• 存储过程是一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数来执行它。存储过程是数据库中的一个重要对象。

2、存储过程特点：

1. 能完成较复杂的判断和运算
2. 可编程性强，灵活
3. SQL 编程的代码可重复使用
4. 执行的速度相对较快
5. 减少网络之间的数据传输，节省开销

3、简单的存储过程

简单语法

create procedure 名称()
begin
.........
end

简单存储过程示例

create procedure test1()
begin
    select * from student;
    select * from teacher;
end;

调用存储过程

call test1();  

4、存储过程的变量

通过一个简单的例子来学习变量的声明和赋值

create procedure test2()
begin
  -- 使用 declare语句声明一个变量
  declare inpunt_stu_no varchar(10) default '';
  declare out_stu_name varchar(20) default '';
  -- 使用set语句给变量赋值
  set inpunt_stu_no = 'stu_01';
  -- 将users表中id=1的名称赋值给username
  select stu_name into out_stu_name from student where stu_no = inpunt_stu_no;
  -- 返回变量
  select out_stu_name;
end;

存储过程解析：

1. 变量的声明使用 declare ，一句 declare 只声明一个变量，变量必须先声明后使用；
2. 变量具有数据类型和长度，与 mysql 的 SQL 数据类型保持一致；
3. 变量可以通过 set 来赋值，也可以通过 select into 的方式赋值；
4. 变量需要返回，可以使用 selec t语句，如：select 变量名

5、变量的作用域

1. 变量作用域说明

   存储过程中变量是有作用域的，作用范围在 begin 和 end 块之间需要多个块之间传值，可以使用全局变量，即放在所有代码块之前传参变量是全局的，可以在多个块之间起作用

2. 用实例来说明变量的作用域

   create procedure test3()
       begin
         begin
           declare student_count int default 0; -- 学生数量
           select count(*) into student_count from student;
           select student_count; -- 返回学生数量
         end;
         begin 
           declare max_age int default 0; -- 最大金额
           declare min_age int default 0; -- 最小金额
           select max(age) into max_age from student;
           select min(age) into min_age from student;
           select student_count,max_age,min_age; -- 返回学生数量、最大年龄、最小年龄
          end;
       end;
   -- 总结：当创建成功，call test3，系统报错如下
   [SQL]call test3
   [Err] 1054 - Unknown column 'student_count' in 'field list'

   将student_count改为全局变量

   create procedure test4()
       begin
         declare student_count int default 0; -- 学生数量
         begin
           select count(*) into student_count from student;
           select student_count; -- 返回学生数量
         end;
         begin 
           declare max_age int default 0; -- 最大金额
           declare min_age int default 0; -- 最小金额
           select max(age) into max_age from student;
           select min(age) into min_age from student;
           select student_count,max_age,min_age; -- 返回学生数量、最大年龄、最小年龄
          end;
       end;

6、存储过程参数

1、基本语法

create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
.........
end

2、存储过程的三种传参【in、out、inout】

create procedure test5(in student_id int,out student_age int ,inout student_name varchar(20))
begin
    select age,stu_name into student_age,student_name from student where id=student_id;
end;

存储过程解析：

1. IN类型参数一般只用于传入，默认就是IN类型
2. out是传出参数，不能用于传入参数值
3. INOUT参数集合了IN和OUT类型的参数功能，即可以传入也可以传出

7、

存储过程条件语句

1、基本结构

-- 单条件
if() then...else...end if;
-- 多条件
if() then...
elseif() then...
else ...
end if;

条件语句示例（这次考试，年龄小于20岁的学生每科+10分，小于23岁的学生每科-5分，大于23岁的学生+5）

create procedure test6(in in_stu_no int)
begin
   declare my_age int default 0;
   select age into my_age from student where stu_no=in_stu_no;
   if(my_age < 20)
   then 
       update score set stu_score=stu_score+10 where stu_no=in_stu_no;
    elseif(my_age < 23)
    then 
       update score set stu_score=stu_score-5 where stu_no=in_stu_no;
    else 
       update score set stu_score=stu_score+10 where stu_no=in_stu_no;
    end if;
end;

8、存储过程循环语句

1、while语句的基本语法

while(表达式) do 
   ......  
end while;

while语句示例（这里stu_no数据库设计不能重复，采用了concat()函数进行拼接）

create procedure test7()
begin
  declare i int default 0;
  while(i<10) do 
    begin 
        select i;
        set i=i+1;  
        insert into student(stu_no,stu_name) values(concat('test_',i),'test');
     end;
  end while;
end;