需求一:在element标志构造20棵产品包树。
产品是一个树形结构,我需要造20个可售卖包。
每个可售卖包下,包含一个物理产品包。
每个物理包下面有3个产品。
每个产品下面有1个特征,即1个feature。
每个feature下面有4个参数值,分别是int,float,string,和Boolean类型。
需求二:在element_ref中记录了其他一些信息,例如父节点id。
表element数据构造,这里只包含产品包树中的可售卖包,物理包,产品和特征,
不包含参数!
DELIMITER $$
DROP PROCEDURE IF EXISTS `INSERT_ELEMENT` $$
CREATE PROCEDURE `INSERT_ELEMENT`()
BEGIN
DECLARE iMod SMALLINT DEFAULT 0;
WHILE iMod<=160 DO
IF iMod<=20 THEN
INSERT INTO element VALUES ( '',CONCAT( '可售卖包',iMod),CONCAT( 'sellpackage_',iMod), '', 'ENABLED', 'SELLPACKAGE', '2010-08-24 12:00:00', '2010-08-25 12:00:00');
ELSEIF iMod<=40 THEN
INSERT INTO element VALUES ( '',CONCAT( '产品包',iMod),CONCAT( 'package_',iMod), '', 'ENABLED', 'PACKAGE', '2010-08-24 12:00:00', '2010-08-25 12:00:00');
ELSEIF iMod<=100 THEN
INSERT INTO element VALUES ( '',CONCAT( '产品',iMod),CONCAT( 'product_',iMod), '', 'ENABLED', 'PRODUCT', '2010-08-24 12:00:00', '2010-08-25 12:00:00');
ELSEIF iMod<=160 THEN
INSERT INTO element VALUES ( '',CONCAT( '功能项',iMod),CONCAT( 'feature_',iMod), '', 'ENABLED', 'FEATURE', '2010-08-24 12:00:00', '2010-08-25 12:00:00');
END IF;
SET iMod=iMod+1;
END WHILE;
COMMIT;
END $$
DELIMITER ;
CALL `INSERT_ELEMENT`();
DROP PROCEDURE IF EXISTS `INSERT_ELEMENT` $$
CREATE PROCEDURE `INSERT_ELEMENT`()
BEGIN
DECLARE iMod SMALLINT DEFAULT 0;
WHILE iMod<=160 DO
IF iMod<=20 THEN
INSERT INTO element VALUES ( '',CONCAT( '可售卖包',iMod),CONCAT( 'sellpackage_',iMod), '', 'ENABLED', 'SELLPACKAGE', '2010-08-24 12:00:00', '2010-08-25 12:00:00');
ELSEIF iMod<=40 THEN
INSERT INTO element VALUES ( '',CONCAT( '产品包',iMod),CONCAT( 'package_',iMod), '', 'ENABLED', 'PACKAGE', '2010-08-24 12:00:00', '2010-08-25 12:00:00');
ELSEIF iMod<=100 THEN
INSERT INTO element VALUES ( '',CONCAT( '产品',iMod),CONCAT( 'product_',iMod), '', 'ENABLED', 'PRODUCT', '2010-08-24 12:00:00', '2010-08-25 12:00:00');
ELSEIF iMod<=160 THEN
INSERT INTO element VALUES ( '',CONCAT( '功能项',iMod),CONCAT( 'feature_',iMod), '', 'ENABLED', 'FEATURE', '2010-08-24 12:00:00', '2010-08-25 12:00:00');
END IF;
SET iMod=iMod+1;
END WHILE;
COMMIT;
END $$
DELIMITER ;
CALL `INSERT_ELEMENT`();
表elment_ref数据构造
DELIMITER $$
DROP PROCEDURE IF EXISTS `INSERT_ELEMENT_REF` $$
CREATE PROCEDURE `INSERT_ELEMENT_REF`()
BEGIN
DECLARE iMod SMALLINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 1112123;
DECLARE iSell MEDIUMINT DEFAULT 1112123;
DECLARE iPackage MEDIUMINT DEFAULT 1112144;
DECLARE iProduct MEDIUMINT DEFAULT 1112164;
WHILE iMod<=160 DO
IF iMod<=20 THEN
INSERT INTO element_ref VALUES ( '',iNum, '-1',NOW(),NOW());
ELSEIF iMod<=40 THEN
INSERT INTO element_ref VALUES ( '',iNum,iSell,NOW(),NOW());
SET iSell=iSell+1;
ELSEIF iMod<=100 THEN
INSERT INTO element_ref VALUES ( '',iNum,iPackage,NOW(),NOW());
SET iPackage=iPackage+1;
ELSEIF iMod<=160 THEN
INSERT INTO element_ref VALUES ( '',iNum,iProduct,NOW(),NOW());
SET iProduct=iProduct+1;
END IF;
SET iNum=iNum+1;
SET iMod=iMod+1;
END WHILE;
COMMIT;
END $$
DELIMITER ;
CALL `INSERT_ELEMENT_REF`();
DROP PROCEDURE IF EXISTS `INSERT_ELEMENT_REF` $$
CREATE PROCEDURE `INSERT_ELEMENT_REF`()
BEGIN
DECLARE iMod SMALLINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 1112123;
DECLARE iSell MEDIUMINT DEFAULT 1112123;
DECLARE iPackage MEDIUMINT DEFAULT 1112144;
DECLARE iProduct MEDIUMINT DEFAULT 1112164;
WHILE iMod<=160 DO
IF iMod<=20 THEN
INSERT INTO element_ref VALUES ( '',iNum, '-1',NOW(),NOW());
ELSEIF iMod<=40 THEN
INSERT INTO element_ref VALUES ( '',iNum,iSell,NOW(),NOW());
SET iSell=iSell+1;
ELSEIF iMod<=100 THEN
INSERT INTO element_ref VALUES ( '',iNum,iPackage,NOW(),NOW());
SET iPackage=iPackage+1;
ELSEIF iMod<=160 THEN
INSERT INTO element_ref VALUES ( '',iNum,iProduct,NOW(),NOW());
SET iProduct=iProduct+1;
END IF;
SET iNum=iNum+1;
SET iMod=iMod+1;
END WHILE;
COMMIT;
END $$
DELIMITER ;
CALL `INSERT_ELEMENT_REF`();
表feature_parameter构造数据,这是产品包树中的参数数据
DELIMITER $$
DROP PROCEDURE IF EXISTS `INSERT_FEATURE_PARAMETER` $$
CREATE PROCEDURE `INSERT_FEATURE_PARAMETER`()
BEGIN
DECLARE iMod SMALLINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE iFeatureID MEDIUMINT DEFAULT 1112224;
DECLARE iFeature MEDIUMINT DEFAULT 101;
WHILE iMod<120 DO
WHILE iNum<4 DO
IF iNum=0 THEN
INSERT INTO feature_parameter VALUES ( '',iFeatureID,CONCAT( '参数',iFeature),CONCAT( 'parameter',iFeature), '', 'ENABLED', 'INT', '100', '3333',NOW(),NOW());
ELSEIF iNum=1 THEN
INSERT INTO feature_parameter VALUES ( '',iFeatureID,CONCAT( '参数',iFeature),CONCAT( 'parameter',iFeature), '', 'ENABLED', 'FLOAT', '96.8', '3333',NOW(),NOW());
ELSEIF iNum=2 THEN
INSERT INTO feature_parameter VALUES ( '',iFeatureID,CONCAT( '参数',iFeature),CONCAT( 'parameter',iFeature), '', 'ENABLED', 'BOOLEAN', 'TRUE', '3333',NOW(),NOW());
ELSEIF iNum=3 THEN
INSERT INTO feature_parameter VALUES ( '',iFeatureID,CONCAT( '参数',iFeature),CONCAT( 'parameter',iFeature), '', 'ENABLED', 'STRING', 'sellParam', '3333',NOW(),NOW());
END IF;
SET iNum=iNum+1;
SET iFeature =iFeature+1;
END WHILE;
SET iNum=0;
SET iMod=iMod+1;
SET iFeatureID=iFeatureID+1;
END WHILE;
COMMIT;
END $$
DELIMITER ;
CALL `INSERT_FEATURE_PARAMETER`();
DROP PROCEDURE IF EXISTS `INSERT_FEATURE_PARAMETER` $$
CREATE PROCEDURE `INSERT_FEATURE_PARAMETER`()
BEGIN
DECLARE iMod SMALLINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE iFeatureID MEDIUMINT DEFAULT 1112224;
DECLARE iFeature MEDIUMINT DEFAULT 101;
WHILE iMod<120 DO
WHILE iNum<4 DO
IF iNum=0 THEN
INSERT INTO feature_parameter VALUES ( '',iFeatureID,CONCAT( '参数',iFeature),CONCAT( 'parameter',iFeature), '', 'ENABLED', 'INT', '100', '3333',NOW(),NOW());
ELSEIF iNum=1 THEN
INSERT INTO feature_parameter VALUES ( '',iFeatureID,CONCAT( '参数',iFeature),CONCAT( 'parameter',iFeature), '', 'ENABLED', 'FLOAT', '96.8', '3333',NOW(),NOW());
ELSEIF iNum=2 THEN
INSERT INTO feature_parameter VALUES ( '',iFeatureID,CONCAT( '参数',iFeature),CONCAT( 'parameter',iFeature), '', 'ENABLED', 'BOOLEAN', 'TRUE', '3333',NOW(),NOW());
ELSEIF iNum=3 THEN
INSERT INTO feature_parameter VALUES ( '',iFeatureID,CONCAT( '参数',iFeature),CONCAT( 'parameter',iFeature), '', 'ENABLED', 'STRING', 'sellParam', '3333',NOW(),NOW());
END IF;
SET iNum=iNum+1;
SET iFeature =iFeature+1;
END WHILE;
SET iNum=0;
SET iMod=iMod+1;
SET iFeatureID=iFeatureID+1;
END WHILE;
COMMIT;
END $$
DELIMITER ;
CALL `INSERT_FEATURE_PARAMETER`();
需求三:从400万数据的account表中,导入vaccount_id,为表subscription构造数据。
这里以41*11*20的数据量为例
DELIMITER $$
DROP PROCEDURE IF EXISTS `INSERT_SUBSCRIPTION` $$
CREATE PROCEDURE `INSERT_SUBSCRIPTION`()
BEGIN
DECLARE iloop SMALLINT DEFAULT 0;
DECLARE iMod MEDIUMINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE sellId MEDIUMINT DEFAULT 1112123;
DECLARE packageId MEDIUMINT DEFAULT 1112144;
DECLARE strMemberID VARCHAR(40) DEFAULT 0;
DECLARE Cur_account CURSOR FOR SELECT member_id FROM account;
OPEN Cur_account;
FETCH Cur_account INTO strMemberID;
WHILE iNum <= 40 DO
START TRANSACTION;
SET iloop=0;
WHILE iloop<=10 DO
SET iMod=0;
WHILE iMod < 20 DO
IF iMod < 5 THEN
INSERT INTO subscription VALUES ( '',packageId,strMemberID, 'START', 'INTL',NOW(),NOW(),CONCAT(strMemberID, '_',iMod), 'ENTERPSISE',sellId);
ELSEIF iMod <10 THEN
INSERT INTO subscription VALUES ( '',packageId,strMemberID, 'READY', 'INTL',NOW(),NOW(),CONCAT(strMemberID, '_',iMod), 'ENTERPSISE',sellId);
ELSEIF iMod <15 THEN
INSERT INTO subscription VALUES ( '',packageId,strMemberID, 'CANCEL', 'INTL',NOW(),NOW(),CONCAT(strMemberID, '_',iMod), 'ENTERPSISE',sellId);
ELSEIF iMod < 20 THEN
INSERT INTO subscription VALUES ( '',packageId,strMemberID, 'END', 'INTL',NOW(),NOW(),CONCAT(strMemberID, '_',iMod), 'ENTERPSISE',sellId);
END IF;
SET packageId = packageId+1;
SET sellId= sellId+1;
SET iMod = iMod+1;
END WHILE;
FETCH Cur_account INTO strMemberID;
SET iloop=iloop+1;
END WHILE;
COMMIT;
SET iNum=iNum+1;
END WHILE;
CLOSE Cur_account;
END $$
DELIMITER ;
CALL `INSERT_SUBSCRIPTION`();
DROP PROCEDURE IF EXISTS `INSERT_SUBSCRIPTION` $$
CREATE PROCEDURE `INSERT_SUBSCRIPTION`()
BEGIN
DECLARE iloop SMALLINT DEFAULT 0;
DECLARE iMod MEDIUMINT DEFAULT 0;
DECLARE iNum MEDIUMINT DEFAULT 0;
DECLARE sellId MEDIUMINT DEFAULT 1112123;
DECLARE packageId MEDIUMINT DEFAULT 1112144;
DECLARE strMemberID VARCHAR(40) DEFAULT 0;
DECLARE Cur_account CURSOR FOR SELECT member_id FROM account;
OPEN Cur_account;
FETCH Cur_account INTO strMemberID;
WHILE iNum <= 40 DO
START TRANSACTION;
SET iloop=0;
WHILE iloop<=10 DO
SET iMod=0;
WHILE iMod < 20 DO
IF iMod < 5 THEN
INSERT INTO subscription VALUES ( '',packageId,strMemberID, 'START', 'INTL',NOW(),NOW(),CONCAT(strMemberID, '_',iMod), 'ENTERPSISE',sellId);
ELSEIF iMod <10 THEN
INSERT INTO subscription VALUES ( '',packageId,strMemberID, 'READY', 'INTL',NOW(),NOW(),CONCAT(strMemberID, '_',iMod), 'ENTERPSISE',sellId);
ELSEIF iMod <15 THEN
INSERT INTO subscription VALUES ( '',packageId,strMemberID, 'CANCEL', 'INTL',NOW(),NOW(),CONCAT(strMemberID, '_',iMod), 'ENTERPSISE',sellId);
ELSEIF iMod < 20 THEN
INSERT INTO subscription VALUES ( '',packageId,strMemberID, 'END', 'INTL',NOW(),NOW(),CONCAT(strMemberID, '_',iMod), 'ENTERPSISE',sellId);
END IF;
SET packageId = packageId+1;
SET sellId= sellId+1;
SET iMod = iMod+1;
END WHILE;
FETCH Cur_account INTO strMemberID;
SET iloop=iloop+1;
END WHILE;
COMMIT;
SET iNum=iNum+1;
END WHILE;
CLOSE Cur_account;
END $$
DELIMITER ;
CALL `INSERT_SUBSCRIPTION`();
注意最后一个案例,这里引入了
“游码”,我把它当做C语言里面的指针理解。后续可以加强练习使用。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
悲剧发生了,我理解错了业务,subscription表的status字段,只有enabled和disabled两个值,subscription_detail的status字段,才分Start,pause,cancel,end,ready等值。。。。。
不过,存储过程还是一样的思路
其他表数据的构造,参考:
转载于:https://blog.51cto.com/vicky001/384808