How to store multiple rows in variable in PLSQL

Question

i have a problem that store selected multiple rows in variable (or table variable i don't know about). I think it is possible to make it work. Here is my code

  SELECT RECIPIENT, COUNT (*) N_CNT
    FROM (SELECT RECIPIENT,
                 SERVICE_TYPE,
                 MPS_SWTCH_CD,
                 MPS_FTR_CD1
            FROM ABA_RM_INB_USAGE
           WHERE SERVICE_TYPE = 100
              OR SERVICE_TYPE = 0
             AND MPS_SWTCH_CD = 'T11'
             AND MPS_FTR_CD1 = 1033)
GROUP BY RECIPIENT

This query shows filtered rows and i need insert that rows into another table. Sorry for bad english by the way

What is your context to insert that selected data to another table? You may search for GTT oracle but why don't you just INSERT INTO another_table(column1, column2) SELECT recipient, COUNT(*) FROM....? And your query seem has syntax error, too. — Pham X. Bach
– Pham X. Bach, Commented Apr 3, 2019 at 6:49
Create a table of object with columns same as your query columns and store your query result using a variable to of your table type. — XING
– XING, Commented Apr 3, 2019 at 7:25
If you need to insert that result into another table, then just do so. No need for variables. — user330315
– user330315, Commented Apr 3, 2019 at 8:03

kara · Accepted Answer · 2019-04-03 14:01:01Z

I'm not quite sure, what you want to acchieve.

If you want to select from one table and insert into another, you can wrap an insert-statement around your select:

INSERT INTO TABLE_RECIPIENT_COUNT
      SELECT RECIPIENT, COUNT (*) N_CNT
        FROM (SELECT RECIPIENT,
                     SERVICE_TYPE,
                     MPS_SWTCH_CD,
                     MPS_FTR_CD1
                FROM ABA_RM_INB_USAGE
               WHERE SERVICE_TYPE = 100
                  OR SERVICE_TYPE = 0
                 AND MPS_SWTCH_CD = 'T11'
                 AND MPS_FTR_CD1 = 1033)
    GROUP BY RECIPIENT;

If you want to do it within a plsql-script (or procedure..), you could define a type or loop through a cursor. Here's an example how to loop through a cursor and process the data:

DECLARE
    CURSOR cur
    IS
          SELECT RECIPIENT, COUNT (*) N_CNT
            FROM (SELECT RECIPIENT,
                         SERVICE_TYPE,
                         MPS_SWTCH_CD,
                         MPS_FTR_CD1
                    FROM ABA_RM_INB_USAGE
                   WHERE SERVICE_TYPE = 100
                      OR SERVICE_TYPE = 0
                     AND MPS_SWTCH_CD = 'T11'
                     AND MPS_FTR_CD1 = 1033)
        GROUP BY RECIPIENT;
BEGIN
    FOR item IN cur -- if you realy want to insert all results you should read about BULK-COLLECT and FORALL ;-)
    LOOP
        dbms_output.put_line('Result: ' || item.RECIPIENT || ', ' || item.N_CNT);
        INSERT INTO TABLE_RECIPIENT_COUNT
             VALUES (item.RECIPIENT, item.N_CNT);
    END LOOP loop_emp;
END;

Collectives™ on Stack Overflow

How to store multiple rows in variable in PLSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related