Oracle Array Implementation __oracle

-One-dimensional array:
--Nested table
-There is no limit to size.
--essentially disorderly.
--varray
--Dimensions must be fixed, all instances of the same size.
--In a procedural language, it can be retrieved as an ordered array but viewed as a single, indivisible unit within Oracle.
-High storage efficiency.

--Multidimensional array
--Using a record and record of

--Set up a test table
drop table t_test_1;
CREATE TABLE T_test_1 (
PID Number (10),
PName varchar2 (20),
Birth date,
Score Number (3),
Note VARCHAR2 (50)
);

--Initialize, insert data
--Using the nested table to implement a one-dimensional array
-Do not need to set the upper and lower limits, the following table can be discontinuous, you need to initialize
--
Declare
Type type_test_pid is table of T_test_1.pid%type
Index by Binary_integer;
Type Type_test_pname is table of T_test_1.pname%type
Index by Binary_integer;
Type Type_test_birth is table of T_test_1.birth%type
Index by Binary_integer;
Type Type_test_score is table of T_test_1.score%type
Index by Binary_integer;
Type Type_test_note is table of T_test_1.note%type
Index by Binary_integer;

My_test_pid Type_test_pid;
My_test_panme Type_test_pname;
My_test_birth Type_test_birth;
My_test_score Type_test_score;
My_test_note Type_test_note;

I number (10): = 0;

Begin
  for I in 1.10000
  Loop
    my_test_pid (i): = i;
    ; My_test_panme (i): = ' Names_ ' | | To_char (i);
    My_test_birth (i):=  sysdate;
    My_test_score (i): = To_number (NVL (substr (To_char (i),-2), To_char (i)));
    My_test_note (i): = My_test_panme (i) | | ' Score is ' | |
                     NVL (substr (To_char (i),-2), To_char (i));                   &NBSP
  End loop;

ForAll i in 1. 10000
INSERT INTO t_test_1
Values
My_test_pid (i),
My_test_panme (i),
My_test_birth (i),
My_test_score (i),
My_test_note (i)
);

Commit
exception
When others then
Rollback
End

--using Varray to implement arrays
--Customizing a type uses Varray to get an array
--but only for basic type definitions
-the array to be implemented in this way requires an upper bound, and the subscript is continuous and must be initialized before use
--Suitable for situations where the array length is not too large

Declare
Type Num_pid is Varray (1000) of T_test_1.pid%type;
V_num_pid Num_pid;
I number (8): = 1;
Begin
V_num_pid: = Num_pid ();
V_num_pid.extend;
V_num_pid (1): = 1;
V_num_pid. EXTEND (999,1);--add 999 copies of the 1th element at a one-time
For I in 1. 1000
Loop
--v_num_pid.extend; --Each time you extend a
V_num_pid (i): = i;
Execute immediate ' update t_test_1 set score = trunc (SCORE/2)
where Pid=:1 '
Using V_num_pid (i);
End Loop;
End

-Use the record and table to implement multidimensional arrays

Oracle series: Record and Pl/sql table (table)

One, what is recorded record and Pl/sql table.

Records record: A temporary Record object type consisting of a scalar type of multiple columns of a single row. Similar to multidimensional arrays.

Pl/sql table: A temporary Index Table object type that consists of multiple rows of indexed columns and available columns. Similar to one-dimensional array and key-value pairs.

are user-defined data types.

Second, record + Pl/sql table use is what.

The Record + Pl/sql table can be used for multiple rows and columns of data storage. This allows us to use the Record + Pl/sql table to encapsulate a temporary table object as needed for delivery and manipulation.

Encapsulates a record by customizing the table structure. The Pl/sql table declares that the available column types are record types (the available columns point to the record type variable) and each index corresponds to one record type variable.

Multi-row and multiple-column storage using the Record + Pl/sql table for data

① declares the record type and the Pl/sql table,

Where the index of the Pl/sql table is listed as a PRIMARY KEY constraint and a unique constraint column or a self increment integer. Can be listed as a record type or%rowtype type.

② fills the Pl/sql table available columns (record type): points to records through the index, and access to log members using the recording.

Grammar:

Pl/sql table name (indexed column value). Record member: = record Member type value;

