oci_fetch_array

(PHP 5, PECL OCI8 >= 1.1.0)

oci_fetch_array — Returns the next row from a query as an associative or numeric array

说明

array oci_fetch_array ( resource $statement [, int $mode ] )

Returns an array containing the next result-set row of a query. Each array entry corresponds to a column of the row. This function is typically called in a loop until it returns FALSE , indicating no more rows exist.

If statement corresponds to a PL/SQL block returning Oracle Database 12c Implicit Result Sets, then rows from all sets are consecutively fetched. If statement is returned by oci_get_implicit_resultset() , then only the subset of rows for one child query are returned.

要获取 OCI8 扩展进行数据类型映射的细节，请参见驱动所支持的数据类型。

参数

statement

有效的 OCI8 报表标识符由 oci_parse() 创建，被 oci_execute() 或 REF CURSOR statement 标识执行。

Can also be a statement identifier returned by oci_get_implicit_resultset() .

mode

An optional second parameter can be any combination of the following constants:

**oci_fetch_array()** Modes
Constant	Description
`OCI_BOTH`	Returns an array with both associative and numeric indices. This is the same as `OCI_ASSOC` + `OCI_NUM` and is the default behavior.
`OCI_ASSOC`	Returns an associative array.
`OCI_NUM`	Returns a numeric array.
`OCI_RETURN_NULLS`	Creates elements for `NULL` fields. The element values will be a PHP `NULL` .
`OCI_RETURN_LOBS`	Returns the contents of LOBs instead of the LOB descriptors.

The default mode is OCI_BOTH .

Use the addition operator "+" to specify more than one mode at a time.

返回值

Returns an array with associative and/or numeric indices. If there are no more rows in the statement then FALSE is returned.

By default, LOB columns are returned as LOB descriptors.

DATE columns are returned as strings formatted to the current date format. The default format can be changed with Oracle environment variables such as NLS_LANG or by a previously executed ALTER SESSION SET NLS_DATE_FORMAT command.

Oracle's default, non-case sensitive column names will have uppercase associative indices in the result array. Case-sensitive column names will have array indices using the exact column case. Use var_dump() on the result array to verify the appropriate case to use for each query.

The table name is not included in the array index. If your query contains two different columns with the same name, use OCI_NUM or add a column alias to the query to ensure name uniqueness, see example #7. Otherwise only one column will be returned via PHP.

范例

Example #1 oci_fetch_array() with OCI_BOTH

  <?php

$conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/XE' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 $stid  =  oci_parse ( $conn ,  'SELECT department_id, department_name FROM departments' );
 oci_execute ( $stid );

while (( $row  =  oci_fetch_array ( $stid ,  OCI_BOTH )) !=  false ) {
     // Use the uppercase column names for the associative array indices
     echo  $row [ 0 ] .  " and "  .  $row [ 'DEPARTMENT_ID' ]   .  " are the same<br>\n" ;
    echo  $row [ 1 ] .  " and "  .  $row [ 'DEPARTMENT_NAME' ] .  " are the same<br>\n" ;
}

 oci_free_statement ( $stid );
 oci_close ( $conn );

 ?>

Example #2 oci_fetch_array() with OCI_NUM

  <?php

 

 $conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/XE' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 $stid  =  oci_parse ( $conn ,  'SELECT id, description FROM mytab' );
 oci_execute ( $stid );

while (( $row  =  oci_fetch_array ( $stid ,  OCI_NUM )) !=  false ) {
    echo  $row [ 0 ] .  "<br>\n" ;
    echo  $row [ 1 ]-> read ( 11 ) .  "<br>\n" ;  // this will output first 11 bytes from DESCRIPTION
 }

 // Output is:
//    1
//    A very long

 oci_free_statement ( $stid );
 oci_close ( $conn );

 ?>

