计算项预加工的实质逻辑存储过程。
1 调用格式
p_dn_ci_computer(
v_app VARCHAR (50),
v_ci VARCHAR (50),
v_cf VARCHAR (50),
OUT i_count_field INT(2),
OUT v_err VARCHAR(4096),
OUT v_log TEXT
)参数说明：
v_app：字符串类型；乐创者的应用名。因为1个乐创者服务支持多个应用，所以需要指定1个明确的应用。
v_ci：字符串类型；计算项名称。
v_cf：字符串类型；字段名称。
i_count_field：整型；返回计算的字段数量。
v_err：字符串类型；返回错误报告信息。
v_log：字符串类型；返回日志信息。2 实现原理
本存储过程由 p_dn_ci 进行调用，完成lczWord进行计算项预加工的实质逻辑。
处理过程大致是：
1）启动事务；
2）清除历史数据；
3）构造参数组合信息；
3）按字段轮询，每个字段基于参数组合表进行轮询；
4）如果计算依据是一个SQL，则执行SQL；
5）如果计算依据是一个用户自定义存储过程，则判断是在 p_dn_ci_computer 来实现参数轮询呢还是用户自定义存储过程中实现参数轮询。
6）如果不需要由 p_dn_ci_computer 来进行参数轮询，则直接执行用户自定义存储过程即可，在 p_dn_ci_computer 中调用用户的自定义存储过程。
7）如果需要由 p_dn_ci_computer 来进行参数轮询，则需要轮询参数，每种参数组合分别执行用户自定义存储过程。
8）如执行顺利则递交事务，否则事务回滚。
3 存储过程
存储过程如下：
CREATE DEFINER=`root`@`%` PROCEDURE `p_dn_ci_compute`(v_App VARCHAR (50),-- 应用
v_CI VARCHAR (50), -- 计算项
v_CF VARCHAR (50), -- 字段
OUT i_Count_Field INT(2), -- 返回计算的字段数量
OUT v_Err VARCHAR(4096), -- 返回错误报告
OUT v_Log TEXT)
LABEL1:BEGIN
/* -------------------------------------------
1、定义全局变量
------------------------------------------- */
DECLARE v_t1_fields VARCHAR(200) DEFAULT '';
DECLARE v_t1_create VARCHAR(500) DEFAULT '';
DECLARE v_where_result VARCHAR(500) DEFAULT '';
DECLARE v_str_result VARCHAR(500) DEFAULT '';
DECLARE v_from VARCHAR(1024) DEFAULT '';
DECLARE i_debug TINYINT(1) DEFAULT 1;
SET v_Log = '';
SET v_Err = '';
START TRANSACTION;
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,'{\"step\":\"开始事务\"}');
END IF;
/* -------------------------------------------
1、FOR 清空已有结果数据
------------------------------------------- */
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_Err = '严重错误：清空结果数据失败';
-- FIX 修正删除数据错误问题，改为cszh_guid,修正删除单个字段时删除所有字段值的异常
IF v_CF = '' THEN
DELETE FROM dn_cc_jsx_zd_jg WHERE cszh_guid IN (SELECT GUID FROM dn_cc_jsx_cs_zh WHERE YYMC = v_App AND JSXMC = v_CI);
ELSE
DELETE FROM dn_cc_jsx_zd_jg WHERE zdmc = v_CF AND cszh_guid IN (SELECT GUID FROM dn_cc_jsx_cs_zh WHERE YYMC = v_App AND JSXMC = v_CI);
END IF;
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"清空了现有结果数据\"}');
END IF;
END;
-- 错误检查
BEGIN
IF v_Err <> '' THEN
ROLLBACK;
LEAVE LABEL1;
END IF;
END;
/* -------------------------------------------
2、FOR 参数组合
------------------------------------------- */
-- 2.1 准备创建临时表 t1，构造v_t1_create和v_t1_fields
BEGIN
DECLARE i_found INT(2) DEFAULT 0;
DECLARE v_para_name VARCHAR(50) DEFAULT '';
DECLARE cur_paras CURSOR FOR SELECT CSMC FROM dn_dy_jsx_cs WHERE YYMC = v_App AND JSXMC = v_CI ORDER BY CSSX;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET i_found = NULL;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_Err = '严重错误：无法打开dn_dy_jsx_cs表';
OPEN cur_paras;
FETCH cur_paras INTO v_para_name;
IF i_found IS NULL THEN
SET v_Err = 'Err:dn_dy_jsx_cs表没有检索到数据';
CLOSE cur_paras;
ROLLBACK;
LEAVE LABEL1;
END IF;
WHILE (i_found IS NOT NULL) DO
IF v_t1_create = '' THEN
SET v_t1_create = v_para_name;
SET v_t1_fields = v_para_name;
ELSE
SET v_t1_create = CONCAT(v_t1_create,',',v_para_name);
SET v_t1_fields = CONCAT(v_t1_fields,',',v_para_name);
END IF;
SET v_t1_create = CONCAT(v_t1_create,' VARCHAR(50)');
FETCH cur_paras INTO v_para_name;
END WHILE;
CLOSE cur_paras;
END;
-- 错误检查
BEGIN
IF v_Err <> '' THEN
ROLLBACK;
LEAVE LABEL1;
END IF;
END;
-- 2.2 创建临时表t1
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_Err = '严重错误：无法创建临时表t1';
SET @t1_create = CONCAT('CREATE TEMPORARY TABLE t1(',v_t1_create,');');
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"创建临时表t1，有几个全局参数就会有几个字段，sql:',replace(@t1_create,"\"","'"),'\"}');
END IF;
PREPARE pr1 FROM @t1_create;
EXECUTE pr1;
DROP PREPARE pr1;
END;
-- 错误检查
BEGIN
IF v_Err <> '' THEN
ROLLBACK;
LEAVE LABEL1;
END IF;
END;
-- 2.3 生成记录准备插入临时表t1
BEGIN
-- 1) 构造Cross联合表
DECLARE i_found INT(2) DEFAULT 0;
DECLARE v_para_name VARCHAR(50) DEFAULT '';
DECLARE v_sql VARCHAR(1024) DEFAULT '';
DECLARE v_default_value VARCHAR(500) DEFAULT '';
DECLARE v_id_code VARCHAR(50);
DECLARE v_branch_selected TINYINT(1); -- 是否选择枝干节点：1 - 是，0 - 不是
DECLARE v_leaf_field VARCHAR(50); -- 叶子节点字段名称
DECLARE cur_cross CURSOR FOR
SELECT
dn_dy_qjcs.CSMC, dn_dy_qjcs.CSMRZ, dn_dy_qjcs.ZDSQL,dn_dy_qjcs.BMZD, dn_dy_qjcs.sfxzzgjd, dn_dy_qjcs.yzjdzd
FROM
dn_dy_qjcs
INNER JOIN
dn_dy_jsx_cs
ON
dn_dy_qjcs.CSMC = dn_dy_jsx_cs.CSMC AND dn_dy_qjcs.YYMC = dn_dy_jsx_cs.YYMC
WHERE
dn_dy_jsx_cs.YYMC = v_App AND dn_dy_jsx_cs.JSXMC = v_Ci
ORDER BY dn_dy_jsx_cs.CSSX;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET i_found = NULL;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_Err = '严重错误：无法打开交叉查询';
OPEN cur_cross;
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"打开交叉查询成功\"}');
END IF;
FETCH cur_cross INTO v_para_name,v_default_value,v_sql,v_id_code,v_branch_selected,v_leaf_field;
IF i_found IS NULL THEN
SET v_Err = 'Err:交叉查询没有检索到数据';
CLOSE cur_cross;
DROP TEMPORARY TABLE IF EXISTS t1;
ROLLBACK;
LEAVE LABEL1;
END IF;
WHILE (i_found IS NOT NULL) DO
-- FIX 修正只有默认值的情况
IF v_sql IS NULL AND v_default_value IS NOT NULL THEN
SET v_sql = CONCAT('SELECT \'',v_default_value,'\' res_value');
SET v_id_code = 'res_value';
END IF;
IF v_branch_selected = 0 AND v_leaf_field IS NOT NULL AND v_leaf_field != '' THEN
SET v_sql = CONCAT(
'SELECT\r\n',
v_id_code,
'\r\nFROM', '\r\n(' ,
v_sql, ') inner_' , v_id_code,
'\r\nWHERE\r\n',
v_leaf_field, ' = \'1\''
);
END IF;
IF v_from = '' THEN
SET v_from = CONCAT(
'(\r\nSELECT ',
v_id_code,
' FROM\r\n(',
v_sql,
') inner_',v_id_code,'\r\n) ',
v_para_name
);
SET v_where_result = CONCAT(
'CONCAT("',
v_para_name,
'=",'
'"\'",',v_para_name,',"\'"'
);
SET v_str_result = CONCAT(
'CONCAT(\r\n"',
v_para_name,
'=",\r\n',
v_para_name,',\r\n',
'";"'
);
ELSE
SET v_from = CONCAT(
v_from,
'\r\n,\r\n(\r\nSELECT ',
v_id_code,
' FROM\r\n(',
v_sql,
') inner_',
v_para_name,
'\r\n) ',
v_para_name
);
SET v_where_result = CONCAT(
v_where_result,
'," AND ",',
'"',
v_para_name,
'=",'
'"\'",',v_para_name,',"\'"'
);
SET v_str_result = CONCAT(
v_str_result,
',\r\n',
'"',
v_para_name,
'=",\r\n',
v_para_name,',\r\n',
'";"'
);
END IF;
FETCH cur_cross INTO v_para_name,v_default_value,v_sql,v_id_code,v_branch_selected,v_leaf_field;
END WHILE;
CLOSE cur_cross;
END;
-- 错误检查
BEGIN
IF v_Err <> '' THEN
DROP TEMPORARY TABLE IF EXISTS t1;
ROLLBACK;
LEAVE LABEL1;
END IF;
END;
-- 2.4 插入数据至t1临时表
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_Err = '严重错误：把交叉查询结果插入到临时表t1失败';
SET v_where_result = CONCAT(v_where_result,'\r\n)');
SET v_str_result = CONCAT(v_str_result,'\r\n)');
SET @t1_into = CONCAT(
'INSERT INTO t1 (',
v_t1_fields,
')\r\n SELECT * FROM \r\n',
v_from
);
PREPARE pr1_1 FROM @t1_into;
EXECUTE pr1_1;
DROP PREPARE pr1_1;
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"t1表的作用是进行多个参数组合。插入t1表成功,sql:',replace(@t1_into,"\"","'"),'\"}');
END IF;
END;
-- 错误检查
BEGIN
IF v_Err <> '' THEN
SET v_Err = CONCAT(v_Err,'\r\nsql is:\r\n',@t1_into);
DROP TEMPORARY TABLE IF EXISTS t1;
ROLLBACK;
LEAVE LABEL1;
END IF;
END;
-- 2.5 创建和初始化临时表t2
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_Err = '严重错误：创建并设置临时表t2内容失败';
CREATE TEMPORARY TABLE t2 (for_where VARCHAR(1024),for_Str VARCHAR(1024));
SET @t2_create = CONCAT('INSERT INTO t2(for_where,for_str)\r\nSELECT\r\n',v_where_result,' for_where,\r\n',v_str_result,' for_str\r\nFROM t1');
PREPARE pr2 FROM @t2_create;
EXECUTE pr2;
DROP PREPARE pr2;
DROP TEMPORARY TABLE IF EXISTS t1;
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,
',{\"step\":\"t2表的作用是存储多个参数组合对应的sql where条件和组合串。创建临时表t2并插入数据成功,sql:',replace(@t2_create,"\"","'"),'\"}');
END IF;
END;
-- 错误检查
BEGIN
IF v_Err <> '' THEN
DROP TEMPORARY TABLE IF EXISTS t2;
DROP TEMPORARY TABLE IF EXISTS t1;
ROLLBACK;
LEAVE LABEL1;
END IF;
END;
-- ---------------------------------------
-- 3、根据t2进行轮询，维护参数组合表
-- ---------------------------------------
BEGIN
DECLARE i_count_para INT(2) DEFAULT 0;
DECLARE i_found TINYINT(1) DEFAULT 0;
DECLARE i_index INT(2);
DECLARE cur_t2 CURSOR FOR SELECT * FROM t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET i_found = NULL;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_Err = '严重错误：打开临时表t2失败';
OPEN cur_t2;
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"打开t2表成功，开始针对t2的记录进行轮询，轮询次数代表参数的组合数量（日志仅记录第1个参数组合）...\"}');
END IF;
FETCH cur_t2 INTO v_where_result,v_str_result;
IF i_found IS NULL THEN
SET v_Err = 'p_dn_ci_compute Err:open t2 is succeed,but not data!';
CLOSE cur_t2;
DROP TEMPORARY TABLE IF EXISTS t2;
ROLLBACK;
LEAVE LABEL1;
END IF;
WHILE i_found IS NOT NULL DO
SET i_count_para = i_count_para+1;
-- 3.4.1 插入不存在的参数组合
SELECT COUNT(*) INTO i_index FROM dn_cc_jsx_cs_zh WHERE YYMC = v_App AND JSXMC = v_CI AND CSZHNR = v_str_result;
-- PROBLEM 参数组合只增不减会不会造成参数组合表的爆炸呢？
IF i_index = 0 THEN
INSERT INTO dn_cc_jsx_cs_zh(GUID,YYMC,JSXMC,CSZHNR,CSZHTJ) VALUES (UUID(),v_App,v_Ci,v_str_result,v_where_result);
END IF;
FETCH cur_t2 INTO v_where_result,v_str_result;
END WHILE ;
CLOSE cur_t2;
DROP TEMPORARY TABLE IF EXISTS t2;
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"t2表轮询成功结束，共检查和维护了',i_count_para,'个参数组合\"}');
END IF;
END;
-- 错误检查
BEGIN
IF v_Err <> '' THEN
DROP TEMPORARY TABLE IF EXISTS t2;
ROLLBACK;
LEAVE LABEL1;
END IF;
END;
-- ---------------------------------------
-- 4、按字段轮询，每个字段基于参数组合表进行轮询
-- ---------------------------------------
BEGIN
DECLARE i_found_field TINYINT(1) DEFAULT 0;
DECLARE v_field_name VARCHAR(50) DEFAULT '';
DECLARE v_execute_type TINYINT(1) DEFAULT 0;
DECLARE v_procedure_type TINYINT(1) DEFAULT 0;
DECLARE v_sql VARCHAR(1024);
DECLARE cur_field Cursor FOR
SELECT ZDMC,ZXLX,JSSQL,LXLX FROM dn_dy_jsx_zd
WHERE YYMC = v_App AND JSXMC = v_CI AND ( v_CF <> '' AND ZDMC = v_CF OR v_CF = '' );
DECLARE CONTINUE HANDLER FOR NOT FOUND SET i_found_field= NULL;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_Err = '严重错误：打开字段表失败';
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"开始字段轮询计算...\"}');
END IF;
OPEN cur_field;
FETCH cur_field INTO v_field_name,v_execute_type,v_sql,v_procedure_type;
IF i_found_field IS NULL THEN
SET v_Err = '严重错误：检索不到计算项的字段';
CLOSE cur_field;
ROLLBACK;
LEAVE LABEL1;
END IF;
SET i_Count_Field = 0;
WHILE i_found_field IS NOT NULL DO
IF v_sql = '' OR v_sql IS NULL THEN
SET v_Err = CONCAT('严重错误：找不到',v_CI,'.',v_field_name,'对应的SQL或存储过程定义信息');
CLOSE cur_field;
ROLLBACK;
LEAVE LABEL1;
END IF;
SET i_Count_Field = i_Count_Field + 1;
IF v_execute_type = 2 THEN
-- -----------------------------
-- 如果计算形式是一个存储过程
-- -----------------------------
IF v_procedure_type = 1 THEN
-- ----------------------------------
-- 如果需要由本文件进行轮询参数组合
-- ----------------------------------
BEGIN
DECLARE v_guid VARCHAR(50) DEFAULT '';
DECLARE v_where VARCHAR(500) DEFAULT '';
DECLARE v_group VARCHAR(500) DEFAULT '';
DECLARE i_found_para TINYINT(1) DEFAULT 0;
DECLARE v_ok INT(1) DEFAULT 0;
DECLARE cur_para Cursor FOR
SELECT GUID,CSZHTJ,CSZHNR FROM dn_cc_jsx_cs_zh
WHERE YYMC = v_App AND JSXMC = v_CI;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET i_found_para = NULL;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_Err = '严重错误：打开参数组合表失败';
SET @sum_sql = CONCAT(
'call ',
v_sql,'(',
'"',v_App,'",',
'"',v_CI,'",',
'"',v_field_name,'",?,?)'
);
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"尝试为 [',v_CI,'.',v_field_name,'] 进行参数组合轮询并逐个启动存储过程：',replace(@sum_sql,"\"","'"),'\"}');
END IF;
OPEN cur_para;
FETCH cur_para INTO v_guid,v_where,v_group;
WHILE i_found_para IS NOT NULL DO
SET v_ok = DATANEW_FIND_UNUSE_P_COMBINE(v_group,v_App,v_CI);
IF v_ok = 1 THEN
SET @v_guid = v_guid;
SET @v_where = v_where;
PREPARE pr FROM @sum_sql;
EXECUTE pr USING @v_guid,@v_where;
drop PREPARE pr;
END IF;
FETCH cur_para INTO v_guid,v_where,v_group;
END WHILE;
CLOSE cur_para;
END;
ELSE
-- ----------------------------------
-- 如果由该存储过程自行进行轮询参数组合
-- ----------------------------------
SET @sum_sql = CONCAT(
'call ',
v_sql,'(',
'"',v_App,'",',
'"',v_CI,'",',
'"',v_field_name,'"',
')'
);
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"',i_Count_Field,' 为 [',v_CI,'.',v_field_name,'] 启动自身具备参数轮询能力的存储过程：',replace(@sum_sql,"\"","'"),'\"}');
END IF;
PREPARE pr FROM @sum_sql;
EXECUTE pr;
DROP PREPARE pr;
END IF;
ELSE
-- -----------------------------
-- 如果计算形式是一个SQL
-- -----------------------------
BEGIN
DECLARE v_guid VARCHAR(50) DEFAULT '';
DECLARE v_where VARCHAR(500) DEFAULT '';
DECLARE v_group VARCHAR(500) DEFAULT '';
DECLARE v_ok INT(1) DEFAULT 0;
DECLARE i_found_para TINYINT(1) DEFAULT 0;
DECLARE cur_para Cursor FOR
SELECT GUID,CSZHTJ,CSZHNR FROM dn_cc_jsx_cs_zh
WHERE YYMC = v_App AND JSXMC = v_CI;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET i_found_para = NULL;
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_Err = '严重错误：打开参数组合表或插入结果值失败';
OPEN cur_para;
FETCH cur_para INTO v_guid,v_where,v_group;
WHILE i_found_para IS NOT NULL DO
SET v_ok = DATANEW_FIND_UNUSE_P_COMBINE(v_group,v_App,v_CI);
IF v_ok = 1 THEN
SET @sum_sql =
CONCAT(
'INSERT INTO dn_cc_jsx_zd_jg(GUID,\r\nCSZH_GUID,\r\nZDMC,\r\nZDJG)\r\nVALUES(\r\n',
'"',UUID(),'"',
',\r\n',
'"',v_guid,'",\r\n',
'"',v_field_name,'"',
',\r\n',
'(\r\nSELECT SUM(',v_field_name,') FROM\r\n',
'(\r\n',v_sql,') sub1',
'\r\nWHERE\r\n',v_where,
'\r\n)\r\n)'
);
PREPARE pr FROM @sum_sql;
EXECUTE pr;
DROP PREPARE pr;
END IF;
FETCH cur_para INTO v_guid,v_where,v_group;
END WHILE;
CLOSE cur_para;
END;
END IF;
FETCH cur_field INTO v_field_name,v_execute_type,v_sql,v_procedure_type;
END WHILE;
CLOSE cur_field;
END;
-- 错误检查
BEGIN
IF v_Err <> '' THEN
-- 发生异常时，记录 SQL
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"尝试为',v_CI,'.',v_field_name,'启动SQL：',replace(@sum_sql,"\"","'"),'\"}');
END IF;
SET v_Err = CONCAT(v_Err,'\r\ndetaile sql is:\r\n',@sum_sql);
ROLLBACK;
LEAVE LABEL1;
END IF;
END;
COMMIT;
IF i_debug = 1 THEN
SET v_Log = CONCAT(v_Log,',{\"step\":\"成功递交事务\"}');
END IF;
END作者：敏 创建时间：2024-09-14 15:42
最后编辑：敏 更新时间：2025-03-07 10:05
最后编辑：敏 更新时间：2025-03-07 10:05
扫码关注网盛数新公众号，获取更多帮助资料
上一篇：无
