Records

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

Initial values of record variables

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

For a RECORD type variable declared using %ROWTYPE or %TYPE, each field has an initial value of NULL. The 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

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

A RECORD type defined in a package header is a public RECORD type. You can reference it from outside the package by using the package_name.type_name notation. It is stored in the database and is 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 the types of its fields. 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.

A RECORD type defined in a 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 types 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, records 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

Record variables include single-record variables and composite record variables.

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 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.

Assign a record variable to another

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

• The two 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 must be the same. In addition, each corresponding field must have the same data type.

Here is an example of directly assigning a value to a record type variable using a constructor:

DECLARE
  TYPE employee_record_type IS RECORD (
    id employee.id%TYPE,
    name employee.name%TYPE,
    salary employee.salary%TYPE
  );
  -- Assign values using 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 that match 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 in 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 an SQL statement to return a row from a PL record variable.

  The RETURNING INTO clause specifies the variable that stores the values returned by the statement. INSERT, UPDATE, and DELETE SQL statements with the RETURNING INTO clause can return row data from a PL 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. To access the fields of a record, you need to use the dot operator (.) in the format: record_name.field_name.

You can create a record in the following ways:

• Declare a RECORD type and then declare a variable of that type. The syntax is as follows:

  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. The syntax is as follows:

  variable_name table_name%ROWTYPE ;
  

  Note

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

• Declare a variable, type, and associate it with the type of a cursor. The syntax is as follows:

  variable_name cursor_name%ROWTYPE ;
  

  Note

  The fields of a 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 regular variable. You can use the SELECT INTO statement to assign values to it.