【MySQL/MariaDB】存储过程学习记录

时间：2022-09-23 12:53:46

1.简介

存储过程 (Stored Procedure)，是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象，它可以视为数据库中的一种函数或子程序。

简单点说，存储过程可以将复杂的SQL封装成一个可调用的SQL方法（区别于SQL自带的函数），然后使用CALL调用，需要注意的是，存储过程是针对每一个数据库，即存储过程不能跨数据库使用。

2.特点

关于存储过程的优缺点，很多人都有自己的理解，可以看相关讨论：开源中国、知乎。

就我个人理解，对于业务场景不是很大的系统，不应过多地使用存储过程，而是将业务逻辑交于代码实现，统一操作的同时也方便理解。

总的说，存储过程有以下优缺点（来自*）：

2.1.优点

  • 存储过程可以回传值，并可以接受参数。
  • 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
  • 存储过程可以用在数据检验，强制实行商业逻辑等。

2.2.缺点

  • 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的预存程序。
  • 存储过程的性能调校与撰写，受限于各种数据库系统。

3.使用

下面介绍一下存储过程的一些简单实例，针对Mariadb，理论上与MySQL通用

先创建两张测试的表并插入几条数据：

CREATE TABLE `user` (
`user_sid` int(11) NOT NULL,
`user_account` varchar(50) NOT NULL,
`user_name` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
PRIMARY KEY (`user_account`),
KEY `user_sid` (`user_sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('1', 'admin', '管理员', 'admin');
INSERT INTO `user` VALUES ('2', 'jack', '普通成员', '123');

CREATE TABLE `role` (
`role_sid` int(11) NOT NULL,
`role_number` varchar(50) NOT NULL,
`role_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`role_number`),
KEY `role_sid` (`role_sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `role` VALUES ('1', '1', 'admin');
INSERT INTO `role` VALUES ('3', '2', 'editor');

3.1.创建

这是Mariadb官方的说明：

CREATE
    [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
 [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement

其中：

DEFINER ：表示存储过程的定义用户，用于上下文的权限检查
sp_name ：存储过程的名称
proc_parameter：存储过程的参数，定义格式为：（参数作用域 参数名 参数类型），其中参数作用域又分为IN OUT INOUT，其中IN值参数只在存储过程中可见，在存储过程外不受影响；OUT表示参数可以被存储过程修改并对外部可见；INOUT表示这个变量既能被当做输入，也能被当做输出
type：Mariadb中的数据类型，如int/varchar等
characteristic：存储过程特征？？？暂时没了解清楚

实例如图：

【MySQL/MariaDB】存储过程学习记录

IN作用域只在存储过程内 ↑

【MySQL/MariaDB】存储过程学习记录

OUT在存储过程内自动设置为NULL，并将结果返回到外部变量 ↑

【MySQL/MariaDB】存储过程学习记录

INOUT将变量声明为参数同时又作为返回值，并将结果返回到外部变量 ↑

3.2.查看

需要注意的是，存储过程是针对每一个数据库，

查看一个数据库的存储过程：

SHOW PROCEDURE STATUS WHERE db=’数据库名’;

查看一个存储过程的详情：

SHOW CREATE PROCEDURE 数据库.存储过程名;

3.3.使用

CALL 存储过程名（参数）

如上文的 ： CALL sp_count(@id);

3.4.删除

DROP PROCEDURE [IF EXISTS] sp_name

与删除数据库、表类似

4.Java中的应用

Java存储过程的使用主要还是围绕CallableStatement接口，其继承自PreparedStatement，所以操作与平时的JDBC差别不大，官方的解释：

CallableStatement用于执行 SQL 存储过程的接口。JDBC API 提供了一个存储过程 SQL 转义语法，该语法允许对所有 RDBMS 使用标准方式调用存储过程。此转义语法有一个包含结果参数的形式和一个不包含结果参数的形式。如果使用结果参数，则必须将其注册为 OUT 参数。其他参数可用于输入、输出或同时用于二者。参数是根据编号按顺序引用的，第一个参数的编号是 1。

   {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
   {call <procedure-name>[(<arg1>,<arg2>, ...)]}

IN 参数值是使用继承自 PreparedStatement 的 set 方法设置的。在执行存储过程之前，必须注册所有 OUT 参数的类型；它们的值是在执行后通过此类提供的 get 方法获取的。

CallableStatement 可以返回一个 ResultSet 对象或多个 ResultSet 对象。多个 ResultSet 对象是使用继承自 Statement 的操作处理的。

为了获得最大的可移植性，某一调用的 ResultSet 对象和更新计数应该在获得输出参数的值之前处理。

具体的API操作可以查看IBM这篇文章，讲的很详细。

5.引用

https://zh.wikipedia.org/wiki/%E5%AD%98%E5%82%A8%E7%A8%8B%E5%BA%8F

https://mariadb.com/kb/en/mariadb/sql-commands/

https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0809liuli/
标签：

相关文章