ORACLE FORALL介绍
ORACLE FORALL介绍
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
[sql]
CREATE TABLE employees_temp AS SELECT * FROM employees;
[sql]
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）
[sql]
CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
[sql]
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结构：
[sql]
Execution Time (secs)
---------------------
FOR loop: 5.14
FORALL: .56
PL/SQL 过程已成功完成。
3、集合部分元素使用FORALL（Using FORALL with Part of a Collection）
[sql]
DROP TABLE employees_temp;
[sql]
CREATE TABLE employees_temp AS SELECT * FROM employees;
[sql]
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）
[sql]
-- 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;
[sql]
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;
/
[sql]
--- 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
[sql]
-- 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;
- 更多Oracle疑问解答：
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有，生产Oracle Rman 备份脚本的工具啊！
- 初学orcle，希望有大大帮忙解说一下详细步骤，从登录oracle到创建表的过程
- oracle语句问题：一张user表，三个字段，id,name,time，插入记录比如：张三2007，李四2008，张三2011
- 如何写一个ORACLE触发器同步两个表中的数据？
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G，在RMAN中如何按指定的时间恢复数据文件啊？
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊？
