[数据库] MYSQL之存储过程

一存储过程的特点

MySQL 5.0 版本开始支持存储过程

1.1 定义

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程在思想上很简单，就是数据库 SQL 语言层面的【代码封装与重用】。

1.2 优点

存储过程【可封装】，并【隐藏复杂的商业逻辑】。
存储过程【可回传值】，并【可接受参数】。
存储过程【无法使用 SELECT 指令来运行】。因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程【可用在数据检验】，【强制实行商业逻辑】等。
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

1.3 缺点

尽管有这些缺陷，存储过程还是非常有用的，并且应该尽可能地使用。

【移植性：差】。往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
【编程开发的复杂程度：高】。一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
用户可能【没有创建存储过程的安全访问权限】。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

1.4 具体业务场景

迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。例如，考虑以下的情形。

为了处理订单，需要核对以保证库存中有相应的物品。
如果库存有物品，这些物品需要预定以便不将它们再卖给别的人，并且要减少可用的物品数量以反映正确的库存量。
库存中没有的物品需要订购，这需要与供应商进行某种交互。
关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的客户。

1.5 适用场景：简单、安全、高性能

1通过把处理封装在容易使用的单元中，【简化复杂的操作】。
2由于不要求反复建立一系列处理步骤，这【保证了数据的完整性和一致性】。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是【防止错误】。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
3【简化对变动的管理】。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是【安全性】。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
4【提高性能】。因为使用存储过程比使用单独的SQL语句要快。
5存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来【编写功能更强更灵活的代码】。

二存储过程的使用 -- 创建与调用

2.1 无参数的存储过程

-- DELIMITER $$ # [可选项]声明语句结束符 - 将语句的结束符号从分号;临时改为两个$$(即 可自定义)
DROP PROCEDURE IF EXISTS procedure_view_all_student;
CREATE PROCEDURE procedure_view_all_student() -- 查看所有学生信息
BEGIN -- 1执行段 - 起始 
    DECLARE varName INT DEFAULT 0;  -- 变量 de 声明
    SET varName = 100+1; -- 变量 de 赋值(SET =) 1
    # 变量(varName)
    # 变量的使用： 必须先声明，才能后使用
    # 变量的声明格式： DECLARE variable_name datatype(size) [DEFAULT default_value];
    SELECT COUNT(*) INTO varName FROM Student; -- 变量 de 赋值(SELECT INTO) 2
    SELECT varName AS '总人数'; -- 变量 de 输出
END; -- 2执行段 - 结束
-- END $$

CALL procedure_view_all_student;

2.2 仅含输入参数(IN)的存储过程

DROP PROCEDURE IF EXISTS procedure_view_select_course_of_one_student; -- 查看 指定某一学生的选课信息
CREATE PROCEDURE procedure_view_select_course_of_one_student(IN sno CHAR(9))
BEGIN
	SELECT * FROM select_course AS sc WHERE sc.sno = sno;
END;

CALL procedure_view_select_course_of_one_student('201215121');

2.3 仅含输出参数(OUT)的存储过程

DROP PROCEDURE IF EXISTS procedure_view_total_student; -- 查看 总共的学生数目
CREATE PROCEDURE procedure_view_total_student(OUT total INT)
BEGIN
	SELECT COUNT(*)
		INTO total
		FROM select_course AS sc;
END;

CALL procedure_view_total_student(@total_student);
SELECT @total_student AS '学生总人数';

2.4 含又作输出又作输入参数(INOUT)的存储过程

