首页 > 代码库 > PHP 使用 Oracle 分页方法
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=http://www.mamicode.com/"$pagedir&page=1\"><img src=http://www.mamicode.com/"../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\" onclick=\"location.href=http://www.mamicode.com/‘{$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=http://www.mamicode.com/"$pagedir&page=$PreviousPage\"><img src=http://www.mamicode.com/"../themes/Sugar/images/left1.png\" align=\"absmiddle\" border=\"0\" alt=\"Previous\" /></a>";
$pagecode .="<button type=\"button\" id=\"popupViewPrevButton\" title=\"Previous\" class=\"button\" onclick=\"location.href=http://www.mamicode.com/‘{$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\" onclick=\"location.href=http://www.mamicode.com/‘{$pagedir}&page={$NextPage}‘/">
<span align=\"absmiddle\" border=\"0\" class=\"spr_8770145eda4b0bf4f79175b595174ae2\" title=\"Next\"></span>
</button>
<button type=\"button\" id=\"popupViewEndButton\" title=\"End\" class=\"button\" onclick=\"location.href=http://www.mamicode.com/‘{$pagedir}&page={$pagecount}‘/">
<span align=\"absmiddle\" border=\"0\" class=\"spr_60d1e91e479a5648f0ca1a59e0f39c61\" title=\"End\"></span>
</button>";
//$pagecode .= "<a href=http://www.mamicode.com/"$pagedir&page=$NextPage\"><img src=http://www.mamicode.com/"../themes/Sugar/images/right1.png\" align=\"absmiddle\" border=\"0\" alt=\"Next\" /></a>";
//$pagecode .= "<a href=http://www.mamicode.com/"$pagedir&page=$pagecount\"><img src=http://www.mamicode.com/"../themes/Sugar/images/right2.png\" align=\"absmiddle\" border=\"0\" alt=\"End\" /></a> ";
}
$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;
$result = $this->pages( $newSQL, $pagedir, $_REQUEST[‘page‘], $count, 20);
PHP 使用 Oracle 分页方法