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] 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

2. Create the fn_splitString function (which splits the string into multiple records)

-- Fn_splitString function script code

Return splitTable is

Var_out splitTable;

Begin

-- Var_out.extend (1 );

Return var_out;

3. create function fn_getNumber (function is to convert a digit into a digit)

-- Fn_getNumber function script code

Create or replace function fn_getNumber (p_str in varchar2)

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.

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

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

Cursor mycursor is select * from TB_USER where NNDP <> ''; -- query the records to be updated from the data table:

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

-- Obtain and process the field content

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

Else

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

End;

Else

Appendix: Run