PL/SQL - Working with Records | ROWTYPE data type

Records:

• A record is a composite datatype, which means that it can hold more than one piece of information, as compared to a scalar datatype such as string or number.
• Tables are made up of rows of data, each consisting of one or more colunns, so it stands to reason that Oracle database would make it as easy as possible to work with those rows of data inside a PL/SQL program, and it does precisely that through its implementation of the record. 
• Dealing one row at a time rather than long list of columns or variables,  is far better performance and easy to maintain.

Example:

CREATE OR REPLACE PROCEDURE process_customer

(

c_id IN customer.customer_id%type

)

IS

c_rec customer%rowtype;

BEGIN

Select * into c_rec FROM customer WHERE customer_id = c_id;

Dbms_output.put_line('First Name: ' || c_rec.first_name);

Dbms_output.put_line('Last Name: ' || c_rec.last_name);

END;

Passing Records as parameters:

CREATE OR REPLACE PROCEDURE process_customer

(

c_id IN customer.customer_id%type

)

IS

c_rec customer%rowtype;

BEGIN

Select * into c_rec FROM customer WHERE customer_id = c_id;

show_customer(c_rec);

END;

CREATE PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

Dbms_output.put_line('First Name: ' || customer_in.first_name);

Dbms_output.put_line('Last Name: ' || customer_in.last_name);

END;

/

Insert data using Records:

CREATE OR REPLACE PROCEDURE show_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

INSERT INTO CUSTOMER VALUES CUSTOMER_IN;

COMMIT;

END;

/

Update data using Records:

CREATE OR REPLACE PROCEDURE update_customer

(

customer_in IN customer%ROWTYPE

)

IS

BEGIN

UPDATE CUSTOMER SET ROW =  CUSTOMER_IN WHERE CUSTOMER_ID = CUSTOMER_IN.CUSTOMER_ID;

COMMIT;

END;

/

User defined Record types:

So far whatever records we have used , are based on table or cursor. We can declare our own user defined record using TYPE....RECORD statement.

Some times business requirement is such that it does not meet with table columns, so in that case, we can create custom record to overconme such hardle.

Syntax:

TYPE customer_rec IS RECORD

(

customer_name varchar2(100),

total_sales number,

...

)

customer_info_rec1 customer_rec;

customer_info_rec2 customer_rec;

Example:

CREATE OR REPLACE PROCEDURE process_customer

(

c_id IN customer.customer_id%type

)

IS

TYPE customer_rec IS RECORD

(

first_name varchar2(100);

last_name varchar2(100);

)

c_rec customer_rec;

BEGIN

Select first_name,last_name into c_rec FROM customer WHERE customer_id = c_id;

Dbms_output.put_line('First Name: ' || c_rec.first_name);

Dbms_output.put_line('Last Name: ' || c_rec.last_name);

END;