如何使用SYS_REFCURSOR处理未找到的数据-ORACLE [英] how to deal with NO DATA FOUND using SYS_REFCURSOR - ORACLE

问题描述

如果我选择的结果不包含使用SYS_REFCURSOR的结果怎么办?

how can i do if my select dont come with result using SYS_REFCURSOR ?

到目前为止，我尝试使用的是NO_DATA_FOUND，但无法正常工作，我的STATUS始终以= 1

what i have try so far is using NO_DATA_FOUND, but its not working, my STATUS keep returning me as = 1

代码:

...   
MYVARIABLE IN OUT SYS_REFCURSOR
...

OPEN MYVARIABLE FOR
     SELECT NAME FROM TABLE WHERE COD = 1; 
     STATUS := 1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
      STATUS := 0;

任何想法?

谢谢！

真正的sql是这样的:

the real sql is something like this:

修改

V_SQL := 'SELECT SUM(T1.VLRLIQ) VALOR,T1.CODCLI,T1.NOMCLI
     ,(SELECT METCLI FROM WEB_CRM_CLIVEN T2
     WHERE T2.CODCLI = T1.CODCLI AND T2.CODVEN = '|| P_CODVEN ||'
      AND T2.MES = '|| V_MES ||' AND T2.ANO = '|| V_ANO ||') META
      FROM SAPIENS.USU_VRESNFV T1,SAPIENS.E085CLI T2
    WHERE T1.CODVEN = '|| P_CODVEN ||'
     AND TO_CHAR(T1.DATEMI,''YYYY'') = '|| V_ANO ||'
     AND TO_CHAR(T1.DATEMI,''MM'') = '|| V_MES ||'
     AND T1.VENFAT = ''S''
     '|| V_CGCCPF ||'
     '|| V_NOMCLI ||'
     AND T2.CODCLI = T1.CODCLI
     AND T1.CODEMP = 1
     GROUP BY T1.CODCLI,T1.NOMCLI
     UNION
   SELECT SUM(''0'') VALOR,CODCLI,NOMCLI,(SELECT METCLI FROM WEB_CRM_CLIVEN T3
      WHERE T3.CODCLI = T2.CODCLI AND T3.CODVEN = '|| P_CODVEN ||'
      AND T3.MES = '|| V_MES ||' AND T3.ANO = '|| V_ANO ||') META
      FROM SAPIENS.E085CLI T2
    WHERE
     CODCLI IN (SELECT CODCLI FROM WEB_CRM_VEN_CARTEIRA
      WHERE CODVEN = '|| P_CODVEN ||' AND MES = '|| V_MES ||' AND ANO = '|| V_ANO ||')
      '|| V_CGCCPF ||'
      '|| V_NOMCLI ||'
     GROUP BY CODCLI,NOMCLI
     ORDER BY VALOR DESC';

     STATUS := 1;

     OPEN RESULTADO FOR V_SQL;

推荐答案

在您的代码中，您只是打开游标，而不是从中获取游标.当您打开游标时，PL/SQL将对该游标执行查询.它还标识满足WHERE子句和连接条件中的条件的行. OPEN实际上不会检索任何这些行；该动作是由FETCH语句执行的.然后，您将使用游标属性检查结果集是否为空.如果是，则以下光标属性将具有以下值:％FOUND = FALSE，％NOTFOUND = TRUE和％ROWCOUNT = 0.

In your code, you are just opening the cursor but not fetching from it. When you open a cursor, PL/SQL executes the query for that cursor. It also identifies the rows that meet the criteria in the WHERE clause and join conditions. The OPEN does not actually retrieve any of these rows; that action is performed by the FETCH statement. You would then use cursor attributes to check if the result set is empty; if it is, then the following cursor attributes would have these values: %FOUND = FALSE, %NOTFOUND = TRUE, and %ROWCOUNT = 0.

这里是一个例子:

     SQL> DECLARE
       2     l_cur   SYS_REFCURSOR;
       3     l_col   VARCHAR2 (10);
       4  BEGIN
       5     OPEN l_cur FOR
       6        SELECT 'Hi there' col
       7          FROM DUAL
       8         WHERE 1 = 0;
       9
      10     DBMS_OUTPUT.put_line ('Opened cursor');
      11
      12     FETCH l_cur INTO l_col;
      13
      14     DBMS_OUTPUT.put_line ('Fetched from cursor');
      15
      16     IF l_cur%NOTFOUND
      17     THEN
      18        DBMS_OUTPUT.put_line ('Oops! No data found. Raising exception...');
      19        RAISE NO_DATA_FOUND;
      20     END IF;
      21
      22     CLOSE l_cur;
      23  EXCEPTION
      24     WHEN NO_DATA_FOUND
      25     THEN
      26        DBMS_OUTPUT.put_line ('Exception raised.');
      27  END;
      28  /
     Opened cursor
     Fetched from cursor
     Oops! No data found. Raising exception...
     Exception raised.

     PL/SQL procedure successfully completed.

这篇关于如何使用SYS_REFCURSOR处理未找到的数据-ORACLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！