mysql 存储过程详解 临时表 加案例

前言

在项目开发中，经常会遇到这样一种场景，当修改A表的一条数据时，需要关联修改B表、C表甚至其他更多表的数据，为什么会这样呢？

在真实的业务场景中，往往一张表的数据关联的业务是多样的，举例来说，用户在页面上完成一个订单，对服务端来说，与这个订单相关的业务还有很多，比如生成一条出库记录，扣减库存，用户的账户余额扣减，商家的账户余额增加，财务结算等等…

所有这些不同的业务员操作，在服务端来说，最终体现的就是与数据库的交互，可想而知，假如关联的业务越复杂，需要与数据库进行IO的次数就越多；

那么有没有什么办法，在面对此类场景业务时，提升服务端的性能，减少数据库IO呢？这就是本篇将要分享的存储过程。

什么是存储过程

存储过程就是事先经过编译并存储在数据库中的一段 SQL 语句的集合；

为什么使用存储过程

调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

存储过程特点：

• 封装，复用， 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可；
• 可以接收参数，也可以返回数据， 在存储过程中，可以传递参数，也可以接收返回值；
• 减少网络交互，提升效率，如果一次操作涉及到多条SQL，每执行一次都是一次网络传
  输。 如果将这些sql操作封装在存储过程中，只需网络交互一次可能就可以了；

存储过程基础语法

-- 创建存储过程语法
CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
	-- SQL
END;

-- IN    该类参数作为输入，也就是需要调用时传入值
-- OUT   该类参数作为输出，也就是该参数可以作为返回值
-- INOUT 既可以作为输入参数，也可以作为输出参数

-- 调用存储过程
CALL 名称 ([ 参数 ]);


-- 查看存储过程
SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义


-- 删除存储过程
DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ;

注意点：上面创建存储过程的语句在navicat或者sqlyog中没问题，但是放到命令行中执行会报错，在命令行中模式下，需要通过关键字 delimiter 指定SQL语句的结束符

存储过程中的变量

在MySQL中，变量一般可分为分为三种类型: 系统变量、用户定义变量、局部变量；

系统变量

系统变量是MySQL服务器系统自身提供的，分为全局变量（GLOBAL）、会话
变量（SESSION）；

-- 查看系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方 式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值
-- 例
SHOW VARIABLES LIKE '%query_cache%';

-- 设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL] 系统变量名 = 值 ;

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。

注意点：

• mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置；
• 全局变量(GLOBAL): 全局变量针对于所有的会话；
• 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了；

用户自定义变量

用户定义变量，是用户根据需要自己定义的变量，用户自定义变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以。其作用域为当前session的连接。

-- 自定义变量 赋值时，可以使用 = ，也可以使用 := 。
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;


-- 使用select into 的方式给变量赋值
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;

注意: 用户定义变量无需对其进行声明或初始化，只不过获取到的值为NULL而已；

局部变量

局部变量定义

根据需要定义的在局部生效的变量，访问前，需通过DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块。

定义方式

DECLARE 变量名 变量类型 [DEFAULT ... ] ;

变量类型就是数据库字段类型，可选值包括：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

例

CREATE PROCEDURE p2 ( ) 

BEGIN

DECLARE my_count INT DEFAULT 0;
SELECT count( * ) INTO my_count FROM a;
SELECT my_count;

END;

CALL p2();

条件判断语句if的使用

if 条件语句语法结构

IF 条件1 THEN
	.....
ELSEIF 条件2 THEN -- 可选
	.....
ELSE -- 可选
	.....
END IF;

在if条件判断结构中，ELSE IF 结构可以有多个，也可以没有。 ELSE结构可以有，也可以没有。

case - when 语法

CASE case_value
	WHEN when_value1 THEN statement_list1
	
	[ WHEN when_value2 THEN statement_list2] ...
	
	[ ELSE statement_list ]
	
END CASE;

存储过程输入输出参数使用

存储过程中使用到的参数的类型，主要分为以下三种：IN、OUT、INOUT；

类型	含义	备注说明
IN	该类参数作为输入，也就是需要调用时传入值	默认
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

