前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql存储过程

Mysql存储过程

作者头像
用户3467126
发布2019-08-29 15:12:06
6.7K0
发布2019-08-29 15:12:06
举报
文章被收录于专栏:爱编码

简介

存储过程就是一条或者多条SQL语句的集合,可以视为批文件。它可以定义批量插入的语句,也可以定义一个接收不同条件的SQL。

存储函数与存储过程本质上是一样的,都是封装一系列SQL语句,简化调用。我们自己编写的存储函数可以像MySQL函数那样自由的被调用。

其实我用到最多的就是如果项目已经部署上线了,但是有些数据是出错了,导致无法正常使用,要修改数据表的方式来解决,但又涉及多个表联动操作的话,这个时候我第一时间就是用存储过程来解决,当然你也可以通过写Java代码来操作。

基础入门

本文基于mysql5.7以上版本

创建存储过程的语句为CREATE PROCEDURE,创建存储函数的语句为CREATE FUNCTION调用存储过程的语句为CALL。调用存储函数的形式就像调用MySQL内部函数一样。

例子:1、准备:创建表并插入数据

代码语言:javascript
复制
DROP TABLE IF EXISTS t_student;

CREATE TABLE t_student
(
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age  INT(11) NOT NULL
);

INSERT INTO t_student VALUES(NULL,'aaa',22),(NULL,'bbb',20);

2、创建存储过程:

代码语言:javascript
复制
DROP PROCEDURE IF EXISTS getStuById;

DELIMITER //  -- 定义存储过程结束符号为//
CREATE PROCEDURE getStuById(IN stuId INT(11),OUT stuName VARCHAR(255),OUT stuAge INT(11)) -- 定义输入与输出参数
COMMENT 'query students by their id'  -- 提示信息
SQL SECURITY DEFINER  -- DEFINER指明只有定义此SQL的人才能执行,MySQL默认也是这个
BEGIN
   SELECT name ,age INTO stuName , stuAge FROM t_student WHERE id = stuId; -- 分号要加
END // -- 结束符要加
DELIMITER ;  -- 重新定义存储过程结束符为分号

语法: CREATE PROCEDURE sp_name(定义输入输出参数) [ 存储特性 ] BEGIN SQL语句; END

IN 表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出的参数。sp_name为存储过程的名字

如果此存储过程没有任何输入输出,其实就没什么意义了,但是sp_name()的括号不能省略。

3、调用存储过程:下面是调用存储过程。对于存储过程提供的临时变量而言,MySQL规定要加上@开头。

代码语言:javascript
复制
#study 是当前数据库名称

CALL study.getStuById(1,@name,@age);

SELECT @name AS stuName,@age AS stuAge;

4、创建存储函数

代码语言:javascript
复制
DROP FUNCTION IF EXISTS getStuNameById;

DELIMITER //
CREATE FUNCTION getStuNameById(stuId INT)  -- 默认是IN,但是不能写上去。stuId视为输入的临时变量
RETURNS VARCHAR(255)   -- 指明返回值类型
RETURN  (SELECT name FROM t_student WHERE id = stuId); //  -- 指明SQL语句,并使用结束标记。注意分号位置
DELIMITER ;

5、调用存储函数

代码语言:javascript
复制
SELECT getStuNameById(1);

小结:从上述存储函数的写法上来看,存储函数有一定的缺点。首先与存储过程一样,只能返回一条结果记录。另外就是存储函数只能指明一列数据作为结果,而存储过程能够指明多列数据作为结果。

变量定义

如果希望MySQL执行批量插入的操作,那么至少要有一个计数器来计算当前插入的是第几次。这里的变量是用在存储过程中的SQL语句中的,变量的作用范围在BEGIN .... END 中。没有DEFAULT子句,初始值为NULL。

定义变量的操作:

代码语言:javascript
复制
DECLARE name,address VARCHAR;  -- 发现了吗,SQL中一般都喜欢先定义变量再定义类型,与Java是相反的。
DECLARE age INT DEFAULT 20; -- 指定默认值。若没有DEFAULT子句,初始值为NULL。

变量赋值:

