Database Interaction with PL/SQL: OBJECT and OBJECT - Accessing column based OBJECTs in PL/SQL
(Page 4 of 4 )
We will use the same concept in previous section, but with PL/SQL. Let us consider the following code.
declare
v_OffAddress t_Address;
v_ename varchar2(20) := 'jag';
begin
select OffAddress into v_OffAddress
from employees where ename = v_ename;
dbms_output.put_line(v_OffAddress.city);
end;
/
I hope the above program looks very much familiar to that of PL/SQL RECORD fetching. In this case, VALUE is not necessary as, we are not converting the whole row as OBJECT. Let us consider another example:
declare
TYPE tbl_Address is TABLE of t_Address index by pls_integer;
v_tblAddress tbl_Address;
v_ename varchar2(20) := 'jag';
begin
select OffAddress, ResAddress into v_tblAddress(0), v_tblAddress(1)
from employees where ename = v_ename;
dbms_output.put_line(v_tblAddress(0).city);
dbms_output.put_line(v_tblAddress(1).city);
end;
/
The above program introduces several new concepts (even though it looks almost the same as previous examples). Let me explain part by part.
TYPE tbl_Address is TABLE of t_Address index by pls_integer;
Till now, I never explained the part ‘index by…’. According to the above statement, I am creating a new data type ‘tbl_address’ which can hold any number of records (just in the form of a table) based on the OBJECT TYPE ‘t_Address’. The extension ‘index by pls_integer’ creates an index based table. This means the PL/SQL table gets indexed based on the records populated. The ‘pls_integer’ can also be replaced using ‘binary_integer’. Now each and every row within the table are accessible using an index (or counter).
v_tblAddress tbl_Address;
I hope you can understand this. We are just creating a variable ‘v_tblAddress’ based on the PL/SQL TABLE type ‘tbl_Address’.
select OffAddress, ResAddress into v_tblAddress(0), v_tblAddress(1)
from employees where ename = v_ename;
The above SELECT statement retrieves the objects available in the two columns (OffAddress and ResAddress) and places them within ‘v_tblAddress’ at two different locations i.e., 0 and 1.
dbms_output.put_line(v_tblAddress(0).city);
dbms_output.put_line(v_tblAddress(1).city);
Finally, we display the cities of both addresses as shown above.
It is not exactly necessary to follow the same method as above. But this way I can introduce the index based PL/SQL tables along with OBJECTs together. If necessary we can also use BULK COLLECT to retrieve more than one row. I leave this to you, the readers, as an exercise.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 20 
