oracle oracle中如何计算字符串的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1267025/
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
How to calculate value of string in oracle?
提问by APC
If I have a string say "3*2+24" how can calculate its value in Oracle? In sql server you can do exec ('select 3*2+24') and it returns 30
如果我有一个字符串说“3*2+24”，如何在 Oracle 中计算它的值？在 sql server 中，您可以执行 exec ('select 3*2+24') 并返回 30
Thanks
谢谢
回答by DCookie
Here's a little function to calculate arbitrary strings of arithmetic:
这是一个计算任意算术字符串的小函数：
SQL> CREATE OR REPLACE FUNCTION calc(pi_val VARCHAR2) RETURN NUMBER IS
2 v_return NUMBER;
3 BEGIN
4 EXECUTE IMMEDIATE 'select '||pi_val||' from dual' INTO v_return;
5 RETURN v_return;
6 END;
7 /
Function created
SQL> SELECT calc('2*6*10') FROM dual;
CALC('2*6*10')
--------------
120
SQL>
Obviously, for production purposes you'd need some error handling...
显然，出于生产目的，您需要一些错误处理......
回答by tuinstoel
You can use DCookie's solution.
您可以使用 DCookie 的解决方案。
You can decrease the possibility of an sql injection attack by creating a new user without privileges.
您可以通过创建一个没有权限的新用户来降低 sql 注入攻击的可能性。
Log in as system and create a new user without privileges:
以系统身份登录并创建一个没有权限的新用户：
create user new_user identified by password_new_user;
Create the function (as system) in schema new_user.
在模式 new_user 中创建函数（作为系统）。
create or replace function new_user.calc(pi_val varchar2) return number
is
v_return number;
begin
execute immediate 'select '||pi_val||' from dual' INTO v_return;
return v_return;
end;
/
Next grant execute privileges (as system) on function new_user.calc to the relevant Oracle users (for instance tuinstoel (that's me)).
接下来将函数 new_user.calc 的执行权限（作为系统）授予相关的 Oracle 用户（例如 tuinstoel（就是我））。
grant execute on new_user.calc to tuinstoel;
Log in as user tuinstoel.
以 tuinstoel 用户身份登录。
connect tuinstoel/cheese_and_cheese@ora11
SQL> select new_user.calc('2+3') from dual;
NEW_USER.CALC('2+3')
--------------------
5
Everyone who calls the function new_user.calchas the privileges of new_user(none) inside function new_user.calc, not the rights of the caller (definer's privileges not invoker's privileges).
每个调用函数的人都new_user.calc拥有new_user(none) 函数内部new_user.calc的权限，而不是调用者的权限（定义者的权限而不是调用者的权限）。
When you don't want to include new_user.in every call to calc do as tuinstoel:
当您不想new_user.在每次调用 calc 时都包含它时，请执行 tuinstoel：
create synonym calc for new_user.calc;
Next you can test this with:
接下来，您可以使用以下方法进行测试：
SQL> select calc('2+3') from dual;
CALC('2+3')
-----------
5
edit1:Others have contemplated about this solution and they have pointed out some (potential) problems: See http://forums.oracle.com/forums/thread.jspa?forumID=75&threadID=943576.
编辑 1：其他人已经考虑过这个解决方案，他们指出了一些（潜在的）问题：见http://forums.oracle.com/forums/thread.jspa?forumID=75&threadID=943576。
回答by northpole
In ORACLE you can do any computation you want using the dual feature. For example
在 ORACLE 中，您可以使用双重功能进行任何您想要的计算。例如
SELECT 3*3-(2+2) FROM DUAL
However, you cannot do
但是，您不能这样做
SELECT '3*3-(2+2)' FROM DUAL
Because it will just return the string of '3*3-(2+2)'. If all you have is a string, I would probably strip it out into a numeric function then do the calculation.
因为它只会返回 '3*3-(2+2)' 的字符串。如果你只有一个字符串，我可能会把它剥离成一个数字函数然后进行计算。
回答by Sergey Nasonov
You can use the xmlquery()function to implement it. Here is a sample code:
您可以使用该xmlquery()函数来实现它。这是一个示例代码：
select
xmlquery(
replace( 'your variable', '/', ' div ')
returning content
).getNumberVal()
from
dual;
select
xmlquery(
replace( 'your variable', '/', ' div ')
returning content
).getNumberVal()
from
dual;
For example the code below
例如下面的代码
SELECT
XMLQUERY(
REPLACE( '3/4', '/', ' div ')
RETURNING CONTENT
).getNumberVal() FROM DUAL;
returns 0.75
返回 0.75
回答by APC
It's a bit clunkier in Oracle but it can be done.
在 Oracle 中它有点笨拙，但可以做到。
SQL> set serveroutput on
SQL> declare
2 str varchar2(250) := '3*2+24';
3 n number;
4 begin
5 execute immediate 'select '||str||' from dual' into n;
6 dbms_output.put_line(str||' = '||to_char(n)||' !!');
7 end;
8 /
3*2+24 = 30 !!
PL/SQL procedure successfully completed.
SQL>
