Oracle database uses cursors to convert uppercase numbers to lowercase numbers

When the project encounters demand, it needs to convert uppercase numbers to lowercase. The code is as follows:

DECLARET_ZL VARCHAR2 (T_TS VARCHAR2); t_l number;hh varchar2 (+); XX VARCHAR2 ( TYPE TIARRAY IS TABLE OF VARCHAR2 (+); type tcarray is  TABLE OF VARCHAR2 (100); A tiarray; b tcarray;cursor c is select zl,tstybm from  fc_h _qsdc2;begin   a:=tiarray (' 47 ', ' 46 ', ' 45 ', ' 44 ', ' 43 ', ' 42 ', ' 41 ', ' 39 ', ' 38 ', ' 37 ',     ' 36 ', ' 35 ', ' 34 ', ' 33 ', ' 32 ', ' 31 ', ' 29 ', ' 28 ', ' 27 ', ' 26 ', ' 25 ', ' 24 ', ' 23 ',       ' 22 ', ' 21 ', ' 40 ', ' 30 ', ' 20 ', ' 19 ', ' 18 ', ' 17 ', ' 16 ', ' XV ', ' 14 ', ' 13 ', ' 12 ', ' 11 ', ' ten ', ' nine ', ' eight ', ' seven ', ' Six ', ' five ', ' four ', ' three ', ' two ', ' one ');     b:=tcarray (' 47 ', ' 46 ', ' 45 ', ' 44 ', ' 43 ', ' 42 ', ' 41 ', ' 39 ', ' 38 ', ' 37 ', ' 36 ', ' 35 ', ' 34 ', ' 33 ', ' 32 ', ' 31 ', ' 29 ', ' 28 ', ' 27 ', ' 26 ', ' 25 ', ' 24 ', ' 23 ', ' 22 ', ' 21 ', ' 40 ', ' 30 ', ' 20 ', ' 19 ', ' 18 ', ' 17 ', ' 16 ', ' A ', ' n ', ' One ', ' ten ', ' 9 '    , ' 8 ', ' 7 ', ' 6 ', ' 5 ', ' 4 ', ' 3 ', ' 2 ', ' 1 ');   open  c;  loop    fetch c into t_zl,t_ts;    exit  when c%notfound;        for i in 1..47  Loop      select instr (T_zl,a (i))  into xx from dual;          if  (Xx>0 and length (A (i)) =3)  then           hh:=substr (t_zl,1,xx-1) | | B (i) | | SUBSTR (t_zl,xx+3);           elsif (xx>0 and  Length (A (i)) =2)  then          hh:=substr (t_zl,1,xx-1) | | B (i) | | SUBSTR (t_zl,xx+2);           elsif (xx>0 and  Length (A (i)) =1)  then          hh:=substr (t_zl,1,xx-1) | | B (i) | | SUBSTR (t_zl,xx+1);           else             hh:=t_zl;            end if ;            t_zl:=hh;           end loop;             update fc_h_qsdc2 set zl=t_zl where tstybm=t_ts;             end loop;             close c;             End

Note: Implement key points, using (Type type_name is table of{column_type|variable_name%type|table_name.column_name%type|table_name% Rowtype}[not NULL] index BY{PLS_INTEGER|BINARY_TNTEGER|VARCHAR2 (v_size)}) the type and array are similar, using key values to find the corresponding value.

2016-05-11

This article is from the "MST" blog, so be sure to keep this source http://wolihaito.blog.51cto.com/1904662/1772865

Oracle database uses cursors to convert uppercase numbers to lowercase numbers