ORACLE 10G OFFICIAL DOCUMNET 

-------------------------------------------------------------------------
一 介绍：
1、语法
for all statement ::=



bounds_clause ::=







2、关键字与参数介绍：
==index_name：一个无需声明的标识符，作为集合下标使用；
==sql_statement：静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML语句。
==SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL loop执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。
==lower_bound .. upper_bound：数字表达式，来指定一组连续有效的索引数字。该表达式只需解析一次。
==INDICES OF collection_name：用于指向稀疏数组的实际下标
==VALUES OF index_collection_name：用于指向集合的一个子集的下标数组


二 使用FORALL：
1、循环中声明删除语句（Issuing DELETE Statements in a Loop

CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS VARRAY(20) OF NUMBER;
   depts NumList := NumList(10, 30, 70);  -- department numbers
BEGIN
   FORALL i IN depts.FIRST..depts.LAST
      DELETE FROM employees_temp WHERE department_id = depts(i);
   COMMIT;
END;
/

2、循环中声明插入语句（Issuing INSERT Statements in a Loop）

CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
DECLARE
  TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
  TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
  pnums  NumTab;
  pnames NameTab;
  iterations CONSTANT PLS_INTEGER := 50000;
  t1 INTEGER;
  t2 INTEGER;
  t3 INTEGER;
BEGIN
  FOR j IN 1..iterations LOOP  -- load index-by tables
     pnums(j) := j;
     pnames(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;
  t1 := DBMS_UTILITY.get_time;
  FOR i IN 1..iterations LOOP  -- use FOR loop
     INSERT INTO parts1 VALUES (pnums(i), pnames(i));
  END LOOP;
  t2 := DBMS_UTILITY.get_time;
  FORALL i IN 1..iterations  -- use FORALL statement
     INSERT INTO parts2 VALUES (pnums(i), pnames(i));
  t3 := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
  DBMS_OUTPUT.PUT_LINE('---------------------');
  DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((t2 - t1)/100));
  DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)/100));
  COMMIT;
END;
/

FORALL要明显快于FOR..LOOP结构：

Execution Time (secs)
---------------------
FOR loop: 5.14
FORALL:   .56

PL/SQL 过程已成功完成。


3、集合部分元素使用FORALL（Using FORALL with Part of a Collection）

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS VARRAY(10) OF NUMBER;
   depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
BEGIN
   FORALL j IN 4..7  -- use only part of varray
      DELETE FROM employees_temp WHERE department_id = depts(j);
   COMMIT;
END;
/


4、对非连续索引值使用FORALL（Using FORALL with Non-Consecutive Index Values）

-- Create empty tables to hold order details
CREATE TABLE  valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
CREATE TABLE  big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
CREATE TABLE  rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;

DECLARE
-- Make collections to hold a set of customer names and order amounts.
   SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
   TYPE cust_typ IS TABLe OF cust_name;
   cust_tab cust_typ;
   SUBTYPE order_amount IS valid_orders.amount%TYPE;
   TYPE amount_typ IS TABLE OF NUMBER;
   amount_tab amount_typ;
-- Make other collections to point into the CUST_TAB collection.
   TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
   big_order_tab index_pointer_t := index_pointer_t();
   rejected_order_tab index_pointer_t := index_pointer_t();
   PROCEDURE setup_data IS BEGIN
 -- Set up sample order data, including some invalid orders and some 'big' orders.
     cust_tab := cust_typ('Company1','Company2','Company3','Company4','Company5');
     amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
   END;
   
BEGIN
   setup_data(); --initialization
   DBMS_OUTPUT.PUT_LINE('--- Original order data ---');
   FOR i IN 1..cust_tab.LAST LOOP
     DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
                           amount_tab(i));
   END LOOP;
-- Delete invalid orders (where amount is null or 0).
   FOR i IN 1..cust_tab.LAST LOOP
     IF amount_tab(i) is null or amount_tab(i) = 0 THEN
        cust_tab.delete(i);
        amount_tab.delete(i);
     END IF;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('--- Data with invalid orders deleted ---');
   FOR i IN 1..cust_tab.LAST LOOP
     IF cust_tab.EXISTS(i) THEN
       DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
                             amount_tab(i));
      END IF;
   END LOOP;
-- Because the subscripts of the collections are not consecutive, use
-- FORALL...INDICES OF to iterate through the actual subscripts, 
-- rather than 1..COUNT
   FORALL i IN INDICES OF cust_tab
     INSERT INTO valid_orders(cust_name, amount) 
        VALUES(cust_tab(i), amount_tab(i));
   
-- Now process the order data differently
-- Extract 2 subsets and store each subset in a different table
   setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.
   FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
     IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
       rejected_order_tab.EXTEND; -- Add a new element to this collection
