Oracle PL/SQL:函数游标 [英] Oracle PL/SQL: Function Cursor

问题描述

我收到错误消息:

PLS-00231:函数'GET_NUM'不能在SQL中使用

PLS-00231: function 'GET_NUM' may not be used in SQL

当执行以下代码时；

CREATE OR REPLACE PACKAGE BODY TESTJNSABC IS

  -- FUNCTION IMPLEMENTATIONS
  FUNCTION get_num(num IN NUMBER) 
    RETURN SYS_REFCURSOR AS
    my_cursor SYS_REFCURSOR;
  BEGIN
    --
    OPEN my_cursor FOR 
    WITH ntable AS (
         SELECT 1 ID, 111 AGT, 'ABC' DESCRIP FROM DUAL
         UNION ALL
         SELECT 2 ID, 222 AGT, 'ABC' DESCRIP FROM DUAL
         UNION ALL
         SELECT 1 ID, 333 AGT, 'ABC' DESCRIP FROM DUAL
    )
    SELECT AGT FROM ntable WHERE ID = num;
    RETURN my_cursor;

  END;

  -- PROCEDURE IMPLEMENTATIONS 
  PROCEDURE testingabc AS

    BEGIN
      WITH xtable AS (
         SELECT 111 AGT, 'A' DESCRIP FROM DUAL
         UNION ALL
         SELECT 222 AGT, 'B' DESCRIP FROM DUAL
         UNION ALL
         SELECT 333 AGT, 'C' DESCRIP FROM DUAL
     )
      SELECT DESCRIP FROM xtable WHERE COD_AGT IN get_num(1);


    END testingabc;

END TESTJNSABC;

即使我将函数调用为TESTJNSABC.get_num(1)，我仍然会遇到相同的错误. - 更新.因此，在现实生活中，我想从WHERE子句中调用Function；该函数应该返回一组NUMBER值(这就是为什么我使用IN子句).

Even if I call the function as TESTJNSABC.get_num(1) I still get the same error. --UPDATE. So in real life scenario I would like to call a Function from a WHERE CLAUSE; the function should return a set of NUMBER values (that's why I use the IN clause).

推荐答案

in () requires either a subquery or a comma-separated list of values, so no, you can't substitute a function that returns a collection.

假设该函数在SQL查询范围内(它是独立函数或在包规范中声明)，则可以在table()构造中使用它(这需要一个表函数，即它需要返回一个集合) ，而不是光标):

Assuming the function is in scope for SQL queries (it's either a standalone function or declared in a package specification), you could use it in a table() construction (this needs a table function, i.e. it needs to return a collection, not a cursor):

where somecol in (select column_value from table(get_num(1)) )

(或等效的内部联接等)

(or the equivalent inner join etc.)

演示，位于 livesql.oracle.com/apex/livesql/file/content_EF2M0F1LV9LTP6PEII3BDFKAI .html

我刚刚注意到问题中的示例尝试使用ref游标.请注意，table()运算符适用于集合，而不适用于引用游标.因此，该函数必须返回集合类型(嵌套表或varray).

I've just noticed the example in the question tried to use a ref cursor. Note that the table() operator works on collections, not ref cursors. Therefore the function has to return a collection type (nested table or varray).

这篇关于Oracle PL/SQL:函数游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！