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
说明
$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 ): " " ). "</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 ): " " ). "</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 ): " " ). "</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() 可显著提高性能。