Oracle string split sort bubbling algorithm

Example:

A string "11,15,13,17,12", separated by commas, is now sorted into "11,12,13,15,17".

Write an implementation method that is recorded for later use:

----------------------------------------------------------Export file for user BEN----Created by Administrator on 2015/8/5 Wed, 10:24:20----------------------------------------------------------SetDefineifspool get_bubble.Logpromptprompt Creatingfunctionget_bubbleprompt============================promptCreate or Replace functionget_bubble (Av_strvarchar2,--the string to splitAv_splitvarchar2  --Delimited Symbols)return varchar2 isLv_strvarchar2(1024x768); Str  varchar2(1024x768); STR1varchar2(1024x768);--the first substring to be exchangedstr2varchar2(1024x768);--the second substring to be exchanged  Temp varchar2(1024x768);--temporarily swap intermediate spaceNew_strvarchar2(1024x768);--maximum value of the inner loop per interchangeAll_strvarchar2(1024x768);--save a new string generated after each loopEnd_strvarchar2(1024x768);--final value, return valueLv_length Number;--number of sub-strings after splittingJflagChar(1);--identification of the first internal loop after each outer loopI Number:= 0;--External loop controlJ Number:= 0;--Internal Loop ControlX Number;--First substring intercept position in first loopK Number;--first Inner Loop second substring intercept positionY Number; Z Number;--the position of a non-first loop substring interceptbeginLv_str:= LTrim(RTrim(AV_STR));--go space before and after  Str:=Lv_str; Lv_length:= 0; ----Split string "Get the number of sub-strings"   whileInStrStr, Av_split)<>0Loop lv_length:=Lv_length+1; Str:=SubstrStr, InStr (Str, Av_split)+Length (av_split), Length (Str)); EndLoop; Lv_length:=Lv_length+1; ifLv_length<= 1  Then    return 'only one substring after splitting'; End if;------------------------------------------------  END_STR:= "'; <<Loop1>>Loop--Outer Loop    ifI<Lv_length ThenJ:=I+1; Jflag:= 0; NEW_STR:= "'; ALL_STR:= "'; <<Loop2>>Loop--Inner Loop        ifJ<Lv_length Then          ifJflag= 0  Then --first internal loop (easy to deal with substring stitching problem)            SelectInStr (Lv_str, Av_split,1) intoX fromdual; STR1:=SUBSTR (LV_STR,1X-1); --Decode handles the last substring intercept position 0 problem            SelectDecode (InStr (Lv_str, Av_split, X+Length (av_split)),0, Length (LV_STR)+1, InStr (Lv_str, Av_split, X+Length (av_split))) intoK fromdual; STR2:=SUBSTR (LV_STR, X+Length (av_split), K-X-Length (av_split)); Select Count(*) intoY fromDualwhereStr1>str2; ifY> 0  ThenNew_str:=str1; Temp:=str1; STR1:=str2; STR2:= Temp; ElseNew_str:=str2; End if; ALL_STR:=str1; Jflag:= 1; Else --Non-firstSTR1:=NEW_STR;--the maximum value since the last comparison is the previous value of this comparison            SelectDecode (InStr (Lv_str, Av_split, K+Length (av_split)),0, Length (LV_STR)+1, InStr (Lv_str, Av_split, K+Length (av_split))) intoZ fromdual; STR2:=Substr (Lv_str, K+Length (av_split), Z-K-Length (av_split));--a secondK:=Z--Z assigns K, convenient next intercept str2            Select Count(*) intoY fromDualwhereStr1>str2; ifY> 0  ThenNew_str:=str1; Temp:=str1; STR1:=str2; STR2:= Temp; ElseNew_str:=str2; End if; ALL_STR:=All_str||Av_split||str1; End if; End if; J:=J+1; ExitLoop2 whenJ>=lv_length; EndLoop Loop2; LV_STR:=All_str||Av_split||NEW_STR;--a new string generated in the previous round            ifEnd_str is NULL  Then --the final substring processingEND_STR:=New_str; ElseEnd_str:=New_str||Av_split||End_str; End if; End if; I:=I+1; Exit  whenI>=Lv_length-1; EndLoop Loop1; END_STR:=Str1||Av_split||END_STR;--returns the last value before stitching  returnEnd_str;Endget_bubble;/Spooloff

Oracle string split sort bubbling algorithm