CODEWITHSUNDEEP
oracle-databaseplsql
John Wick
John Wick

Reputation: 745

How to store the number of affected records and return it as a parameter?

I have an in parameter (set = 0), to keep track of the count of entries I will be modifying. I am trying to merge data into a table called Table1, the records that have been updated in a different table (Table2) since the last time Table1 has been updated. The conditional statement will compare the Table1.LastUpdate column to the max(Modified_date) column of Table2 and only insert entries where the table1.last_update column is greater than the table2.max(modified_date) column. Then I will need to store this number and return it as an out parameter. What I have is follows:

create or replace procedure test_proc (rUpdated_Row_Count IN NUMBER, rUpdated_Row_Count_2 OUT NUMBER) is
CURSOR c1 is
            select max(modified_date) as max_modified_date
              from table1;
        l_var c1%ROWTYPE;
-----------
CURSOR c2 is
            select table2_id
                 , last_update
              from table2;
         k_var c2%ROWTYPE;
BEGIN
LOOP
Open c1;
        Fetch c1 into l_var;
Open c2;
        Fetch c2 into k_var;
EXIT WHEN c1%NOTFOUND;
IF k_var.last_update > l_var.max_modified_date THEN 
Insert into table2(table2_id, last_update)
            values(null, k_var.last_update);
            commit;
      rUpdated_Row_Count_2 := rUpdated_Row_Count + 1;
END IF;
END LOOP;
Close c1;
Close c2;
END test_proc;

Thanks in advance!

Modified my code (after doing further research):

create or replace procedure test_proc (rUpdated_Row_Count IN NUMBER, rUpdated_Row_Count_2 OUT NUMBER) is
CURSOR c1 is
            select max(modified_date) as max_modified_date
              from table1;
        l_var c1%ROWTYPE;
-----------
CURSOR c2 is
            select table2_id
                 , last_update
              from table2;
         k_var c2%ROWTYPE;
BEGIN
Open c1;
Open c2;
LOOP
        Fetch c1 into l_var;
        Fetch c2 into k_var;
EXIT WHEN c2%NOTFOUND;
IF k_var.last_update > l_var.max_modified_date THEN 
Insert into table2(table2_id, last_update)
            values(null, k_var.last_update);
            commit;
      rUpdated_Row_Count_2 := rUpdated_Row_Count + 1;
END IF;
END LOOP;
Close c1;
Close c2;
END test_proc;

Reproducable data / Code is below:

Create table1
(
  table1_id number,
  modified_date date
 );

Create table2
(
  table2_id number,
  last_update date
);

insert into table1(table1_id, modified_date) values(1, sysdate);
insert into table1(table1_id, modified_date) values(2, sysdate);
insert into table1(table1_id, modified_date) values(3, sysdate -1);
insert into table2(table2_id, last_update) values(1, sysdate + 1);
insert into table2(table2_id, last_update) values(2, sysdate + 2);

Upvotes: 0

Views: 57

Answers (1)

stefan
stefan

Reputation: 2252

Not quite sure what the "IN" parameter is for. Also not quite sure about the overall rationale. However, here's how I'd write a first version of your procedure:

create or replace procedure test_proc2 (
  rUpdated_Row_Count IN NUMBER
, rUpdated_Row_Count_2 IN OUT NUMBER ) 
is
  max_modified_date date ;
begin
  select max( modified_date ) into max_modified_date from table1 ;  

  for rec_ in ( 
     select table2_id, last_update
     from table2
  ) loop
      if rec_.last_update > max_modified_date then 
        insert into table2( table2_id, last_update )
          values( null, rec_.last_update ) ;
        rUpdated_Row_Count_2 := rUpdated_Row_Count_2 + 1 ;
      end if ;
  end loop;
end ;
/

Using your test tables (your DDL code should be: CREATE TABLE table1 ... by the way), we can use the following anonymous block for executing the procedure.

-- not sure what the "IN" parameter is used for 
declare
  rowcount_in number := 0 ;  -- not needed
  rowcount_out number := 0 ;
begin
  test_proc2( rowcount_in, rowcount_out ) ;
  dbms_output.put_line( 'updated rows: ' || rowcount_out ) ;
end;
/

updated rows: 2

After executing the anonymous block the tables contain ...

SQL> select * from table1 ;
TABLE1_ID  MODIFIED_DATE  
1          15-MAY-18      
2          15-MAY-18      
3          14-MAY-18      


SQL> select * from table2 ;
TABLE2_ID  LAST_UPDATE  
1          16-MAY-18    
2          17-MAY-18    
NULL       16-MAY-18    
NULL       17-MAY-18

Many people will tell you that you should use BULK operations (BULK COLLECT, FORALL etc) whenever possible. Does all that help you?

Upvotes: 2

Related Questions