Shulou(Shulou.com)06/01 Report--

Custom type

Users can use CREATE TYPE statements to define record types, object types, named array types, collection types, and so on. If procedures or methods are declared in object types, you can use CREATE TYPE BODY to define these procedures and methods.

Create types you can use CREATE TYPE statements to create record types, object types, arrays, and collection types.

Grammatical format

CREATE [OR REPLACE] TYPE [

< 模式名>

.]

< 类型名>

[WITH ENCRYPTION] [

< 调用权限子句>

] AS | IS

< 记录类型定义子句>

| |

< 对象类型定义子句>

| |

< 数组类型定义子句>

| |

< 集合类型定义子句>

[

< 调用权限子句>

]:: = AUTHID DEFINER

| | AUTHID CURRENT_USER |

< 对象类型定义子句>

: = OBJECT [UNDER]

< 模式名>

.]

< 父类型名>

] (

< 对象定义>

, {

< 对象定义>

}) [[NOT] FINAL] [[NOT] INSTANTIABLE]

< 对象定义>

:: =

< 变量列表定义>

| |

< 过程声明>

| |

< 函数声明>

| |

< 构造函数声明>

< 过程声明>

: = [

< 方法继承属性>

] [STATIC | MEMBER] PROCEDURE

< 过程名>

< 参数列表>

< 函数声明>

: = [

< 方法继承属性>

] [MAP] [STATIC | MEMBER] FUNCTION

< 函数名>

< 参数列表>

RETURN

< 返回值数据类型>

[DETERMINISTIC]

[PIPELINED]

< 方法继承属性>

:: =

< 重载属性>

| | |

< 重载属性>

< 重载属性>

:: = [NOT] OVERRDING

:: = FINAL | NOT FINAL | INSTANTIABLE | NOT INSTANTIABLE

< 构造函数声明>

:: = CONSTRUCTOR FUNCTION

< 函数名>

< 参数列表>

RETURN SELF AS RESULT

< 记录类型定义子句>

:: = RECORD (variable list definition)

< 数组类型定义子句>

:: = ARRAY

< 数据类型>

'[' [

< 常量表达式>

] {, [

< 常量表达式>

]}']'

< 集合类型定义子句>

:: =

< 数组集合定义子句>

| |

< 嵌套表定义子句>

| |

< 索引表定义子句>

< 数组集合定义子句>

:: = VARRAY (

< 常量表达式>

) OF

< 数据类型>

< 嵌套表定义子句>

:: = TABLE OF

< 数据类型>

< 索引表定义子句>

:: = TABLE OF

< 数据类型>

[INDEX BY

< 数据类型>

]

instructions

1. The declarations of procedures and functions in object types are forward declarations, and the type definition does not include any implementation code; object types and classes are equivalent in the Dream system.

2. Procedures and functions in object types can be declared as STATIC types, indicating static procedures or functions, or MEMBER, indicating that they are member procedures or functions. Non-STATIC and non-constructor methods are member methods by default. MAP means to map an instance of an object type to a scalar value and can only be used for member functions

3. For object type inheritance, refer to the description of class inheritance in section 12.1.

4. WITH ENCRYPTION option, which specifies whether to encrypt the custom type definition

5. The definition format of record type is similar to that of object type, but there can be no procedure and function declaration in record type.

6. In

< 数组类型定义子句>

You can define a multidimensional array by adding', 'within the [] definition of the array length of the If a constant expression is specified, a static array is defined and its array length is fixed. If no constant expression is specified, a dynamic array is defined and its array length is specified when used. Theoretically, DM supports that the maximum length of each dimension of a static array is 65534, and that of each dimension of a dynamic array is 2147483646, but the maximum length of the array is also limited by the internal space of the system. If the stack / heap space limit is exceeded, the system will report an error.

7. A constant expression in an array collection type defines its maximum capacity, and its array element data type can be either a base type or a custom data type.

8. There is no limit on the number of elements in nested table types and index table types. Element data types can be basic data types or other custom types or objects, records, static arrays, but not dynamic arrays; the second is the subscript type of index table, which only supports INTEGER/INT and VARCHAR, which represent integer subscript and string subscript, respectively. For VARCHAR types, the length cannot exceed 1024.

Required permissions

1. The user using this statement must be DBA or a user with CREATE TYPE database permissions.

two。 You can use the keyword AUTHID DEFINER | AUTHID CURRENT_USER to specify the caller permission of the custom type. If DEFINER, the custom type definer permission is used. If CURRENT_USER, the current user permission is used. The default is the definer permission.

Creating a type body implements the procedures and functions declared in the object type in the type body.

Grammatical format

CREATE [OR REPLACE] TYPE BODY [

< 模式名>

.]

< 类型名>

[WITH ENCRYPTION] AS | IS

< 对象类型体定义子句>

END

< 对象类型体定义子句>

:: =

< 对象类型体定义>

, {

< 对象类型体定义>

}

< 对象类型体定义>

:: =

< 过程实现>

| |

< 函数实现>

| |

< 构造函数实现>

< 过程实现>

: = [

< 方法继承属性>

] [STATIC | MEMBER] PROCEDURE

