--这个是修正后的版本。
--多多支持
--drop existed function DROP FUNCTION toRmb(NUMERIC(10, 2)) CASCADE; --create function CREATE FUNCTION toRmb( NUMERIC(12, 2) ) RETURNS varchar AS ‘ --author: zhangyud at gmail dot com DECLARE retval varchar := ‘‘‘‘; UNIT CONSTANT VARCHAR[] := ARRAY[‘‘分‘‘,‘‘角‘‘,‘‘圆‘‘,‘‘拾‘‘,‘‘佰‘‘,‘‘仟‘‘,‘‘万‘‘,‘‘拾‘‘,‘‘佰‘‘,‘‘仟‘‘,‘‘亿‘‘,‘‘佰‘‘,‘‘仟‘‘]; NUMBER CONSTANT VARCHAR[] := ARRAY[‘‘零‘‘,‘‘壹‘‘,‘‘贰‘‘,‘‘叁‘‘,‘‘肆‘‘,‘‘伍‘‘,‘‘陆‘‘,‘‘柒‘‘,‘‘捌‘‘,‘‘玖‘‘]; rmbint bigint; i smallint; --rmbidx smallint; rmblength smallint; status boolean[13] := ARRAY[false, false, false, false, false, false, false, false, false, false, false, false, false]; test boolean;-- if zero occured then true; unitset int; saved boolean;-- if zero saved then true; num smallint; numtext varchar; unittext varchar; --para je alias for $1; BEGIN --init --zero := false; saved := true; test := false; rmbint := round(je * 100); -- 转换 numeric 为 bigint rmblength := char_length(rmbint); -- 100倍后金额长度 FOR i in 1 .. 13 LOOP status[i] := false; END LOOP; --start FOR i IN REVERSE rmblength-1 .. 0 LOOP num := to_number(substr(rmbint,rmblength-i,1),‘‘9‘‘); IF num > 0 THEN status[i+1] := true; --status[(i-3)/4*4+2] := true; ELSE --do nothing END IF; test := status[i+1]; --raise notice ‘‘%,test%‘‘,num,test; END LOOP; --analyze FOR i IN REVERSE rmblength .. 1 LOOP IF status[i] THEN IF NOT saved THEN status[i+1] := true; END IF; saved := true; ELSE saved :=false; END IF; --raise notice ‘‘% saved %‘‘,i,saved; END LOOP; FOR i IN REVERSE rmblength .. 3 LOOP unitset :=(i-3)/4*4+3; --raise notice ‘‘% @ %‘‘,unitset,i; IF status[i] THEN status[unitset] := true; status[3] :=true; END IF; END LOOP; -- finished --status[3] := true; --status[7] := true; --status[11] := true; FOR i IN REVERSE rmblength .. 1 LOOP num := to_number(substr(rmbint,rmblength-i+1,1),‘‘9‘‘)::smallint; numtext := NUMBER[num+1]; unittext := UNIT[i]; IF status[i] THEN IF num >0 THEN retval:= retval || numtext || unittext; ELSE IF num=0 AND (i=3 OR i=7 OR i=11) THEN retval:= retval || unittext; ELSE retval:= retval || NUMBER[1]; END IF; END IF; END IF; --RAISE NOTICE ‘‘%,%,%,%,%‘‘, i, num,numtext,unittext,retval; END LOOP; -- return return retval; END; ‘ LANGUAGE plpgsql; --test code --select tormb(123.0); --select tormb(100000023.05); --select tormb(1000000.05); --select tormb(100000000.05);
:smile2: 
