function中的一个语句,该怎么解决
日期：2014-05-17 浏览次数：20767 次
function中的一个语句
CREATE FUNCTION "tab"."F_PERCENT" (
"P" DECIMAL(16,10),
"D" INTEGER )
RETURNS CHARACTER(20)
SPECIFIC "SQL120401142399999"
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
begin ATOMIC
declare c char(20);
set c=(
select case when p>=0.01 then
trim(T '.' from trim(B '0' from trim(char(round(p*100,d)))))||'%'
else '0'||trim(T '.' from trim(B '0' from trim(char(round(p*100,d)))))||'%'
end
from SYSIBM.SYSDUMMY1
);
return c;
end;
trim里面的看不懂，请高人指点
------解决方案--------------------
CREATE FUNCTION "tab"."F_PERCENT" (
"P" DECIMAL(16,10),
"D" INTEGER )
RETURNS CHARACTER(20)
SPECIFIC "SQL120401142399999"
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
begin ATOMIC
declare c char(20);
set c=(
select case when p>=0.01 then
trim(T '.' from trim(B '0' from trim(char(round(p*100,d)))))||'%'
else '0'||trim(T '.' from trim(B '0' from trim(char(round(p*100,d)))))||'%'
end
from SYSIBM.SYSDUMMY1
);
return c;
end;
trim里面的看不懂，请高人指点
------解决方案--------------------
- SQL code
TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1)
如果没有指定任何参数则oracle去除c1头尾空格
例如：SELECT TRIM(' WhaT is tHis ') FROM DUAL;
如果指定了c2参数，则oracle去掉c1头尾c2(这个建议细致测试，有多种不同情形的哟)
例如：SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了leading参数则会去掉c1头部c2
例如：SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了trailing参数则会去掉c1尾部c2
例如：SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗？没区别！)
例如：SELECT TRIM(both 'Wh' FROM 'WhaT is tHis w W') FROM DUAL;
注意：c2长度=1
------解决方案--------------------
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
