FORALL and BULKCOLLECT statements for Oracle databases

Let's take a look at the execution process of PLSQL blocks: When the PLSQL runtime engine processes a piece of code, it uses the PLSQL engine to execute Procedural Code and sends the SQL statement

Let's take a look at the execution process of PL/SQL blocks: When the PL/SQL runtime engine processes a piece of code, it uses the PL/SQL engine to execute Procedural Code, the SQL statement is sent

Let's take a look at the execution process of PL/SQL blocks: When the PL/SQL runtime engine processes a piece of code, it uses the PL/SQL engine to execute Procedural Code, the SQL statement is sent to the SQL engine for execution. After the SQL engine completes execution, the result is returned to the PL/SQL engine. This kind of interaction between PL/SQL engine and SQL engine is called context switch ). Each exchange brings additional costs.

FORALL is used to enhance the exchange between PL/SQL engines and SQL engines.

Bulk collect is used to enhance the exchange between SQL engines and PL/SQL engines. (We have already introduced it)

1. FORALL Introduction

With FORALL, multiple DML can be batch sent to the SQL engine for execution, minimizing the overhead of context interaction. The following is one of FORALL:

Syntax:

1 FORALL index_name IN 2 {lower_bound .. upper_bound index_collection5} dml_statement;

Note:

Index_name: A non-declarative identifier used as a set subscript.

Lower_bound... upper_bound: A number expression to specify the lower limit and upper limit of consecutive valid index numbers. This expression only needs to be parsed once.

Indices of collection_name: used to point to the actual subscript OF a sparse array. Skip the element without a value assignment, such as the deleted element. NULL is also a value.

Values of index_collection_name: treats the value in the set as a subscript, and the type OF the set value can only be PLS_INTEGER/BINARY_INTEGER.

Save exceptions: an optional keyword indicating that an exception is thrown until the forall loop is executed even if some DML statements fail. You can use SQL % BULK_EXCEPTIONS to view the exception information.

Dml_statement: a static statement, such as UPDATE or DELETE, or a dynamic (execute immediate) DML statement.

2. Use of FORALL

The table structure used in the example is as follows:

Tmp_tab (2 id NUMBER (5), 3 name VARCHAR2 (50) 4 );

Example 1: Use FORALL to insert, modify, and delete data in batches:

TYPE tb_table_type tmp_tab % rowtype index by BINARY_INTEGER; 5 tb_table tb_table_type; I IN 1 .. 100 LOOP 8tb_table (I ). id: = I; 10 END LOOP ;.. tb_table. tmp_tab VALUES tb_table (I); 14 END;

TYPE tb_table_type tmp_tab % rowtype index by BINARY_INTEGER; 4 tb_table tb_table_type; I IN 1 .. 100 LOOP 7tb_table (I ). id: = I; 9 end loop; tmp_tab t SET row = tb_table (I) WHERE t. id = tb_table (I ). id; 12 END;

TYPE tb_table_type tmp_tab % rowtype index by BINARY_INTEGER; tb_table tb_table_type ;.. 10 LOOPtb_table (I ). id: = I; tb_table (I ). name: I; end loop; FORALL I tmp_tab WHERE id = tb_table (I ). id; END;

Example 2: Use the indices of clause:

TYPE demo_table_type tmp_tab % rowtype index by BINARY_INTEGER; 3 demo_table demo_table_type; I IN 1 .. 10 LOOP 6demo_table (I ). id: = I; 8 END LOOP; demo_table.delete (3); 11 demo_table.delete (6); 12 demo_table.delete (9 ); 13 FORALL I in indices of demo_tabletmp_tab VALUES demo_table (I); 15 END;

Example 3: Use the values of clause:

TYPE required pls_integer; 3 index_poniter index_poniter_type; tmp_tab % rowtype index by BINARY_INTEGER; 5 demo_table demo_table_type; index_poniter: = index_poniter_type );.. 10 LOOP 9demo_table (I ). id: = I; 11 end loop; index_ponitertmp_tab VALUES demo_table (I); 14 END;

3. Notes for FORALL

When using FORALL, follow the following rules:

4. Introduction to BULK COLLECT

The bulk collect clause will batch retrieve the results, that is, bind the result set to a collection variable at a time, and send the results from the SQL engine to the PL/SQL engine.

Bulk collect can be used in select into, fetch into, and returning into clauses. The usage of bulk collect in these situations is described one by one.

5. Use of BULK COLLECT

5.1 use bulk collect in SELECT

Example: