100 lines
3.6 KiB
Plaintext
100 lines
3.6 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE OR REPLACE PROCEDURE orm_exec_aftersave_v2(
|
|
IN p_tablename VARCHAR(100),
|
|
IN p_pk VARCHAR(100), -- primary key(for update delete)
|
|
IN p_action VARCHAR(10),
|
|
IN p_userid VARCHAR(100)
|
|
)
|
|
/*
|
|
功能: orm_api_v2 [存档后处理]的核心程序，智能构建相应SQL
|
|
描述: 用于保存后执行额外处理逻辑
|
|
创建日期: 2023/12/16
|
|
MariaDB转换版本
|
|
*/
|
|
BEGIN
|
|
DECLARE v_sp_name TEXT;
|
|
DECLARE v_para01 TEXT;
|
|
DECLARE v_para02 TEXT;
|
|
DECLARE v_sql_command TEXT;
|
|
DECLARE v_dest_table TEXT;
|
|
DECLARE v_dest_field TEXT;
|
|
DECLARE v_dest_field2 TEXT;
|
|
DECLARE v_RelationKey TEXT;
|
|
DECLARE v_filter_user_fieldname TEXT;
|
|
DECLARE v_max_id INT;
|
|
|
|
-- 取得 sys_transaction 信息
|
|
SELECT
|
|
sp_name,
|
|
para01,
|
|
para02,
|
|
dest_table,
|
|
dest_field,
|
|
dest_field2,
|
|
RelationKey,
|
|
filter_user_fieldname
|
|
INTO
|
|
v_sp_name,
|
|
v_para01,
|
|
v_para02,
|
|
v_dest_table,
|
|
v_dest_field,
|
|
v_dest_field2,
|
|
v_RelationKey,
|
|
v_filter_user_fieldname
|
|
FROM sys_transaction
|
|
WHERE tablename = p_tablename AND (actiontype = '*' OR LOCATE(p_action, actiontype) > 0)
|
|
LIMIT 1;
|
|
|
|
IF v_sp_name IS NOT NULL AND v_sp_name != '' THEN
|
|
-- 检查是否为特殊情况
|
|
IF (v_sp_name != 'sys_transfer_owned2rolemenusecurity') AND (v_sp_name != 'sys_transfer_owned2userroles') THEN
|
|
-- 如果 pk 是 identity
|
|
IF v_para01 = 'identity' THEN
|
|
-- 处理 identity 的情况
|
|
IF IFNULL(v_filter_user_fieldname, '') = '' THEN
|
|
SET @sql = CONCAT('SELECT MAX(', p_pk, ') INTO @max_id FROM ', p_tablename);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET v_sql_command = CONCAT('UPDATE sys_transaction SET para01 = ? WHERE tablename = ''', p_tablename, '''');
|
|
PREPARE stmt FROM v_sql_command;
|
|
EXECUTE stmt USING @max_id;
|
|
DEALLOCATE PREPARE stmt;
|
|
ELSE
|
|
SET @sql = CONCAT('SELECT MAX(', p_pk, ') INTO @max_id FROM ', p_tablename,
|
|
' WHERE ', v_filter_user_fieldname, ' = ''', p_userid, '''');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET v_sql_command = CONCAT('UPDATE sys_transaction SET para01 = ? WHERE tablename = ''', p_tablename, '''');
|
|
PREPARE stmt FROM v_sql_command;
|
|
EXECUTE stmt USING @max_id;
|
|
DEALLOCATE PREPARE stmt;
|
|
END IF;
|
|
END IF;
|
|
|
|
SET v_sql_command = CONCAT('CALL ', v_sp_name, '( ''', p_tablename, ''' )');
|
|
ELSE
|
|
-- 针对特殊情况特别改写 sp 的方案
|
|
IF IFNULL(v_para01, '') != '' AND IFNULL(v_para02, '') != '' THEN
|
|
SET v_sql_command = CONCAT('CALL ', v_sp_name, '( ''', v_para01, ''', ''', v_para02, ''' )');
|
|
ELSEIF IFNULL(v_para01, '') != '' AND IFNULL(v_para02, '') = '' THEN
|
|
SET v_sql_command = CONCAT('CALL ', v_sp_name, '( ''', v_para01, ''' )');
|
|
ELSE
|
|
SET v_sql_command = CONCAT('CALL ', v_sp_name, '()');
|
|
END IF;
|
|
END IF;
|
|
|
|
-- 执行 SQL
|
|
SET @dynamic_sql = v_sql_command;
|
|
PREPARE stmt FROM @dynamic_sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
END IF;
|
|
END //
|
|
|
|
DELIMITER ; |