OCI8 函数
在线手册:中文  英文

oci_fetch_array

(PHP 5, PECL OCI8 >= 1.1.0)

oci_fetch_arrayReturns 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.

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

参数

statement

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

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.

范例

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($stidOCI_BOTH))) {
    
// 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

/*
  Before running, create the table:
      CREATE TABLE mytab (id NUMBER, description CLOB);
      INSERT INTO mytab (id, description) values (1, 'A very long string');
      COMMIT;
*/

$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($stidOCI_NUM))) {
    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

/*
  Before running, create the table:
      CREATE TABLE mytab (id NUMBER, description CLOB);
      INSERT INTO mytab (id, description) values (1, 'A very long string');
      COMMIT;
*/

$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($stidOCI_ASSOC))) {
    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 ($stidOCI_ASSOC))) { // Ignore NULLs
    
var_dump($row);
}

/*
The above code prints:
  array(1) {
    [1]=>
    string(1) "1"
  }
*/

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

/*
The above code prints:
  array(2) {
    [1]=>
    string(1) "1"
    ["NULL"]=>
    NULL
  }
*/

?>

Example #5 oci_fetch_array() with OCI_RETURN_LOBS

<?php

/*
  Before running, create the table:
      CREATE TABLE mytab (id NUMBER, description CLOB);
      INSERT INTO mytab (id, description) values (1, 'A very long string');
      COMMIT;
*/

$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($stidOCI_ASSOC+OCI_RETURN_LOBS))) {
    echo 
$row['ID'] . "<br>\n";
    echo 
$row['DESCRIPTION'] . "<br>\n"// this contains all of DESCRIPTION
}

// 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

/*
   Before running, create the table:
      CREATE TABLE mytab ("Name" VARCHAR2(20), city VARCHAR2(20));
      INSERT INTO mytab ("Name", city) values ('Chris', 'Melbourne');
      COMMIT;
*/

$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($stidOCI_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

/*
  Before running, create the tables:
      CREATE TABLE mycity (id NUMBER, name VARCHAR2(20));
      INSERT INTO mycity (id, name) values (1, 'Melbourne');
      CREATE TABLE mycountry (id NUMBER, name VARCHAR2(20));
      INSERT INTO mycountry (id, name) values (1, 'Australia');
      COMMIT;
*/

$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($stidOCI_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($stidOCI_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($stidOCI_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
/*
  Create the PL/SQL stored procedure as:

  CREATE OR REPLACE PROCEDURE myproc(p1 OUT SYS_REFCURSOR) AS
  BEGIN
    OPEN p1 FOR SELECT * FROM all_objects WHERE ROWNUM < 5000;
  END;
*/

$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, -1OCI_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($refcurOCI_ASSOC+OCI_RETURN_NULLS)) {
    echo 
"<tr>\n";
    foreach (
$row as $item) {
        echo 
"    <td>".($item !== null htmlentities($itemENT_QUOTES) : "&nbsp;")."</td>\n";
    }
    echo 
"</tr>\n";
}
echo 
"</table>\n";

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

?>

Example #10 oci_fetch_array() with 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);
}

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

// This nested query selects a subset of rows from $sql.
// In production environments, be careful to avoid SQL Injection
// issues with concatenated SQL statements
$limit_sql 
   
'select *
    from ( select a.*, rownum as rnum
        from (' 
$sql ') a
        where rownum < :FIRST_ROW + :NUM_ROWS )
    where rnum >= :FIRST_ROW'
;

$first 1;  // start with the first row
$num   5;  // return 5 rows
$stid oci_parse($conn$limit_sql);
oci_bind_by_name($stid':FIRST_ROW'$first);
oci_bind_by_name($stid':NUM_ROWS'$num);
oci_execute($stid);

while ((
$row oci_fetch_array($stidOCI_ASSOC))) {
    echo 
$row['CITY'] . " " $row['POSTAL_CODE'] . "<br>\n";
}

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

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.

参见


OCI8 函数
在线手册:中文  英文

用户评论:

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.

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.

易百教程