Assign multiple values into variable oracle PLSQL-CodePudding

How to assign multiple values to variable using select query in PLSQL Oracle, example query is below

Select * into v_name from tbl_name;

CodePudding user response：

If you just declare the variable using %rowtype, it won't work if there's not exactly one row in the table (because, if it is empty, select will return no_data_found, and if there are two or more rows, you'll get too_many_rows), e.g.

SQL> declare
  2    v_name dept%rowtype;
  3  begin
  4    select *
  5    into v_name
  6    from dept;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

But, if you restrict number of rows, it'll work:

SQL> declare
  2    v_name dept%rowtype;
  3  begin
  4    select *
  5    into v_name
  6    from dept
  7    where rownum = 1;
  8
  9    dbms_output.put_line(v_name.dname);
 10  end;
 11  /
ACCOUNTING

PL/SQL procedure successfully completed.

SQL>

Though, I believe you'd actually want to use a collection:

SQL> declare
  2    type  v_dept_rec  is record (deptno number, dname varchar2(20), loc varchar2(10));
  3    type  v_dept_tab  is table of v_dept_rec;
  4    v_tab v_dept_tab;
  5  begin
  6    select *
  7    bulk collect
  8    into v_tab
  9    from dept;
 10
 11    for i in v_tab.first .. v_tab.last loop
 12      dbms_output.put_line(v_tab(i).dname);
 13    end loop;
 14  end;
 15  /
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>