oracle 如何检查游标是否在oracle中返回任何记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10814111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if cursor returns any records in oracle?
提问by ashishjmeshram
I have a following stored procedure in which I have used a cursor. Depending on whether the cursor return any records or not I need to do some processing.
我有以下存储过程，其中使用了游标。根据游标是否返回任何记录，我需要做一些处理。
But I am not sure how to check if the cursor return any records.
但我不确定如何检查游标是否返回任何记录。
CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_LOOKUP_BY_EMP_ID
(
IN_USER_ID IN NUMBER,
IN_EMPLOYEE_ID NUMBER,
IN_HC_AS_ON_DATE VARCHAR2,
emp_cursor OUT SYS_REFCURSOR
)
IS
CURSOR employees IS
SELECT * FROM EMPLOYEE e;
BEGIN
if(record exist ) then
FOR employee IN employees
LOOP
// do something
END LOOP;
else if employees is empty then
// do something else
END;
回答by A.B.Cade
It's not possible to check if the cursor returns records without opening it.
(see here)
So you can either have some fast query just to see if there are records (using count for example),
无法在不打开游标的情况下检查游标是否返回记录。
（请参阅此处）
因此您可以进行一些快速查询以查看是否有记录（例如使用计数），
Or, you can do it like this:
或者，您可以这样做：
CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_LOOKUP_BY_EMP_ID
(
IN_USER_ID IN NUMBER,
IN_EMPLOYEE_ID NUMBER,
IN_HC_AS_ON_DATE VARCHAR2,
emp_cursor OUT SYS_REFCURSOR
)
IS
is_found_rec boolean := false;
CURSOR employees IS
SELECT * FROM EMPLOYEE e;
BEGIN
FOR employee IN employees
LOOP
is_found_rec := true;
// do something
END LOOP;
if not is_found_rec then
// do something else
end if;
END;
回答by Simon Dorociak
I think that it possible only with FETCH. Try to use
我认为只有使用FETCH. 尝试使用
if myCursor%found then
// some body
end if;
But if somebody know another way so correct me.
但是，如果有人知道另一种方式，请纠正我。
回答by guritaburongo
I like this way:
我喜欢这种方式：
DECLARE
CURSOR my_cur
IS
SELECT 'a' AS a FROM DUAL WHERE 1=1;
my_rec my_cur%rowtype;
BEGIN
OPEN my_cur;
LOOP
FETCH my_cur INTO my_rec;
IF my_cur%NOTFOUND
THEN
IF my_cur%ROWCOUNT=0
THEN
-- do stuff when cursor empty
DBMS_OUTPUT.PUT_LINE('NOTFOUND,RC=0' || '_' || my_cur%ROWCOUNT || '_' || my_rec.a);
END IF;
EXIT;
ELSE
-- do stuff when cursor not empty
DBMS_OUTPUT.PUT_LINE('FOUND,RC>0' || '_' || my_cur%ROWCOUNT || '_' || my_rec.a);
END IF;
END LOOP;
CLOSE MY_CUR;
END;
Output when cursor is 1=1 (not empty):
游标为 1=1（非空）时的输出：
FOUND,RC>0_1_a
Output when cursor is 1=0 (empty):
光标为 1=0（空）时的输出：
NOTFOUND,RC=0_0_
回答by alexherm
I like to use the same select query that comprises the cursor and select the count it returns into a variable. You can then evaluate the variable to determine what to do next. For example you have a cursor like this:
我喜欢使用包含游标的相同选择查询并选择它返回到变量中的计数。然后，您可以评估变量以确定下一步要做什么。例如，您有一个像这样的游标：
CURSOR c_example IS
SELECT field1
,field2
,field3
FROM table1 a
WHERE a.field1 LIKE '%something%';
Before you open the cursor to do any processing, select cursor result count into a variable.
在打开游标进行任何处理之前，选择游标结果计数到一个变量中。
SELECT count(*)
INTO v_exampleCount
FROM table1 a
WHERE a.field1 LIKE '%something%';
Now, again before you open cursor, do something like:
现在，再次在打开光标之前，执行以下操作：
IF exampleCount>0 THEN
FOR example IN c_example
LOOP
....
END LOOP; --end example loop
....
END IF; --end example if
