Use cursor to convert the format of specified fields in the data table in Oracle (pinyin to numbers)

Use cursor to convert the format of specified fields in the data table in Oracle (pinyin to numbers)

Use cursor to convert the format of specified fields in the data table in Oracle (pinyin to numbers)

Application Scenario: Convert [sannanyinv] In the NNDP field of the Oracle Data Table TB_USER to [3 male and 1 female]

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