一个ORACLE分页程序,挺实用的.

  •   2009-08-01/08:12
  • <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
    <HTML>
    <HEAD>
    <TITLE>Paging Test</TITLE>
    <META NAME="Generator" CONTENT="TextPad 4.0">
    <META NAME="Author" CONTENT="?">
    <META NAME="Keywords" CONTENT="?">
    <META NAME="Description" CONTENT="?">
    </HEAD>

    <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#FF0000" VLINK="#800000" ALINK="#FF00FF" BACKGROUND="?">
    <?php

    // How to split the result into pages, like 'limits' in MySQL?
    // ===========================================================
    // Tutorial by Neil Craig (neilc@netactive.co.za)
    // Date: 2001-06-05
    // With this example, I will explain paging of database queries where the
    // result is more than the developer want to print to the page, but wish to
    // split the result into seperate pages.
    // The table "SAMPLE_TABLE" accessed in this tutorial has 4 fields:
    // PK_ID, FIELD1, FIELD2 and FIELD3. The types don't matter but you should
    // define a primary key on the PK_ID field.

    $display_rows = 5;     // The rows that should be display at a time. You can
                           // modify this if you like.

    // Connect to the Oracle database
    putenv("ORACLE_SID=purk");
    putenv("ORACLE_HOME=/export/oracle8i");
    putenv("TNS_ADMIN=$ORACLE_HOME/network/admin");
    $OracleDBConn = OCILogon("purk","purk","lengana.world");

    // This query counts the records
    $sql_count = "SELECT COUNT(*) FROM SAMPLE_TABLE";

    // Parse the SQL string & execute it
    $row_count=OCIParse($OracleDBConn, $sql_count);       
    OCIExecute($row_count);

    // From the parsed & executed query, we get the amount of records found.
    // I'm not storing this result into a session variable because it allows for
    // new records to be shown as it is entered by another user while the result
    // is printed.
    if (OCIFetch($row_count)) {
        $num_rows = OCIResult($row_count,1);
    } else {
        $num_rows = 0;        // If no record was found
    }

    // Free the resources that were used for this query
    OCIFreeStatement($row_count);

    // We need to prepare the query that will print the results as a page. I will
    // explain the query to you in detail.

    // If no page was specified in the url (ex. http://mysite.com/result.php?page=2),
    // set it to page 1.
    if (empty($page) || $page == 0) {
        $page = 1;
    }

    // The start range from where the results should be printed
    $start_range = (($page - 1) * $display_rows) + 1;

    // The end range to where the results should be printed
    $end_range = $page * $display_rows;

    // The main query. It consists of 3 "SELECT" statements nested into each
    // other. The center query is the query you would normally use to return the
    // records you want. Do you ordering and "WHERE" clauses in this statement.
    // We select the rows to limit our results but because the row numbers are
    // assigned to the rows before any ordering is done, lets the code print the
    // result unsorted.
    // The second nested "SELECTED" assigns the new row numbers to the result
    // for us to select from.

    $sql = "SELECT PK_ID, FIELD1, FIELD2, FIELD3, ROW_NO FROM (SELECT PK_ID, ";
    $sql .= "FIELD1, FIELD2, FIELD3, ROWNUM ROW_NO FROM (SELECT PK_ID, FIELD1, ";
    $sql .= "FIELD2, FIELD3 FROM SAMPLE_TABLE ORDER BY FIELD3)) WHERE ROW_NO BETWEEN ";
    $sql .= $start_range." AND ".$end_range;

    // start results formatting
    echo "<table width='95%' border='1' cellspacing='1' cellpadding='2' align='center'>";
    echo "<tr bgcolor='#666666'>";
    echo "<td><b><font color='#FFFFFF'>PK ID</font></b></td>";
    echo "<td><b><font color='#FFFFFF'>Field 1</font></b></td>";
    echo "<td><b><font color='#FFFFFF'>Field 2</font></b></td>";
    echo "<td><b><font color='#FFFFFF'>Field 3</font></b></td>";
    echo "<td><b><font color='#FFFFFF'>Row No</font></b></td>";
    echo "</tr>";

    if ($num_rows != 0) {

        // Parse the SQL string & execute it
        $rs=OCIParse($OracleDBConn, $sql);       
        OCIExecute($rs);
        
        // get number of columns for use later
        $num_columns = OCINumCols($rs);
        
        while (OCIFetch($rs)){
            echo "<tr>";
            for ($i = 1; $i < ($num_columns + 1); $i++) {
                $column_value = OCIResult($rs,$i);
                echo "<TD>$column_value</TD>";
            }
            echo "</tr>";
        }
            
    } else {

        // Print a message stating that no records was found
        echo "<tr><td align=center>Sorry! No records was found</td></tr>";
    }

    // Close the table
    echo "</TABLE>";

    // free resources and close connection
    OCIFreeStatement($rs);
    OCILogoff($OracleDBConn);

    ?>
    <div align=center>
    <?php

    // Here we will print the links to the other pages

    // Calculating the amount of pages
    if ($num_rows % $display_rows == 0) {
        $total_pages = $num_rows / $display_rows;
    } else {
        $total_pages = ($num_rows / $display_rows) + 1;
        settype($total_pages, integer); // Rounding the variable
    }

    // If this is not the first page print a link to the previous page
    if ($page != 1) {
        echo "<a href='".$PHP_SELF."?page=".($page - 1)."'>Previous</a>";
    }

    // Now we can print the links to the other pages
    for ($i = 1; $i <= $total_pages;  $i++) {
        if ($page == $i){
            // Don't print the link to the current page
            echo " ".$i;
        } else {
            //Print the links to the other pages
            echo " <a href='".$PHP_SELF."?page=".$i."'>".$i."</a>";
        }
    }

    // If this is not the last page print a link to the next page
    if ($page < $total_pages) {
        echo " <a href='".$PHP_SELF."?page=".($page + 1)."'>Next</a>";
    }

    ?>
    </div>
    <?php

    // I'm just adding this section to print some of the variables for extra info
    // and some debugging

    echo "<p><b>Total pages: </b>".$total_pages."</p>";
    echo "<p><b>Number of records: </b>".$num_rows."</p>";
    echo "<p><b>The SQL Query is:</b> ".$sql."</p>";

    ?>
    </BODY>
    </HTML>

    评论 {{userinfo.comments}}

    {{money}}

    {{question.question}}

    A {{question.A}}
    B {{question.B}}
    C {{question.C}}
    D {{question.D}}
    提交

    驱动号 更多