1 2 Previous Next 15 Replies Latest reply: Feb 27, 2013 9:27 AM by John Spencer RSS

    need to update multiple records using store procedure

    992892
      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.
          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
            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
              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
                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
                  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
                    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
                      Billy~Verreynne
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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