52 lines
1.4 KiB
Plaintext
52 lines
1.4 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE OR REPLACE PROCEDURE get_keyvalue(
|
|
IN p_tablename VARCHAR(20),
|
|
IN p_fieldname VARCHAR(20)
|
|
)
|
|
/*
|
|
Function : 配合 amis 的 select 元件，取得 key-value 資料
|
|
Description: 從 zen_keyvalue 表獲取鍵值對資料，用於下拉選項
|
|
合併了原有的 zen_keyvalue 和 sys_keyvalue 表功能
|
|
Build Date : 2023/07/19 (MariaDB 版本)
|
|
|
|
Modify History
|
|
Item Who Date Modify Docs
|
|
==== ========== ========== ================================================
|
|
1 Michael 2022/05/28 初始版本
|
|
2 Michael 2025/07/20 改寫為 MariaDB 並最佳化(ChatGPT+DeepSeek)
|
|
*/
|
|
BEGIN
|
|
DECLARE v_count INT;
|
|
|
|
-- 設置默認返回值
|
|
SELECT 0 AS code, 'ok' AS msg;
|
|
|
|
-- 檢查指定表名和欄位名的記錄是否存在
|
|
SELECT COUNT(*) INTO v_count
|
|
FROM zen_keyvalue
|
|
WHERE tablename = p_tablename
|
|
AND fieldname = p_fieldname;
|
|
|
|
-- 根據查詢結果返回資料
|
|
IF v_count > 0 THEN
|
|
-- 返回指定表名和欄位名的鍵值對
|
|
SELECT
|
|
keyid AS `value`,
|
|
keyvalue AS `label`
|
|
FROM zen_keyvalue
|
|
WHERE tablename = p_tablename
|
|
AND fieldname = p_fieldname;
|
|
ELSE
|
|
-- 返回默認欄位名的鍵值對
|
|
SELECT
|
|
keyid AS `value`,
|
|
keyvalue AS `label`
|
|
FROM zen_keyvalue
|
|
WHERE tablename = 'xxxx'
|
|
AND fieldname = p_fieldname;
|
|
END IF;
|
|
END //
|
|
|
|
DELIMITER ;
|