代码语言:javascript
复制
SET name = 'jay';  -- 为name变量设置值

例子:

查询出age为mage的记录的数量。

代码语言:javascript
复制
DROP PROCEDURE IF EXISTS contStById;

DELIMITER //  -- 定义存储过程结束符号为//
CREATE PROCEDURE contStById(IN mage INT(11),OUT result INT(11)) -- 定义输入变量
BEGIN
    DECLARE sCount INT;
    SELECT COUNT(*) INTO sCount FROM t_student WHERE age= mage;
    SET result = sCount; -- 用变量为输出结果设值
END // -- 结束符要加
DELIMITER ;  -- 重新定义存储过程结束符为分号

CALL contStById(1,@result);
SELECT @result;

流程控制

1、IF语句的使用:

代码语言:javascript
复制
DROP PROCEDURE IF EXISTS testIf;
DELIMITER //
CREATE PROCEDURE testIf(OUT result VARCHAR(255))
BEGIN
     DECLARE val VARCHAR(255);
     SET val = 'a';
     IF val IS NULL
        THEN SET result = 'IS NULL';
        ELSE SET result = 'IS NOT NULL';
     END IF;
END //
DELIMITER ;

CALL testIf(@result);
SELECT @result;

2、CASE语句

代码语言:javascript
复制
DROP PROCEDURE IF EXISTS testCase;
DELIMITER //
CREATE PROCEDURE testCase(OUT result VARCHAR(255))
BEGIN
     DECLARE val VARCHAR(255);
     SET val = 'a';
     CASE val IS NULL
         WHEN 1 THEN SET result = 'val is true';
         WHEN 0 THEN SET result = 'val is false';
         ELSE SELECT 'else';
     END CASE;
END //
DELIMITER ;

CALL testCase(@result);
SELECT @result;

3、LOOP语句

LOOP用于重复执行SQL。LEAVE 用于退出循环。

下面一个批量插入的例子:

代码语言:javascript
复制
DROP TABLE IF EXISTS t_student;

CREATE TABLE t_student
(
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age  INT(11) NOT NULL
);

DROP PROCEDURE IF EXISTS testLoop;
DELIMITER //
CREATE PROCEDURE testLoop(IN columnCount INT(11))
BEGIN
     DECLARE id INT DEFAULT 0;
     add_loop:LOOP
    SET id = id + 1;
    IF id>columnCount THEN LEAVE add_loop;
    END IF;
       INSERT INTO t_student(id,name,age) VALUES(id,'dayu',22);
     END LOOP add_loop;
END //
DELIMITER ;

CALL testLoop(15);

4、WHILE语句

代码语言:javascript
复制
DROP PROCEDURE IF EXISTS testWhile;
DELIMITER //

CREATE PROCEDURE testWhile(IN myCount INT(11),OUT result INT(11))
BEGIN
   DECLARE i INT DEFAULT 0 ; -- 定义变量
   WHILE i < myCount DO  -- 符合条件就循环
       -- 核心循环SQL;  
       SET i = i + 1 ; -- 计数器+1
   END WHILE;       -- 当不满足条件,结束循环  --分号一定要加!
   SET result = i;  -- 将变量赋值到输出
END //


CALL testWhile(10,@result);
SELECT @result AS 循环次数;

调用过程

1、存储过程必须使用CALL语句来调用。如果要调用其它数据库的存储过程,需要指定数据库名称。例如 CALL dbname.spname

代码语言:javascript
复制
DELIMITER //  --存储过程分隔符设定为//
CREATE PROCEDURE CountStu(IN stu_sex CHAR,OUT num INT)  --stu_sex表示输入,num表示输出
BEGIN 
   SELECT COUNT(*) INTO num FROM t_student WHERE sex = stu_sex; --结果存入num,条件用上变量
END //        --以指定分隔符结束存储过程
DELIMITER ;   --存储过程分隔符设定为;

-- 调用存储过程
CALL CountStu('男',@num);
SELECT @num;

-- 本质为执行下面的SQL:

SELECT COUNT(*) AS @num 
FROM t_student WHERE sex = '男';