语法定义

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])

BEGIN
	-- SQL
END;

将上面的案例进行简单改造，通过外面传入的参数，判断分数在不同的区间，从而返回不同的结果；

create procedure p4(in score int,out result varchar(12))

BEGIN

	if score >= 85 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';

	end if;

END;

call p4(90,@result);

select @result;

存储过程中的循环使用

循环语句在编程中经常被用到，常用于对批量的数据进行循环处理，在mysql的存储过程中，也提供了几种常用的循环语句，包括：while循环，repeat循环，和loop循环；

while循环语句

while 循环是有条件的循环控制语句。满足条件时，再执行循环体中的SQL语句；

WHILE 条件 DO

	SQL逻辑...
	
END WHILE;

先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑

需求：从1累加到N，并求出累加的结果

create procedure p6(in n int)

	begin

		declare total int default 0;

		while n>0 do
			set total := total + n;
			set n := n - 1 ;

		end while;

		select total;

	end ;

repeat 循环语句

repeat是有条件的循环控制语句, 当满足 until 声明的条件的时候，则退出循环,语法结构为：

REPEAT

	SQL逻辑...
	UNTIL 条件
	END REPEAT;

先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环；

create procedure p7(in n int)

	begin

		declare total int default 0;

		repeat 
			set total := total + n;
			set n := n - 1;

		until n <= 0
			
		end repeat;

		select total;

	end ;

loop 循环语句

LOOP 可以实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用：

• LEAVE ：配合循环使用，退出循环；
• ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环；

[begin_label:] LOOP

	SQL逻辑...
	
END LOOP [end_label];

说明：

• LEAVE label; – 退出指定标记的循环体
• ITERATE label; – 直接进入下一次循环

上述语法中的 begin_label，end_label，label 指的是我们自定义的标记名称；

使用loop语法完成上面的从1到N的累加求和

create procedure p8(in n int)

	begin

		declare total int default 0;

		
		sum:loop
			if n<=0 then 
					leave sum;

			end if;

			set total := total + n;
			set n := n - 1;

		
		end loop sum;

		select total;

	end ;

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])

RETURNS type [characteristic ...]

	BEGIN
		-- SQL语句
		RETURN ...;
	END ;	

characteristic说明：

• DETERMINISTIC：相同的输入参数总是产生相同的结果；
• NO SQL ：不包含 SQL 语句；
• READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句；

存储函数案例1：求1到N的累加

create function fun_add(n int)

returns int deterministic

BEGIN

		declare sum int default 0;
		while n > 0 do
				set sum := sum + n;
				set n := n - 1;
		end while;

		return sum;
END;

存储过程中游标的使用

游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理；

游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过
程开发的能力；

在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标 充当了 指针的作用 ，我们可以通过操作游标来对数据行进行操作。

游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE

-- 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句 ;

-- 打开游标
OPEN 游标名称 ;

-- 获取游标记录
FETCH 游标名称 INTO 变量 [, 变量 ] ;

-- 关闭游标
CLOSE 游标名称 ;

案例需求，有下面一张员工表，创建一个存储过程，声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count；

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)

BEGIN
	DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
	DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
	DECLARE emp_count INT DEFAULT 0; #记录循环个数

	#定义游标 
	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;

	#打开游标 
	OPEN emp_cursor;

	REPEAT

		#使用游标（从游标中获取数据）
		FETCH emp_cursor INTO cursor_salary;

		SET sum_salary = sum_salary + cursor_salary;
		SET emp_count = emp_count + 1;

		UNTIL sum_salary >= limit_total_salary

	END REPEAT;

	SET total_count = emp_count;
	
	#关闭游标

	CLOSE emp_cursor;

END ;

案例需求根据传入的参数salary，来查询用户表employees中，所有工资大于等于15000的员工ID，以及first_name，并将员工的ID和first_name插入到一张新表；

create procedure p_emp(in in_salary int)

