Records

A RECORD type stores logically related data as a unit. It contains at least one scalar or RECORD data type as a member, which is referred to as a field of the PL record. The fields store different but logically related information.

The internal components of a record are referred to as fields. A field in a record is accessed by using the format of record name.field name.

Initial values of record variables

For a variable of the RECORD type, the initial value of each field is NULL unless otherwise specified when the RECORD type is defined.

For a record variable declared by using %ROWTYPE or %TYPE, the initial value of each field is NULL. The variable does not inherit the initial value of the referenced item.

Declare record constants

When you declare a record constant, you must first create a function, initialize the record, and call the function in the constant declaration.

Example:

obclient> CREATE OR REPLACE PACKAGE my_rec_types IS
  TYPE rec IS RECORD (a NUMBER, b NUMBER,k NUMBER);
  FUNCTION init_rec RETURN rec;
END my_rec_types;
/
Query OK, 0 rows affected

obclient> CREATE OR REPLACE PACKAGE BODY my_rec_types IS
  FUNCTION init_rec RETURN rec IS
    c_rec REC;
  BEGIN
    c_rec.a := 111;
    c_rec.b := 222;
    c_rec.k := 333;
    RETURN c_rec;
  END init_rec;
END my_rec_types;
/
Query OK, 0 rows affected

obclient> DECLARE
  r CONSTANT my_rec_types.rec := my_rec_types.init_rec();
BEGIN
  DBMS_OUTPUT.PUT_LINE('r.a = ' || r.a);
  DBMS_OUTPUT.PUT_LINE('r.b = ' || r.b);
  DBMS_OUTPUT.PUT_LINE('r.k = ' || r.k);
END;
/
Query OK, 0 rows affected

r.a = 111
r.b = 222
r.k = 333

Record types

A RECORD type defined in a PL block is a local type. It can be used only in a block and is stored in the database only when the block is located in an independent subprogram or in a subprogram of a program package.

A RECORD type defined in a package specification is a public item. You can use package_name.type_name to limit the package name and reference the RECORD type from outside of the package. The RECORD type is stored in the database, and will also be dropped when you use the DROP PACKAGE statement to drop the program package.

You cannot create a RECORD type at the schema ``level. Therefore, a RECORD type cannot be an abstract data type (ADT).

When you define a RECORD type, you must specify its name and fields. To define a field, you need to specify its name and data type. By default, the initial value of a field is NULL. You can specify a NOT NULL constraint for a field. In this case, the initial value must not be NULL. If you do not specify a NOT NULL constraint for a field, its initial value can be NULL.

A RECORD type defined in a package specification is incompatible with a local RECORD type with the same definition.

When you define a RECORD variable type, you can use a data type supported by the stored procedure, or use the %TYPE method to copy the type of the table or cursor column.

Example:

DECLARE
   TYPE region_record_type IS RECORD (
       id REGIONS.region_id%TYPE,
       name REGIONS.region_name%TYPE
   );
   region_record region_record_type;

Record variables

Record variables include individual record variables and composite record variables.

You can assign a value to each field of a record variable. In some cases, you can also assign the value of one record variable to another. If a record variable represents a full or partial row of a database table or view, you can assign the value of the row to the record variable.

Assign the value of one record variable to another

You can assign the value of one record variable to another only when the following conditions are met:

• The two variables have the same RECORD type.

• The target variable is declared by using a RECORD type, and the source variable is declared by using ％ROWTYPE. Their fields match in quantity and sequence, and the corresponding fields have the same data type.

Assign a full or partial row to a record variable

If a record variable represents a full or partial row of a database table or view, you can assign the row to the record variable by using the following methods:

• Use SELECT INTO to assign the row to the record variable.

  Syntax of a simple SELECT INTO declaration:

  SELECT select_list INTO record_variable_name FROM table_or_view_name;
  

  For each column in select_list, the record variable must have a corresponding field that is compatible with the column in type. The sequence of columns in select_list must be the same as the sequence of fields in the record variable.

• Use FETCH to assign the row to the record variable.

  Syntax of a simple FETCH declaration:

  FETCH cursor INTO record_variable_name;
  

  The cursor is associated with a query. For each column selected by the query, the record variable must have a corresponding compatible field that is compatible with the column in type. The cursor must be an explicit cursor or a strong cursor variable.

• Use SQL statements to return the rows in the PL record variable

  The INSERT, UPDATE, and DELETE statements can be used in conjunction with the RETURNING INTO clause to return the rows in the PL record variable.

Assign the NULL value to a record variable

When you assign the NULL value to a record variable, the NULL value is assigned to each field in this record variable.

The assignment is recursive. If a field is a record, its fields are also assigned the NULL value.

Create a record

The following three methods are available for you to create a record:

• Declare a RECORD type and then declare a variable of this type. Syntax:

TYPE record_name IS RECORD
( field_name data_type [:= initial_value]
[, field_name data_type [:= initial_value ] ]... );

variable_name record_name;

• Declare a variable type and associate it with the row type of a table. Syntax:

variable_name table_name%ROWTYPE ;

Note

The fields in the record variable must correspond to the column names and data types of the table.

• Declare a variable type and associate it with the data type of a cursor. Syntax:

variable_name cursor_name%ROWTYPE ;

Note

The fields in the record variable must correspond to the column names and data types in the SELECT query of the cursor.

Example:

obclient> DECLARE
    TYPE my_rec IS RECORD(
          code VARCHAR2(10),
          name VARCHAR2(50) NOT NULL :='book');
    v_book MY_REC;
BEGIN
    v_book.code :='10000';
    v_book.name :='OceanBase PL Developer Guide';
    DBMS_OUTPUT.PUT_LINE(v_book.code||': '||v_book.name);
END;
/
Query OK, 0 rows affected

10000: OceanBase PL Developer Guide

Note

The operations on record variables are the same as those on common variables. You can use the SELECT statement to assign values to record variables.