【基础】存储过程

最新推荐文章于 2024-01-06 08:30:00 发布

街灯下的小草

最新推荐文章于 2024-01-06 08:30:00 发布

阅读量3.4k

点赞数 2

分类专栏：数据库文章标签： mysql procedure 存储过程数据库 declare

本文链接：https://blog.csdn.net/yhl_jxy/article/details/52290411

版权

数据库专栏收录该内容

30 篇文章 2 订阅

订阅专栏

前言

Github：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总条数返回，

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

存储过程调用：

通过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;

调用：

三变量声明和赋值

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;

存储过程调用：