Updated March 31, 2023
Introduction to PLSQL rowtype
The definition of the columns or fields retrieved from the cursor table or cursor itself in PL/SQL is done using the attribute called %ROWTYPE. Each column or record in the field is considered to have its own datatype corresponding to the type of column it is declared as. The cursor variable name or cursor name is kept at the beginning at the prefix place if the %ROWTYPE attribute. In this article. We will study the syntax, working, and implementation of rowtype attributes in PL/ SQL. In this topic, we are going to learn about PLSQL rowtype.
Syntax
As the cursor name is prefixed while making the use of the ROWTYPE attribute, the syntax of the rowtype while using it in PL/ SQL is as shown below –
cursorRecord cursorName%ROWTYPEIn the above syntax, the terms used are as explained below –
cursorRecord – the cursorRecord is the value by which the record or field is identified.
curserName – The cursor name is the name of the cursor, which is declared explicitly in the current scope of the program.
Working of %ROWTYPE attribute
The cursor works as the pointer, which points to the individual field or record in the table. Whenever the usage of loops is done at that time, in order to refer to each of the individual records, we take the use of the sample cursor, which will store the temporary field to which we are currently referring. Thus, the complete record or the value of a single column can be pointed by the cursor.
Points to be considered
We can declare a variable in PL/ SQL as the name of the table%ROWTYPE datatype can prove very much helpful for data transferring between PL/SQL and tables of the database. The most important point here is that we can make the use of a single variable instead of multiple separate variables for each column of the database.
We don’t even need to have any idea regarding each of the columns of the table because instead of using the made-up variable names for each of the columns, we will be referring to each of the columns by using its real name. Thus, even if any of the changes are made in the structure of the table later on, which involves the addition and deletion of the columns from it, there will be no necessity to make any changes in the program or code that we have written.
We can refer to each of the individual fields of the record by making the use of dot symbol in the format name of the record. name of the field. Using this format, we can read and write one field at a time.
In order to assign all the fields value at once, we can make the use of one of the two mentioned methods below –
- Aggregate assignment of all the records can be done in PL/ SQL in case if they refer to the same cursor or table.
- SELECT or FETCH statements can be used to assign the list of values to a particular record. The order in which the names of the column are declared should be the same as they appear in the command. The selected and fetched items should have simple names, or if in case they are, expressions must have aliases if they are associated with %ROWTYPE.
Example of PLSQL rowtype
Given below are the example of PLSQL rowtype:
Example #1
Let us look at the example that demonstrates how the %ROWTYPE attribute can be used in PL/ SQL to retrieve the information about a particular entry in the table. Consider that we have a table called customer details which stores the details of the customers. To check the contents of the table, we can fire the following query in SQL –
SELECT * FROM [customers_details];The output of the execution of the above query statement is as shown below, showing the contents of the table customer details –
To retrieve the details of the table in such a way that each of the customer’s first name is retrieved and the contact details showing its mobile number is retrieved, we can create a procedure in PL/ SQL. In this procedure, we will make use of the cursor named sample cursor, which will point out all the resultset of the customer details table having its f_name and mobile number fields.
Further, we will loop through the result by rotating it in the loop where we will take each of the individual records of the cursor table contents one by one in a variable named sample variable. Then we will keep displaying the result in such a way that the DBMS output will contain the name and its corresponding mobile number with an in-between string attached as “having the mobile number”.
CREATE OR REPLACE PROCEDURE 2
IS
CURSOR sampleCursor IS SELECT f_name, mobile_number FROM customers_details
sampleVariable sampleCursor%ROWTYPE;
BEGIN
OPEN sampleCursor;
LOOP
FETCH sampleCursor INTO sampleVariable;
EXIT WHEN sampleCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( sampleVariable.f_name || ' has the contact number '
|| sampleVariable.mobile_number );
END LOOP;
CLOSE sampleCursor;
END;The output of the above code is as displayed below –
Example #2
Consider one more example where we have a table named Employees, which has the following contents in it when retrieved by using the query statement –
SELECT * FROM [Employees]The execution of the above instruction gives the following output –
Now, in case if we want to retrieve all the data of the Employees table in such a way that it should display the first name and then the birth date in the format – firstname has his birthday on birthdate. For that thing, we can make the use of the following procedure –
CREATE OR REPLACE PROCEDURE 2
IS
CURSOR sampleCursor IS SELECT FirstName, BirthDate FROM Employees
sampleVariable sampleCursor%ROWTYPE;
BEGIN
OPEN sampleCursor;
LOOP
FETCH sampleCursor INTO sampleVariable;
EXIT WHEN sampleCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( sampleVariable.FirstName || ' has his birthday on '
|| sampleVariable.BirthDate );
END LOOP;
CLOSE sampleCursor;
END;The output of the above procedure is as shown below –
Conclusion
The use of the %ROWTYPE attribute in PL/ SQL is mostly used for referring and having a cursor pointer to the records, which can be columns of the particular table or even the complete fields record itself.
Recommended Articles
We hope that this EDUCBA information on “PLSQL rowtype” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
