PL-SQL Table based Records

PL-SQL Table based Records-Learn how to declare Table Based records with %ROWTYPE and %TYPE with Examples. Also learn use of %ROWTYPE, %TYPE while managing data in the tables.

To declare a table based Record, use table Name with %ROWTYPE.
for a table record always reflects the current structure of a table and it makes useful whem managing the information stored in that table.

syntax

DECLARE
    table_name%ROWTYPE;

<record_name>is the name of the record.
<table_name> is the name of a table or view whose structure forms the basis for the record.
%TYPE attribute automatically provides the column’s datatype to the variable.
%ROWTYPE provides the datatypes of each of the columns in a table for the record’s fields.

After declaring the table based record we can use it multiple ways like we can select the data into a record variable as shown in the Example Program -1.

To declare particular field use table_name.column_name%type

syntax

table_based_column  table_name.column_name%type

Example Program-1 – Program to declare table_name with %ROWTYPE

DECLARE
--%ROWTYPE provides the datatypes of each of the columns
-- in a table for the record's fields.
  EMPLOYEE_TBL EMPLOYEE_TBL1%ROWTYPE;
BEGIN
  SELECT * INTO EMPLOYEE_TBL FROM EMPLOYEE_TBL1 WHERE id=10;
  dbms_output.put_line('Employee ID: ' || EMPLOYEE_TBL.id);
  dbms_output.put_line('Employee Name: ' || EMPLOYEE_TBL.name);
  dbms_output.put_line('Employee Gender: ' || EMPLOYEE_TBL.Gender);
  dbms_output.put_line('Employee Salary: ' || EMPLOYEE_TBL.salary);
END;

Output

Employee ID: 10
Employee Name: Russell
Employee Gender: Male
Employee Salary: 8800

Example Program -2- Program to declare table_name.colum_name with %TYPE

DECLARE
--%ROWTYPE provides the datatypes of each of the columns in a
-- table for the record's fields.
  EMPLOYEE_TBL EMPLOYEE_TBL1%ROWTYPE;
-- %TYPE attribute automatically provides the column's 
--datatype to the variable

-- declaring field type of table and initializing the value.
  emp_id  EMPLOYEE_TBL1.id%type :=5;
BEGIN
  SELECT * INTO EMPLOYEE_TBL FROM EMPLOYEE_TBL1 WHERE id=emp_id;
  dbms_output.put_line('Employee ID: ' || EMPLOYEE_TBL.id);
  dbms_output.put_line('Employee Name: ' || EMPLOYEE_TBL.name);
  dbms_output.put_line('Employee Gender: ' || EMPLOYEE_TBL.Gender);
  dbms_output.put_line('Employee Salary: ' || EMPLOYEE_TBL.salary);
END;

Ouput

Employee ID: 5
Employee Name: Todd
Employee Gender: Male
Employee Salary: 2800

PL-SQL Table based Records