如何从PLSQL中的函数返回一组数字，然后在FOR LOOP中使用它? [英] How to return a set of NUMBERS from a Function in PLSQL and then use it in a FOR LOOP?

问题描述

我想在PLSQL中有一个函数，该函数将返回一组数字，然后有一个for循环，该循环将遍历该数据集并对其进行处理.

I wanted to have a function in PLSQL that would return an set of numbers, and then have a for loop that would iterate over that dataset and do something with it.

有什么建议吗?该函数是否需要成为管道函数才能在for循环中使用?即使我只是返回数字，我还需要创建一个新类型吗?

Any suggestions ? Does the function need to be a pipeline function for it to be used in the for loop? Do I need to create a new type even though Im just returning numbers?

谢谢！

 CREATE OR REPLACE PACKAGE BODY someBody AS

      FUNCTION getListOfNumbers RETURN someList IS -- what type do I return ??
      BEGIN
        RETURN SELECT SID FROM V$SESSION;  -- Not sure what do here ??
      END;

      PROCEDURE soSomeStuff IS
      BEGIN
        FOR rec IN(getListOfNumbers)  -- how do I select from the function?
        LOOP
          dbms_output.put_line(rec);
        END LOOP;
      END;
  END;

推荐答案

您需要声明然后您将使用如下定义的类型:

You would then use the defined type like this:

SQL> CREATE OR REPLACE PACKAGE BODY my_package AS
  2  
  3     FUNCTION getListOfNumbers RETURN someList IS
  4        l_list someList;
  5     BEGIN
  6        SELECT SID BULK COLLECT INTO l_list FROM V$SESSION;
  7        RETURN l_list;
  8     END;
  9  
 10     PROCEDURE soSomeStuff IS
 11        l_list someList;
 12     BEGIN
 13        l_list := getListOfNumbers;
 14        FOR i IN 1..l_list.count LOOP
 15           dbms_output.put_line(l_list(i));
 16        END LOOP;
 17     END;
 18  
 19  END my_package;
 20  /

Package body created

SQL> exec my_package.soSomeStuff;

284
285
287
288
[...]
PL/SQL procedure successfully completed

这篇关于如何从PLSQL中的函数返回一组数字，然后在FOR LOOP中使用它?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！