This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Feb 27, 2013 7:27 AM by John Spencer RSS

need to update multiple records using store procedure

992892 Newbie
Currently Being Moderated
Hi i am trying to update multiple records using store procedure but failed to achieve pls help me with this

for example my source is

emp_name sal

abhi 2000
arti 1500
priya 1700

i want to increase salary of emp whose salary is less than 2000 it means rest two salary should get update..using stored procedure only

i have tried following code

create or replace procedure upt_sal(p_sal out emp.sal%type, p_cursor out sys_refcursor)
is
begin
open p_cursor for
select sal into p_sal from emp;
if sal<2000 then
update emp set sal= sal+200;
end i;f
end;


and i have called the procedure using following codes

set serveroutput on
declare
p_sal emp.sal%type;
v_cursor sys_refcursor;

begin
upt_sal(p_sal,v_cursor);
fetch v_cursor into p_sal;
dbms_output.put_line(p_sal);
end;

the program is executing but i should get o/p like this after updating

1700
1900

but i am getting first row only
2000

and record is not upsating...please help me with this

thanks
  • 1. Re: need to update multiple records using store procedure
    Nitesh. Explorer
    Currently Being Moderated
    Corrected proceduree :

    CREATE OR REPLACE PROCEDURE upt_sal (
    p_sal OUT emp.sal%TYPE,
    p_cursor OUT sys_refcursor
    )
    IS
    BEGIN
    OPEN p_cursor FOR
    SELECT sal
    INTO p_sal
    FROM emp;

    IF p_sal < 2000
    THEN
    UPDATE emp
    SET sal = sal + 200;
    END IF;
    END;

    Now try its updating ..

    Rgds,
    Nitkhush..
  • 2. Re: need to update multiple records using store procedure
    jeneesh Guru
    Currently Being Moderated
    Please don't create How to update a multiple records using store procedure

    Why are you creating a new one, instead of providing the details in your first thread?
  • 3. Re: need to update multiple records using store procedure
    jeneesh Guru
    Currently Being Moderated
    Niteshkhush wrote:
    Corrected proceduree :

    CREATE OR REPLACE PROCEDURE upt_sal (
    p_sal OUT emp.sal%TYPE,
    p_cursor OUT sys_refcursor
    )
    IS
    BEGIN
    OPEN p_cursor FOR
    SELECT sal
    INTO p_sal
    FROM emp;
    OPEN cursor with INTO ..? Is this syntax correct?
    >
    IF p_sal < 2000
    THEN
    UPDATE emp
    SET sal = sal + 200;
    END IF;
    END;
  • 4. Re: need to update multiple records using store procedure
    Paul Horth Expert
    Currently Being Moderated
    Why not do it with simple SQL? It will be faster.
    update emp
    set sal=sal+200
    where sal<2000;
  • 5. Re: need to update multiple records using store procedure
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    Consider simplifying your code. For what you have to do, you can just have one single SQL statement:
    update emp 
       set sal= sal+200
     where sal < 2000;
    No need of procedures and loops.

    Regards.
    Al
  • 6. Re: need to update multiple records using store procedure
    992892 Newbie
    Currently Being Moderated
    Hi jeneesh

    sorry for creating duplicate post.

    actually my procedure has got executed but records r not updating... i have gone through syntax but i am not getting..please provide me a solution

    thanx
  • 7. Re: need to update multiple records using store procedure
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Niteshkhush wrote:
    Corrected proceduree :
    A procedure that compiles without an error, does not imply a correct procedure. Your example code is horribly wrong.

    It reads EVERY SINGLE ROW from a table into PL/SQL. And then apply a filter condition to determine whether to process the row of not.

    This is NOT how one use a database and SQL.

    The correct approach is shown by Paul.
  • 8. Re: need to update multiple records using store procedure
    992892 Newbie
    Currently Being Moderated
    Hi paul

    thanx for your solution..but in one of my interview they asked me to do with store procedure only
  • 9. Re: need to update multiple records using store procedure
    AlbertoFaenza Expert
    Currently Being Moderated
    989889 wrote:
    Hi paul

    thanx for your solution..but in one of my interview they asked me to do with store procedure only
    create procedure upd_sal is
    begin
       update emp 
          set sal= sal+200
        where sal < 2000;
    end;
    /
    We should understand exactly what are the business and technical requirements. Complicating a simple task does not make sense.
    In your procedure you were using reference cursors as output parameter but I have the impression that you don't understand exactly what you are doing.

    This is not a good approach. Keep the things as simple as possible.

    Regards.
    Al

    Edited by: Alberto Faenza on Feb 27, 2013 1:55 PM
  • 10. Re: need to update multiple records using store procedure
    992892 Newbie
    Currently Being Moderated
    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
  • 11. Re: need to update multiple records using store procedure
    Paul Horth Expert
    Currently Being Moderated
    989889 wrote:
    Hi paul

    thanx for your solution..but in one of my interview they asked me to do with store procedure only
    I wouldn't want to work for somewhere that wants to slow things down by using stored procedures instead of pure SQL :-)
  • 12. Re: need to update multiple records using store procedure
    992892 Newbie
    Currently Being Moderated
    hi paul thanx i have posted one more problem above pls can u find any solution for that

    Edited by: 989889 on Feb 27, 2013 6:53 AM
  • 13. Re: need to update multiple records using store procedure
    6363 Guru
    Currently Being Moderated
    989889 wrote:

    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.
    Conditional statements are available in SQL, which PL/SQL conditional statements, functions or operators do you need to use that have no equivalent in SQL? For example IF is equivalent to CASE which is available in both SQL and PL/SQL.

    You would use loops only when you want to make a process as slow as possible. The bigger the process the more slowly the loops will make it run.
    and i have similar kind of requirement where i need to deal with procedure which returns more than one row
    Here is an example.

    {message:id=10742902}
  • 14. Re: need to update multiple records using store procedure
    Paul Horth Expert
    Currently Being Moderated
    If you really want to do it that way.
    create or replace procedure show_emp_det
    (
      p_empno  in number
     ,p_cursor out sys_refcursor
    ) as
    begin
      open p_cursor for
        select ename
              ,salary
          from emp
         where empno = p_empno;
    end;
    /
    
    declare
      v_cursor sys_refcursor;
      v_name   emp.ename%type;
      v_salary emp.salary%type;
    begin
      show_emp_det(111
                  ,v_cursor);
      loop
        fetch v_cursor
          into v_name
              ,v_salary;
        exit when v_cursor%notfound;
        dbms_output.put_line('Name = ' || v_name || ', Salary = ' || to_char(v_salary));
      end loop;
    end;
    /
    Why do two different people have the same empno though? Surely that is wrong.
1 2 Previous Next

Legend

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