-- Record the subscript from the original collection
       rejected_order_tab(rejected_order_tab.LAST) := i; 
     END IF;
     IF amount_tab(i) > 2000 THEN
        big_order_tab.EXTEND; -- Add a new element to this collection
-- Record the subscript from the original collection
        big_order_tab(big_order_tab.LAST) := i;
     END IF;
   END LOOP;
-- Now it's easy to run one DML statement on one subset of elements, 
-- and another DML statement on a different subset.
   FORALL i IN VALUES OF rejected_order_tab
     INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
   FORALL i IN VALUES OF big_order_tab
     INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
   COMMIT;
END;
/

--- Original order data ---
Customer #1, Company1: $5000.01
Customer #2, Company2: $0
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
Customer #5, Company5: $
--- Data with invalid orders deleted ---
Customer #1, Company1: $5000.01
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
 
PL/SQL procedure successfully completed

-- Verify that the correct order details were stored
SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;
SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;
SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;


5、使用%BULK_ROWCOUNT返回受影响的记录行数
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(30, 50, 60);
BEGIN
   FORALL j IN depts.FIRST..depts.LAST
      DELETE FROM emp_temp WHERE department_id = depts(j);
-- How many rows were affected by each DELETE statement?
   FOR i IN depts.FIRST..depts.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted ' ||
         SQL%BULK_ROWCOUNT(i) || ' rows.');
   END LOOP;
END;
/




6、FORALL与BULK COLLECT 一起使用（Using FORALL With BULK COLLECT）

CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10,20,30);
   TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
   TYPE dept_t IS TABLE OF employees.department_id%TYPE;
   e_ids enum_t;
   d_ids dept_t;
BEGIN
  FORALL j IN depts.FIRST..depts.LAST
    DELETE FROM emp_temp WHERE department_id = depts(j)
       RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids;
  DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
  FOR i IN e_ids.FIRST .. e_ids.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));
  END LOOP;
END;
/












-----------------------------
Present   by   dylan.

repo forall可以遍历每个repo仓库并执行同样的命令
用法为:
repo forall [<project>...] -c <command> [<arg>...]

-c后可以直接跟任何shell命令
eg:
repo forall -p -c git checkout branch_name



repo forall -h

Usage: repo forall [<project>...] -c <command> [<arg>...]

repo forall -r str1 [str2] ... -c <command> [<arg>...]"



Options:

  -h, --help            show this help message and exit

  -r, --regex           Execute the command only on projects matching regex or

                        wildcard expression

  -g GROUPS, --groups=GROUPS

                        Execute the command only on projects matching the

                        specified groups

  -c, --command         Command (and arguments) to execute

  -e, --abort-on-errors

                        Abort if a command exits unsuccessfully



  Output:

    -p                  Show project headers before output

    -v, --verbose       Show command error messages

    -j JOBS, --jobs=JOBS

                        number of commands to execute simultaneously

文章目录
1 概述
1.1 图示
2 forall 的三种用法
2.1 forall i in index_min .. index_max
2.2 forall i in indices of collection
2.3 forall i in values of collection
3 forall 与 for 效率对比

1 概述
1. 作用：提高在 pl/sql 块中，处理 dml 语句的效率

2. "上下文切换": 
   (1) 在 pl/sql 中，'pl/sql 块' 由 'pl/sql 引擎' 处理
   (2) 而其中的 'sql 语句' 则由 'pl/sql 引擎' 发送至 'sql 引擎处理' 
   (3) 后者处理完毕后再向前者返回数据，两者之间的通信称为 "上下文切换"，
       过多的上下文切换将带来过量的性能负载。

3. pl/sql 和 sql 引擎之间的 交互（"上下文切换"）
   (1) forall      : 用于增强 'pl/sql 引擎' -> 'sql 引擎' 的交互
   (2) bulk collect: 用于增强 'sql 引擎' -> 'pl/sql 引擎' 的交互


扩展： pl/sql bulk collect 详解

1.1 图示
举个例子: 在 pl/sql 中处理 10 个 update 语句
(1) 一般的 : 'pl/sql 引擎' '每次' 发送 1 个 update 语句给 'sql 引擎' 处理， "上下文切换" 1 次，共计 10 次
(2) forall: 'pl/sql 引擎' '一次性' 发送 10 个 update 语句给 'sql 引擎' 处理， "上下文切换" 1 次，共计 1 次


2 forall 的三种用法
1. 三种用法
   (1) forall i in index_min .. index_max: '下标必须存在'，否则报错
   (2) forall i in indices of collection : 若下标 '不存在，就跳过'（上述的扩展）
   (3) forall i in values of collection  : 仅插入 values 中 '存在的记录'
 
2. 注意：forall 后只能紧跟 1 条 dml 语句

3. 基础数据准备
   create table stu_info (
     sno   number(10),
     sname varchar2(10)
   );

