Php/docs/function.oci-get-implicit-resultset

来自菜鸟教程
跳转至:导航、​搜索

oci_get_implicit_resultset

(PHP 5 >= 5.6.0, PHP 7, PECL OCI8 >= 2.0.0)

oci_get_implicit_resultsetReturns the next child statement resource from a parent statement resource that has Oracle Database Implicit Result Sets


说明

oci_get_implicit_resultset ( resource $statement ) : resource

Used to fetch consectutive sets of query results after the execution of a stored or anonymous Oracle PL/SQL block where that block returns query results with the Oracle Database 12 (or later) DBMS_SQL.RETURN_RESULT PL/SQL function. This allows PL/SQL blocks to easily return query results.

The child statement can be used with any of the OCI8 fetching functions: oci_fetch(), oci_fetch_all(), oci_fetch_array(), oci_fetch_object(), oci_fetch_assoc() or oci_fetch_row()

Child statements inherit their parent statement's prefetch value, or it can be explicitly set with oci_set_prefetch().


参数

statement
A valid OCI8 statement identifier created by oci_parse() and executed by oci_execute(). The statement identifier may or may not be associated with a SQL statement that returns Implicit Result Sets.


返回值

Returns a statement handle for the next child statement available on statement. Returns false when child statements do not exist, or all child statements have been returned by previous calls to oci_get_implicit_resultset().


范例

Example #1 Fetching Implicit Result Sets in a loop

<?php$conn = oci_connect('hr', 'welcome', 'localhost/pdborcl');if (!$conn) {    $e = oci_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 = oci_parse($conn, $sql);oci_execute($stid);while (($stid_c = oci_get_implicit_resultset($stid))) {    echo "<h2>New Implicit Result Set:</h2>\n";    echo "<table>\n";    while (($row = oci_fetch_array($stid_c, 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://    New Implicit Result Set://     Beijing 190518//     Bern    3095//     Bombay  490231//    New Implicit Result Set://     CN//     CH//     INoci_free_statement($stid);oci_close($conn);?>

Example #2 Getting child statement handles individually

<?php$conn = oci_connect('hr', 'welcome', 'localhost/pdborcl');if (!$conn) {    $e = oci_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 = oci_parse($conn, $sql);oci_execute($stid);$stid_1 = oci_get_implicit_resultset($stid);$stid_2 = oci_get_implicit_resultset($stid);$row = oci_fetch_array($stid_1, OCI_ASSOC+OCI_RETURN_NULLS);var_dump($row);$row = oci_fetch_array($stid_2, OCI_ASSOC+OCI_RETURN_NULLS);var_dump($row);$row = oci_fetch_array($stid_1, OCI_ASSOC+OCI_RETURN_NULLS);var_dump($row);$row = oci_fetch_array($stid_2, OCI_ASSOC+OCI_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"//    }oci_free_statement($stid);oci_close($conn);?>

Example #3 Explicitly setting the Prefetch Count

<?php$conn = oci_connect('hr', 'welcome', 'localhost/pdborcl');if (!$conn) {    $e = oci_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 = oci_parse($conn, $sql);oci_execute($stid);$stid_c = oci_get_implicit_resultset($stid);oci_set_prefetch($stid_c, 200);   // Set the prefetch before fetching from the child statementecho "<table>\n";while (($row = oci_fetch_array($stid_c, 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";oci_free_statement($stid);oci_close($conn);?>

Example #4 Implicit Result Set example without using oci_get_implicit_resultset()

All results from all queries are returned consecutively.


<?php$conn = oci_connect('hr', 'welcome', 'localhost/pdborcl');if (!$conn) {    $e = oci_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 = oci_parse($conn, $sql);oci_execute($stid);// Note: oci_fetch_all and oci_fetch() cannot be used in this mannerecho "<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//    INoci_free_statement($stid);oci_close($conn);?>

注释

Note:

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