如何在Oracle PLSQL和SQL中执行字符串插值.给定一个带有命名占位符的文本模板，用变量替换这些占位符

Question

问题：Oracle如何使用PLSQL执行字符串插值？给定一个模板，该模板的命名参数包含在大括号中，然后用命名参数替换变量。功能类似于C#字符串插值、Java等语言。应该针对性能对代码进行优化，尽管大多数代码很少使用。

大括号是必需的，因为模板是业务用户定义的，并保存在表中。这是公司使用的标准惯例。目标是"；邮件合并"；Oracle本身中的业务文档和片段。

实现：最好打包同时在PLSQL和SQL中操作的函数。对于SQL，将在示例'name, jane, age, 26'中传递名称-值对的csv列表。name, value, name, value等…

示例用例：

请注意，使用双大括号作为文字"｛"的转义符。模板将存储在数据库表中，替换变量将在运行时基于复杂规则派生。下面的示例很简单：

template='Hi my name is {name}, I am aged {age}, yes {age}! Replaced placeholders {{name}}, {{age}}'

变量=name: jane, age: 26

输出=Hi my name is Jane, I am aged 26, yes 26! Replaced placeholders {name} and {age}

调用虚包的PLSQL示例：

DECLARE v_template NVARCHAR2(500);
v_name NVARCHAR2(50) := 'Jane';
v_age NVARCHAR2(50) := '26';
v_output NVARCHAR2(1000); TYPE ty_dictionary IS TABLE OF NVARCHAR2(1000) INDEX BY VARCHAR2(200);
v_vars ty_dictionary;
BEGIN
-- note, escaped double curly braces {{ will output {,
v_template := 'Hi my name is {name}, I am aged {age}, yes {age}! Replaced placeholders {{name}}, {{age}}';
v_vars('name') := v_name;
v_vars('age') := v_age;
v_output:= pkg_interpolation.fn_format(v_template, v_vars);
-- should output:
-- Hi my name is Jane, I am aged 26, yes 26! Replaced placeholders {name} and {age}
dbms_output.put_line('output: ' || v_output);
END;

Answer 1

试试这样的东西(db<>fiddle)：

declare 
type vararg is table of varchar2 (96) index by varchar2 (32);

function format (template varchar2, args vararg) return varchar2 is
key varchar2 (32);
ret varchar2  (32767) := template;
pattern varchar2 (32) := '(^|[^{]){(w+)}([^}]|$)';
begin
<<substitute>> loop
key := regexp_substr  (ret, pattern, 1, 1, null, 2);
exit substitute when key is null;
ret := regexp_replace (ret, pattern, 
'1'||case when args.exists (key) then args (key) else '?'||key||'?' end||
'3', 1, 1);
end loop;
return replace (replace (ret, '{{','{'), '}}', '}');
end;
begin
dbms_output.put_line ('output: '||format (q'[
{name} said: Hi my name is {name}, I am aged {age}, yes {age}! 
Missing key {somekey}; Replaced placeholders {{name}}, {{age}}. Again I am {age}]',
vararg ('name' => 'Jane', 
'age'  => '26')));
end;
/

输出：
Jane说：嗨，我叫Jane，我今年26岁，是的，26岁
钥匙丢失？somekey？；已替换占位符｛name｝、｛age｝。我又是26

Answer 2

utl_lms.format_message()似乎可以完成您想要的大部分功能：

declare
v_template nvarchar2(500)  := 'Hi my name is %s, I am aged %d.';
v_name     nvarchar2(50)   := 'Jane';
v_age      nvarchar2(50)   := '26';
v_output   nvarchar2(1000) := utl_lms.format_message(v_template, v_name, v_age);
begin
dbms_output.put_line('output: ' || v_output);
end;

output: Hi my name is Jane, I am aged 26.

您可能可以编写一个包装器来提供所需的其余功能，例如传入值集合的能力。

Answer 3

如果安装了APEX，则可以使用wwv_flow_utilities.fast_replace_manyf函数。

它也可以作为一个过程使用：wwv_flow_utilities.fast_replace_many。

SELECT wwv_flow_utilities.fast_replace_manyf(
p_srcstr => 'Hi my name is {name}, I am aged {age}, yes {age}! Replaced placeholders {{name}}, {{age}}',
oldsub   => wwv_flow_t_varchar2('{{', '}}', '{name}', '{age}', CHR(0), CHR(1)),
newsub   => wwv_flow_t_varchar2(CHR(0), CHR(1), 'jane', '26', '{', '}')) newtext
FROM DUAL;
--Output: Hi my name is jane, I am aged 26, yes 26! Replaced placeholders {name}, {age}

我在替换字符串中添加了四个额外的值来处理大括号，因为这不是内置的，但如果源文本包括CHR(0)或CHR(1)，这将失败，并且可以通过选择更长的替换来轻松避免。