aci_get_implicit_resultset

aci_get_implicit_resultset-从具有数据库隐式结果集的父语句资源返回下一个子语句资源

说明

aci_get_implicit_resultset(resource $statement): resource|false

用于在执行存储的或匿名的PL/SQL块之后获取连续的查询结果集。RETURN_RESULT PL/SQL函数。这允许PL/SQL块轻松返回查询结果。

子语句可以与任何ACI8提取函数一起使用：aci_fetch()、aci_fetch_all()、aci_fetch_array()、aci_fetch_object()、aci_fetch_assoc() 或 aci_fetch_row()

子语句继承其父语句的预取值，也可以使用aci_set_prefetch（）显式设置。

参数

statement

由 aci_parse() 创建并由 aci_execute() 执行的有效OCI8语句标识符。 语句标识符可以与返回隐式结果集的SQL语句关联，也可以不关联。

返回值

返回语句上可用的下一个子语句的语句句柄。如果子语句不存在， 或者所有子语句都已通过先前对aci_get_implicit_resultset（）的调用返回，则返回false。

范例

示例 #1 Fetching Implicit Result Sets in a loop

<?php

$conn = aci_connect('hr', 'welcome', 'localhost/pdborcl');
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);

while (($stid_c = aci_get_implicit_resultset($stid))) {
        echo "<h2>New Implicit Result Set:</h2>\n";
        echo "<table>\n";
        while (($row = aci_fetch_array($stid_c, aci_ASSOC+aci_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:
//    New Implicit Result Set:
//     Beijing 190518
//     Bern    3095
//     Bombay  490231
//    New Implicit Result Set:
//     CN
//     CH
//     IN

aci_free_statement($stid);
aci_close($conn);

?>

示例 #2 Getting child statement handles individually

<?php

$conn = aci_connect('hr', 'welcome', 'localhost/pdborcl');
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);

$stid_1 = aci_get_implicit_resultset($stid);
$stid_2 = aci_get_implicit_resultset($stid);

$row = aci_fetch_array($stid_1, aci_ASSOC+aci_RETURN_NULLS);
var_dump($row);
$row = aci_fetch_array($stid_2, aci_ASSOC+aci_RETURN_NULLS);
var_dump($row);
$row = aci_fetch_array($stid_1, aci_ASSOC+aci_RETURN_NULLS);
var_dump($row);
$row = aci_fetch_array($stid_2, aci_ASSOC+aci_RETURN_NULLS);
var_dump($row);

// Output is:
//    array(2) {
//      ["CITY"]=>
//      string(7) "Beijing"
//      ["POSTAL_CODE"]=>
//      string(6) "190518"
//    }
//    array(1) {
//      ["COUNTRY_ID"]=>
//      string(2) "CN"
//    }
//    array(2) {
//      ["CITY"]=>
//      string(4) "Bern"
//      ["POSTAL_CODE"]=>
//      string(4) "3095"
//    }
//    array(1) {
//      ["COUNTRY_ID"]=>
//      string(2) "CH"
//    }

aci_free_statement($stid);
aci_close($conn);

?>

示例 #3 Explicitly setting the Prefetch Count

<?php

$conn = aci_connect('hr', 'welcome', 'localhost/pdborcl');
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 ORDER BY city;
                   DBMS_SQL.RETURN_RESULT(c1);
                END;';

$stid = aci_parse($conn, $sql);
aci_execute($stid);

$stid_c = aci_get_implicit_resultset($stid);
aci_set_prefetch($stid_c, 200);   // Set the prefetch before fetching from the child statement
echo "<table>\n";
while (($row = aci_fetch_array($stid_c, aci_ASSOC+aci_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";

aci_free_statement($stid);
aci_close($conn);

?>

示例 #4 Implicit Result Set example without using aci_get_implicit_resultset()

所有查询的所有结果都将连续返回。

<?php

$conn = aci_connect('hr', 'welcome', 'localhost/pdborcl');
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):"&nbsp;")."</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.default_prefetch 值或使用 aci_set_prefetch() 可显著提高性能。