The Record data type in Oracle is a composite data type that allows you to store multiple related values as a single unit. It is similar to a structure in programming languages like C or C++. A record is a collection of fields, each with its own name and data type.
To define a record data type in Oracle, you use the %ROWTYPE attribute. It allows you to create a record type that matches the structure of a table or cursor. This means that you can easily fetch rows from a table or query into a record variable without explicitly specifying each column.
Defining Record Data Type
To define a record data type, you can use the following syntax:
TYPE record_type_name IS RECORD
(
field1 data_type1,
field2 data_type2,
..
);
Here, record_type_name is the name you give to the record type, and field1, field2, . are the names of the fields within the record. Each field has its own data type.
Example:
TYPE employee_rec IS RECORD
(
emp_id NUMBER,
emp_name VARCHAR2(100),
emp_salary NUMBER
);
In this example, we define a record type called employee_rec, which has three fields: emp_id (NUMBER), emp_name (VARCHAR2), and emp_salary (NUMBER).
Using Record Data Type
Once you have defined a record data type, you can use it to declare variables and fetch rows from tables or cursors. Here’s how you can do it:
DECLARE
variable_name record_type_name;
BEGIN
SELECT column1, column2, .
INTO variable_name
FROM table_name;
-- You can now access the fields of the record using dot notation
dbms_output.put_line('Employee ID: ' || variable_name.emp_id);
dbms_output.put_line('Employee Name: ' || variable_name.emp_name);
dbms_output.put_line('Employee Salary: ' || variable_name.emp_salary);
END;
In this code snippet, we declare a variable of type record_type_name. Then we fetch the values from a table or cursor into that variable using the SELECT INTO statement. Finally, we can access the individual fields of the record using dot notation.
DECLARE
emp employee_rec;
BEGIN
SELECT emp_id, emp_name, emp_salary
INTO emp
FROM employees
WHERE emp_id = 100;
dbms_output.put_line('Employee ID: ' || emp.put_line('Employee Name: ' || emp.put_line('Employee Salary: ' || emp.emp_salary);
END;
In this example, we declare a variable emp (employee_rec) and fetch the values of an employee with ID 100 from the employees table into that variable. We then print the values of the individual fields using dot notation.
Conclusion
The record data type in Oracle provides a way to store and manipulate related values as a single unit. It allows you to define record types that match the structure of tables or cursors, making it convenient to fetch rows into record variables.
By using dot notation, you can access the individual fields of a record and perform operations on them. Understanding how to use record data types can greatly simplify your code and improve its readability.