Oracle 'printf' 等价物
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1002818/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 'printf' equivalent
提问by Steven
Is there an equivalent or alternative to the following?
是否有以下内容的等效项或替代项？
SELECT mix_type || ' (' || mix_num || ')' as description
FROM acid_batch
WHERE mix_num < 10
Does Oracle have something like printf style formatting?
Oracle 有类似 printf 风格的格式吗？
SELECT printf("%s (%s)", mix_type, mix_num) as description,
FROM acid_batch
WHERE mix_num < 10
采纳答案by dpbradley
No there are no built-in Oracle functions that apply a formatting string in this fashion. Although it would be easy to write a custom function for this specific example, writing a PL/SQL-based implementation of printf would be challenging.
不，没有以这种方式应用格式化字符串的内置 Oracle 函数。尽管为此特定示例编写自定义函数很容易，但编写 printf 的基于 PL/SQL 的实现将具有挑战性。
If you have a frequent need for this, perhaps you could write an Oracle function that wraps a Java call for a richer string handling environment.
如果您经常需要这样做，也许您可以编写一个 Oracle 函数来包装 Java 调用以获得更丰富的字符串处理环境。
回答by René Nyffenegger
The closest standard approximation to printf for Oracle I can think of is utl_lms.format_message. However, it won't work in SQL statements, that is, this is ok:
我能想到的最接近于 Oracle 的 printf 的标准近似值是utl_lms.format_message。但是，它在 SQL 语句中不起作用，也就是说，这是可以的：
begin
dbms_output.put_line(
utl_lms.format_message('hello %s, the number is %d', 'world', 42)
);
end;
/
but this gives a ORA-00902: invalid datatypeerror:
但这给出了ORA-00902: invalid datatype错误：
select utl_lms.format_message('hello %s, the number is %d', 'world', 42)
from dual
回答by Jeffrey Kemp
Just another idea for you: I've found REPLACE to be useful for this kind of thing, especially when the template is complex:
给你的另一个想法：我发现 REPLACE 对这种事情很有用，尤其是当模板很复杂时：
SELECT REPLACE(REPLACE(
'%mix_type% (%mix_num%)' /*template*/
,'%mix_type%', mix_type)
,'%mix_num%' , mix_num ) as description,
FROM acid_batch
WHERE mix_num < 10
The only downside is you need to add as many REPLACE('s as there are variables to replace - but at least you only need to have one per variable, regardless of how many times it appears in the template.
唯一的缺点是您需要添加与REPLACE(要替换的变量一样多的's - 但至少每个变量只需要一个，无论它在模板中出现多少次。
(NOTE: There is no particular significance to using "%" as a delimiter, it's just a personal convention of mine - you might choose a different pattern, e.g. <mix_type>or [mix_type])
（注意：使用“%”作为分隔符没有特别的意义，这只是我的个人习惯——你可以选择不同的模式，例如<mix_type>或[mix_type]）
For this particular instance it looks like overkill, but in some cases it can make things much easier, e.g.:
对于这个特定的例子，它看起来有点矫枉过正，但在某些情况下，它可以让事情变得更容易，例如：
template := 'bla bla %a% %b% %a%';
output := REPLACE(REPLACE(template
,'%a%', some_complex_expression)
,'%b%', b);
Compare the above with:
将以上与：
output := 'bla bla ' || some_complex_expression || ' ' || b || ' ' || some_complex_expression;
回答by AntonLosev
I've made a simple template engine named ora_te (on GitHub)for Oracle SQL / PLSQL. With the help of it your goal can be achieved in the following ways:
我为 Oracle SQL / PLSQL制作了一个名为ora_te（在 GitHub 上）的简单模板引擎。借助它，您可以通过以下方式实现目标：
Noneffective implementation with multiple parsings of template string:
多次解析模板字符串的无效实现：
with acid_batch as (
select rownum as mix_type, rownum + 2 as mix_num
from all_objects
where rownum < 10
)
--
SELECT pk_te.substitute(' ()', ty_p( mix_type, mix_num ) ) as description
FROM acid_batch
WHERE mix_num < 10;
An effective implementation with one time compilation (parsing):
一次编译（解析）的有效实现：
with acid_batch as (
select rownum as mix_type, rownum + 2 as mix_num
from all_objects
where rownum < 10
),
--
o as (
select ty_te.compile_numbered( ' ()' ) te from dual
)
SELECT pk_te.substitute( o.te, ty_p( mix_type, mix_num ) ) as description
FROM acid_batch, o
WHERE mix_num < 10;
BTW it also supports named placeholders.
顺便说一句，它还支持命名占位符。
回答by EvilTeach
You can resolve it in the select.
您可以在选择中解决它。
SELECT mix_type || '(' || mix_num || ')' as description,
FROM acid_batch
WHERE mix_num < 10
you should also take a look at the functions
你还应该看看功能
as they give your a finer granularity on how you want the things represented.
因为它们可以让您更详细地了解您希望如何表示事物。
