DML : RECORDS

We can use records in Insert and Update statements.

Record based Inserts:

We can use records that are
1.Based on %ROWTYPE declarations against the table to which the insert is to be made.
2.Explicit record type that is compatible with the structure of the table.


Example:

Create table student
(Rno number,
 name varchar2(100),
 branch varchar2(10));
insert into student values (1,'Kiran','ECE');
insert into student values (2,'Gowtham','ECE');
insert into student values (3,'Lokesh','CSE');
commit;

-- Based on %ROWTYPE declarations against the table to which the insert is to be made.

Declare
  student_rec  student%rowtype;
Begin
  student_rec.rno:=4;
  student_rec.name:='Srikanth';
  student_rec.branch:='ECE';
  Insert into student values student_rec;
  commit;
End;
/

-- Explicit record type that is compatible with the structure of the table.

Declare
  Type student_t is record  (  rno  student.rno%type,
                                            name student.name%type,
                                           branch student.branch%type);
  student_rec student_t;
Begin
  student_rec.rno:=5;
  student_rec.name:='Vijay krishna';
  student_rec.branch:='CSE';
  Insert into student values student_rec;
  commit;
End;
/

Note:
          Don't include parentheses around the record specifier.

Record based updates:(keyword: ROW)

We must update an entire row with the ROW syntax.

Declare
Type student_t is record  (  rno  student.rno%type,
                             name student.name%type,
                             branch student.branch%type);
student_rec student_t;
Begin
student_rec.rno:=5;
student_rec.name:='Vijay krishna P';
student_rec.branch:='CSE';
update student set row=student_rec
where rno=student_rec.rno;
commit;
End;
/