oci_get_implicit_resultset

(PECL OCI8 >= 2.0.0)

oci_get_implicit_resultset — Returns the next child statement resource from a parent statement resource that has Oracle Database 12c Implicit Result Sets

说明

resource oci_get_implicit_resultset ( resource $statement )

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 Oracle's 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
//     IN

 oci_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 statement
 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" ;

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

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