This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 27, 2013 7:27 AM by John Spencer Go to original post RSS
  • 15. Re: need to update multiple records using store procedure
    John Spencer Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points