Detailed use of RECORD, VARRAY, and TABLE in ORACLE, oraclevarray

Source: Internet
Author: User

Detailed use of RECORD, VARRAY, and TABLE in ORACLE, oraclevarray
Limit limit 1 Description 1.1 RECORD

Define the record data type. It is similar to the STRUCTURE in C language. PL/SQL provides a holistic approach to combine several related, separated, and basic data type variables, that is, the RECORD compound data type. When using a record data type variable, you must first define the record composition and record variables in the Declaration section, and then reference the record variable itself or its members in the execution section.

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

TYPE RECORD_NAME is record (

V1 DATA_TYPE1 [not null] [: = DEFAULT_VALUE],

V2 DATA_TYPE2 [not null] [: = DEFAULT_VALUE],

VN DATA_TYPEN [not null] [: = DEFAULT_VALUE]);

1.2 VARRAY

An array is a set of group members of the same data type. Each member has a unique subscript, which depends on the position of the Member in the array. In PL/SQL, the array data type is VARRAY (variable array, that is, variable array ).

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

TYPE VARRAY_NAMEIS VARRAY (SIZE) OF ELEMENT_TYPE [not null];

Where, varray_name is the name of the VARRAY data type, and size is a positive integer, indicating the maximum number of members that can be accommodated. When the data type of each member is element_typeo by default, the member can take null values, otherwise, use not null.

 

1.3 TABLE

