mysql存储过程学习笔记

本文转载：https://blog.csdn.net/qq_33157666/article/details/87877246

一、定义

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

二、存储过程的特定

1、能完成较复杂的判断和运算

2、可编程性强，灵活

3、SQL编程的代码可重复使用

4、执行的速度相对快一些

5、减少网络之间的数据传输，节省开销

三、创建一个简单的存储过程

1、创建存储过程的简单语法

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

2、创建一个简单的存储过程

create procedure test()
begin
    select * from users;
    select * from orders;
end;

3、调用存储过程

call test();

四、存储过程的变量

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

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

2、概括

• 变量的声明使用declare,一句declare 只声明一个变量，变量必须先声明后使用；
• 变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能制定默认值、字符集和排序规则等；
• 变量可以通过set来赋值，也可以通过select into 的方式赋值；
• 变量需要返回，可以使用select语句，如：select 变量名。

五、变量的作用域

1、变量作用域说明：

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

2、通过一个实例来验证变量的作用域

　　需求：创建一个存储过程，用来统计users、orders表中行数数量和orders表中的最大金额和最小金额

create procedure test3()
begin
    begin
        declare userscount int default 0;  -- 用户表中的数量
        declare ordercount int default 0;  -- 订单表中的数量
        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 maxmoney,minmoney;      -- 返回最大金额、最小金额
    end;
end;

3、我将过程test(3)改为如下

create procedure test3()
begin
    begin
        declare userscount int default 0; -- 用户表中的数量
        declare ordercount int default 0; -- 订单表中的数量
        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 orders;
        select userscount,ordercount,maxmoney,minmoney; -- 返回最大金额、最小金额
    end;
end;

-- 上述代码，会报错，需要将userscount,ordercount变量声明为全局变量

declare userscount int default 0; -- 用户表中的数量
declare ordercount int default 0; -- 订单表中的数量
声明到最外围的块中。

六、存储过程参数

1、基本语法

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

存储过程的参数类型有： IN,OUT,INOUT,下面分别介绍这个三种类型：

2、存储过程的传出参数IN

　　说明：

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

　　通过一个实例来演示：

　　需求：编写存储过程，传入id，根据id返回name

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;

3、存储过程的传出参数out

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

　　

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

-- 定义一个变量 set 0 @变量名
    set @uname='';
    call test5(1,@uname);
    select @uname as username;

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

　　(3).存储过程的可变参数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;

　　概括：

　　　1.可变变量INOUT : 调用时可以传入值，在调用过程中，可以修改值，同时也可以返回值；

　　　2.INOUT参数集合了IN和OUT类型的参数功能；

　　　3.INOUT调用时传入的是变量，而不是常量；

 七、存储过程条件语句

　　1、基本结构

　　　　1）、条件语句基本结构：

　　　　　　

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

　　　　2)、多条件判断语句：

　　　　

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

　　2、实例

　　实例1：编写存储过程，如果用户userId是偶数则返回username,否则返回userId

　　

create procedure test6(in userId int)
begin
    declare username varchar(32) default '';
    if(userId%2=0)
    then
        select name into username from users where id=userId;
        select username;
    else
        select userId;
        end if;
end;

调用及运行结果
call test6(2);

　　实例2：存储过程的多条件语句应用示例

　　需求：根据用户传入的uid参数判断

　　1）、如果用户状态status为1，则给用户score加10分；

　　2）、如果用户状态status为2，则给用户score加20分；

　　3）、其他情况加30分

create procedure test7(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;

 

八、存储过程循环语句

　　1、while语句

　　　　1）、while语句的基本结构

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

　　2、示例

　　需求：使用循环语句，向表test1(id）中插入10条连续的记录

　　

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;

2、repeat语句

　　1）、repeat语句基本的机构：

repeat ... until ... end repeat;

　　2)、示例

　　需求： 给test1表中的id字段插入数据，从1到10

　　

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;

　　概括：  until判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式，只有当until语句为真时，循环结束。

 

九、存储过程游标的使用

　　1、什么是游标

　　　　游标是保存查询结果的临时区域