AI 助理
备案控制台
开发者社区 数据库 文章 正文

mysq 5.7 存储过程

2022-05-26 143
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 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 ] 系统变量名 -- 查看指定变量名


设置系统变量


1. set [ session | global ] 系统变量名 = 值;
2. 
3. 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);
文章标签：
SQL
数据库
存储
小寇
目录
相关文章
进步`于辰
|
25天前
|
存储 SQL 关系型数据库
[MySQL]存储过程
本文介绍了存储过程的概念、优点和缺点，并通过具体示例详细讲解了存储过程的创建、使用和调用方法。此外，还介绍了游标的定义和使用，帮助读者更好地理解和应用存储过程。文章旨在为读者提供一个学习和使用存储过程的实用指南。
进步`于辰
74 18
[MySQL]存储过程
云梦泽123
|
6月前
|
存储
MySQL-存储过程
MySQL-存储过程
云梦泽123
69 1
云梦泽123
|
6月前
|
存储 关系型数据库 MySQL
MySQL 中的存储过程-1
MySQL 中的存储过程
云梦泽123
61 2
云梦泽123
|
6月前
|
存储 SQL 关系型数据库
MySQL 中的存储过程-2
MySQL 中的存储过程
云梦泽123
33 0
云梦泽123
|
6月前
|
存储 关系型数据库 MySQL
MySQL 中的存储过程-3
MySQL 中的存储过程
云梦泽123
39 0
流烟默
|
11月前
|
存储 SQL 关系型数据库
MySQL中存储过程使用详解
MySQL中存储过程使用详解
流烟默
439 0
龙腾九州
|
存储 SQL 关系型数据库
认识MySQL的存储过程
认识MySQL的存储过程。
龙腾九州
68 4
小郭的技术笔记
|
存储 SQL 关系型数据库
【MySQL】技多不压身，存储过程你会了吗？
今天我们来学习存储过程，在几年前的系统中存储过程随处可见，他的优势就是提高数据库的安全性和数据的完整性，我们可以利用存储过程完成一系列的流程而无需在代码中反复跳转，但是他的缺点也暴露了出来，开发与维护的难度时间越长难度越大，我们来看看他的神奇之处。
小郭的技术笔记
143 0
力不竭战不止
|
存储 SQL 关系型数据库
mysql使用存储过程
mysql使用存储过程
力不竭战不止
155 0
我是咔咔
|
存储 SQL 缓存
【MySQL】存储过程
【MySQL】存储过程
我是咔咔
201 0
【MySQL】存储过程