PHP Operations Oracle Database

Original source

(This is from the "Baidu Library" in the article written very good)

PHP Operations Oracle Database (OCI Data abstraction layer)
OCI (Oracle 8 call-interface) is a Database abstraction layer function built into PHP.
The following are examples of common tasks for connecting to an Oracle database operations 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 the function is a resource
Username, password: is the user name password of Oracle, i.e. scheme name and password
DB: is an optional parameter, if you use a local Oracle instance or use the Local service name registered in the Tnsnames.ora configuration file, give it a name directly.
If this parameter is left blank, the local oracle_sid is used or the default local service name of the registered tnsnames.ora file is searched.
CharSet: Parameters are parameters that are used in versions above Oracle9.2, which can be left blank by default and replaced with Nls_lang environment variables.
Session_mode: Used to set up login with privileged identity (disabled by default), there are 3 options by default: Oci_default,oci_sysoper and OCI_SYSDBA
Example: An Oracle instance that connects to a native registered local service named Hy, with a username/password of Scott/tiger

<?php//Establish Connection $conn=oci_connect ("Scott", "Tiger", "hy");//Check if the connection is successful if ($conn) {    echo "connect Success";} else{    echo "Connect error";}? >

2. Compile SQL statement: function: Oci_parse ()
Function: Resource Oci_parse (resource connection, string query)
Description: The function returns the value as a resource, compiling the SQL statement provided as a string
Connection: The link resource identifier created for the first step
Query: is a string of queries, enclosed in double quotation marks.
Configure query on connection and return the statement identifier for use with Oci_bind_by_name (), Oci_execute (), and other functions

<?php$stmt=oci_parse ($conn, "SELECT * from emp"); >

3, Execute SQL statement: function: Oci_execute ();
Function: BOOL Oci_execute (Resource stmt [, int mode])
Description: The function returns a Boolean value that executes a previously parsed statement
STMT: The compilation resource name created in the second step
Mode: Allows you to define the execution mode,
Oci_commit_on_success (default): Automatic commit when statement execution is successful
Oci_default: Automatically establishes a transaction that is automatically rolled back when the connection is closed or the script ends, and if you want to commit it, you need to explicitly call Oci_commit () to commit the transaction, or call the Oci_rollback () fallback transaction

<?phpoci_execute ($stmt, oci_default);? >

4, extract the results of the query:
function: int Oci_fetch_all (resource statement, array &output [, int skip [, int maxrows [, int flags]])
Extracts all the resulting data into an array (returns the number of rows that get the data)
Array Oci_fetch_array (resource statement [, int mode])
Extracts a row of the result data into an associative array (OCI_ASSOC) or a numeric index array (oci_num) or both (Oci_both), etc.
Array Oci_fetch_assoc (Resource statement)
Extracts a row of result data into an associative array
Object Oci_fetch_object (Resource statement)
Extracts a row of result data to an object
Array Oci_fetch_row (Resource statement)
Extracts a row of result data into a numeric index array

<?PHP$RESULT=OCI_FETCH_ASSOC ($stmt);p rint_r ($result);? >

5. Releasing Resources:
Function: BOOL Oci_free_statement (Resource statement)
To release all resources associated with a statement or cursor
BOOL Oci_close (Resource connection)
Close Oracle Database connection

<?phpoci_free_statement ($statement); Oci_close ($Oracle _conn);? >

======================= Basic query: Query with query criteria ===================
1, database connection (slightly)
2. Compiling SQL statements (slightly)
3. Binding variables and execution:
Functions: BOOL Oci_bind_by_name (Resource stmt, String ph_name, mixed &variable [, int maxlength [, int type]])
Bind the PHP variable variable to the location identifier Ph_name of Oracle. The length parameter determines the maximum size of the binding, if you want to bind a
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 (:d eptno) "; $statement =oci_parse ($Oracle _conn, $query);//Set the value of the binding variable $job=" clerk "; $deptno =10;oci_bind_ By_name ($statement, ": Job", $job) Oci_bind_by_name ($statement, ":d Eptno", $deptno);//EXECUTE Statement oci_execute ($statement); /Get Result data Oci_fetch_all ($statement, $result); foreach ($result as $rows) {    echo "";    foreach ($rows as $col _values) {    echo $col _values;       }} Release Resources oci_free_statement ($statement); Oci_close ($Oracle _conn);? >

========================= Data insertion Operation ===================
=======================1, providing values through variables ======================
1, database connection (slightly)
2. Compiling SQL statements (slightly)
3. Binding variables and execution:

<?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);//=============== Sets the value of the binding variable (supplied by the variable) ================ $empno =1203; $ename = ' TEST '; $sal =1500; $hiredate = ' March-December-81 ';/ If you are using Oracle database server time, Sysdateoci_bind_by_name ($statement, ": Empno", $empno) are provided directly in the DML statement; Oci_bind_by_name ($ Statement, ": ename", $ename); Oci_bind_by_name ($statement, ": Sal", $sal); Oci_bind_by_name ($statement, ": HireDate", $ HireDate);//execute statement, set execution mode to Autocommit oci_execute ($statement, oci_commit_on_success);//Check the number of rows affected if (oci_num_rows) {    echo " Insert succeeded ";} Release Resources oci_free_statement ($statement); Oci_close ($Oracle _conn);? >