Example #3 oci_fetch_array() with OCI_ASSOC

  <?php

 

 $conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/XE' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 $stid  =  oci_parse ( $conn ,  'SELECT id, description FROM mytab' );
 oci_execute ( $stid );

while (( $row  =  oci_fetch_array ( $stid ,  OCI_ASSOC )) !=  false ) {
    echo  $row [ 'ID' ] .  "<br>\n" ;
    echo  $row [ 'DESCRIPTION' ]-> read ( 11 ) .  "<br>\n" ;  // this will output first 11 bytes from DESCRIPTION
 }

 // Output is:
//    1
//    A very long

 oci_free_statement ( $stid );
 oci_close ( $conn );

 ?>

Example #4 oci_fetch_array() with OCI_RETURN_NULLS

  <?php

$conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/XE' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 $stid  =  oci_parse ( $conn ,  'SELECT 1, null FROM dual' );
 oci_execute ( $stid );
while (( $row  =  oci_fetch_array  ( $stid ,  OCI_ASSOC )) !=  false ) {  // Ignore NULLs
     var_dump ( $row );
}

 

 $stid  =  oci_parse ( $conn ,  'SELECT 1, null FROM dual' );
 oci_execute ( $stid );
while (( $row  =  oci_fetch_array  ( $stid ,  OCI_ASSOC + OCI_RETURN_NULLS )) !=  false ) {  // Fetch NULLs
     var_dump ( $row );
}

 

 ?>

Example #5 oci_fetch_array() with OCI_RETURN_LOBS

  <?php

 

 $conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/XE' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 $stid  =  oci_parse ( $conn ,  'SELECT id, description FROM mytab' );
 oci_execute ( $stid );

while (( $row  =  oci_fetch_array ( $stid ,  OCI_ASSOC + OCI_RETURN_LOBS )) !=  false ) {
    echo  $row [ 'ID' ] .  "<br>\n" ;
    echo  $row [ 'DESCRIPTION' ] .  "<br>\n" ;  // this contains all of DESCRIPTION
    // In a loop, freeing the large variable before the 2nd fetch reduces PHP's peak memory usage
     unset( $row ); 
}

 // Output is:
//    1
//    A very long string

 oci_free_statement ( $stid );
 oci_close ( $conn );

 ?>

Example #6 oci_fetch_array() with case sensitive column names

  <?php

 

 $conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/XE' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 $stid  =  oci_parse ( $conn ,  'select * from mytab' );
 oci_execute ( $stid );
 $row  =  oci_fetch_array ( $stid ,  OCI_ASSOC + OCI_RETURN_NULLS );

 // Because 'Name' was created as a case-sensitive column, that same
// case is used for the array index.  However uppercase 'CITY' must
// be used for the case-insensitive column index
 print  $row [ 'Name' ] .  "<br>\n" ;    //  prints Chris
 print  $row [ 'CITY' ] .  "<br>\n" ;    //  prints Melbourne

 oci_free_statement ( $stid );
 oci_close ( $conn );

 ?>

Example #7 oci_fetch_array() with columns having duplicate names

  <?php

 

 $conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/XE' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 $sql  =  'SELECT mycity.name, mycountry.name
        FROM mycity, mycountry
        WHERE mycity.id = mycountry.id' ;
 $stid  =  oci_parse ( $conn ,  $sql );
 oci_execute ( $stid );
 $row  =  oci_fetch_array ( $stid ,  OCI_ASSOC );
 var_dump ( $row );

 // Output only contains one "NAME" entry:
//    array(1) {
//      ["NAME"]=>
//      string(9) "Australia"
//    }

// To query a repeated column name, use an SQL column alias like "AS ctnm":
 $sql  =  'SELECT mycity.name AS ctnm, mycountry.name 
        FROM mycity, mycountry 
        WHERE mycity.id = mycountry.id' ;
 $stid  =  oci_parse ( $conn ,  $sql );
 oci_execute ( $stid );
 $row  =  oci_fetch_array ( $stid ,  OCI_ASSOC );
 var_dump ( $row );

 // Output now contains both columns selected:
