How to assign multiple values to variable using select query in PLSQL Oracle, example query is below
Select * into v_name from tbl_name;
Add a comment
|
2 Answers
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>
1 Comment
Naveed Ul Islam
Yes the collection is solution
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.
