PHP operations on Oracle databases

PHP operations on Oracle databases (OCI data abstraction layer)
OCI (Oracle 8 call-Interface) is a built-in database abstraction layer function in PHP.
The following are examples of common tasks for connecting to an Oracle database to operate a database:
==================================== Basic query: unconditional query ==============================
1. Database Connection: function: oci_connect ()
Function: Resource oci_connect (string username, string password [, string DB [, string charset [, int session_mode])
Description: The return value of a function is a resource.
Username and password: the username and password of Oracle, that is, the solution name and password.
DB: an optional parameter. If you use a local Oracle instance or a local service name registered in the tnsnames. ora configuration file, provide the name directly.
If this parameter is left blank, use the local oracle_sid or search for the default local service name registered in the tnsnames. ora file.
Charset: this parameter is used in oracle9.2 or later versions. It can be left blank by default and replaced by the nls_lang environment variable.
Session_mode: used to set a privileged identity for Logon (disabled by default). Three options are available by default: oci_default, oci_sysoper, and oci_sysdba.
Example: connect to an oracle instance with the local service name hy registered on the local machine. The user name and password are Scott/tiger.
<? PHP
// Establish a connection
$ Conn = oci_connect ("Scott", "Tiger", "hy ");
// Check whether the connection is successful
If ($ conn ){
Echo "Connect success ";
}
Else {
Echo "Connect error ";
}
?>
2. Compile the SQL statement: function: oci_parse ()
Function: Resource oci_parse (resource connection, string query)
Description: The return value of a function is a resource, and the SQL statements provided in string form are compiled.
Connection: the identifier of the linked resource created in step 1.
Query: a query string that is enclosed in double quotation marks.
Configure the query on the connection and return the statement identifier for oci_bind_by_name (), oci_execute (), and other functions.
<? PHP
$ Stmt = oci_parse ($ Conn, "select * from EMP ");
?>
3. Execute the SQL statement: function: oci_execute ();
Function: bool oci_execute (resource stmt [, int mode])
Description: The function returns a Boolean value and executes a previously resolved statement.
Stmt: name of the compiled resource created in step 2
Mode: allows you to define the execution mode,
Oci_commit_on_success (default): The statement is automatically submitted if execution is successful.
Oci_default: automatically creates a transaction, which will be automatically rolled back after the connection is closed or the script ends. To submit a transaction, you must
Call oci_commit () to submit the transaction, or call oci_rollback () to roll back the transaction.
<? PHP
Oci_execute ($ stmt, oci_default );
?>
4. Extract the query results:
Function: int oci_fetch_all (resource statement, array & Output [, int skip [, int maxrows [, int flags])
Extract all the result data to the array (return the number of rows to obtain the data)
Array oci_fetch_array (resource statement [, int mode])
Extract a row of the result data to an associated array (oci_assoc), a digital Index Array (oci_num), or both (oci_both ).
Array oci_fetch_assoc (resource Statement)
Extract a row of the result data to an associated array
Object oci_fetch_object (resource Statement)
Extract a row of result data to an object
Array oci_fetch_row (resource Statement)
Extract a row of the result data to a numeric index array
<? PHP
$ Result = oci_fetch_assoc ($ stmt );
Print_r ($ result );
?>
5. release resources:
Function: bool oci_free_statement (resource Statement)
Release all resources associated with statements or cursors
Bool oci_close (resource connection)
Close Oracle Database Connection
<? PHP
Oci_free_statement ($ statement );
Oci_close ($ oracle_conn );
<?
==================================== Basic query: queries with query conditions =============================
1. Database Connection (omitted)
2. Compile SQL statements (omitted)
3. bind variables and execute:
Function: bool oci_bind_by_name (resource stmt, string ph_name, mixed & Variable [, int maxlength [, int type])
Bind the PHP variable to the location flag ph_name of oracle. The length parameter determines the maximum length of the binding. If you want to bind
ABSTRACT Data Type, using the type parameter
<? PHP
$ Oracle_conn = oci_connect ("Scott", "Tiger", "hy ");
$ Query = "select * from EMP where job = upper (: Job) and deptno = upper (: deptno )";
$ Statement = oci_parse ($ oracle_conn, $ query );
// Set the value of the bound variable
$ Job = "clerk ";
$ Deptno = 10;
Oci_bind_by_name ($ statement, ": job", $ job );
Oci_bind_by_name ($ statement, ": deptno", $ deptno );
// Execute the statement
Oci_execute ($ statement );
// Obtain result data
Oci_fetch_all ($ statement, $ result );
Foreach ($ result as $ rows ){
Echo "<br> ";
Foreach ($ rows as $ col_values ){
Echo $ col_values;
}
}
// Release resources
Oci_free_statement ($ statement );
Oci_close ($ oracle_conn );
?>
========================================================== =====
= ==============
1. Database Connection (omitted)
2. Compile SQL statements (omitted)
3. bind variables and execute:
<? PHP
$ Oracle_conn = oci_connect ("Scott", "Tiger", "hy ");
$ Query = "insert into EMPs (empno, ename, Sal, hiredate) values (: empno,: ename,: Sal,: hiredate )";
$ Statement = oci_parse ($ oracle_conn, $ query );
// ====================== Set the value of the bound variable (provide a value through the variable) ======================
$ Empno = 1203;
$ Ename = 'test ';
$ Sal = 1500;
$ Hiredate = '03-August-81 '; // If the Oracle database server time is used, sysdate is directly provided in the DML statement.
Oci_bind_by_name ($ statement, ": empno", $ empno );
Oci_bind_by_name ($ statement, ": ename", $ ename );
Oci_bind_by_name ($ statement, ": Sal", $ Sal );
Oci_bind_by_name ($ statement, ": hiredate", $ hiredate );
// Execute the statement and set the execution mode to automatic submission
Oci_execute ($ statement, oci_commit_on_success );
// Check the number of affected rows
If (oci_num_rows ){
Echo "successful insertion ";
}
// Release resources
Oci_free_statement ($ statement );
Oci_close ($ oracle_conn );
?>
=================================== 2. Provide the value through the array ================ ==============
<? PHP
$ Oracle_conn = oci_connect ("Scott", "Tiger", "hy ");
$ Query = "insert into EMPs (empno, ename) values (: empno,: ename )";
$ Statement = oci_parse ($ oracle_conn, $ query );
// ====================== Set the value of the bound variable (provide the value through the array) ======================
$ DATA = array (
1884 => "",
1885 => "B ",
1886 => "C ");
Oci_bind_by_name ($ statement, ": empno", $ empno, 32 );
Oci_bind_by_name ($ statement, ": ename", $ ename, 32 );
Foreach ($ data as $ empno => $ ename ){
If (oci_execute ($ statement )){
Echo "successfully inserted". "<br> ";
}
}
Oci_free_statement ($ statement );
Oci_close ($ oracle_conn );
?>
 

PHP operations on Oracle databases (OCI data abstraction layer) (2)
====================================== PHP call the Stored Procedure ================
<? PHP
// Connect to the database
$ Oracle_conn = oci_connect ("Scott", "Tiger", "hy ");

// Allocate and return a cursor handle
$ Cur = oci_new_cursor ($ oracle_conn );
// Create a call statement
$ Query = "Call get_emp_inf (: deptno,: v_cur )";
$ Statement = oci_parse ($ oracle_conn, $ query );
// Provide input parameters
$ Deptno = 10;
// Bind a cursor handle to receive the returned cursor Parameters
Oci_bind_by_name ($ statement, ": deptno", $ deptno, 16 );
Oci_bind_by_name ($ statement, ": v_cur", $ cur,-1, oci_ B _cursor );
// Execute
Oci_execute ($ statement );
// Obtain the returned cursor data to the cursor handle
Oci_execute ($ cur );
// Traverses the cursor content
While ($ dat = oci_fetch_row ($ cur )){
Var_dump ($ dat );
}
Oci_free_statement ($ statement );
Oci_close ($ oracle_conn );
?>
==================================== PHP call the storage function ================== ================
<? PHP
// Connect to the database
$ Oracle_conn = oci_connect ("Scott", "Tiger", "hy ");

// Create a call statement
$ Query = "begin: Res: = chk_emp_exist (: empno); end ;";
$ Statement = oci_parse ($ oracle_conn, $ query );
// Provide input parameters
$ Empno = 10;
$ Res =-100; // the return value may be negative, so a negative value is used during initialization.
// Bind the variable to receive the returned Parameters
Oci_bind_by_name ($ statement, ": res", $ res );
Oci_bind_by_name ($ statement, ": empno", $ empno );
// Execute
Oci_execute ($ statement );
// Determine whether the object exists
If ($ res = 1 ){
Echo "this employee exists ";
}
Else {
Echo "this employee does not exist ";
}
Oci_free_statement ($ statement );
Oci_close ($ oracle_conn );
?>