An array of Oracle

I have not encountered an array of this type before, and recently encountered the need to solve the problem of arrays, find a bit about the use of arrays, now sorted down.

Arrays in Oracle are divided into fixed arrays and mutable arrays.

One, fixed array

Fixed array: The size of the array is pre-defined at the time of definition, and if this size is exceeded when initializing an array, it will prompt ORA-06532: exceeding the limit of the small mark!

Grammar:

type arry_var is varray(2) of VARCHAR2(10);--defines an array that holds 2 char types

Cases:

DECLARE
--Declare fixed array
TYPE arry_var is varray(2) of VARCHAR2(10);
Arry_name Arry_var;
BEGIN
--init array
Arry_name: = Arry_var (' Tom ', ' Jim ', ' Tim '); ---note: There are three values initialized here, which will cause an error
Dbms_output.put_line (Arry_name (1));
Dbms_output.put_line (Arry_name (2));
END;

Second, variable array

variable Arrays: You can have any number of elements in an array, the type of the element is predefined, and the subscript of the array can be set to self-increment.

1. One-dimensional arrays

1). The subscript is self-growing, the table represents a variable length, and the index by Bybinary_integer clause represents the index by a signed integer

Grammar:

TYPE t_table is table of VARCHAR2(in) INDEX by Binary_integer;

Cases:

DECLARE
TYPET_table isTABLE ofVARCHAR2(30)INDEX byBinary_integer;
V_table t_table;
V_cnt Number;
BEGIN
V_table (1): = ' 1 ';
V_table (2): = ' 3 ';
V_table (3): = ' 9 ';

V_CNT: = v_table.COUNT;
forIinch1.. V_cntLOOP
Dbms_output.put_line (V_table (i));
END LOOP;
END;

The output is: 1 3 9, where 1 3 9 is stored in the array as a character type

Note: Array subscripts in Oracle start at 1 and subscript must be contiguous.

2. Multidimensional arrays

Multidimensional arrays: can be considered as nested tables, as tables in tables, can have any number of elements, no need to pre-defined limit values.

1). Create Table

Create Table Xxuser
(
user_id number,
User_name VARCHAR2(255),
SEX VARCHAR2(2),
Age Number (3),
ADDRESS VARCHAR2(2000)
)

2). Define a result set (Record) that holds some of the fields in the Xxuser

DECLARE

--Only 2 fileds
TYPE T_record_user is record (
USER_ID Xxuser.user_id%type,
User_name Xxuser.user_name%type);--only two fields in the Xxuser table are used here

TYPE T_user is TABLE of T_record_user INDEX by Binary_integer;

V_arry_user T_user;
BEGIN
SELECT user_id, user_name BULK COLLECT to v_arry_user from Xxuser; --bulk collect indicates that bulk query data is inserted directly into collection instead of through Cursur, and most implementations in the current program are inserted through cursors.
For I in 1.. V_arry_user. COUNT LOOP
Dbms_output.put_line (V_arry_user (i). User_name); --How to call multidimensional arrays
END LOOP;
END;

3). Use ROWTYPE to store all the fields of the Xxuser, which is more concise than the record.

DECLARE

--All,xxuser (user_id, user_name, sex, age, address)
TYPE T_user is TABLE of Xxuser%rowtype INDEX by Binary_integer;

V_arry_user T_user;
BEGIN
SELECT * BULK COLLECT into v_arry_user from Xxuser;
For I in 1.. V_arry_user. COUNT LOOP
Dbms_output.put_line (V_arry_user (i). User_name | | v_arry_user (i). Sex);
END LOOP;
END;

An array of Oracle