Introduction to oracle array instances

This article introduces in detail various oracle Array Operations. If you need it, you can refer to it.

Oracle Arrays can be divided into fixed arrays and variable arrays.
Fixed Array
 
 

The Code is as follows:

Copy code

Declare Type v_ar is varray (10) of varchar2 (30 ); My_ar v_ar: = v_ar ('G', 'M', 'D', 'handler', 'handler '); Begin For I in 1 .. my_ar.count Loop Dbms_output.put_line (my_ar (I )); End loop; End; Declare Type v_ar is varray (10) of varchar2 (30 ); My_ar v_ar: = v_ar ('G', 'M', 'D', 'handler', 'handler '); Begin For I in 1 .. my_ar.count Loop Dbms_output.put_line (my_ar (I )); End loop; End;   -- Variable array -- One-dimensional array Declare Type v_table is table of varchar2 (30) index by binary_integer; -- The type can be the previous type definition. The index by binary_integer clause indicates that the index is composed of symbol integers, -- In this way, the data method used to access table-type variables is "Table variable name (index symbol integer )". My_table v_table; Begin For I in 1 .. 20 Loop My_table (I): = I; Dbms_output.put_line (my_table (I )); End loop; End; Declare Type v_table is table of varchar2 (30) index by binary_integer; -- The type can be the previous type definition. The index by binary_integer clause indicates that the index is composed of symbol integers, -- In this way, the data method used to access table-type variables is "Table variable name (index symbol integer )". My_table v_table; Begin For I in 1 .. 20 Loop My_table (I): = I; Dbms_output.put_line (my_table (I )); End loop; End;   -- Multi-dimensional array -- multiple records     Declare Type v_table is table of t_user % rowtype index by binary_integer; My_table v_table; Begin Select * bulk collect into my_table from t_user; For I in 1 .. my_table.count/10 -- my_table.count/10 get the rounding Value Loop Dbms_output.put_line ('suser -- '| my_table (I). suser ); Dbms_output.put_line ('name --- '| my_table (I). name ); Dbms_output.put_line ('sex ---- '| my_table (I). sex ); End loop; End; Declare Type v_table is table of t_user % rowtype index by binary_integer; My_table v_table; Begin Select * bulk collect into my_table from t_user; For I in 1 .. my_table.count/10 -- my_table.count/10 get the rounding Value Loop Dbms_output.put_line ('suser -- '| my_table (I). suser ); Dbms_output.put_line ('name --- '| my_table (I). name ); Dbms_output.put_line ('sex ---- '| my_table (I). sex ); End loop; End;   Multi-dimensional array-single record   Declare Type v_table is table of t_user % rowtype index by binary_integer; My_table v_table; Begin Select * into my_table (9) from t_user where suser = 'admin '; -- My_table (I) I can be any integer, but the values must be consistent with I; Dbms_output.put_line ('-- suser --' | my_table (9). suser | '-- name --' | my_table (9). name ); End; Declare Type v_table is table of t_user % rowtype index by binary_integer; My_table v_table; Begin Select * into my_table (9) from t_user where suser = 'admin '; -- My_table (I) I can be any integer, but the values must be consistent with I; Dbms_output.put_line ('-- suser --' | my_table (9). suser | '-- name --' | my_table (9). name ); End;   -- Custom Array Create or replace type varray_list as varray (30) of varchar2 (50 ); -- Use a custom Array Create or replace procedure show_list (p_varlist in varray_list) Is V_str varchar2 (50 ); Begin For I in 1 .. p_varlist.count Loop V_str: = p_varlist (I ); Dbms_output.put_line ('v _ str = '| v_str ); Dbms_output.put_line ('P _ varlist ('| I |') = '| p_varlist (I )); End loop; End; Declare My_var varray_list: = varray_list ('G', 'M', 'D', 'handler', 'handler '); Begin Show_list (my_var ); End;

Instance

The Code is as follows:

Copy code

