INSERT and UPDATE a record using cursors in oracle

Question

I have 2 tables- student and studLoad both having 2 fields studID and studName. I want to load data from student table into stuLoad table. If the data already exists in the studLoad table, then it should be updated else it should be inserted. following is my code to do so:

    create or replace procedure studentLoad is
v_id student.studID%type;
v_name student.studName%type;
v_sn studLoad.studName%type;
cursor cur_load is
select * from student;


begin
  open cur_load;
  loop
   fetch cur_load into v_id,v_name;

   exit when cur_load%notfound;
   select studName into v_sn from studLoad where studID = v_id;
   if(v_sn!= v_name) then
      update studLoad set studName= v_name where studID= v_id;
   else
      insert into studLoad values(v_id,v_name);
   dbms_output.put_line(v_id || ' ' || v_name);
   end if;
  end loop;
      close cur_load;
end;

It's not working. the rows in studLoad table are noT updated. How do I solve this? In SQL server we use IF EXISTS(select...from stuLoad..) to check if the record exists in the table, is there a way to do the same in Oracle? if yes then please let me know the same.

possible duplicate of [Oracle: how to UPSERT (update or insert into a table?)](http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) — Ben, Aug 12 '12 at 12:03
Your code is not working fine because it's not doing what you're talking about. I'm surprised why it doesn't throw exceptions. NO_DATA_FOUND exception in the case of unmatched records. The IF condition to evaluate if it's the case to insert the records is wrong because it would insert only new duplicates. Ben's proposed solution would work fine if studId is unique in both studLoad and student tables. — Alessandro Rossi, Aug 12 '12 at 13:47

Ben · Accepted Answer · 2012-08-12T17:47:03.443

This is a highly inefficient way of doing it. You can use the merge statement and then there's no need for cursors, looping or (if you can do without) PL/SQL.

MERGE INTO studLoad l
USING ( SELECT studId, studName FROM student ) s
ON (l.studId = s.studId)
WHEN MATCHED THEN
  UPDATE SET l.studName = s.studName
   WHERE l.studName != s.studName
WHEN NOT MATCHED THEN 
INSERT (l.studID, l.studName)
VALUES (s.studId, s.studName)

Make sure you commit, once completed, in order to be able to see this in the database.

To actually answer your question I would do it something like as follows. This has the benefit of doing most of the work in SQL and only updating based on the rowid, a unique address in the table.

It declares a type, which you place the data within in bulk, 10,000 rows at a time. Then processes these rows individually.

However, as I say this will not be as efficient as merge.

declare

   cursor c_data is
    select b.rowid as rid, a.studId, a.studName
      from student a
      left outer join studLoad b
        on a.studId = b.studId
       and a.studName <> b.studName
           ;

   type t__data is table of c_data%rowtype index by binary_integer;
   t_data t__data;

begin

   open c_data;
   loop
      fetch c_data bulk collect into t_data limit 10000;

      exit when t_data.count = 0;

      for idx in t_data.first .. t_data.last loop
         if t_data(idx).rid is null then
            insert into studLoad (studId, studName)
            values (t_data(idx).studId, t_data(idx).studName);
         else
            update studLoad
               set studName = t_data(idx).studName
             where rowid = t_data(idx).rid
                   ;
         end if;
      end loop;

   end loop;
   close c_data;

end;
/

To limit the number of changes (with consequent reduction of Undo space, redo logs and execution time) I suggest to add a condition for new_studName != old_studName in the updates in both merge statement ("where l.studName = s.studName" http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#i2081030) and in PL/SQL block (can be done in different ways, the better one would be to add the "a.studName != b.studName" condition in the outer join clause of the cursor). — Alessandro Rossi, Aug 12 '12 at 13:59
@AlessandroRossi, you are, of course, correct. I've updated it. — Ben, Aug 12 '12 at 17:51
pls view the thread http://stackoverflow.com/questions/11924657/merge-when-matched-insert . i wish to perform constructive load. how do i do that using merge? — z22, Aug 12 '12 at 18:30
how do i call this merge code inserted into a procedure? begin proc_name; end; doesnt work here — z22, Aug 12 '12 at 18:40

score 0 · Answer 2 · answered Oct 22 '21 at 13:56

If you would like to use your procedure, consider to change some lines:

create or replace procedure studentLoad is
v_id student.studID%type;
v_name student.studName%type;
v_sn studLoad.studName%type;
cursor cur_load is
       select * from student;
begin
    open cur_load;
    loop
        fetch cur_load into v_id,v_name;
        exit when cur_load%notfound;
        begin
           select studName into v_sn from studLoad where studID = v_id;
           if(v_sn!= v_name) then
               update studLoad set studName= v_name where studID= v_id;
           end if;
        exception
           when no_data_found then
                insert into studLoad values(v_id,v_name);
        end;
        dbms_output.put_line(v_id || ' ' || v_name);
    end loop;
    close cur_load;
end;

I think it should work, didn't test it.

INSERT and UPDATE a record using cursors in oracle

2 Answers2

Linked