Oracle 数据库排序及分页

由于Oracle数据库不支持LIMIT/OFFSET,Oracle 数据库的分页只能通过子查询进行,如果还要排序,就要注意子查询的写法,以免影响性能.
本文以SQLalchemy + ox_Oracle + Oracle 11为例子,说明排序及分页.

    def query(self,kwargs):
        """以分页方式提取记录，参数：start 游标起始位置；size:每次返回的记录条数;
        fields:field list
        if size = 0,then不分页，返回所有记录集
        order_by(text("id"))
        --------------------------------
        //page是当前页序数，rows是显示行数
        int page=2;
        int rows=5;                            
        List<Articles> list=a.select(page*rows+1，(page-1)*rows);
        //  sql语句：  
        select * from(select a.*,rownum rn from (select * from t_articles) a where rownum < 11) where rn>5
　　　　　　//第一个参数，对应着第一个rownum<11,第二个参数对应着rn>5
        --------------------------------        
        """

        start = int(kwargs['start']) 
        size = int(kwargs['size'])
#         import pdb
#         pdb.set_trace()
        max = size + start + 1 
        keyword = kwargs['SearchableText']
        import os
        os.environ['NLS_LANG'] = '.AL32UTF8'        
        direction = kwargs['sort_order'].strip()        

        if size != 0:
            if keyword == "":
                if direction == "reverse":
                    selectcon = text("select * from"
                                     "(select a.*,rownum rn from "
                                     "(select * from admin_logs ORDER BY id DESC) a "
                                     "where rownum < :max) where rn > :start")
                    
#                     selectcon = text("select * from admin_logs  ORDER BY id DESC")
                else:
                    selectcon = text("select * from"
                                     "(select a.*,rownum rn from "
                                     "(select * from admin_logs ORDER BY id ASC) a "
                                     "where rownum < :max) where rn > :start")                    
#                     selectcon = text("select * from admin_logs  ORDER BY id ASC LIMIT :start,:size")

                recorders = session.query("adminid","userid","datetime",
                                      "ip","type","operlevel","description","result").\
                            from_statement(selectcon.params(start=start,max=max)).all()
            else:
                if direction == "reverse":
                    selectcon = text("select * from"
                                "(select a.*,rownum rn from "
                                "(select * from admin_logs where"
                                 " description LIKE :x "
                                  " OR userid LIKE :x"
                                  " OR adminid LIKE :x"
                                  " OR operlevel LIKE :x"
                                  " OR result LIKE :x"
                                  " OR ip LIKE :x"
                                  " ORDER BY id DESC ) a "
                                  "where rownum < :max) where rn > :start"
                                  )
                else:
                    selectcon = text("select * from"
                                "(select a.*,rownum rn from "
                                "(select * from admin_logs where"
                                 " description LIKE :x "
                                  " OR userid LIKE :x"
                                  " OR adminid LIKE :x"
                                  " OR operlevel LIKE :x"
                                  " OR result LIKE :x"
                                  " OR ip LIKE :x"
                                  " ORDER BY id ASC ) a "
                                  "where rownum < :max) where rn > :start"
                                  )
                recorders = session.query("adminid","userid","datetime",
                                      "ip","type","operlevel","description","result").\
                                      from_statement(selectcon.params(x=keyword,start=start,max=max)).all()  
                
        else:
            if keyword == "":
                selectcon = text("select * from admin_logs  order by id desc ")
                recorders = session.query("adminid","userid","datetime",
                                      "ip","type","operlevel","description","result").\
                            from_statement(selectcon).all()
            else:
                selectcon = text("select * from admin_logs where"
                                 " description LIKE :x "
                                  " OR userid LIKE :x"
                                  " OR adminid LIKE :x"
                                  " OR operlevel LIKE :x"
                                  " OR result LIKE :x"
                                  " OR ip LIKE :x"
                                  " order by id DESC ")

                recorders = session.query("adminid","userid","datetime",
                                      "ip","type","operlevel","description","result").\
                                      from_statement(selectcon.params(x=keyword)).all()  
            
            
            nums = len(recorders)
            return nums
        try:
#             session.commit()
            return recorders
        except:
            session.rollback()
            pass