Oracle array example

Source: Internet
Author: User
Tags 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;

Related Article

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.