Oracle check character data type functions & stored Procedures

The dump function is used to determine whether a character type in a string belongs to number or string

Stored Procedures

Create or Replace procedurePro_isnum (i_stringinch varchar2, O_note outvarchar2)  asstring1varchar2( +);--after the field is converted by dump ()Num1 Number( -);--the number of characters in the fieldNum2 Number( -);--ASCII comparison variableI Number( -);--Cyclic Cumulative variables begin   IFI_string is  not NULL  Then      Select            ','||SubstrDump(i_string), InStr (Dump(i_string),':')+1, Length (Dump(i_string))-InStrDump(i_string),':'))||','            intoString1 fromdual; SelectNVL (Length (string1)-LengthReplace(String1,',',"')),-1) intoNum1 fromdual; I:=1; O_note:=' Number'; <<First_loop>>LoopifI=Num1 ThenDbms_output.put_line ('the loop has ended with a data type of'||o_note); ExitFirst_loop; End if; SelectTo_number (substr (STRING1,INSTR (string1,',',1I+1, InStr (String1,',',1, I+1)-InStr (String1,',',1I-1)) intoNum2 fromdual; IFNum2< - orNum2> $  ThenO_note:='string'; return; End IF; I:=I+1; EndLoop Fst_loop; Dbms_output.put_line ('Loop loop is over! '); ElseO_note:='NULL'; End if; End;

Function

Create or Replace functionFunc_isnum (i_stringinch varchar2) return varchar2--return type  isstring1varchar2( +);--after the field is converted by dump ()Num1 Number( -);--the number of characters in the fieldNum2 Number( -);--ASCII comparison variableI Number( -);--Cyclic Cumulative variablesO_notevarchar2( -);--return type begin   IFI_string is  not NULL  Then      Select            ','||SubstrDump(i_string), InStr (Dump(i_string),':')+1, Length (Dump(i_string))-InStrDump(i_string),':'))||','            intoString1 fromdual; SelectNVL (Length (string1)-LengthReplace(String1,',',"')),-1) intoNum1 fromdual; I:=1; O_note:=' Number'; <<First_loop>>LoopifI=Num1 ThenDbms_output.put_line ('the loop has ended with a data type of'||o_note); ExitFirst_loop; End if; SelectTo_number (substr (STRING1,INSTR (string1,',',1I+1, InStr (String1,',',1, I+1)-InStr (String1,',',1I-1)) intoNum2 fromdual; IFNum2< - orNum2> $  ThenO_note:='string'; End IF; I:=I+1; EndLoop Fst_loop; Dbms_output.put_line ('Loop loop is over! '); ElseO_note:='NULL'; End if; returnO_note;End;

Oracle check character data type functions & stored Procedures