Cursor with iteration to grab data from last row PL/SQL-CodePudding

I have a test script that I'm beggining to play with. I'm getting stuck with something that seems simple. I want to iterate through rows to fetch data from last row of result set to use only it.

procedure e_test_send
is

cursor get_rec is 
    select 
    id,
    email_from,
    email_to,
    email_cc,
    email_subject,
    email_message
    from test_email_tab;

begin
    for rec_ in get_rec loop
    ifsapp.send_email_api.send_html_email(rec_.email_to,rec_.email_from, rec_.email_subject, rec_.email_message);
    end loop;
    
    end e_test_send;

All I'm trying to do is send an email with a message and to a person from the last row only. This is a sample table that will grow in records. At the minute I have 2 rows of data in it, if I execute this procedure it will send 2 emails which is not the desired action. I hope this makes sense.

Thanks

CodePudding user response：

Do you know which row is the last row? The one with the MAX(ID) value? If so, then you could base cursor on a straightforward

SELECT id,
       email_from,
       email_to,
       email_cc,
       email_subject,
       email_message
  FROM test_email_tab
 WHERE id = (SELECT MAX (id) FROM test_email_tab)

As it scans the same table twice, its performance will drop as number of rows gets higher and higher. In that case, consider

WITH
   temp
   AS
      (SELECT id,
              email_from,
              email_to,
              email_cc,
              email_subject,
              email_message,
              ROW_NUMBER () OVER (ORDER BY id DESC) rn
         FROM test_email_tab)
SELECT t.id,
       t.email_from,
       t.email_to,
       t.email_cc,
       t.email_subject,
       t.email_message
  FROM temp t
 WHERE t.rn = 1

which does it only once; sorts rows by ID in descending order and returns the one that ranks as the "highest" (i.e. the last).