< 过程名>

< 参数列表>

AS | IS BEGIN

< 实现体>

END [procedure name]

< 函数实现>

: = [

< 方法继承属性>

] [MAP] [STATIC | MEMBER] FUNCTION

< 函数名>

< 参数列表>

RETURN

< 返回值数据类型>

[DETERMINISTIC]

[PIPELINED] AS | IS BEGIN

< 实现体>

END [function name]

< 方法继承属性>

:: =

< 重载属性>

| | |

< 重载属性>

< 重载属性>

:: = [NOT] OVERRDING

:: = FINAL | NOT FINAL | INSTANTIABLE | NOT INSTANTIABLE

< 构造函数实现>

:: = CONSTRUCTOR FUNCTION

< 函数名>

< 参数列表>

RETURN SELF AS RESULT AS | IS BEGIN

< 实现体>

END [function name]

instructions

1. The procedure and function definition in the object type body must be exactly the same as the forward declaration in the type definition. Including the name of the procedure, the parameter name of the parameter definition list, and the data type definition

Required permissions

The user using this statement must be the owner of DBA or an object of that type and have CREATE TYPE database permissions.

Recompile type

Recompile the type, or set the type to disabled if the recompilation fails.

The reprogramming function is mainly used to verify the correctness of the type.

Grammatical format

ALTER TYPE [

< 模式名>

.]

< 类型名>

COMPILE [DEBUG]

Parameters.

1.

< 模式名>

Indicates the pattern to which the recompiled type belongs

two。

< 类型名>

Indicates the name of the type being recompiled

3. [DEBUG] can be ignored.

Required permissions

The user performing this operation must be the creator of the type or have DBA permissions.

Delete Typ

Type deletion can be divided into type deletion and type body deletion. For an object type that has a type body, deleting the type deletes the type body together; if the type body is deleted, the type itself still exists.

Delete type deletes the type using DROP TYPE. For an object type that has a type body, deleting the type deletes the type body together.

Grammatical format

DROP TYPE [

< 模式名>

.]

< 类型名>

[RESTRICT | CASCADE]

instructions

1. If the deleted type does not belong to the current schema, you must indicate the schema name in the statement

2. If an object type that owns a type body is deleted, the corresponding type body is automatically deleted.

Required permissions

The user performing this operation must be the owner of this type or have DBA privileges.

Delete type body

Use DROP TYPE BODY to delete a type body of an object type.

Grammatical format

DROP TYPE BODY [

< 模式名>

.]

< 类型名>

[RESTRICT | CASCADE]; instructions for use

If the deleted type body does not belong to the current schema, you must indicate the schema name in the statement.

Required permissions

The user performing this operation must be the owner of this type or have DBA privileges.

Use of custom types

Use Rul

1. Object types are equivalent to classes. The rules for the use of classes can be found in Chapter 12, "Class types".

two。 The record type, array type and collection type created can be used directly in the DMSQL program statement block without declaring the type in the statement block. For more information on how to use them, please see Section 10.1.1.

3. User-defined data types can be used as element types or member variable types of other user-defined data types

4. Only object types can be used directly as data types for columns in a table; other types can only be used as types of member variables in object types or data types nested within types. However, object types that contain index table types and cursor types cannot be used as data types for columns in the table.

Application example

Create an object type to represent the plural, with a real part and an imaginary part, and realize the operation of adding and subtracting the plural.

SQL > CREATE TYPE COMPLEX AS OBJECT (2 RPART REAL,3 IPART REAL,4 FUNCTION PLUS (X COMPLEX) RETURN COMPLEX,5 FUNCTION LES (X COMPLEX) RETURN COMPLEX6), 7 / executed successfullyused time: 53.553 (ms). Execute id is 128SQL > CREATE TYPE BODY COMPLEX AS2 FUNCTION PLUS (X COMPLEX) RETURN COMPLEX IS3 BEGIN4 RETURN COMPLEX (RPART+X.RPART, IPART+X.IPART); 5 END;6 FUNCTION LES (X COMPLEX) RETURN COMPLEX IS7 BEGIN8 RETURN COMPLEX (RPART-X.RPART, IPART-X.IPART); 9 END;10 END;11 / executed successfullyused time: 14.330 (ms). Execute id is 129.

Create the table c_tab, and the column type of the second column in the table is complex object type.

SQL > CREATE TABLE C_TAB (C1 INT, C2 COMPLEX); executed successfullyused time: 16.381 (ms). Execute id is 130.SQL > INSERT INTO C_TAB VALUES (1, COMPLEX (2d3)), affect rows 1used time: 1.508 (ms). Execute id is 131.

Insert data into table c_tab

SQL > INSERT INTO C_TAB VALUES (2, COMPLEX (4Magne2). Plus (COMPLEX (2Magne3)); affect rows 1used time: 0.969 (ms). Execute id is 132.SQL > commit;executed successfullyused time: 10.709 (ms). Execute id is 133.SQL > select * from caterpillar LINEID C1 C2-11 SYSDBA.COMPLEX (2mem3) 22 SYSDBA.COMPLEX (6mae5) used time: 1.047 (ms). Execute id is 134.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.