Oracle Learning Path (ii)------use of array type/record type

Introduction to Oracle Record types

Record: User-defined data type, composed of single-row multi-column scalar composite data type, it is one or more scalar encapsulated into an object for operation records can not be compared to the overall comparison and can not be judged as a whole, can be assigned as a whole, composed like a multidimensional array,
Encapsulates one or more scalars into an object for operation. is a temporary composite object type.

Creation and assignment of records

• The definition record type syntax is as follows:

TYPEISRECORD(   v1 data_type1  [NOTNULL]  [:= default_value ],   v2 data_type2  [NOTNULL]  [:= default_value ],   ......   vn data_typen  [NOTNULL]  [:= default_value ] );

• An example of a record type

DeclareTYPET_employee_rec is RECORD --Define the type of record(C_fnumber t_employee.fnumber%TYPE,--use%type to get the data type of this columnC_fname VARCHAR ( -), C_fage int,c_fsalary NUMERIC (Ten,2)); T_EMPLOYEE_REC1 T_employee_rec;--Declaring record type variablesbegin        ---Assigning a value to a record type variable SelectFnumber,fname,fage,fsalary to T_EMPLOYEE_REC1 from T_employee where fnumber=' SALES002‘; Dbms_output.put_line (' Fnumber= ' | | T_employee_rec1.c_fnumber); Dbms_output.put_line (' FName= ' | | T_employee_rec1.c_fname); Dbms_output.put_line (' Fage= ' | | To_char (T_employee_rec1.c_fage)); Dbms_output.put_line (' Fsalary= ' | | To_char (t_employee_rec1.c_fsalary));End;

Note: A variable of record type can only hold a row of records queried from the database, and if multiple rows of records are queried, an error occurs.

Introduction to Oracle Array types

Data is a collection of a group of members with the same data type. Each member has a unique subscript, depending on the position of the member in the array. In PL/SQL, the array data type is Varray.

• Define the Varry data type syntax as follows

TYPEISOF element_type [NOTNULL];

Varray_name is the name of the Varray data type, and size is the next integer that represents the maximum number of members that can be accommodated, and the data type of each member is element_type. The default member can take a null value, otherwise it needs to be restricted with NOT NULL. For the Varray data type, there must be three steps, namely: define, declare, initialize .

• Small examples of array types

--Created on  -/6/ One  byLyh declare--defines a maximum save5A varchar ( -) The Varray data type of the data type memberTYPEReg_varray_type isVarray (5) ofvarchar -);--Declares a variable v_reg_varray reg_varray_type of the Varray data type;begin--Assigning the initial value with the constructor syntax V_reg_varray: =reg_varray_type (' China ',' America ',' UK ',' Japan ',' France '); Dbms_output.put_line (' region name: '|| V_reg_varray (2)||', '|| V_reg_varray (3)||', '|| V_reg_varray (4)); Dbms_output.put_line (' value of the 5th member assigned to null: '|| V_reg_varray (5)); V_reg_varray (5) :=' Russia '; Dbms_output.put_line (' value of the 5th member assigned to null: '|| V_reg_varray (5));End;

Oracle Learning Path (ii)------use of array type/record type