如何使用 SYS_REFCURSOR 处理 NO DATA FOUND - ORACLE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8824930/
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 deal with NO DATA FOUND using SYS_REFCURSOR - ORACLE
提问by Ricardo Binns
how can i do if my select dont come with result using SYS_REFCURSOR?
如果我的选择没有使用结果，我该怎么办SYS_REFCURSOR？
what i have try so far is using NO_DATA_FOUND, but its not working, my STATUSkeep returning me as = 1
到目前为止我尝试过的是使用NO_DATA_FOUND，但它不起作用，我STATUS一直将我返回为 =1
code:
代码：
...
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;
any ideias ?
任何想法？
thanks!
谢谢！
the real sql is something like this:
真正的sql是这样的：
Edit
编辑
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;
回答by Eddie Awad
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.
在您的代码中，您只是打开游标而不是从中获取。当您打开一个游标时，PL/SQL 执行该游标的查询。它还标识满足 WHERE 子句中的条件和连接条件的行。OPEN 实际上并不检索任何这些行；该操作由 FETCH 语句执行。然后您将使用游标属性来检查结果集是否为空；如果是，则以下游标属性将具有以下值：%FOUND = FALSE、%NOTFOUND = TRUE 和 %ROWCOUNT = 0。
Here is an example:
下面是一个例子：
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.
回答by Bob Jarvis - Reinstate Monica
To slightly modify @Eddie Awad's answer, the usual code pattern I use for fetching from a cursor variable is as follows:
为了稍微修改@Eddie Awad 的答案，我用于从游标变量中获取的常用代码模式如下：
DECLARE
l_cur SYS_REFCURSOR;
l_col VARCHAR2 (10);
BEGIN
OPEN l_cur FOR
SELECT 'Hi there' col
FROM DUAL
WHERE 1 = 0;
DBMS_OUTPUT.PUT_LINE('Opened cursor');
<<cursor_loop>>
LOOP
FETCH l_cur INTO l_col;
DBMS_OUTPUT.PUT_LINE('Fetched from cursor');
EXIT cursor_loop WHEN l_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Process data fetched from cursor');
END LOOP; -- cursor_loop
CLOSE l_cur;
DBMS_OUTPUT.PUT_LINE('Closed cursor');
END;
The idea is to open the cursor variable (or get it back from a procedure), then loop until all rows have been fetched from the cursor.
这个想法是打开游标变量（或从过程中取回它），然后循环直到从游标中提取了所有行。
Share and enjoy.
分享和享受。
