Oracle 'printf' 等效

Question

以下是否有等效或替代方法？

SELECT mix_type || ' (' || mix_num || ')' as description
  FROM acid_batch
 WHERE mix_num < 10

Oracle 有类似 printf 样式的格式吗？

SELECT printf("%s (%s)", mix_type, mix_num) as description,
  FROM acid_batch
 WHERE mix_num < 10

Answer 1

我能想到的最接近 Oracle 的 printf 的标准近似值是 utl_lms.format_message .但是在SQL语句中就不行了，也就是这样就ok了:

begin
  dbms_output.put_line(
    utl_lms.format_message('hello %s, the number is %d', 'world', 42)
  );
end;
/

但这会产生 ORA-00902: invalid datatype 错误:

select utl_lms.format_message('hello %s, the number is %d', 'world', 42)
  from dual