Oracle-defined data Type

Oracle provides the custom type function. You can define the Record and Table based on your own situation. The Record data type is

Oracle provides the custom type function. You can define the Record and Table based on your own situation. The Record data type is

Oracle provides the custom type function. You can define the Record and Table based on your own situation. Record data storage is a Record, A record Table is used to store multiple records. If the Record data type Record is a one-dimensional array, the Record Table Type Table is a two-dimensional array.

The custom TYPE can be written in two ways: TYPE... IS and create type, the difference between the two definition methods IS: the former IS generally defined in the stored procedure and function, the scope of use IS also limited to the process or function, while the latter method declares the object TYPE, the object type is a solution object (like a table, index, view, or trigger). It can be used in a process or function, or when a table is defined, as the field type.

The following sections describe:

Type is defined TYPE

1. Define the data record type

Syntax:

TYPE type_name is record (Field 1 TYPE 1, Field 2 TYPE 2,... field n TYPE n );

Note:

1) type_name: type name.

Example:

Declare
TYPE type_student is record (
Name VARCHAR2 (100 ),
Age NUMBER (3 ),
Sex VARCHAR2 (1)
);

R_student type_student;
V_name VARCHAR2 (100 );
I _age NUMBER (3 );
V_sex VARCHAR (1 );
I integer;
Begin
Select name, age, sex into r_student from t_student where gid = 1;
V_name: = r_student.name;
I _age: = r_student.age;
V_sex: = r_student.sex;
Dbms_output.put_line (v_name );
Dbms_output.put_line (v_sex );
Dbms_output.put_line (I _age );
End;

Output result:

Zhansan

1

12

2. Define table records

Syntax:

TYPE type_name is table of element_typeINDEX BY [BINARY_INTEGER | PLS_INTEGER | VARRAY2];

Note:

1) type_name: type name.

2) element_type: It can be basic type (such as varchar2, Ingeger, number), RECORD data type (that is, type defined by TYPEtype_nameIS RECORD), and % ROWTYPE.

3) index by: This statement increases the Number type subscript, automatically initializes, and allocates space. With this statement, you can insert elements to table records.

You do not need to display initialization or allocate space through extend. Both Binary_Integer and Pls_Integer are integer types.

Binary_Integer type variable value calculation is performed by Oracle without Overflow, but the execution speed is slow because it is performed by Oracle

Simulate execution. The execution of Pls_Integer is performed by the hardware, that is, directly by the CPU. Therefore, overflow occurs, but the execution speed is much faster than that of Pls_Integer.

If this statement is not used and extend is not used, an error is reported: ORA-06531: Reference to uninitialized

Collection, please refer to ORA-06531: Reference to uninitialized collection Problem Solving

Example:

Declare
TYPE type_student is record (
Name VARCHAR2 (100 ),
Age NUMBER (3 ),
Sex VARCHAR2 (1)
);

TYPE t_student_var is table of VARCHAR2 (100) index by BINARY_INTEGER;

TYPE t_student_rec is table of type_student index by BINARY_INTEGER;

TYPE t_student_rowtype is table of t_student % rowtype index by BINARY_INTEGER;

V_tbl_name t_student_var;
V_tbl_record t_student_rec;
V_tbl_rowtype t_student_rowtype;
Begin
Dbms_output.put_line ('------------ basic type VARCHAR2 Table Record ------------');
Select name into v_tbl_name (1) from t_student where gid = 1;
Select name into v_tbl_name (2) from t_student where gid = 2;
Select name into v_tbl_name (3) from t_student where gid = 3;
Dbms_output.put_line (v_tbl_name (1 ));
Dbms_output.put_line (v_tbl_name (2 ));
Dbms_output.put_line (v_tbl_name (3 ));

Dbms_output.put_line ('------------ custom RECORD type Table RECORD ------------');
Select name, age, sex into v_tbl_record (1) from t_student where gid = 1;
Select name, age, sex into v_tbl_record (2) from t_student where gid = 2;
Select name, age, sex into v_tbl_record (3) from t_student where gid = 3;
Dbms_output.put_line ('name: '| v_tbl_record (1 ). name | ',' | 'Age: '| v_tbl_record (1 ). age | ',' | 'sex: '| v_tbl_record (1 ). sex );
Dbms_output.put_line ('name: '| v_tbl_record (2 ). name | ',' | 'Age: '| v_tbl_record (2 ). age | ',' | 'sex: '| v_tbl_record (2 ). sex );
Dbms_output.put_line ('name: '| v_tbl_record (3 ). name | ',' | 'Age: '| v_tbl_record (3 ). age | ',' | 'sex: '| v_tbl_record (3 ). sex );

Dbms_output.put_line ('------------ Table Record type Table Record ------------');
Select * into v_tbl_rowtype (1) from t_student where gid = 1;
Select * into v_tbl_rowtype (2) from t_student where gid = 2;
Select * into v_tbl_rowtype (3) from t_student where gid = 3;
Dbms_output.put_line ('name: '| v_tbl_rowtype (1 ). name | ',' | 'Age: '| v_tbl_rowtype (1 ). age | ',' | 'sex: '| v_tbl_rowtype (1 ). sex | ',' | 'grade: '| v_tbl_rowtype (1 ). grade );
Dbms_output.put_line ('name: '| v_tbl_rowtype (2 ). name | ',' | 'Age: '| v_tbl_rowtype (2 ). age | ',' | 'sex: '| v_tbl_rowtype (2 ). sex | ',' | 'grade: '| v_tbl_rowtype (2 ). grade );
Dbms_output.put_line ('name: '| v_tbl_rowtype (3 ). name | ',' | 'Age: '| v_tbl_rowtype (3 ). age | ',' | 'sex: '| v_tbl_rowtype (3 ). sex | ',' | 'grade: '| v_tbl_rowtype (3 ). grade );

End;

Output result: