MySQL 存储过程

存储过程(Stored procedure)

---

简介

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

特点

• 封装、复用
• 可以接收参数，也可以返回数据
• 减少网络交互，效率提升

基本语法

创建

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
   -- SQL语句
END;
1
2
3
4

调用

CALL 存储过程名称([参数]);
1

查询整个存储过程的定义

SHOW CREATE PROCEDURE 存储过程名称;
1

删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;
1

变量赋值:

SET @p_in=1  
1

系统变量

1、介绍

系统变量时MySQL服务器提供，不是用户定义的，属于服务器层面。

可分为：全局变量（GLOBAL）、会话变量（SESSION）。

全局变量在所有会话中有效，会话变量仅在当前会话中有效。

2、语法：

查看系统变量

-- 查看所有系统变量
SHOW [SESSION | GLOBAL] VARIABLES;

-- 可以通过like模糊匹配方式查找变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE '...';

-- 查看指定变量的值
SELECT @@[SESSION. | GLOBAL.] 系统变量名;
1
2
3
4
5
6
7
8

设置系统变量

SET [SESSION | GLOBAL] 系统变量名 = 值;
SET @@[SESSION. | GLOBAL.]系统变量名 = 值;
1
2

局部变量

1、介绍

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块。

2、语法

声明

DECLARE 变量名 变量类型 [DEFAULT ...];
1

变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCAHR、DATE、TIME等。

赋值

SET 变量名 = 值;
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名 ...;
1
2
3

if判断

语法：

IF 条件1 THEN
  ...
ELSEIF 条件2 THEN    -- 可选
  ...
ELSE                -- 可选
  ...
END IF;
1
2
3
4
5
6
7

参数(IN/OUT/INOUT)

参数

语法：

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
  -- SQL语句
END;

1
2
3
4
5

根据传入参数score，判断当前分数对应的分数等级，并返回。

score >= 85分，等级为优秀；
score >= 60分，且score < 85分，等级为及格；
score < 60分，等级为不及格。

-- 创建存储过程
create procedure grade_core_2(in score int, out grade varchar(10))
begin
  if score >= 85 then
     set grade := '优秀';
  elseif score >= 60 then
     set grade := '及格';
  else
     set grade := '不及格';
  end if;
  
  select grade;
end;

-- 调用 
call grade_core_2(18, @result);
select @result; 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

循环 while

语法

# 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
   SQL逻辑...
END WHILE;

1
2
3
4
5

练习

1、查询score表的得分信息按降序排序，要求显示序号

SET @n=0;
SELECT s_id,s_score,@n:=@n+1 rank FROM `score` ORDER BY s_score DESC;
1
2

2、编写一个存储过程，通过该存储过程在teacher表中添加100条数据，仅添加ID从10开始的奇数数据

CREATE PROCEDURE proct ( IN num INT ) 
BEGIN
	DECLARE
		i INT;
	SET i = 10;
	WHILE
			i < num DO
			SET i = i + 1;
		IF
			i % 2 = 1 THEN
				INSERT INTO teacher (t_id, t_name)
			VALUES
				(i, CONCAT( 'xcz', i));
		END IF;
	END WHILE;
END;
call proct(210);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

---