函数大致思路select (length(str1) - length(replace(str1, '*'))) into num form dual 这样取出*号的个数 然后做个循环 i:=1 result:=to_number(substr(str1,1,instr(str1,'*',1)-1)) while i<num loop result:=result*to_number( substr(str1,instr(str1,'*',num)+1),instr(str1,'*',num+1)-instr(str1,'*',num)-1) i:=i+1 end loop; result:=result*to_number( substr(str1,instr(str1,'*',num)+1)) return result
function fun1(inexp varchar2) return number 2 as 3 v number; 4 begin 5 execute immediate 'select '||inexp||' from dual' into v; 6 return v; 7 end; 8 /
Function created.
SQL> select fun1('2+3+4') from dual;
FUN1('2+3+4') ------------- 9
晚了一步 没那么麻烦,用动态sql,拼接参数就行了
-- zhpsam109 正解CREATE OR REPLACE FUNCTION fun1 (inexp VARCHAR2) RETURN NUMBER IS ncount NUMBER; BEGIN EXECUTE IMMEDIATE 'select ' || inexp || ' from dual' INTO ncount; RETURN ncount; END fun1;
我提供一个最简单的算法。 可以解决任何合法的表达公式: SELECT 2*4*5*7 FROM DUAL; SELECT (23-12)*6-9/3 FROM DUAL;
如果不是固定的,写个函数,把字段传进去,然后程序把字符串拆分再相乘人,传出结果
这样取出*号的个数
然后做个循环
i:=1
result:=to_number(substr(str1,1,instr(str1,'*',1)-1))
while i<num loop
result:=result*to_number( substr(str1,instr(str1,'*',num)+1),instr(str1,'*',num+1)-instr(str1,'*',num)-1)
i:=i+1
end loop; result:=result*to_number( substr(str1,instr(str1,'*',num)+1))
return result
2 as
3 v number;
4 begin
5 execute immediate 'select '||inexp||' from dual' into v;
6 return v;
7 end;
8 /
Function created.
SQL> select fun1('2+3+4') from dual;
FUN1('2+3+4')
-------------
9
没那么麻烦,用动态sql,拼接参数就行了
RETURN NUMBER
IS
ncount NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select ' || inexp || ' from dual' INTO ncount;
RETURN ncount;
END fun1;
可以解决任何合法的表达公式:
SELECT 2*4*5*7 FROM DUAL;
SELECT (23-12)*6-9/3 FROM DUAL;