-- Fixed Array Declare Type type_array is varray (10) of varchar2 (20 ); Var_array type_array: = type_array ('ggs ', 'glast', 'wsb', 'csl', 'dd', 'bb '); Begin For I in 1 .. var_array.count loop Dbms_output.put_line (var_array (I )); End loop; End; -- Variable array Declare Type type_array is table of varchar2 (20) index by binary_integer; Var_array type_array; Begin Var_array (1): = 'a '; Var_array (2): = 'bb '; For I in 1 .. var_array.count loop Dbms_output.put_line (var_array (I )); End loop; End; -- Variable array to retrieve table Declare Begin End; Create or replace procedure proc_stock (n number) As Var_stock_code varchar2 (10 ); Var_stock_price number; Begin For I in 1. n loop Var_stock_code: = lpad (STR1 => I, LEN => 6, PAD => '0 '); Var_stock_price: = trunc (dbms_random.value * 100) + 1; -- Dbms_output.put_line (var_stock_code ); -- Dbms_output.put_line (var_stock_price ); Insert into t_stock (stockcode, stockprice) Values (var_stock_code, var_stock_price ); Commit; End loop; End; Declare Begin Proc_stock (1000000 ); End; -- Access 14.578 seconds 13.5 13.8 with a cursor Declare Cursor cur is select * from t_stock; Row_stock t_stock % rowtype; Begin Open cur; Loop Fetch cur into row_stock; Exit when cur % notfound; Null; End loop; Close cur; End; -- Implement 4.813 1.953 2 with Arrays Declare Type type_array is table of t_stock % rowtype index by binary_integer; Var_array type_array; Begin Select * bulk collect into var_array from t_stock; For I in 1 .. var_array.count loop Null; End loop; End; -- Access a custom table Declare Type type_record is record ( Username varchar2 (20 ), Sex varchar2 (2) ); Type_record_user type_record; Type type_array is table of type_record_user % type index by binary_integer; Var_array type_array; Begin Select username, sex bulk collect into var_array from tuser; For I in 1 .. var_array.count loop Dbms_output.put_line (var_array (I). username ); Dbms_output.put_line (var_array (I). sex ); End loop; End;

About arrays in ORACLE: record the same set
The set can be implemented in three ways:
1. Use VARRAY to customize a TYPE to obtain an array. However, you can only define the basic TYPE as follows:
Create type name as varray of VARCHAR2 (20 );

1. Use VARRAY to customize a TYPE to obtain an array. However, you can only define the basic TYPE as follows:
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 specify the array size first.
Otherwise, create an array type.

2 embedded tables such:
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

BINARY_INTEGER.
Example:

The Code is as follows:

Copy code

Declare Cursor 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; Begin SELECT id, mc INTO var_test2 (0) FROM test WHERE id = '000000 '; Dbms_output.put_line ('var _ test2 (0): '| var_test2 (0). id |' --- '| var_test2 (0). mc ); SELECT id, mc INTO 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 cannot be assigned if no Initialization is performed. 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_update '; 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 the value assignment is in the form of varray_element.record_column: =.
In addition to constructor functions, there are also many built-in functions in the Set, which are called methods according to the object-oriented method.
Method = ========================================================== limits

System
COUNT ========= returns the number of elements in the Set
DELETE ======== DELETE all elements in the Set
DELETE (x) ===== delete an element whose subscript is x ============================ ======================================

Invalid VARRAY
DELETE (x, y) === Delete the element whose subscript ranges from X to Y ========================== ================================

Invalid VARRAY
EXIST (x) ====== if the set element x has been initialized, TRUE is returned; otherwise, FALSE is returned.
EXTEND ========== add an element at the end of the Set ========================== ======================================

Invalid Index_by
EXTEND (x) ===== add x elements at the end of the Set ================================== ======================================

Invalid Index_by
EXTEND (x, n) ==== add x copies of element n to the end of the Set ====================== ========================

Invalid Index_by
FIRST ========== return the bottom label of the FIRST element in the Set, and 1 is always returned 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

========================================================== === The Index_by set and nested table are useless.
NEXT (x) ====== returns the value of the element NEXT to and NEXT to the x element. 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 ======================= ======================================

Index_by is invalid.
TRIM (x) ======== Delete x elements from the end of the Set ====================== ====================================

Index_by is invalid.
**************************************** **************************************** ************
The record can be defined:
TYPE name is recorder (with break TYPE)
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 the record variable, we use the % ROWTYPE command to define and database tables,

View. The cursor has records of the same structure.
Some PL/SQL commands do not use the % ROWTYPE attribute when using the implicit definition record, such as the cursor FOR loop or the: old in the trigger.

And: new Records

The Code is as follows:

Copy code

Declare T_record1 test % rowtype; Cursor cur_test (v_id in varchar2) is Select id, mc from test Where id <= v_id; T_record2 cur_test % rowtype; Begin For row_test in cur_test ('20140901') loop T_record1.id: = row_test.id; T_record1.mc: = row_test.mc; T_record2.id: = row_test.id; T_record2.mc: = row_test.id; Dbms_output.put_line ('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 then Dbms_output.put_line (sqlcode | sqlerrm ); End; ========================================================== ========================================================== ====== Declare Type t_record is record ( Id test. id % type, Mc test. mc % type ); Var_record t_record; Counter number default 0; Begin For row_test in (select id, mc from test) loop Counter: = 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 then Dbms_output.put_line (sqlcode | sqlerrm ); End;

Iii. Usage of BULK COLLECT

The Code is as follows:

Copy code

*/ Set serverout on DECLARE TYPE t_record is record ( Id number (18,0 ), 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; Begin Open cur_test; Fetch cur_test bulk collect into var_test; For I in 1 .. var_test.count () loop Dbms_output.put_line (var_test (I). id | '---' | var_test (I). mc ); End loop; End;