CODEWITHSUNDEEP
oracle-databaseplsql
satya Kumari
satya Kumari

Reputation: 7

How to update a table using cursor

I need to write a procedure to update a column of a table Xyz if today date is between the start_date and end_date of table ABC.

table ABC

    fin_cycle    start_date   end_date    account_class
    ----------------------------------------------------

    F2018        27-05-2020    29-05-20    2003

table xyz
account_no    account_class  ac_no_dr

1234           2003             Y

when I run the procedure today and if today's date is between start_date and end date of table ABC then the procedure will update column ac_no_dr as Y, else it will update the column as N. I have prepared this skeleton.

Create Or Replace PROCEDURE pr_no_debit
     Cursor c_Today(start_date, end_Date) is
     Select Today from sttm_branch  where today between start_Date and end_Date;

   l_No_Debit_List   ABC%ROW_TYPE;
   Begin



     For c_Today(l_No_Debit_List.start_Date,l_No_Debit_List.end_Date) Loop  


       Update XYZ set ac_no_DR='Y' where account_class=l_No_Debit_List.account_class;

   End Loop;

     -- At the end of the period Change No_Debit to 'N'

   End pr_no_debit;

Upvotes: 0

Views: 72

Answers (2)

Paulo Planez
Paulo Planez

Reputation: 1

update table SET field = value
where trunc(sysdate) between trunc(start_date) and trunc(end_date)

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

Here's one option: merge. (Today is 27.05.2020 which is between start and end date stored in the abc table).

Sample data:

SQL> select * From abc;

FIN_C START_DATE END_DATE   ACCOUNT_CLASS
----- ---------- ---------- -------------
F2018 27.05.2020 29.05.2020          2003

SQL> select * From xyz;

ACCOUNT_NO ACCOUNT_CLASS A
---------- ------------- -
      1234          2003

Merge statement:

SQL> merge into xyz a
  2    using (select account_class,
  3                  case when sysdate between start_date and end_date then 'Y'
  4                       else 'N'
  5                  end ac_no_dr
  6           from abc
  7          ) x
  8    on (a.account_class = x.account_class)
  9    when matched then update set a.ac_no_dr = x.ac_no_dr;

1 row merged.

Result:

SQL> select * From xyz;

ACCOUNT_NO ACCOUNT_CLASS A
---------- ------------- -
      1234          2003 Y

SQL>

The bottom line: you don't need a procedure nor a loop (which is inefficient) as everything can be done with a single SQL statement.

If - as you commented - has to be a procedure, no problem either:

create or replace procedure p_merge as
begin
  merge into xyz a
  using (select account_class,
                case when sysdate between start_date and end_date then 'Y'
                     else 'N'
                end ac_no_dr
         from abc
        ) x
  on (a.account_class = x.account_class)
  when matched then update set a.ac_no_dr = x.ac_no_dr;
end;
/

Upvotes: 1

Related Questions