MySQL代码 - 自定义函数、存储过程

自定义函数Function

1.1 传入有分隔符的数字字符串，计算结果

CREATE DEFINER=`skip-grants user`@`skip-grants host` FUNCTION `sumSplit`(`nums` varchar(100),`delim` char(1)) RETURNS int(11)
BEGIN
	
	DECLARE total int DEFAULT 0;
	select sum(num) into total from (	
			select SUBSTRING_INDEX(SUBSTRING_INDEX(nums,',',help_topic_id+1),delim,-1) as num
				FROM mysql.help_topic
				where help_topic_id < LENGTH(nums) - LENGTH(REPLACE(nums,delim,'')) + 1
	) tables;

	RETURN total;
END

 
 

1.2 传入ID( 用来查表 ),数量计算总价

CREATE DEFINER=`skip-grants user`@`skip-grants host` FUNCTION `sumOrderTotalPrice`(`productIds` varchar(100),`productNums` varchar(100),`delim` varchar(1)) RETURNS int(11)
BEGIN
	
	-- 声明变量
	DECLARE price INT DEFAULT 0;
	DECLARE num INT ;
	DECLARE totalPriece INT DEFAULT 0;
	DECLARE endTag INT DEFAULT 0;
	
	
	-- 获取产品价格
	DECLARE cur_currentPrices CURSOR FOR select current_price 
									from pms_product 
									where id in (
											select SUBSTRING_INDEX(SUBSTRING_INDEX(productIds,delim,help_topic_id+1),delim,-1) as num
											FROM mysql.help_topic
											where help_topic_id < LENGTH(productIds) - LENGTH(REPLACE(productIds,delim,'')) + 1
									);
	
	
	-- 获取产品数量
	DECLARE cur_nums CURSOR FOR 
			select SUBSTRING_INDEX(SUBSTRING_INDEX(productNums,delim,help_topic_id+1),delim,-1) as num
						FROM mysql.help_topic
						where help_topic_id < LENGTH(productNums) - LENGTH(REPLACE(productNums,delim,'')) + 1;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET endTag = 1;
	
	-- 打开游标
	OPEN cur_currentPrices;
	OPEN cur_nums;
	
	-- 循环游标进行计算
	WHILE endTag != 1 DO
		FETCH cur_currentPrices into price;
		
		IF endTag = 1 THEN
			CLOSE cur_currentPrices;
			CLOSE cur_nums;
			RETURN totalPriece;
		END IF;
		
		FETCH cur_nums into num;
		SET totalPriece = totalPriece + price * num;
	END WHILE;
	
		
	RETURN totalPriece;
END

 

2 存储过程procedure

2.1 批量插入数据行 - 随机字符串

CREATE DEFINER=`root`@`%` PROCEDURE `
batchData`(IN `num` int)
BEGIN

	declare myName varchar(25) default '';
	declare myNickname varchar(25) default '';
	declare myYear int default 0;
	declare myIndex int default 0;
	
	while myIndex < num  do 
		set myName = substring(MD5(RAND()),1,10);
		set myNickname = substring(MD5(RAND()),1,10);
		set myYear = ROUND(rand()*100+1);
		insert into member(year,name,nickname) values(myYear,myName,myNickname);
		set myIndex = myIndex+1;
		
	end while;

END

 
 

2.2 从已有表数据，进行重复随机插入

CREATE DEFINER=`root`@`%` PROCEDURE `batchInsertData`(IN `num` int)
BEGIN
	declare myId int default 1;
	
	declare curCount int default 0;
	declare curNum int default 0;
	
	select count(*) into curCount from member;
	
	while curNum < num do
		set myId = rand() * curCount + 1;
		
		insert into member(year,name,nickname,class_id,food_id, hobby)
			select year,name,nickname,class_id,food_id, hobby from member where id = myId;
		
		set curNum = curNum + 1;
	end while;

END

 
 

2.3 从已有的外表数据，进行顺序插入外键ID值

CREATE DEFINER=`root`@`%` PROCEDURE `updateData`()
BEGIN
	
	declare endIndex int default 1;
	declare myIndex int default 1;
	declare classNum int default 1;
	declare foodNum int default 1;
	declare classId int default 1;
	declare foodId int default 1;
	
	select id into myIndex from member order by id asc limit 1;
	select id into endIndex from member order by id desc limit 1;
	select count(*) into classNum from class;
	select count(*) into foodNum from food;
	

	
	while myIndex <= endIndex do
		/*if classId = classNum then
			set classId = 1;
		end if;*/
		
		if foodId = foodNum+1 then
			set foodId = 1;
		end if;
		
		update member set food_id = foodId where id = myIndex;
		# update member set class_id = classId where id = myIndex;
		# set classId = classId + 1;
		set foodId = foodId + 1;
		set myIndex = myIndex + 1;
	end while;

END