http://www.eygle.com/archives/2005/10/oracle_howto_use_decode_function.html
TABLE:
TABLE EEP.TERMINFO
(
TERMID CHAR(1 BYTE),
SEQNO INTEGER,
CURRFLAG CHAR(1 BYTE),
SETDATE DATE
)
DATA:
SCRIPT:
Declare
l_case varchar2(2); --選擇變數
TYPE tt IS ref cursor;
CUR tt ;
l_data TERMINFO%ROWTYPE;
BEGIN
l_case := 'C'; --篩選條件
OPEN CUR FOR
SELECT * FROM TERMINFO
ORDER BY
DECODE (l_case,'A',TERMID) DESC, --如果l_case =A以TERMID欄位排序
DECODE (l_case,'B',SEQNO) DESC, --如果l_case =B以SEQNO欄位排序
SETDATE ASC; --其他以SETDATE欄位排序
loop
Fetch CUR into l_data;
exit when CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_data.TERMID);
end loop;
END;
----------------------------------------------------------
結果:
Case A
E
D
C
B
A
Case B
A
B
C
D
E
Case C
D
B
A
C
E
沒有留言:
張貼留言