1 2 Previous Next 15 Replies Latest reply: Feb 27, 2013 9:27 AM by John Spencer Go to original post RSS
      • 15. Re: need to update multiple records using store procedure
        John Spencer
        989889 wrote:
        Hi Alberto,

        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

        111,abhi,300
        112,arti,200
        111,naveen,600

        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)
        is
        begin
        open p_cursor for
        select ename,salary into p_ename,p_salary from emp where empno=p_empno;
        end;

        and i have called this by using

        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);
        fetch v_cursor into p_ename,p_salary;
        dbms_output.put_line(p_ename);
        dbms_output.put_line(p_salary);
        end;

        here i should get
        abhi,300
        naveen,600

        but i am getting first row only
        abhi,300

        but i want to fetch both rows...pls help me to find the solution
        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.

        So, simplisticly, to use your show_emp_det you would need something more like:
        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;
        1 2 Previous Next