PL/SQL - Record Type vs Rowtype

Question

When to use a record type, defined by the programmer, rather than declaring a variable of type %rowtype?

ex:

/**Definido pelo usuário**/
declare
type bbb is record(
id int,
nome varchar(100));
v bbb;
begin
select id,nome into v.id,v.nome from danilo.teste where id = 10;
dbms_output.put_line(v.nome);
end;
/

/**Utilizando o atributo %rowtype**/
declare
v danilo.teste%rowtype;
begin
select id,nome into v.id,v.nome from danilo.teste where id = 10;
dbms_output.put_line(v.nome);
end;
/

Is there any performance gain?

Answer 1

Basically ROWTYPE is associated with a physical table, while RECORD TYPE is associated with an object.

%ROWTYPE should be used whenever the query returns an entire row from a table or view.

TYPE rec RECORD should be used whenever the query returns columns from different tables or views and variables.

Example:

TYPE r_emp IS RECORD (eno emp.empno% type,ename emp ename %type
);
e_rec emp% ROWTYPE
cursor c1 is SELECT empno,deptno FROM emp;
e_rec c1 %ROWTYPE

Disadvantages

When we have a variable of type RECORD, we have to declare additional variables, but with %ROWTYPE, we can have all the fields that are present in the table.

Scenario example:

Like %ROWTYPE is used when you need to work with complete registration, and TYPE RECORD is used to create your own data type with a specific number of values to be retained. Suppose a table has 20 columns and you need to work with only seven columns. If you use %ROWTYPE, you get all 20 values unnecessarily. At the same time, your program will be a bit clumsy if you use seven TYPE. A better way to solve this solution is to define my own type of data, which may contain seven values.