FORALL方法批量更新数据简单实例

文 修改源：http://www.itpub.net/thread-1941544-1-1.html

----创建一张简单的数据表

点击(此处)折叠或打开

1. --drop table plch_employees;
   
2. CREATE TABLE plch_employees
   
3. (
   
4.    employee_id INTEGER
   
5. , last_name VARCHAR2 (100)
   
6. , salary NUMBER
   
7. )
   
8. /

----为数据表插入数据

点击(此处)折叠或打开

1. BEGIN
   
2.    INSERT INTO plch_employees
   
3.         VALUES (100, 'Jobs', 1000000);
   
4. 
   
5.    INSERT INTO plch_employees
   
6.         VALUES (200, 'Ellison', 1000000);
   
7. 
   
8.    INSERT INTO plch_employees
   
9.         VALUES (300, 'Gates', 1000000);
   
10. 
    
11.    COMMIT;
    
12. END;
    
13. /

---利用forall循环对数据更新

点击(此处)折叠或打开

1. DECLARE
   
2.    TYPE pls_integer_t IS TABLE OF PLS_INTEGER
   
3.                             INDEX BY PLS_INTEGER;
   
4. 
   
5.    l_ids pls_integer_t;
   
6.    l_indexes pls_integer_t;
   
7.    l_salary NUMBER;
   
8. BEGIN
   
9.    l_ids (1) := 100;
   
10.    l_ids (2) := 200;
    
11.    l_ids (3) := 300;
    
12.    --
    
13.    l_indexes (1) := 1;
    
14.    l_indexes (2) := 3;
    
15. 
    
16.    FORALL indx IN VALUES OF l_indexes
    
17.       UPDATE plch_employees
    
18.          SET salary = salary * 2
    
19.        WHERE employee_id = l_ids (indx);
    
20. 
    
21.    SELECT SUM (salary) INTO l_salary FROM plch_employees;
    
22.    DBMS_OUTPUT.put_line (l_salary);
    
23. END;
    
24. /

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30260000/viewspace-1819687/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30260000/viewspace-1819687/