Oracle Data Transformation method that displays the ID value of the dictionary table as text

• (This article original, quote please specify source--zhangjieatbky)
• Scenario: For example, to convert an ID value stored in a field, such as a to a text display, such as basketball, soccer, volleyball
  • Create two tables

  • 1 CreateA1 (ID Number(8),text varchar( the));//Create a dictionary table2 Insert  intoA1Values(Ten, football);3 Insert  intoA1Values( One, basketball);4 Insert  intoA1Values( A, badminton);5 Insert  intoA1Values( -, volleyball);6 7 CreateB1 (bid Number(8), Btextvarchar2( the));//Business Master Table8 Insert  intoB1Values( +,'10,11,13');9 Insert  intoB1Values( -,'11,12');

  • Create a transform function

  • 1 //Create a function2 Create or Replace functionConverter (3P_bid B1.bid%type)4 return varchar25  as6V_len Number(2);7V_dict_textvarchar( the);8V_index Binary_integer:=1;9V_resultvarchar( Max):="';TenV_temp1varchar2(3); OneV_temp2varchar2( -); A begin -   SelectB1.btext intoV_dict_text fromB1whereBid=P_bid; -   SelectLength (Translate (V_dict_text,'a0123456789',' ')) intoV_len fromdual; the    whileV_index<=V_len+1Loop -     SELECTRegexp_substr (V_dict_text,'[[: alnum:]]+',1, V_index) intoV_temp1 fromdual; -     SELECT TEXT  intoV_temp2 fromA1WHEREId=V_temp1; -V_result:=Concat (Concat (V_RESULT,V_TEMP2),','); +V_index:=V_index+1; -   EndLoop; +   RETURNSUBSTR (V_result,0, Length (V_result)-1); A EndConverter; at  - //test results, such as successful conversion of dictionary values to text values - SELECTBid,converter (BID)TEXT  fromB1

  • The results are as follows

Oracle Data Transformation method that displays the ID value of the dictionary table as text