=======================2, providing values through arrays ======================

<?php       $Oracle _conn=oci_connect ("Scott", "Tiger", "hy"); $query = "INSERT INTO Emps (Empno,ename) VALUES (: empno,: ename) "; $statement =oci_parse ($Oracle _conn, $query);//=============== sets the value of the binding variable (supplied by the array) ================ $data = Array (                    1884=> "A",                    1885=> "B",                    1886=> "C"); Oci_bind_by_name ($statement, ": Empno", $empno, +); o Ci_bind_by_name ($statement, ": ename", $ename, +); foreach ($data as $empno = = $ename) {        if (Oci_execute ($ Statement) {            echo "Insert succeeded". ";        }} Oci_free_statement ($statement); Oci_close ($Oracle _conn);               ? >

=======================php Call Stored Procedure =========================

<?php//Connection Database        $Oracle _conn=oci_connect ("Scott", "Tiger", "hy");/* The code that defines the calling statement, which executes a stored procedure with input parameters and returns a cursor, is as follows: Create or replace procedure Get_emp_inf (V_deptno in Emp.deptno%type,v_res out Sys_refcursor) isbegin    open v_res for SELECT * from EMP where deptno=v_deptno;end Get_emp_inf; The calling statement of the stored procedure is written as follows: Begin ... End and call two ways to *///assign and return a cursor handle $cur=oci_new_cursor ($Oracle _conn);//Create a calling statement $query= "calls Get_emp_inf (:d eptno,:v_cur)"; $statement =oci_parse ($Oracle _conn, $query);//provide input parameters $deptno=10;//bind the cursor handle, receive the returned cursor parameter oci_bind_by_name ($statement, ": Deptno ", $deptno, +); Oci_bind_by_name ($statement,": V_cur ", $cur, -1,oci_b_cursor);//execute Oci_execute ($statement);// Gets the cursor data returned to the cursor handle oci_execute ($cur);//traverse the cursor contents while ($dat = Oci_fetch_row ($cur)) {    var_dump ($dat);} Oci_free_statement ($statement); Oci_close ($Oracle _conn);                ? >

=======================php Call store function =========================

        <?php//Connect database $Oracle _conn=oci_connect ("Scott", "Tiger", "hy"); /* Defines the code for the calling statement (which executes a function with one input parameter) as follows:/* Create or Replace function chk_emp_exist (v_empno em        P.empno%type) return int as v_num int;        Begin SELECT COUNT (ROWID) into V_num from EMP where empno=v_empno;        If V_num<>0 then return 1;        else return-1;        End If;        End The calling statement of the stored function is written in two ways, the first using Begin...end, and the second can be called in a SELECT statement, but is limited to the *//Create CALL statement without the output parameter $query = "begin:res:= Chk_emp_exist (: empno);        end; ";        $statement =oci_parse ($Oracle _conn, $query);        Provide input parameters $empno = 10;        $res =-100;//return value may appear negative, so initialize with a negative value//bound variable, receive the returned parameter Oci_bind_by_name ($statement, ": res", $res);        Oci_bind_by_name ($statement, ": Empno", $empno);        Executive Oci_execute ($statement);        Determine if there is an if ($res ==1) {echo "This employee exists"; }       else{echo "This employee does not exist";        } oci_free_statement ($statement);                        Oci_close ($Oracle _conn); ?>

PHP Operations Oracle Database