Two-dimensional Oracle collection operations

Oracle collection two-dimensional operations Oracle collection is an object used in pl SQL development. It represents a group of ordered elements. The types of elements in a group must be consistent. It is divided into three categories: Associative arrays (also called index-by tables), Nested tables, and Varrays. The first Associative arrays in www.2cto.com can be regarded as a data dictionary with two columns: key and value. The key value can be any number or string, and the value can be any object or even collection object. The second Nested tables, which can look at the one-dimensional array. You can operate on each array element in sequence using number numbers. The third Varrays looks at the pre-defined one-dimensional array, and uses number numbers to operate on each array element in sequence. The three types of definitions and initialization methods are as follows: type typ_dict is table of varchar2 (100) index by varchar2 (10); type typ_array is table of integer; type typ_varray is varray (3) of varchar2 (10); v_dict typ_dict; v_array typ_array: = typ_array (); v_varray typ_varray: = typ_varray (null, null, null); these classes remove common data types, the object type is also encountered. This is somewhat similar to the object-oriented programming language, but it is not so powerful. For example, www.2cto.com defines an object consisting of multiple varchar2. Create or replace type obj_food as object (name VARCHAR2 (100), food_group VARCHAR2 (100), color VARCHAR2 (100); create type typ_array_food IS table OF obj_food; l_food typ_array_food: = trim (); Example 1: DECLARE l_food typ_array_food: = typ_array_food (); BEGIN l_food.extend (3); l_food (1): = obj_food ('scrambled Eggs ', 'protein ', 'yellow'); l_food (2): = obj_food ('deluxe salad', 'vegetable S ', 'Mostly green'); l_food (3): = obj_food ('tofu and Rice', 'protein ', 'white'); dbms_output.put_line ('Food (1) = '| l_food (1 ). name); for j in 1 .. rochelle food.count loop dbms_output.put_line ('Food ('| j |') = '| l_food (j ). name); end loop; END; Result: SQL> set serveroutput onSQL>/food (1) = Scrambled Eggsfood (1) = Scrambled Eggsfood (2) = Deluxe Saladfood (3) = Tofu and Rice PL/SQL procedure successfully completed This is an example of a one-dimensional array. How can we define a two-dimensional array? The premise IS to first define a two-dimensional array TYPE in the database create type typ_array_foods IS table OF typ_array_food; then develop the example program, as follows: DECLARE l_food typ_array_food: = typ_array_food: = typ_array_foods (); BEGIN l_food.extend (3); l_food (1): = obj_food ('scrambled Eggs ', 'protein', 'yellow'); l_food (2 ): = obj_food ('deluxe salad', 'Vegetables ', 'Mostly green'); l_food (3): = obj_food ('tofu and Rice', 'protein ', 'white'); l _ Foods. extend (1); l_foods (1): = l_food; dbms_output.put_line ('Foods (1, 1) = '| l_foods (1) (1 ). name); l_foods.extend (1); l_foods (2): = l_food; l_food.trim (l_food.count); for I in 1 .. 2 loop l_food.extend (1); l_food (I): = obj_food ('scrambled Eggs '| I, 'protein', 'yellow'); end loop; l_foods.extend (1 ); rochelle foods (3): = l_food; for I in 1 .. rochelle foods.count loop for j in 1 .. rochelle foods (I ). count loop dbms_outp Ut. put_line ('Foods ('| I |', '| j |') = '| l_foods (I) (j ). name); end loop; END; we can easily understand it through the trim and extend operations of collection. In actual development, the Nested tables type is used in most cases.