I want to create a stored procedure with one parameter which will return multiple rows based on parameter value. how can i do this ?
Question
Share
Sign Up to our social questions and Answers to ask questions, answer people’s questions, and connect with other people.
Login to our social questions & Answers to ask questions, answer people’s questions & connect with other people.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
I want to create a stored procedure with one parameter which will return multiple rows based on parameter value. how can i do this ?
hello aya,
as you know the procedure you can’t use inside query so if you need to create procedure to return multiple row or values you can use :
create or replace procedure myprocedure(retval in out sys_refcursor) isbegin
open retval for
select employee_id,last_name from employees;
end myprocedure;
and here you can call procedure inside anonymous pl/sql block :
[code]
DECLARE
myrefcur SYS_REFCURSOR;
employee_id employees.employee_id%TYPE;
last_name employees.last_name%TYPE;
BEGIN
myprocedure (myrefcur);
LOOP
FETCH myrefcur INTO employee_id,last_name;
EXIT WHEN myrefcur%NOTFOUND;
DBMS_OUTPUT.put_line (‘Employee Id: ‘ || employee_id || ‘ Last Name: ‘|| last_name);
END LOOP;
CLOSE myrefcur;
END;
[/code]
hope this help.
CREATE OR REPLACE PROCEDURE pr_return_multiple_out(p_row_number VARCHAR2) IS
CURSOR c0 IS
SELECT pol_no, pol_assr_name FROM pgit_policy WHERE ROWNUM <= p_row_number;
BEGIN
FOR i IN c0 LOOP
dbms_output.put_line(i.pol_no||’ : ‘||i.pol_assr_name);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END pr_return_multiple_out;
/
—————-Calling by passing number of output we want
BEGIN
pr_return_multiple_out(4);
END;
/