42 lines
1.2 KiB
Plaintext
42 lines
1.2 KiB
Plaintext
DELIMITER //
|
|
CREATE OR REPLACE PROCEDURE sys_transfer_userroles2owned()
|
|
BEGIN
|
|
/*
|
|
function : 配合 amis 的 transfer 元件, 將userroles 寫入暫存在 owned 欄位
|
|
description: 本程式基本上只會很低頻率的使用, 因為程式的 SOP 是由 owned -> userrole
|
|
不會逆向執行
|
|
build date : 2021/10/22
|
|
|
|
modify history
|
|
item who date modify docs
|
|
==== ========== ========== ================================================
|
|
1 michael 2021-10-17 新增
|
|
2 Michael 2025/07/20 改寫為 MariaDB 並最佳化(ChatGPT+DeepSeek)
|
|
*/
|
|
DECLARE v_done INT DEFAULT FALSE;
|
|
DECLARE v_roleid VARCHAR(100);
|
|
DECLARE v_userids TEXT;
|
|
|
|
-- 宣告游標，按 roleid 分組取得所有 userid
|
|
DECLARE cur CURSOR FOR
|
|
SELECT roleid, GROUP_CONCAT(userid SEPARATOR ',')
|
|
FROM sys_userroles
|
|
GROUP BY roleid;
|
|
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
|
|
|
|
OPEN cur;
|
|
|
|
read_loop: LOOP
|
|
FETCH cur INTO v_roleid, v_userids;
|
|
IF v_done THEN
|
|
LEAVE read_loop;
|
|
END IF;
|
|
|
|
-- 更新 owned 欄位
|
|
UPDATE sys_roles SET owned = v_userids WHERE roleid = v_roleid;
|
|
END LOOP;
|
|
|
|
CLOSE cur;
|
|
END //
|
|
DELIMITER ; |