检查SYS_REFCURSOR是否为空的最佳方法 [英] Best way to check if SYS_REFCURSOR is empty

问题描述

我有一个游标，其中包含来自选择的值，我想根据是否找到任何行来做些什么.

I have a cursor with values from a select and i want to do something after depending if i had any row found or none.

recs_Table SYS_REFCURSOR;

begin

    open recs_Table for
       select * from table1, table2;


    if recs_Table%found then
        --do this
    else
        --do that
    end if;

end;

这似乎不起作用，有帮助吗?

This doesnt seem to work, any help?Ty

推荐答案

在使用％FOUND属性之前，您需要针对游标执行FETCH.将您的代码更改为类似

You need to execute a FETCH against the cursor prior to using the %FOUND attribute. Change your code to something like

DECLARE
  recs_Table SYS_REFCURSOR;
  nTable_1_value  NUMBER;
  nTable_2_value  NUMBER;
begin

    open recs_Table for
       select * from table1, table2;


    FETCH recs_Table INTO nTable_1_value, nTable_2_value;

    if recs_Table%found then
        --do this
    else
        --do that
    end if;

end;

请注意，您可能需要在FETCH语句的INTO子句中添加变量的方式，一种用于TABLE1和TABLE2中的每一列.还要注意，编写此游标的方式可能会获得比预期更多的返回行.因为没有指定连接条件，所以您将获得所谓的笛卡尔连接，其中TABLE1中的每一行都被连接到TABLE2中的每一行-因此，您将获得的行数是(TABLE1中的行数)* (表2中的行数).

Note that the way you'll probably need to add variables to the INTO clause of the FETCH statement, one for each column in TABLE1 and TABLE2. Note also that the way this cursor is written you'll probably get more rows returned than you might expect; because there is no join criteria specified you'll get what's called a Cartesian join, where each row in TABLE1 is joined to each row in TABLE2 - thus, the number of rows you'll get back is (# of rows in TABLE1) * (# of rows in TABLE2).

执行此操作的一种可能更简单的方法是使用游标FOR循环，如下所示:

A potentially simpler way to do this would be to use a cursor FOR loop, as follows:

DECLARE
  bData_found  BOOLEAN := FALSE;
begin
  FOR aRow IN (select * from table1, table2)
  LOOP
    -- If the program gets here, it means a row was fetched

    -- do this

    bData_found := TRUE;

    EXIT;  -- if you only care if data was found and don't want to 
           -- process all the rows
  END LOOP;

  IF NOT bData_found THEN
    -- do that
  END IF;
end;

分享并享受.

这篇关于检查SYS_REFCURSOR是否为空的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！