Specify the field content format (Pinyin to numeric) in Oracle using the Cursor Transform data table

Scenario: convert [SANNANYINV] to [3 male 1 female] in the contents of the field NNDP in datasheet tb_user

Main script: A cursor script + split string function + Pinyin to digital script

The procedure is as follows:

1. Create a Type
Create or Replace type splittable is table of VARCHAR2 (100);

2. Create function fn_splitstring (function is to split the string into multiple records)
--Test Statement SELECT * FROM table (fn_splitstring (' Ernanyinv ', ' nan '))

--fn_splitstring Function Script code
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 ();
--if there are matching delimiters
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, create function Fn_getnumber (function is to convert the number pinyin string to a number)
--Test Statement Select Fn_getnumber (' Yi ') from dual;

--fn_getnumber Function Script code

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. Run the conversion script (the function is to convert the specified field content in the data table to the desired format)

--oracle Specifies the field content format by using a cursor in a data table from pinyin to a number
--update tb_user set nndp= ' SANNANSINV ';
--Defining cursors
Declare
Femalenumber number:=0;--defines the final number of males
Malenumber number:=0;--Define the final number of females

Femalestring VARCHAR2: = ' er ';--Define male Pinyin separator
Malestring VARCHAR2 (): = ' SINV ';--Define female phonetic separators

Columnstring NVARCHAR2 (40): = "; --Defines the string contents of the Data table field
Resultstring NVARCHAR2 (40): = ";--defines the last processed string

Cursor MyCursor is a select * from Tb_user where nndp<> '; --Query the records that correspond to the update from the data table:
Myrecord Mycursor%rowtype; --Define Cursor record type
Counter int: = 0;
Begin
Open mycursor; --Open cursor
If Mycursor%isopen then--Judging open success
Loop-Loop Get recordset
Fetch mycursor into Myrecord; --Get the record in the cursor

If Mycursor%found then--the found property of the cursor determines if there is a record
Begin

--Get the contents of the field and process it
Columnstring:=myrecord. NNDP;
--dbms_output.put_line (' all strings corresponding to the current field ' | | columnstring); --Show results
Resultstring:= ";
DECLARE CURSOR MyCursor1 is select * FROM table (fn_splitstring (columnstring, ' Nan '));
Myrecord1 Mycursor1%rowtype; --Define Cursor record type
Counter1 int: = 0;
Begin
Open MyCursor1; --Open cursor

If Mycursor1%isopen then-cursor open successfully
Loop-Loop Get recordset
Fetch MyCursor1 into myrecord1; --Get the record in the cursor

If Mycursor1%found then--determine if there is a record
Begin

--to determine whether the number of males has been taken
If Length (resultstring) >0 Then
Begin
--Get the number of females string
SELECT REPLACE (Myrecord1.column_value, ' NV ', ') into femalestring from dual;
Dbms_output.put_line (' female numeral pinyin ' | | femalestring);
--Convert digital pinyin to numeric characters
Select Fn_getnumber (femalestring) to femalenumber from dual;
Resultstring:=resultstring| | femalenumber| | ' Female ';
--dbms_output.put_line (' Female string ' | | femalestring); --Show results
End
Else
Begin
--Get the number of males string
Malestring:=myrecord1.column_value;
Dbms_output.put_line (' male numeral pinyin ' | | malestring);
--Convert digital pinyin to numeric characters
Select Fn_getnumber (malestring) to malenumber from dual;
resultstring:=malenumber| | ' Male ';
--dbms_output.put_line (' male string ' | | resultstring); --Show results
End
End If;
--Determine if the number of males ends

End

Else
Exit
End if;--To determine if there is a record

End Loop;
Else
Dbms_output.put_line (' Cursor 1 not open ');
End If; --End Open cursor successful
Close MyCursor1;
End

Dbms_output.put_line (resultstring); --Show results

--Update database data
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 (' Cursor 0 not open ');
End If;
Close MyCursor;
End

Attached: Running

Specify the field content format (Pinyin to numeric) in Oracle using the Cursor Transform data table