Oracle中使用遊標轉換資料表中指定欄位內容格式(拼音轉數字)，oracle遊標

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;

 

附:運行