begin

	declare u_empid int(12);
	declare u_fname varchar(100);
	declare u_cursor cursor for select employee_id,first_name from employees where salary >= in_salary;

	-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
	declare exit handler for SQLSTATE '02000' close u_cursor;

	drop table if exists tb_em_pro;

	create table if not exists tb_em_pro( 
			id int primary key auto_increment, 
			employee_id int(12), 
			first_name varchar(20) 
	);

	-- 打开游标
	open u_cursor;
	
	while true do
			fetch u_cursor into u_empid,u_fname;
			insert into tb_em_pro values (null, u_empid, u_fname);
	end while;

	-- 关闭游标
	close u_cursor;

end;

案例

两个表 一个单据表 一个物流码表 一对多关系 根据单据表数据关联物流码表生成新的关系表

-- 单据表
CREATE TABLE `u_miduo_code_new` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(64) DEFAULT NULL COMMENT '订单号',
  `sale_no` varchar(32) DEFAULT NULL COMMENT '出货单号',
  `big_label` varchar(32) DEFAULT NULL COMMENT '物流码',
  `label_type` varchar(32) DEFAULT NULL COMMENT '标签类型',
  `distributor_code` varchar(32) DEFAULT NULL COMMENT '收货人编号',
  `distributor_name` varchar(32) DEFAULT NULL COMMENT '收货人名称',
  `batch` varchar(64) DEFAULT NULL COMMENT '生产批次号',
  `product_code` varchar(64) DEFAULT NULL COMMENT '商品编号',
  `product_name` varchar(64) DEFAULT NULL COMMENT '商品名称',
  `product_norm` varchar(64) DEFAULT NULL COMMENT '商品规格',
  `sale_num` varchar(64) DEFAULT NULL COMMENT '出货数量',
  `sale_time` varchar(64) DEFAULT NULL COMMENT '出货时间',
  `sale_warehouse` varchar(64) DEFAULT NULL COMMENT '出货仓库',
  `operator` varchar(64) DEFAULT NULL COMMENT '操作人',
  `type` varchar(16) DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `u_miduo_code_new_big_label_index` (`big_label`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9463883 DEFAULT CHARSET=utf8mb4 COMMENT='单据表';

-- 物流码表
CREATE TABLE `u_miduo_code_relation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `pid` bigint(20) DEFAULT NULL COMMENT 'u_miduo_code_new ID',
  `order_source` tinyint(2) DEFAULT NULL COMMENT '订单类型 0 销售订单 1 出库单',
  `order_source_name` varchar(10) DEFAULT NULL COMMENT '订单类型名称',
  `order_direct` tinyint(2) DEFAULT NULL COMMENT '订单流向:0-入库，1-出库',
  `order_direct_name` varchar(10) DEFAULT NULL COMMENT '订单流向名称',
  `order_no` varchar(64) DEFAULT NULL COMMENT '单据号',
  `code` varchar(32) DEFAULT NULL COMMENT '物流码',
  `tag_type` tinyint(2) DEFAULT NULL COMMENT '标签类型；0大标1中标2小标3垛标',
  `biggerserialnumber` varchar(32) DEFAULT NULL COMMENT '垛标',
  `bigserialnumber` varchar(32) DEFAULT NULL COMMENT '大标',
  `mediumserialnumber` varchar(32) DEFAULT NULL COMMENT '中标',
  `smallserialnumber` varchar(32) DEFAULT NULL COMMENT '小标',
  `distributor_code` varchar(32) DEFAULT NULL COMMENT '收货人编号',
  `distributor_name` varchar(32) DEFAULT NULL COMMENT '收货人名称',
  `batch` varchar(64) DEFAULT NULL COMMENT '生产批次号',
  `prod_id` varchar(30) DEFAULT NULL COMMENT '商品编号',
  `prod_name` varchar(150) DEFAULT NULL COMMENT '商品名称',
  `specification` varchar(50) DEFAULT NULL COMMENT '规格型号',
  `product_code` varchar(64) DEFAULT NULL COMMENT '旧商品编号',
  `product_name` varchar(64) DEFAULT NULL COMMENT '旧商品名称',
  `product_norm` varchar(64) DEFAULT NULL COMMENT '旧商品规格',
  `sale_num` varchar(64) DEFAULT NULL COMMENT '出货数量',
  `sale_time` varchar(64) DEFAULT NULL COMMENT '出货时间',
  `sale_warehouse` varchar(64) DEFAULT NULL COMMENT '出货仓库',
  `operator` varchar(64) DEFAULT NULL COMMENT '操作人',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_pid` (`pid`),
  KEY `bigserialnumber` (`bigserialnumber`),
  KEY `smallserialnumber` (`smallserialnumber`)
) ENGINE=InnoDB AUTO_INCREMENT=27711525 DEFAULT CHARSET=utf8mb4 COMMENT='物流码表';

