Several points of attention of%rowtype

One,%rowtype only indicates the type of record referenced, but does not guarantee NOT NULL constraint

We know that by using%type and%rowtype you can specify the fields and record types that are referenced, but there is one particular area to note:

Both%type and%rowtype do not guarantee a NOT NULL constraint. For example:

DECLARE
My_empno Emp.empno%type; --emp.empno should not being null
...
BEGIN
My_empno: = NULL; --This works

The above code can still be performed if the Empno field of the EMP table cannot be empty.


ii. Assignment of%rowtype Records

It is not possible to initialize an assignment when%rowtype is declared, but there are two ways to assign values to all fields at once.

Method One: If two record types of declarations refer to the same data table or cursor , they can assign values to each other, such as:

DECLARE
DEPT_REC1 Dept% RowType;
DEPT_REC2 Dept% RowType;
CURSOR C1 is SELECT * from dept;
DEPT_REC3 c1% RowType;

BEGIN
.. .
DEPT_REC1: = DEPT_REC2--this Works

However, if one type refers to a data table and the other refers to a cursor, then they cannot be assigned to each other even if they represent the same content.

DEPT_REC2: = DEPT_REC3; --Not allowed

Because the DEPT_REC2 reference here is a table, and DEPT_REC3 references a cursor, even the contents of their query

Method Two: We can use the Select or FETCH statements to assign the obtained data to the record. However, the order of the field names defined in the table or view is the same as the order of names in the record.

DECLARE
Dept_rec Dept% RowType;
...
BEGIN
SELECT * into Dept_rec from dept WHERE deptno = 30;
...
End;

However, we cannot use assignment statements to assign the values in the field list to records. Therefore, the following grammatical form is not allowed:

Record_name: = (value1, value2, Value3, ...); --Not allowed

Iii. use of aliases

Data taken from a cursor, if the cursor definition contains an expression, we need to use an alias to correctly assign a value to the%rowtype type record:

DECLARE
CURSOR My_cursor is
SELECT sal + NVL (comm, 0) wages, ename from EMP;
My_rec my_cursor% RowType;
BEGIN
OPEN My_cursor;
LOOP
FETCH My_cursor
into My_rec;
EXIT when my_cursor% NotFound;
IF my_rec.wages > THEN
INSERT into temp VALUES (NULL, My_rec.wages, my_rec.ename);
End IF;
End LOOP;
Close my_cursor;
End;