PostgreSQL 影片

Loading...
Google

2007-09-01

Stored Procedure 案例:動態排列資料

在資料整理時,我們常碰到有些資料需要往前移動,有些則要往後移動,尤其在資料的顯示流程規劃上就常常要使用到這種 功能。可是傳統資料庫的排序功能,不外乎就是使用日期、時間、資料編號做為排序依據,然後利用 ORDER BY 命令達成資料排序的目的,除此之外,我們無法動態調整資料列的先後次序。


一種解決辦法是為每個資料列增加新的欄位﹝例子中取名為mytable_after﹞,用來記錄該筆資料列的前一筆資料編號。所以我們在讀取資料時就使用該欄位記錄的值,逐一列出每筆資料。操作流程大致上是:

讀取第一筆資料 Amytable_after 為 0
讀取欄位 mytable_after = A 的第二筆資料 B
重覆操作直到找不到下一筆資料
此方法讀取過程繁複,若交由外部程式去做,將會不停的發送 SELECT 命令給 SQL Server,可想而之,其效能必定差矣。所以,我們可以建立 PostgreSQL 預儲程序,讓 PostgreSQL 在內部操作處理,然後直接回傳已排序完成的資料流。

PostgreSQL 動態排列實作例子:

CREATE OR REPLACE FUNCTION mytable_dynlist()
RETURNS SETOF mytable AS
$BODY$DECLARE
rscount Integer;
rsnext Integer;
rs record;

BEGIN
    select count(*) INTO rscount from mytable;
rsnext = 0;
FOR i IN 1 ..
rscount LOOP
SELECT * INTO rs from mytable where mytable_after = rsnext LIMIT 1;
rsnext = rs.mytable_id;
RETURN NEXT rs;
END LOOP;

END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION
mytable_dynlist() OWNER TO somebody;

當 資料不多時,這種在資料庫內部處理動態排列很省事,在前端的程式也很好寫,但是,資料量一大,這種方式會造成 SQL Server 相當大的負擔。所以,當資料量多或是在前端程式有做多台機器的負載平衡,還是建議在前端先將所有要排序的資料下載後,在前端的程式做排序處理。



延伸閱讀(Link):
PostgreSQL 的 Stored Procedure 簡易實例
Stored Procedure 實例:資料分頁

3 則留言:

匿名 提到...

方法總是找得到,想法是否合宜? DB應該負責將資料集準備好,而把資料的輸出佈局這樣的工作放在DB裡面去搞定,真的是對的事嗎? 那建構在DB之上的服務如:reporting, BI, OLAP要幹啥?

郭朝益(ChaoYi, Kuo) 提到...

您好, 見你多次發表意見了, 您的觀念非常之棒, 給否給小僧您的 mail 您也來寫文表達一下觀點!
再者從您多次的文中可以得知您確實在專業上"閱讀"很精進, 但在實際工作上與對 Stored Procedure, BI, reporting, BI, OLAP,是互補而非對立的, 是延展也是表現, 但本Blog的出發點是在推廣, 而非大論差異, 這必須請您多從事實際面的工作或把您提出的問句寫成文章, 給小僧幫您公開, 讓更多人來評論您的對錯如何~
感謝您的閱讀~更期待您的文章.

Fred 提到...

謝謝您的指教,我也同意您所說的,資料輸出佈局在資料庫內完成是應該要再三考慮的,因為處理不當,很有可能拖垮整個資料庫機制的正常運作。

寫本文的初衷,只是想藉由這個例子,說明宣告變數類別、FOR 和 RETURN NEXT 的用法以及其語法與 Stored Procedure Function 的關係,除此之外,這例子於實際應用面來說是有待考量的。

所以,基於良心的建議立場,還是希望由前端的其他程式和服務做佈局的處理,於文章之後,我有附帶解釋並提醒。再次謝謝您的意見。

PostgreSQL & Google-Analytics Running...

::Planet PostgreSQL::

PostgreSQL Information Page

PostgreSQL日記(日本 石井達夫先生Blog)

PostgreSQL News

黑喵的家 - 資料庫相關

Google 網上論壇
PostgreSQL 8 DBA 專業指南中文版
書籍內容討論與更多下載區(造訪此群組)
目錄下載: PostgreSQL_8 _DBA_Index_zh_TW.pdf (更新:2007-05-18)

全球訪客分佈圖(Google)

全球訪客分佈圖(Google)