-- 目标表结构
CREATE TABLE `u_miduo_code_relation_new` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `pid` bigint(20) DEFAULT NULL COMMENT 'u_miduo_code_new ID',
  `order_source` tinyint(2) DEFAULT NULL COMMENT '订单类型 0 销售订单 1 出库单',
  `order_source_name` varchar(10) DEFAULT NULL COMMENT '订单类型名称',
  `order_direct` tinyint(2) DEFAULT NULL COMMENT '订单流向:0-入库，1-出库',
  `order_direct_name` varchar(10) DEFAULT NULL COMMENT '订单流向名称',
  `order_no` varchar(64) DEFAULT NULL COMMENT '单据号',
  `code` varchar(32) DEFAULT NULL COMMENT '物流码',
  `tag_type` tinyint(2) DEFAULT NULL COMMENT '标签类型；0大标1中标2小标3垛标',
  `biggerserialnumber` varchar(32) DEFAULT NULL COMMENT '垛标',
  `bigserialnumber` varchar(32) DEFAULT NULL COMMENT '大标',
  `mediumserialnumber` varchar(32) DEFAULT NULL COMMENT '中标',
  `smallserialnumber` varchar(32) DEFAULT NULL COMMENT '小标',
  `distributor_code` varchar(32) DEFAULT NULL COMMENT '收货人编号',
  `distributor_name` varchar(32) DEFAULT NULL COMMENT '收货人名称',
  `batch` varchar(64) DEFAULT NULL COMMENT '生产批次号',
  `prod_id` varchar(30) DEFAULT NULL COMMENT '商品编号',
  `prod_name` varchar(150) DEFAULT NULL COMMENT '商品名称',
  `specification` varchar(50) DEFAULT NULL COMMENT '规格型号',
  `product_code` varchar(64) DEFAULT NULL COMMENT '旧商品编号',
  `product_name` varchar(64) DEFAULT NULL COMMENT '旧商品名称',
  `product_norm` varchar(64) DEFAULT NULL COMMENT '旧商品规格',
  `sale_num` varchar(64) DEFAULT NULL COMMENT '出货数量',
  `sale_time` varchar(64) DEFAULT NULL COMMENT '出货时间',
  `sale_warehouse` varchar(64) DEFAULT NULL COMMENT '出货仓库',
  `operator` varchar(64) DEFAULT NULL COMMENT '操作人',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_pid` (`pid`),
  KEY `bigserialnumber` (`bigserialnumber`),
  KEY `smallserialnumber` (`smallserialnumber`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='物流码关系表';

存储过程

-- 如果存在就删除move_code
DROP PROCEDURE IF EXISTS move_code;
CREATE DEFINER = `root` @`%` PROCEDURE move_code(INOUT in_id int, IN step int,  IN max_id int)  BEGIN

-- 设置变量
DECLARE newsql TEXT;
DECLARE tempdata TEXT;
DECLARE vpid INT ( 11 );
DECLARE vorder_no VARCHAR ( 64 );
DECLARE vsale_no VARCHAR ( 32 );
DECLARE vbig_label VARCHAR ( 32 );
DECLARE vlabel_type VARCHAR ( 32 );
DECLARE vdistributor_code VARCHAR ( 32 );
DECLARE vdistributor_name VARCHAR ( 32 );
DECLARE vbatch VARCHAR ( 64 );
DECLARE vproduct_code VARCHAR ( 64 );
DECLARE vproduct_name VARCHAR ( 64 );
DECLARE vproduct_norm VARCHAR ( 64 );
DECLARE vsale_num VARCHAR ( 64 );
DECLARE vsale_time VARCHAR ( 64 );
DECLARE vsale_warehouse VARCHAR ( 64 );
DECLARE voperator VARCHAR ( 64 );
DECLARE vtype VARCHAR ( 16 );
DECLARE vorder_source tinyint(2);
DECLARE vorder_source_name varchar(10);
DECLARE vorder_direct tinyint(2);
DECLARE vorder_direct_name varchar(10);

set @exedata = "";

-- 基础sql
SET newsql = 
	"INSERT INTO `u_miduo_code_relation_new`(`pid`,`order_source`,`order_source_name`,
	`order_direct`,
	`order_direct_name`,
	`order_no`,
	`code`,
	`tag_type`,
	`biggerserialnumber`,
	`bigserialnumber`,
	`mediumserialnumber`,
	`smallserialnumber`,
	`distributor_code`,
	`distributor_name`,
	`batch`,
	`prod_id`,
	`prod_name`,
	`specification`,
	`product_code`,
	`product_name`,
	`product_norm`, 
	`sale_num`,
	`sale_time`,
	`sale_warehouse`,
	`operator`
	) VALUES ";
	
	-- 循环执行
	WHILE in_id <= max_id DO
		-- 批量赋值
		SELECT
			`id`,
			`order_no`,
			`sale_no`,
			`big_label`,
			`label_type`,
			`distributor_code`,
			`distributor_name`,
			`batch`,
			`product_code`,
			`product_name`,
			`product_norm`,
			`sale_num`,
			`sale_time`,
			`sale_warehouse`,
			`operator`,
			`type`
			INTO 
			vpid,
			vorder_no,
			vsale_no,
			vbig_label,
			vlabel_type,
			vdistributor_code,
			vdistributor_name,
			vbatch,
			vproduct_code,
			vproduct_name,
			vproduct_norm,
			vsale_num,
			vsale_time,
			vsale_warehouse,
			voperator,
			vtype
		FROM
			u_miduo_code_new 
		WHERE
			id = in_id;
			
			-- 业务处理
			CASE 
			WHEN vorder_no IS NOT NULL THEN
					set vorder_source = 0;
					set vorder_source_name='销售订单';
					set vorder_no = vorder_no;
			WHEN vsale_no IS NOT NULL THEN
					set vorder_source = 1;
					set vorder_source_name='出库单';
					set vorder_no = vsale_no;
				END CASE;
		if vtype = '出库' then 
			set vorder_direct = 1;
			set vorder_direct_name = '出库';
		elseif vtype = '入库' then
			set vorder_direct = 0;
			set vorder_direct_name = '入库';
		end if;
			
		-- 组装数据
		SELECT
			GROUP_CONCAT(
			"(",
			vpid,
			",",
			vorder_source,
			",'",
			vorder_source_name,
			"',",
			vorder_direct,
			",'",
			vorder_direct_name,
			"','",
			vorder_no,
			"','",
			ifnull(ifnull(`smallserialnumber`, `bigserialnumber`), vbig_label),
			"','",
			ifnull( `tag_type`, '' ),
			"','",
			ifnull( `biggerserialnumber`, '' ),
			"','",
			ifnull( `bigserialnumber`, '' ),
			"','",
			ifnull( `mediumserialnumber`, '' ),
			"','",
			ifnull( `smallserialnumber`, '' ),
			"','",
			ifnull( vdistributor_code, '' ),
			"','",
			ifnull( vdistributor_name, '' ),
			"','",
			ifnull( vbatch, '' ),
			"','",
			ifnull( `prod_id`, '' ),
			"','",
			ifnull( `prod_name`, '' ),
			"','",
			ifnull( `specification`, '' ),
			"','",
			ifnull( vproduct_code, '' ),
			"','",
			ifnull( vproduct_name, '' ),
			"','",
			ifnull( vproduct_norm, '' ),
			"','",
			ifnull( `sale_num`, '' ),
			"','",
			ifnull( vsale_time, '' ),
			"','",
			ifnull( vsale_warehouse, '' ),
			"','",
			ifnull( voperator, '' ),
			"')" 
			) INTO tempdata
		FROM
			u_miduo_code_relation 
		WHERE
			bigserialnumber = vbig_label;
			


		-- 处理没有查到返回null
		CASE 
		WHEN tempdata IS NOT NULL THEN
			set @exedata = concat(@exedata, ',', tempdata);
		ELSE 
			set @exedata = @exedata;
		END CASE;		

		-- 调试用
-- 		select tempdata;
		
		-- 删除表中已存在的
		DELETE from u_miduo_code_relation_new where pid = vpid;

		if in_id%step = 0 and length(@exedata) > 0
    then 
		-- @表示全局变量
		-- 拼接赋值  必须要用全局变量不然语句会报错
		-- 存储sql内容的变量必须是全局变量，使用 @ 符修饰
        set @exedata = SUBSTRING(@exedata, 2);
        set @exesql = concat(newsql, @exedata);

        prepare stmt from @exesql;
        execute stmt;
        DEALLOCATE prepare stmt;
				
        set @exedata = "";
    end if;


		SET in_id = in_id + 1;

	END WHILE;

-- 补偿未提交的
if length(@exedata) > 0 
then 
    set @exedata = SUBSTRING(@exedata, 2);
    set @exesql = concat(newsql, @exedata);
						
    prepare stmt from @exesql;
    execute stmt;
    DEALLOCATE prepare stmt;
end if;

END;

• 调用

set @id =1;
CALL move_code (@id, 1000,100001);
select @id;

• 游标案例

drop PROCEDURE if exists updatecode;
CREATE DEFINER=`root`@`%` PROCEDURE `updatecode`()
BEGIN
    declare vpid int;
    declare flag int default 0;
    --  定义一个游标来记录sql查询的结果
    declare s_list cursor for select pid from u_miduo_temp;
    --  循环结束标识
    declare continue handler for not found set flag = 1;
    --  打开游标
    open s_list;
    --  将游标中的值赋给定义好的变量
    fetch s_list into vpid;
    while flag <> 1 do
				set @id = vpid;
				call move_code(@id, 1, vpid);
				delete from u_miduo_temp where pid = vpid;
				select vpid;
			  --  游标往后移
        fetch s_list into vpid;
    end while;
    --  关闭游标
    close s_list;
end

• 调用

call updatecode();

存储函数

CREATE DEFINER=`root`@`localhost` FUNCTION `query_child`(`code` varchar(1000)) RETURNS TEXT CHARSET utf8
BEGIN
DECLARE resTemp TEXT;
DECLARE sTempChd TEXT;
 
SET resTemp='$';
SET sTempChd = `code`;
 
WHILE sTempChd IS NOT NULL DO
 
SET resTemp = CONCAT(resTemp,',',sTempChd);
SELECT GROUP_CONCAT(`code`) INTO sTempChd FROM tour_distributor_new WHERE FIND_IN_SET(parent_code,sTempChd)>0;
 
END WHILE;
RETURN resTemp;
end

--  查询父级下所有含父级code
select query_child('P003');

临时表

	DROP TABLE IF EXISTS tempTableNames;
	CREATE TEMPORARY TABLE tempTableNames ( tableName VARCHAR ( 100 ) not null, type tinyint(1) not null );
	INSERT INTO tempTableNames
	VALUES
		( 'act_hi_taskinst', 1 ),
		( 'act_hi_varinst' , 1 ),
		( 'act_hi_actinst' , 1 ),
		( 'act_hi_comment' , 1 ),
		( 'act_hi_detail' , 1 ),
		( 'act_hi_identitylink' , 1 ),
		( 'act_ru_task' , 1 ),
		( 'act_ru_variable' , 1 ),
		( 'act_ru_identitylink' , 1 ),
		( 'act_ru_execution' , 1 ),
		( 'act_hi_procinst' , 2 );
	
	select tableName from tempTableNames;

mysql 临时表能不能作为游标 不能 网上答案

游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用fetch语句可以移动该指针，从而对游标中的数据进行各种操作，然后将操作结果写回数据表中。