insert PL/SQL record into Database

insert PL/SQL record into Database

 PL/SQL record is a collection of related fields, it can be single/multiple fields.

Suppose peter schema has following customers table.

SQL>DESC Customers
CUSTOMERID                                NOT NULL NCHAR(5)
COMPANYNAME                               NOT NULL NVARCHAR2(40)
CONTACTNAME                                        NVARCHAR2(40)
CONTACTTITLE                                       NVARCHAR2(30)
ADDRESS                                            NVARCHAR2(60)
CITY                                               NVARCHAR2(15)
REGION                                             NVARCHAR2(15)
POSTACODE                                          NVARCHAR2(10)
PHONE                                              NVARCHAR2(24)
FAX                                                NVARCHAR2(24)


Declare a variable cust  of type Customers ROW.
i.e cust  tablename%rowtype;

variable cust has all columns of customers table. and it can accommodate only one row.

PL/SQL Anonymous block Insert PL/SQL Record into Database.

SQL>
declare
cust customers%rowtype;
begin

cust.CUSTOMERID := 'ABCDE';
cust.COMPANYNAME :='PETER JOHNSON';
cust.CONTACTNAME:='PETER HANS';
cust.CONTACTTITLE:='Mr.';
cust.ADDRESS:='450 N Mathilda Ave.';
cust.CITY:='SunnyDale';
cust.REGION:='NA';
cust.POSTACODE:='95086';
cust.PHONE:='4084268989';
cust.FAX:='4084268989';

--insert PL/SQL record.
insert into customers values cust;

commit;
end;

Note:PL/SQL record insert --> instead of values just specify record type variable.

SQL> select * from customers where customerid='ABCDE';


CUSTO COMPANYNAME
----- ----------------------------------------
CONTACTNAME                              CONTACTTITLE
---------------------------------------- ------------------------------
ADDRESS                                                      CITY
------------------------------------------------------------ --------------
REGION          POSTACODE  PHONE                    FAX
--------------- ---------- ------------------------ -----------------------
ABCDE PETER JOHNSON
PETER HANS                               Mr.
450 N Mathilda Ave.                                          SunnyDale
NA              95086      4084268989               4084268989

Tags:insert PL/SQL record into Database,Declare PL/SQL record type,Using PL/SQL record type, assign values PL/SQL record, Initialize values to PL/SQL record type. PL/SQL record Insert.