I am trying to write a procedure (in PL/SQL) for returning users that are enrolled to a course between specific dates. There will be 2 inputs (date1, date2) and 3 outputs (enrollno, error_code and enroll_date). I want it to display the info of multiple rows of users that are enrolled between date1 and date2. This is the first time that I am writing a procedure, I was able to write it in a way that it can return one row. But since there could be many users enrolled between these dates I want to display many rows. I see that I can use sys_refcursor but I couldn't do it. The examples on the internet was mostly for one output procedures so I could not adapt them for mine.
For example I looked at the example here https://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets but I was confused with declare statements.
Edit: I am planning to call this procedure from Java code and assign the returned result into something and I am not allowed to add a new table into the database.
Here is my procedure:
create or replace procedure display_users(pi_date1 in date,
pi_date2 in date,
po_enrollno out number,
po_error_code out varchar2,
po_enroll_date out date) is
cursor user_display is
select u.enrollno, u.error_code, u.enroll_date,
from user_table u
where u.enroll_date between pi_date1 and pi_date2;
begin
open user_display;
loop
fetch user_display
into po_enrollno, po_error_code, po_enroll_date;
EXIT WHEN user_display%notfound;
end loop;
close user_display;
end;
You may use a single REFCURSOR out parameter instead of multiple out parameters.
CREATE OR REPLACE PROCEDURE display_users (
pi_date1 IN DATE,
pi_date2 IN DATE,
po_userdisp_cur OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN po_userdisp_cur FOR SELECT u.enrollno,
u.error_code,
u.enroll_date
FROM user_table u
WHERE u.enroll_date BETWEEN pi_date1 AND pi_date2;
END;
This can be easily used in java to fetch the records as shown in this link: