How to use two cursors and display them PL/SQL

Question

I have a problem and a can't find a solution to it. I started working with PL/SQL in the past week, which means that my abilities are very limited.

Anyways, I had a working script that formatted a table to be export to another:

    select
        '7' as M_ID,
         N_PROT as SUBSCRIPTION_VERSION_ID,
      /* ..... bunch of other business logic */
    from N_TRANS
    where (CORA in ('0215','0320') or CORA in ('0215', '0320'))
    and N_PROT IS NOT null;

however it needed a change to search only the last ID (S_ID) grouped by the NUMBER (N_PROT). So, I did another separeted script to search from the table the last ID (S_ID) group by the NUMBER (N_PROT):

    declare
       cursor S_CURS IS
          select 
               max(S_ID) keep(dense_rank LAST order by N_PROT) S_ID,
               NR_PROTOCOLO
          from N_TRANS 
          group by N_PROT
          order by S_ID;

    S_HIST_T  S_CURS%ROWTYPE;
    type NTT_S_HIST_TRANS is table of S_HIST_T%type;
    LLL_S_HIST_TRANS NTT_S_HIST_TRANS;

    begin
       open  S_CURS;
       fetch S_CURS bulk collect into LLL_S_HIST_TRANS;
       close S_CURS;
       for indxx in 1..LLL_S_HIST_TRANS.count loop
          --The IDs as a var
          DBMS_OUTPUT.PUT_LINE(LLL_S_HIST_TRANS(indxx).S_HIST_T);
       end loop;
   end;

and the problem is how I will join this two scripts. One possible way to solve that came into my mind was to use execute the first script inside the loop in the second script (¹). Something like this:

    declare
       cursor S_CURS IS
          select 
          ...     
    begin
      ... 
      for indxx in 1..LLL_S_HIST_TRANS.count loop
          --The IDs as a var
          select
             '7' as M_ID,
              N_PROT as SUBSCRIPTION_VERSION_ID,
              /* ..... bunch of other business logic */
          from N_TRANS
          where (CORA in ('0215','0320') or CORA in ('0215', '0320'))
          and N_PROT IS NOT null
          and S_ID = LLL_S_HIST_TRANS(indxx).S_HIST_T; -- (¹)
       end loop;
   end;

Thus, in this way, how would I save the insider SELECTs and export the results. I know that is not a good practice, but I couldn't come up with something better. If someone could give me some other perspective on how to solve this or help me with this solution that I came up with, it'd be great!

Just to resume the problem, I had a script already up and running and I needed to change it to do the same thing but add a condition that had to be only with the LAST IDs. I made a script that was able to get the IDs and isolate them with cursor, and now, I have to incorporate this IDs inside the first script.

Thank you!

score 1 · Answer 1 · answered Aug 02 '17 at 14:16

1

If you want to "export" your data from one table to another and you have the select, why don't you try with an "insert as select" ?

insert into your_new_table select <your_data> from N_TRANS where ...

answered Aug 02 '17 at 14:16

Mynsk

199
5

furman87 · Answer 2 · 2017-08-02T18:12:07.770

begin
  for cv1 in (select
                  max(S_ID) keep(dense_rank LAST order by N_PROT) S_ID,              
                  NR_PROTOCOLO
                 from N_TRANS 
                 group by N_PROT
                 order by S_ID)
  loop
    for cv2 in (select
                '7' as M_ID,
                 N_PROT as SUBSCRIPTION_VERSION_ID,
                 /* ..... bunch of other business logic */
              from N_TRANS
              where (CORA in ('0215','0320') or CORA in ('0215', '0320'))
                and N_PROT IS NOT null
                and S_ID = cv1.S_ID)
    loop
      -- Processing logic here. To access either loop's data,
      -- prefix the column with cv1 or cv2, like cv2.M_ID, cv1.NR_PROTOCOLO
    end loop;  
  end loop;
end;

score 0 · Answer 3 · answered Aug 02 '17 at 14:30

0

Looks like you just need something like this:

select m_id, n_prot, etc
from   ( select '7' as m_id
              , n_prot as subscription_version_id
              , /* ..... bunch of other business logic */
              , row_number() over(partition by n_prot order by s_id desc) as seq
         from   n_trans
         where  ( cora in ('0215', '0320') or cora in ('0215', '0320') )
         and    n_prot is not null )
where  seq = 1

answered Aug 02 '17 at 14:30

William Robertson

15,273
4
38
44

Thank you for the reply and I'm sorry if I wasn't clear, but that's not what I need. The insider select that you wrote ([...] select '7' as m_id , n_prot as subscription_version_id [...]) has to be the external and only use the IDs from the Cursor Select. – RochaRF Aug 02 '17 at 17:21
Perhaps some same data and results would make the issue clearer. The above is a general pattern for 'last row per set' type of filtering. – William Robertson Aug 02 '17 at 18:07

How to use two cursors and display them PL/SQL

3 Answers3