2.1 forall i in index_min … index_max
-- 注意：'下标必须存在'，否则报错
declare
  type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
  v_stu_info_rows stu_info_table;
begin
  -- 制造数据
  for i in 1 .. 3 loop
    v_stu_info_rows(i).sno := i;
    v_stu_info_rows(i).sname := 'a' || i;
  end loop;

  -- 演示报错（下标为 2 的元素不存在）
  -- v_stu_info_rows.delete(2);

  -- insert 演示（update、delete 同理）
  forall i in v_stu_info_rows.first .. v_stu_info_rows.last
    insert into stu_info values v_stu_info_rows (i); 
    
  -- commit;
end;

测试结果：
sno	sname
1	a1	
2	a2	
3	a3	

2.2 forall i in indices of collection
-- 注意：若下标 '不存在，就跳过'
declare
  type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
  v_stu_info_rows stu_info_table;
begin
  -- 制造数据
  for i in 1 .. 3 loop
    v_stu_info_rows(i).sno := i;
    v_stu_info_rows(i).sname := 'a' || i;
  end loop;

  -- 演示报错（下标为 2 的元素不存在）
  v_stu_info_rows.delete(2);

  -- insert 演示（update、delete 同理）
  forall i in indices of v_stu_info_rows
    insert into stu_info values v_stu_info_rows (i); 
    
  -- commit;
end;

测试结果：（跳过了 sno = 2 的记录）
sno	sname
1	a1
3	a3

2.3 forall i in values of collection
-- 注意： 仅插入 values 中 '存在的记录'
declare
  type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
  type index_table is table of pls_integer;

  v_stu_info_rows stu_info_table;
  v_index_table   index_table;
begin
  -- 制造数据
  for i in 1 .. 3 loop
    v_stu_info_rows(i).sno := i;
    v_stu_info_rows(i).sname := 'a' || i;
  end loop;

  -- values 记录列表
  v_index_table := index_table(1, 3);

  -- insert 演示（update、delete 同理）
  forall i in values of v_index_table
    insert into stu_info values v_stu_info_rows (i);

  -- commit;
end;

测试结果：(没有 sno = 2 的记录）
sno	sname
1	a1
3	a3

提示：一般用 pls_integer，除非批处理业务量大于 21,4748,3647，才考虑用 binary_integer




关键字
解释




pls_integer
检索速度快，超过最大长度时会溢出（最大长度：-2^31 至 2^31 - 1）


binary_integer
检索速度一般，超过最大长度是不会溢出


3 forall 与 for 效率对比
结论：
dml 执行效率： forall > for

数据准备：
-- 清空数据，方便测试
truncate table stu_info; 

create table stu_info_for as select * from stu_info where 1 = 2; -- for
create table stu_info_forall as select * from stu_info where 1 = 2; -- forall

效率验证：（50W 数据插入）
declare
  type stu_info_table is table of scott.stu_info%rowtype index by pls_integer;
  v_stu_info_rows stu_info_table;
  v_init_time     pls_integer;
  v_for_time      pls_integer;
  v_forall_time   pls_integer;
begin
  -- 制造数据
  for i in 1 .. 500000 loop
    v_stu_info_rows(i).sno := i;
    v_stu_info_rows(i).sname := 'a' || i;
  end loop;

  -- 初始化时间
  v_init_time := dbms_utility.get_time;

  -- 1 演示：for 
  for i in 1 .. 500000 loop
    insert into stu_info_for values v_stu_info_rows (i);
  end loop;

  v_for_time := dbms_utility.get_time;

  -- 2 演示：forall
  forall i in 1 .. 500000
    insert into stu_info_forall values v_stu_info_rows (i);

  v_forall_time := dbms_utility.get_time;

  dbms_output.put_line('for 用时：' || to_char(v_for_time - v_init_time));
  dbms_output.put_line('forall 用时：' || to_char(v_forall_time - v_for_time));
   -- commit;
exception
  when others then
    dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

测试结果：（forall > for，且数据量越大，差异越大）
for 用时：2326
forall 用时：30

提示：dbms_utility.get_time -> 100 = 1s

验证思路：Oracle dbms_utility.get_time 用法

1.repo forall命令
 # repo forall -help
 # repo forall -c: 此命令遍历所有的git仓库，并在每个仓库执行-c所指定的命令，被执行的命令不限于git命令，而是任何被系统支持的命令，比如：ls, git log, git status等
2.repo forall -c使用
  # 切换分支
  # repo forall -c git checkout dev_test
  # 删除分支
  # repo forall -c git branch -D dev_test
  # 丢弃分支
  # repo forall -c git git reset —hard 提交ID(或最原始:HEAD)
  # repo forall -r framework/base/core -c git reset —hard 提交ID(或最原始HEAD)