mysq 5.7 存储过程

小伙伴们，你们好，我是老寇

一、存储过程

1.简介

存储过程是经过编译处理，并且存储在数据库中的一段sql语句集合

2.特点

• sql封装在存储过程中，能够重复调用
• 可以接收参数，也可以返回参数
• 减少网络交互

3.语法

创建

create procedure 存储过程名称 ([参数列表])
begin
    --sql语句
end;

调用

CALL 名称 ([参数]);

查看

select * from information_schema.ROUTINES where ROUTINE_SCHEMA = "表名" -- 查询指定数据库的存储过程及状态信息
show create procedure 存储过程名称; -- 查询某个存储过程定义

删除

drop procedure [if exists] 存储过程名称;

栗子

-- 创建表
DROP TABLE IF EXISTS `boot_city`;
CREATE TABLE `boot_city` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `parent_id` bigint(20) NOT NULL COMMENT '上级编号',
  `region` varchar(100) NOT NULL COMMENT '地区名称',
  `region_id` int(11) NOT NULL COMMENT '地区编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1324914279466823682 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='城市';

-- 事务提交（批量插入数据）
start TRANSACTION;
INSERT INTO `boot_city` VALUES ('1324912501966925826', '227', '东安县', '2093')
,('1324912504676446210', '227', '双牌县', '2094')
,('1324912506161229825', '227', '道县', '2095')
,('1324912507595681794', '227', '江永县', '2096')
,('1324912509306957825', '227', '宁远县', '2097')
,('1324912511995506689', '227', '蓝山县', '2098')
,('1324912514667278338', '227', '新田县', '2099')
,('1324912516038815746', '227', '江华瑶族自治县', '2100')
,('1324912517494239234', '228', '鹤城区', '2101')
,('1324912520161816578', '228', '中方县', '2102')
,('1324912521667571714', '228', '沅陵县', '2103')
,('1324912523072663553', '228', '辰溪县', '2104')
,('1324912524578418690', '228', '溆浦县', '2105')
,('1324912526096756737', '228', '会同县', '2106')
,('1324912527514431490', '228', '麻阳苗族自治县', '2107')
,('1324912529036963841', '228', '新晃侗族自治县', '2108')
,('1324912530588856321', '228', '芷江侗族自治县', '2109')
,('1324912531956199426', '228', '靖州苗族侗族自治县', '2110')
,('1324912533394845697', '228', '通道侗族自治县', '2111');
COMMIT;

-- 创建存储过程
create procedure p1()
begin
    select * from boot_city;
end;

-- 调用
call p1();

-- 查看指定数据库存储过程及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'kcoud';

-- 查看某个存储过程定义
show create procedure p1;

-- 删除
drop procedure if exists p1;

4.变量

1.系统变量

查看系统变量

show [ session | global ] variables; -- 查看所有系统变量

show [ session | global ] variables like '...'; -- 通过模糊匹配方式查找变量

select @@[ session | global ] 系统变量名 -- 查看指定变量名

设置系统变量

set [ session | global ] 系统变量名 = 值;

set @@[ session | global ] 系统变量名 = 值;

** 注：没有指定session/global，默认是session会话变量**

• global（全局变量）：全局变量针对所有的会话
• session（会话变量）：针对单个会话，在另外的会话窗口就不生效

栗子

-- 查看系统变量
show GLOBAL variables;

show global VARIABLES like '%auto%';

SELECT @@global.autocommit;

-- 设置系统变量 ,global会全局更改,session会话窗口,在本窗口起作用,其他窗口不生效
set session autocommit = 1;

INSERT INTO `boot_city` VALUES ('1324912542521651201', '229', '双峰县', '2114');

set global autocommit =  0;

select @@global.autocommit;

2.定义变量

用户自己定义的变量

赋值

方式一
set @var_name = expr [, @var_name = expr] ..;

set @var_name := expr [, @var_name = expr] ..;

方式二
select @var_name := expr [, @var_name := expr] ..;

select 字段名 into @var_name from 表名;

使用

select @var_name;

栗子

-- 赋值
set @city_name := '广州深圳',@weather := '晴';
select count(1)into @cityNum from boot_city;
-- 使用
select @city_name,@weather;
select @cityNum;

3.局部变量

访问之前，需要declare声明，可作存储过程内的局部变量和输入参数，局部变量作用范围在begin..end块

声明

变量类型：int bigint char varchar date等
declare 变量名 变量类型 [default ...];

赋值

set 变量名 = 值;
set 变量名 := 值;
select 字段名 into 变量名 from 表名 ...;

栗子

-- 创建存储过程
create procedure p2()
begin
 declare city_num int default 0;
 select count(1) into city_num from boot_city;
 select city_num;
end;

-- 调用
call p2();

5.if

语法

if 条件1 then ...

elseif 条件2 then

else ...

end if;

栗子

create procedure p3()
begin
    declare score int default 50;
    declare result varchar(10);
    if score >= 85 then set result := '优秀';
    elseif score >= 60 then set result  := '及格';
    else set result := '不及格';
    end if;
    select result;
end;

call p3();

6.参数

介绍

int 参数作为输入，需要调用时传入值 

out 参数作为输出，改参数可以作返回值

inout 即可以作为输入参数，也可以作为输出参数

create procedure 存储过程名称 ([IN/OUT/INOUT 参数名 参数类型])

begin

      -- sql语句

end;

栗子

create procedure p4(in score int,out result varchar(10))
begin 
    if score >= 85 then set result := '优秀';
    elseif score >= 60 then set result := '及格';
    else set result := '不及格';
    end if;
end;

call p4(18,@result);

select @result;

create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;

set @score = 198;
call p5(@score);

select @score;

7.case

语法

case case_value        
     when when_value1 then statement_list1
     [when when_value2 then statement_list2]
     [else statement_list]

end case;

case 
     when search_condition1 then statement_list1
     [when search_condition2 then statement_list2] ...
     [else statement_list]
end case;

栗子

drop procedure if exists p6;
create procedure p6(in mon int)
begin
      declare result varchar(10);
            case 
                 when mon >= 1 and mon <= 3 then set result := '第一季度';
                     when mon > 3 and mon <= 6 then set result := '第二季度';
                     when mon > 6 and mon <= 9 then set result := '第三季度';
                     when mon > 9 and mon <= 12 then set result := '第四季度';
                     else set result := '非法参数';
            end case;
                     select concat('您输入月份:',mon,"，所属季度：" , result);
end;

call p6(12);

7.while

语法

WHILE 条件 DO
       SQL逻辑...
END WHILE;

栗子

drop procedure if exists p7;
create procedure p7(in n int)
begin
     declare total int default 0;
         while n > 0 do
         set total := total + n;
         set n := n - 1;
         end while;
         select total;
end;

call p7(100);

8**.repeat**

语法

REPEAT
          SQL逻辑...
          until 条件
END REPEAT;

栗子

drop procedure if exists p8;
create procedure p8(in n int)
begin
    declare total int default 0;
    repeat
    set total := total + n;
    set n := n - 1;
    until n <= 0
    end repeat;
    select total;
end;

call p8(10);
call p8(100);