create or replace function F_upper_money(p_num in number default null)
return nvarchar2 is
/*Ver:1.0
将金额数字(单位元)转换为大写(采用从低至高算法)
数字整数部分不得超过16位,可以是负数。
测试用例:
SET HEAD OFF
SET FEED OFF
select '无参数时='||f_upper_money() from dual;
select 'null='||f_upper_money(null) from dual;
select '0='||f_upper_money(0) from dual;
select '0.01='||f_upper_money(0.01) from dual;
select '0.126='||f_upper_money(0.126) from dual;
select '01.234='||f_upper_money(01.234) from dual;
select '10='||f_upper_money(10) from dual;
select '100.1='||f_upper_money(100.1) from dual;
select '100.01='||f_upper_money(100.01) from dual;
select '10000='||f_upper_money(10000) from dual;
select '10012.12='||f_upper_money(10012.12) from dual;
select '20000020.01='||f_upper_money(20000020.01) from dual;
select '3040506708.901='||f_upper_money(3040506708.901) from dual;
select '40005006078.001='||f_upper_money(40005006078.001) from dual;
select '-123456789.98='||f_upper_money(-123456789.98) from dual;
-------
select '123456789123456789.89='||f_upper_money(123456789123456789.89) from dual;
*/
Result nvarchar2(100);--返回字符串
num_round nvarchar2(100) :=to_char(abs(round(p_num,2)));--转换数字为小数点后2位的字符(正数)
num_left nvarchar2(100);--小数点左边的数字
num_right nvarchar2(2);--小数点右边的数字
str1 nchar(10) :='零壹贰参肆伍陆柒捌玖';--数字大写
str2 nchar(16) :='元拾佰仟万拾佰仟亿拾佰仟万拾佰仟';--数字位数(从低至高)
num_pre number(1):=1;--前一位上的数字
num_current number(1);--当前位上的数字
num_count number:=0;--当前数字位数
begin
if p_num is null then return null;end if;--转换数字为null时返回null
select to_char(
nvl(substr(to_char(num_round),1,
decode(instr(to_char(num_round),'.'),0,
length(num_round),instr(to_char(num_round),'.')-1)),
0)) into num_left from dual;--取得小数点左边的数字
select substr(to_char(num_round),
decode(instr(to_char(num_round),'.'),0,
length(num_round)+1,instr(to_char(num_round),'.')+1),2)
into num_right from dual;--取得小数点右边的数字
if length(num_left)>16 then return '**********'; end if;--数字整数部分超过16位时
--采用从低至高的算法，先处理小数点右边的数字
if length(num_right)=2 then
if to_number(substr(num_right,1,1))=0 then
result:='零'||substr(str1,to_number(substr(num_right,2,1))+1,1)||'分';
else
result:=substr(str1,to_number(substr(num_right,1,1))+1,1)||'角'||
substr(str1,to_number(substr(num_right,2,1))+1,1)||'分';
end if;
elsif length(num_right)=1 then
result:=substr(str1,to_number(substr(num_right,1,1))+1,1)||'角整';
else
result :='整';
end if;
--再处理小数点左边的数字
for i in reverse 1..length(num_left) loop --(从低至高)
num_count:=num_count+1;--当前数字位数
num_current:=to_number(substr(num_left,i,1));--当前位上的数字
if num_current>0 then --当前位上数字不为0按正常处理
result:=substr(str1,num_current+1,1)||substr(str2,num_count,1)||result;
else --当前位上数字为0时
if mod(num_count-1,4)=0 then --当前位是元、万或亿时
result:=substr(str2,num_count,1)||result;
num_pre:=0;--元、万,亿前不准加零
end if;
if num_pre>0 or length(num_left)=1 then --上一位数字不为0或只有个位时
result:=substr(str1,num_current+1,1)||result;
end if;
end if;
num_pre:=num_current;
end loop;
if p_num<0 then --转换数字是负数时
result:='负'||result;
end if;
return Result;
exception
when others then
raise_application_error(-20001,'数字转换大写出现错误！'||sqlerrm);
end ;
--下面是一个存储过程，主要是看他的结构
create or replace procedure ptr_jsfood_rep_crop_pterm
(outnum out integer)
is
/***********************************************************************
目的：
统计报表（江苏省食品生产加工企业生产条件汇总表）
XXX 2005/06/15 1.0 创建
Co.ltd
***********************************************************************/
var_crop_scope varchar2(50);
var_crop_character varchar2(50);
var_sate_sign number; --执行标准（国标）
var_row_sign number; --执行标准（行标）
var_corp_sign number; --执行标准（企标）
var_none_sign number; --执行标准（无标）
var_pq_fine number; --产品质量 （好）
var_pq_preferably number; --产品质量 （较好）
var_pq_sort number; --产品质量 （较差）
var_pq_errand number; --产品质量 （差）
var_pe_fine number; --生产设备（好）
var_pe_preferably number; --生产设备（较好）
var_pe_sort number; --生产设备（较差）
var_pe_errand number; --生产设备（差）
var_MATERIAL_since_check number; --原材料把关（自检）
var_MATERIAL_consign number; --原材料把关（委托）
var_MATERIAL_prove number; --原材料把关（证明）
var_MATERIAL_n_check number; --原材料把关（不把关）
var_lc_since_check number; --出厂检验方式（自检）
var_lc_p_scheck number; --出厂检验方式（部分自检）
var_lc_consign number; --出厂检验方式（委托）
var_mea_affirm1 number; --计量确认（体系）
var_mea_affirm2 number; --计量确认（保证）
var_mea_affirm3 number; --计量确认（合格）
var_iso9000 number; --体系认证（ISO9000）
var_HACCP number; --体系认证（HACCP）
var_perennial number; --生产情况（常年）
var_season number; --生产情况（季节）
var_stop_prod number; --生产情况（停产）
CURSOR CURJSFOOD_stocktmp IS
select crop_scope,crop_character from tmp_rep_crop_pterm;
begin
execute immediate'truncate table tmp_rep_crop_pterm';
execute immediate'truncate table rep_crop_pterm';
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','国有');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','集体');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','股份');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','私营');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','三资');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','合计');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','国有');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','集体');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','股份');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','私营');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','三资');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','合计');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','国有');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','集体');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','股份');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','私营');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','三资');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','合计');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小作坊','小作坊');
insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('总计','总计');
open CURJSFOOD_stocktmp ;
while true loop
fetch CURJSFOOD_stocktmp into var_crop_scope,var_crop_character;
exit when CURJSFOOD_stocktmp%notfound;
--执行标准（国标）
select nvl(sum(decode(prod_level,'国标',1,0)),0) into var_sate_sign from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--执行标准（行标）
select nvl(sum(decode(prod_level,'行标',1,0)),0) into var_row_sign from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--执行标准（企标）
select nvl(sum(decode(prod_level,'企标',1,0)),0) into var_corp_sign from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--执行标准（无标）
select nvl(sum(decode(prod_level,'无标',1,0)),0) into var_none_sign from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--产品质量（好）
select nvl(sum(decode(prod_evalu,'好',1,0)),0) into var_pq_fine from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--产品质量 （较好）
select nvl(sum(decode(prod_evalu,'较好',1,0)),0) into var_pq_preferably from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--产品质量（较差）
select nvl(sum(decode(prod_evalu,'较差',1,0)),0) into var_pq_sort from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--产品质量（差）
select nvl(sum(decode(prod_evalu,'差',1,0)),0) into var_pq_errand from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产设备（好）
select nvl(sum(decode(EQUIP_EVALU,'好',1,0)),0) into var_pe_fine from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产设备（较好）
select nvl(sum(decode(EQUIP_EVALU,'较好',1,0)),0) into var_pe_preferably from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产设备（较差）
select nvl(sum(decode(EQUIP_EVALU,'较差',1,0)),0) into var_pe_sort from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产设备（差）
select nvl(sum(decode(EQUIP_EVALU,'差',1,0)),0) into var_pe_errand from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--原材料把关（自检）
select nvl(sum(decode(material_gate,'自检',1,0)),0) into var_MATERIAL_since_check from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--原材料把关（委托）
select nvl(sum(decode(material_gate,'委托',1,0)),0) into var_MATERIAL_consign from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--原材料把关（证明）
select nvl(sum(decode(material_gate,'证明',1,0)),0) into var_MATERIAL_prove from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--原材料把关（不把关）
select nvl(sum(decode(material_gate,'不把关',1,0)),0) into var_MATERIAL_n_check from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--出厂检验方式（自检）
select nvl(sum(decode(b.factory_check_type,'自检',1,0)),0) into var_lc_since_check from t_prod_info b,t_corp t ,dic_reg_type d where b.corp_code=t.corp_code and t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--出厂检验方式（部分自检）
select nvl(sum(decode(b.factory_check_type,'部分自检',1,0)),0) into var_lc_p_scheck from t_prod_info b,t_corp t ,dic_reg_type d where b.corp_code=t.corp_code and t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--出厂检验方式（委托）
select nvl(sum(decode(b.factory_check_type,'委托',1,0)),0) into var_lc_consign from t_prod_info b,t_corp t ,dic_reg_type d where b.corp_code=t.corp_code and t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--计量确认（体系）
select nvl(sum(decode(measure_affirm,'体系',1,0)),0) into var_mea_affirm1 from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--计量确认（保证）
select nvl(sum(decode(measure_affirm,'保证',1,0)),0) into var_mea_affirm2 from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--计量确认（合格）
select nvl(sum(decode(measure_affirm,'合格',1,0)),0) into var_mea_affirm3 from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--体系认证（ISO9000）
select nvl(sum(decode(SYS_AUTH,'ISO9000',1,0)),0) into var_iso9000 from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--体系认证（HACCP）
select nvl(sum(decode(SYS_AUTH,'HACCP',1,0)),0) into var_HACCP from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产情况（常年）
select nvl(sum(decode(PROD_CYCLE,'常年',1,0)),0) into var_perennial from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产情况（季节）
select nvl(sum(decode(PROD_CYCLE,'季节',1,0)),0) into var_season from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产情况（停产）
select nvl(sum(decode(PROD_CYCLE,'停产',1,0)),0) into var_stop_prod from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
update tmp_rep_crop_pterm set
sate_sign=var_sate_sign,row_sign=var_row_sign,corp_sign=var_corp_sign,none_sign=var_none_sign,
pq_fine=var_pq_fine,pq_preferably=var_pq_preferably,pq_sort=var_pq_sort,pq_errand=var_pq_errand,
pe_fine=var_pe_fine,pe_preferably=var_pe_preferably,pe_sort=var_pe_sort,pe_errand=var_pe_errand,
MATERIAL_since_check=var_MATERIAL_since_check,MATERIAL_consign=var_MATERIAL_consign,
MATERIAL_prove=var_MATERIAL_prove,MATERIAL_n_check=var_MATERIAL_n_check,
lc_since_check=var_lc_since_check,lc_p_scheck=var_lc_p_scheck,lc_consign=var_lc_consign,
mea_affirm1=var_mea_affirm1,mea_affirm2=var_mea_affirm2,mea_affirm3=var_mea_affirm3,
iso9000=var_iso9000,HACCP=var_HACCP,perennial=var_perennial,season=var_season, stop_prod=var_stop_prod
where crop_scope=var_crop_scope and crop_character=var_crop_character;
end loop;
close CURJSFOOD_stocktmp;
insert into rep_crop_pterm select * from tmp_rep_crop_pterm;
commit;
outnum:=1;
exception
when others then
outnum:=0;
rollback;
end ptr_jsfood_rep_crop_pterm;