奇怪,对pl/sql测试通过,sql却不行.pl/sql function:create or replace function statusToDec( status in varchar2 ) return number IS l_dec number; l_status varchar2(20);begin
l_status := trim(both chr(13) from status); l_status := trim(both chr(10) from status); l_dec := 0; if l_status = '优' then l_dec := 1; elsif l_status = '良' then l_dec := 2; elsif l_status = '轻微污染' then l_dec := 3; elsif l_status = '轻度污染' then l_dec := 4; elsif l_status = '中度污染' then l_dec := 5; elsif l_status = '中度重污染' then l_dec := 6; elsif l_status = '重度污染' then l_dec := 7; elsif l_status = '重污染' then l_dec := 8; end if; return l_dec; end statusToDec ;sql:调用上面的functionselect tt.* from (select to_char(t.oper_date,'yyyy') year, sum( decode( sign(statusToDec(t.status)-1),-1,1,0 ) ) betterDay , floor((sum( decode( sign(statusToDec(t.status)-1),-1,1,0 ) )/365)*100)||'%' betterPercent , sum( decode( sign(statusToDec(t.status)-1),1,1,0 ) ) badDay , floor((sum( decode( sign(statusToDec(t.status)-1),1,1,0 ) )/365)*100)||'%' badPercent , sum( decode( sign(statusToDec(t.status)-1),0,1,0 ) ) equalDay , floor((sum( decode( sign(statusToDec(t.status)-1),0,1,0 ) )/365)*100)||'%' equalPercent from city_day t where t.city='长沙' group by to_char(t.oper_date,'yyyy') ) tt 从执行结果来看, l_dec 始终为 0 条件没有进入.
但用pl/sql test一下. 条件是可以进入的.
更正
l_status := trim(both chr(13) from status); l_status := trim(both chr(10) from l_status);
能打出来的,就把它用replace全替换成20的空格不就行了
然后trim(替换好的字符串)
replace(字符串,chr(10),'')
oracle中将字符替换为回车换行符
http://www.sosdb.com/jdul/dispbbs.asp?boardid=4&id=719&star=1&page=1
status in varchar2
) return number
IS
l_dec number;
l_status varchar2(20);begin
l_status := trim(both chr(13) from status);
l_status := trim(both chr(10) from status);
l_dec := 0;
if l_status = '优' then l_dec := 1;
elsif l_status = '良' then l_dec := 2;
elsif l_status = '轻微污染' then l_dec := 3;
elsif l_status = '轻度污染' then l_dec := 4;
elsif l_status = '中度污染' then l_dec := 5;
elsif l_status = '中度重污染' then l_dec := 6;
elsif l_status = '重度污染' then l_dec := 7;
elsif l_status = '重污染' then l_dec := 8;
end if;
return l_dec;
end statusToDec ;sql:调用上面的functionselect tt.* from
(select to_char(t.oper_date,'yyyy') year,
sum(
decode(
sign(statusToDec(t.status)-1),-1,1,0
)
) betterDay
,
floor((sum(
decode(
sign(statusToDec(t.status)-1),-1,1,0
)
)/365)*100)||'%' betterPercent
,
sum(
decode(
sign(statusToDec(t.status)-1),1,1,0
)
) badDay
,
floor((sum(
decode(
sign(statusToDec(t.status)-1),1,1,0
)
)/365)*100)||'%' badPercent
,
sum(
decode(
sign(statusToDec(t.status)-1),0,1,0
)
) equalDay
,
floor((sum(
decode(
sign(statusToDec(t.status)-1),0,1,0
)
)/365)*100)||'%' equalPercent
from city_day t
where t.city='长沙'
group by to_char(t.oper_date,'yyyy')
) tt 从执行结果来看,
l_dec 始终为 0
条件没有进入.
条件是可以进入的.
l_status := trim(both chr(13) from status);
l_status := trim(both chr(10) from l_status);