PHP 使用 Oracle 分页方法

/*

$sql: SQL 语句

$pagedir : 分页点击跳转地址

$page 当前页

$count 数据总数

$pagesize 每页显示条数

*/

function page( $sql, $pagedir, $page = 1, $count, $pagesize= 10) {
        $pagecode     = '';
        
        $page         = intval($page);                
        $count         = intval($count);            
        if ( !$count) return '';
        $pagecount     = ceil( $count/ $pagesize );
        
        if( $page < 1 ) $page = 1;
        if( $page > $pagecount ) $page = $pagecount;
        
        $pageStart     = $pagesize * ($page-1) + 1;
        $pageEnd     = $pagesize * $page ;
        if ($pageEnd > $count) {$pageEnd = $count;}
        
        //$pagecode =     "<a href=\"$pagedir&page=1\"><img src=\"../themes/Sugar/images/left2.png\" align=\"absmiddle\" border=\"0\" alt=\"Start\" /></a>";
        
        if ( $page == 1 || empty( $page) ) {
            $pagecode = "<button type=\"button\" id=\"popupViewStartButton\" title=\"Start\" class=\"button\" disabled=\"\">
                                    <span align=\"absmiddle\" border=\"0\" class=\"spr_60e88c7c4bc21ade9c3010b6abcdc0fd\" title=\"Start\"></span>
                                </button><button type=\"button\" id=\"popupViewPrevButton\" class=\"button\" disabled=\"\" title=\"Previous\">
                                    <span align=\"absmiddle\" border=\"0\" class=\"spr_85ff471105a04f2dd1f76dd22206dd14\" title=\"Previous\"></span>
                                </button>";
        } else {
            
            $pagecode = "<button type=\"button\" id=\"popupViewStartButton\" title=\"Start\" class=\"button\" οnclick=\"location.href='{$pagedir}&page=1'\">
                                    <span align=\"absmiddle\" border=\"0\" class=\"spr_e6223c471f8cf9df579462569fa9306c\" title=\"Start\"></span>
                                </button>";
            if ( $page > 1 ) $PreviousPage = $page -1; else $PreviousPage = 1;
            
            //$pagecode .= "<a href=\"$pagedir&page=$PreviousPage\"><img src=\"../themes/Sugar/images/left1.png\" align=\"absmiddle\" border=\"0\" alt=\"Previous\" /></a>";
            $pagecode .="<button type=\"button\" id=\"popupViewPrevButton\" title=\"Previous\" class=\"button\" οnclick=\"location.href='{$pagedir}&page={$PreviousPage}'\">
                        <span align=\"absmiddle\" border=\"0\" class=\"spr_a6fa65259296aaa5b94ded054464cf68\" title=\"Previous\"></span></button>";
        }
    
        //$pagecode .= "<span class=\"pageNumbers\">($pageStart - $pageEnd of $count)</span>";
        if ( $page < $pagecount ) $NextPage = $page + 1; else $NextPage = $pagecount;
        
        
        if ( $pagecount == $page ) {
            $pagecode .= "<span class=\"pageNumbers\">($pageStart - $pageEnd of $count)</span>";
            $pagecode .="<button type=\"button\" id=\"popupViewNextButton\" class=\"button\" title=\"Next\" disabled=\"\">
            <span align=\"absmiddle\" border=\"0\" class=\"spr_9c82366f2d3d35c243dd91706811b036\" title=\"Next\"></span>
            </button>
            <button type=\"button\" id=\"popupViewEndButton\" class=\"button\" disabled=\"\" title=\"End\">
            <span align=\"absmiddle\" border=\"0\" class=\"spr_17b56a3610caf7a7ad1aa7da7a984801\" title=\"End\"></span>
            </button>";
        } else {
            $pagecode .= "<span class=\"pageNumbers\">($pageStart - $pageEnd of {$pageEnd}+)</span>";
            $pagecode .= "<button type=\"button\" id=\"popupViewNextButton\" title=\"Next\" class=\"button\" οnclick=\"location.href='{$pagedir}&page={$NextPage}'\">
            <span align=\"absmiddle\" border=\"0\" class=\"spr_8770145eda4b0bf4f79175b595174ae2\" title=\"Next\"></span>
            </button>
            <button type=\"button\" id=\"popupViewEndButton\" title=\"End\" class=\"button\" οnclick=\"location.href='{$pagedir}&page={$pagecount}'\">
            <span align=\"absmiddle\" border=\"0\" class=\"spr_60d1e91e479a5648f0ca1a59e0f39c61\" title=\"End\"></span>
            </button>";
            //$pagecode .= "<a href=\"$pagedir&page=$NextPage\"><img src=\"../themes/Sugar/images/right1.png\" align=\"absmiddle\" border=\"0\"  alt=\"Next\" /></a>";
            //$pagecode .= "<a href=\"$pagedir&page=$pagecount\"><img src=\"../themes/Sugar/images/right2.png\"  align=\"absmiddle\" border=\"0\"  alt=\"End\" /></a>&nbsp;&nbsp;";
        }
        
        $result = array();
        $sql = str_replace( array( '{#pageStart#}', '{#pageEnd#}'), array( $pageStart, $pageEnd), $sql);
        
        $result = $this->oracle->GetArray( $sql );
        return array( 'pagecode'=>$pagecode, 'result'=> $result, 'pagecount'=>$pagecount);
    }

 

