v5.0

Arrays, Sets, and Record

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:

syntax-of-the-record-type

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.

Functions Supported by Sets

Set Operators

Parameter type: nest-table

Return value: true or false, Boolean type

Description: Checks whether two sets are of the same type.

Example:

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2);
MogDB-#     b nest := nest(1,2);
MogDB-#     flag bool; 
MogDB-# begin
MogDB$#     flag := a = b;
MogDB$#     raise info '%', flag;
MogDB$# end;
MogDB$# /
INFO:  t
ANONYMOUS BLOCK EXECUTE

Parameter type: nest-table

Return value: true or false, Boolean type

Description: Checks whether the types of two sets are different.

Example:

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2);
MogDB-#     b nest := nest(1,2);
MogDB-#     flag bool; 
MogDB-# begin
MogDB$#     flag := a <> b;
MogDB$#     raise info '%', flag;
MogDB$# end;
MogDB$# /
INFO:  f
ANONYMOUS BLOCK EXECUTE

MULTISET

MULTISET UNION [ALL | DISTINCT]

Parameter type: nest-table

Return type: nest-table

Description: Union of two set variables. ALL indicates that duplicate elements are not removed, and DISTINCT indicates that duplicate elements are removed.

Example:

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2);
MogDB-#     b nest := nest(2,3);
MogDB-# begin
MogDB$#     a := a MULTISET UNION ALL b;
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {1,2,2,3}
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2);
MogDB-#     b nest := nest(2,3);
MogDB-# begin
MogDB$#     a := a MULTISET UNION DISTINCT b;
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {1,2,3}
ANONYMOUS BLOCK EXECUTE

MULTISET EXCEPT [ALL | DISTINCT]

Parameter type: nest-table

Return type: nest-table

Description: Difference of two set variables. Taking A MULTISET EXCEPT B as an example, ALL indicates that elements that are the same as those in B are removed from A. DISTINCT indicates that duplicate elements are removed from A first and then elements that are the same as those in B are removed from A.

Example:

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2,2);
MogDB-#     b nest := nest(2,3);
MogDB-# begin
MogDB$#     a := a MULTISET EXCEPT ALL b;
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {1,2}
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2,2);
MogDB-#     b nest := nest(2,3);
MogDB-# begin
MogDB$#     a := a MULTISET EXCEPT DISTINCT b;
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {1}
ANONYMOUS BLOCK EXECUTE

MULTISET INTERSECT [ALL | DISTINCT]

Parameter type: nest-table

Return type: nest-table

Description: Intersection of two set variables. Taking A MULTISET INTERSECT B as an example, ALL indicates that all duplicate elements in A and B are obtained, and DISTINCT indicates that duplicate elements in A and B are obtained and then duplicate elements in this intersection are removed.

Example:

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2,2);
MogDB-#     b nest := nest(2,2,3);
MogDB-# begin
MogDB$#     a := a MULTISET INTERSECT ALL b;
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {2,2}
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2,2);
MogDB-#     b nest := nest(2,2,3);
MogDB-# begin
MogDB$#     a := a MULTISET INTERSECT DISTINCT b;
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {2}
ANONYMOUS BLOCK EXECUTE

Set Types

exists(idx)

Parameter: idx is of the int4 or varchar type.

Return value: true or false, Boolean type

Description: Checks whether a valid element exists in a specified position.

Example:

MogDB=# declare
MogDB-#     type nest is table of varchar2;
MogDB-#     a nest := nest('happy','?');
MogDB-#     flag bool;
MogDB-# begin
MogDB$#     flag := a.exists(1);
MogDB$#     raise info '%', flag;
MogDB$#     flag := a.exists(10);
MogDB$#     raise info '%', flag;
MogDB$# end;
MogDB$# /
INFO:  t
INFO:  f
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-#     type nest is table of varchar2 index by varchar2;
MogDB-#     a nest;
MogDB-#     flag bool;
MogDB-# begin
MogDB$#     a('1') := 'Be';
MogDB$#     a('2') := 'happy';
MogDB$#     a('3') := '.';
MogDB$#     flag := a.exists('1');
MogDB$#     raise info '%', flag;
MogDB$#     flag := a.exists('ddd');
MogDB$#     raise info '%', flag;
MogDB$# end;
MogDB$# /
INFO:  t
INFO:  f
ANONYMOUS BLOCK EXECUTE

extend[(count[, idx])]

Parameters: idx and count are of the int4 type.

Return type: No value is returned.

Description: Only the nest-table type is supported. One or count elements are extended at the end of the nest-table variable. If index set element idx exists, count index elements are copied to the end of the variable.

