oracle修改字段值的函数,在sqlldr中执行对字段的函数转换

把对字段的函数转换放到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

函数转换成功[@more@]