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

iilegend 2015-01-08

应用场景:将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; 

附:运行截图

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

相关推荐