oracle存储过程(带有游标)如何工作? [英] how do oracle stored procedures (w/ cursors) work?
问题描述
我有以下oracle存储过程
I have a following oracle stored procedure
CREATE OR REPLACE
PROCEDURE getRejectedReasons
(
p_cursor IN OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_cursor FOR SELECT * FROM reasons_for_rejection;
END;
但是,当我在sql-developer中运行此存储过程时,则看不到任何东西.我只是看到这样的东西:
However, when I run this stored procedure in sql-developer then I dont see anything. I just see something like this:
Connecting to the database oracleLocal.
Process exited.
Disconnecting from the database oracleLocal.
我来自MS sql服务器,习惯于在运行这样的存储过程时看到实际结果.因为我正在使用游标,所以此存储过程是否不返回结果?
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??
推荐答案
存储过程返回的结果只是您对结果不做任何事情.
The stored procedure is returning something it's just you aren't doing anything with the results.
您只需在SQLDeveloper中运行以下脚本即可完成此操作:
You can do this simply by running the following script in 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;
这篇关于oracle存储过程(带有游标)如何工作?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!