CODEWITHSUNDEEP
sqloracle-databaseplsql
h0neybaLL
h0neybaLL

Reputation: 101

How to store multiple rows in a variable in pl/sql function?

I'm writing a pl/sql function. I need to select multiple rows from select statement:

    SELECT pel.ceid
    FROM pa_exception_list pel
    WHERE trunc(pel.creation_date) >= trunc(SYSDATE-7)

if i use:

SELECT pel.ceid
INTO v_ceid

it only stores one value, but i need to store all values that this select returns. Given that this is a function i can't just use simple select because i get error, "INTO - is expected."

Upvotes: 4

Views: 23774

Answers (4)

jumping_monkey
jumping_monkey

Reputation: 7809

SET SERVEROUTPUT ON

BEGIN
    FOR rec IN (
        --an implicit cursor is created here
        SELECT pel.ceid AS ceid
        FROM pa_exception_list pel
        WHERE trunc(pel.creation_date) >= trunc(SYSDATE-7)
    )
    LOOP
        dbms_output.put_line(rec.ceid);
    END LOOP;

END;
/

Notes from here:

In this case, the cursor FOR LOOP declares, opens, fetches from, and closes an implicit cursor. However, the implicit cursor is internal; therefore, you cannot reference it.

Note that Oracle Database automatically optimizes a cursor FOR LOOP to work similarly to a BULK COLLECT query. Although your code looks as if it fetched one row at a time, Oracle Database fetches multiple rows at a time and allows you to process each row individually.

Upvotes: 0

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

You can use a record type to do that. The below example should work for you

DECLARE
    TYPE v_array_type IS VARRAY (10) OF NUMBER;
        var v_array_type;
BEGIN
    SELECT x
    BULK COLLECT INTO
        var 
    FROM (
            SELECT 1 x
            FROM dual
            UNION
            SELECT 2 x
            FROM dual
            UNION
            SELECT 3 x
            FROM dual
        );
        FOR I IN 1..3 LOOP
              dbms_output.put_line(var(I));
            END LOOP;

END;

So in your case, it would be something like

select pel.ceid 
  BULK COLLECT INTO <variable which you create>
  from pa_exception_list
 where trunc(pel.creation_Date) >= trunc(sysdate-7);

Upvotes: 4

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

You may store all in a rowtype parameter and show whichever column you want to show( assuming ceid is your primary key column, col1 & 2 are some other columns of your table ) :

SQL> set serveroutput on;
SQL> declare
  l_exp pa_exception_list%rowtype;
begin
 for c in  (   select *
                 from pa_exception_list pel
                where trunc(pel.creation_date) >= trunc(SYSDATE-7) 
           ) -- to select multiple rows
 loop     
      select *
        into l_exp            
        from pa_exception_list
       where ceid = c.ceid; -- to render only one row( ceid is primary key )

      dbms_output.put_line(l_exp.ceid||' - '||l_exp.col1||' - '||l_exp.col2); -- to show the results     
 end loop;
end;
/

Upvotes: 2

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

If you really need to store multiple rows, check BULK COLLECT INTO statement and examples. But maybe FOR cursor LOOP and row-by-row processing would be better decision.

Upvotes: 3

Related Questions