oracle 存储过程（带游标）如何工作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/817472/
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 do oracle stored procedures (w/ cursors) work?
提问by David
I have a following oracle stored procedure
我有以下 oracle 存储过程
CREATE OR REPLACE
PROCEDURE getRejectedReasons
(
p_cursor IN OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_cursor FOR SELECT * FROM reasons_for_rejection;
END;
However, when I run this stored procedure in sql-developer then I dont see anything. I just see something like this:
但是，当我在 sql-developer 中运行这个存储过程时，我什么也没看到。我只是看到这样的事情：
Connecting to the database oracleLocal.
Process exited.
Disconnecting from the database oracleLocal.
I'm coming from MS sql server and am used to seeing actual results when running a stored procedure like this. Is this stored procedure not returning results because I am using a cursor??
我来自 MS sql server 并且习惯于在运行这样的存储过程时看到实际结果。这个存储过程是不是因为我使用了游标而没有返回结果？？
回答by David
The stored procedure is returning something it's just you aren't doing anything with the results.
存储过程正在返回一些东西，只是你没有对结果做任何事情。
You can do this simply by running the following script in SQLDeveloper:
您只需在 SQLDeveloper 中运行以下脚本即可完成此操作：
VARIABLE csr REFCURSOR;
EXEC getRejectedReasons(:csr); -- the colon identifies the parameter as a variable
PRINT csr;
Another method is to fetch each row and do some sort of processing:
另一种方法是获取每一行并进行某种处理：
DECLARE
-- sys_refcursor is weakly typed
refcsr SYS_REFCURSOR;
-- define a record so we can reference the fields
rej_rec Reasons_for_Rejection%ROWTYPE;
BEGIN
getRejectedReasons(refcsr);
-- loop through the results
LOOP
-- gets one row at a time
FETCH refcsr INTO rej_rec;
-- if the fetch doesn't find any more rows exit the loop
EXIT WHEN refcsr%NOTFOUND;
-- Do something here.
-- For example : DBMS_OUTPUT.PUT_LINE(rej_rec.reason_desc);
END LOOP;
END;
回答by tpdi
You opened the cursor. You didn't select anything from it, update it, or advance it.
你打开了光标。您没有从中选择任何内容、更新或推进它。
All open does, effectively, to select the matching rows into temporary memory, so you can advance the cursor row by row. Which you didn't do.
All open 确实有效地将匹配的行选择到临时内存中，因此您可以逐行推进光标。你没有做的。
回答by Pop
One of the differences between Oracle and SQL Server is that the latter returns result sets naturally. I'd use a function, by the way.
Oracle 和 SQL Server 之间的区别之一是后者自然地返回结果集。顺便说一下，我会使用一个函数。
In Oracle, functions typically return a single element. Cursors came later.
在 Oracle 中，函数通常返回单个元素。游标是后来出现的。
There's some documentation online that will help you understand the use of refcursor bind variables. Here's one such for SQL*Plus:
有一些在线文档可以帮助您了解 refcursor 绑定变量的使用。下面是 SQL*Plus 的一个例子：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#sthref1122
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#sthref1122
I think in SQL Developer you can do the same thing with autoprint on, although I haven't tested that.
我认为在 SQL Developer 中，您可以在启用自动打印的情况下做同样的事情，尽管我还没有测试过。
Found a blog that also discusses something similar:
找到一个博客，也讨论了类似的事情：
回答by aape
ETA: Ok. Ignore what I wrote. Listen to someone else. Apparently it's wrong, as I got down voted.
埃塔：好的。忽略我写的。听别人说。显然这是错误的，因为我投了反对票。
What tpdi said is correct. You have to do something with the cursor after you declare it.
tpdi 说的是正确的。在声明游标后，您必须对其进行处理。
Here's an example using two cursors in nested loops
这是在嵌套循环中使用两个游标的示例
PROCEDURE update_insert_tree (exid_in IN NUMBER, outvar_out OUT VARCHAR2)
IS
nxtid NUMBER;
phaseid NUMBER;
rowcounter1 NUMBER;
BEGIN
rowcounter1 := 0;
outvar_out := 0;
FOR acur IN (SELECT dept_exercise_id, phase
FROM ep_dept_exercise
WHERE exercise_id = exid_in)
LOOP
<<dept_loop>>
FOR thecur IN (SELECT document_name, thelevel, sortnum, type_flag,
ex_save_id
FROM ep_exercise_save
WHERE exercise_id = exid_in)
LOOP
phaseid := acur.phase;
IF phaseid = 0
THEN
phaseid := 10;
UPDATE ep_dept_exercise
SET phase = 10
WHERE dept_exercise_id = acur.dept_exercise_id;
END IF;
<<doc_loop>>
