Oracle Array type Simple example introduction

Oracle arrays can generally be divided into fixed arrays and variable arrays
Fixed array

The code is as follows

Copy Code

Declare Type V_ar is Varray (a) of VARCHAR2 (30); My_ar V_ar:=v_ar (' G ', ' m ', ' d ', ' gong ', ' handsome '); 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 (a) of VARCHAR2 (30); My_ar V_ar:=v_ar (' G ', ' m ', ' d ', ' gong ', ' handsome '); 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 (a) index by Binary_integer; --The type can be the previous type definition, and the index by Binary_integer clause is indexed with a signed integer, --This way you access the data method in the table type variable is the 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 (a) index by Binary_integer; --The type can be the previous type definition, and the index by Binary_integer clause is indexed with a signed integer, --This way you access the data method in the table type variable is the 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 --multidimensional 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; The value for the I in 1..MY_TABLE.COUNT/10--MY_TABLE.COUNT/10 is rounded 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; The value for the I in 1..MY_TABLE.COUNT/10--MY_TABLE.COUNT/10 is rounded 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 Multidimensional 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 value must be kept in accordance 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 value must be kept in accordance 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 of VARCHAR2 (50); --Using 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 ', ' gong ', ' handsome '); Begin Show_list (My_var); End

Instance

The code is as follows

Copy Code

--Fixed array Declare Type Type_array is Varray (a) of VARCHAR2 (20); Var_array type_array:=type_array (' GGs ', ' jjh ', ' 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 (a) index by Binary_integer; Var_array Type_array; Begin Var_array (1): = ' AA '; Var_array (2): = ' BB '; For I in 1..var_array.count loop Dbms_output.put_line (Var_array (i)); End Loop; End --Variable array fetching 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 --with cursor access 14.578 seconds 13.5 13.8 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 --Using an array to achieve 4.813 1.953 2 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 --Accessing custom tables 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 collection
Collections can be implemented in three ways:
1 Custom a type uses Varray to get an array but can only define basic types such as:
CREATE type type name as Varray of VARCHAR2 (20);

1 Custom a type uses Varray to get an array but can only define basic types such as:
CREATE type type name as Varray of VARCHAR2 (20);

You cannot use the following:
CREATE type type name as Varray table name%rowtype;
Note: You must specify the size of the array first when using Varray
Otherwise, create an array type

2 inline tables such as:
Type name is table of specific type such as: (table name%rowtype);
An inline table array is divided into two types: index_by tables with nested tables are nested tables and index_by tables are only returned on their tails by the Index by

Binary_integer on it.
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 (a) 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= ' 111 '; 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= ' 333 '; 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 (a) of test.id%type; Var_test1 T_test1; Var_test2 T_test2; Begin --var_test1 (1): = (' test1.1 '); --No initialization cannot be assigned 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 number: ' | | Var_test2.limit ()); End

The element of a nested table can be a collection, and note that when assigned, it is the form of varray_element.record_column: =.
In addition to constructors, there are many built-in functions in the set, which are called methods according to object-oriented terminology.
Method ========== describe ==================================================================== usage limits

System
count========= returns the number of elements in a collection
delete======== Delete all elements in the collection
Delete (x) ===== deletes an element that is labeled X by =================================================== the

Varray illegal
Delete (x,y) = = remove element subscript from X to Y ================================================ to

Varray illegal
EXIST (x) = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
extend======== adds an element to the end of the collection ================================================== the

Index_by illegal
EXTEND (x) ===== adds x elements to the end of the collection ===================================================

Index_by illegal
EXTEND (x,n) = = Add an x copy of element n at the end of the collection ============================================

Index_by illegal
first========= returns the lower label of the first element in the collection and always returns 1 for the Varray collection.
last========== returns the bottom label of the last element in the collection, and the return value for Varray is always equal to count.
limit========= returns the maximum number of elements in the Varry collection

===========================================index_by collections and nested tables are useless
NEXT (x) ======= returns the value of the element immediately after the X element and next to it, if X is the last element, returns NULL.
PRIOR (x) = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
trim========== deletes an element starting at the end of the collection ============================================== for

Index_by is illegal.
TRIM (x) ======= deletes x elements starting at the end of the collection ===============================================

Index_by is illegal.
********************************************************************************************
Records can be defined as:
Type name is recorder (with Hugh type)
Also available: Variable name table name%rowtype
Example:

In an implicit definition of a record, we don't have to describe each of the fields of the record and use the%rowtype command to define the database table when declaring the record variable.

View, the cursor has a record of the same structure.
There are some pl/sql directives that do not use the%rowtype property when using implicit definition records, such as cursor for loop or trigger: old

and: New record

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 (' 333 ') 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. the use of bulk collect for comprehensive examples

code is as follows

copy code

*/ Set Serverout on DECLARE TYPE t_record be record ( ID number (18,0), MC varchar2 () ); Var_record T_r Ecord; 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; to I in 1..var_test.count () Loop dbm S_output.put_line (Var_test (i) id| | ' ---'|| Var_test (i). MC); End Loop; End;