Oracle collection type

Oracle collection type description set type 1. usage conditions: a. data in a single row, using scalar variables. B. Single Row and multiple columns of data, usage record [For details, see: Click to open the link http://www.bkjia.com/database/201210/163097.html ] C. Single-Column and multi-row data, using a set *: similar to arrays in programming languages. Pl/SQL Collection types include the associated array Associative array (index table pl/SQL Table), Nested table, and variable-length array (VARRAY ). Www.2cto.com 2. there are three types of sets: Nested table and VARRY can be used either in PL/SQL or directly in a database, but Associative array doesn't work, that is, associative array cannot be created separately using the create type statement. It can only be defined and used in PL/SQL blocks (or packages) (that is, it applies to PL/SQL Block levels ), nested table and VARRAY can be created using create type (that is, the scope is Schema level), and they can also be directly used as the TYPE of columns in the database table. 3. set Method: www.2cto.com exists (index) whether the elements in the index exist count the total number of elements in the current set limit the maximum number of element indexes in the set is not limited to the number of nested tables, so return null, the largest index first returned when the variable length array returns the first element index of the set. last returned the last element index of the set. The last extend of the current element before next of the current element expands the capacity of the set, the add element is only used for nested tables and varry Type x. extend adds a null element x. extend (n) adds n null elements x. extend (n, I) adds n elements, the element value is the same as the I element. trim deletes an element from the end of the set. It is only used for NEST TABLE and VARRY trim to delete an element trim (n) from the end of the set) delete n elements from the end of the set delete by index delete set elements delete Delete (index) is used to delete all the elements between a and B. declaration and initialization of the set type: 1) join array:. the subscript is unrestricted. It can be a negative number B. no limit on the number of elements c. define TYPE type_name is table of element_type [not null] index by key_type; type_name: name OF the user-defined data TYPE element_type: index table element TYPE key_type: data Type (BINARY_INTEGER, PLS_INTEGER, VARCHAR2) d. example 1: www.2cto.com [SQL] declare type index_tab_type is table of varchar2 (30) index by BINARY_INTEGER; v_table index _ Tab_type; begin v_table (-1): = 'hello'; -- sets the value of v_table (1): = ',' for an element whose subscript is-1 ','; dbms_output.put_line (v_table (-1) | '-' | v_table (1); dbms_output.put_line ('number of elements: '| v_table.count); v_table (5 ): = 'World'; dbms_output.put_line ('number of elements: '| v_table.count); dbms_output.put_line ('first elemental' | v_table.first ); dbms_output.put_line ('Last element '| v_table.last); end;/[SQL] hello-, number of elements: 2 Number of elements: 3 first element-1 last element 5 www.2cto.com PL/SQL The process has been completed successfully. E. example 2: varchar2 is used as the index element type, which is actually the same as the key-value (key-value pair) of map in java) format storage [SQL] declare type index_tab_type is table of varchar2 (30) index by varchar2 (30); v_table index_tab_type; v_record emp % rowtype; begin -- emp table query 3 Records, store it in name-job format to the index table select * into v_record from emp where emp. empno = 7788; v_table (v_record.ename): = v_record.job; select * into v_record from emp where emp. empno = 7844; v_table (v_record.ename): = V_record.job; select * into v_record from emp where emp. empno = 7900; v_table (v_record.ename): = v_record.job; dbms_output.put_line (v_table.count); -- 3 dbms_output.put_line (v_table (v_record.ename); -- clerend; /[SQL] 3 clerk pl/SQL process completed successfully. 2) Nested Table. the subscript starts from 1, and there is a limit on the number of elements to be eliminated (* Initialization is required before use, and the number of elements can be extended using the extend attribute) B. the data TYPE can be defined as a TABLE, but the premise IS that you need to create a nested table type first, which can implement 1-to-many definitions of type type_name is table of element_type; c. the difference with the index table is to see whether there is an index by statement. The index of the nested table is fixed in the int type. d. example 1: www.2cto.com [SQL] declare type nest_table_type is table of emp. ename % type; v_nest_tab nest_table_type; begin v_nest_tab: = nest_table_type ('x'); -- Initialization is required! Statement type_name (...) select ename into v_nest_tab (1) from emp where empno = 7788; dbms_output.put_line (v_nest_tab (1); end; [SQL] scott pl/SQL process completed successfully. E. example 2: columns of the nested table type in the table column are stored in a separate table, to use www.2cto.com [SQL] create type nest_tab_type is table of varchar2 (30); create table test_nest_tab (id int, vals nest_tab_type -- Use) nested table vals store as nest_tab; -- the vals field is stored in a nested table, indicating that nest_tab -- after the preceding statement is executed, when TEST_NEST_TAB is generated, a join table NEST_TAB is generated to store the data of the joined table -- insert data into test_nest_tab values (1, nest_tab_type ('one', 'two ', 'Three ', 'four'); -- query data declare V_id int; v_tab nest_tab_type; begin select * into v_id, v_tab from test_nest_tab where id = 1; dbms_output.put_line (v_id); for I in 1 .. v_tab.count loop dbms_output.put_line (v_tab (I); end loop; end; [SQL] 1 one two three four PL/SQL process has been completed successfully. 3) Varry variable array. define TYPE type_name is varray (size_limit) OF element_type [not null]; B. this is almost the same as the array in java. The subscript from 1 first specifies the maximum number of elements, which is also the same as varchar2 (size. C. you must also use the constructor method to initialize a table. It can be a column type d. example 1: www.2cto.com [SQL] declare type varr is VARRAY (10) of int; v_varr varr: = varr (); begin -- dbms_output.put_line (varr. count); for I in 1 .. 5 loop v_varr.extend; v_varr (I): = I * I; end loop; for I in 1 .. 5 loop dbms_output.put_line (v_varr (I); end loop; end;/[SQL] 1 4 9 16 25 PL/SQL process completed successfully. Www.2cto.com e. example 2: The variable array is stored in the table as a variable array of the table column type. Unlike the nested table [SQL] create type varr_type is varray (10) of varchar2 (30 ); -- first create the type create table test_varray (id int, name varchar2 (30), params varr_type -- param is a variable array type); -- insert data into test_varray values (1, 'bird ', varr_type ('A',' B ', 'C'); -- query data declare v_varr varr_type; v_name test_varray.name % type; begin select name, params into v_name, v_varr from test_varray Where id = 1; for I in 1 .. v_varr.count loop dbms_output.put_line (v_varr (I); end loop; end; [SQL] a B c PL/SQL process has been completed successfully. Www.2cto.com 5. how to select the applicable collection type: Generally, the first choice for the collection type should be Associative array, because it does not require initialization or EXTEND operations and is the most efficient collection type so far. The only drawback is that it can only be used for PL/SQL but not for databases. If you want to allow negative index, you should select Associative array; if you want to use the set operations in 10g, 11g, you should select Nested table; if you want to limit the number of set elements, select VARRAY.