Arrays and Records

Arrays

Use of Array Types

Before the use of arrays, an array type needs to be defined:

Define an array type immediately after the AS keyword in a stored procedure. The method is as follows:

TYPE array_type IS VARRAY(size) OF data_type;

In the preceding information:

• array_type: indicates the name of the array type to be defined.
• VARRAY: indicates the array type to be defined.
• size: indicates the maximum number of members in the array type to be defined. The value is a positive integer.
• data_type: indicates the types of members in the array type to be created.

NOTE:

• In MogDB, an array automatically increases. If an access violation occurs, a null value is returned, and no error message is reported.
• The scope of an array type defined in a stored procedure takes effect only in this storage process.
• It is recommended that you use one of the preceding methods to define an array type. If both methods are used to define the same array type, MogDB prefers the array type defined in a stored procedure to declare array variables.
• data_type can also be a record type defined in a stored procedure (anonymous blocks are not supported), but cannot be an array or collection type defined in a stored procedure.

MogDB supports access to array elements by using parentheses, and it also supports the extend, count, first, last, prior, exists, trim, next, and delete functions.

NOTE:

• If a stored procedure contains a DML statement (such as SELECT, UPDATE, INSERT, and DELETE), you are advised to use square brackets to access array elements. Using parentheses will access arrays by default. If no array exists, function expressions will be identified.
• When the CLOB size is greater than 1 GB, the table of type, record type, and CLOB cannot be used in the input or output parameter, cursor, or raise info in a stored procedure.

Sets

Use of Set Types

Before the use of sets, a set type needs to be defined.

Define a set type immediately after the AS keyword in a stored procedure. The definition method is as follows:

In the preceding information:

• table_type: indicates the name of the set type to be defined.
• TABLE: indicates the set type to be defined.
• data_type: indicates the types of members in the set to be created.
• indexby_type: indicates the type of the set index to be created.

NOTE:

• In MogDB, a set automatically increases. If an access violation occurs, a null value is returned, and no error message is reported.
• The scope of a set type defined in a stored procedure takes effect only in this stored procedure.
• The index can only be of the integer or varchar type. The length of the varchar type is not restricted.
• NOT NULL has no function but only takes effect in the syntax.
• data_type can also be the record type or set type defined in a stored procedure (anonymous blocks are not supported), but cannot be the array type.
• Variables of the nested set type cannot be used across packages.
• Variables of the TABLE OF index by type cannot be nested in a record as the input and output parameters of a stored procedure.
• Variables of the TABLE OF index by type cannot be used as input and output parameters of functions.
• The RAISE INFO command cannot be used to print the entire nested TABLE OF variable.
• The TABLE OF variable cannot be transferred across autonomous transactions.
• The input and output parameters of a stored procedure cannot be defined as the nested TABLE OF type.

MogDB supports access to set elements by using parentheses, and it also supports the extend, count, first, last, prior, next, and delete functions.

The set functions support multiset union, intersect, except all, and distinct.

NOTE: An expression can contain only one variable of the TABLE OF index by type.

record

record Variables

Perform the following operations to create a record variable:

Define a record type and use this type to declare a variable.

Syntax

For the syntax of the record type, see [Figure 1](#Syntax of the record type).

Figure 1 Syntax of the record type

The above syntax diagram is explained as follows:

• record_type: record name
• field: record columns
• datatype: record data type
• expression: expression for setting a default value

NOTE: In MogDB:

• When assigning values to record variables, you can:
  • Declare a record type and define member variables of this type when you declare a function or stored procedure.
  • Assign the value of a record variable to another record variable.
  • Use SELECT INTO or FETCH to assign values to a record type.
  • Assign the NULL value to a record variable.
• The INSERT and UPDATE statements cannot use a record variable to insert or update data.
• Just like a variable, a record column of the compound type does not have a default value in the declaration.
• date_type can also be the record type, array type, and collection type defined in the stored procedure (anonymous blocks are not supported).

Example

The table used in the following example is defined as follows:
MogDB=# \d emp_rec
                Table "public.emp_rec"
  Column  |              Type              | Modifiers
----------+--------------------------------+-----------
 empno    | numeric(4,0)                   | not null
 ename    | character varying(10)          |
 job      | character varying(9)           |
 mgr      | numeric(4,0)                   |
 hiredate | timestamp(0) without time zone |
 sal      | numeric(7,2)                   |
 comm     | numeric(7,2)                   |
 deptno   | numeric(2,0)                   |

-- Perform array operations in the function.
MogDB=# CREATE OR REPLACE FUNCTION regress_record(p_w VARCHAR2)
RETURNS
VARCHAR2  AS $$
DECLARE

   -- Declare a record type.
   type rec_type is record (name  varchar2(100), epno int);
   employer rec_type;

   -- Use %type to declare the record type.
   type rec_type1 is record (name  emp_rec.ename%type, epno int not null :=10);
   employer1 rec_type1;

   -- Declare a record type with a default value.
   type rec_type2 is record (
         name varchar2 not null := 'SCOTT',
         epno int not null :=10);
    employer2 rec_type2;
    CURSOR C1 IS  select ename,empno from emp_rec order by 1 limit 1;

BEGIN
      -- Assign a value to a member record variable.
     employer.name := 'WARD';
     employer.epno = 18;
     raise info 'employer name: % , epno:%', employer.name, employer.epno;

      -- Assign the value of a record variable to another variable.
     employer1 := employer;
     raise info 'employer1 name: % , epno: %',employer1.name, employer1.epno;

      -- Assign the NULL value to a record variable.
     employer1 := NULL;
     raise info 'employer1 name: % , epno: %',employer1.name, employer1.epno;

      -- Obtain the default value of a record variable.
     raise info 'employer2 name: % ,epno: %', employer2.name, employer2.epno;

      -- Use a record variable in the FOR loop.
      for employer in select ename,empno from emp_rec order by 1  limit 1
          loop
               raise info 'employer name: % , epno: %', employer.name, employer.epno;
          end loop;

      -- Use a record variable in the SELECT INTO statement.
      select ename,empno  into employer2 from emp_rec order by 1 limit 1;
      raise info 'employer name: % , epno: %', employer2.name, employer2.epno;

      -- Use a record variable in a cursor.
      OPEN C1;
      FETCH C1 INTO employer2;
      raise info 'employer name: % , epno: %', employer2.name, employer2.epno;
      CLOSE C1;
      RETURN employer.name;
END;
$$
LANGUAGE plpgsql;

-- Invoke the function.
MogDB=# CALL regress_record('abc');

-- Delete the function.
MogDB=# DROP FUNCTION regress_record;