在 Oracle 过程中的字符串中调用函数调用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2919168/
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
Invoking a function call in a string in an Oracle Procedure
提问by DMS
I writing an application using Oracle 10g.
我使用 Oracle 10g 编写了一个应用程序。
I am currently facing this problem. I take in "filename" as parameter of type varchar2.
我目前正面临这个问题。我将“文件名”作为 varchar2 类型的参数。
A sample value that filename may contain is: 'TEST || to_char(sysdate, 'DDD')'.
文件名可能包含的示例值是：'TEST || to_char(sysdate, 'DDD')'。
In the procedure, I want to get the value of this file name as in TEST147. When i write:
在程序中，我想得到这个文件名的值，如 TEST147。当我写：
select filename
into ffilename
from dual;
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?
我得到值 ffilename = TEST || to_char(sysdate, 'DDD') 是有道理的。但是我怎样才能解决这个问题并调用字符串值中的函数呢？
Help appreciated. Thanks.
帮助表示赞赏。谢谢。
回答by Tony Andrews
The string value in your example is an invalid expression; it should be: 'TEST' || to_char(sysdate, 'DDD')
您示例中的字符串值是无效表达式；它应该是：'测试'|| to_char（系统日期，'DDD'）
To evaluate that you could do this:
要评估您是否可以这样做：
execute immediate 'begin :result := ' || filename || '; end;'
using out v_string;
v_string will then contain 'TEST147'.
然后 v_string 将包含“TEST147”。
回答by APC
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>
EDIT
编辑
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>
In fact, by avoiding the SELECT on DUAL it is probably better.
事实上，通过避免 DUAL 上的 SELECT 可能会更好。
