Preamble
In Oracle PL/SQL, the %ROWTYPE attribute provides a record type representing a string in the Oracle database table (or view).
A record may store an entire string of data selected from the table, or be extracted from a cursor or a strictly typed cursor variable.
Syntax for declaring a variable with the %ROWTYPE attribute in Oracle PL/SQL
v_rec table_name%ROWTYPE
Parameters and arguments of the attribute
- v_rec – name of a variable which is assigned a record value
- table_name – name of the database table
Note:
- Columns in a row and corresponding fields in a record have the same names and data types.
- Fields in the %ROWTYPE record do not inherit the NOT NULL column limit.
Consider the Oracle example to understand how to apply %ROWTYPE in Oracle PL/SQL.
For example:
DECLARE
-- %ROWTYPE can include all columns in a table ...
emp_rec employees%ROWTYPE;
-- ... or a subset of columns based on the cursor.
CURSOR c1 IS
SELECT department_id, department_name FROM departments;
dept_rec c1%ROWTYPE;
-- You can even create %ROWTYPE with columns from several tables.
CURSOR c2 IS
SELECT employee_id, email, employees.manager_id, location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
join_rec c2%ROWTYPE;
BEGIN
-- We know that EMP_REC can contain a row from an EMPLOYEES table.
SELECT * INTO emp_rec FROM employees WHERE ROWNUM < 2;
-- We can refer to EMP_REC fields using column names.
-- from the EMPLOYEES table.
IF emp_rec.department_id = 20 AND emp_rec.last_name = 'JOHNSON' THEN
emp_rec.salary := emp_rec.salary * 1.15;
END IF;
END;
In this Oracle PL/SQL example, we declared the emp_rec variable based on the employees table entry. We also declared the join_rec variable based on the record of the c2 cursor created from the fields of the employees and departments tables.
An example with cumulative assignment
Consider an example that shows how to assign values to all fields in one record simultaneously. You can assign one record to another if their ads belong to the same table or cursor. For example, the following assignment is allowed:
DECLARE
dept_rec1%ROWTYPE;
dept_rec2 departments%ROWTYPE;
CURSOR c1 IS SELECT department_id, location_id FROM departments;
dept_rec3 c1%ROWTYPE;
BEGIN
dept_rec1 := dept_rec2; -- acceptable
-- dept_rec2 refers to the table, dept_rec3 refers to the cursor
-- dept_rec2 := dept_rec3; -- not allowed
END;
In this Oracle PL/SQL example, we declared the variables dept_rec1, dept_rec2 based on a table entry. We also declared the c1 cursor containing the department_id, location_id fields of the departments table.
Since the departments table is represented in the database by the fields department_id, department_name, manager_id, location_id, the assignment of dept_rec2 := dept_rec3 is unacceptable.
The following example shows how to assign a list of column values to a record using the SELECT instruction.
DECLARE
dept_rec departments%ROWTYPE;
BEGIN
SELECT * INTO dept_rec FROM departments
WHERE department_id = 30 and ROWNUM < 2;
END;
This example assigns a record value from the department’s table with department id equal to 30 to the dept rec variable.
Oracle pl sql tutorial; %TYPE and %ROWTYPE attributes
About Enteros
Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Database Performance with Enteros and AWS Resource Groups: A RevOps Approach to Streamlined Efficiency
- 13 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enhancing Healthcare Data Integrity: How Enteros, Logical Models, and Database Security Transform Healthcare Operations
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Budgeting and Cost Allocation in the Finance Sector with Enteros: A Smarter Approach to Financial Efficiency
- 12 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros and Cloud FinOps: Unleashing Big Data Potential for eCommerce Profitability
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…