Notes for % rowtype

1.% rowtype only specifies the type of the referenced record, but the not null constraint is not guaranteed.

We know that % Type and % rowtype can be used to specify the referenced field and record type respectively, but there is a special note:

Neither % type nor % rowtype guarantee the not null constraint. For example:

Declare
My_empno EMP. empno % type; -- EMP. empno shocould not be null
...
Begin
My_empno: = NULL;--This worksWell

The above code can still be executed if the empno field in the EMP table cannot be blank.

Ii. Value assignment of % rowtype records

Initialization assignment cannot be performed when % rowtype is declared, but there are two ways to assign values to all fields at a time.

Method 1: assume that the declaration of two record types referencesSame data table or cursorThen they can assign values to each other, such:

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 well

However, if one type references a data table and the other references a cursor, no value can be assigned to each other even if the content is the same.

Dept_rec2: = dept_rec3;--Not allowed

Because dept_rec2 references tables and dept_rec3 references cursors, even if they query the same content

Method 2: Use the select or fetch statement to assign the obtained data to the record. However, the order of field names defined in a table or view must be the same as that in a record.

Declare
Dept_rec dept % rowtype;
...
Begin
Select * into dept_rec from Dept where deptno = 30;
...
End;

However, values in the field list cannot be assigned to records using the value assignment statement. Therefore, the following syntax form is not allowed:

Record_name: = (value1, value2, value3 ,...);-- Not Allowed 

III,Use alias

The data retrieved from the cursor. If the cursor definition contains an expression, we need to use an alias to correctly assign values to the % rowtype 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> 2000 then
Insert into temp values (null, my_rec.wages, my_rec.ename );
End if;
End loop;
Close my_cursor;
End;