气泡排序法

Question

請大家幫幫忙，
请写一Procedure，传入一数列字符串，数字间以逗号相隔，使用气泡排序法(bubble sort)做排序，并显示排序的过程及结果。(说明 : 气泡排序法又称为交换排序，从第一个数字开始比较，相邻的两个数相比，假使前一个数字比后一个大时，则互相对调。) 
例如: Execute  pro_sort( ’18 , 2 , 20 , 34 , 12’ )  (请记录执行过程)
显示执行过程及结果----------------------------------------
Access :  2  18  20  34  12
Access :  2  12  20  34  18
Access :  2  12  18  34  20
Access :  2  12  18  20  34
----------------------------------------
Sorting :  2  12  18  20  34

Answer 1

这个可以这样实现，先分拆字符串，然后排序，再用WMSYS.WM_CONCAT来拼出来

Answer 2

SELECT wmsys.wm_concat (new_str) out_str
  FROM (SELECT   SUBSTR (',' || :INSTR || ',',
                         INSTR (',' || :INSTR || ',', ',', 1, ROWNUM) + 1,
                           INSTR (',' || :INSTR || ',', ',', 1, ROWNUM + 1)
                         - INSTR (',' || :INSTR || ',', ',', 1, ROWNUM)
                         - 1
                        ) AS new_str
            FROM all_objects
           WHERE ROWNUM <=
                        LENGTH (:INSTR) - LENGTH (REPLACE (:INSTR, ',', ''))
                        + 1
        ORDER BY 1)
         
输入:instr=20,10,30,40,70,60,50
输出
out_str
10,20,30,40,50,60,70

Answer 3

SELECT wmsys.wm_concat (new_str) out_str
  FROM (SELECT   to_number(trim(SUBSTR (',' || :INSTR || ',',
                         INSTR (',' || :INSTR || ',', ',', 1, ROWNUM) + 1,
                           INSTR (',' || :INSTR || ',', ',', 1, ROWNUM + 1)
                         - INSTR (',' || :INSTR || ',', ',', 1, ROWNUM)
                         - 1
                        ))) AS new_str
            FROM all_objects
           WHERE ROWNUM <=
                        LENGTH (:INSTR) - LENGTH (REPLACE (:INSTR, ',', ''))
                        + 1
        ORDER BY 1)
输入 部分数字前有空格
18 , 2 , 20 , 34 , 12输出
2,12,18,20,34

Answer 4

在我電腦裡面運行時會報出如下的錯誤信息Error: PLS-00201: 識別字 'WMSYS.WM_CONCAT' 必須被宣告
Line: 4
Text: SELECT wmsys.wm_concat (new_str) out_strError: PL/SQL: SQL Statement ignored
Line: 4
Text: SELECT wmsys.wm_concat (new_str) out_str

Answer 5

你是9I
的吧
wmsys.wm_concat是10G才有的
9I的参考此贴来改http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048
用SYS_CONNECT_BY_PATH 来变相实现

Answer 6

/* Formatted on 2008/11/21 10:25 (Formatter Plus v4.8.8) */SELECT     SUBSTR (MAX (SYS_CONNECT_BY_PATH (new_str, ',')), 2) out_str
      FROM (SELECT a.*, ROWNUM rn
              FROM (SELECT   TO_NUMBER
                                 (TRIM (SUBSTR (',' || :INSTR || ',',
                                                  INSTR (',' || :INSTR || ',',
                                                         ',',
                                                         1,
                                                         ROWNUM
                                                        )
                                                + 1,
                                                  INSTR (',' || :INSTR || ',',
                                                         ',',
                                                         1,
                                                         ROWNUM + 1
                                                        )
                                                - INSTR (',' || :INSTR || ',',
                                                         ',',
                                                         1,
                                                         ROWNUM
                                                        )
                                                - 1
                                               )
                                       )
                                 ) AS new_str
                        FROM all_objects
                       WHERE ROWNUM <=
                                  LENGTH (:INSTR)
                                - LENGTH (REPLACE (:INSTR, ',', ''))
                                + 1
                    ORDER BY 1) a)
START WITH rn = 1
CONNECT BY rn - 1 = PRIOR rn
9I用这个方法