MySQL存储过程实战二：语法讲解。

最新推荐文章于 2022-05-11 09:00:10 发布

现场喷口水

最新推荐文章于 2022-05-11 09:00:10 发布

阅读量474

点赞数

分类专栏： MySQL 文章标签： MySQL 存储过程

本文链接：https://blog.csdn.net/wrathli/article/details/103778655

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

先展示一段完整的存储过程代码作为例子方便进行语法说明。代码如下：

--##########定义课程分享量存储过程######################
delimiter $$
DROP PROCEDURE IF EXISTS statisCourseShareCount;
CREATE PROCEDURE statisCourseShareCount(IN day varchar(32),IN month varchar(32))
	BEGIN
		DECLARE d BOOLEAN DEFAULT FALSE;
		DECLARE course_id_value BIGINT(20) DEFAULT 0;
		DECLARE t_count_value BIGINT(20) DEFAULT 0;
		DECLARE search_table varchar(200);
		DECLARE param varchar(200);
		DECLARE existsFlat int default 0;
		DECLARE ownerMemberId BIGINT(20) DEFAULT 0;
		DECLARE tmpShopId BIGINT(20) DEFAULT 0;
		DECLARE tmpCategoryId BIGINT(20) DEFAULT 0;
		DECLARE tmpCategoryPath VARCHAR(100) DEFAULT '';
		DECLARE tmpSubjectId BIGINT(20) DEFAULT 0;
		DECLARE tmpCourseType INT(10) DEFAULT 0;
		DECLARE tmpClassId BIGINT(20) DEFAULT 0;
		DECLARE tmpGradeId BIGINT(20) DEFAULT 0;
		DECLARE tmpGradePath varchar(100) DEFAULT '';
		DECLARE paperCount INT(10) DEFAULT 0;

		DECLARE share_count_cursor CURSOR FOR SELECT * FROM course_share_count_view;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET d = TRUE;

		SET search_table = CONCAT('youke_course_share_',month);
		SET param = CONCAT("'",day,"'");
		SET @cursor_sql = CONCAT('CREATE VIEW course_share_count_view as select course_id,count(id) as t_count from ',search_table,' where DATE(create_date) = ',param,' group by course_id');
		select @cursor_sql;
		PREPARE stmt from @cursor_sql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;

		
		OPEN share_count_cursor;
		read_loop: LOOP
			FETCH share_count_cursor INTO course_id_value,t_count_value;
			IF d THEN
					LEAVE read_loop;
			ELSE
					SET existsFlat = 0;
					select count(id) into existsFlat from youke_statis_course_summary where course_id = course_id_value LIMIT 1;
					IF existsFlat > 0 THEN
						UPDATE youke_statis_course_summary set share_count = share_count + t_count_value where course_id = course_id_value;
					ELSE 
						SET paperCount = 0;
						select OwerID,ShopId,CategoryId,CategoryPath,CourseType into ownerMemberId,tmpShopId,tmpCategoryId,tmpCategoryPath,tmpCourseType from himall_products where Id = course_id_value;
						select Id,CategoryId,CategoryPath into tmpClassId,tmpGradeId,tmpGradePath from himall_grades where MemberId = ownerMemberId;
						IF tmpCourseType = 1 THEN
							select count(Id) into paperCount from himall_paperlist where ProductId = course_id_value and `Delete` = 0;
						END IF;
						INSERT INTO youke_statis_course_summary(course_id,owner_member_id,shop_id,category_id,category_path,subject_id,course_type,class_id,grade_id,grade_path,share_count,paper_count) 
						values(course_id_value,ownerMemberId,tmpShopId,tmpCategoryId,tmpCategoryPath,statisSplit(tmpCategoryPath,'|',2),tmpCourseType,tmpClassId,tmpGradeId,tmpGradePath,t_count_value,paperCount);
					END IF;
				
			END IF;
		END LOOP;
		CLOSE share_count_cursor;
		DROP VIEW course_share_count_view;
	END;
 $$

1.delimiter声明

delimiter的作用是改变执行语句的分号，比如delimiter $$，那么创建语句就是以$$为结尾构成一句完整的创建语句，而不是遇到分号就自动结束了。

2.删除存储过程，创建存储过程，IN，OUT, INOUT参数

创建存储过程之前必须执行一个删除操作，避免之前残留的代码影响创建过程。

DROP PROCEDURE IF EXISTS myProcedure;

存储过程运行传入多个输入、输出参数，输入参数使用IN修饰，输出参数使用OUT修饰，输入输出参数INOUT修饰。

输入参数使用：

CREATE PROCEDURE myProcedure(IN param1 varchar(32),IN param2 varchar(32))

BEGIN

...

END;

调用存储过程传入参数

Call myProcedure(‘参数1’,’参数2’);

输出参数使用:

CREATE PROCEDURE myProcedure(IN input varchar(32),OUT output varchar(32))

