Mysql 存储过程初识

存储过程

认识

在一些编程语言中, 如pascal, 有一个概念叫"过程" procedure, 和"函数" function, 如vb中的sub. java, python, php, 没有过程, 只有function.

过程(procedure) : 封装了若干条语句, 调用时, 这些封装体执行.

函数(function): 是一个有返回值的 "过程" (ps: python函数即可是过程, 也是是函数)

存储过程(sql_procedure): 将若干条sql封装起来, 取一个名字, 即为过程, 把此过程存储在数据库中, 即存储过程.

存储过程-创建语法

-- 创建
create procedure procedurename()
begin
    sql语句1;
    sql语句2;.....
end

-- 调用
call procedurename();

第一存储过程

helloworld

-- 第一个存储过程: 打印hello world
delimiter //
drop procedure if exists p1;
create procedure p1()
begin
    select "hello world!";
    select 1 + 1;
end //
delimiter ;

-- call 调用
call p1;
-- 查看: show procedure status;
show show procedure status;

效果

mysql> -- 第一个存储过程: 打印hello world
delimiter //
drop procedure if exists p1;
create procedure p1()
begin
    select "hello world!";
    select 1 + 1;
end //
delimiter ;
query ok, 0 rows affected (0.16 sec)

query ok, 0 rows affected (0.16 sec)

mysql> call p1();
+--------------+
| hello world! |
+--------------+
| hello world! |
+--------------+
1 row in set (0.06 sec)

+-------+
| 1 + 1 |
+-------+
|     2 |
+-------+
1 row in set (0.21 sec)

query ok, 0 rows affected (0.00 sec)

引入变量-declare 局部

存储过程是可以编程的, 意味着可以用变量, 表达式, 控制结构来完成各种复杂的功能.

在存储过程中, 用 declare 变量名 变量类型 [default 默认值].

-- 变量引入
drop procedure if exists p2;
delimiter //
create procedure p2()
begin
    declare age int default 18;
    declare height int default 180;
    -- concat 拼接输出
    select concat("油哥的年龄是:", age, "身高是:", height);
end //
delimiter ;

call p2();

效果:

call p2();
query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

+-------------------------------------------------+
| concat("油哥的年龄是:", age, "身高是:", height) |
+-------------------------------------------------+
| 油哥的年龄是:18身高是:180                       |
+-------------------------------------------------+
1 row in set (0.10 sec)

query ok, 0 rows affected (0.04 sec)

引入运算

在储存过程中, 变量可以引入sql语句中合法的运算, 如 + - * /, 值的注意的是, 运算的结果,如何赋值给变量?

set 变量名 := expression 在存储过程中的变量是一个局部变量.

set @变量名 := expression 用户(会话)变量, 在存储过程外面也能用, 类似"全局变量".

declare 变量名 变量类型 [default value] 用在过程中的局部变量, 声明类型.

关于赋值 = 与 := 的区别

:=

• 标准的赋值符号, 在任何场景都是赋值.

=

• 只在 set 和 update 是和 := 一样是赋值, 其他都是等于的作用.

drop procedure if exists p3;
delimiter //
create procedure p3()
begin
    declare age int default 18;

    select concat("现在的年龄是:", age);
    -- 变量运算,赋值
    set age := age + 10;
    select concat("10年后, 年龄变成了:", age); 
end //
delimiter ;

-- out
mysql> call p3();
+------------------------------+
| concat("现在的年龄是:", age) |
+------------------------------+
| 现在的年龄是:18              |
+------------------------------+
1 row in set (0.11 sec)

+------------------------------------+
| concat("10年后, 年龄变成了:", age) |
+------------------------------------+
| 10年后, 年龄变成了:28              |
+------------------------------------+
1 row in set (0.25 sec)

控制结构 if - then - else - end if;

-- 语法
if condition then
    statement_01
else
    statement_02
end if;

drop procedure if exists p4;
delimiter //
create procedure p4()
begin
    declare age int default 18;
    
    if age >= 18 then
        select "已成年";
    else
        select "未成年";
    end if;
end //
delimiter ;

-- test
call p4();

-- out
+--------+
| 已成年 |
+--------+
| 已成年 |
+--------+
1 row in set (0.09 sec)

query ok, 0 rows affected (0.00 sec)

存储过程传参

存储过程的括号里, 可以声明参数, 语法是 [in / out / inout] 参数名 参数类型

in 表示往procedure里面传参数; out 表示其往外发射参数

-- 输入矩形的 width, height 求矩形的面积
drop procedure if exists p5;
delimiter //
create procedure p5(width int, height int)
begin
    select concat("面积是:", width * height);
    if width > height then
        select "比较胖";
    elseif width < height then
        select "比较瘦";
    else
        select "方的一痞";
    end if;
    
end //
delimiter ;

