Search code examples
oracle

Simplest way to declare a variable that can store multiple rows from "select" and have 2 columns?

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 throws 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?

Solution

  • You'd create your own type for such a purpose. Here's a demo:

    SQL> create or replace type t_row as object
  2    (id      number,
  3     datum   date
  4    );
  5  /

Type created.

SQL> create or replace type t_tab as table of t_row;
  2  /

Type created.

SQL> set serveroutput on

    This is how you populate it, and do something with stored values (I just displayed them on the screen):

    SQL> declare
  2    l_tab t_tab;
  3  begin
  4    select t_row(empno, hiredate)
  5      bulk collect into l_tab
  6      from emp
  7      where deptno = 10;
  8
  9    for i in l_tab.first .. l_tab.last loop
 10      dbms_output.put_line(l_tab(i).id ||' - '|| to_char(l_tab(i).datum, 'dd.mm.yyyy'));
 11    end loop;
 12  end;
 13  /
7782 - 09.06.1981
7839 - 17.11.1981
7934 - 23.01.1982

PL/SQL procedure successfully completed.

SQL>

    As of the rest of questions you asked:

    Code that begins after "I could try" doesn't make much sense. If you declared a cursor, then use it, so that piece of code would have been

    SQL> declare
  2    cursor cur1 is
  3      select empno as id, hiredate as datum
  4      from emp
  5      where deptno = 10;
  6    my_var cur1%rowtype;
  7  begin
  8    open cur1;
  9    loop
 10      fetch cur1 into my_var;
 11      exit when cur1%notfound;
 12
 13      dbms_output.put_line(my_var.id);
 14    end loop;
 15    close cur1;
 16  end;
 17  /
7782
7839
7934

PL/SQL procedure successfully completed.

SQL>

    But that's not how we do it; it is simpler to use a cursor FOR loop and let Oracle do most of dirty things (explicitly declaring a cursor and cursor variable, opening/closing the cursor, exiting the loop) for you:

    SQL> begin
  2    for my_var in (select empno as id, hiredate as datum
  3                   from emp
  4                   where deptno = 10
  5                  )
  6    loop
  7      dbms_output.put_line(my_var.id);
  8    end loop;
  9  end;
 10  /
7782
7839
7934

PL/SQL procedure successfully completed.

SQL>

    Then, you asked whether my_var table of number can be of only one column. Certainly, it can. Either declare your own type which contains only one column, or use Oracle's built-in datatype:

    SQL> declare
  2    my_var sys.odcinumberlist;
  3  begin
  4    select empno as id
  5      bulk collect into my_var
  6      from emp
  7      where deptno = 10;
  8
  9    for i in my_var.first .. my_var.last loop
 10      dbms_output.put_line(my_var(i));
 11    end loop;
 12  end;
 13  /
7782
7839
7934

PL/SQL procedure successfully completed.

SQL>

    Finally, you asked why do they use for loop with cursors?. Why not? It lets you work with a single row fetched from the cursor. Sometimes we have to do that, but - generally speaking, performance wise - if you can, do everything with pure SQL as loop does it row-by-row (which is what some people call slow-by-slow).