Oracle Common Composite data types: BULK COLLECT (Batch aggregation type) and array collection type type is table of tables%rowtype index by binary_integer__oracle

Example 1: Bulk Query The department number "10" and print them out.

DECLARE

TYPE Emp_table_type is table of My_emp%rowtype INDEX by Binary_integer;

V_emp_table Emp_table_type;

BEGIN

SELECT * BULK COLLECT into v_emp_table from My_emp WHERE deptno=&deptno;

For I in 1..v_emp_table. COUNT LOOP

Dbms_output.put_line (' employee_info: ' | | V_emp_table (i). ename| |

','|| V_emp_table (i). job| |

','|| V_emp_table (i). HireDate);

End LOOP;

End;

Description section:

1. DECLARE the section below that you want to declare

2. Type declaration is the name of type Emp_table_typ E type

3. Is table of specifies the array type of the table of a collection, simply a data type that can store more than one row of rows, my_emp indicates on which table (the existing table)%rowtype the data type of the row on the table.

4. Index by Binary_integer refers to the indexing organization type

5. v_emp_table defines a variable to store the collection data type

6. BULK COLLECT into refers to a batch aggregation type, which, in simple terms, can store a multiline, multiple-column storage type, into which the following specifies where to come from,

7. V_emp_table. Count is used to v_emp_table the quantity inside.

8. (i) indication of the next label

Example 2: Batch update employee pay with department number "10"

DECLARE

TYPE Ename_table_type is table of My_emp.ename%type;

TYPE Sal_table_type is table of My_emp.sal%type;

V_ename_table Ename_table_type;

V_sal_table Sal_table_type;

BEGIN

UPDATE my_emp SET sal=sal*1.20 WHERE Deptno=&deptno returning ename,sal

BULK COLLECT into v_ename_table,v_sal_table;

For I in 1..v_ename_table. COUNT LOOP

Dbms_output.put_line (' employee_info: ' | | V_ename_table (i) | | | V_sal_table (i));

End LOOP;

End;

Description section:

1.%type and My_emp.ename data types

V_ename_table Ename_table_type;

2. V_sal_table Sal_table_type; Define variables to store them.

3. Returning is used to specify the part to be returned, BULK COLLECT into a mass aggregation type

4. Print them out with a for

(summary):

1. Open the set serveroutput on the experiment

2. The above examples are intended primarily to improve performance. The performance here mainly refers to speed. If the integrated from the entire OLTP or OLAP system is a different story, not discussed here.

3. Speed refers to the bulk INSERT, UPDATE, Delete, why will the speed increase?

4. The extracted data is processed in memory because it is common knowledge that the memory is processed more quickly.

5. Here are a few examples to see for yourselves.

Example 3: BULK INSERT

CREATE TABLE my_emp (empno number (4), ename VARCHAR2 (15))

/

DECLARE

TYPE Empno_table_type is Table of number (4) INDEX by Binary_integer;

TYPE Ename_table_type is Table of VARCHAR2 (a) INDEX by Binary_integer;

V_empno_table Empno_table_type;

V_ename_table Ename_table_type;

BEGIN

For I in 1..1000 loop

V_empno_table (i): =i+2000;

V_ename_table (i): = ' NAME ' | | To_char (i);

End LOOP;

ForAll i in 1..v_empno_table. COUNT inserts into My_emp VALUES (v_empno_table (i), v_ename_table (i));

End;

/

Example 4: Batch Update

CREATE TABLE my_emp (empno number (4), ename VARCHAR2 (15))

/

DECLARE

TYPE Empno_table_type is table of My_emp.empno%type INDEX by Binary_integer;

TYPE Ename_table_type is table of My_emp.ename%type INDEX by Binary_integer;

V_empno_table Empno_table_type;

V_ename_table Ename_table_type;

BEGIN

For I in 1..1000 loop

V_empno_table (i): =i+2000;

V_ename_table (i): = ' CN ' | | To_char (i);

End LOOP;

ForAll i in 1..v_empno_table. COUNT UPDATE my_emp SET ename=v_ename_table (i) WHERE empno=v_empno_table (i);

End;

/

Example 5: Bulk deletion

DECLARE

TYPE Empno_table_type is table of My_emp.empno%type INDEX by Binary_integer;

V_empno_table Empno_table_type;

BEGIN

For I in 1..8 loop

V_empno_table (i): =i+2000;

End LOOP;

ForAll i in 1..3 deletes from my_emp WHERE empno=v_empno_table (i);

End;

/

Example 6:sql%bulk_rowcount Property

DECLARE

TYPE Deptno_table_type is Table of number (2);

V_deptno_table Deptno_table_type:=deptno_table_type (10,20,30);

BEGIN

ForAll i in 1. V_deptno_table. COUNT UPDATE emp2 SET sal=sal*0.5 WHERE deptno=v_deptno_table (i);

Dbms_output.put_line (' department_10: ' | | Sql%bulk_rowcount (1) | | | ' rows ');

Dbms_output.put_line (' department_10: ' | | Sql%bulk_rowcount (1) | | | ' rows ');

Dbms_output.put_line (' department_20: ' | | Sql%bulk_rowcount (2) | | | ' rows ');

Dbms_output.put_line (' department_30: ' | | Sql%bulk_rowcount (3) | | | ' rows ');

End;

/

Sql%bulk_rowcount (i) represents the number of rows that the ForAll statement acts on