Records in PL/SQL

In this blog we will explore the record in PL/SQL.

Record is composite data structure i.e. composed of one or more fields. Similar in concept to rows of any database table. Likewise database table has one or many columns of various datatypes, and each column store individual values. Similarly a record, composed of one or more fields. As a whole it does not store any values, each individual element has its own value.

Declaring Records

There are three ways we can declare a record.

  • Table-Based Records
  • Cursor-Based Records
  • User-Defined Records

Table-Based Records

Creating record ‘my_record_table‘ based on row of table user-objects : Using %ROWTYPE – attribute provides a record type that represents a row in a database table. See below example:

set serveroutput on;
DECLARE
  my_record_table user_objects%ROWTYPE;
BEGIN
  SELECT * INTO my_record_table FROM user_objects WHERE object_name='DUAL';
  dbms_output.put_line('OBJECT_TYPE : '|| my_record_table.OBJECT_TYPE);
END;
/

Cursor-Based Records

Creating record ‘my_record_cursor‘ based on row of explicit cursor. See below example.

DECLARE
  CURSOR my_cursor
  IS
    SELECT * FROM user_objects WHERE object_name='DUAL';
  my_record_cursor my_cursor%rowtype;
BEGIN
  OPEN my_cursor;
  FETCH my_cursor INTO my_record_cursor;
  dbms_output.put_line('OBJECT_TYPE : '|| my_record_cursor.OBJECT_TYPE);
  CLOSE my_cursor;
END;
/

User-Defined Records

Defining a rcords using TYPE & Records statement. Later on creating instance of that type. my_record

TYPE type_name IS RECORD
   (field1 data_type1 [NOT NULL] := [DEFAULT VALUE],
    field2 data_type2 [NOT NULL] := [DEFAULT VALUE],
    field3 data_type3 [NOT NULL] := [DEFAULT VALUE]
    ...
    );

See below example.

DECLARE
type my_rec_type
IS
  record
  (
    OBJECT_TYPE user_objects.OBJECT_TYPE%TYPE ,
    CREATED user_objects.CREATED%TYPE);
  my_record my_rec_type;
BEGIN
  SELECT OBJECT_TYPE,
    CREATED
  INTO my_record
  FROM user_objects
  WHERE object_name='DUAL';
  dbms_output.put_line('OBJECT_TYPE : '|| my_record.OBJECT_TYPE);
  dbms_output.put_line('CREATED : '|| my_record.CREATED);
END;
/

Creating Record Type at Database Level

It can be created at database level, which can be stored type as database object. Later on you can use in your PL/SQL subprograms or anonymous block. It like you have created a new data type.

CREATE type obj_type
AS
  OBJECT
  (
    OBJECT_NAME VARCHAR2(128),
    OBJECT_TYPE VARCHAR2(23) ,
    CREATED     DATE );
  /
  SELECT * FROM user_objects WHERE OBJECT_NAME='OBJ_TYPE' ;

Thanks!

Happy Learning! Your feedback would be appreciated!

One thought on “Records in PL/SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s