Oracle ForAll EXECUTE UPDATE statement

On the itpub see a forall execute update post, feel a little meaning, simple record.

Previously studied INSERT statements in ForAll, found that the statement through the binding array, the way to achieve a batch binding, a run of the way, thereby increasing the efficiency of execution.

However, for an UPDATE statement, Oracle implementations and inserts are different:

Sql> CREATE TABLE T as SELECT rownum ID from TAB;

Table has been created.

Sql> SELECT * from T;

Id

----------

1

2

3

4

5

6

6 rows have been selected.

Sql> DECLARE

2 TYPE t_id is TABLE of number INDEX by Binary_integer;

3 v_id t_id;

4 BEGIN

5 SELECT ID

6 BULK COLLECT into v_id

7 from T;

8 ForAll I in v_id. A.. v_id. Last

9 UPDATE T

SET ID = v_id (I);

one end;

12/

The PL/SQL process has completed successfully.

Sql> SELECT * from T;

Id

----------

6

6

6

6

6

6

6 rows have been selected.

Sql> ROLLBACK;

Fallback is complete.

The results are not as expected, still 123456, but all records are updated to 6. It is obvious that Oracle is unlikely to use only the array variable to update the last value in, so the records in the T table should be updated 6 times, so for the UPDATE statement, how many values are in the bound array, how many times the update runs, and for the Where condition is not currently specified, So every time you run, all the records are updated.

The correct wording is similar to:

Sql> DECLARE

2 TYPE t_id is TABLE of number INDEX by Binary_integer;

3 v_id t_id;

4 BEGIN

5 SELECT ID

6 BULK COLLECT into v_id

7 from T;

8 ForAll I in v_id. A.. v_id. Last

9 UPDATE T

SET ID = v_id (I)

One WHERE ID = v_id (I);

End;

13/

The PL/SQL process has completed successfully.

Sql> SELECT * from T;

Id

----------

1

2

3

4

5

6

6 rows have been selected.

The following verify the accuracy of the analysis just now:

sql> CREATE OR REPLACE TRIGGER t_statement

2 before UPDATE on T

3 BEGIN

4 Dbms_output. Put_Line (' STATEMENT ');

5 end;

6/

Trigger has been created

sql> CREATE OR REPLACE TRIGGER t_row

2 before UPDATE on T

3 for each ROW

4 BEGIN

5 Dbms_output. Put_Line (' ROW: ' | |: old.id);

6 end;

7/

Trigger has been created

Sql> SET Serverout on

Sql> DECLARE

2 TYPE t_id is TABLE of number INDEX by Binary_integer;

3 v_id t_id;

4 BEGIN

5 SELECT ID

6 BULK COLLECT into v_id

7 from T;

8 ForAll I in v_id. A.. v_id. Last

9 UPDATE T

SET ID = v_id (I)

One WHERE ID = v_id (I);

End;

13/

STATEMENT

Row:1

STATEMENT

Row:2

STATEMENT

Row:3

STATEMENT

Row:4

STATEMENT

Row:5

STATEMENT

Row:6

The PL/SQL process has completed successfully.

Sql> DECLARE

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

2 TYPE t_id is TABLE of number INDEX by Binary_integer;

3 v_id t_id;

4 BEGIN

5 SELECT ID

6 BULK COLLECT into v_id

7 from T;

8 ForAll I in v_id. A.. v_id. Last

9 UPDATE T

SET ID = v_id (I);

one end;

12/

STATEMENT

Row:1

Row:2

Row:3

Row:4

Row:5

Row:6

STATEMENT

Row:1

Row:1

Row:1

Row:1

Row:1

Row:1

STATEMENT

Row:2

Row:2

Row:2

Row:2

Row:2

Row:2

STATEMENT

Row:3

Row:3

Row:3

Row:3

Row:3

Row:3

STATEMENT

Row:4

Row:4

Row:4

Row:4

Row:4

Row:4

STATEMENT

Row:5

Row:5

Row:5

Row:5

Row:5

Row:5

The PL/SQL process has completed successfully.

For an UPDATE statement in ForAll, the number of executions is determined by the length of the array in the ForAll statement, which is significantly different from the only time that an INSERT statement is run.

Author: 51cto Blog Oracle Little Bastard