Shulou(Shulou.com)06/01 Report--

Requirement description: two tables, as follows. You need to query the name of id_new in tmp2 in tmp1 table.

Select from tmp1

Select from tmp2

Method 1:

Benefits: simple, direct sql presentation

Disadvantages: if the amount of data of the main table is too large, more than one billion, the performance will be greatly degraded, at this time, the second method is suggested.

Select a.id_old

To_char (wm_concat (distinct a.id_new)) id_new

To_char (wm_concat (distinct b.name)) name

From tmp2 b

(select a.id_old, regexp_substr (a.id_new,'[^,] +', 1, level) id_new

From tmp1 a

Connect by level = v_len then

Exit

End if

Else

V_result (v_num): = substr (p_str, v_pre_pos, v_pos-v_pre_pos)

Begin

Select a.name | | pairf

Into v_name_tmp

From tmp2 a

Where a.id_old = v_result (v_num)

Exception

When no_data_found then

V_name_tmp: =''

End

V_name_class: = v_name_class | | v_name_tmp

V_pre_pos: = v_pos + v_len1

End if

End loop

Return v_name_class

End

The effect is as follows:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.