BEGIN
DECLARE orgcode_1 VARCHAR (100) DEFAULT NULL ;
DECLARE item_code_1 VARCHAR (100) DEFAULT NULL ;
DECLARE item_name_1 VARCHAR (100) DEFAULT NULL ;
DECLARE busi_name_1 VARCHAR (100) DEFAULT NULL ;
DECLARE english_name_1 VARCHAR (100) DEFAULT NULL ;
DECLARE charge_type_1 VARCHAR (100) DEFAULT NULL ;
DECLARE charge_level_1 VARCHAR (100) DEFAULT NULL ;
DECLARE py_mnemonic_code_1 VARCHAR (100) DEFAULT NULL ;
DECLARE wb_mnemonic_code_1 VARCHAR (100) DEFAULT NULL ;
DECLARE masi_units_1 VARCHAR (100) DEFAULT NULL ;
DECLARE dosage_form_1 VARCHAR (100) DEFAULT NULL ;
DECLARE specification_1 VARCHAR (100) DEFAULT NULL ;
DECLARE each_dosage_1 VARCHAR (100) DEFAULT NULL ;
DECLARE rx_drug_mark_1 VARCHAR (100) DEFAULT NULL ;
DECLARE check_price_1 VARCHAR (100) DEFAULT NULL ;
DECLARE masi_remark_1 VARCHAR (100) DEFAULT NULL ;
DECLARE usage_frequency_1 VARCHAR (100) DEFAULT NULL ;
DECLARE masi_usage_1 VARCHAR (100) DEFAULT NULL ;
DECLARE token int DEFAULT 0;
DECLARE done int DEFAULT 0;
/*达到一定数量就进行提交,计数器*/
DECLARE counts INT DEFAULT 0;
#定义游标
DECLARE rs_cursor CURSOR FOR SELECT orgcode FROM wssorgcode;
#游标结束条件
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
/*关闭自动提交事务*/
set autocommit=0;
/*开启事务*/
START TRANSACTION;
#打开游标
OPEN rs_cursor;
cursor_loop:LOOP
FETCH rs_cursor INTO orgcode_1;
IF done = 1 THEN
LEAVE cursor_loop;
END IF;
BEGIN
DECLARE
rs_cursor_item CURSOR
FOR
SELECT
AKA060,AKA061,AKA062,AKA063,AKA065,AKA066,AKA067,AKA070,AKA074,AKA071,AKA064,AKA068,AAE013,AKA072,AKA073
FROM ka02ins GROUP BY AKA060,AKA061 ;
#游标结束条件
DECLARE CONTINUE HANDLER FOR NOT FOUND SET token=1;
OPEN rs_cursor_item;
REPEAT
-- 批读取数据到指定变量上
FETCH rs_cursor_item INTO
item_code_1,
item_name_1,
english_name_1,
charge_type_1,
charge_level_1,
py_mnemonic_code_1,
masi_units_1,
dosage_form_1,
specification_1,
each_dosage_1,
rx_drug_mark_1,
check_price_1,
masi_remark_1,
usage_frequency_1,
masi_usage_1; -- 进行逻辑操作
INSERT INTO `catalog_org_drug_copy` (
`id`,`shop_id`,`city_code`,`medical_type`,`item_code`,`item_name`,`busi_name`,`english_name`,`charge_type`,`masi_imported`,`rx_drug_mark`,`charge_level`,`each_dosage`,
`usage_frequency`, `py_mnemonic_code`,`wb_mnemonic_code`,`masi_units`,`masi_usage`,`masi_factory`,`min_payment_units`,`dosage_form`,`specification`,
`status`,`upload_flag`,`check_price`,`spda_code`,`approval_number`,`approval_remark`,`create_time`,`create_operator`,`update_time`,`update_operator`,
`source`,`masi_remark`,`drug_standard_code`,`currency_name`,`chemical_name`
)
VALUES
(
CONCAT(CONCAT(
unix_timestamp(now()),
round(rand() * 100000000000,0)
),round(rand() * 100000000000,0)),
(
SELECT
shop_id
FROM
auth_user_info
WHERE org_code = orgcode_1
),'610300','103',item_code_1,item_name_1,item_name_1,english_name_1,charge_type_1,'0',rx_drug_mark_1,charge_level_1,each_dosage_1,
usage_frequency_1,py_mnemonic_code_1,NULL,masi_units_1,masi_usage_1,NULL,NULL,dosage_form_1,specification_1,
'1','1',check_price_1,NULL,NULL,NULL,NOW(),'ww20',NOW(),'ww20','3',
masi_remark_1,
NULL,
NULL,
NULL
);
INSERT INTO `catalog_relation_drug_copy` (
`id`,`medical_type`,`shop_id`,`city_code`,`org_code`,
`org_item_code`,`insurance_item_code`,`status`,`relation_id`,`refuse_cause`,
`create_time`,`create_operator`,`update_time`,`update_operator`
)
VALUES
(
CONCAT(CONCAT(
unix_timestamp(now()),
round(rand() * 100000000000,0)
),round(rand() * 100000000000,0)),'103',
(
SELECT
shop_id
FROM
auth_user_info
WHERE org_code = orgcode_1
),'610300', orgcode_1,
item_code_1,item_code_1,'1',NULL,NULL,
NOW(),'ww20',NOW(),'ww20'
);
SET counts = counts + 1;
IF counts = 1000 THEN
COMMIT;
/*达到1000条提交后,重置计数器*/
SET counts=0;
END IF;
#UNTIL token=1 END REPEAT ;
UNTIL token = 1 END REPEAT;
CLOSE rs_cursor_item;
SET token=0;
END;
END LOOP;
CLOSE rs_cursor;
COMMIT;
END