MySQL 存储过程

存储过程(Stored procedure)

---

简介

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

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

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

特点

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

基本语法

创建

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

调用

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

查询整个存储过程的定义

SHOW CREATE PROCEDURE 存储过程名称;

删除

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

变量赋值:

SET @p_in=1  

系统变量

1、介绍

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

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

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

2、语法：

查看系统变量

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

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

-- 查看指定变量的值
SELECT @@[SESSION. | GLOBAL.] 系统变量名;

设置系统变量

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

局部变量

1、介绍

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

2、语法

声明

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

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

赋值

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

if判断

语法：

IF 条件1 THEN
  ...
ELSEIF 条件2 THEN    -- 可选
  ...
ELSE                -- 可选
  ...
END IF;

参数(IN/OUT/INOUT)

参数

类型	含义	备注
IN	该类参数作为输入，也就是需要调用时传入值	默认
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

语法：

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

根据传入参数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; 

循环 while

语法

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

练习

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

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

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);

---