BEGIN

...

END;

调用存储过程：

set @myOutput=’我的输出’;

call myProcedure(‘我的输入’,@myOutput);

输入输出参数使用:

CREATE PROCEDURE myProcedure(INOUT inoutValue varchar(32))

BEGIN

...

END;

调用存储过程：

set @myInout=’我的输入输出’;

Call myProcedure(@myInout);

3.声明变量的三种方式

A) DECLARE

这种方式定义的变量，语句的位置必须在begin下,同时要带上参数类型。

语句

begin

DECLARE tname VARCHAR(50);

赋值

set tname =‘XXX’;

另外需要注意的是使用这种方式声明变量，set语句必须在所有DECLARE语句之后，否则就会编译失败。例如

DECLARE a VARCHAR(50);

set a=’XXX’;

DECLARE b VARCHAR(50);

就会失败。

B) set

这种方式定义的变量，定义同时赋值，语句的位置可以任意出现，不需要带上参数类型，但定义的变量是会话变量，如果想定义局部变量，那么变量名加上'@'。

语句

set @aa='XXX';

C) select

性质同上一个set，但是这个可以通过插询记录去赋值

语句

select @var=cnm FROM tbnm;

set和select其实可以相互转化,下面两句是相同的

set @var=(select cnm FROM tbnm);

select @var=cnm FROM tbnm;

4.字符串拼接，动态表名和动态参数

存储过程中的SQL语句如果要使用动态表名或者动态参数的时候，需要利用预处理语句来实现。

首先讲解一个创建动态表的例子

声明一个对象存储动态表名

DECLARE comment_report_table VARCHAR(50);

2.使用字符串拼接函数生成动态表名；

SET comment_report_table = CONCAT("youke_comment_report_",month);

3.使用预处理语句创建动态表

SET @commentReportTableSQL = CONCAT('CREATE TABLE IF NOT EXISTS ',comment_report_table," (`id` bigint(20) NOT NULL AUTO_INCREMENT,`owner_shop_id` bigint(20) DEFAULT '0',`target_type` int(10) DEFAULT '0' COMMENT '1:课程，2：资源',`target_id` bigint(20) DEFAULT '0' COMMENT '目标id',`owner_id` bigint(20) DEFAULT '0',`member_id` bigint(20) DEFAULT '0',`good_comment` tinyint(1) DEFAULT '0' COMMENT '是否好评',`comment_score` bigint(20) DEFAULT '0' COMMENT '评分',`create_date` datetime DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;");

PREPARE temp FROM @commentReportTableSQL;
EXECUTE temp;

5.游标、循环以及判断的整合使用

存储过程中有三种循环方式，WHILE、REPEAT和LOOP

WHILE循环的语法如下：

WHILE expression DO

Statements

END WHILE

WHILE循环在每次迭代开始时检查表达式。如果expression为TRUE，MySQL将执行WHILE和END WHILE之间的语句，直到expression为FALSE。 WHILE循环称为预先测试条件循环，因为它总是在执行前检查语句的表达式。

REPEAT循环的语法如下：

REPEAT

statements;

UNTIL expression

END REPEAT

首先，MySQL执行语句，然后评估求值表达式(expression)。如果表达式(expression)的计算结果为FALSE，则MySQL将重复执行该语句，直到该表达式计算结果为TRUE。

因为REPEAT循环语句在执行语句后检查表达式(expression)，因此REPEAT循环语句也称为测试后循环。

LOOP，LEAVE和ITERATE语句

有两个语句允许您用于控制循环：

LEAVE语句用于立即退出循环，而无需等待检查条件。LEAVE语句的工作原理就类似PHP，C/C++，Java等其他语言的break语句一样。

ITERATE语句允许您跳过剩下的整个代码并开始新的迭代。ITERATE语句类似于PHP，C/C++，Java等中的continue语句。

MySQL还有一个LOOP语句，它可以反复执行一个代码块，另外还有一个使用循环标签的灵活性。

以下是使用LOOP循环语句的示例。

CREATE PROCEDURE test_mysql_loop()
 BEGIN
 DECLARE x  INT;
        DECLARE str  VARCHAR(255);

 SET x = 1;
        SET str =  '';

 loop_label:  LOOP
 IF  x > 10 THEN 
 LEAVE  loop_label;
 END  IF;

 SET  x = x + 1;
 IF (x mod 2) THEN
     ITERATE  loop_label;
 ELSE
    SET  str = CONCAT(str,x,',');
 END IF;
    END LOOP;    
    SELECT str;
END;

