G-Tech Moment: Principles and Applications of Record Types in GBase 8s Database

Published on 2025-05-08

In PL/SQL programming within the database, the record type is a very important data structure that allows developers to define a composite data type containing multiple fields. This type of data structure is highly flexible when handling complex data, effectively improving code readability and maintainability.

This article uses GBase 8s database as an example to discuss the features, use cases, and practical applications of record types, helping developers and database users better understand and apply this functionality.

To use PL/SQL syntax, you must explicitly set the environment variable SQLMODE to ORACLE. By default, GBase 8s has SQLMODE set to GBASE, which does not support PL/SQL syntax. Therefore, before writing PL/SQL code, ensure that the environment variable has been set correctly.

Syntax

Creating a Record Type

  • A record type can be defined in an anonymous block and is effective only within that block;

  • A record type can be defined as a public type in a package, making it globally effective;

  • It cannot be created externally using CREATE TYPE.

Ways to Create a Record Variable

  • Define a RECORD type and then declare a variable of that type;

  • Use %ROWTYPE to declare a record variable that represents a full or partial row of a database table or view;

  • Use %TYPE to declare a record variable with the same type as a previously declared record variable.

Initial Values of Record Type Variables

  • For a RECORD-type record variable, the initial value of each field is NULL unless you specify a different initial value when defining the type;

  • For record variables declared with %ROWTYPE or %TYPE, the initial value of each field is NULL. The variable does not inherit the initial values of the referenced item.

Applications of Record Types

1) Declare a record variable that always represents a full row.

variable_name table_or_view_name%ROWTYPE;

Note: If the table contains virtual columns, you cannot directly insert the record variable value into the table; you must insert individual fields that do not include virtual columns. If the table contains invisible columns, %ROWTYPE does not include that column.

2) Declare a record variable that always represents a partial row.

variable_name cursor%ROWTYPE;

3) Assign values between variables of the same type.

name1 := name2;

4) %ROWTYPE and RECORD variables can be assigned to each other.

5) Use SELECT INTO to assign a row to a record variable.

SELECT select_list INTO record_variable_name FROM table_or_view_name;

6) Use FETCH to assign a row to a record variable.

FETCH cursor INTO record_variable_name;

7) Use SQL statements to return the value of a PL/SQL record type variable.

SELECT select_list INTO record_variable_name FROM table_or_view_name;

8) Assigning NULL to a record variable assigns NULL to each field of the variable.

Example Scenario

Execution Result

The record type in GBase 8s provides developers with a flexible data structure that can effectively simplify the processing of complex data. By properly applying record types in real-world development, you can improve code readability and maintainability, further enhancing development efficiency.

Live Webinar: GBase Database Applications in Core Rail Transit Systems

GBase 8s, independently developed by General Data Technology Co., Ltd., is an enterprise-class domestic transactional database that is mature, stable, and meets the national security standard at Level 4. It supports shared-storage clusters and active-active-three-site high-availability deployments, providing stable data services for mission-critical core systems in finance, telecommunications, energy, transportation, and other industries.

In the rail transit sector, GBase 8s has been deployed across 70+ lines in 27 Chinese cities, including Shenzhen, Chongqing, Xi'an, Chengdu, Zhengzhou, Hangzhou, Shenyang, and Jinan. It supports core business systems such as AFC (Automatic Fare Collection), clearing centers, multi-line centers, internet ticketing systems, integrated monitoring systems, and operation control centers, delivering efficient, convenient, and stable travel services to citizens.

Tonight at 19:30, Wang Dingzhuo, Technical Director of the Energy & Transportation Department at General Data Technology Co., Ltd., will share a keynote titled “GBase Empowers Domestic Database Selection in Rail Transit”. Experience the technical features of GBase 8s in core business scenarios. There will also be a lucky draw with gifts. We welcome you to reserve your spot!