当要在 Oracle 中之心批量 INSERT、UPDATE 和 DELETE 操作时,可以使用 FORALL 语句。比for loop效率高

==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

[sql] view plain copy
  1. CREATE TABLE employees_temp AS SELECT * FROM employees;  
[sql] view plain copy
  1. DECLARE  
  2.    TYPE NumList IS VARRAY(20) OF NUMBER;  
  3.    depts NumList := NumList(10, 30, 70);  -- department numbers  
  4. BEGIN  
  5.    FORALL i IN depts.FIRST..depts.LAST  
  6.       DELETE FROM employees_temp WHERE department_id = depts(i);  
  7.    COMMIT;  
  8. END;  
  9. /  

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

[sql] view plain copy
  1. CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));  
  2. CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));  
[sql] view plain copy
  1. DECLARE  
  2.   TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;  
  3.   TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;  
  4.   pnums  NumTab;  
  5.   pnames NameTab;  
  6.   iterations CONSTANT PLS_INTEGER := 50000;  
  7.   t1 INTEGER;  
  8.   t2 INTEGER;  
  9.   t3 INTEGER;  
  10. BEGIN  
  11.   FOR j IN 1..iterations LOOP  -- load index-by tables  
  12.      pnums(j) := j;  
  13.      pnames(j) := 'Part No. ' || TO_CHAR(j);  
  14.   END LOOP;  
  15.   t1 := DBMS_UTILITY.get_time;  
  16.   FOR i IN 1..iterations LOOP  -- use FOR loop  
  17.      INSERT INTO parts1 VALUES (pnums(i), pnames(i));  
  18.   END LOOP;  
  19.   t2 := DBMS_UTILITY.get_time;  
  20.   FORALL i IN 1..iterations  -- use FORALL statement  
  21.      INSERT INTO parts2 VALUES (pnums(i), pnames(i));  
  22.   t3 := DBMS_UTILITY.get_time;  
  23.   DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');  
  24.   DBMS_OUTPUT.PUT_LINE('---------------------');  
  25.   DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((t2 - t1)/100));  
  26.   DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)/100));  
  27.   COMMIT;  
  28. END;  
  29. /  

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

[sql] view plain copy
  1. Execution Time (secs)  
  2. ---------------------  
  3. FOR loop: 5.14  
  4. FORALL:   .56  
  5.   
  6. PL/SQL 过程已成功完成。  

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

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

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

[sql] view plain copy
  1. -- Create empty tables to hold order details  
  2. CREATE TABLE  valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));  
  3. CREATE TABLE  big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;  
  4. CREATE TABLE  rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;  

[sql] view plain copy
  1. DECLARE  
  2. -- Make collections to hold a set of customer names and order amounts.  
  3.    SUBTYPE cust_name IS valid_orders.cust_name%TYPE;  
  4.    TYPE cust_typ IS TABLe OF cust_name;  
  5.    cust_tab cust_typ;  
  6.    SUBTYPE order_amount IS valid_orders.amount%TYPE;  
  7.    TYPE amount_typ IS TABLE OF NUMBER;  
  8.    amount_tab amount_typ;  
  9. -- Make other collections to point into the CUST_TAB collection.  
  10.    TYPE index_pointer_t IS TABLE OF PLS_INTEGER;  
  11.    big_order_tab index_pointer_t := index_pointer_t();  
  12.    rejected_order_tab index_pointer_t := index_pointer_t();  
  13.    PROCEDURE setup_data IS BEGIN  
  14.  -- Set up sample order data, including some invalid orders and some 'big' orders.  
  15.      cust_tab := cust_typ('Company1','Company2','Company3','Company4','Company5');  
  16.      amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);  
  17.    END;  
  18.      
  19. BEGIN  
  20.    setup_data(); --initialization  
  21.    DBMS_OUTPUT.PUT_LINE('--- Original order data ---');  
  22.    FOR i IN 1..cust_tab.LAST LOOP  
  23.      DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||  
  24.                            amount_tab(i));  
  25.    END LOOP;  
  26. -- Delete invalid orders (where amount is null or 0).  
  27.    FOR i IN 1..cust_tab.LAST LOOP  
  28.      IF amount_tab(i) is null or amount_tab(i) = 0 THEN  
  29.         cust_tab.delete(i);  
  30.         amount_tab.delete(i);  
  31.      END IF;  
  32.    END LOOP;  
  33.    DBMS_OUTPUT.PUT_LINE('--- Data with invalid orders deleted ---');  
  34.    FOR i IN 1..cust_tab.LAST LOOP  
  35.      IF cust_tab.EXISTS(i) THEN  
  36.        DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||  
  37.                              amount_tab(i));  
  38.       END IF;  
  39.    END LOOP;  
  40. -- Because the subscripts of the collections are not consecutive, use  
  41. -- FORALL...INDICES OF to iterate through the actual subscripts,   
  42. -- rather than 1..COUNT  
  43.    FORALL i IN INDICES OF cust_tab  
  44.      INSERT INTO valid_orders(cust_name, amount)   
  45.         VALUES(cust_tab(i), amount_tab(i));  
  46.      
  47. -- Now process the order data differently  
  48. -- Extract 2 subsets and store each subset in a different table  
  49.    setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.  
  50.    FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP  
  51.      IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN  
  52.        rejected_order_tab.EXTEND; -- Add a new element to this collection  
  53. -- Record the subscript from the original collection  
  54.        rejected_order_tab(rejected_order_tab.LAST) := i;   
  55.      END IF;  
  56.      IF amount_tab(i) > 2000 THEN  
  57.         big_order_tab.EXTEND; -- Add a new element to this collection  
  58. -- Record the subscript from the original collection  
  59.         big_order_tab(big_order_tab.LAST) := i;  
  60.      END IF;  
  61.    END LOOP;  
  62. -- Now it's easy to run one DML statement on one subset of elements,   
  63. -- and another DML statement on a different subset.  
  64.    FORALL i IN VALUES OF rejected_order_tab  
  65.      INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));  
  66.    FORALL i IN VALUES OF big_order_tab  
  67.      INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));  
  68.    COMMIT;  
  69. END;  
  70. /  

[sql] view plain copy
  1. --- Original order data ---  
  2. Customer #1, Company1: $5000.01  
  3. Customer #2, Company2: $0  
  4. Customer #3, Company3: $150.25  
  5. Customer #4, Company4: $4000  
  6. Customer #5, Company5: $  
  7. --- Data with invalid orders deleted ---  
  8. Customer #1, Company1: $5000.01  
  9. Customer #3, Company3: $150.25  
  10. Customer #4, Company4: $4000  
  11.    
  12. PL/SQL procedure successfully completed  

[sql] view plain copy
  1. -- Verify that the correct order details were stored  
  2. SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;  
  3. SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;  
  4. SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;  


5、使用%BULK_ROWCOUNT返回受影响的记录行数

[sql] view plain copy
  1. CREATE TABLE emp_temp AS SELECT * FROM employees;  
  2. DECLARE  
  3.    TYPE NumList IS TABLE OF NUMBER;  
  4.    depts NumList := NumList(30, 50, 60);  
  5. BEGIN  
  6.    FORALL j IN depts.FIRST..depts.LAST  
  7.       DELETE FROM emp_temp WHERE department_id = depts(j);  
  8. -- How many rows were affected by each DELETE statement?  
  9.    FOR i IN depts.FIRST..depts.LAST  
  10.    LOOP  
  11.       DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted ' ||  
  12.          SQL%BULK_ROWCOUNT(i) || ' rows.');  
  13.    END LOOP;  
  14. END;  
  15. /  



6、FORALL与BULK COLLECT 一起使用(Using FORALL With BULK COLLECT)

[sql] view plain copy
  1. CREATE TABLE emp_temp AS SELECT * FROM employees;  
  2. DECLARE  
  3.    TYPE NumList IS TABLE OF NUMBER;  
  4.    depts NumList := NumList(10,20,30);  
  5.    TYPE enum_t IS TABLE OF employees.employee_id%TYPE;  
  6.    TYPE dept_t IS TABLE OF employees.department_id%TYPE;  
  7.    e_ids enum_t;  
  8.    d_ids dept_t;  
  9. BEGIN  
  10.   FORALL j IN depts.FIRST..depts.LAST  
  11.     DELETE FROM emp_temp WHERE department_id = depts(j)  
  12.        RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids;  
  13.   DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');  
  14.   FOR i IN e_ids.FIRST .. e_ids.LAST  
  15.   LOOP  
  16.     DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));  
  17.   END LOOP;  
  18. END;  


  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值