Restriction: extend() is not supported in nesting scenarios.

Example:

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1);
MogDB-# begin
MogDB$#     raise info '%', a;
MogDB$#     a.extend;
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {1}
INFO:  {1,NULL}
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1);
MogDB-# begin
MogDB$#     raise info '%', a;
MogDB$#     a.extend(2);
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {1}
INFO:  {1,NULL,NULL}
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1);
MogDB-# begin
MogDB$#     raise info '%', a;
MogDB$#     a.extend(2,1);
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {1}
INFO:  {1,1,1}
ANONYMOUS BLOCK EXECUTE

delete[(idx1[, idx2])]

Parameters: idx1 and idx2 are of the int4 or varchar2 type.

Return type: No value is returned.

Description: Deletes all elements and releases corresponding storage space in a nest-table set (to use this set, extend must be executed again), or deletes all elements (including index set elements) in an index-by table set but does not release corresponding storage space.

Restriction: delete() is not supported in nesting scenarios.

Example:

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2,3,4,5);
MogDB-# begin
MogDB$#     raise info '%', a;
MogDB$#     a.delete;
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {1,2,3,4,5}
INFO:  {}
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2,3,4,5);
MogDB-# begin
MogDB$#     raise info '%', a;
MogDB$#     a.delete(3);
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {1,2,3,4,5}
INFO:  {1,2,4,5}
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-#     type nest is table of int;
MogDB-#     a nest := nest(1,2,3,4,5);
MogDB-# begin
MogDB$#     raise info '%', a;
MogDB$#     a.delete(2,4);
MogDB$#     raise info '%', a;
MogDB$# end;
MogDB$# /
INFO:  {1,2,3,4,5}
INFO:  {1,5}
ANONYMOUS BLOCK EXECUTE

trim[(n)]

Parameter: n is of the int4 type.

Return type: No value is returned.

Description: Deletes one or n elements and corresponding storage space from a nest-table set. Only the nest-table set type is supported.

Restriction: trim() is not supported in nesting scenarios.

Example:

MogDB=# declare
MogDB-# type nest is table of int;
MogDB-# aa nest:=nest(11,22,33,44,55);
MogDB-# begin
MogDB$# raise info 'aa:%' ,aa;
MogDB$# aa.trim;
MogDB$# raise info 'aa:%' ,aa;
MogDB$# aa.trim(2);
MogDB$# raise info 'aa:%' ,aa;
MogDB$# end;
MogDB$# /
INFO:  aa:{11,22,33,44,55}
INFO:  aa:{11,22,33,44}
INFO:  aa:{11,22}
ANONYMOUS BLOCK EXECUTE

count

Parameter: none

Return type: int

Description: Returns the number of valid elements in a set.

Example:

MogDB=# declare
MogDB-# type nest is table of int;
MogDB-# aa nest:=nest(11,22,33,44,55);
MogDB-# begin
MogDB$# raise info 'count:%' ,aa.count;
MogDB$# end;
MogDB$# /
INFO:  count:5
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-# type nest is table of int index by varchar;
MogDB-# aa nest;
MogDB-# begin
MogDB$# aa('aaa') := 111;
MogDB$# aa('bbb') := 222;
MogDB$# aa('ccc') := 333;
MogDB$# raise info 'count:%' ,aa.count;
MogDB$# end;
MogDB$# /
INFO:  count:3
ANONYMOUS BLOCK EXECUTE

first

Parameter: none

Return type: int or varchar

Description: Returns the index of the first valid element in a set.

Example:

MogDB=# declare
MogDB-# type nest is table of int;
MogDB-# aa nest:=nest(11,22,33,44,55);
MogDB-# begin
MogDB$# raise info 'first:%' ,aa.first;
MogDB$# end;
MogDB$# /
INFO:  first:1
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-# type nest is table of int index by varchar;
MogDB-# aa nest;
MogDB-# begin
MogDB$# aa('aaa') := 111;
MogDB$# aa('bbb') := 222;
MogDB$# aa('ccc') := 333;
MogDB$# raise info 'first:%' ,aa.first;
MogDB$# end;
MogDB$# /
INFO:  first:aaa
ANONYMOUS BLOCK EXECUTE

last

Parameter: none

Return type: int or varchar

Description: Returns the index of the last valid element in a set.

Example:

MogDB=# declare
MogDB-# type nest is table of int;
MogDB-# aa nest:=nest(11,22,33,44,55);
MogDB-# begin
MogDB$# raise info 'last:%' ,aa.last;
MogDB$# end;
MogDB$# /
INFO:  last:5
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-# type nest is table of int index by varchar;
MogDB-# aa nest;
MogDB-# begin
MogDB$# aa('aaa') := 111;
MogDB$# aa('bbb') := 222;
MogDB$# aa('ccc') := 333;
MogDB$# raise info 'last:%' ,aa.last;
MogDB$# end;
MogDB$# /
INFO:  last:ccc
ANONYMOUS BLOCK EXECUTE

prior(idx)

Parameter: idx is of the int or varchar type.

Return type: int or varchar

Description: Returns the index of a valid element before the current index in a set.

Example:

MogDB=# declare
MogDB-# type nest is table of int;
MogDB-# aa nest:=nest(11,22,33,44,55);
MogDB-# begin
MogDB$# raise info 'prior:%' ,aa.prior(3);
MogDB$# end;
MogDB$# /
INFO:  prior:2
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-# type nest is table of int index by varchar;
MogDB-# aa nest;
MogDB-# begin
MogDB$# aa('aaa') := 111;
MogDB$# aa('bbb') := 222;
MogDB$# aa('ccc') := 333;
MogDB$# raise info 'prior:%' ,aa.prior('bbb');
MogDB$# end;
MogDB$# /
INFO:  prior:aaa
ANONYMOUS BLOCK EXECUTE

next(idx)

Parameter: idx is of the int or varchar type.

Return type: int or varchar

Description: Returns the index of a valid element following the current index in a set.

Example:

MogDB=# declare
MogDB-# type nest is table of int;
MogDB-# aa nest:=nest(11,22,33,44,55);
MogDB-# begin
MogDB$# raise info 'next:%' ,aa.next(3);
MogDB$# end;
MogDB$# /
INFO:  next:4
ANONYMOUS BLOCK EXECUTE

MogDB=# declare
MogDB-# type nest is table of int index by varchar;
MogDB-# aa nest;
MogDB-# begin
MogDB$# aa('aaa') := 111;
MogDB$# aa('bbb') := 222;
MogDB$# aa('ccc') := 333;
MogDB$# raise info 'next:%' ,aa.next('bbb');
MogDB$# end;
MogDB$# /
INFO:  next:ccc
ANONYMOUS BLOCK EXECUTE

limit

Parameter: none

Return value: null

Description: Returns the maximum number of elements that can be stored in a nest-table set. This function applies only to the array type. The return value is null.

Example:

MogDB=# declare
MogDB-# type nest is table of int;
MogDB-# aa nest:=nest(11,22,33,44,55);
MogDB-# begin
MogDB$# raise info 'limit:%' ,aa.limit;
MogDB$# end;
MogDB$# /
INFO:  limit:<NULL>
ANONYMOUS BLOCK EXECUTE

unnest_table(anynesttable)

Description: Returns a set of elements in a nest-table.

Return type: setof anyelement

Restriction: The tableof type cannot be nested with the tableof type, or the tableof type cannot be nested with other types and then the tableof type.

Example:

create or replace procedure f1()
as
    type t1 is table of int;
    v2 t1 := t1(null, 2, 3, 4, null);
    tmp int;
    cursor c1 is select * from unnest_table(v2);
begin
open c1;
for i in 1 .. v2.count loop
    fetch c1 into tmp;
    if tmp is null then
        dbe_output.print_line(i || ': is null');
    else
        dbe_output.print_line(i || ': ' || tmp);
    end if;
end loop;
close c1;
end;
/

MogDB=# call f1();
1: is null
2: 2
3: 3
4: 4
5: is null
 f1 
----

(1 row)

unnest_table(anyindexbytable)

Description: Returns the set of elements in an index-by table sorted by index.

Return type: setof anyelement

Restriction: The tableof type cannot be nested with the tableof type, or the tableof type cannot be nested with other types and then the tableof type. Only the index by int type is supported. The index by varchar type is not supported.

Example:

create or replace procedure f1()
as
    type t1 is table of int index by int;
    v2 t1 := t1(1=>1, -10=>(-10), 6=>6, 4=>null);
    tmp int;
    cursor c1 is select * from unnest_table(v2);
begin
open c1;
for i in 1 .. v2.count loop
    fetch c1 into tmp;
    if tmp is null then
        dbe_output.print_line(i || ': is null');
    else
        dbe_output.print_line(i || ': ' || tmp);
    end if;
end loop;
close c1;
end;
/

MogDB=# call f1();
1: -10
2: 1
3: is null
4: 6
 f1 
----

(1 row)

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

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;

Issue

Arrays, Sets, and Record

Arrays

Use of Array Types

Sets

Use of Set Types

Functions Supported by Sets

Set Operators

MULTISET

Set Types

Set-related Functions

record