如何处理光标异常，当SELECT查询返回"零"记录 [英] How to handle cursor exception when the select query returns "zero" records

问题描述

如何处理光标异常时，选择查询返回零"的记录

How to handle cursor exception when the select query returns "zero" records

我在过程中有一个游标，在游标初始化之后，我遍历游标以访问其中的数据.

I have a cursor in a procedure, and after cursor initialization I'm iterating through the cursor to access the data from it.

但是问题是，当游标选择查询返回0条记录时，它将引发异常

But the problem is when the cursor select query returns 0 records then it throws exception

ORA-06531:引用未初始化的集合.

ORA-06531: Reference to uninitialized collection.

如何处理此异常?

--- 程序代码

create or replace PROCEDURE BIQ_SECURITY_REPORT 
             (out_chr_err_code   OUT VARCHAR2,
              out_chr_err_msg    OUT VARCHAR2,
              out_security_tab   OUT return_security_arr_result   ,

             )
IS             

      l_chr_srcstage     VARCHAR2 (200);
      lrec               return_security_report;


CURSOR cur_security_data IS
    SELECT
    "ID"                  "requestId",
    "ROOM"                "room",
    "FIRST_NAME"          "FIRST_NAME",
    "LAST_NAME"           "LAST_NAME",

FROM
   "BI_REQUEST_CATERING_ACTIVITY" ;

   TYPE rec_security_data IS TABLE OF cur_security_data%ROWTYPE
   INDEX BY PLS_INTEGER;
   l_cur_security_data   rec_security_data;  


begin


      OPEN cur_security_data;

      LOOP      
         FETCH cur_security_data
         BULK COLLECT INTO l_cur_security_data
         LIMIT 1000;

         EXIT WHEN l_cur_security_data.COUNT = 0;

        lrec := return_security_report();
        out_security_tab  := return_security_arr_result(return_security_report());
        out_security_tab.delete;


   FOR i IN 1 .. l_cur_security_data.COUNT
       LOOP


        BEGIN  

           l_num_counter                := l_num_counter + 1;
            lrec                         := return_security_report();
       lrec.requestid              := l_cur_security_data(i).requestId ;                                   lrec.room                 := l_cur_security_data(i).room ;                                  lrec.firstName              := l_cur_security_data(i).firstName ;

               IF l_num_counter > 1 
                      THEN
                         out_security_tab.extend();
                         out_security_tab(l_num_counter) := return_security_report();
                 ELSE
              out_security_tab := return_security_arr_result(return_security_report());
                     END IF;
                     out_security_tab(l_num_counter) := lrec; 

        EXCEPTION             
                WHEN OTHERS 
               THEN
         DBMS_OUTPUT.PUT_LINE('Error occurred : '  || SQLERRM);
               END; 

           END LOOP;              
       END LOOP;              


   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE ('HERE INSIIDE OTHERS' || SQLERRM);
   END;

能否请您解释一下处理方式.

Can you please explain how handle it.

推荐答案

您必须使用out_security_tab，它是调用该过程的其他代码中的输出参数.

You must be using out_security_tab, which is an output parameter in some other code where the procedure is called.

在您的过程中，如果游标返回零行，则不会执行循环，并且您的代码甚至不会初始化out_security_tab，这将导致您面临的错误.

In your procedure, If cursor returns zero rows then the loop will not be executed and your code will not even initialize the out_security_tab which will lead to the error that you are facing.

有一个简单的方法可以避免:

There is a simple way to avoid:

1. 在循环外初始化out_security_tab －肯定会对其进行初始化
2. 您可以创建一个out variable，其中包含Y或N的详细信息，具体取决于游标行是否计数-不推荐

1. initialize out_security_tab outside the loop -- which will definitely initialize it
2. You can create one out variable containing details as Y or N based on if cursor rows count -- Not recommended

干杯！

这篇关于如何处理光标异常，当SELECT查询返回"零"记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！