【基础】存储过程

落日映苍穹つ 2022-07-17 00:14 189阅读 0赞

# 前言 #

Github：[https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql][https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]

# 概述 #

mysql 5.0开始支持存储过程，存储过程是存在数据库中的一段sql集合，调用存储过程可以减少很多工作量，

减少数据在数据库和应用服务器上的传输，对于提高数据处理的效率，同时注意，存储过程没有or replace的关键字，

mysql的存储过程参数包括 in，out，inout 三种模式。

**创建存储过程语法：**  
CREATE PROCEDURE proc\_name(proc\_parameter\[,...\])  
\[characteristic...\]  
routine\_body  
  
**参数代表含义：**  
proc\_parameter: \[IN | OUT | INOUT\]  param\_name type

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

routine\_body: 存储过程

**更改存储过程语法：**  
ALTER PROCEDURE proc\_name \[characteristic...\]  
  
characteristic:  
\{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA\}  
| SQL SECURITY \{DEFINER | INVOKER\}  
| COMMENT 'String'

**存储过程调用：**

CALL pro\_name(\[parameter,......\]);

# 一 实例分析准备条件 #

创建表：

CREATE TABLE `t_user_main` (
      `f_userId` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键',
      `f_userName` varchar(5) DEFAULT NULL COMMENT '用户名',
      `f_age` int(3) DEFAULT NULL COMMENT '年龄',
      PRIMARY KEY (`f_userId`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO t_user_main (f_userName, f_age) 
    VALUES('one',24),('two',25),('three',26),('four',27),('five',28),('six',29);

# 二 实例分析存储过程 #

创建一个简单的实例，该存储过程统计t\_user\_main表的条数，过程参数为out模式。

# 创建存储过程
    DELIMITER $$ -- 定界符
    # 开始创建存储过程
    CREATE PROCEDURE user_main_pro(OUT s INT)
    BEGIN
      SELECT COUNT(*) INTO s FROM t_user_main;
    END $$ # 创建存储过程结束
    DELIMITER;# 恢复数据库定界符

关于创建存储过程的分析：

mysql 中创建存储过程与oracle等数据库中不同的是，mysql需要使用定界符(DELIMITER),

因为mysql默认 ‘分号' 为运行结束，通过定界符指定结束符号，避免mysql创建存储过程

编译报错。当存储过程创建成功后，再通过DELIMITER ；将mysql语句结束符号恢复为分号。

这个地方使用out模式，存储过程再被调用时，会返回out参数，将t\_user\_main总条数返回，

通过变量接受存储过程返回的执行结果。

**存储过程调用：**

![Center][]

通过call去调用存储过程，set设置的变量接受call调用时存储过程out模式返回的处理结果。

in 和out一起使用：

DELIMITER $$
    CREATE PROCEDURE user_main_pro4(IN v_id INT,OUT v_count INT)
    BEGIN
    	SELECT COUNT(*) INTO v_count FROM t_user_main WHERE f_userId > v_id;
    END $$
    DELIMITER;

调用：

![Center 1][]

# 三 变量声明和赋值 #

MySql中变量从5.1后不区分大小写。

**变量的声明：**

通过DECLARE可以定义一个局部变量，变量的作用范围BEGIN...END块中；

变量语句必须卸载复合语句开头，并且在其他语句的前面;

一次性可以声明多个变量;  
  
变量定义语法：DECLARE var\_name\[,...\] type \[DEFAULT value\]

存储过程中定义变量的用法：

DELIMITER $$
    CREATE PROCEDURE user_main_pro3(IN v_id INTEGER)
    BEGIN
      #定义两个变量
      DECLARE v_userName VARCHAR(50);
      DECLARE v_userName2 VARCHAR(50);
      #set赋值
      SET v_userName = 's';
      #SELECT ... INTO 赋值
      SELECT f_userName INTO v_userName2 FROM t_user_main 
      WHERE f_userId = v_id;
      #DDL语句
      INSERT INTO t_user_main (f_userName) VALUES(CONCAT(v_userName,'*',v_userName2));
    END $$
    DELIMITER;

存储过程调用：

![Center 2][]

变量赋值：变量可以通过直接赋值，也可以通过查询语句赋值。

直接赋值语法：SET var\_name = expr\[,var\_name=expr\]...  
  
在上面存储过程中，定义一个v\_testSet变量，通过set直接赋值，eg:

DECLARE v\_testSet VARCHAR(50);

SET v\_testSet = 't';

通过select...INTO...赋值，通过这种方式赋值，要求查询返回只有一行结果，

使用语法：

SELECT col\_name\[,...\] INTO var\_name\[,...\] table\_expr;

eg:

SELECT f\_userName INTO v\_userName2 FROM t\_user\_main WHERE f\_userId = v\_id;

# **四 查看存储过程状态和定义语句** #

查看存储过程状态语法：

SHOW PROCEDURE STATUS \[LIKE 'pattern'\]

查看存储过程的定义语法：

SHOW CREATE PROCEDURE pro\_name;

eg:

![Center 3][]

# 五 删除存储过程 #

DROP PROCEDURE \[IF EXISTS\] pro\_name;

[https_github.com_yihonglei_road-of-arch_tree_master_thinking-in-mysql]: https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql
[Center]: /images/20220717/08415618cbc94589829139cc94b40282.png
[Center 1]: /images/20220717/6757089c18274296bdc967f98c067154.png
[Center 2]: /images/20220717/32f19584c6044ff48eb0c190e3c53358.png
[Center 3]: /images/20220717/ffd181be6baa4510af55637a65b96b7c.png