mysql 进阶&&存储过程

一、需求描述

基于datum_copy3_copy1表中数据，对于每一行中的各列数据，其中还有数据是已；分隔的形式。需要对应到tm_datum_detail表中的多列；
反推出tm_datum_attr_item、tm_datum_attr 的id值；
分别将信息存于tm_datum_info、tm_datum_detail表中。

二、存储过程写法

具体过车如下sql。

CREATE PROCEDURE `new_procedure` ()
BEGIN
-- 声明变量，所有的声明必须在非声明的语句前面
	DECLARE copyId INT;
	DECLARE rongliang VARCHAR(255);
	DECLARE yongliang VARCHAR(255);
	DECLARE dengji VARCHAR(255);
	DECLARE shiyongniandu VARCHAR(255) DEFAULT '';
	DECLARE zuiyouniandu VARCHAR(255);
	DECLARE oilType VARCHAR(255);
	DECLARE ll VARCHAR(255);
	DECLARE singelMid INT;
	DECLARE countMids VARCHAR(10000);
	DECLARE datumName VARCHAR(255);
	DECLARE fetchSeqOk boolean DEFAULT false;
	DECLARE orgCur CURSOR FOR
		-- SQL 循环对象
		SELECT id, rong_liang, yong_liang, `level`, adapt_niandu
			, perrfect_niandu, oil_type, fa_dong_ji AS ll, mid AS singelMid, mids AS countMids
			, datum_name AS datumName
		FROM datum_copy3_copy1 a;
	DECLARE CONTINUE HANDLER FOR NOT FOUND
		SET fetchSeqOk = true;
	OPEN orgCur;
	fetchSeqLoop: LOOP 
		FETCH orgCur INTO copyId, rongliang, yongliang, dengji, shiyongniandu, zuiyouniandu, oilType, ll, singelMid, countMids, datumName;
		IF fetchSeqOk THEN
			LEAVE fetchSeqLoop
		ELSE 
			INSERT INTO tm_datum_info (datum_name, category_id)
			VALUES (datumName, 10000016);
			INSERT INTO tm_datum_detail (datum_id, attr_id, attr_value_id, attr_value)
			SELECT info.id, attr.id, 0, rongliang
			FROM tm_datum_attr attr, tm_datum_info info
			WHERE attr_name = '容量'
				AND info.id = (
					SELECT MAX(id)
					FROM tm_datum_info
				);
			INSERT INTO tm_datum_detail (datum_id, attr_id, attr_value_id, attr_value)
			SELECT info.id, attr.id, 0, yongliang
			FROM tm_datum_attr attr, tm_datum_info info
			WHERE attr_name = '用量'
				AND info.id = (
					SELECT MAX(id)
					FROM tm_datum_info
				);
			INSERT INTO tm_datum_detail (datum_id, attr_id, attr_value_id, attr_value)
			SELECT info.id AS infoId, attr.id, it.id, ''
			FROM tm_datum_attr attr, tm_datum_attr_item it, tm_datum_info info
			WHERE (attr_name = '级别'
				AND info.id = (
					SELECT MAX(id)
					FROM tm_datum_info
				)
				AND it.item_value IN (
					SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(dengji, ';', help_topic_id + 1), ';', -1) AS num
					FROM mysql.help_topic
					WHERE help_topic_id < LENGTH(dengji) - LENGTH(REPLACE(dengji, ';', '')) + 1
				));
			INSERT INTO tm_datum_detail (datum_id, attr_id, attr_value_id, attr_value)
			SELECT info.id AS infoId, attr.id, it.id, ''
			FROM tm_datum_attr attr, tm_datum_attr_item it, tm_datum_info info
			WHERE (attr_name = '适用粘度'
				AND info.id = (
					SELECT MAX(id)
					FROM tm_datum_info
				)
				AND it.item_value IN (
					SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(shiyongniandu, ';', help_topic_id + 1), ';', -1) AS num
					FROM mysql.help_topic
					WHERE help_topic_id < LENGTH(shiyongniandu) - LENGTH(REPLACE(shiyongniandu, ';', '')) + 1
				));
			INSERT INTO tm_datum_detail (datum_id, attr_id, attr_value_id, attr_value)
			SELECT info.id, attr.id, 0, zuiyouniandu
			FROM tm_datum_attr attr, tm_datum_info info
			WHERE attr_name = '最优粘度'
				AND info.id = (
					SELECT MAX(id)
					FROM tm_datum_info
				);
			INSERT INTO tm_datum_detail (datum_id, attr_id, attr_value_id, attr_value)
			SELECT info.id, attr.id, 0, oilType
			FROM tm_datum_attr attr, tm_datum_info info
			WHERE attr_name = '基础油类型'
				AND info.id = (
					SELECT MAX(id)
					FROM tm_datum_info
				);
			INSERT INTO tm_datum_detail (datum_id, attr_id, attr_value_id, attr_value)
			SELECT info.id, attr.id, 0, oilType
			FROM tm_datum_attr attr, tm_datum_info info
			WHERE attr_name = '适配发动机'
				AND info.id = (
					SELECT MAX(id)
					FROM tm_datum_info
				);
			INSERT INTO tm_mid_datum_relation_detail (datum_id, mid, category_id)
			SELECT DISTINCT info.id, relate.mid, 10000016
			FROM (
				SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(countMids, ';', help_topic_id + 1), ';', -1) AS mid
				FROM mysql.help_topic
				WHERE help_topic_id < LENGTH(countMids) - LENGTH(REPLACE(countMids, ';', '')) + 1
			) relate, tm_datum_info info
			WHERE info.id = (
				SELECT MAX(id)
				FROM tm_datum_info
			);
		END IF;
	END LOOP fetchSeqLoop;
	CLOSE orgCur;