游标的语法如下：
DROP PROCEDURE IF EXISTS text5;
CREATE PROCEDURE text5()
  BEGIN
    DECLARE d BOOLEAN DEFAULT FALSE;
    DECLARE n INT;
    DECLARE cursor_names CURSOR FOR (SELECT BUY_COUNT
                                    FROM tb_ranking); -- 定义游标，并给游标赋值(集合)
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET d = TRUE; -- 定义异常捕捉，当游标也没有可取的值时，done会进行捕捉，然后我们依次跳出循环
    OPEN cursor_names; -- 打开游标
    REPEAT
      FETCH cursor_names INTO n; -- 取一条数据
      # 省略
    UNTIL !d END REPEAT;
    CLOSE cursor_names; -- 关闭游标
  END;

但是如果游标查询的是一个动态表名，就必须先创建一个视图，再用视图来定义游标才能实现游标的遍历功能。下面我们就用一段具体的代码来说明一下。

--##### 定义课程访问量存储过程 #####
delimiter $$
DROP PROCEDURE IF EXISTS statisCourseVisitCount;
CREATE PROCEDURE statisCourseVisitCount(IN day varchar(32),IN month varchar(32))
	BEGIN
		DECLARE d BOOLEAN DEFAULT FALSE;
		DECLARE course_id_value BIGINT(20) DEFAULT 0;
		DECLARE t_count_value BIGINT(20) DEFAULT 0;
		DECLARE search_table varchar(200);
		DECLARE param varchar(200);
		DECLARE existsFlat int default 0;
		DECLARE ownerMemberId BIGINT(20) DEFAULT 0;
		DECLARE tmpShopId BIGINT(20) DEFAULT 0;
		DECLARE tmpCategoryId BIGINT(20) DEFAULT 0;
		DECLARE tmpCategoryPath VARCHAR(100) DEFAULT '';
		DECLARE tmpSubjectId BIGINT(20) DEFAULT 0;
		DECLARE tmpCourseType INT(10) DEFAULT 0;
		DECLARE tmpClassId BIGINT(20) DEFAULT 0;
		DECLARE tmpGradeId BIGINT(20) DEFAULT 0;
		DECLARE tmpGradePath varchar(100) DEFAULT '';
		DECLARE paperCount INT(10) DEFAULT 0;

		##利用视图定义游标
		DECLARE visit_count_cursor CURSOR FOR SELECT * FROM visit_count_view;
		##遍历停止判断
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET d = TRUE;
		##拼接动态表名
		SET search_table = CONCAT('youke_course_visit_log_',month);
		SET param = CONCAT("'",day,"'");
		##创建视图的SQL语句
		SET @cursor_sql = CONCAT('CREATE VIEW visit_count_view as select course_id,count(id) as t_count from ',search_table,' where DATE(create_date) = ',param,' group by course_id');
		select @cursor_sql;
		##预处理视图的SQL语句
		PREPARE stmt from @cursor_sql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;

		##开启游标
		OPEN visit_count_cursor;
		##遍历游标
		read_loop: LOOP
			##将遍历的结果赋值到变量中，注意顺序要一一对应
			FETCH visit_count_cursor INTO course_id_value,t_count_value;
			IF d THEN
					##没有更多数据跳出循环
					LEAVE read_loop;
			ELSE
					SET existsFlat = 0;
					select count(id) into existsFlat from youke_statis_course_summary where course_id = course_id_value LIMIT 1;
					IF existsFlat > 0 THEN
						UPDATE youke_statis_course_summary set visit_count = visit_count + t_count_value where course_id = course_id_value;
					ELSE 
						SET paperCount = 0;
						select OwerID,ShopId,CategoryId,CategoryPath,CourseType into ownerMemberId,tmpShopId,tmpCategoryId,tmpCategoryPath,tmpCourseType from himall_products where Id = course_id_value;
						select Id,CategoryId,CategoryPath into tmpClassId,tmpGradeId,tmpGradePath from himall_grades where MemberId = ownerMemberId;
						IF tmpCourseType = 1 THEN
							select count(Id) into paperCount from himall_paperlist where ProductId = course_id_value and `Delete` = 0;
						END IF;
						INSERT INTO youke_statis_course_summary(course_id,owner_member_id,shop_id,category_id,category_path,subject_id,course_type,class_id,grade_id,grade_path,visit_count,paper_count) 
						values(course_id_value,ownerMemberId,tmpShopId,tmpCategoryId,tmpCategoryPath,statisSplit(tmpCategoryPath,'|',2),tmpCourseType,tmpClassId,tmpGradeId,tmpGradePath,t_count_value,paperCount);
					END IF;
				
			END IF;
##关闭变量
		END LOOP;
##关闭游标
		CLOSE visit_count_cursor;
##删除视图
		DROP VIEW visit_count_view;
	END;
 $$

现场喷口水

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL存储过程实战二：语法讲解。

先展示一段完整的存储过程代码作为例子方便进行语法说明。代码如下：--##########定义课程分享量存储过程######################delimiter $$DROP PROCEDURE IF EXISTS statisCourseShareCount;CREATE PROCEDURE statisCourseShareCount(IN day varchar(32...
复制链接

扫一扫