工作中使用到的一个存储过程，记录一下，以备后面查阅。
代码语言：javascript复制DROP PROCEDURE
IF EXISTS INI_MOD_POOL;
CREATE PROCEDURE INI_MOD_POOL (
IN c_company_guid VARCHAR (50),
IN c_project_guid VARCHAR (50),
IN c_project_card_id VARCHAR (50)
)
BEGIN
DECLARE
c_mod INT;
DECLARE
c_mod_pool INT;
DECLARE
c_mode_app_pool INT;
DECLARE
demp_standard_catelog_code VARCHAR (50);
DECLARE
demp_standard_modular_code VARCHAR (50);
DECLARE
demp_building_id VARCHAR (50);
DECLARE
demp_is_exist INT;
DECLARE
demp_mod_app_info_exist INT;
DECLARE Done INT DEFAULT 0;
#创建游标，第二步,取两个表的合集
DECLARE
first_cursor CURSOR FOR SELECT
mainfo.standard_catalog_code standard_catelog_code,
mainfo.standard_modular_code standard_modular_code,
mainfo.building_id building_id
FROM
td_modular_app_info mainfo
WHERE
mainfo.company_guid = c_company_guid
AND mainfo.project_guid = c_project_guid
AND mainfo.project_card_id = c_project_card_id
UNION
SELECT
mp.standard_catelog_code standard_catelog_code,
mp.standard_modular_code standard_modular_code,
mp.building_id building_id
FROM
td_modular_pool mp
WHERE
mp.company_guid = c_company_guid
AND mp.project_guid = c_project_guid
AND mp.project_card_id = c_project_card_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
#声明数据类型的方法，首先查看是否模块应用表中是否有值
SELECT
COUNT(*) INTO c_mod
FROM
td_modular_pool mp,
td_modular_app_info mainfo
WHERE
mp.company_guid = mainfo.company_guid
AND mp.project_guid = mainfo.project_guid
AND mp.project_card_id = mainfo.project_card_id
AND mp.company_guid = c_company_guid
AND mp.project_guid = c_project_guid
AND mp.project_card_id = c_project_card_id;
#模块池第一次进入模块应用表
IF c_mod = 0 THEN
INSERT INTO td_modular_app_info (
version,
project_guid,
project_card_id,
company_guid,
standard_catalog_code,
standard_catalog_name,
standard_modular_code,
standard_module_name,
building_id,
building_num,
groups,
Architecture_type,
uom,
unit_price,
quantity,
estimated_amount,
create_time,
create_by
) SELECT
mp.version,
mp.project_guid,
mp.project_card_id,
mp.company_guid,
mp.standard_catelog_code,
mp.standard_catelog_name,
mp.standard_modular_code,
mp.standard_module_name,
mp.building_id,
mp.building_num,
mp.groups,
mp.Architecture_type,
mp.uom,
mp.unit_price,
mp.quantity,
mp.estimated_amount,
mp.create_time,
mp.create_by
FROM
td_modular_pool mp
WHERE
mp.company_guid = c_company_guid
AND mp.project_guid = c_project_guid
AND mp.project_card_id = c_project_card_id;
ELSE
#把模块应用表里的数据保存到历史表
INSERT INTO td_modular_app_his_info(
modular_id
,version
,project_guid
,project_card_id
,company_guid
,standard_catalog_code
,standard_catalog_name
,standard_modular_code
,standard_module_name
,building_id
,building_num
,groups
,architecture_type
,uom
,unit_price
,quantity
,estimated_amount
,create_time
,is_enabled
,is_distribution
,is_delete_abled
,init_status
,plan_id
,plan_status
,contract_demand_id
,contract_demand_status
,project_id
,project_status
,tender_doc_id
,tender_doc_status
,scaling_id
,scaling_status
,column_1
,column_2
,column_3
,column_4
,column_5
,column_6
,Column_39
,create_by
,last_update_time
,last_update_by
,is_delete
)SELECT
id
,version
,project_guid
,project_card_id
,company_guid
,standard_catalog_code
,standard_catalog_name
,standard_modular_code
,standard_module_name
,building_id
,building_num
,groups
,architecture_type
,uom
,unit_price
,quantity
,estimated_amount
,create_time
,is_enabled
,is_distribution
,is_delete_abled
,init_status
,plan_id
,plan_status
,contract_demand_id
,contract_demand_status
,project_id
,project_status
,tender_doc_id
,tender_doc_status
,scaling_id
,scaling_status
,column_1
,column_2
,column_3
,column_4
,column_5
,column_6
,Column_39
,create_by
,last_update_time
,last_update_by
,is_delete
from td_modular_app_info mappinfo
where
mappinfo.company_guid = c_company_guid
AND mappinfo.project_guid = c_project_guid
AND mappinfo.project_card_id = c_project_card_id;
SELECT
'以前测试通过';
#第二步 模块应用表有记录的判断
OPEN first_cursor;
FETCH NEXT
FROM
first_cursor INTO demp_standard_catelog_code,
demp_standard_modular_code,
demp_building_id;
REPEAT
IF NOT Done THEN
#SELECT demp_standard_catelog_code;#查询模块池表
SELECT COUNT(*) INTO demp_is_exist FROM td_modular_pool mp
WHERE
mp.standard_catelog_code = demp_standard_catelog_code
AND mp.standard_modular_code = demp_standard_modular_code
AND mp.building_id = demp_building_id;
#SELECT demp_is_exist;
IF demp_is_exist = 0 THEN
SELECT '表td_modular_pool模块池不存在，表示删除了该条记录' as is_exist;
UPDATE td_modular_app_info SET init_status=0 WHERE
standard_catalog_code = demp_standard_catelog_code
AND standard_modular_code = demp_standard_modular_code
AND building_id = demp_building_id
AND company_guid = c_company_guid
AND project_guid = c_project_guid
AND project_card_id = c_project_card_id;
ELSE
SELECT '表td_modular_pool模块池存在，则更新该条记录' as is_exist;
update td_modular_app_info modinfo,td_modular_pool mp
SET
modinfo.version=mp.version,
modinfo.standard_catalog_name=mp.standard_catelog_name,
modinfo.standard_module_name=mp.standard_module_name,
modinfo.building_num=mp.building_num,
modinfo.groups=mp.groups,
modinfo.Architecture_type=mp.Architecture_type,
modinfo.uom=mp.uom,
modinfo.unit_price=mp.unit_price,
modinfo.quantity=mp.quantity,
modinfo.estimated_amount=mp.estimated_amount,
modinfo.create_time=mp.create_time,
modinfo.create_by=mp.create_by
WHERE
mp.company_guid = modinfo.company_guid
AND mp.project_guid = modinfo.project_guid
AND mp.project_card_id = modinfo.project_card_id
AND modinfo.standard_catalog_code = demp_standard_catelog_code
AND modinfo.standard_modular_code = demp_standard_modular_code
AND modinfo.building_id = demp_building_id
AND mp.company_guid = c_company_guid
AND mp.project_guid = c_project_guid
AND mp.project_card_id = c_project_card_id;
END IF;
#SELECT demp_standard_catelog_code;#查询模块应用表
SELECT COUNT(*) INTO demp_mod_app_info_exist FROM td_modular_app_info modappinfo
WHERE
modappinfo.standard_catalog_code = demp_standard_catelog_code
AND modappinfo.standard_modular_code = demp_standard_modular_code
AND modappinfo.building_id = demp_building_id;
IF demp_mod_app_info_exist = 0 THEN
INSERT INTO td_modular_app_info (
version,
project_guid,
project_card_id,
company_guid,
standard_catalog_code,
standard_catalog_name,
standard_modular_code,
standard_module_name,
building_id,
building_num,
groups,
Architecture_type,
uom,
unit_price,
quantity,
estimated_amount,
create_time,
create_by
) SELECT
mp.version,
mp.project_guid,
mp.project_card_id,
mp.company_guid,
mp.standard_catelog_code,
mp.standard_catelog_name,
mp.standard_modular_code,
mp.standard_module_name,
mp.building_id,
mp.building_num,
mp.groups,
mp.Architecture_type,
mp.uom,
mp.unit_price,
mp.quantity,
mp.estimated_amount,
mp.create_time,
mp.create_by
FROM
td_modular_pool mp
WHERE
mp.company_guid = c_company_guid
AND mp.project_guid = c_project_guid
AND mp.project_card_id = c_project_card_id
AND mp.standard_catelog_code = demp_standard_catelog_code
AND mp.standard_modular_code = demp_standard_modular_code
AND mp.building_id = demp_building_id;
END IF;
END IF;
FETCH NEXT
FROM
first_cursor INTO demp_standard_catelog_code,
demp_standard_modular_code,
demp_building_id;
UNTIL Done END REPEAT;
CLOSE first_cursor;
END IF;
END;
