In the previous tutorial we learnt that the record datatype variables are composite data structures made up of different components called fields that have the same name and data types as that of the columns of a table using which you created that record. There we saw that there are three types of record variables available in Oracle Database. In this tutorial we will concentrate on the first type of record variable which is “Table Based Record Datatypes” Variables.
What are Table Based Record Datatype Variables?
As the name suggests Table Based Record Datatype Variables are variable of record datatype created over a table of your database.
Whenever you declare a record type variable oracle engine will declare a composite data structure with fields corresponding to each column of the specified table. For example if we have a record variable created using Employees table then that variable has 11 fields corresponding to 11 columns of the employees table. Also all these fields have the same name, data type and data width as that of the columns of the table.
How to Declare a Table Based Record Datatype Variables in Oracle Database?
The declaration of a table based record datatype variables is very similar to the declaration of simple user variables.
SET SERVEROUTPUT ON; DECLARE v_emp employees%ROWTYPE;
That is the declaration section of an anonymous PL/SQL block where we declared a record type variable with the name v_emp. V_emp record variable is based on employees table of the HR sample user. As this record variable is based on “employee” table of HR sample user thus it will have 11 fields corresponding to the 11 columns of the employees table.
How to Initialize Record Datatype Variables in Oracle Database?
In Oracle Database a record data type variable can be initialized by multiple ways similar to the simple user variables which we have seen in PL/SQL tutorial 2. However in this tutorial we will focus on the three most common ways of initializing a Record type variable. These are:
- By fetching data from all the columns of a row of a table into the record variable using SELECT-INTO statement.
- By fetching data from selected columns of the table into the record variable using SELECT-INTO statement.
- By directly assigning the value into the record variable using assignment operator.
We will discuss each of the above ways of initializing a record data type variable one tutorial at a time. In this tutorial we will focus on the first way of initializing database records which is by Fetching data from all the columns of a row of a table.
Reference Book for SQL Expert 1z0-047 Affiliate link
OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)
Initialize Record Variable by Fetching Data from All the Columns (SELECT-INTO Statement)
We already know that whenever you declare a record type variable oracle engine will declare a composite data structure with fields corresponding to each column of the specified table. We can initialize all these fields by fetching the data from all the columns of a row of the table using SELECT-INTO statement.
SELECT * INTO v_emp FROM employees WHERE employee_id = 100;
As we are talking about fetching data from all the columns thus we are using the SELECT * statement and storing the data into our record variable V_EMP.
Take a note here that a table based record variable can hold data of a single row at a time, thus you have to make sure that the SELECT-INTO statement returns the data from one row only and for that you can filter the data using WHERE clause, as we did in the above statement.
For more detailed knowledge on SELECT-INTO statement please read PL/SQL Tutorial 3.
Till so far we have learnt how to Declare and Initialize a database record type variable, next we will see how to access data from the record data type variable in Oracle Database.
How to access data from the record datatype variables in Oracle Database?
In order to access the data stored into a record variable we use the dot notation. In which we first write the name of our record variable followed by a dot and then the name of the field (or the name of the column) as both fields of a Table based Record Type variable and the column of the table over which the record variable is declared share the same name whose data we want to retrieve.
DBMS_OUTPUT.PUT_LINE (v_emp.first_name ||' '||v_emp.salary);
Now that we have learnt how to Declare and Initialize table based record type variables in Oracle Database. Now let’s put together all these pieces into a single PL/SQL block
Example: Table based record type variable initialization with SELECT asterisk (*) statement.
SET SERVEROUTPUT ON; DECLARE v_emp employees%ROWTYPE; BEGIN SELECT * INTO v_emp FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE (v_emp.first_name ||' '||v_emp.salary); DBMS_OUTPUT.PUT_LINE(v_emp.hire_date); END; /
That is a quick tutorial on How to Declare, Initialize and access data of a record data type variable in Oracle Database. Stay tuned as in the next tutorial we will learn another way of initializing a record variable.
If you enjoyed reading then make sure to share this article on your Social Media.
Do make sure to tag me because I am giving away RebellionRider’s merchandise to randomly selected winners every week. Thanks & Have a Great Day!