MYSQL 自定义函数，变量使用与游标

CREATE DEFINER=`root`@`%` FUNCTION `formatNum`(str VARCHAR(255)) RETURNS VARCHAR(255) CHARSET latin1 COLLATE latin1_bin
    NO SQL
    COMMENT '仅保留英文的数字'
BEGIN
	DECLARE result VARCHAR(255) DEFAULT '';
	DECLARE strTemp VARCHAR(255) DEFAULT '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE i INT DEFAULT 1;
	DECLARE pos INT DEFAULT 0;
	DECLARE strLen INT DEFAULT 0;
	SET strLen = LENGTH(str);
	each_loop:LOOP
		IF i>strLen THEN
			LEAVE each_loop;
		END IF;
		SET pos = LOCATE(MID(str,i,1),strTemp);
		IF pos>0 THEN
			SET result = CONCAT(result,MID(str,i,1));
		END IF;
		SET i = i + 1;
	END LOOP;
	RETURN result;
    END$$

DELIMITER ;

DELIMITER $$

USE `money`$$

DROP FUNCTION IF EXISTS `getObjectFullName`$$

CREATE DEFINER=`root`@`%` FUNCTION `getObjectFullName`(id INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
	DECLARE fullname VARCHAR(255) DEFAULT '';
	DECLARE txt VARCHAR(255) DEFAULT '';
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE cur CURSOR FOR SELECT o_name FROM object o JOIN (SELECT o_lft,o_rgt FROM object WHERE o_id=id) t ON o.o_lft<=t.o_lft AND o.o_rgt >= t.o_rgt WHERE o_parent_id>0 ORDER BY o.o_lft;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
	OPEN cur;
	read_loop:LOOP
		FETCH cur INTO txt;
		IF done THEN
			LEAVE read_loop;
		END IF;
		IF fullname = '' THEN
			SET fullname = txt;
		ELSE
			SET fullname = CONCAT(fullname,'_',txt);
		END IF;
	END LOOP;
	CLOSE cur;
    RETURN fullname;
END$$

DELIMITER ;

DELIMITER $$

USE `gspcatalog`$$

DROP FUNCTION IF EXISTS `getLastProMerge`$$

CREATE DEFINER=`root`@`%` FUNCTION `getLastProMerge`(fromid INT) RETURNS INT(11)
    READS SQL DATA
    COMMENT '取最终合并到的产品'
BEGIN
	DECLARE result INT DEFAULT 0;
	DECLARE toid INT DEFAULT 0;
	DECLARE regi INT DEFAULT 0;
	SELECT pch_p_id_to INTO toid FROM pro_merge WHERE pch_p_id_from = fromid LIMIT 1;
	a: WHILE toid>0 AND regi < 9 DO
	    SET result := toid;
	    SELECT pch_p_id_to INTO toid FROM pro_merge WHERE pch_p_id_from = result LIMIT 1;
	    SET regi := regi + 1;
	END WHILE a;
	RETURN result;
    END$$

DELIMITER ;

DELIMITER $$

USE `gspcatalog_federated`$$

DROP PROCEDURE IF EXISTS `load_to_memory1`$$

CREATE DEFINER=`root`@`%` PROCEDURE `load_to_memory1`()
BEGIN
	DECLARE tablename VARCHAR(255) DEFAULT '';
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE cur CURSOR FOR SELECT `TABLE_NAME` FROM information_schema.`TABLES` WHERE `TABLE_SCHEMA`='gspcatalog_federated';
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
	OPEN cur;
	read_loop:LOOP
		FETCH cur INTO tablename;
		IF done THEN
			LEAVE read_loop;
		END IF;
		SET @sql = CONCAT("TRUNCATE TABLE gspcatalog_memory1.",tablename);
		PREPARE stmt FROM @sql;
		EXECUTE stmt ;
		DEALLOCATE PREPARE stmt;
		SET @sql = CONCAT("INSERT INTO gspcatalog_memory1.",tablename," SELECT * FROM ",tablename);
		PREPARE stmt FROM @sql;
		EXECUTE stmt ;
		DEALLOCATE PREPARE stmt;
	END LOOP;
	CLOSE cur;
    END$$

DELIMITER ;

循环嵌套 

DELIMITER $$

USE `gspcatalog_params`$$

DROP PROCEDURE IF EXISTS `create_param_tables`$$

CREATE DEFINER=`root`@`%` PROCEDURE `create_param_tables`() COMMENT "创建品类参数表"
BEGIN
	DECLARE var_cate_id INT DEFAULT 0;
	DECLARE var_param_id INT DEFAULT 0;
	DECLARE var_param_type VARCHAR(10) DEFAULT '';
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE cur_cate CURSOR FOR SELECT DISTINCT ptpa_pc_id FROM gspcatalog.`gsp_part_param`;
	DECLARE cur_param CURSOR FOR SELECT ptpa_id,ptpa_type FROM gspcatalog.`gsp_part_param` WHERE ptpa_pc_id=var_cate_id;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
	OPEN cur_cate;
	SET @REMARK = "遍历品类";	
	loop_cate:LOOP
		FETCH cur_cate INTO var_cate_id;
		IF done THEN
			LEAVE loop_cate;
		END IF;
		SET @REMARK = "遍历参数字段";	
		SET @fileds = '';
		OPEN cur_param;
		loop_param:LOOP
			FETCH cur_param INTO var_param_id,var_param_type;
			IF done THEN
				LEAVE loop_param;
			END IF;
			IF var_param_type = 'string' THEN
				SET @fileds = CONCAT(@fileds,"`param_",var_param_id,"` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,");
			ELSEIF var_param_type = 'int' THEN
				SET @fileds = CONCAT(@fileds,"`param_",var_param_id,"` INT(11) DEFAULT NULL,");
			ELSEIF var_param_type = 'double' THEN
				SET @fileds = CONCAT(@fileds,"`param_",var_param_id,"` DECIMAL(20,6) DEFAULT NULL,");
			END IF;
		END LOOP loop_param;
		CLOSE cur_param;
		SET done=0;
		SET @REMARK = "创建表结构";	
		SET @sql = CONCAT("DROP TABLE IF EXISTS gspcatalog_params.`cate_",var_cate_id,"`");
		PREPARE stmt FROM @sql;
		EXECUTE stmt ;
		DEALLOCATE PREPARE stmt;
		SET @sql = CONCAT(
			"CREATE TABLE gspcatalog_params.`cate_",var_cate_id,"` (",
			"  `pro_id` INT(11),",
			@fileds,
			"  PRIMARY KEY (`pro_id`)",
			") ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
		);
		PREPARE stmt FROM @sql;
		EXECUTE stmt ;
		DEALLOCATE PREPARE stmt;
	END LOOP loop_cate;
	CLOSE cur_cate;		
    END$$

DELIMITER ;

JSON数组的使用 

ALTER TABLE smt_pro ADD INDEX idx_smt_makes ((CAST(smt_makes->>'$[*]' AS UNSIGNED ARRAY)));
SELECT * FROM smt_pro WHERE  JSON_CONTAINS(smt_makes,'445')

存储过程

DELIMITER $$

USE `gsp_smart`$$

DROP PROCEDURE IF EXISTS `build_smt_pro_makes`$$

CREATE DEFINER=`root`@`%` PROCEDURE `build_smt_pro_makes`()
BEGIN
DECLARE p_id INT;	
DECLARE mk_id INT;	
DECLARE old_id INT DEFAULT 0;
#DECLARE txt varchar(4096) DEFAULT '';
DECLARE jsonArr JSON DEFAULT JSON_ARRAY();
DECLARE done BOOLEAN DEFAULT 0;
DECLARE cur CURSOR FOR SELECT DISTINCT smt_p_id,mm_mk_id FROM smt_pro JOIN fed_pdc_pro_model_link ON  smt_p_id =pml_p_id JOIN fed_pdc_m_model ON pml_mm_id=mm_id WHERE smt_p_id<50 ORDER BY smt_p_id;	
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
START TRANSACTION;
	OPEN cur;
	read_loop:LOOP
		FETCH cur INTO p_id,mk_id;
		IF done THEN
			LEAVE read_loop;
		END IF;
		IF old_id < 1 THEN
			SET old_id = p_id;
		END IF;
		IF old_id <> p_id THEN
			#update smt_pro set smt_makes = txt where smt_p_id=old_id;
			#set txt = '';
			UPDATE smt_pro SET smt_makes = jsonArr WHERE smt_p_id=old_id;
			SET jsonArr = JSON_ARRAY();
			SET old_id = p_id;
		END IF;
		SET jsonArr = JSON_ARRAY_APPEND(jsonArr, '$', mk_id);
		#SET txt = CONCAT(txt,',',CAST(mk_id AS CHAR));
	END LOOP;
	CLOSE cur;
	SET @Remark = '最后一条PID';
	#UPDATE smt_pro SET smt_makes = txt WHERE smt_p_id=old_id;
	UPDATE smt_pro SET smt_makes = jsonArr WHERE smt_p_id=old_id;
COMMIT;
END$$

DELIMITER ;