CODEWITHSUNDEEP
oracle-databaseplsql
Naveed Ul Islam
Naveed Ul Islam

Reputation: 85

Assign multiple values into variable oracle PLSQL

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

Select * into v_name from tbl_name;

Upvotes: 1

Views: 3595

Answers (2)

Lucian Lazar
Lucian Lazar

Reputation: 82

Oracle database and PL/SQL language have two categories of data types: scalar and composite. Scalar data types can store one value. It can be number, character, date or large objects. Composite data types can store multiple values of the same type or of different types. There are two composite data types: collections and records. Collections are sets of components with the same data type. Records are structures with components of different data types. If you need to store values of the same type use a collection. If you need to store values of different types use a record.

There are three types of collections in Oracle – variable arrays that have a maximum number of elements, nested tables that are unbounded and can have gaps and associative arrays that can be indexed by strings. I attached an example of nested table because it's more flexible.

set serveroutput on;
declare
  type employees is table of varchar2(30);
  employee employees;
  first integer;
  last integer;
begin
  employee := employees('Kent', 'Wayne', 'Allen', 'Prince');
  first := employee.first;
  last := employee.last;
  dbms_output.put_line('First index: ' || first);
  dbms_output.put_line('Last index: ' || last);
  for i in first..last loop
    dbms_output.put_line('Element ' || i || ': ' || employee(i));
  end loop;
end;

There are three types of records in Oracle - the user defined records with a custom structure, the table based records that copy the structure of a table using the attribute ROWTYPE and the cursor based records that copy the structure of a cursor. I attached an example of user defined record because you can define the exact fields you need.

set serveroutput on;
declare
  type t_employees is record (name varchar2(30), department varchar2(30), salary number); 
  employee t_employees;
begin
  employee.name := 'Quinn';
  employee.department := 'DC';
  employee.salary := 2500;
  dbms_output.put_line(employee.name);
  dbms_output.put_line(employee.department);
  dbms_output.put_line(employee.salary);
end;

I hope this helps you. Good luck.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143103

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>

Upvotes: 3

Related Questions