[Oracle] 페이징하기

최근 닷넷 개발 작업을 하면서 Oracle 에서도 페이징하는 부분을 만들어 보았다.

 

12개씩 뿌려주고,  1페이지의 내용을 불러오는 간단한 쿼리문이다.

SELECT *
FROM (
    SELECT rownum rn, A.*
    FROM (
        SELECT * FROM board ORDER BY NO desc
    ) A
) B
WHERE CEIL(B.rn / 12) = 1
ORDER BY B.rn ASC;

 

StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(keyword))
{
    sb.Append("Select * ");
    sb.Append("From ( ");
    sb.Append("  Select  ROWNUM RN, A.* ");
    sb.Append("  From ( ");
    sb.Append("  Select * From " + table_name + " Order by NO desc ) A ");
    sb.Append(") B Where 1=1 ");
    sb.Append("And CEIL(B.RN / " + showPage + ") = " + nowPage + " Order By B.RN ASC");
 
}
else
{
    sb.Append("Select B.*, RN ");
    sb.Append("From ( ");
    sb.Append("  Select A.*, ROWNUM RN ");
    sb.Append("  From ( ");
    sb.Append("         Select * ");
    sb.Append("         From " + table_name + " ");
    sb.Append("         Where 1=1 ");
    //sb.Append("         And " + keyfield + " Like '%" + keyword + "%' ");
    sb.Append("         And  (SUBJECT Like '%" + keyword + "%' or CONTENT Like '%" + keyword + "%') ");
    sb.Append("         Order by NO DESC ");
    sb.Append("       ) A ");
    sb.Append(") B Where 1=1 ");
    sb.Append("And CEIL(B.RN/ " + showPage + ") = " + nowPage + " Order By B.RN ASC");
}
 
string query = sb.ToString();

 

 

select * from 
    ( select A.* , rownum rn from 
        ( select * from board where 1=1 order by NO desc ) A 
        where rownum < 10 )
        where rn >= 1