oracle pl/sql 冒泡排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4093377/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pl/sql bubble sort
提问by user496698
okay, I am beating myself up over this. I am need to load a array in people last name stored in a table. Then sort the last names and print them out in alphabetical order. This must be done using the bubble sort algorithm.
好吧，我为此自责。我需要在存储在表中的姓氏中加载一个数组。然后将姓氏排序并按字母顺序打印出来。这必须使用冒泡排序算法来完成。
here is what I have so far
这是我到目前为止所拥有的
CREATE OR REPLACE PROCEDURE TEAM_TABLE_SORT AS
TYPE player_Name_type IS TABLE OF databasename.team.player%type
INDEX BY PLS_INTEGER ;
player_name player_Name_type;
i integer := 1;
temp integer;
BEGIN
FOR player_names IN (SELECT * FROM marshall.team )
LOOP
player_name(i) := player_names.player;
DBMS_OUTPUT.PUT_LINE(i|| ' - ' ||chr(9) || player_name(i) ) ;
i := i + 1 ;
END LOOP
All this really does is print out the names. I cannot get it to sort. I am not try thing this
所有这些真正做的是打印出名称。我无法对其进行排序。我不是尝试这个
TYPE player_Name_type IS TABLE OF %type INDEX BY varchar2(20) ;
aux player_Name_type;
i integer := 1;
v_current is table of aux
swapped BOOLEAN := TRUE;
BEGIN
FOR aux IN (SELECT * FROM )
LOOP
DBMS_OUTPUT.PUT_LINE(i|| ' - ' ||chr(9) || aux.player);
i := i + 1 ;
END LOOP;
v_current := aux.first;
WHILE(swapped)
LOOP
swapped := FALSE;
FOR I IN 1..(aux.count-2) LOOP
IF aux(i) > aux(I+1) THEN
v_current := aux(i+1);
aux(I+1) := aux(i);
aux(i) := v_current;
END IF;
swapped := TRUE;
END LOOP;
END LOOP;
FOR aux IN (SELECT * FROM LOOP
DBMS_OUTPUT.PUT_LINE(i|| ' - ' ||chr(9) ||aux.player);
i := i + 1 ;
END LOOP;
回答by Craig
This should be what you are looking for. Note that it is better to type the variables/collections off of the tables like you have in your example. I just used generic versions since I don't have your tables to work with. If you don't understand how this is working, feel free to ask. I am guessing this is homework (who else would bubble sort in Oracle), so the point of the assignment is for you to understand it, not just to get it right. :)
这应该是你正在寻找的。请注意，最好像示例中那样从表中键入变量/集合。我只使用了通用版本，因为我没有你的表可以使用。如果您不明白这是如何工作的，请随时提问。我猜这是家庭作业（还有谁会在 Oracle 中进行冒泡排序），因此作业的重点是让您理解它，而不仅仅是为了让它正确。:)
DECLARE
coll DBMS_SQL.VARCHAR2A;
swapped BOOLEAN;
tmp VARCHAR2(10);
BEGIN
/*
Generate 10 random strings and collect them into our collection
Note: you would replace this with your query on marshall.team
*/
select dbms_random.string('l',10) rand_string
BULK COLLECT INTO coll
from dual
connect by level <= 10;
/*
At this point, all of the rows we need are in our collection
so there is no need to go back to the table anymore. Now onto the...
Bubble sort.. walk through the collection swapping elements until
we make a pass where no swapping takes place
*/
LOOP
swapped := false;
FOR i IN 2 .. coll.LAST
LOOP
IF coll(i-1) > coll(i)
THEN
-- swap records
tmp := coll(i);
coll(i) := coll(i-1);
coll(i-1) := tmp;
/*
Mark that swap has taken place. note we mark as true only inside
the if block, meaning a swap really did take place
*/
swapped := true;
END IF;
END LOOP;
-- If we passed through table without swapping we are done, so exit
EXIT WHEN NOT swapped;
END LOOP;
/*
Now print out records to make sure they are in order. Again notice
how we are just referencing the (now sorted) collection and not going
back to the table again
*/
FOR i in coll.FIRST .. coll.LAST
LOOP
dbms_output.put_line(coll(i));
END LOOP;
END;
/
回答by Gary Myers
You generally want to use an ORDER BY in the source query.
您通常希望在源查询中使用 ORDER BY。
You can use a VARCHAR2 index-by table to do sorting as well though.
不过，您也可以使用 VARCHAR2 索引表进行排序。
DECLARE
CURSOR c_1 is
SELECT table_name, num_rows FROM user_tables order by num_rows;
TYPE typ_tab IS TABLE OF c_1%rowtype INDEX BY user_tables.table_name%type;
t_tab typ_tab;
v_str user_tables.table_name%type;
BEGIN
FOR c_rec IN c_1 LOOP
t_tab(c_rec.table_name) := c_rec;
END LOOP;
v_str := t_tab.first;
WHILE v_str IS NOT NULL LOOP
dbms_output.put_line(t_tab(v_str).table_name||':'||t_tab(v_str).num_rows);
v_str := t_tab.next(v_str);
END LOOP;
END;
/
回答by APC
The second chunk of code you have posted looks like a valid implementation of the bubble sort algorithm. The reason it appears not to work is because of that final loop. Instead of printing out the sorted array you are repopulating it with randomly-ordered data from your table.
您发布的第二段代码看起来像是冒泡排序算法的有效实现。它似乎不起作用的原因是因为最后一个循环。您不是打印出已排序的数组，而是使用表中随机排序的数据重新填充它。
So, just change the final loop:
因此，只需更改最终循环：
FOR i IN 1..aux.count()
LOOP
DBMS_OUTPUT.PUT_LINE(i|| ' - ' ||chr(9) ||aux(i).player);
END LOOP;
回答by MrMagilla
You can also use the example of the Bubble Sort in PL / SQL on this website:
您还可以使用本网站上 PL/SQL 中的冒泡排序示例：
