345 lines
11 KiB
Plaintext
345 lines
11 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE OR REPLACE PROCEDURE orm_api_kernel_v2(
|
|
IN p_tablename VARCHAR(100),
|
|
IN p_pk VARCHAR(100), -- primary key(for update delete)
|
|
IN p_action VARCHAR(20), -- CRUD
|
|
IN p_uuid VARCHAR(100),
|
|
IN p_json_id INT
|
|
)
|
|
/*
|
|
Function : 通用 orm-api 根据传入参数及设定，智能生成相应SQL
|
|
Description: 模仿 ORM 设定，让前端程序只需传入参数就能执行增删查改基本功能
|
|
Build Date : 2024-03-20
|
|
|
|
call orm_api_kernel_v2('pms_sprint','sprintid','U','uuid-20250803_2',0);
|
|
|
|
Modify History
|
|
Item Who Date Modify Docs
|
|
==== ========== ========== ================================================
|
|
1 Developer 2024/03/20 从 MS-SQL v2 版本转换为 MariaDB 版本并优化
|
|
*/
|
|
BEGIN
|
|
DECLARE v_field_identity VARCHAR(100);
|
|
DECLARE v_fields TEXT;
|
|
DECLARE v_datas TEXT;
|
|
DECLARE v_updatedatas TEXT;
|
|
DECLARE v_pkwhere TEXT;
|
|
DECLARE v_sql LONGTEXT;
|
|
DECLARE v_pk_cnt INT;
|
|
DECLARE v_pk_no INT;
|
|
DECLARE v_max_no VARCHAR(20);
|
|
DECLARE v_bill_uuid VARCHAR(100);
|
|
DECLARE v_bill_head VARCHAR(20);
|
|
DECLARE v_middlefield VARCHAR(100);
|
|
DECLARE v_dest_table VARCHAR(100);
|
|
DECLARE v_pk_id VARCHAR(50);
|
|
DECLARE v_fieldname VARCHAR(100);
|
|
DECLARE v_contents LONGTEXT;
|
|
DECLARE v_fieldtype VARCHAR(20);
|
|
DECLARE v_quot CHAR(1);
|
|
DECLARE v_cnt INT;
|
|
DECLARE v_cnt2 INT;
|
|
DECLARE v_maxcount INT;
|
|
DECLARE v_currid INT;
|
|
DECLARE v_nextrowid INT;
|
|
DECLARE v_crlf VARCHAR(2);
|
|
|
|
-- 创建临时表存储JSON数据
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS temp_jsondata (
|
|
parent_id INT,
|
|
json_key VARCHAR(100),
|
|
json_value LONGTEXT,
|
|
ValueType VARCHAR(20),
|
|
rowid INT AUTO_INCREMENT PRIMARY KEY
|
|
);
|
|
|
|
-- 临时表用于同步删除
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS temp_sync_del (
|
|
dest_table VARCHAR(100),
|
|
pk_id VARCHAR(50),
|
|
rowid INT AUTO_INCREMENT PRIMARY KEY
|
|
);
|
|
|
|
-- 临时表用于避免重复字段名
|
|
CREATE TEMPORARY TABLE IF NOT EXISTS temp_comp_json (
|
|
fieldname VARCHAR(100)
|
|
);
|
|
|
|
-- 清空临时表
|
|
TRUNCATE TABLE temp_jsondata;
|
|
TRUNCATE TABLE temp_sync_del;
|
|
TRUNCATE TABLE temp_comp_json;
|
|
|
|
-- 将传入参数转为JSON数据(多筆 Grid 資料寫入)
|
|
IF p_json_id = 1 then
|
|
INSERT INTO temp_jsondata (parent_id, json_key, json_value, ValueType)
|
|
SELECT parent_id, json_key, json_value, ValueType
|
|
FROM orm_json_tmp
|
|
WHERE uuid = p_uuid;
|
|
else
|
|
INSERT INTO temp_jsondata (parent_id, json_key, json_value, ValueType)
|
|
SELECT parent_id, json_key, json_value, ValueType
|
|
FROM orm_json_tmp
|
|
WHERE uuid = p_uuid AND parent_id = FLOOR(p_json_id / 1000) and object_id = p_json_id - FLOOR(p_json_id / 1000)*1000;
|
|
end if;
|
|
|
|
-- 获取自增字段
|
|
SELECT COLUMN_NAME INTO v_field_identity
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME = p_tablename
|
|
AND EXTRA = 'auto_increment'
|
|
LIMIT 1;
|
|
|
|
-- 初始化变量
|
|
SET v_fields = '';
|
|
SET v_datas = '';
|
|
SET v_pkwhere = '';
|
|
SET v_updatedatas = '';
|
|
SET v_pk_cnt = 0;
|
|
SET v_bill_uuid = '';
|
|
SET v_bill_head = '';
|
|
SET v_crlf = CONCAT(CHAR(13), CHAR(10));
|
|
|
|
-- 获取记录数
|
|
SELECT COUNT(*) INTO v_maxcount FROM temp_jsondata;
|
|
SELECT MIN(rowid) INTO v_currid FROM temp_jsondata;
|
|
|
|
-- 遍历JSON数据构建SQL
|
|
WHILE v_currid <= v_maxcount DO
|
|
-- 获取当前记录
|
|
SELECT json_key, IFNULL(json_value, ''), ValueType
|
|
INTO v_fieldname, v_contents, v_fieldtype
|
|
FROM temp_jsondata
|
|
WHERE rowid = v_currid;
|
|
|
|
-- 设置引号
|
|
IF lower(v_fieldtype) = 'string' THEN
|
|
SET v_quot = '''';
|
|
ELSE
|
|
SET v_quot = '';
|
|
END IF;
|
|
|
|
-- 记录billno和uuid用于MD更新
|
|
IF v_fieldname = 'uuid' THEN
|
|
SET v_bill_uuid = v_contents;
|
|
END IF;
|
|
IF v_fieldname = 'billno' THEN
|
|
SET v_bill_head = v_contents;
|
|
END IF;
|
|
|
|
-- 检查字段是否存在
|
|
SELECT COUNT(*) INTO v_cnt
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME = p_tablename AND COLUMN_NAME = v_fieldname;
|
|
|
|
SELECT COUNT(*) INTO v_cnt2
|
|
FROM temp_comp_json
|
|
WHERE fieldname = v_fieldname;
|
|
|
|
-- 如果字段存在且未处理过
|
|
IF v_cnt > 0 AND v_cnt2 <= 0 THEN
|
|
-- 记录主键内容
|
|
SELECT COUNT(*) INTO v_pk_no
|
|
FROM (
|
|
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(p_pk, ',', numbers.n), ',', -1) AS col
|
|
FROM (
|
|
SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
|
|
SELECT 4 UNION ALL SELECT 5 -- 假设最多5个主键字段
|
|
) numbers
|
|
WHERE numbers.n <= LENGTH(p_pk) - LENGTH(REPLACE(p_pk, ',', '')) + 1
|
|
) AS split_pk
|
|
WHERE col = v_fieldname;
|
|
|
|
IF v_pk_no > 0 THEN
|
|
IF v_pk_cnt >= 1 THEN
|
|
UPDATE sys_transaction SET para02 = v_contents WHERE tablename = p_tablename;
|
|
ELSE
|
|
UPDATE sys_transaction SET para01 = v_contents WHERE tablename = p_tablename;
|
|
END IF;
|
|
SET v_pk_cnt = v_pk_cnt + 1;
|
|
END IF;
|
|
|
|
-- 非自增字段处理
|
|
IF IFNULL(v_field_identity, '') <> v_fieldname THEN
|
|
SET v_fields = CONCAT(v_fields, v_fieldname, ',');
|
|
|
|
-- 构建INSERT值
|
|
IF p_action = 'C' OR p_action = 'Insert' THEN
|
|
SET v_datas = CONCAT(v_datas, v_quot, v_contents, v_quot, ',');
|
|
END IF;
|
|
END IF;
|
|
|
|
-- 构建UPDATE语句
|
|
IF (p_action = 'U' OR p_action = 'Update') AND v_pk_no = 0 THEN
|
|
-- 处理日期格式
|
|
IF SUBSTRING(v_contents, 11, 19) = 'T08:00:00.000+08:00' THEN
|
|
SET v_contents = SUBSTRING(v_contents, 1, 10);
|
|
END IF;
|
|
|
|
SET v_updatedatas = CONCAT(v_updatedatas, v_fieldname, ' = ', v_quot, v_contents, v_quot, ',');
|
|
END IF;
|
|
|
|
-- 构建WHERE条件
|
|
IF v_pk_no > 0 THEN
|
|
SET v_pkwhere = CONCAT(v_pkwhere, v_fieldname, ' = ', v_quot, v_contents, v_quot, ' AND ');
|
|
END IF;
|
|
END IF;
|
|
|
|
-- 记录已处理字段
|
|
INSERT INTO temp_comp_json VALUES (v_fieldname);
|
|
|
|
-- 获取下一行
|
|
SELECT MIN(rowid) INTO v_nextrowid
|
|
FROM temp_jsondata
|
|
WHERE rowid > v_currid;
|
|
SET v_currid = v_nextrowid;
|
|
END WHILE;
|
|
|
|
-- 处理没有主键的情况
|
|
IF v_pk_cnt = 0 THEN
|
|
SELECT middlefield INTO v_middlefield
|
|
FROM sys_transaction
|
|
WHERE tablename = p_tablename
|
|
LIMIT 1;
|
|
|
|
SELECT json_value INTO v_contents
|
|
FROM temp_jsondata
|
|
WHERE json_key = v_middlefield
|
|
LIMIT 1;
|
|
|
|
IF LENGTH(v_contents) <= 1000 THEN
|
|
UPDATE sys_transaction SET para01 = v_contents WHERE tablename = p_tablename;
|
|
END IF;
|
|
END IF;
|
|
|
|
-- 处理自增主键情况
|
|
IF (p_action = 'C' OR p_action = 'Insert') AND (p_pk = v_field_identity) THEN
|
|
UPDATE sys_transaction SET para01 = 'identity' WHERE tablename = p_tablename;
|
|
END IF;
|
|
|
|
-- 移除末尾的逗号或AND
|
|
IF v_fields <> '' THEN
|
|
-- SET v_fields = SUBSTRING(v_fields, 1, LENGTH(v_fields) - 1);
|
|
SET v_fields = TRIM(TRAILING ',' FROM v_fields);
|
|
END IF;
|
|
|
|
IF v_datas <> '' THEN
|
|
-- SET v_datas = SUBSTRING(v_datas, 1, LENGTH(v_datas) - 1);
|
|
SET v_datas = TRIM(TRAILING ',' FROM v_datas);
|
|
END IF;
|
|
|
|
IF v_updatedatas <> '' THEN
|
|
-- SET v_updatedatas = SUBSTRING(v_updatedatas, 1, LENGTH(v_updatedatas) - 1);
|
|
SET v_updatedatas = TRIM(TRAILING ',' FROM v_updatedatas);
|
|
END IF;
|
|
|
|
IF v_pkwhere <> '' THEN
|
|
SET v_pkwhere = SUBSTRING(v_pkwhere, 1, LENGTH(v_pkwhere) - 5);
|
|
END IF;
|
|
|
|
-- 构建基础SQL
|
|
IF p_action = 'C' OR p_action = 'Insert' THEN
|
|
SET v_sql = CONCAT('INSERT INTO ', p_tablename, ' (', v_fields, ') VALUES (', v_datas, ')');
|
|
|
|
-- 加入 M-D 的 D 同步 billno update
|
|
INSERT INTO temp_sync_del (dest_table, pk_id)
|
|
SELECT dest_table, pk_id FROM sys_sync_delete_detail WHERE tablename = p_tablename;
|
|
|
|
-- 处理同步更新
|
|
SELECT COUNT(*) INTO v_maxcount FROM temp_sync_del;
|
|
SELECT MIN(rowid) INTO v_currid FROM temp_sync_del;
|
|
|
|
WHILE v_currid <= v_maxcount DO
|
|
SELECT dest_table, pk_id INTO v_dest_table, v_pk_id
|
|
FROM temp_sync_del
|
|
WHERE rowid = v_currid;
|
|
|
|
-- 检查目标表是否有uuid字段
|
|
SELECT COUNT(*) INTO v_cnt
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_NAME = v_dest_table AND COLUMN_NAME = 'uuid';
|
|
|
|
IF v_cnt > 0 THEN
|
|
SET v_sql = CONCAT(v_sql, ';\n',
|
|
'UPDATE ', v_dest_table, ' SET ', IFNULL(v_pk_id, 'billno'), ' = ''',
|
|
v_bill_head, ''' WHERE uuid = ''', v_bill_uuid, '''');
|
|
END IF;
|
|
|
|
-- 获取下一行
|
|
SELECT MIN(rowid) INTO v_nextrowid
|
|
FROM temp_sync_del
|
|
WHERE rowid > v_currid;
|
|
SET v_currid = v_nextrowid;
|
|
END WHILE;
|
|
|
|
ELSEIF p_action = 'U' OR p_action = 'Update' THEN
|
|
SET v_sql = CONCAT('UPDATE ', p_tablename, ' SET ', v_updatedatas, ' WHERE ', v_pkwhere);
|
|
|
|
ELSEIF p_action = 'D' OR p_action = 'Delete' THEN
|
|
-- 基础删除语句
|
|
SET v_sql = CONCAT('DELETE FROM ', p_tablename, ' WHERE ', v_pkwhere);
|
|
|
|
-- 处理同步删除
|
|
INSERT INTO temp_sync_del (dest_table, pk_id)
|
|
SELECT dest_table, pk_id FROM sys_sync_delete_detail WHERE tablename = p_tablename;
|
|
|
|
SELECT COUNT(*) INTO v_maxcount FROM temp_sync_del;
|
|
SELECT MIN(rowid) INTO v_currid FROM temp_sync_del;
|
|
|
|
WHILE v_currid <= v_maxcount DO
|
|
SELECT dest_table, pk_id INTO v_dest_table, v_pk_id
|
|
FROM temp_sync_del
|
|
WHERE rowid = v_currid;
|
|
|
|
-- 解决 M-D 的 pk 栏名不同的问题
|
|
IF IFNULL(v_pk_id, '') = '' THEN
|
|
SET v_sql = CONCAT(v_sql, ';', v_crlf,
|
|
'DELETE FROM ', v_dest_table, ' WHERE ', v_pkwhere, ';');
|
|
ELSE
|
|
SET v_sql = CONCAT(v_sql, ';', v_crlf,
|
|
'DELETE FROM ', v_dest_table, ' WHERE ',
|
|
REPLACE(v_pkwhere, p_pk, v_pk_id), ';');
|
|
END IF;
|
|
|
|
-- 获取下一行
|
|
SELECT MIN(rowid) INTO v_nextrowid
|
|
FROM temp_sync_del
|
|
WHERE rowid > v_currid;
|
|
SET v_currid = v_nextrowid;
|
|
END WHILE;
|
|
END IF;
|
|
|
|
-- 处理日期格式问题
|
|
IF INSTR(v_sql, '000+08:00') > 0 THEN
|
|
SET v_sql = REPLACE(v_sql, '000+08:00', '000Z');
|
|
END IF;
|
|
|
|
-- 执行SQL
|
|
/* Mariadb 不支援動態語句多 sql, 只能切割
|
|
GET DIAGNOSTICS CONDITION 1
|
|
@sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
|
|
|
|
SELECT 1 AS code, CONCAT('Error: ', @errno, ' (', @sqlstate, '): ', @text) AS msg, 0 AS recno;
|
|
SELECT v_sql AS error_sql;
|
|
END;
|
|
|
|
-- 返回成功结果
|
|
SELECT 0 AS code, 'ok' AS msg, 0 AS recno;
|
|
|
|
-- 执行SQL
|
|
SET @dynamic_sql = v_sql;
|
|
PREPARE stmt FROM @dynamic_sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
END;
|
|
*/
|
|
call execute_multiple_sql(v_sql);
|
|
|
|
-- 清理临时表
|
|
DROP TEMPORARY TABLE IF EXISTS temp_jsondata;
|
|
DROP TEMPORARY TABLE IF EXISTS temp_sync_del;
|
|
DROP TEMPORARY TABLE IF EXISTS temp_comp_json;
|
|
END //
|
|
|
|
DELIMITER ; |