In Oracle, the field content format specified in the data table is converted from pinyin to numbers. oracle cursor

In Oracle, the field content format specified in the data table is converted from pinyin to numbers. oracle cursor

Application Scenario: Convert [sannanyinv] to [3 male and 1 female] In the NNDP field of the data table TB_USER.

Main Script: A cursor script + split string Function + pinyin to digital script

The procedure is as follows:

1. Creation type
Create or replace type splitTable is table of varchar2 (100 );

2. Create the fn_splitString function (which splits 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 a matched delimiter exists
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 a digit into a digit)
-- 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 to convert the specified fields in the data table to the required format)

-- Convert the content of a specified field from pinyin to a number in a data table using a cursor in Oracle.
-- Update TB_USER set NNDP = 'sannansinv ';
-- Define a cursor
Declare
Femalenumber number: = 0; -- defines the final number of male
Malenumber number: = 0; -- defines the last number of female

Femalestring VARCHAR2 (20): = 'er'; -- defines the male pinyin delimiter.
Malestring VARCHAR2 (20): = 'sinv'; -- defines the female pinyin delimiter.

Columnstring NVARCHAR2 (40): = ''; -- defines the string content retrieved from the data table field.
Resultstring NVARCHAR2 (40): = ''; -- defines the final string to be processed.

Cursor mycursor is select * from TB_USER where NNDP <> ''; -- query the records to be updated from the data table:
Myrecord mycursor % rowtype; -- defines the cursor record type
Counter int: = 0;
Begin
Open mycursor; -- open the cursor
If mycursor % isopen then -- determines whether the open is successful
Loop -- Obtain Record Sets cyclically
Fetch mycursor into myrecord; -- get records in the cursor

If mycursor % found then -- the cursor's found attribute determines whether a record exists
Begin

-- Obtain and process the field content
Columnstring: = myrecord. NNDP;
-- Dbms_output.put_line ('all strings corresponding to the current field '| columnstring); -- Display Results
Resultstring: = '';
Declare cursor mycursor1 is select * from table (fn_splitString (columnstring, 'nan '));
Myrecord1 mycursor1 % rowtype; -- defines the cursor record type
Counter1 int: = 0;
Begin
Open mycursor1; -- open the cursor

If mycursor1 % isopen then -- the cursor is successfully opened.
Loop -- Obtain Record Sets cyclically
Fetch mycursor1 into myrecord1; -- get records in the cursor

If mycursor1 % found then -- determines whether a record exists
Begin

-- Determine whether the number of men is obtained.
If length (resultstring)> 0 then
Begin
-- Obtain the number of female strings
Select replace (myrecord1.column _ value, 'nv ', '') into femalestring from dual;
Dbms_output.put_line ('female digital pinyin '| femalestring );
-- Convert a digit to a digit
Select fn_getNumber (femalestring) into femalenumber from dual;
Resultstring: = resultstring | femalenumber | 'femal ';
-- Dbms_output.put_line ('female string' | femalestring); -- display result
End;
Else
Begin
-- Obtain the number of male strings
Malestring: = myrecord1.column _ value;
Dbms_output.put_line ('male pinyin '| malestring );
-- Convert a digit to a digit
Select fn_getNumber (malestring) into malenumber from dual;
Resultstring: = malenumber | 'male ';
-- Dbms_output.put_line ('male string' | resultstring); -- display result
End;
End if;
-- Determine whether the number of men is reached

End;

Else
Exit;
End if; -- end to determine whether a record exists

End loop;
Else
Dbms_output.put_line ('cursor 1 not open ');
End if; -- the cursor is successfully opened.
Close mycursor1;
End;

Dbms_output.put_line (resultstring); -- display the result

-- 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 is not open ');
End if;
Close mycursor;
End;

 

Appendix: Run