检查sys_refcursor中的值 [英] Check Values in sys_refcursor

问题描述

我在函数中有以下代码

        CREATE OR REPLACE FUNCTION my_func (
        v_dt events.raised_date%TYPE
        )
        RETURN SYS_REFCURSOR
    IS    
            p_events    SYS_REFCURSOR;

        OPEN p_events FOR
                SELECT  event_id
                  FROM  events
                 WHERE  raised_date = v_dt;
RETURN p_events;
END;

我想检查p_events光标中是否存在100.如何在我的函数中执行此操作.

I would like to check whether 100 exists in p_events cursor or not. How can I do this inside my function.

任何帮助都是非常重要的.

Any help is highly appreciable.

推荐答案

在函数内部检查它不是一个好主意.您不知道为什么返回游标.而是在函数外部执行此操作.

It is not good idea to check it inside of the function. You are missing why the cursor is returned. Instead do it outside of the function.

DECLARE
    l_rc SYS_REFCURSOR := my_func();

    TYPE events_ntt IS TABLE OF NUMBER;
    l_events  events_ntt;

    l_lookup  events_ntt := events_ntt(100);
    l_diff    events_ntt;
BEGIN
    FETCH l_rc BULK COLLECT INTO l_events;

    l_diff := l_events MULTISET INTERSECT DISTINCT l_lookup;

    IF l_diff.COUNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE('100 EXISTS');
    ELSE
        DBMS_OUTPUT.PUT_LINE('100 DOES NOT EXIST');
    END IF;
END;

使用游标变量(REF CURSOR)

Using Cursor Variables (REF CURSORs)

像游标一样，游标变量指向该行中的当前行. 多行查询的结果集.游标变量更灵活 因为它不受特定查询的约束.你可以打开一个游标 任何返回正确的列集的查询的变量.

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. A cursor variable is more flexible because it is not tied to a specific query. You can open a cursor variable for any query that returns the right set of columns.

您将游标变量作为参数传递给本地并存储 子程序.在一个子程序中打开光标变量，然后 在不同的子程序中处理它，有助于集中数据 恢复.此技术对于多语言也很有用 PL/SQL子程序可能将结果集返回给应用程序的应用程序 用其他语言(例如Java或Visual)编写的子程序 基本.

You pass a cursor variable as a parameter to local and stored subprograms. Opening the cursor variable in one subprogram, and processing it in a different subprogram, helps to centralize data retrieval. This technique is also useful for multi-language applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language, such as Java or Visual Basic.

什么是游标变量(REF CURSOR)?

What Are Cursor Variables (REF CURSORs)?

游标变量就像指向结果集的指针.您在以下情况下使用它们 您想要在一个子程序中执行查询并处理结果 在不同的子程序中(可能是用不同的子程序编写的 语言).游标变量的数据类型为REF CURSOR，您可能 看到它们被非正式地称为REF CURSOR.

Cursor variables are like pointers to result sets. You use them when you want to perform a query in one subprogram, and process the results in a different subprogram (possibly one written in a different language). A cursor variable has datatype REF CURSOR, and you might see them referred to informally as REF CURSORs.

与显式游标不同，该游标始终引用相同的查询工作 区域，光标变量可以引用不同的工作区域.你不能 在需要光标的地方使用光标变量，反之亦然.

Unlike an explicit cursor, which always refers to the same query work area, a cursor variable can refer to different work areas. You cannot use a cursor variable where a cursor is expected, or vice versa.

来源: http://docs.oracle. com/cd/B19306_01/appdev.102/b14261/sqloperations.htm#i7106

(《 Oracle数据库PL/SQL用户指南和参考》)

(Oracle Database PL/SQL User's Guide and Reference)

这篇关于检查sys_refcursor中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！