Records with UPDATE Statement
In PL/SQL, records are used with UPDATE statements is update the existing data in the database tables. Records are allowed to be organized and manipulate the data in a structured manner. It will make the work with many fields as a single unit.
DECLARE
-- Define a record type
TYPE employee_record_type IS RECORD (
emp_id employees.employee_id%TYPE,
emp_name employees.employee_name%TYPE,
emp_salary employees.salary%TYPE
);
-- Declare a record variable
emp_rec employee_record_type;
BEGIN
-- Assign values to the record fields
emp_rec.emp_id := 101;
emp_rec.emp_name := 'John Doe';
emp_rec.emp_salary := 55000; -- Updated salary
-- Update data in the employees table using the record
UPDATE employees
SET employee_name = emp_rec.emp_name,
salary = emp_rec.emp_salary
WHERE employee_id = emp_rec.emp_id;
-- Commit the transaction
COMMIT;
END;
/
Here, in the above INSERT and UPDATE Statements examples:
- we can define the record type ’employee_record_type’ that matches with the structure of the ’employee’ table.
- A record variable ‘emp_rec‘ is declared as ’employee_record_type’.
- The values are assigned to the fields of the variables of the record.
- For the INSERT statement, we can use record fields directly in the values of the table.
- For the UPDATE statement, we can use record fields to update the corresponding columns in the table.
PL/SQL Records
PL/SQL stands for Procedural Language/Structured Query Language. It is an extension of the Structured Query Language (SQL). SQL is used in the Oracle databases for procedural programming. The main feature of PL/SQL is it can work with the composite data types, like records. PL/SQL records can be provided the binding the data into a single unit. It consists of multiple fields and each field has its data type and its name, it defines the structure of the record. These records are declared at the package, block, or schema level. PL/SQL records are allowed to developers for groups of the data under a single name, creating a structured and manageable way to handle data within the programs.