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