PHPのお勉強！

PHP TOP
PHP 8.4.2 Released!

    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

    oci_connect() あるいは oci_pconnect() が返す Oracle 接続 ID。

    戻り値

    新しいステートメントハンドル、あるいはエラー時に false を返します。

    例

    例1 Oracle ストアドプロシージャにおける REF CURSOR のバインド

    <?php

    // 以下のプロシージャを用意します
    // 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); // REF CURSOR を、ステートメント 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);

    ?>

    Found A Problem?

    Learn How To Improve This Page • Submit a Pull Request • Report a Bug
    ＋add a note

    User Contributed Notes 3 notes

    up
    10
    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
    -3
    sixd at php dot net
    14 years ago
    Oracle 11.2 introduced support for REF CURSOR prefetching
    up
    -4
    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
    To Top