Records

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

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

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 specification is a public RECORD type. You can use package_name.type_name to limit the package name and reference the 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 field type. 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. 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 data types such as collections and arrays.

The following example shows 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 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.

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

Here is an example of assigning values to record variables by using a constructor function:

DECLARE
  TYPE employee_record_type IS RECORD (
    id employee.id%TYPE,
    name employee.name%TYPE,
    salary employee.salary%TYPE
  );
  -- Assign values by using the syntax of a constructor function.
  v_employee_rec employee_record_type := employee_record_type(1, 'John Doe', 55000.00);
BEGIN
  -- Here you can use the `v_employee_rec` variable.
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_rec.name || ', Salary: ' || v_employee_rec.salary);
END;
/

Here is an example of assigning a value to each field separately:

DECLARE
  -- Declare the record type and specify that a field of this record type has the same structure as the `employee` table.
  v_employee_rec employee%ROWTYPE;
BEGIN
  -- Assign a value 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 output record.
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_rec.name || ', Salary: ' || v_employee_rec.salary);
END;
/

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 one of the following methods:

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

  The syntax of a simple SELECT INTO declaration is as follows:

  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.

  The syntax of a simple FETCH declaration is as follows:

  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 that is compatible with the column in type. The cursor must be an explicit cursor or a strongly-typed cursor variable.

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

  The RETURNING INTO clause specifies the variable that stores the value returned by the statement to which the clause belongs. The INSERT, UPDATE, and DELETE SQL statements with the RETURNING INTO clause can 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 internal components of a record are referred to as fields. A field in a record is accessed in the format of record name.field name.

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

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

  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 of the record type and associate it with the data type of a cursor. The syntax is as follows:

  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.

  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

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