Oracle批量绑定FORALL字句的增强功能

最新推荐文章于 2023-06-09 19:49:32 发布

decaizhang

最新推荐文章于 2023-06-09 19:49:32 发布

阅读量461

点赞数

分类专栏： SQL Oracle 文章标签： oracle insert null date archive integer

SQL 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

Oracle

6 篇文章 0 订阅

订阅专栏

技术概述

当 PL/SQL 应用程序需要处理索引表时，在不确定所有索引是否均位于该表上时，有时不能使用标准 FOR 循环来迭代值。Oracle 数据库 10g 通过集合中的非连续索引引入了对 FORALL 语法的支持。INDICES OF 子句允许将 FORALL 语法用于稀疏集合，而 VALUE OF 子句用于指向其他集合的索引集合。

INDICES OF 关键字可用在以编程方式验证记录集合并将无效记录从集合中删除的场合。这会产生一个有效元素的稀疏集合，随后可以将该稀疏集合批量插入到表中。通过使用INDICES OF 关键字，避免了丢失记录例外情况的发生。当要使用在另一数组中定义的行数时，可以使用 INDICES OF 子句。

当在 DML 操作中使用在另一个数组中定义的值时，可以使用 VALUES OF 子句。它将只处理行号与驱动数组中的行内容相匹配的行。请考虑这样一个情形，即必须将记录集合复制到一个或多个集合变量，这取决于某些决定可以或不可以复制一些记录的条件，然后插入表中。使用“VALUES OF”语法，结合其元素均为原始集合内选定记录的指针的指针数组，可以高效地完成此任务。

应用程序实例：

概述：该示例应用程序通过订单处理情景演示了 FORALL 语句中新子句的用法。当客户创建了购物车并结算钱款时，订单数据被存储到 ALL_ORDERS 表中。假设该应用程序需要分离出新建的记录，并将其发送去进行订单处理。此外，还需要将已处理的订单和时间非常久远的订单在单独的表中存档。在该应用程序中，所有订单都被存储在 ALL_ORDERS 表中。当运行filter_records.sql 时，它会将所有订单抓取到一个集合中，并进行进一步处理以分离新建订单和已履行的订单。此处使用的条件基于订单日期和订单状态。在满足该条件后，则将相应元素从集合中删除。这会产生一个稀疏集合，该集合用于将订单插入 NEW_ORDERS 表中。该部分代码演示了“INDICES OF”子句的使用。此外，还会创建一个指向“要存档”记录的索引值的集合，然后，使用该集合将数据插入 ORDERS_ARCHIVE 表中。这演示了在使用 FORALL 语句时“VALUES OF”子句的使用。

所需的DML语句：（插入数据的inser语句在程序代码之后）

CREATE TABLE all_orders (

order_id NUMBER(12) PRIMARY KEY,

order_date DATE,

order_mode VARCHAR2(8),

customer_id NUMBER(6),

order_status NUMBER(2),

order_total NUMBER(8,2),

sales_rep_id NUMBER(6),

promotion_id NUMBER(6)

);

CREATE TABLE new_orders (

order_id NUMBER(12) PRIMARY KEY,

order_date DATE,

order_mode VARCHAR2(8),

customer_id NUMBER(6),

order_status NUMBER(2),

order_total NUMBER(8,2),

sales_rep_id NUMBER(6),

promotion_id NUMBER(6)

);

CREATE TABLE orders_archive (

order_id NUMBER(12) PRIMARY KEY,

order_date DATE,

order_mode VARCHAR2(8),

customer_id NUMBER(6),

order_status NUMBER(2),

order_total NUMBER(8,2),

sales_rep_id NUMBER(6),

promotion_id NUMBER(6)

);

代码：

DECLARE

--定义嵌套表类型orders_type表中元素为ALL_ORDERS表的记录类型，用来存放ALL_ORDERS表数据

TYPE orders_type IS TABLE OF all_orders%ROWTYPE;

-- 定义索引表orders_index_type，用来存放需要归档记录的索引

TYPE orders_index_type IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER ;

-- 定义程序中使用的各种变量

orders_archive orders_type;

new_order orders_type;

all_order orders_type;

orders_archive_index orders_index_type ;

order_status INTEGER;

new_orders_count INTEGER := 0;

old_orders_count INTEGER := 0;

BEGIN

-- 从 all_orders表中批量取出订单数据，绑定到all_order中

SELECT * BULK COLLECT INTO all_order FROM all_orders;

-- 将all_order赋值给new_order

new_order := all_order;

-- 循环记录集合，按照业务逻辑进行处理

FOR i IN all_order.FIRST .. all_order.LAST LOOP

order_status := all_order(i).order_status ;

--根据订单状态删除老订单记录.状态码为 '1'代表新订单

IF ( order_status != 1 ) THEN

new_order.DELETE(i);

ELSE

--统计新订单数量

new_orders_count := new_orders_count+1;

END IF;

-- 筛选出老订单以及已经处理过的订单

IF ( all_order(i).order_date < TO_DATE('01-JAN-2000','DD-MON-YYYY') AND (order_status = 2 OR order_status = 3 OR order_status = 10 ) ) THEN

-- 统计老订单和已经处理过的订单数量，并且将这些订单的索引存入索引表

orders_archive_index(old_orders_count) := i;

old_orders_count := old_orders_count+1;

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Inserting '|| TO_CHAR(new_orders_count) || ' new ORDERS into NEW_ORDERS table');

DBMS_OUTPUT.PUT_LINE(' ');

-- 从新订单集合中将数据插入new_orders表中

FORALL indx IN INDICES OF new_order

INSERT INTO new_orders VALUES all_order(indx);

DBMS_OUTPUT.PUT_LINE('Inserting ' ||TO_CHAR(old_orders_count) ||' old ORDERS data into ORDERS_ARCHIVE table');

--从归档订单记录索引集合中，通过需要归档记录索引，将数据插入orders_archive表中

FORALL indx IN VALUES OF orders_archive_index

INSERT INTO orders_archive VALUES all_order(indx);

commit;

END;

-- Insert orders data into the table