Or

Pl/sql table name (indexed column value): = record type variable;

--note that the available columns declared in the Pl/sql table are the same as the record type variable structure that is assigned here

③ Access to Pl/sql table

Here is an example:

/*conn Scott/tiger

Create table Empa as SELECT * from EMP;

*/

Example:

Declare

Type RecType is record

(

Rno Empa.empno%type,

Rname Empa.ename%type,

Rsal Empa.sal%type

);

Type Tabtype is Table of RecType Index by Binary_integer;

Mytab Tabtype;

VN number;

Begin

--Fill

VN: = 1;

For Varr in (Select * from Empa-empno ASC) loop

Mytab (VN). Rno: = Varr.empno;

Mytab (VN). Rname: = Varr.ename;

Mytab (VN). Rsal: = Varr.sal;

VN: = VN + 1;

End Loop;

--Visit

VN: = Mytab.first;

--Get the first subscript of the table record, not necessarily 1 oh, let's actually here is 1, because the above is from 1 start to save OH

For Varr in VN.. Mytab.count Loop

Dbms_output.   Put_Line (VN | | ') '||   Mytab (VN). rno| | ' '||   Mytab (VN). rname| | ' '|| Mytab (VN). Rsal);

VN: = Mytab.next (VN);--can be replaced by vn:=vn+1;

End Loop;

End;

Example: Populating a Pl/sql table with the feature of the overall assignment of record records

Declare

Type RecType is record

(

Rno Empa.empno%type,

Rname Empa.ename%type,

Rsal Empa.sal%type

);

Type Tabtype is Table of RecType Index by Binary_integer;

Mytab Tabtype;

VN number;

Begin

--Fill

VN: = 1;

For Varr in (Select empno, ename, Sal from Empa order by Empno ASC)

Loop

Mytab (VN): = Varr; --Record overall assignment

VN: = VN + 1;

End Loop;

--Visit

VN: = Mytab.first;

For Varr in VN.. Mytab.count

Loop

Dbms_output.   Put_Line (VN | | ') '||   Mytab (VN). rno| | ' '||   Mytab (VN). rname| | ' '|| Mytab (VN). Rsal);

VN: = Mytab.next (VN);

End Loop;

End;

Example: Using PRIMARY KEY constraints and UNIQUE constraint columns as indexed columns (using EMPNO in the EMP table as indexed columns)

and use custom record types as available columns

Declare

Type RecType is record

(

Rno Empa.empno%type,

Rname Empa.ename%type,

Rsal Empa.sal%type

);

Type Tabtype is Table of RecType Index by Binary_integer;

Mytab Tabtype;

VN Empa.empno%type;

Begin

--Fill

For Varr in (Select empno,ename,sal to Empa ORDER by empno) loop

Mytab (varr.empno): = Varr;

Note that this is different from the padding above, oh, the table index above 1,2,3 .... Know to put all the data in, the table index here is the main key of the EMP table Oh, so the following in the value of this table can not simply let the index + 1, but use the next () function

End Loop;

--Visit

VN: = Mytab.first;

For Varr in 1..mytab.count

Loop

Dbms_output.   Put_Line (Mytab (VN). rno| | ' '||   Mytab (VN). rname| | ' '|| Mytab (VN). Rsal);

VN: = Mytab.next (VN);

End Loop;

End;

Example: Using PRIMARY KEY constraints and UNIQUE constraint columns as indexed columns (using EMPNO in the EMP table as indexed columns)

and use the%rowtype type as available columns

Declare

Type Tabtype is Table of Empa%rowtype Index by Binary_integer;

Mytab Tabtype;

VN Empa.empno%type;

Begin

--Fill

For Varr in (Select * from Empa ORDER by Empno)

Loop

Mytab (varr.empno): = Varr;

End Loop;

--Visit

VN: = Mytab.first;

For Varr in 1..mytab.count

Loop

Dbms_output.   Put_Line (Mytab (VN). empno| | ' '||   Mytab (VN). ename| | ' '|| Mytab (VN). Sal);

VN: = Mytab.next (VN);

End Loop;

End;

Select version from Product_component_version

Where SUBSTR (product,1,6) = ' Oracle ';