DROP PROCEDURE IF EXISTS procedure_create_huge_test_records_for_student; 
CREATE PROCEDURE procedure_create_huge_test_records_for_student(INOUT records_num INT) -- 插入 指定数目的学生信息的测试数据
BEGIN
	DECLARE sno CHAR(9);
	DECLARE sname VARCHAR(30);
	DECLARE ssex VARCHAR(2) DEFAULT '男';
	DECLARE sage SMALLINT DEFAULT 29;
	DECLARE sdept VARCHAR(50) DEFAULT 'MS';

	DECLARE counter INT;
	SET counter = records_num;
	START TRANSACTION; -- 整个存储过程指定为1个事务
	-- ↑ 大量插删数据时，追加事务可避免插删每条数据时频繁的磁盘IO交互，便不再受磁盘IO限制，暂存到了内存缓存区，以此大大提高速度
		WHILE counter >= 1 DO
			SELECT MID(UUID(), 1, 9) INTO sno; -- 从36位的字符串UUID()中的第1个位置截取9个字符：
			SELECT MID(UUID(), 1, 5) INTO sname;
			insert into Student (`sno`, `sname`, `ssex`, `sage`, `sdept`) VALUES(sno, sname, ssex, sage, sdept);
			SET counter = counter - 1;
		END WHILE;
	COMMIT; -- 必须主动提交
END;

SET @records_num = 200000; #插入20万条数据 总计 36.296秒(启用了事务 start transaction + sql... + commit)
call procedure_create_huge_test_records_for_student(@records_num);
SELECT * FROM student WHERE student.sno= '1231546-';

2.5 循环存储过程

2.5.1 简单示例

# DEMO - 存储过程 - 循环篇
-- CALL testloop(@out1); SELECT @out1 as out; --out := 11
CREATE PROCEDURE testloop(OUT outvalue INT(11))
BEGIN
	DECLARE max_value INT(11) DEFAULT 10;
	DECLARE i INT(11);   -- 局部变量(i , max_value) 必须先声明，后使用
	SET i = 0;	
	myLoop:LOOP
		SET i = i+1;
		IF i > max_value THEN
			LEAVE myLoop;
		END IF;
	END LOOP myLoop;
	SET outvalue = i;
END

2.5.2 自动插入N条记录

/**
 * 自动插入 `Person`表 amount 条记录
 * @param in  amount : 插入自动测试记录的总数
 * @param out result: 执行结果
 */
# 1 [调用存储过程]
-- SET @number = 10; CALL loopInsertRecords(@number, @exe_result); SELECT @exe_result as `执行结果`; 
-- `执行结果` := [执行结果]成功10; 失败:0 
# 2 [创建 存储过程]
DROP PROCEDURE IF EXISTS `loopInsertRecords`;
CREATE PROCEDURE loopInsertRecords(IN amount INT(10), OUT result VARCHAR(100))
BEGIN
	DECLARE i INT(10) DEFAULT 1; -- 局部变量 必须先声明，后使用
	DECLARE success INT DEFAULT 0;
	DECLARE fail INT DEFAULT amount;
	DECLARE hasSqlError INT DEFAULT FALSE; -- 声明1个变量，标识是否有SQL异常
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE; -- 在执行过程中出任何SQL异常， 则: 设置hasSqlError为TRUE
	myLoop:LOOP
		SET i = i+1;
		START TRANSACTION; -- transaction start
			INSERT INTO `CJ_TEST`.`Person` (
				`CSSJY111`, 
				`CSSJY107`, 
				`CSSJY106`, 
				`CSSJY112`, 
				`CSSJY103`, 
				`CSSJY113`, 
				`CSSJY104`, 
				`CSSJY108`, 
				`CSSJY102`, 
				`CSSJY109`, 
				`CSSJY101`, 
				`CSSJY110`, 
				`CSSJY105`
			)  VALUES (
				SUBSTR(REPLACE(UUID(), "-", "@"),1, 3), 
				1, 
				2, 
				DATE_ADD(NOW(), INTERVAL -10 DAY), -- INTERVAL -N DAY: N天前；  INTERVAL N DAY: N天后
				SUBSTR(REPLACE(UUID(), "-", "@"),1, 10), 
				"blob-data", 
				SUBSTR(REPLACE(UUID(), "-", "#"),1, 10), 
				SUBSTR(REPLACE(UUID(), "-", "$"),1, 10), 
				SUBSTR(REPLACE(UUID(), "-", "%"),1, 10), 
				SUBSTR(REPLACE(UUID(), "-", "&"),1, 10), 
				SUBSTR(REPLACE(UUID(), "-", "*"),1, 10), 
				DATE_ADD( NOW(), INTERVAL 10 DAY ), -- INTERVAL -N DAY: N天前；  INTERVAL N DAY: N天后 
				( SELECT ROUND( exp(sum(log(RAND())+log(100))) , 0 ) )
			);
		IF hasSqlError THEN  -- 根据hasSqlError判断是否有异常，做回滚和提交操作
			ROLLBACK;
		ELSE
			COMMIT; -- transaction end
			SET success = success+1;
		END IF;
		IF i > amount THEN
			LEAVE myLoop;
		END IF;
	END LOOP myLoop;
	SET fail = amount - success;
	SET result = CONCAT("[执行结果]成功", success, "; 失败:", fail);
