本文介绍了如何在SQL数据加载过程中使用函数进行字段转换，并通过示例展示了转换过程及验证方法。
把对字段的函数转换放到sqlldr加载中执行
msc "transne2id(:msc,'mss','')" cs_cdr_camel;
1）使用的函数
create or replace function transne2id(code varchar2,
ne_type varchar2,
code_type varchar2)
return number is
ne_id varchar2(100);
--p_id varchar2(50);
--根据网元的spc、id、gt翻译成id
--code：网元的id、spc、
--ne_type:要显示的网元类型，如:bsc、rnc
begin
ne_id := '';
--select id into p_id from meta_province where is_location=1;
begin
if(code is not null) then
if (Lower(ne_type) = 'mss') then
if(Lower(code_type) = 'gt') then
select id into ne_id from ne_mss where gt_list like '%|' || code || '|%' ;--and prov_id=p_id;
elsif(Lower(code_type)='spc') then
select id into ne_id from ne_mss where spc_list like '%|' || code || '|%' ;--and prov_id=p_id;
elsif(Lower(code_type)='id') then
select code into ne_id from dual;
else
if(length(code)=10 and substr(code,0,2)='86') then
select id into ne_id from ne_mss where gt_list like '%|' || code || '|%' ;--and prov_id=p_id;
elsif(length(code)>=5) then
select id into ne_id from ne_mss where spc_list like '%|' || code || '|%' ;--and prov_id=p_id;
else
select code into ne_id from dual;
end if;
end if;
else
ne_id := 0;
end if;
else
ne_id := code;
end if;
EXCEPTION
WHEN OTHERS THEN
ne_id := 0;
end;
return(ne_id);
end transne2id;
2）更改sqlldr控制文件
LOAD DATA
Append
INTO TABLE CS_CDR_CAMEL
fields terminated by ','
optionally enclosed by '"' TRAILING NULLCOLS
(
cdr_id,
begin_time,
end_time,
idp_time,
rrbe_time,
opc,
dpc,
ogt,
dgt,
msc "transne2id(:msc,'mss','')", --对msc列值进行函数转换
connect_time,
continue_time,
locationnum,
msrn,
service_type,
callingnum,
callednum,
calling_imsi,
called_imsi,
maxcalltime,
calltime,
release_time,
release_cause,
create_time date "yyyy-mm-dd hh24:mi:ss"
)
3）查询确定msc值是转换的值；
SQL> select msc
2 from cs_cdr_camel
3 where begin_time = (select to_char(max(begin_time)) from cs_cdr_camel);
MSC
----------
38
函数转换成功
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26179867/viewspace-708249/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26179867/viewspace-708249/
扫一扫
2596
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