call p5(12, 13);

-- out
call p5(12, 13);
query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.05 sec)

+-----------------------------------+
| concat("面积是:", width * height) |
+-----------------------------------+
| 面积是:156                        |
+-----------------------------------+
1 row in set (0.11 sec)

+--------+
| 比较瘦 |
+--------+
| 比较瘦 |
+--------+
1 row in set (0.22 sec)

query ok, 0 rows affected (0.01 sec)

流程控制 while , repeat, loop

任何编程语言, 只要具备控制结构顺序, 选择, 循环就足够了.

感觉就是, 编程其实思路都是一样的, 只是不同语言的应用场景, 语法特性有差别而已, 思路都是一样的.

-- while 循环 语法
while search_condition do
    statement_list
end while [end_label]

-- 求 1+2+3+...100
drop procedure if exists p6;
delimiter //
create procedure p6()
begin
    declare total int default 0;
    declare num int default 0;
    -- while 循环
    while num <= 100 do
        set total := total + num;
        set num := num + 1;
    end while;
    -- 最后输出结果
    select concat("1+2+...100的值是: ", total) as 'sum';
end //
delimiter ;

call p6();

-- out
call p6();
query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.05 sec)

+------------------------+
| sum                    |
+------------------------+
| 1+2+...100的值是: 5050 |
+------------------------+
1 row in set (0.09 sec)

改进: 求 1+2+....n 的和, 这里引入参数 in

-- 求 1+2+3+...n
drop procedure if exists p7;
delimiter //
-- 传入参数 in类型
create procedure p7(in n int)
begin
    declare total int default 0;
    declare num int default 0;
    -- while 循环
    while num <= n do
        set total := total + num;
        set num := num + 1;
    end while;
    -- 最后输出结果
    select concat("1+2+.. 的值是: ", total) as 'sum';
end //
delimiter ;

call p7(10000);

-- out
call p7(10000);
query ok, 0 rows affected (0.00 sec)

query ok, 0 rows affected (0.00 sec)

+-------------------------+
| sum                     |
+-------------------------+
| 1+2+.. 的值是: 50005000 |
+-------------------------+
1 row in set (0.14 sec)

out 型参数

drop procedure if exists p8;
delimiter //
create procedure p8(in n int, out total int)
begin
    -- 声明一个局部(临时)变量num来存储 1..n
    declare num int default 0;
    -- while 
    while num <= n do
        set total := total + num;
        set num := num + 1;
    end while;
    -- select concat("the sum is:", total)
end //
delimiter ;

-- 区别: 没有在 begin ..end 中声明 total变量, 而是在 out类型的参数中.
-- out: 传入一个变量去接收输出
call p8(100, @cj); 

mysql> select @cj;
+------+
| @cj  |
+------+
| null |
+------+
1 row in set (0.10 sec)

null 的特殊性, 导致没有正确输出 , 解决: 给total 一个默认值即可

mysql> select null = null;
+-------------+
| null = null |
+-------------+
| null        |
+-------------+
1 row in set (0.09 sec)

mysql> select 1 + null;
+----------+
| 1 + null |
+----------+
| null     |
+----------+
1 row in set (0.05 sec)

-- 解决null的特殊性
drop procedure if exists p8;
delimiter //
create procedure p8(in n int, out total int)
begin

    -- 先声明一个局部(临时)变量num来存储 1..n
    declare num int default 0;
    -- 再给out变量一个默认值即可(顺序是先declare哦)
    set total := 0;
    -- while 
    while num <= n do
        set total := total + num;
        set num := num + 1;
    end while;
end //
delimiter ;

-- 区别: 没有在 begin ..end 中声明 total变量, 而是在 out类型的参数中.

-- out: 传入一个变量去接收输出的total变量值
call p8(100, @thesum);
select @thesum;

-- out

mysql> call p8(100, @thesum);
query ok, 0 rows affected (0.00 sec)

mysql> select @thesum;
+---------+
| @thesum |
+---------+
|    5050 |
+---------+
1 row in set (0.11 sec)

小结参数 in 和 out 和 inout

• in 类型, 是要输入一个值进去, 传递给procedure的in类型变量(传值)
• out类型, 是要输入一个变量进去, 接收procedure的out类型变量的值
• inout类型, 传入值进入和传入变量接收值出来

-- inout 类型
drop procedure if exists p9;
delimiter //
create procedure p9(inout age int)
begin
    set age := age + 20;
end //
delimiter ;

-- call 的时候, inout, 首先要定义一个"全局(会话变量)", 然后再传入
-- out
mysql> set @age := 100;
query ok, 0 rows affected (0.00 sec)

mysql> call p9(@age);
query ok, 0 rows affected (0.00 sec)

mysql> select @age;
+------+
| @age |
+------+
|  120 |
+------+