aci_fetch_array¶
aci_fetch_array-以关联数组或数字数组的形式返回查询的下一行
说明¶
aci_fetch_array(resource $statement, int $mode = ACI_BOTH | ACI_RETURN_NULLS): array|false
返回包含查询的下一个结果集行的数组。每个数组条目对应于行的一列。此函数通常在循环中调用，直到返回false，表示不再存在行。
若语句对应于返回数据库隐式结果集的PL/SQL块，则将连续提取所有集合中的行。如果aci_get_implicit_resultset（）返回语句，则只返回一个子查询的行子集。
要获取 ACI8公司扩展进行数据类型映射的细节，请参见驱动所支持的数据类型。
参数¶
有效的 ACI8标识符 由 aci_parse（）创建，被 aci_execute（）或 REF CURSOR语句标识执行。 也可以是aci_get_implicit_resultset（）返回的语句标识符。
可选的第二个参数可以是以下常量的任意组合：
| Contant | Description |
| aci_BOTH | 返回具有关联索引和数字索引的数组。这与aci_ASSOC+aci_NUM相同，是默认行为。 |
| aci_ASSOC | 返回关联数组。 |
| aci_NUM | 返回数值数组。 |
| aci_RETURN_NULLS | 为空字段创建元素。元素值将为PHP空。 |
| aci_RETURN_LOBS | 返回LOB的内容，而不是LOB描述符。 |
默认模式为aci_BOTH。
使用加法运算符“+”一次指定多个模式。
返回值¶
返回具有关联和/或数字索引的数组。如果语句中没有更多的行，则返回false。
默认情况下，LOB列作为LOB描述符返回。
DATE列作为字符串返回，格式为当前日期格式。可以使用Oracle环境变量（如NLS_LANG）或以前执行的ALTER SESSION SET NLS_DATE_format命令更改默认格式。
数据库的默认、不区分大小写的列名将在结果数组中具有大写的关联索引。区分大小写的列名将具有使用精确列大小写的数组索引。对结果数组使用var_dump（）来验证用于每个查询的适当大小写。
数组索引中不包括表名。如果查询包含两个名称相同的不同列，请使用aci_NUM或向查询添加列别名以确保名称的唯一性，请参见示例7。否则，只有一个列将通过PHP返回。
范例¶
示例 #1 aci_fetch_array() with aci_BOTH
<?php
$conn = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = aci_parse($conn, 'SELECT department_id, department_name FROM departments');
aci_execute($stid);
while (($row = aci_fetch_array($stid, aci_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";
}
aci_free_statement($stid);
aci_close($conn);
?>
示例 #2 aci_fetch_array() with aci_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 = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = aci_parse($conn, 'SELECT id, description FROM mytab');
aci_execute($stid);
while (($row = aci_fetch_array($stid, aci_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
aci_free_statement($stid);
aci_close($conn);
?>
示例 #3 aci_fetch_array() with aci_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 = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = aci_parse($conn, 'SELECT id, description FROM mytab');
aci_execute($stid);
while (($row = aci_fetch_array($stid, aci_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
aci_free_statement($stid);
aci_close($conn);
?>
示例 #4 aci_fetch_array() with aci_RETURN_NULLS
<?php
$conn = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = aci_parse($conn, 'SELECT 1, null FROM dual');
aci_execute($stid);
while (($row = aci_fetch_array ($stid, aci_ASSOC)) != false) { // Ignore NULLs
var_dump($row);
}
/*
The above code prints:
array(1) {
[1]=>
string(1) "1"
}
*/
$stid = aci_parse($conn, 'SELECT 1, null FROM dual');
aci_execute($stid);
while (($row = aci_fetch_array ($stid, aci_ASSOC+aci_RETURN_NULLS)) != false) { // Fetch NULLs
var_dump($row);
}
/*
The above code prints:
array(2) {
[1]=>
string(1) "1"
["NULL"]=>
NULL
}
*/
?>
示例 #5 aci_fetch_array() with aci_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 = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = aci_parse($conn, 'SELECT id, description FROM mytab');
aci_execute($stid);
while (($row = aci_fetch_array($stid, aci_ASSOC+aci_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
aci_free_statement($stid);
aci_close($conn);
?>
示例 #6 aci_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 = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = aci_parse($conn, 'select * from mytab');
aci_execute($stid);
$row = aci_fetch_array($stid, aci_ASSOC+aci_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
aci_free_statement($stid);
aci_close($conn);
?>
示例 #7 aci_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 = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_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 = aci_parse($conn, $sql);
aci_execute($stid);
$row = aci_fetch_array($stid, aci_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 = aci_parse($conn, $sql);
aci_execute($stid);
$row = aci_fetch_array($stid, aci_ASSOC);
var_dump($row);
// Output now contains both columns selected:
// array(2) {
// ["CTNM"]=>
// string(9) "Melbourne"
// ["NAME"]=>
// string(9) "Australia"
// }
aci_free_statement($stid);
aci_close($conn);
?>
示例 #8 aci_fetch_array() with DATE columns
<?php
$conn = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_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 = aci_parse($conn, "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'");
aci_execute($stid);
$stid = aci_parse($conn, 'SELECT hire_date FROM employees WHERE employee_id = 188');
aci_execute($stid);
$row = aci_fetch_array($stid, aci_ASSOC);
echo $row['HIRE_DATE'] . "<br>\n"; // prints 1997-06-14
aci_free_statement($stid);
aci_close($conn);
?>
示例 #9 aci_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 = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = aci_parse($conn, 'BEGIN myproc(:rc); END;');
$refcur = aci_new_cursor($conn);
aci_bind_by_name($stid, ':rc', $refcur, -1, aci_B_CURSOR);
aci_execute($stid);
// Execute the returned REF CURSOR and fetch from it like a statement identifier
aci_execute($refcur);
echo "<table border='1'>\n";
while (($row = aci_fetch_array($refcur, aci_ASSOC+aci_RETURN_NULLS)) != false) {
echo "<tr>\n";
foreach ($row as $item) {
echo " <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : " ")."</td>\n";
}
echo "</tr>\n";
}
echo "</table>\n";
aci_free_statement($refcur);
aci_free_statement($stid);
aci_close($conn);
?>
示例 #10 Pagination with aci_fetch_array() using a LIMIT-like query
<?php
$conn = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Find the version of the database
preg_match('/Release ([0-9]+)\./', aci_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 = aci_parse($conn, $sql);
aci_bind_by_name($stid, ':numrows', $numrows);
aci_bind_by_name($stid, ':offset', $offset);
aci_execute($stid);
while (($row = aci_fetch_array($stid, aci_ASSOC + aci_RETURN_NULLS)) != false) {
echo $row['CITY'] . " " . $row['POSTAL_CODE'] . "<br>\n";
}
// Output is:
// Beijing 190518
// Bern 3095
// Bombay 490231
// Geneva 1730
// Hiroshima 6823
aci_free_statement($stid);
aci_close($conn);
?>
示例 #11 aci_fetch_array() with Oracle Database Implicit Result Sets
<?php
$conn = aci_connect('sysdba', 'szoscar55', 'localhost:2003/OSRDB');
if (!$conn) {
$e = aci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$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 = aci_parse($conn, $sql);
aci_execute($stid);
// Note: aci_fetch_all and aci_fetch() cannot be used in this manner
echo "<table>\n";
while (($row = aci_fetch_array($stid, aci_ASSOC+aci_RETURN_NULLS)) != false) {
echo "<tr>\n";
foreach ($row as $item) {
echo " <td>".($item!==null?htmlentities($item, ENT_QUOTES|ENT_SUBSTITUTE):" ")."</td>\n";
}
echo "</tr>\n";
}
echo "</table>\n";
// Output is:
// Beijing 190518
// Bern 3095
// Bombay 490231
// CN
// CH
// IN
aci_free_statement($stid);
aci_close($conn);
?>
注释¶
注意:
对于使用不区分大小写的名称创建的标准列，关联数组索引需要大写。
注意:
查询返回巨大数量的数据行时，通过增大 aci8.默认_预取值或使用 aci_set_prefetch（）可显著提高性能。
注意:
函数aci_fetch_array（）比aci_fetch_assoc（）或aci_fetch_row（）慢得多，但更灵活。
参见¶
aci_fetch()-将下一行提取到结果缓冲区
aci_fetch_all()-获取结果数据的所有行到一个数组
aci_fetch_assoc()-将查询的下一行作为关联数组返回
aci_fetch_object()-将查询的下一行作为对象返回
aci_fetch_row()-以数字数组形式返回查询的下一行
aci_set_prefetch()-设置预提取行数