从PLSQL函数获取值的数组 [英] Get a array of values form a PLSQL Function

问题描述

我编写了一个函数，用于选择队列中的某些流量并使用更新的标志锁定它们. 我用游标制作而成，效果很好.但是我需要获取锁定的流量的ID才能在应用程序中对其进行处理.

I coded a function to select some flux in a queue and lock them with an updated flag. I made it with a cursor and it worked great. But i need to get the ID of the flux i locked to process them in my application.

所以我开始编写函数代码:

So i start to code a function:

CREATE OR REPLACE Function getIDArray
  RETURN VARCHAR2 is
  arr varchar2(1000);

          CURSOR flux_to_process                                                          
          IS                                                                              
           SELECT  FLUX_ID, LOCKED_FLAG
           FROM (
               SELECT FLUX_ID, FLUX, GROUP_STORE_ID, STORE_ID, REFID, FLUX_TYPE, LOCKED_FLAG
                 FROM DEV_ISB_TRANSACTIONS.BUFFER_FLUX                                      
                 WHERE status = 0                                                           
                 AND LOCKED_FLAG = 0                                                                                                             
                 ORDER BY DATE_CREATION ASC)
            WHERE ROWNUM <= 8;

          BEGIN                                                                           

          FOR flux_rec IN flux_to_process                                                 
              LOOP                                                                        
              IF flux_rec.LOCKED_FLAG = 0
                THEN      

                UPDATE DEV_ISB_TRANSACTIONS.BUFFER_FLUX                                    
                SET LOCKED_FLAG = 1                                                         
                WHERE FLUX_ID = flux_rec.FLUX_ID; 
                arr := flux_rec.FLUX_ID;

              else exit;
          COMMIT;                                                                         
          END IF;
          END LOOP; 
          RETURN arr;
         END;

函数编译返回OK，但是我没有返回值.

The function compilation return an OK but i got no return of my values.

你们对如何做到这一点有任何了解吗?

Do you guys have any clue to how to do this ?

推荐答案

关于您自己的问题，我看到函数返回无值"的唯一两个原因是:

Concerning your issue per se, the only two reasons I can see for the function to return "no value" would be either:

• SELECT部分返回一个 empty 集，
• 您有一条记录，其中FLUX_ID是NULL.

• the SELECT part returns an empty set,
• you have one record where FLUX_ID is NULL.

对于不太可能的是在给定列名称的情况下使用该选项的情况，这与以下事实相当一致:您在每次迭代时都覆盖结果-并且ORDER BY在not- NULL进行排序>默认情况下.

For improbable that could be that later option given the name of the column, it would be rather coherent with the fact that you override the result at each iteration -- and the ORDER BY orders NULL after not-NULL by default.

这篇关于从PLSQL函数获取值的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！