Edit Report a Bug

    oci_new_cursor

    (PHP 5, PHP 7, PHP 8, PECL OCI8 >= 1.1.0)

    oci_new_cursor — 分配并返回新的游标（语句句柄）

    说明

    oci_new_cursor(resource $connection): resource|false

    在指定连接上分配新的语句句柄。

    参数

    connection

    Oracle 连接标识符，由 oci_connect() 或 oci_pconnect() 返回。

    返回值

    返回新的语句句柄，或者失败时返回 false。

    示例

    示例 #1 在调用 Oracle 存储过程中绑定 REF CURSOR

    <?php

    // Precreate:
    // create or replace procedure myproc(myrc out sys_refcursor) as
    // begin
    // open myrc for select first_name from employees;
    // end;

    $conn = oci_connect("hr", "hrpwd", "localhost/XE");
    if (!    $conn) {
    $m = oci_error();
    trigger_error(htmlentities($m['message']), E_USER_ERROR);
    }

    $curs = oci_new_cursor($conn);
    $stid = oci_parse($conn, "begin myproc(:cursbv); end;");
    oci_bind_by_name($stid, ":cursbv", $curs, -1, OCI_B_CURSOR);
    oci_execute($stid);

    oci_execute($curs); // Execute the REF CURSOR like a normal statement id
    while (($row = oci_fetch_array($curs, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    echo     $row['FIRST_NAME'] . "<br />\n";
    }

    oci_free_statement($stid);
    oci_free_statement($curs);
    oci_close($conn);

    ?>

    add a note add a note

    User Contributed Notes 3 notes

    up
    6
    mgumiel at mgait dot com
    12 years ago
    Some packages in oracle are functions, and that functions returns a cursor.

    For example:

    CREATE FUNCTION F_Function( p1 char(2), p2 int)
      RETURN SYS_REFCURSOR
    AS
      my_cursor SYS_REFCURSOR;
    BEGIN
      OPEN my_cursor FOR SELECT * FROM allitems
                               WHERE (cod=p1)
                                          AND (Number=p2);
      RETURN my_cursor;
    END F_Function;

    Here is the code that allows to obtain data from a function that returns a cursor.

    <pre>
    <?php
    $conn    =oci_connect("server", "user", "pass");
    if (!    $conn) {
            $e = oci_error();
            trigger_error(htmlentities($e['message']), E_USER_ERROR);
    }

            //You must asign before.
            $p1 = '03';
            $p2 = 2012016191;

            $stid = oci_parse($conn, 'begin :cursor := server.PKG_package.F_Function(:p1,:p2); end;');
            $p_cursor = oci_new_cursor($conn);

            //Send parameters variable  value  lenght
            oci_bind_by_name($stid, ':p1', $p1,2);
            oci_bind_by_name($stid, ':p2', $p2,10);

            //Bind Cursor     put -1
            oci_bind_by_name($stid, ':cursor', $p_cursor, -1, OCI_B_CURSOR);

            // Execute Statement
            oci_execute($stid);
            oci_execute($p_cursor, OCI_DEFAULT);

            oci_fetch_all($p_cursor, $cursor, null, null, OCI_FETCHSTATEMENT_BY_ROW);
        echo     '<br>';
            print_r($cursor);
    ?>
    up
    -1
    sixd at php dot net
    14 years ago
    Oracle 11.2 introduced support for REF CURSOR prefetching
    up
    -1
    sixd at php dot net
    16 years ago
    Because OCI8 uses "prefetching" to greatly improve returning query results, but Oracle doesn't support prefetching for REF CURSORs, application performance using REF CURSORs can be greatly improved by writing a PL/SQL function that pulls data from the REF CURSOR and PIPEs the output. The new function can be queried in a SELECT as if it were a table.  See http://blogs.oracle.com/opal/2008/11/
    converting_ref_cursor_to_pipe.html
    add a note add a note
    To Top