In my stored procedure, I want to have a variable, that can store multiple rows from select query result.

For example:

select a.id, a.date from table1 a; — Note how I don’t select all columns from table1

yields:

ID DATE
200.321311 12.1.2023
200.977600 13.1.2023
I want to store these results into a variable, my_var.

I know I can do something like:

declare
my_var table1%rowtype;
begin
...
end;

however, oracle SQL gives me an error (and understandably so) when I try to

select * into my_var from (select a.id, a.date from table1 a);

because this variable wants to have ALL columns from table1, while my select query only returns 2 columns (from say 10 columns in table1).

I could try:

declare
cursor cur1 is select id, date from table1 where rownum =1;  -- rownum 1 because I need to consider
--optimization, and there's no need to allocate entire table into this cursor, or am I misunderstanding?

my_var cur1%rowtype;
begin
select * into my_var from (select a.id, a.date from table 1 a where a.id = 200.321311 OR 200.321311);

-- dbms put line here
end;

Also, “my_var table of number” can be of only one column?

Not what I need I guess? I need variable that can store at least 2 columns and multiple rows.

I know I can create a blank table with 2 columns and empty rows and then insert into it from table1, but I was wondering if it’s possible with a single variable? (I know how to declare two variables and then separate query result for each column and insert separately).

Also, why do they use for loop with cursors?

Isn’t it possible like this:

for I in (select * from table1)
loop
dbms_output.put_line('id is ', to_char(I.id)||chr(10))
end loop;

and it’d just go through every row from select result?

Anyhow, how do I do this with my_var (that can have multiple rows) to print every row in this variable?