71 lines
2.7 KiB
Plaintext
71 lines
2.7 KiB
Plaintext
DELIMITER //
|
|
CREATE OR REPLACE PROCEDURE sys_transfer_comm(
|
|
IN p_para_tablename VARCHAR(100)
|
|
)
|
|
/*
|
|
function : 配合 amis 的 transfer 元件, 將暫存在 owned(or else) 欄位的值, 寫到指定的 table(透過設定)
|
|
description: 目前先配合 omi_api 的存檔後機制(sys_transaction) 執行, 希望能全部透過設定, 而不是每一個 table 都必須客製
|
|
copy from sys_transfer_owned2rolemenusecurity
|
|
build date : 2022/04/08
|
|
|
|
modify history
|
|
item who date modify docs
|
|
==== ========== ========== ================================================
|
|
1 michael 2022-4-9 測試
|
|
2 Michael 2025/07/20 改寫為 MariaDB 並最佳化(ChatGPT+DeepSeek)
|
|
*/
|
|
BEGIN
|
|
/*
|
|
Function: Transfer data from temporary fields to specified tables
|
|
Build Date: 2022/04/08
|
|
*/
|
|
DECLARE v_RelationKey VARCHAR(100);
|
|
DECLARE v_MiddleField VARCHAR(100);
|
|
DECLARE v_dest_table VARCHAR(100);
|
|
DECLARE v_dest_field VARCHAR(100);
|
|
DECLARE v_dest_field2 VARCHAR(100);
|
|
DECLARE v_dest_field2_value VARCHAR(100);
|
|
DECLARE v_sql LONGTEXT;
|
|
DECLARE v_para01 VARCHAR(100);
|
|
DECLARE v_para02 VARCHAR(100);
|
|
|
|
-- Read parameters from sys_transaction
|
|
SELECT
|
|
RelationKey, MiddleField, para01, para02,
|
|
dest_table, dest_field, dest_field2, dest_field2_value
|
|
INTO
|
|
v_RelationKey, v_MiddleField, v_para01, v_para02,
|
|
v_dest_table, v_dest_field, v_dest_field2, v_dest_field2_value
|
|
FROM sys_transaction WHERE tablename = p_para_tablename;
|
|
|
|
-- Build dynamic SQL
|
|
SET v_sql = CONCAT(
|
|
'DELETE FROM ', v_dest_table, ' WHERE ', v_RelationKey, ' = ''', v_para01, ''';'
|
|
);
|
|
|
|
IF v_dest_field2 IS NOT NULL AND v_dest_field2 != '' THEN
|
|
SET v_sql = CONCAT(v_sql,
|
|
' INSERT INTO ', v_dest_table, ' (', v_RelationKey, ',', v_dest_field, ',', v_dest_field2, ') ',
|
|
'SELECT t.', v_RelationKey, ', s.col, ''', v_dest_field2_value, ''' ',
|
|
'FROM ', p_para_tablename, ' t ',
|
|
'JOIN (SELECT col FROM STRING_SPLIT((SELECT ', v_MiddleField, ' FROM ', p_para_tablename,
|
|
' WHERE ', v_RelationKey, ' = ''', v_para01, '''), '','') WHERE col != '''') s ',
|
|
'WHERE t.', v_RelationKey, ' = ''', v_para01, ''';'
|
|
);
|
|
ELSE
|
|
SET v_sql = CONCAT(v_sql,
|
|
' INSERT INTO ', v_dest_table, ' (', v_RelationKey, ',', v_dest_field, ') ',
|
|
'SELECT t.', v_RelationKey, ', s.col ',
|
|
'FROM ', p_para_tablename, ' t ',
|
|
'JOIN (SELECT col FROM STRING_SPLIT((SELECT ', v_MiddleField, ' FROM ', p_para_tablename,
|
|
' WHERE ', v_RelationKey, ' = ''', v_para01, '''), '','') WHERE col != '''') s ',
|
|
'WHERE t.', v_RelationKey, ' = ''', v_para01, ''';'
|
|
);
|
|
END IF;
|
|
|
|
SET @sql = v_sql;
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
END //
|
|
DELIMITER ; |