oracle数据转成其他格式,Oracle中使用游标转换数据表中指定字段内容格式(拼音转数字)...

应用场景：将数据表TB_USER中字段NNDP的内容中为[sannanyinv]转换为[3男1女]

主要脚本：一个游标脚本+分割字符串函数+拼音转数字脚本

操作步骤如下：

1、创建类型

create or replace type splitTable is table of varchar2(100);

2、创建函数fn_splitString(功能是将字符串分割成多条记录)

--测试语句select * from table(fn_splitString(‘ernanyinv‘,‘nan‘))

--fn_splitString函数脚本代码

create or replace function fn_splitString(var_str   in varchar2, var_split in varchar2)

return splitTable is

var_out     splitTable;

var_tmp     varchar2(4000);

var_element varchar2(4000);

begin

var_tmp := var_str;

var_out := splitTable();

--如果存在匹配的分割符

while instr(var_tmp, var_split) > 0 loop

var_element := substr(var_tmp, 1, instr(var_tmp, var_split) - 1);

var_tmp     := substr(var_tmp,

instr(var_tmp, var_split) + length(var_split),

length(var_tmp));

--var_out.extend(1);

var_out.extend;

var_out(var_out.count) := var_element;

end loop;

--var_out.extend(1);

var_out.extend;

var_out(var_out.count) := var_tmp;

return var_out;

end fn_splitString;

3、创建函数fn_getNumber(功能是将数字拼音字符串转为数字)

--测试语句SELECT fn_getNumber(‘yi‘) from dual;

--fn_getNumber函数脚本代码

create or replace function fn_getNumber(p_str in varchar2)

return VARCHAR2

as

v_compare   VARCHAR2(20);

v_return    VARCHAR2(1);

begin

IF p_str IS NULL

THEN

RETURN ‘‘;

END IF;

v_compare:=  Lower(p_str);

CASE

WHEN v_compare = ‘yi‘

THEN

v_return := ‘1‘;

WHEN v_compare = ‘er‘

THEN

v_return := ‘2‘;

WHEN v_compare = ‘san‘

THEN

v_return := ‘3‘;

WHEN v_compare = ‘si‘

THEN

v_return := ‘4‘;

WHEN v_compare = ‘wu‘

THEN

v_return := ‘5‘;

WHEN v_compare = ‘liu‘

THEN

v_return := ‘6‘;

WHEN v_compare = ‘qi‘

THEN

v_return := ‘7‘;

WHEN v_compare = ‘ba‘

THEN

v_return := ‘8‘;

WHEN v_compare = ‘jiu‘

THEN

v_return := ‘9‘;

ELSE

v_return := ‘0‘;

END CASE;

return v_return;

end fn_getNumber;

4、运行转换脚本(功能是将数据表中指定字段内容转换为所需要的格式)

--Oracle中使用游标转换数据表中指定字段内容格式由拼音到数字

--update TB_USER set NNDP=‘sannansinv‘;

--定义游标

declare

femalenumber number:=0;--定义最后的男性数量

malenumber number:=0;--定义最后的女性数量

femalestring VARCHAR2(20):=‘er‘;--定义男性拼音分割符

malestring VARCHAR2(20):=‘sinv‘;--定义女性拼音分割符

columnstring NVARCHAR2(40):=‘‘; --定义数据表字段取出的字符串内容

resultstring  NVARCHAR2(40):=‘‘;--定义最后处理的字符串

cursor mycursor is select * from TB_USER where NNDP<>‘ ‘; --从数据表查询对应要更新的记录：

myrecord mycursor%rowtype;  --定义游标记录类型

Counter int :=0;

begin

open mycursor;  --打开游标

if mycursor%isopen  then  --判断打开成功

loop --循环获取记录集

fetch mycursor into myrecord; --获取游标中的记录

if mycursor%found then  --游标的found属性判断是否有记录

begin

--获取到字段内容并进行处理

columnstring:=myrecord.NNDP;

--dbms_output.put_line(‘当前字段对应的所有字符串‘||columnstring); --显示结果

resultstring:=‘‘;

declare  cursor mycursor1 is select * from table(fn_splitString(columnstring,‘nan‘));

myrecord1 mycursor1%rowtype;  --定义游标记录类型

Counter1 int :=0;

begin

open mycursor1;  --打开游标

if mycursor1%isopen  then  --游标打开成功

loop --循环获取记录集

fetch mycursor1 into myrecord1; --获取游标中的记录

if mycursor1%found then  --判断是否有记录

begin

--判断是否取到男性数量开始

if length(resultstring)>0 then

begin

--获取女性数量字符串

SELECT REPLACE(myrecord1.column_value,‘nv‘,‘‘) into femalestring from dual;

dbms_output.put_line(‘女性数字拼音‘||femalestring);

--转换数字拼音为数字字符

select fn_getNumber(femalestring) into femalenumber from dual;

resultstring:=resultstring||femalenumber||‘女‘;

--dbms_output.put_line(‘女性字符串‘||femalestring); --显示结果

end;

else

begin

--获取男性数量字符串

malestring:=myrecord1.column_value;

dbms_output.put_line(‘男性数字拼音‘||malestring);

--转换数字拼音为数字字符

select fn_getNumber(malestring) into malenumber from dual;

resultstring:=malenumber||‘男‘;

--dbms_output.put_line(‘男性字符串‘||resultstring); --显示结果

end;

end if;

--判断是否取到男性数量结束

end;

else

exit;

end if;--结束判断是否有记录

end loop;

else

dbms_output.put_line(‘游标1没有打开‘);

end if;      --结束打开游标成功

close mycursor1;

end;

dbms_output.put_line(resultstring); --显示结果

--更新数据库数据

update TB_USER set NNDP=resultstring where ID= myrecord.ID;

--select NNDP from TB_USER where NNDP <> ‘ ‘;

end;

else

exit;

end if;

end loop;

else

dbms_output.put_line(‘游标0没有打开‘);

end if;

close mycursor;

end;

附:运行截图

原文：http://blog.csdn.net/xqf222/article/details/42525137