Introduction to oracle array instances

Source: Internet
Author: User
This article introduces in detail various oracle Array Operations. If you need it, you can refer to it.

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

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:
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
* 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:

-- 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:
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 the table and table,

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

And: new Records

The Code is as follows:
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:

*/

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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.