END

三、总结

存储过程写法:

-- 如果已存在就删除
DROP PROCEDURE if exists  `newProcedure`;
-- 创建名为newProcedure的存储过程，以begin开始，以end结束
CREATE  PROCEDURE `newProcedure`()
--存储过程执行权限设置 https://blog.csdn.net/qq_19865749/article/details/69662568
  --  SQL SECURITY INVOKER 
BEGIN
	-- 声明变量，所有的声明必须在非声明的语句前面
	DECLARE copyId INT;
	DECLARE rongliang VARCHAR(255);
    -- 
	DECLARE fetchSeqOk boolean default false;
  
	-- 声明游标 
	DECLARE orgCur CURSOR for
    -- SQL 循环对象 这里有个小坑
    -- 坑:有的字段名如果不起别名，拿不到值，先可以少点数据，
    -- 看那个值没有取到，试试别名，我这里别名是可以取到值得
	SELECT id as copyId,rongliang (查你需要的字段即可) FROM datum_copy3_copy1 a
  
  -- 声明游标的异常处理，设置一个终止标记
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOk = true;
  
  -- 打开游标
	OPEN orgCur;
	-- 开始循环
		fetchSeqLoop:LOOP
      -- 读取一行数据到变量,赋值
			FETCH orgCur into copyId,rongliang;
			-- 如果有异常退出，前面已经设置了如果有异常为true
			if fetchSeqOk
			then 			
				leave fetchSeqLoop;
			else  	
              -- 业务逻辑，do what you want to do
           -- 结束if
           end if; 
    -- 终止循环
	END LOOP;
-- 关闭游标
CLOSE orgCur;		
end

**注意:**游标内还可以定义游标，即双重游标。
第二个游标定义和赋值要在第一个游标内部哦！

四、传送门

至此，恭喜你，你基本学会存储过程的编写!
然而我要说的不仅是这个，
而是我的demo中业务逻辑部分设计到的sql知识点；
开启传送门模式

1、 mysql中的行转列问题
2、 mysql不同表且没有关联关系,分别在两张表中查出自己想要的字段
3、 insert into table() select () from table 批量查询
4、 group by 分组查询遇到的坑
5、 分组取最大的那个值
6、 GROUP_CONCAT 由于长度报错
7、 mysql 连表更新进阶