REF CURSORS let you return a set of query results to PHP. In PHP you bind an
OCI_B_CURSOR variable to a PL/SQL REFCURSOR procedure parameter and retrieve the
rows of the result set in a normal fetch loop.
As an example, we create a PL/SQL package with a procedure that queries the employees
table. The procedure returns a REF CURSOR containing the employees’ last names.
The PL/SQL procedure contains the code.
SQL> create or replace
2 procedure myproc(p1 out sys_refcursor) as
3 begin
4 open p1 for
5 select last_name from employees where rownum <= 5;
6 end;
7 /
In PHP the oci_new_cursor() function returns a REF CURSOR. This is bound to :rc in
the call to myproc(). Once the PL/SQL procedure has completed the value in $refcur is
treated like a prepared statement identifier. It is simply executed like a normal query and used
in a fetch loop.
navioo_refcur.php
<?php
$c = oci_connect('hr', 'hrpwd', '//localhost/XE');
// Excute the call to the PL/SQL stored procedure
$stid = oci_parse($c, "call myproc(:rc)");
$refcur = oci_new_cursor($c);
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
// Execute and fetch from the cursor
oci_execute($refcur);
echo '<table border="1">';
while($row = oci_fetch_assoc($refcur)) {
echo '<tr>';
foreach ($row as $c) {
echo "<td>$c</td>\n";
}
echo '</tr>';
}
echo '</table>';
?>
The output is:
Abel
Ande
Atkinson
Austin
Baer
Records returned by REF CURSORS cannot currently take advantage of row pre-fetching.
