989889 wrote:If you are going to iterate over a cursor, then you need a loop contruct to do so. Each fetch just gets a single row so you need to keep fetching until you run out of cursor. Even if you use bulk collect/limit, the fetch call simply populates an array, so you would still need to loop over the array.
thanx for your valuable suggestion. but still i have doubt. the code which i have mentioned above might be simple but what if i have big requirement where i need update the data by using loops and conditional statement.
and i have similar kind of requirement where i need to deal with procedure which returns more than one row
my source is
empno ename salary
here i need to write a store procedure which accepts the empno (111) as input para and display ename and salary
here i have written store procedure like this
create or replace procedure show_emp_det(p_empno in emp.empno%type, p_ename out emp.ename%type,p_salary out emp.salary%type, p_cursor out sys_refcursor)
open p_cursor for
select ename,salary into p_ename,p_salary from emp where empno=p_empno;
and i have called this by using
fetch v_cursor into p_ename,p_salary;
here i should get
but i am getting first row only
but i want to fetch both rows...pls help me to find the solution
declare p_salary emp.salary%type; p_ename emp.ename%type v_cursor sys_refcursor; begin show_emp_det(111,p_ename,p_salary,v_cursor); loop fetch v_cursor into p_ename,p_salary; exit when v_cursor%NotFound; dbms_output.put_line(p_ename); dbms_output.put_line(p_salary); end loop; end;