2、存储函数不需要使用CALL关键字。另外,存储函数的参数类型默认为IN输入。

代码语言:javascript
复制
DELIMITER //
CREATE FUNCTION countStu2(stu_sex CHAR) -- 默认是IN,OUT、INOUT不支持。效果是IN,但是不能加上IN
RETURNS INT
RETURN  (SELECT COUNT(*) FROM t_student WHERE sex = stu_sex); // -- 不加结束标记,运行失败
DELIMITER ;

-- 调用存储函数
SELECT countStu2('男');

游标

要处理存储过程中的结果集,请使用游标。游标允许您迭代查询返回的一组行,并相应地处理每行。

MySQL游标为只读,不可滚动和敏感。

1、只读:无法通过光标更新基础表中的数据。2、不可滚动:只能按照SELECT语句确定的顺序获取行。不能以相反的顺序获取行。此外,不能跳过行或跳转到结果集中的特定行。3、敏感:有两种游标:敏感游标和不敏感游标。敏感游标指向实际数据,不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快,因为它不需要临时拷贝数据。但是,对其他连接的数据所做的任何更改都将影响由敏感游标使用的数据,因此,如果不更新敏感游标所使用的数据,则更安全。MySQL游标是敏感的。

您可以在存储过程,存储函数和触发器中使用MySQL游标。

代码语言:javascript
复制
DELIMITER $$

USE `chy2019` $$

DROP PROCEDURE IF EXISTS `copy_order_data` $$

CREATE DEFINER = `root` @`%` PROCEDURE `copy_order_data` (IN p_source VARCHAR (100)) 
BEGIN
  -- 需要定义接收游标数据的变量 
  DECLARE done BOOLEAN DEFAULT 0 ;
  -- 自定义变量
  DECLARE var_price DOUBLE DEFAULT NULL ;
  DECLARE var_pay_time TIMESTAMP DEFAULT NULL ;
  DECLARE var_product VARCHAR (100) DEFAULT NULL ;
  DECLARE var_source VARCHAR (100) DEFAULT NULL ;
  -- 声明游标
  DECLARE cur CURSOR FOR
  -- 作用于哪个语句
  SELECT
    price,
    pay_time,
    product,
    source
  FROM
    cms_aw_order
  WHERE source = p_source ;
  -- 设置结束标志
  -- 这条语句定义了一个 CONTINUE HANDLER,它是在条件出现时被执行的代码。这里,它指出当 SQLSTATE '02000'出现时,SET done=1 。SQLSTATE '02000'是一个未找到条件,当REPEAT由于没有更多的行供循环而不能继续时,出现这个条件
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
  -- 打开游标
  OPEN cur ;
  -- 使用repeat循环语法
  REPEAT
    -- 批读取数据到指定变量上
    FETCH cur INTO var_price,
    var_pay_time,
    var_product,
    var_source ;
    -- 进行逻辑操作
    INSERT INTO cms_aw_order_copy (price, pay_time, product, source) 
    VALUES
      (
        var_price,
        var_pay_time,
        var_product,
        var_source
      ) ;
    -- 循环结束条件
    UNTIL done 
  END REPEAT ;
  -- 关闭游标
  CLOSE cur ;
END $$

DELIMITER ;

参考文章

https://blog.csdn.net/yanluandai1985/article/details/89632265 https://www.cnblogs.com/chywx/p/10397123.html

总结

MySQL的存储过程与存储函数有什么区别?存储函数只能通过return语句返回单个值或者表对象。存储过程不能用return,但是可以使用多个out参数返回多个值。

基本上存储过程的知识就是上面这些了。还有关于定义条件与定义处理程序,这里就不学了。你如果有需要,可以再参考文章中查看。如果对你有帮助可以收藏一下的哦。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-08-26,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 爱编码 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 简介
  • 基础入门
  • 变量定义
  • 流程控制
  • 调用过程
  • 游标
  • 参考文章
  • 总结
相关产品与服务
对象存储
对象存储(Cloud Object Storage,COS)是由腾讯云推出的无目录层次结构、无数据格式限制,可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限,无需分区管理,适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档