PHP+Oracle(OCI) preliminary_PHP tutorial

WBOY

Release： 2016-07-15 13:23:22

Original

1057 people have browsed it

Start with Oracle (OCI)

With more and more PHP users choosing Oracle as their database, how to access the Oracle interface in the PHP release environment has become more and more important. . We'll start our journey with a quick look at a simple, basic and more explicit Oracle scenario. Oralce and Oralce8 use PHP's OCI8 function library. There is a brief description in the PHP manual (http://www.php.net/manual/ref.oci8.php): These functions allow you to access Oracle and Oracle7d databases. They use the Oracle8 Call-Interface (OCI8) . You will need Oracle8's client libraries to use these extensions.

These extension functions are more flexible than standard Oracle extension functions. It supports PHP global and local variables and integration with Oracle. It has complete LOB, FILE and ROWID support and allows you to define users. Supplementary variables.

From here on I will use "Oracle" to refer to any Oracle version, this article assumes you have PHP and Oracle installed and running. Oracle's help can be found at http://www.oracle.com or http://technet.oracle.com.

The point of this article is
1. Connect to ORALCLE
2. Build and run the SQL statement
3. Display the results
4. The limit/offset is close to the "mark page number" ” results

as an additional feature. You can take a look at how to accomplish a query that displays limits/offsets derived from a very large data set.

/*We will show a solution from a basic usage. The file used in this example is the new version of the article written by Rod Kreisler (http://www.phpbuilder.com/columns/rod20000221.php3). The new version of the file can be found at http://php.socket7.net. Set $offset to 0 if it is set to 0 or empty.
*/
if (empty($offset) || $offset $offset=0;
}

/*We will start from a basic use Show a plan. The file used in this example is the new version of the article written by Rod Kreisler (http://www.phpbuilder.com/columns/rod20000221.php3). The new version of the file can be found at http://php.socket7.net. Set $offset to 0 if it is set to 0 or empty.
*/
if (empty($offset) || $offset $offset=0;
}

$limit = 3;
/ *
Connect to Oralce. If you want, you can use ORACLE_SID directly when connecting to the database. Using PHP as CGI uses discontinuous connections. Now let's use this discontinuous connection in this example. In Windows this can be set in the registry, while in Unix you can use the putenv() function putenv("ORALCE_SID=ORASID");
Or, if you prefer, you can refer to it directly when connecting to the database. ORACLE_SID, let us refer directly to ORACLE_SID in this example.
If you use PHP as a module of APACHE, you can use persistent connections. While using non-persistent connections when using PHP as CGI, we use non-persistent connections in this example.
*/

$conn = OCILogon("user_name", "password", "ORASID");

/*
Error message. If no database is connected, an error message will be displayed and the execution of the script will exit.
*/

if (!$conn) {
echo "

ERROR - Could not connect to Oracle

";
exit;
}

/*
If the connection is successful, $conn is a connector. Otherwise, the script will end and output the "Could not connect to Oracle" error message
Now start analyzing and creating your SQL statements to get countable records from unlimited query results.
The format statement "Select count(*) from table_name" here is equivalent to count(), which is a SQL function that will be executed in the database. Better than returning results calculated by relying on PHP.
*/

$sql = "Select count(*) from table_name";

$stmt = OCIParse($conn, $sql);
if(!$stmt ) {
echo "

ERROR - Could not parse SQL statement.

";
exit;
}

/*
If you enter a wrong SQL statement or another error occurs, you will see the error message "Cannot parse SQL", $stmt is now a definition statement.
Now execute your analysis statement
*/

OCIExecute($stmt);

/*
The statement has now been executed, but as you can see Yes, no result identifier is returned from OCIExecute(), and the result returned by OCIParse() contains all the information required by Oracle.
Now start selecting the results of this query, $total rows[0] will contain a number. If no rows are returned, an error is displayed and the script exits.
*/

OCIFetchInto($stmt, &$total_rows);

if ( !$total_rows[0] ) {
echo "

Error - no rows returned !

";
exit;
}

/*
This code is optional but will make the returned results clearer, showing a message similar to "A total of 15 records, the 4th to 15th results are now displayed. "'s comments
*/

$begin =($offset+1);
$end = ($begin+($limit-1));
if ($end > $total_rows[0]) {
$end = $total_rows[0];
}

echo "There are $total_rows[0] results.
n";
echo "Now showing results $begin to $end.
n";

/*
Now it’s time to get down to business, release the original statement identifier and create the SQL statement. Analyze and execute SQL statements.
Be careful when writing code: unlike MYSQL, Oracle does not support restriction statements in SQL statements. As such, there are many ways to select specific rows, the best being to place the selection results in a temporary table or to "buffer" all results. Such methods are beyond the scope of this tutorial. We will use a simpler approach, explained further below.
*/
OCIFreeStatement($stmt);

$sql = "Select * from table_name";

$stmt = OCIParse($conn, $sql);

if(!$stmt) {
echo "

ERROR - Could not parse SQL statement.

";
exit;
}

OCIExecute($ stmt);

/*
Now display the result. The simplest way is to use this loop in the result collection. HTML will be used to display the final result, but this example is very simple, so we won't use any of it.
Note when writing code: As specified above, there is no limit in Oracle, so we must do this loop by taking out the results we want in all result sets and terminating the results we already have.
In this section, we will have a few different approaches, and there should actually be a better way to write this code. But while I think my approach is a bit unreadable, it does work.
*/

$i=0;
$j=0;
while( OCIFetchInto($stmt, &$result_array) ) {
if ($i>=$ offset) {
if ($j for ($k=0; $kecho $result_array[$k]. " ";
}
echo "
";
$j++;
}
}
$i++;
}
echo "
" ;

/*
The results will be displayed on the current page, now it is time to give the visitor a way to click to other pages using NEXT/PREV
*/

/ / Calculate the number of pages required for the result,
$pages = intval($total_rows[0]/$limit);

// $pages is now the total number of pages required excluding the remaining parts
if ($total_rows[0]%$limit) {
// has remainder so add one page
$pages++;
}

//The first one is displayed PREV connection is not displayed when page
if ($offset!=0) {
$prevoffset=$offset-$limit;
echo "