Oracle array and case analysis

Oracle array and case analysis about arrays in ORACLE: three methods can be used to record the same set: 1. Define a TYPE and use VARRAY to obtain an array. However, you can only define the basic TYPE as follows: create type name as varray of VARCHAR2 (20); 1. You can use VARRAY to customize a TYPE to obtain an array, but you can only define the basic TYPE, for example, create type name as varray (52) OF VARCHAR2 (20); the following cannot be used: create type name as varray (52) OF table name % ROWTYPE; note: when using VARRAY, you must first specify the array size or create an array TYPE 2 embedded TABLE, for example, TYPE name is table of specific TYPE, such as: (TABLE name % ROWTYPE ); the number of embedded tables has two types: The Index_by table and the nested table above are nested tables, while the Index_by table only needs to return the INDEX BY BI Example: declarecursor cur_test is select id, mc from test; type t_test1 is table of varchar2 (60) index by binary_integer; type t_test2 is table of test % rowtype index by binary_integer; var_test1 t_test1; var_test2 t_test2; var_new t_test2; beginSELECT id, mc INTO var_test2 (0) FROM test WHERE id = '2016 '; dbms_output.put_line ('var _ test2 (0): '| var_test2 (0 ). id | '---' | var_test2 (0 ). mc); SELECT id, mc INT O var_test2 (8) FROM test WHERE id = '000000'; dbms_output.put_line ('var _ test2 (8): '| var_test2 (8 ). id | '---' | var_test2 (8 ). mc); var_new: = var_test2; dbms_output.put_line ('==== copy var_test2 to var_new ===='); dbms_output.put_line ('var _ new (0 ): '| var_new (0 ). id | '---' | var_new (0 ). mc); dbms_output.put_line ('var _ new (8): '| var_new (8 ). id | '---' | var_new (8 ). mc); end; ========================================================== ========== ========================================== Declare type t_test1 IS TABLE OF test. id % TYPE; TYPE t_test2 is varray (10) OF test. id % TYPE; var_test1 t_test1; var_test2 t_test2; begin -- var_test1 (1): = ('test1. 1 '); -- the value of var_test1: = t_test1 ('test1. 1', 'test1. 2', 'test1. 3 '); dbms_output.put_line ('var _ test1:' | var_test1 (1) | ',' | var_test1 (2) | ', '| var_test1 (3); var_test2: = t_test2 ('test2. 1', 'test2. 2', 'test2. 3 '); Dbms_output.put_line ('var _ test2: '| var_test2 (1) |', '| var_test2 (2) |', '| var_test2 (3 )); var_test1 (2): = 'test1. 2_updat'; dbms_output.put_line ('=== modified var_test1 (2) ===='); dbms_output.put_line ('var _ test1: '| var_test1 (1) | ',' | var_test1 (2) | ',' | var_test1 (3); dbms_output.put_line (var_test1.next (3 )); dbms_output.put_line ('var _ test2 element count: '| var_test2.limit (); end; the element of the nested table can be a set. Note that when assigning values, varray_element.rec Ord_column: = form. In addition to constructor functions, there are many built-in functions in the Set, which are called methods according to the object-oriented method. Method = ========================================================== use Limit COUNT ========== number of elements in the returned set DELETE ======= DELETE all elements in the set DELETE (x) ===== delete an element whose subscript is x ============================ ===========================================invalid for varray delete (x, y) === Delete the element whose subscript ranges from X to Y ========================== ================================ illegal EXIST (x) for VARRAY) ====== if the set element x has been initialized, TRUE is returned, otherwise, false extend ========== add an element at the end of the Set ======================== ==== ============================================ Illegal EXTEND (x) for Index_by) ===== add x elements at the end of the Set ================================== ========================================== illegal EXTEND for Index_by (x, n) ==== add x copies of element n to the end of the Set ====================== ======================== the Index_by illegal FIRST ========== returns the next label of the FIRST element in the set, returns 1 for the VARRAY set. LAST ========== returns the bottom label of the LAST element in the set. The return value for VARRAY is always equal to COUNT. LIMIT ========== returns the maximum number of elements in the VARRY set ====================== ================================ Index_by set and nested table useless NEXT (x) ======== returns the value of the element following the x element and next to it. If x is the last element, null is returned. PRIOR (x) ====== returns the value of the element next to it before the x element. If x is the first element, null is returned. TRIM ========== delete an element from the end of the Set ======================= ============================================ TRIM (x) is not legal for index_by) ======== Delete x elements from the end of the Set ====================== ================================== the index_by statement is invalid ************** **************************************** * ************************************ records can be defined: TYPE name is recorder (with break TYPE) IS also available: variable name table name % ROWTYPE example: In the implicit definition record, we do not need to describe every field of the record, when declaring a record variable, use the % ROWTYPE command to define a record with the same structure as the database table, view, and cursor. Some PL/SQL commands do not use the % ROWTYPE attribute when using the implicit definition record, such as the: old and: new Record declaret_record1 test % rowtype In the cursor FOR loop or trigger; cursor cur_test (v_id in varchar2) isselect id, mc from testwhere id <= v_id; t_record2 cur_test % rowtype; beginfor row_test in cur_test ('20140901') Partition: = row_test.mc; t_record2.id: = row_test.id; t_record2.mc: = row_test.id; substring ('t_record1: '| t_record1.id |' --- '| t_record1.mc ); dbms_output.put_line ('t_record2: '| t_record2.id |' --- '| t_record2.mc); dbms_output.put_line ('row _ test: '| row_test.id |' --- '| row_test.mc ); dbms_output.put_line ('==================== loop' | cur_test % rowcount | 'times. '); end loop; exception when others thendbms_output.put_line (sqlcode | sqlerrm); end; ========================================================== ========================================================== ===== declaretype t_record is record (id test. id % type, mc test. mc % type); var_record t_record; counter number default 0; beginfor row_test in (select id, mc from test) loopcounter: = counter + 1; var_record.id: = row_test.id; var_record.mc: = row_test.mc; dbms_output.put_line ('var _ record: '| var_record.id |' --- '| var_record.mc); dbms_output.put_line ('row _ test: '| row_test.id |' --- '| row_test.mc ); dbms_output.put_line ('==================== loop' | counter | 'times. '); end loop; exception when others thendbms_output.put_line (sqlcode | sqlerrm); end; iii. Usage of bulk collect */set serverout onDECLARETYPE t_record is record (id number (), mc varchar2 (50); var_record t_record; type t_test is table of t_record; var_test t_test: = t_test (); cursor cur_test is select id, mc from test; beginopen cur_test; fetch cur_test bulk collect into var_test; for I in 1 .. var_test.count () loopdbms_output.put_line (var_test (I ). id | '---' | var_test (I ). mc); end loop; end;