How to update each row in a table Oracle PLSQL

Question

How can I create a procedure to verify each row of a table and update a field according to the established statement? I have 4 fields that I work with

1. SYS_UPDATE_PING
2. UPDATE_PING
3. PING_STATUS
4. TIME_OUT_PING

Here is my code:

create or replace procedure SP_DASHBOARD_PINGSTATUS is
begin
  declare
  sp_ping_final number;
  BEGIN
        Update rsmes.tb_op_pc_monitoring_v4 t
         set t.sys_update_ping = sysdate;
   for c in (select ((SYS_UPDATE_PING - UPDATE_PING)*60*60*24) as PING_RESULT into sp_ping_final from TB_OP_PC_MONITORING_V4)
     loop
       sp_ping_final := c.ping_result;
       if c.ping_result <= 5 then
        Update rsmes.tb_op_pc_monitoring_v4 tg
        set tg.ping_status = 'OK',
            tg.time_out_ping = sp_ping_final;
       else
        Update rsmes.tb_op_pc_monitoring_v4 tn
        set tn.ping_status = 'NG',
            tn.time_out_ping = sp_ping_final;
        end if;
       end loop;
        commit;
  END;
end SP_DASHBOARD_PINGSTATUS;

I make a time difference between SYS_UPDATE_PING and UPDATE_PING, if the result in seconds is less than 5 it must update the PING_STATUS field to OK and put the seconds difference in TIME_OUT_PING, otherwise it will update NG and put the seconds difference in TIME_OUT_PING, I want it to compare on each row but it updates me on all fields instead of one by one.

I know I'm forgetting something but could you help me find my fault and know the solution? Thank you

Answer 1

That's because UPDATE statements miss the WHERE clause. Without it, you're always updating all rows in the table.

It means that cursor should contain some kind of an ID, which you'll the reuse in UPDATE. For example:

for c in (select id,                            --> this
                ((sys_update_ping - ...)
         ) loop
  ...
  update tb_op_pc_monitoring_v4 tg set
    tg.ping_status = 'OK'
    where tg.id = c.id;                         --> this
  ...
end loop;

Also, you don't SELECT INTO in cursor. Remove the sp_ping_final entirely.

---

Finally, I'd say that you don't need PL/SQL (loop especially) at all. The whole code you wrote can be rewritten into a single

UPDATE tb_op_pc_monitoring_v4 tg
   SET tg.ping_status =
          CASE
             WHEN (SYSDATE - tg.update_ping) * 60 * 60 * 24 <= 5 THEN 'OK'
             ELSE 'NG'
          END,
       tg.time_out_ping = (SYSDATE - tg.update_ping) * 60 * 60 * 24;

which should work way faster than your row-by-row processing.