Oracle array example

-- 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;