RECORD

A record type is a unit for storing logically related data. It contains at least one scalar or RECORD data type member, which is referred to as a field (FIELD) of the PL/SQL RECORD. The field stores logically related but distinct information.

Initial values of fields in a record variable

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

For a record variable declared with %ROWTYPE or %TYPE, the initial value of each field is NULL. This variable does not inherit the initial values of the referenced items.

Declare a record constant

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

Here is an 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

The RECORD type defined in a PL block is a local type.

The RECORD type defined in the package header is a public RECORD type. You can reference it from outside the package by using the package_name.type_name syntax. It is stored in the database and will be dropped when you execute the DROP PACKAGE statement.

You cannot create a RECORD type at the Schema level. Therefore, a RECORD type cannot be an ADT attribute data type.

When you define a RECORD type, you must specify its name and define its field types. To define a field, you must specify its name and data type. By default, the initial value of a field is NULL. You can specify the NOT NULL constraint for a field, in which case the initial value must not be NULL. If the NOT NULL constraint is not specified, the initial value can be NULL.

The RECORD type defined in the package header is not compatible with a local RECORD type that has the same definition.

When you define a RECORD variable type, you can use the data types supported by stored procedures or use the %TYPE method to copy the type of a table or cursor column. Here is an example:

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

In OceanBase Database, a record can be nested with other complex types, such as collections and arrays.

Here is an example of a RECORD type with a varray field:

DECLARE
  TYPE empname IS VARRAY(3) OF VARCHAR2(20);

  TYPE contact IS RECORD (
    name  empname := empname('Zhang','San','Feng'),  -- Varray field
    phone employees.phone_number%TYPE
  );
  emp contact;
BEGIN
   emp.phone := '123456789';
   DBMS_OUTPUT.PUT_LINE (
         emp.name(1) || ' ' ||
         emp.name(2) || ' ' ||
         emp.name(3) || ', ' ||
         emp.phone
   );
END;/
Query OK, 0 rows affected

Zhang San Feng, 123456789

Record variables

A record variable can be a single record variable or a composite record variable.

You can assign values to each field of a record variable separately. In some cases, you can also assign the value of one record variable to another. If a record variable represents all or part of a row in a database table or view, you can assign the corresponding row to the record variable.

Assign a record variable to another

Starting from OceanBase Database V4.2.4, you can use the constructor syntax to directly assign values to a variable of the RECORD type without assigning values to each field separately. If you need to assign values to each field separately, you can assign the value of one record variable to another only if the following conditions are met:

• Both variables have the same RECORD type.

• If the target variable is explicitly declared as a RECORD type and the source variable is declared using ％ROWTYPE based on the row type of a table, the number and order of fields in both variables must be exactly the same. Additionally, each corresponding field must have the same data type.

Here is an example of directly assigning values to a RECORD type variable using the constructor syntax:

DECLARE
  TYPE employee_record_type IS RECORD (
    id employee.id%TYPE,
    name employee.name%TYPE,
    salary employee.salary%TYPE
  );

  -- Assign values using the constructor syntax
  v_employee_rec employee_record_type := employee_record_type(1, 'John Doe', 55000.00);
BEGIN
  -- You can use the v_employee_rec variable here
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_rec.name || ', Salary: ' || v_employee_rec.salary);
END;
/

Here is an example of assigning values to each field separately:

DECLARE
  -- Declare a record type with fields matching the structure of the employee table.
  v_employee_rec employee%ROWTYPE;
BEGIN
  -- Assign values to each field separately
  v_employee_rec.id := 1;
  v_employee_rec.name := 'John Doe';
  v_employee_rec.salary := 55000.00;

  -- Print the information from the record
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_rec.name || ', Salary: ' || v_employee_rec.salary);
END;
/

Assign all or part of a row to a record variable

If a record variable represents all or part of a row in a database table or view, you can assign the corresponding row to the record variable. Here are the methods:

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

  The syntax for a simple SELECT INTO statement is:

  SELECT select_list INTO record_variable_name FROM table_or_view_name;
  

  For each column in the select_list, the record variable must have a corresponding field with a compatible data type. The order of columns in the select_list must match the order of fields in the record.

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

  The syntax for a simple FETCH statement is:

  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 field with a compatible data type. The cursor must be an explicit cursor or a strongly typed cursor variable.

• Use SQL statements to return rows to a PL/SQL record variable.

  The RETURNING INTO clause specifies the variable that stores the values returned by the statement to which the clause belongs. INSERT, UPDATE, and DELETE SQL statements with the RETURNING INTO clause can return row data to a PL/SQL record variable.

Assign NULL to a record variable

Assigning NULL to a record variable also assigns NULL to each of its fields.

This assignment is recursive. If a field is itself a record, its fields will also be assigned NULL.

Create a record

The internal components of a record are called fields. When you access the fields of a record, you must use the dot (.) operator. The syntax is: record_name.field_name.

You can create a record in the following ways:

• Declare a RECORD type and then declare a variable of the 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, a type, and associate the type with the row type of a table. Syntax:

  variable_name table_name%ROWTYPE ;
  

  Note

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

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

  variable_name cursor_name%ROWTYPE ;
  

  Note

  The fields of the record must correspond to the column names and data types in the SELECT query of the cursor. Here is an 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

  You can perform operations on a record variable in the same way as on a normal variable. You can use the SELECT INTO statement to assign values to the record variable.