PL/SQL中如何传递格式化字符串至列并在包中调用？含错误表操作

一、插入带占位符的错误记录 #

先创建错误主表（未创建时执行）：

CREATE TABLE errorTabl (
    error_id INT PRIMARY KEY,
    error_desc VARCHAR(255) NOT NULL
);

插入带格式化占位符的错误描述，用{0}作为列名的占位位置：

INSERT INTO errorTabl (error_id, error_desc) VALUES (1, 'There is error in {0}');

注：如果需要同时替换多个变量（比如表名+列名），可以用{0}、{1}这类序号占位符扩展

二、查询时替换占位符生成最终描述 #

根据不同数据库语法，实现占位符替换：

1. Oracle（PL/SQL包实现） #

在PL/SQL包中借助UTL_FORMAT.FORMAT_MESSAGE完成占位符替换：

CREATE OR REPLACE PACKAGE error_pkg IS
    FUNCTION get_error(p_error_id INT, p_col_name VARCHAR2) RETURN VARCHAR2;
END error_pkg;
/

CREATE OR REPLACE PACKAGE BODY error_pkg IS
    FUNCTION get_error(p_error_id INT, p_col_name VARCHAR2) RETURN VARCHAR2 IS
        v_error_desc VARCHAR2(255);
        v_result VARCHAR2(255);
    BEGIN
        SELECT error_desc INTO v_error_desc FROM errorTabl WHERE error_id = p_error_id;
        v_result := UTL_FORMAT.FORMAT_MESSAGE(v_error_desc, p_col_name);
        RETURN p_error_id || ' ' || v_result;
    END get_error;
END error_pkg;
/

调用示例：

SELECT error_pkg.get_error(1, 'col1') FROM DUAL;

返回结果：1 There is error in col1

2. MySQL（存储过程实现） #

用REPLACE函数替换占位符，示例存储过程：

DELIMITER //
CREATE PROCEDURE get_error(IN p_error_id INT, IN p_col_name VARCHAR(50), OUT p_result VARCHAR(255))
BEGIN
    DECLARE v_error_desc VARCHAR(255);
    SELECT error_desc INTO v_error_desc FROM errorTabl WHERE error_id = p_error_id;
    SET v_error_desc = REPLACE(v_error_desc, '{0}', p_col_name);
    SET p_result = CONCAT(p_error_id, ' ', v_error_desc);
END //
DELIMITER ;

调用示例：

CALL get_error(1, 'col1', @result);
SELECT @result;

返回结果：1 There is error in col1

3. 直接SQL查询（无需存储过程） #

临时查询时可直接在SQL中完成替换：

-- MySQL
SELECT CONCAT(error_id, ' ', REPLACE(error_desc, '{0}', 'col1')) AS error_info
FROM errorTabl
WHERE error_id = 1;

-- Oracle
SELECT error_id || ' ' || UTL_FORMAT.FORMAT_MESSAGE(error_desc, 'col1') AS error_info
FROM errorTabl
WHERE error_id = 1;

内容的提问来源于stack exchange，提问作者Dipika