Oracle learning path (2) ------ use of array type/record type, oracle ------

Oracle learning path (2) ------ use of array type/record type, oracle ------
Oracle record types

RECORD: A composite data type consisting of scalar values in multiple columns of a single row, it encapsulates one or more scalar values into one object for operation record. It cannot be compared in general or be judged as null in whole. It can be assigned as a whole to form a multi-dimensional array,
Encapsulate one or more scalar values into one object for operations. Is a temporary composite object type.

Record creation and assignment

• Syntax for defining record types is as follows:

TYPE record_name IS RECORD(   v1 data_type1  [NOT NULL]  [:= default_value ],   v2 data_type2  [NOT NULL]  [:= default_value ],   ......   vn data_typen  [NOT NULL]  [:= default_value ] );

• Example of a record type

Declare TYPE T_Employee_rec is RECORD -- Define the record type (c_FNumber T_Employee.FNumber % TYPE, -- Use % TYPE to obtain the data TYPE c_FName VARCHAR (20), c_FAge int, c_FSalary NUMERIC (); T_Employee_rec1 T_Employee_rec; -- declare the record type variable begin --- assign select FNumber, FName, FAge to the record type variable, FSalary into T_Employee_rec1 from T_Employee where FNumber = 'sales00'; dbms_output.put_line ('fnumber = '| percent); dbms_output.put_line ('fname =' | percent ); dbms_output.put_line ('fage = '| to_char (T_Employee_rec1.c_FAge); dbms_output.put_line ('fsalary =' | to_char (bytes); end;

Note:A variable of the record type can only store one row of records queried from the database. If multiple rows of records are queried, an error occurs.

Introduction to oracle array types

Data is a set of members of the same data type. Each member has a unique subscript, which depends on the position of the Member in the array. In PL/SQL, the array data type is VARRAY.

• The syntax for defining VARRY data types is as follows:

TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];

Varray_name is the name of the VARRAY data type, and size is the next integer, which indicates the maximum number of members that can be accommodated. The data type of each member is element_type. By default, members can take NULL values. Otherwise, they must be limited by not null. For the VARRAY data type, three steps must be taken:Definition, declaration, and initialization.

• Small Example of array type

-- Created on by LYH declare -- defines a VARRAY data TYPE reg_varray_type is varray (5) of VARCHAR (25) that saves up to five varchar (25) data TYPE members ); -- declare a variable v_reg_varray reg_varray_type for the varray data type; begin -- use the constructor syntax to assign the initial value v_reg_varray: = reg_varray_type ('China', 'u.s., '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 ('values of the 5th members with null values:' | v_reg_varray (5); v_reg_varray (5 ): = 'Russian '; Dbms_Output.put_line (the value of the first Member with a null value:' | v_reg_varray (5); end;