//    array(2) {
//      ["CTNM"]=>
//      string(9) "Melbourne"
//      ["NAME"]=>
//      string(9) "Australia"
//    }


 oci_free_statement ( $stid );
 oci_close ( $conn );

 ?>

Example #8 oci_fetch_array() with DATE columns

  <?php

$conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/XE' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 // Set the date format for this connection.
// For performance reasons, consider changing the format
// in a trigger or with environment variables instead
 $stid  =  oci_parse ( $conn ,  "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'" );
 oci_execute ( $stid );

 $stid  =  oci_parse ( $conn ,  'SELECT hire_date FROM employees WHERE employee_id = 188' );
 oci_execute ( $stid );
 $row  =  oci_fetch_array ( $stid ,  OCI_ASSOC );
echo  $row [ 'HIRE_DATE' ] .  "<br>\n" ;   // prints 1997-06-14

 oci_free_statement ( $stid );
 oci_close ( $conn );

 ?>

Example #9 oci_fetch_array() with REF CURSOR

  <?php
 

 $conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/XE' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 $stid  =  oci_parse ( $conn ,  'BEGIN myproc(:rc); END;' );
 $refcur  =  oci_new_cursor ( $conn );
 oci_bind_by_name ( $stid ,  ':rc' ,  $refcur , - 1 ,  OCI_B_CURSOR );
 oci_execute ( $stid );

 // Execute the returned REF CURSOR and fetch from it like a statement identifier
 oci_execute ( $refcur );  
echo  "<table border='1'>\n" ;
while (( $row  =  oci_fetch_array ( $refcur ,  OCI_ASSOC + OCI_RETURN_NULLS )) !=  false ) {
    echo  "<tr>\n" ;
    foreach ( $row  as  $item ) {
        echo  "    <td>" .( $item  !==  null  ?  htmlentities ( $item ,  ENT_QUOTES ) :  "&nbsp;" ). "</td>\n" ;
    }
    echo  "</tr>\n" ;
}
echo  "</table>\n" ;

 oci_free_statement ( $refcur );
 oci_free_statement ( $stid );
 oci_close ( $conn );

 ?>

Example #10 Pagination with oci_fetch_array() using a LIMIT-like query

  <?php

$conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/XE' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 // Find the version of the database
 preg_match ( '/Release ([0-9]+)\./' ,  oci_server_version ( $conn ),  $matches );
 $oracleversion  =  $matches [ 1 ];

 // This is the query you want to "page" through
 $sql  =  'SELECT city, postal_code FROM locations ORDER BY city' ;

if ( $oracleversion  >=  12 ) {
     // Make use of Oracle 12c OFFSET / FETCH NEXT syntax
     $sql  =  $sql  .  ' OFFSET :offset ROWS FETCH NEXT :numrows ROWS ONLY' ;
} else {
     // Older Oracle versions need a nested query selecting a subset
    // from $sql.  Or, if the SQL statement is known at development
    // time, consider using a row_number() function instead of this
    // nested solution.  In production environments, be careful to
    // avoid SQL Injection issues with concatenation.
     $sql  =  "SELECT * FROM (SELECT a.*, ROWNUM AS my_rnum
                           FROM ( $sql ) a
                           WHERE ROWNUM <= :offset + :numrows)
            WHERE my_rnum > :offset" ;
}

 $offset   =  0 ;   // skip this many rows
 $numrows  =  5 ;   // return 5 rows
 $stid  =  oci_parse ( $conn ,  $sql );
 oci_bind_by_name ( $stid ,  ':numrows' ,  $numrows );
 oci_bind_by_name ( $stid ,  ':offset' ,  $offset );
 oci_execute ( $stid );

while (( $row  =  oci_fetch_array ( $stid ,  OCI_ASSOC  +  OCI_RETURN_NULLS )) !=  false ) {
    echo  $row [ 'CITY' ] .  " "  .  $row [ 'POSTAL_CODE' ] .  "<br>\n" ;
}

 // Output is:
//    Beijing 190518
//    Bern 3095
//    Bombay 490231
//    Geneva 1730
//    Hiroshima 6823

 oci_free_statement ( $stid );
 oci_close ( $conn );

 ?>

Example #11 oci_fetch_array() with Oracle Database 12c Implicit Result Sets

  <?php

$conn  =  oci_connect ( 'hr' ,  'welcome' ,  'localhost/pdborcl' );
if (! $conn ) {
     $e  =  oci_error ();
     trigger_error ( htmlentities ( $e [ 'message' ],  ENT_QUOTES ),  E_USER_ERROR );
}

 // Requires OCI8 2.0 and Oracle Database 12c
// Also see oci_get_implicit_resultset()
 $sql  =  'DECLARE
           c1 SYS_REFCURSOR;
        BEGIN
           OPEN c1 FOR SELECT city, postal_code FROM locations WHERE ROWNUM < 4 ORDER BY city;
           DBMS_SQL.RETURN_RESULT(c1);
           OPEN c1 FOR SELECT country_id FROM locations WHERE ROWNUM < 4 ORDER BY city;
           DBMS_SQL.RETURN_RESULT(c1);
        END;' ;

 $stid  =  oci_parse ( $conn ,  $sql );
 oci_execute ( $stid );

 // Note: oci_fetch_all and oci_fetch() cannot be used in this manner
 echo  "<table>\n" ;
while (( $row  =  oci_fetch_array ( $stid ,  OCI_ASSOC + OCI_RETURN_NULLS )) !=  false ) {
    echo  "<tr>\n" ;
    foreach ( $row  as  $item ) {
        echo  "  <td>" .( $item !== null ? htmlentities ( $item ,  ENT_QUOTES | ENT_SUBSTITUTE ): "&nbsp;" ). "</td>\n" ;
    }
    echo  "</tr>\n" ;
}
echo  "</table>\n" ;

 // Output is:
//    Beijing 190518
//    Bern    3095
//    Bombay  490231
//    CN
//    CH
//    IN

 oci_free_statement ( $stid );
 oci_close ( $conn );

 ?>

注释

Note:

Associative array indices need to be in uppercase for standard Oracle columns that were created with case insensitive names.

Note:

查询返回巨大数量的数据行时，通过增大 oci8.default_prefetch 值或使用 oci_set_prefetch() 可显著提高性能。

Note:

The function oci_fetch_array() is insignificantly slower than oci_fetch_assoc() or oci_fetch_row() , but is more flexible.

参见

oci_fetch() - Fetches the next row into result-buffer
oci_fetch_all() - 获取结果数据的所有行到一个数组
oci_fetch_assoc() - Returns the next row from a query as an associative array
oci_fetch_object() - Returns the next row from a query as an object
oci_fetch_row() - Returns the next row from a query as a numeric array
oci_set_prefetch() - 设置预提取行数

用户评论:

[#1] Maxwell_Smart at ThePentagon dot com [2002-08-09 12:29:55]

When using OCI_RETURN_LOBS to get a BFILE (stored with a DIRECTORY) the user needs READ on the DIRECTORY. (GRANT READ on DIRECTORY <directory name> TO <user>;) Otherwise, you'll get a cryptic error. Warning: OCILobFileOpen: ORA-22285: non-existent directory or file for FILEOPEN operation in ... on line ...
<BR>
The user that CREATEs the DIRECTORY is automatically GRANTed READ WITH THE GRANT OPTION.

[#2] junk at netburp dot com [2000-10-19 02:39:26]

Here's a clue about rowid.

Don't forget about the oracle functions:

"rowidtochar" and "chartorowid"

"select rowidtochar(rowid) as FOO from table ...."

When you want to pass the rowid in a form or link, that's
the only way to go.