END

三存储过程的使用 -- 查看与删除

3.1 查看指定表的存储过程

3.1.1 查看表内的存储过程【基本信息】

[方式1]

-- 语法格式: SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]
SHOW PROCEDURE STATUS; -- 存储过程
  show create procedure <proc_name>;

SHOW FUNCTION STATUS; -- 函数

SELECT * FROM information_schema.VIEWS -- 视图
SELECT * FROM information_schema.TABLES -- 表

SHOW TRIGGERS [FROM db_name] [LIKE expr] -- 触发器
SELECT * FROM triggers T WHERE trigger_name="mytrigger" \G

[方式2]

select `name` from mysql.proc where db = 'sys' and `type` = 'PROCEDURE' -- 存储过程
  -- mysql 5.x - 7.x 默认支持此表；
  -- mysql 8.0 默认不支持此表；解决方法：数据层引用的 MySql.Data.dll 这个插件版本过低导致的，可尝试更新官网最新的  MySql.Data.dll (https://www.cnblogs.com/xuling-297769461/p/10570040.html)

[方式3] 【推荐】

-- 查看指定数据库的所有存储过程
> SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'test' AND ROUTINE_TYPE = 'PROCEDURE'; -- test 库
AddColumnIfNotExists

3.1.2 查看表内的存储过程【详细信息】

> -- SHOW CREATE PROCEDURE indexName;
> SHOW CREATE PROCEDURE test.AddColumnIfNotExists;
Procedure           |sql_mode           |Create Procedure                                                                                                                                                                                                                                               |character_set_client|collation_connection|Database Collation|
--------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+------------------+
AddColumnIfNotExists|STRICT_TRANS_TABLES|CREATE DEFINER=`root`@`%` PROCEDURE `AddColumnIfNotExists`(¶    IN dbName tinytext,¶    IN tableName tinytext,¶    IN fieldName tinytext,¶    IN fieldDef text¶)¶begin¶    IF NOT EXISTS (¶        SELECT ¶   * ¶  FROM information_schema.COLUMNS¶        WHER|utf8mb4             |utf8mb4_general_ci  |utf8_general_ci   |

3.2 删除存储过程

DROP PROCEDURE IF EXISTS indexName;

Y 经典应用场景

CASE 当列不存在时方新增列

需求背景: 亲测 MYSQL 5.7.37 / 8.0.39 等均不支持在增/删列时判断列的存在性(IF NOT EXISTS)的语法

-- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT exists email VARCHAR(255) NOT NULL' at line 1
ALTER TABLE `test`.`t_user` ADD COLUMN IF NOT EXISTS `email` VARCHAR(255) NOT NULL;

解决思路：利用存储过程，判断列是否存在，尔后生成对应的DDL，执行之
Step1 创建存储过程

use test;

drop procedure if exists test.AddColumnIfNotExists;

create procedure AddColumnIfNotExists(
    IN dbName tinytext,
    IN tableName tinytext,
    IN fieldName tinytext,
    IN fieldDef text
)
begin
    IF NOT EXISTS (
        SELECT 
			* 
		FROM information_schema.COLUMNS
        WHERE 1=1
			and column_name = fieldName
			and table_name = tableName
			and table_schema = dbName
    ) THEN
        set @ddl=CONCAT('ALTER TABLE ', dbName, '.', tableName, ' ADD COLUMN ', fieldName, ' ', fieldDef);
        prepare stmt from @ddl;
        execute stmt;
    END IF;
end;

Step2 查看存储过程是否创建成功

> SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'test' AND ROUTINE_TYPE = 'PROCEDURE';
AddColumnIfNotExists

> SHOW CREATE PROCEDURE test.AddColumnIfNotExists;
Procedure           |sql_mode           |Create Procedure                                                                                                                                                                                                                                               |character_set_client|collation_connection|Database Collation|
--------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+------------------+
AddColumnIfNotExists|STRICT_TRANS_TABLES|CREATE DEFINER=`root`@`%` PROCEDURE `AddColumnIfNotExists`(¶    IN dbName tinytext,¶    IN tableName tinytext,¶    IN fieldName tinytext,¶    IN fieldDef text¶)¶begin¶    IF NOT EXISTS (¶        SELECT ¶   * ¶  FROM information_schema.COLUMNS¶        WHER|utf8mb4             |utf8mb4_general_ci  |utf8_general_ci   |

Step2 使用存储过程

-- 表不存在时，会报: SQL 错误 [1146] [42S02]: Table 'test.t_user' doesn't exist
-- 创建成功后，执行成功。通过 show create table test.t_user 可查之
call test.AddColumnIfNotExists(DATABASE(), 't_user', 'email', 'varchar(255) NULL'); 


-- 对一些特殊的字段名（如order）、数据库名或表名（如含有-_等特殊字符），使用你``引起来
call test.AddColumnIfNotExists('`test`', '`t_user`', '`order`', 'varchar(50) NULL');

【补充】java spring + mysql 数据库的应用程序中，还可以尝试：更简单粗暴地，你可在初始化数据库时直接忽略错误.

未亲测

<!-- 在初始化数据库时，遇到错误是否继续，默认false -->
spring.datasource.continue-on-error=true

参考文献

Mysql - add a column to a table if not exists - CSDN

mysql添加字段,如果有则不添加 - CSDN

SQLite ALTER TABLE 添加列（如果不存在）在SQLite中的使用 - 极客教程

SQLLite数据库原生支持本特性: ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name data_type;

X 参考文献

posted @ 2020-03-20 22:11 千千寰宇阅读(394) 评论(0) 编辑收藏举报

刷新页面返回顶部

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。

[数据库] MYSQL之存储过程

一存储过程的特点

1.1 定义

1.2 优点

1.3 缺点

1.4 具体业务场景

1.5 适用场景：简单、安全、高性能

二存储过程的使用 -- 创建与调用

2.1 无参数的存储过程

2.2 仅含输入参数(IN)的存储过程

2.3 仅含输出参数(OUT)的存储过程

2.4 含又作输出又作输入参数(INOUT)的存储过程

2.5 循环存储过程

2.5.1 简单示例

2.5.2 自动插入N条记录

三存储过程的使用 -- 查看与删除

3.1 查看指定表的存储过程

3.1.1 查看表内的存储过程【基本信息】

3.1.2 查看表内的存储过程【详细信息】

3.2 删除存储过程

Y 经典应用场景

CASE 当列不存在时方新增列

X 参考文献

公告

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。

[数据库] MYSQL之存储过程

一 存储过程的特点

1.1 定义

1.2 优点

1.3 缺点

1.4 具体业务场景

1.5 适用场景：简单、安全、高性能

二 存储过程的使用 -- 创建与调用

2.1 无参数的存储过程

2.2 仅含输入参数(IN)的存储过程

2.3 仅含输出参数(OUT)的存储过程

2.4 含又作输出又作输入参数(INOUT)的存储过程

2.5 循环存储过程

2.5.1 简单示例

2.5.2 自动插入N条记录

三 存储过程的使用 -- 查看与删除

3.1 查看指定表的存储过程

3.1.1 查看 表内的存储过程 【基本信息】

3.1.2 查看 表内的存储过程 【详细信息】

3.2 删除 存储过程

Y 经典应用场景

CASE 当列不存在时方新增列

X 参考文献

公告

一存储过程的特点

二存储过程的使用 -- 创建与调用

三存储过程的使用 -- 查看与删除

3.1.1 查看表内的存储过程【基本信息】

3.1.2 查看表内的存储过程【详细信息】

3.2 删除存储过程