Records

A record type stores logically related data as a unit. It contains at least one scalar or RECORD data type member, referred to as a field (FIELD) in PL/SQL. Fields store logically related but distinct information.

Initial values of record variables

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. The variable does not inherit the initial values of the referenced items.

Declare record constants

To declare a record constant, you must first create a function and initialize the record, 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 package_name.type_name. 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 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 the package header is incompatible with a local RECORD type that has the same definition.

When you define a variable of the RECORD 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. 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 the value of one record variable to another

You can assign the value of one record variable to another 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 for both variables. Additionally, corresponding fields must have the same data types.

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

• Use SQL statements to return rows to a PL 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 record variable.

Assign NULL to a record variable

When you assign NULL to a record variable, NULL is also assigned 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, use the dot (.) operator in the format: record_name.field_name.

You can create a record by using any of the following methods:

• Declare a RECORD type and then declare a variable of the 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, a type, and associate the type 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, a type, and associate the type with the type of a cursor. The syntax is as follows:

  variable_name cursor_name%ROWTYPE ;
  

  Note

  The fields of a record 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

  Record variables can be used like regular variables. You can use the SELECT INTO statement to assign values to them.