调用示例如下：

 

$count = $this->oracle->GetOne("select count(1) from (
        SELECT DISTINCT C.ID FROM ACCOUNTS A
        LEFT JOIN ACCOUNTS_CSTM B ON ( A.ID = B.ID_C )
        LEFT JOIN CASES C ON ( C.ACCOUNT_ID = A.ID )
        LEFT JOIN CASES_CSTM CS ON ( C.ID = CS.ID_C )
        WHERE A.DELETED = 0 AND C.DELETED =0 AND  B.MG_INSERTDATE_C < TO_DATE('{$this->existing_date}', '{$this->time_format}' )
        AND B.MG_SUPPLIER_STATUS_C = 'NON MSC'
        AND CS.MG_CASE_ORIGIN_C = 'eform_portal' $whereSQL ) tt ");
        
        $SQL = "SELECT DISTINCT C.ID , C.NAME NAME, A.NAME SUPPLIER_NAME, C.STATUS, CS.MG_CASE_SUBJTYPE_C CASE_SUBJECT_TYPE, TO_CHAR( C.DATE_ENTERED, 'DD.MM.YYYY HH24:MI:SS') CREATE_DATE,
        TO_CHAR( CS.MG_CASE_DUE_DATE_C, 'DD.MM.YYYY' ) DUE_DATE,
        (SELECT NAME FROM TEAMS WHERE DELETED = 0 AND ID =C.TEAM_ID ) TEAM_NAME  
        FROM ACCOUNTS A
        LEFT JOIN ACCOUNTS_CSTM B ON ( A.ID = B.ID_C )
        LEFT JOIN CASES C ON ( C.ACCOUNT_ID = A.ID )
        LEFT JOIN CASES_CSTM CS ON ( C.ID = CS.ID_C )
        WHERE A.DELETED = 0 AND C.DELETED =0 AND  B.MG_INSERTDATE_C < TO_DATE('{$this->existing_date}', '{$this->time_format}' )
        AND B.MG_SUPPLIER_STATUS_C = 'NON MSC'
        AND CS.MG_CASE_ORIGIN_C = 'eform_portal' $whereSQL ";

$newSQL = "select * from ( select aa.*, rownum rn from ($SQL) aa where rownum <= {#pageEnd#} ) where rn >= {#pageStart#}";
        
$pagedir = "funnel_list.php?action=non_msc_cases_list&para=".$para;
$result = $this->pages( $newSQL, $pagedir, $_REQUEST['page'], $count, 20);

 

转载于:https://www.cnblogs.com/cc2008/p/6865716.html