文章目录
 
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 用法

 

 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
 

作为一个脚本小子，肯定离不开工具了
 
 
OneForAll是一款功能强大的子域收集工具
 
OneForAll基于Python 3.8.0开发和测试，请使用高于Python 3.8.0的稳定发行版本，其他版本可能会出现一些问题（Windows平台必须使用3.8.0以上版本），安装Python环境可以参考Python 3 安装指南。
 
项目地址和安装指南https://github.com/shmilylty/OneForAll
 
root@kali:~# git clone https://gitee.com/shmilylty/OneForAll.git
 
 
root@kali:~# sudo apt install python3.8 python3.8-dev python3-testresources
root@kali:~/OneForAll# sudo apt install python3-pip
root@kali:~/OneForAll# sudo python3.8 -m pip install -U pip setuptools wheel -i https://mirrors.aliyun.com/pypi/simple/
 
root@kali:~/OneForAll# sudo pip3 install --ignore-installed -r requirements.txt -i https://mirrors.aliyun.com/pypi/simple/
 
 
root@kali:~/OneForAll# python3.8 oneforall.py --target 网站域名 run
 
 运行完后在result的目录下会生成搜集好的信息
 
 拉到window桌面用excel打开，还是那句，猛如老狗