本人刚学oracle,对oracle了解不深,其中一样是如何转换oracle字符串中的数字再进行数学运算,例如:
select replace('面积*2+6','面积',5) aa,'5*2+6' bb,5*2+6 cc from dual
aa及bb出来的结果都是字符串'5*2+6' ,本人想得到的是cc的结果16。请教各位如何将aa及bb字符串变为像cc一样是数字,再最终得到像cc一样的结果。
select replace('面积*2+6','面积',5) aa,'5*2+6' bb,5*2+6 cc from dual
aa及bb出来的结果都是字符串'5*2+6' ,本人想得到的是cc的结果16。请教各位如何将aa及bb字符串变为像cc一样是数字,再最终得到像cc一样的结果。
replace('面积*2+6','面积',5) aa
,TO_Number(substr(aa,1,1))*TO_Number(substr(aa,3,1))+TO_Number(substr(aa,5,1)) cc
from dual
to_number(substr(aa, 5, 1)) as "面积"
from (select replace('面积*2+6', '面积', 5) as aa from dual);
select code from t_productmanage;
得出结果
面积*2+6
面积*3+7-5
面积*7+6-2
面积*6+8-4
假如是这样一列,如何将这一列的面积换成数字,然后直接得出计算结果。
as
l_res number;
l_sql varchar2(1000);
begin
l_sql := 'select '||replace(p_expression, p_str, p_val)||' from dual';
execute immediate l_sql into l_res;
return l_res;
end;
/select get_value('面积*2+6','面积','3') from dual;select get_value(code,'面积','3') from t_productmanage;
declare nchichuc_ number:=240;
nchichuk_ number:=120;
nchichug_ number:=60;
pprice_ number:=4.22;
pexpressionsj_ varchar2(500):='((nchichug_+nchichuk_)*2+4)*(nchichug_*2+nchichuc_*2)*pprice_/10000';
temp number;
tempa varchar2(2000);
get_attr_ sys_refcursor;
begin
tempa:= ' select '||pexpressionsj_||' from dual';
DBMS_OUTPUT.PUT_LINE('tempa:'||tempa);
execute immediate tempa into temp; /* Open get_attr_ for tempa;
Fetch get_attr_ Into temp;
DBMS_OUTPUT.PUT_LINE('temp:'||temp);
Close get_attr_;*/
DBMS_OUTPUT.PUT_LINE('temp:'||temp);
end;错误提示是:ORA-00904:“pprice_”invalid identifier
ORA-06512:at line 12请教各位这段代码哪里出错了,应如何修改
已写入 file afiedt.buf 1 begin
2 declare
3 nchichuc_ number:=240;
4 nchichuk_ number:=120;
5 nchichug_ number:=60;
6 pprice number:=4.22;
7 pexpressionsj_ varchar2(500):='((:n1+:n2)*2+4)*(:n3*2+:n4*2)*:n5/10000';
8 temp number;
9 tempa varchar2(2000);
10 get_attr_ sys_refcursor;
11 begin
12 tempa:= ' select '||pexpressionsj_||' from dual';
13 DBMS_OUTPUT.PUT_LINE('tempa:'||tempa);
14 execute immediate tempa into temp using nchichug_,nchichuk_,nchichug_,nchichuc_,pprice;
15 DBMS_OUTPUT.PUT_LINE('temp:'||temp);
16 end;
17* end;
SQL> /
tempa: select ((:n1+:n2)*2+4)*(:n3*2+:n4*2)*:n5/10000 from dual
temp:92.1648PL/SQL 过程已成功完成。
nchichuc_ number:=240;
nchichuk_ number:=120;
nchichug_ number:=60;
pprice_ number:=4.22;
pexpressionsj_ varchar2(500):='((nchichug_+nchichuk_)*2+4)*(nchichug_*2+nchichuc_*2)*pprice_/10000';
temp number;
tempa varchar2(2000);
get_attr_ sys_refcursor;
begin
pexpressionsj_ := replace(pexpressionsj_ ,'nchichug_','60');
pexpressionsj_ := replace(pexpressionsj_ ,'nchichuk_','120');
pexpressionsj_ := replace(pexpressionsj_ ,'nchichuc_','240');
pexpressionsj_ := replace(pexpressionsj_ ,'pprice_','4.22');
tempa:= ' select '||pexpressionsj_||' from dual';
DBMS_OUTPUT.PUT_LINE('tempa:'||tempa);
execute immediate tempa into temp; /* Open get_attr_ for tempa;
Fetch get_attr_ Into temp;
DBMS_OUTPUT.PUT_LINE('temp:'||temp);
Close get_attr_;*/
DBMS_OUTPUT.PUT_LINE('temp:'||temp);
end;
Function Get_pexpressionsj(
nchichuc_ In number,
nchichuk_ In number,
nchichug_ In number,
pprice_ In number,
pexpressionsj In varchar2
)
Return number
Is
temp number;
tempa varchar2(2000);
Begin
tempa:='select replace('''||pexpressionsj||''',''nchichuc_'','''||nchichuc_||''') from dual';
execute immediate tempa into tempa; tempa:='select replace('''||tempa||''',''nchichuk_'','''||nchichuk_||''') from dual';
execute immediate tempa into tempa;
tempa:='select replace('''||tempa||''',''nchichug_'','''||nchichug_||''') from dual';
execute immediate tempa into tempa;
tempa:='select replace('''||tempa||''',''pprice_'','''||pprice_||''') from dual';
execute immediate tempa into tempa; tempa:= 'select '||tempa||' from dual';
execute immediate tempa into temp;
/* temp:=to_number(tempa);*/
Return temp;
End Get_pexpressionsj;
select cc "面积公式",
instr(cc, '*', 1, 1),
substr(cc, 1, instr(cc, '*', 1, 1) - 1),
cc,
substr(cc,
instr(cc, '*', 1, 1) + 1,
instr(cc, '+', 1, 1) - instr(cc, '*', 1, 1) - 1),
substr(cc, instr(cc, '+', 1, 1) + 1),
to_number(substr(cc, 1, instr(cc, '*', 1, 1) - 1)) *
(substr(cc,
instr(cc, '*', 1, 1) + 1,
instr(cc, '+', 1, 1) - instr(cc, '*', 1, 1) - 1)) +
substr(cc, instr(cc, '+', 1, 1) + 1) "面积"
from (select replace('面积*2+5', '面积', 70) as cc from dual);
to_number(substr(cc, 1, instr(cc, '*', 1, 1) - 1)) *
(substr(cc,
instr(cc, '*', 1, 1) + 1,
instr(cc, '+', 1, 1) - instr(cc, '*', 1, 1) - 1)) +
substr(cc, instr(cc, '+', 1, 1) + 1) "面积"
from (select replace('面积*2+5', '面积', 70) as cc from dual);