Mysql 存储过程

最新推荐文章于 2023-07-05 14:13:06 发布

莫茗岂妙

最新推荐文章于 2023-07-05 14:13:06 发布

阅读量1.7k

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/jingyu333/article/details/130884603

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章详细阐述了MySQL中的存储过程与函数的区别，包括返回值、参数类型和功能限制。同时，介绍了三种循环方式：while、repeat和loop，并展示了如何在存储过程中使用游标和嵌套循环。在调试过程中，作者强调了handler的重要性以及DECLARE和@变量的区别。

摘要由CSDN通过智能技术生成

Mysql SQL语言编程主要分为两种：一种是存储过程，一种是函数。

存储过程chai和函数的关系如下（摘抄于网络）：

1存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。

2.存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。

3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；

4.存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。

5.存储过程可以调用存储函数、但函数不能调用存储过程。

6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

Mysql三种循环：

第一种：while方式

WHILE ( tmpname IS NOT NULL) DO 

/*自己的业务逻辑(我是把字符串相加)*/

SET tmpName = CONCAT(tmpName ," ") ; 
SET temp_id = CONCAT(temp_id ,tmpName) ; 
FETCH cur1 INTO tmpName;
END WHILE;
CLOSE cur1;

第二种：Repeat方式

OPEN mycursor;
  REPEAT 
    FETCH mycursor INTO a;
     IF NOT done THEN
            SET temp_id=CONCAT(temp_id,a,' ');/*字符串相加,自己的业务逻辑*/
        END IF;
     UNTIL done END REPEAT;
CLOSE mycursor;

第三种：loop方式

OPEN cur1;  
     emp_loop: LOOP  
         FETCH cur1 INTO id;  
         IF done=1 THEN  
            LEAVE emp_loop; 
         END IF;  
          SET temp_id=CONCAT(temp_id,id,' ');/*字符串相加,自己的业务逻辑*/       
     END LOOP emp_loop;  
     CLOSE cur1;

但需要实现复杂SQL的时候需要循环嵌套定义多个游标。

use test;
DROP PROCEDURE IF EXISTS fun1;
/*声明结束符为$*/
DELIMITER $

/*创建函数*/
CREATE PROCEDURE  fun1( )

BEGIN
    /*用于保存结果*/

    /*创建一个变量，用来保存当前行中a的值*/
    DECLARE _id1 int DEFAULT 0;
    DECLARE time1 int DEFAULT 0;
    DECLARE newend1 VARCHAR(60);
    /*创建一个变量，用来保存当前行中b的值*/
    DECLARE newtime1 int DEFAULT 0;
    DECLARE start_time1 VARCHAR(60);

    /*创建游标结束标志变量*/
    DECLARE v_done int DEFAULT 0;

	DECLARE sped_ _id1 int DEFAULT 0;
	DECLARE sub_id1 int DEFAULT 0;
	DECLARE sub_max_speed DECIMAL(12,2);

    /*创建游标*/
    DECLARE cur_test1 CURSOR FOR
        SELECT _id,file_size,com_size,  avg_speed,start_time,time,
               CONVERT(com_size/1121*8, UNSIGNED)  as "newtime" ,
               end_time,
               adddate(start_time, interval  CONVERT(com_file_size/1121*8, UNSIGNED)   second) as newend
        from   _main ;

       -- where start_time >="2023-04-19 00:00:01" ;
	DECLARE cur_max_speed CURSOR FOR
        SELECT _id ,sub_id ,cur_speed
        from   _sub
        where _id = _id1;
	DECLARE continue handler for not found set v_done = 1;
    /*打开游标*/
    OPEN cur_test1;
    /*使用Loop循环遍历游标*/
		out_loop: LOOP
                FETCH cur_test1 INTO _id1,file_size1,com_size1,  avg_speed1,start_time1,time1,newtime1,end_time1,newend1;
				IF v_done  > 0 THEN
						LEAVE out_loop;
				END IF;
	        #使用游标（从游标中获取数据）
				SELECT  _id1,file_size1,com_size1,  avg_speed1,start_time1,time1,newtime1,end_time1,newend1;
				set time_new=CONVERT(com_file_size1/avg_speed_new, UNSIGNED);
				set  end_new=adddate(start_time1, interval  CONVERT(com_file_size1/avg_speed_new , UNSIGNED)   second);
				update   _main  set avg_speed = avg_speed_new,time =time_new,end_time = end_new  where _id = _id1;
				
				OPEN cur_max_speed;
				SET v_done = 0;
				REPEAT
					FETCH cur_max_speed INTO sped_ _id1,sub_id1,sub_max_speed;
                    set sub_max_speed = 138+ CONVERT(RAND( ) *60, UNSIGNED);
					SELECT sub_max_speed,sub_id1, _id1;
					update   _sub  set cur_speed = 144   where sub_id =sub_id1;
                until v_done   END REPEAT;
				 CLOSE cur_max_speed;
				 SET v_done = 0;
	  END LOOP out_loop;
	  CLOSE cur_test1;

    /*返回结果*/
END $
/*结束符置为;*/
DELIMITER ;

call fun1()

虽然定义了多个游标但是只能有一个handler，不能顶一个多个，这就需要灵活处理了。在调试的过程中遇到了一个问题，就是没有定义handler，只会打印外层的一条数据，不知道为什么，我猜想应该是内层循环的游标循环结束没有handler的处理无法继续进行下一条记录才导致只能打印一条外层循环的记录。反复看了很多遍也看不出来什么问题。最后添加上hanlder定义就好了。

Handler定义必须在游标之后定义。

Declare 和@的区别：

DECLARE 定义的变量的作用范围是BEGIN … END块内，只能在块中使用。 SET 定义的变量用户变量，作用范围是全局的，如果在存储过程中定义了用户变量，在存储过程之外的sql也是可以调用的。