在Oracle过程中以字符串形式调用函数调用 [英] Invoking a function call in a string in an Oracle Procedure

问题描述

我使用Oracle 10g编写应用程序.

I writing an application using Oracle 10g.

我目前正面临这个问题.我将文件名"作为类型varchar2的参数.

I am currently facing this problem. I take in "filename" as parameter of type varchar2.

文件名可能包含的样本值是:'TEST || to_char(sysdate，'DDD')'.

A sample value that filename may contain is: 'TEST || to_char(sysdate, 'DDD')'.

在此过程中，我希望像TEST147一样获取此文件名的值. 当我写的时候:

In the procedure, I want to get the value of this file name as in TEST147. When i write:

select filename
into ffilename
from dual;

我得到值ffilename = TEST || to_char(sysdate，'DDD')鞭子是有道理的.但是，如何解决这个问题并在字符串值中调用函数?

I get the value ffilename = TEST || to_char(sysdate, 'DDD') whick makes sense. But how can I get around this issue and invoke the function in the string value?

帮助表示赞赏. 谢谢.

Help appreciated. Thanks.

推荐答案

动态执行字符串非常简单...

It's easy enough to dynamically execute a string ...

create or replace function fmt_fname (p_dyn_string in varchar2)
    return varchar2
is
    return_value varchar2(128);
begin
    execute immediate 'select '||p_dyn_string||' from dual'
        into return_value;
    return  return_value;
end fmt_fname;
/

问题出现在您的字符串包含文字以及引号令人恐惧的地方...

The problem arises where your string contains literals, with the dreaded quotes ...

SQL> select fmt_fname('TEST||to_char(sysdate, 'DDD')') from dual
  2  /
select fmt_fname('TEST||to_char(sysdate, 'DDD')') from dual
                                          *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL>

因此，我们必须转义所有的撇号，包括您​​未包含在发布的字符串中的撇号:

So we have to escape the apostrophes, all of them, including the ones you haven't included in your posted string:

SQL> select * from t34
  2  /

        ID FILENAME
---------- ------------------------------
         1 APC001
         2 XYZ213
         3 TEST147


SQL> select * from t34
  2  where filename = fmt_fname('''TEST''||to_char(sysdate, ''DDD'')')
  3  /

        ID FILENAME
---------- ------------------------------
         3 TEST147

SQL>

编辑

为了公平起见，我觉得我应该指出，托尼的解决方案同样有效:

Just for the sake of fairness I feel I should point out that Tony's solution works just as well:

SQL> create or replace function fmt_fname (p_dyn_string in varchar2)
  2      return varchar2
  3  is
  4      return_value varchar2(128);
  5  begin
  6      execute immediate 'begin :result := ' || p_dyn_string || '; end;'
  7          using out return_value;
  8      return  return_value;
  9  end;
 10  /

Function created.

SQL> select fmt_fname('''TEST''||to_char(sysdate, ''DDD'')') from dual
  2  /

FMT_FNAME('''TEST''||TO_CHAR(SYSDATE,''DDD'')')
--------------------------------------------------------------------------------
TEST147

SQL>

实际上，通过避免在DUAL上进行SELECT可能会更好.

In fact, by avoiding the SELECT on DUAL it is probably better.

这篇关于在Oracle过程中以字符串形式调用函数调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！