PL/SQL Composite Type

1. PL/SQL records

Introduction: similar to the structure in advanced languages, you can use custom record types, record variables, and % rowtype to define record variables for processing a single row of data.
Benefit: You can regard this record set as a class and then define multiple objects of this class. The fields in this class are the fields required for user filtering. then you can directly access these fields on the object. syntax: tyep type_record_name is record (-- create record type -- Data Type) type_record type_record_name -- specify the record variable name.
For example:

-- Created on 2012/10/5 Friday by mm declare type emp_record_type is record (v_no EMP. empno % type, v_name EMP. ename % type, v_sal EMP. sal % type); emp_record emp_record_type; begin select empno, ename, Sal into emp_record from EMP where empno = 7369; values (values); values (emp_record.v_name); values (emp_record.v_sal ); end;

In addition to queries, after 9i, you can insert, update, or dellete to reference record variables.

2. PL/SQL setIntroduction: A set is similar to an array in advanced languages. It processes data in a single column and multiple rows. It contains index tables (PL/SQL tables), nested tables (nested tables), and variable-length arrays (varry; -- index table description: Index processing PL/SQL array type. The number of elements is not limited, and the subscript can be negative. It can only be used as a PL/SQL compound type, but cannot be used as the Data Type of a table column. Syntax: Type type_table_name is table of element_type [not null] index by key_type; type_table type_table_name; -- element_type the Data Type stored in the index table -- not null indicates that empty elements are not allowed -- key_type (binary_integer, pls_integer, varchar2); for example: -- nested table Introduction: Processing PL/SQL array types, there are no restrictions on element subscripts from the beginning. array elements can be sparse. Note: The constructor must be initialized and can be used as the Data Type of the Data list;
Syntax: Type type_table_name is table of element_type; type_table type_table_name;
For example: -- varray Introduction: Processing PL/SQL array types can be used as the Data Type of the table column. The subscript starts from 1 and the maximum number of elements is a limited Syntax: type type_varry_type is varry (size_limit) of element_type [not null]; type_varray type_varry_type; -- size_limit is the maximum number of specified varry elements; for example:

3. PL/SQL record tableIntroduction:Processing multiple rows and multiple columns of data,

For example: declare type emp_table_type1 is record (-- custom record ename Scott. EMP. ename % type, job Scott. EMP. job % type); emp_table1 partition; Type emp_table_type is table of emp_table_type1 -- Custom Data Type Index by binary_integer; emp_table emp_table_type; counts number; begin select count (*) into counts from EMP; for I in 1 .. counts loop select T. ename, T. job into emp_table1 from (select e. *, rownum rn from EMP e) t where Rn = I; emp_table (I): = emp_table1; end loop;