oracle SYS_REFCURSOR 作为 OUT 参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13517952/
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
SYS_REFCURSOR as OUT parameter
提问by shiva tatikonda
I have a table contains (username-primarykey,password,age,gender);
我有一张桌子包含 (username-primarykey,password,age,gender);
have to create procedure like procedure(username in varchar,s_cursor out sys_refcursor);
必须创建像 procedure(username in varchar,s_cursor out sys_refcursor);
procedure has to accept usernameand returns row (where username=in parameter)as cursor.
过程必须接受username并返回行 ( where username=in parameter) 作为游标。
Rule:Cursor must and should be having unique sequence no along with the record it gives.
example:(unique no(sequence),username ,password,age,gender)
规则：光标必须并且应该具有唯一的序列号以及它给出的记录。例子：(unique no(sequence),username ,password,age,gender)
Every time procedure should return single record along with uniqueno(sequence)
每次过程都应返回单个记录和 uniqueno(sequence)
回答by J?cob
You can try something like this, if you need more information you have to provide more details.
您可以尝试这样的操作，如果您需要更多信息，则必须提供更多详细信息。
Create a sequence for unique no.
为唯一编号创建一个序列。
CREATE SEQUENCE emp_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
Create a procedure which returns sys_refcursoras OUTparameter and emp_idas INparameter
创建一个sys_refcursor作为OUT参数和emp_id作为IN参数返回的过程
CREATE OR REPLACE PROCEDURE get_employee_details (user_id
YOURTABLE.USERNAME%TYPE,
emp_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN emp_cursor FOR
SELECT emp_seq.NEXTVAL,
USERNAME,
PASSWORD,
AGE,
GENDER
FROM YOURTABLE
WHERE USERNAME = user_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('<your message>' || SQLERRM);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('<your message>' || SQLERRM);
END get_employee_details;
/
And to execute the procedure from sqlplus
并从 sqlplus 执行程序
variable usercur refcursor;
DECLARE
user_id YOURTABLE.USERNAME%TYPE;
BEGIN
user_id := 'JON';
get_employees(user_id,:usercur);
END;
/
print usercur
Update 1
更新 1
I assume that you are calling your procedure from sqlplus or from Toad, then you could execute your procedure as
我假设你是从 sqlplus 或 Toad 调用你的过程，那么你可以执行你的过程
variable dcursor refcursor;
DECLARE
p_arrival DEFAULT_DETAILS.ARRIVALCOUNTRY%TYPE;
BEGIN
p_arrival := '123';
PROCEDURE_SAMPLE(p_arrival,:dcursor);
END;
/
print dcursor
Update 2
更新 2
To execute procedure from SQL Developer, do as
要从 SQL Developer 执行过程，请执行以下操作
var usercur refcursor
exec procedure_sample('AU',:usercur)
print usercur
