存储过程学习，很全很细，有语法 | 有例子 | 批量造数据100万 | 附件带脚本

最新推荐文章于 2024-08-06 11:14:15 发布

keep one's resolveY

最新推荐文章于 2024-08-06 11:14:15 发布

阅读量262

点赞数

分类专栏：数据库+ES 文章标签：数据库

原文链接：https://blog.csdn.net/weixin_42360608/article/details/131739311?spm=1001.2014.3001.5506

版权

数据库+ES 专栏收录该内容

28 篇文章 4 订阅

订阅专栏

文章目录

- 1. 数据库 - 存储过程

1. 数据库 - 存储过程

1.1 存储过程 - 简介

1.1.1 存储过程 - 定义

存储过程其实很简单，就是数据库 SQL 脚本层面的代码封装与重用，创建编译并保存在数据库中

1.1.2 存储过程 - 特点

能完成较复杂的判断和运算（能做到 SQL 查询做不到的事情）
SQL 编程的代码可重复使用（提高代码复用性）
存储过程执行的速度很快（没有网络开销）
存储过程无法使用 SELECT 指令来运行，需要使用 CALL 来调用

1.2 存储过程 - 语法学习

1.2.1 存储过程 - 创建

-- 1、存储过程基本语法
CREATE PROCEDURE 名称()
BEGIN
-- SQL语集
END;

1.2.2 存储过程 - 示例

-- 1、一段完整的存储过程
CREATE PROCEDURE test()
BEGIN
	select * from t_student;
	select * from t_teacher;
END;

1.2.3 存储过程 - 调用

CALL test();
-- 思考：别的方式调用存储过程

1.2.4 存储过程 - 变量

CREATE PROCEDURE test1()
BEGIN
	-- 1、使用 DECLARE 语句声明变量
	DECLARE int_type INT DEFAULT 0; -- 声明整型字段
	DECLARE str_type VARCHAR(10) DEFAULT '';
 
	-- 2、使用set语句给变量赋值
	SET str_type = '字符串';
 
	-- 3、使用SELECT返回数据
	SELECT str_type;
END;

1.2.5 存储过程 - 参数

参数类型

in 类型用于参数传入（默认就是in类型）
out 类型是传出参数（不能传入参数值）
inout 类型参数集合了 IN 和 OUT 类型的参数功能

存储过程传参 - 示例

-- 1、使用IN和OUT关键字
CREATE PROCEDURE test2 (
  IN stu_no VARCHAR (10),
  OUT stu_name VARCHAR (20)
)
BEGIN
  SELECT
  stu_name INTO stu_name
  FROM
  student
  WHERE
  stu_no = stu_no;
END;
-- 2、调用上方存储过程
CALL test2("stu_01",@out_stu_name);
SELECT @out_stu_name ; -- 输出存储过程返回数据

1.2.6 存储过程 - 条件

条件语句 - 语法

-- 1、判断条件一
if(判断条件) then
	...
else
	...
end if;

-- 2、判断条件二
if(判断条件) then
	...
elseif(判断条件) then
	...
else
	...
end if;

条件语句 - 示例

本次考试，指定学生学号加分
年龄小于 20 岁的同学，每科增加 20 分
年龄大于 30 岁的同学，每科增加 10 分
其他考生的同学，每科增加 5 分

CREATE PROCEDURE test3(IN in_stu_no VARCHAR(10))
BEGIN
	-- 1、声明一个年龄变量
	DECLARE my_age INT DEFAULT 0;

	-- 2、查询指定学生的年龄
	SELECT age INTO my_age FROM t_student WHERE stu_no=in_stu_no;

IF(my_age < 20) THEN
-- 3、年龄小于20岁的同学，每科增加20分
UPDATE t_score SET stu_score = stu_score + 20 WHERE stu_no = in_stu_no;

ELSEIF(my_age > 30) THEN
-- 4、年龄大于30岁的同学，每科增加10分
UPDATE score SET stu_score = stu_score + 10 WHERE stu_no = in_stu_no;

ELSE
-- 5、其他考生的同学，每科增加5分
UPDATE score SET stu_score = stu_score + 5 WHERE stu_no = in_stu_no;
END IF;
END;

1.2.7 存储过程 - 循环

条件语句 - 语法

while(判断条件) do
	-- SQL语集
end while;

循环语句 - 示例

批量插入1000条数据
学生学号是唯一
学生姓名是唯一

CREATE PROCEDURE test4()
BEGIN
	DECLARE i INT DEFAULT 0;
	WHILE(i < 1000) do
		BEGIN
			SET i=i+1;
			INSERT INTO t_student
			(stu_no,stu_name,age,sex,create_date)
			VALUES (concat('stu_',i), concat('刘备_',i),
			'42', '男', '2023-10-01 00:00:00');
		END;
	END WHILE;
END;

1.3 存储过程 - 实战

造数据需求：一张用户表 t_user 需要插入100 万条数据，具体如何实现呢？

1.3.1 数据准备

云盘链接地址：存储过程初始化脚本

表结构如下

序号	字段	说明
1	id	主键（自动增长）
2	user_name	用户名称（不允许为空）
3	sex	性别（1或0）
4	age	年龄（18~70）
5	phone	手机号码（唯一）
6	create_time	创建时间

1.3.2 存储过程实战

1.3.2.1 数据表分析

分析数据表 t_user ，查看字段类型、确定表中是否有外键、索引、主键

1.3.2.2 编写存储过程

创建一个存储过程基本结构

CREATE PROCEDURE 存储过程名称()
BEGIN
-- ...实现的逻辑代码...
END;

声明变量语法

CREATE PROCEDURE 存储过程名称()
BEGIN
  -- 声明一个电话号码变量
  DECLARE phone INT DEFAULT 13100000000;
END;

声明循环语法

CREATE PROCEDURE 存储过程名称()
BEGIN
	-- 声明一个电话号码变量
	DECLARE phone INT DEFAULT 13100000000;
	-- 循环100次（可以插入100条数据）
	WHILE(i < 100) do
		BEGIN
		-- 实现具体的插入语句
		END;
	END WHILE;
END;

往用户表插入100万条数据

CREATE PROCEDURE userDataInit()
BEGIN
	DECLARE sex INT DEFAULT 0; -- 性别
	DECLARE age INT DEFAULT 0; -- 年龄
	DECLARE userName VARCHAR(25) DEFAULT ''; -- 姓名
	DECLARE phone VARCHAR(11) DEFAULT 13100000000;
	-- 循环100万次（可以插入100万条数据）
	DECLARE i INT DEFAULT 0;

	WHILE(i < 1000000) do
	  BEGIN
		-- 随机生成性别0或1
		SET sex = (SELECT FLOOR(RAND()*2));
		-- 性别（随机生成18~70）
		SET age = 18 + (SELECT FLOOR(RAND()*53));
		-- 随机姓名
		SET userName = concat('张',(SELECT FLOOR(RAND()*1000000)));
		SET phone = phone + 1; -- 每次循环后把手机号码 + 1
		
		-- 编写插入数据
		INSERT INTO t_user (user_name,sex,age,phone,create_time) VALUES (userName, sex,age, phone, '2023-10-01 00:00:00');
		
-- 每次循环后+1
		SET i = i + 1;
		END;
	END WHILE;
END;