存储过程

MySql的存储过程

一 定义

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

二 存储过程的优点

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

创建一个存储过程的简单语法
create procedure NAME()
begin
	~~~~~~
end

实例：
create procedure testa()
begin 
	select * from users;
	select * from orders;
end;

调用：
call testa();

三 变量

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

作用域：

1. 存储过程的作用域，作用范围为begin到end之间，end结束变量的作用范围即结束。
2. 需要多个块传值，需要定义全局变量，即放在所有代码块之前
3. 传参变量是全局的，可以在多个块之间器起作用。

    create procedure test3()
    begin

        declare userscount int default 0; -- 用户表中的数量
        declare ordercount int default 0; -- 订单表中的数量
        begin
            select count(*) into userscount from users;
            select count(*) into ordercount from orders;
            select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量
      end;
      begin 
        declare maxmoney int default 0; -- 最大金额
        declare minmoney int default 0; -- 最小金额
        select max(money) into maxmoney from orders;
        select min(money) into minmoney from orders;
        select userscount,ordercount，maxmoney,minmoney; -- 返回最金额、最小金额
       end;
    end;

四 存储过程参数

基本语法

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

​ Tips： IN参数

​ （1）、传入参数：类型为in,表示该参数的值必须在调用存储过程事指定，如果不显示指定为in,那么默认就是in类型。
​ （2）、IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回
​ （3）、如果调用存储过程中需要修改和返回值，可以使用OUT类型参数

实例：

 create procedure test4(userId int)
    begin
            declare username varchar(32) default '';
            declare ordercount int default 0;
            select name into username from users where id=userId;
            select username;
    end;

out参数

调用存储过程时，传入userId返回该用户的name

 create procedure test5(in userId int,out username varchar(32))
        begin
            select name into username from users where id=userId;
        end;

​ 1、传出参数：在调用存储过程中，可以改变其值，并可返回；
​ 2、out是传出参数，不能用于传入参数值；
​ 3、调用存储过程时，out参数也需要指定，但必须是变量，不能是常量；
​ 4、如果既需要传入，同时又需要传出，则可以使用INOUT类型参数

inout参数

需求：调用存储过程时，传入userId和userName,即使传入，也是传出参数。

create procedure test6(inout userId int,inout username varchar(32))
begin
    set userId=2;
    set username='';
    select id,name into userId,username from users where id=userId;
end;

五：存储过程的语句

基本结构(条件语句，多条件判断，循环)

if() then...else...end if;

if() then...
elseif() then...
else ...
end if;

实例：

create procedure test8(in userid int)
begin
   declare my_status int default 0;
   select status into my_status from users where id=userid;
   if(my_status=1)
   then 
       update users set score=score+10 where id=userid;
    elseif(my_status=2)
    then 
       update users set score=score+20 where id=userid;
    else 
       update users set score=score+30 where id=userid;
    end if;
end;

while语句

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

实例：

create procedure test9()
begin
  declare i int default 0;
  while(i<10) do 
    begin 
        select i;
        set i=i+1;
        insert into test1(id) values(i);
     end;
  end while;
end;

repeat语句

repeat...until...end repeat;

实例：

create procedure test10()
begin
    declare i int default 0;
    repeat 
    begin 
        select i;
        set i=i+1;
        insert into test1(id) values(i);
    end;
    until i>=10 -- 如果i>=10,则跳出循环
    end repeat;
end;

六 游标

什么是游标？

游标是保存插叙结果的临时区域

create procedure test11()
    begin
        declare stopflag int default 0;
        declare username VARCHAR(32);
        -- 创建一个游标变量，declare 变量名 cursor ...
        declare username_cur cursor for select name from users where id%2=0;
        -- 游标是保存查询结果的临时区域
        -- 游标变量username_cur保存了查询的临时结果，实际上就是结果集
        -- 当游标变量中保存的结果都查询一遍(遍历),到达结尾，将变量stopflag设置为1，用于循环中判断是否结束
        declare continue handler for not found set stopflag=1;
 
        open username_cur; -- 打卡游标
        fetch username_cur into username; -- 游标向前走一步，取出一条记录放到变量username中
        while(stopflag=0) do -- 如果游标还没有结尾，就继续
            begin 
                -- 在用户名前门拼接 '_cur' 字符串
                update users set name=CONCAT(username,'_cur') where name=username;
                fetch username_cur into username;
            end;
        end while; -- 结束循环
        close username_cur; -- 关闭游标
    end;

七 自定义函数

函数与存储过程最大的区别就是函数必须有返回值，否则会报错

实例：

create function getusername(userid int) returns varchar(32)
    reads sql data  -- 从数据库中读取数据，但不修改数据
    begin
        declare username varchar(32) default '';
        select name into username from users where id=userid;
        return username;
    end;

概括：
1.创建函数使用create function 函数名(参数) returns 返回类型；
2.函数体放在begin和end之间；
3.returns指定函数的返回值；
4.函数调用使用select getusername()。