Defines the Data Type of the record table (or index table. It is similar to the record type, but it is an extension of the record type. It can process multiple rows of records, similar to the two-dimensional array in C language, so that it can imitate the tables in the database in PL/SQL.

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

Type table name is table of ELEMENT_TYPE [not null]

Index by [BINARY_INTEGER | PLS_INTEGER | VARRAY2];

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

Description of BINARY_INTEGER

For example, the statement "type numbers is table of number index by BINARY_INTEGER" adds "INDEX BYBINARY_INTEGER", and the subscript OF the numbers type is auto-increment. When the numbers type inserts an element, no Initialization is required, and no EXTEND space is required each time.

Without this statement "INDEXBY BINARY_INTEGER", you must display the initialization of the pair. EXTEND is required for each element inserted into a NUMBERS-type TABLE.

 

2. Example 2.1 create a table structure and prepare data

 

-- Create table SF_ORG (ORG_ID int not null, -- Organization primary key IDORG_NAME VARCHAR2 (50), -- Organization Name PARENT_ID INT -- Organization parent level) -- insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (1, 'level 1 Department 1', 0); -- level 2 Department insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (2, 'second-level department 2', 1); insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (3, 'second-level department 3', 1 ); insert into SF_ORG (ORG_ID, ORG_NAME, PARENT_ID) VALUES (4, 'level 2 Department 4', 1 );

2.2 example of using RECORD

First, define a record data type TYPE_ORG_RECORD with the same data type as several columns in the SF_ORG table, and then declare a record variable V_ORG_RECORD of this data type, finally, use the replace variable & ORG_ID to accept the input employee code, query and display the information in these columns of the employee. Note: When using a RECORD data type variable, use the "." operator to specify the RECORD variable name qualifier.

A variable of the record type can only store one row of records queried from the database. If multiple rows of records are queried, an error occurs.

 

Declare type TYPE_ORG_RECORD is record (V_ORG_NAME rows % TYPE, V_PARENT_ID rows % TYPE); V_ORG_RECORD TYPE_ORG_RECORD; begin select ORG_NAME, PARENT_ID INTO V_ORG_RECORD FROM SF_ORG so where so. ORG_ID = & ORG_ID; DBMS_OUTPUT.PUT_LINE ('department name: '| region); DBMS_OUTPUT.PUT_LINE ('superior department code:' | TO_CHAR (V_ORG_RECORD.V_PARENT_ID); END;

2.3 examples of VARRAY

First define a VARRAY data type ORG_VARRAY_TYPE that can save five Members of the VARCHAR2 (25) data type, and then declare a VARRAY variable V_ORG_VARRAY of the data type, finally, use the constructor syntax with the same name as ORG_VARRAY_TYPE to assign the initial value to the V_ORG_VARRAY variable and display the value assignment result.

Note: When referencing members in an array, you must use the sequential subscript in a pair of parentheses. The subscript starts from 1 rather than from 0.

 

Declare type ORG_VARRAY_TYPE is varray (5) OF VARCHAR2 (25); V_ORG_VARRAY ORG_VARRAY_TYPE; BEGIN V_ORG_VARRAY: = ORG_VARRAY_TYPE ('1', '2', '3', '4 ', '5'); DBMS_OUTPUT.PUT_LINE ('output 1: '| V_ORG_VARRAY (1) |', '| V_ORG_VARRAY (2) |', '| V_ORG_VARRAY (3) | ',' | V_ORG_VARRAY (4); DBMS_OUTPUT.PUT_LINE ('output 2: '| V_ORG_VARRAY (5); V_ORG_VARRAY (5): = '123 '; DBMS_OUTPUT.PUT_LINE ('output 3: '| V_ORG_VARRAY (5); END;

2.4 example of using TABLE 2.4.1 to store multiple rows in a single column

This is similar to VARRAY. However, the assignment method is slightly different. You cannot assign values using constructors of the same name. The details are as follows:

 

Declare type ORG_TABLE_TYPE is table of VARCHAR2 (25) index by BINARY_INTEGER; V_ORG_TABLE ORG_TABLE_TYPE; BEGIN V_ORG_TABLE (1): = '1'; V_ORG_TABLE (2): = '2 '; v_ORG_TABLE (3): = '3'; V_ORG_TABLE (4): = '4'; V_ORG_TABLE (5): = '5'; DBMS_OUTPUT.PUT_LINE ('output 1: '| V_ORG_TABLE (1) |', '| V_ORG_TABLE (2) |', '| V_ORG_TABLE (3) | ',' | V_ORG_TABLE (4); DBMS_OUTPUT.PUT_LINE ('output 2: '| V_ORG_TABLE (5); END;

2.4.2 combining multiple columns, multiple rows, and ROWTYPE

Bulkcollect can be used to load the query results to collections at a time. Instead of processing it one by one Using cursor.

 

DECLARE    TYPE T_TYPE IS TABLE OF SF_ORG%ROWTYPE;   V_TYPE  T_TYPE; BEGIN    SELECT ORG_ID,ORG_NAME,PARENT_ID BULK COLLECT INTO V_TYPE    FROM SF_ORG    WHERE SF_ORG.ORG_ID <= 3;    FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP        DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);    END LOOP; END;

2.4.3 use multiple columns, multiple rows, and RECORD together

Bulkcollect can be used to load the query results to collections at a time. Instead of processing it one by one Using cursor.

 

DECLARE    TYPE TEST_EMP IS RECORD   (    C1 SF_ORG.ORG_NAME%TYPE,    C2 SF_ORG.PARENT_ID%TYPE   );      TYPE T_TYPE IS TABLE OF TEST_EMP;   V_TYPE  T_TYPE; BEGIN    SELECT ORG_NAME,  PARENT_ID BULK COLLECT INTO V_TYPE    FROM SF_ORG    WHERE SF_ORG.ORG_ID <= 3;    FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP        DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);    END LOOP; END;

3. Problem

The VARRAY and TABLE sets cannot be queried directly. It can only be traversed.


Oracle table and array and record types are commonly used ??

Not commonly used. After all, the more complex things are to be written, the more complicated debugging and later handover work will be.
In fact, a well-designed mathematical model can make everything simple.

How to manipulate nested columns, varray columns, and object type columns in an oracle table

Well, let's talk about the update problem first. You can update and insert a record at a time, or bind multiple updates and inserts records at a time.
Here is an example of data insertion (insert multiple records. Handle other details by yourself.

My_temp emp_type; -- defines the set variable
Begin
For I in 1 .. 10 loop --- initialization assignment
My_temp (I): = I; --- initialize assignment
End loop; --- initialize assignment
Forall I in 1 .. my_temp.count --- insert in the table you want (this is not a loop, it is batch processing)
Insert into employee --- insert in the table you want
Values (my_temp (I) --- insert in the table you want

End;

Others, all in one mode

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.