Array types are used in stored procedures

Oracle's definition array type

Note: The source of this article:Oracle's definition Group type

Oracle array type, there is no ready-made type, but can be defined freely, it is convenient.

Oracle arrays can be divided into fixed-length arrays and variable-length arrays in two categories. The following are mainly one-dimensional arrays: 1: Fixed-length arrays:

  1/* Fixed-length character array, array size 10*/  2 declare  3 type V_arr is Varray (ten) of VARCHAR2 (30);   4 My_arr V_arr;   5 my_arr:=v_arr (' 1 ', ' 2 ', ' 3 ');   6 begin  7for i in 1..my_arr.count  8  Loop  9 Dbms_output_line (My_arr (i)); ten end loop;  one end;  A

2: variable-length array:

1 / * variable long character array, element size 30, index designator integer type self-growing * /2Declare3Type v_table is Table of VARCHAR2 (in) index by Binary_integer;4 --The type can be the preceding type definition, and the index by Binary_integer clause represents the index as a signed integer.5 --This is the table variable name (index symbol integer) that accesses the data method in the table type variable. 6My_table v_table;7Begin8For I in 1..209LoopTenMy_table (i): =i; OneDbms_output.put_line (my_table (i)); AEnd Loop; -End - 

Use of an Oracle stored procedure array collection

Note: The use of the Oracle stored procedure Array collection is the source of this article

1 description

1.1 RECORD

Defines the record data type. It is similar to the structure data type (STRUCTURE) in C, and PL/SQL provides a way to make a whole of several related, detached, basic data types, the record composite data type. When using a record data type variable, you need to define the composition of the record, the recorded variable, and then refer to the record variable itself or its members in the execution section.

The syntax for defining a record data type is as follows:

  1 TYPE record_name is the RECORD (  2  3 V1 data_type1 [not Null][:=default_value],   4  5 V2 data_type2 [not Null][:=default_value],  6  7 VN Data_typen [not Null][:=default_value]);   8

1.2 Varray

An array is a set of members with the same data type. Each member has a unique subscript, depending on the position of the member in the array. In PL/SQL, the array data type is varray (variable array, which can be a variable group).

The syntax for defining the Varray data type is as follows:

  1 TYPE varray_nameis varray (SIZE) of Element_type [not NULL];

Where Varray_name is the name of the Varray data type, size is a positive integer that represents the maximum number of members that can be accommodated, and the data type of each member is ELEMENT_TYPEO by default, the member can take a null value, otherwise it needs to be restricted with NOT NULL. 1.3 TABLE

Defines the record table (or index table) data type. It is similar to the record type, but it is an extension of the record type. It can handle multiple rows of records, similar to a two-dimensional array in C, making it possible to mimic tables in a database in PL/SQL.

The syntax for defining a record table type is as follows:

  1 TYPE table NAME is table of Element_type [not NULL]  2  3 INDEX by [Binary_integer | pls_integer| VARRAY2];   4

The keyword index by indicates that a primary key index is created to reference a particular row in the record table variable.

Binary_integer's description

Such as statement: Type NUMBERS is TABLE of number index by Binary_integer; the effect is that after adding "INDEX Bybinary_integer", the subscript of NUMBERS type is self-growth, NUMBERS class type when inserting elements, there is no need to initialize, and you do not need to add a space each time extend.

Without the phrase "Indexby binary_integer", it is necessary to display the initialization, and each time a element is inserted into a table of type numbers, it needs to be extend first.

2 examples

2.1 Creating table structures and data preparation

1 --Organizational structure chart2CREATE TABLE sf_org3(4org_id INT not NULL,--organization primary Key ID5Org_name VARCHAR2 (50),--Organization name6parent_id INT--The parent of the organization7)8 9 --Level one organizational structureTenINSERT into sf_org (org_id, Org_name, parent_id) VALUES (1, 'first-level department 1', 0); One  A -- Level two division -  -INSERT into sf_org (org_id, Org_name, parent_id) VALUES (2, 'Level Two Department 2', 1); theINSERT into sf_org (org_id, Org_name, parent_id) VALUES (3, 'Level Two Department 3', 1); -INSERT into sf_org (org_id, Org_name, parent_id) VALUES (4, 'Level Two Department 4', 1);

Examples of use of the 2.2 record

Define a record data type Type_org_record that is the same as the data type of only a few columns in the sf_org table, then declare a record variable V_org_record for that data type, and finally use the replacement variable &org_id to accept the entered employee code, Query and display the information in these columns of the employee. Note that the "." is used when using variables of the record data type. operator to specify the record variable name qualifier.

A variable of record type can only hold a row of records queried from the database, and if multiple rows of records are queried, an error occurs.

1DECLARE2TYPE Type_org_record is RECORD (3V_org_name Sf_org.org_name%type,4v_parent_id sf_org. Parent_id%type);5V_org_record Type_org_record;6BEGIN7SELECT org_name,parent_id into V_org_record8From sf_org so9WHERE so.org_id=&org_id;TenDbms_output. Put_Line ('Department Name:' | | V_org_record. V_org_name); OneDbms_output. Put_Line ('Parent Department Code:' | | To_char (V_org_record. v_parent_id)); AEND;

Examples of use of 2.3 varray

Define a Varray data type Org_varray_type that can hold a member of 5 VARCHAR2 (25) data types, then declare a varray variable V_org_varray for that data type, and finally use the Org_varray_ A constructor syntax with the same name as the type datatype assigns an initial value to the V_org_varray variable and displays the result of the assignment.

Note that when you reference a member in an array. You need to use a sequential subscript in a pair of parentheses, and the subscript starts at 1 instead of 0.

1DECLARE2TYPE Org_varray_type is Varray (5) of VARCHAR2 (25);3V_org_varray Org_varray_type;4BEGIN5V_org_varray: = Org_varray_type ('1‘,‘2‘,‘3‘,‘4‘,‘5‘);6Dbms_output. Put_Line ('Output 1:' | | V_org_varray (1) | | ‘,' | | V_org_varray (2) | | ‘,' | | V_org_varray (3) | | ‘,' | | V_org_varray (4));7Dbms_output. Put_Line ('Output 2:' | | V_org_varray (5));8V_org_varray (5): = '5001‘;9Dbms_output. Put_Line ('Output 3:' | | V_org_varray (5));TenEND;

2.4 Table uses example 2.4.1 to store single-row multiple rows

This is similar to Varray. However, the assignment is slightly different and cannot be assigned using a constructor of the same name. The following are specific:

1DECLARE2TYPE Org_table_type is TABLE of VARCHAR2 (25)3INDEX by Binary_integer;4V_org_table Org_table_type;5BEGIN6V_org_table (1): = '1‘;7V_org_table (2): = '2‘;8V_org_table (3): = '3‘;9V_org_table (4): = '4‘;TenV_org_table (5): = '5‘; OneDbms_output. Put_Line ('Output 1:' | | V_org_table (1) | | ‘,' | | V_org_table (2) | | ‘,' | | V_org_table (3) | | ‘,' | | V_org_table (4)); ADbms_output. Put_Line ('Output 2:' | | V_org_table (5)); -END;

2.4.2 Storage Doriedo and RowType used together

With Bulkcollect, query results can be loaded into collections at once. Instead of being dealt with by the cursor in a single line.

1DECLARE2TYPE T_type is TABLE of Sf_org%rowtype;3V_type T_type;4BEGIN5SELECT org_id,org_name,parent_id BULK COLLECT into V_type6From sf_org7WHERE sf_org.org_id <= 3;8 9For V_index in V_type. First: V_type. Last LOOPTenDbms_output. Put_Line (V_type (V_index). C1 | | ‘ ' | | V_type (V_index). C2); OneEND LOOP; AEND;

2.4.3 storage with Doriedo and record

With Bulkcollect, query results can be loaded into collections at once. Instead of being dealt with by the cursor in a single line.

1DECLARE2TYPE Test_emp is RECORD3(4C1 Sf_org.org_name%type,5C2 sf_org. Parent_id%type6);7TYPE T_type is TABLE of test_emp;8V_type T_type;9BEGINTenSELECT org_name, parent_id BULK COLLECT into V_type OneFrom sf_org AWHERE sf_org.org_id <= 3; -  -For V_index in V_type. First: V_type. Last LOOP theDbms_output. Put_Line (V_type (V_index). C1 | | ‘ ' | | V_type (V_index). C2); -END LOOP; -END;

Oracle stored procedure custom array definition and usage

Recently archived for the company project database through stored procedures, you need to use a custom array

Baidu results in many of the written is not very clear, variable-length array after the definition of how to use.

Make a note here:

Defined:

Type Id_array is table of number (a) index by Binary_integer;

Acb_ids Id_array;

This definition method applies to the package, if it is purely in the stored procedure custom type please Baidu.

Use:

Acb_ids (acb_ids.count+1): = c_account_books_cbs_rec.acb_id;

Many examples on the Web are: Acb_ids (acb_ids.count): = c_account_books_cbs_rec.acb_id; it is useless to write, because Acb_ids is empty at this time, then acb_ Ids.count is also invalid, acb_ids in the use of "no data found", so need to acb_ids.count+1.

C_account_books_cbs_rec is an object in the cursor traversal, which means that the ID of each row of records in the cursor data is placed in a custom array, which facilitates the return of stored procedure values or the use of cursors in the outside program body.

For x in 1: Acb_ids.count Loop
Do something;
End Loop;

A parameter type is defined as an array in a stored procedure

Note: This article is from: " stored procedure defines parameter type array "

1: Stored Procedure

  1 Procedure update_batch_id (p_entity_id in number,  2                             p_vdr_id in    fnd_table_of_ Number) is  3   begin  4  5for     i in 1: p_vdr_id. COUNT Loop  6       update cux_table_header CVS  7          Set cvs.attribute10 = P_entity_ ID  8        where cvs.header_id = p_vdr_id (i);   9     End Loop, ten   end;

Called in 2:java

1List List =NewArrayList ();2...3List.add (Row.getheaderid ());4...5 6 7Oraclecallablestatement statement =NULL;8OracleConnection OracleConnection = (oracleconnection) tsn.getjdbcconnection ();9 intSize = List.size ();Ten if(size>0) One{ ANumber[] Vdridarray = (number[]) List.toarray (NewNumber[size]); -ARRAY vdrarray=NULL; - Try{ theArraydescriptor Tableofnumber = -Oracle.sql.ArrayDescriptor.createDescriptor ("Fnd_table_of_number", -OracleConnection); -Vdrarray =NewARRAY (Tableofnumber, OracleConnection, Vdridarray); +String sql = -"BEGIN cux_xxxxxxx_pkg. UPDATE_BATCH_ID (: 1,:2); end;"; +statement = (oraclecallablestatement) oracleconnection.preparecall (SQL); A  atStatement.setobject (1, BatchId); -Statement.setarray (2, Vdrarray); -Statement.execute (); -}Catch(Exception ex) { -String[][] Stra2 = {"123456wewee", Ex.getmessage ()},}; -Logutil.of (Stra2, This). Print (PageContext); inEx.printstacktrace (); -System.out.println (Ex.getmessage ()); to} +}

——————————————————————————————————————————————————————————————————————————————————————————

Array types are used in stored procedures