Oracle数据库之FORALL与BULK COLLECT语句

简介: Oracle数据库之FORALL与BULK COLLECT语句    我们再来看一下PL/SQL块的执行过程:当PL/SQL运行时引擎处理一块代码时,它使用PL/SQL引擎来执行过程化的代码,而将SQL语句发送给SQL引擎来执行;SQL引擎执行完毕后,将结果再返回给PL/SQL引擎。

 Oracle数据库之FORALL与BULK COLLECT语句

   我们再来看一下PL/SQL块的执行过程:当PL/SQL运行时引擎处理一块代码时,它使用PL/SQL引擎来执行过程化的代码,而将SQL语句发送给SQL引擎来执行;SQL引擎执行完毕后,将结果再返回给PL/SQL引擎。这种在PL/SQL引擎和SQL引擎之间的交互,称为上下文交换(context switch)。每发生一次交换,就会带来一定的额外开销。

switch

 

  1. FORALL,用于增强PL/SQL引擎到SQL引擎的交换。
  2. BULK COLLECT,用于增强SQL引擎到PL/SQL引擎的交换。(前面我们已经介绍过了)

1. FORALL介绍

使用FORALL,可以将多个DML批量发送给SQL引擎来执行,最大限度地减少上下文交互所带来的开销。下面是 FORALL 的一个示意图:

forall

语法:

 

1 FORALL index_name IN 
2     { lower_bound .. upper_bound
3     | INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ]
4     | VALUES OF index_collection
5     }
6  [ SAVE EXCEPTIONS ] dml_statement;

 

 

说明:

index_name:一个无需声明的标识符,作为集合下标使用。

lower_bound .. upper_bound:数字表达式,来指定一组连续有效的索引数字下限和上限。该表达式只需解析一次。

INDICES OF collection_name:用于指向稀疏数组的实际下标。跳过没有赋值的元素,例如被 DELETE 的元素,NULL 也算值。

VALUES OF index_collection_name:把该集合中的值当作下标,且该集合值的类型只能是 PLS_INTEGER/BINARY_INTEGER。

SAVE EXCEPTIONS:可选关键字,表示即使一些DML语句失败,直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。

dml_statement:静态语句,例如:UPDATE或者DELETE;或者动态(EXECUTE IMMEDIATE)DML语句。

2. FORALL的使用

示例所使用表结构:

1 CREATE TABLE tmp_tab(
2   id NUMBER(5),
3   name VARCHAR2(50)
4 );

 

示例1,使用FORALL批量插入、修改、删除数据:

 1 --批量插入
 2 DECLARE
 3   -- 定义索引表类型
 4   TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY  BINARY_INTEGER;
 5   tb_table tb_table_type;
 6 BEGIN
 7   FOR i IN 1..100 LOOP
 8     tb_table(i).id:=i;
 9     tb_table(i).name:='NAME'||i;
10   END LOOP;
11 
12   FORALL i IN 1..tb_table.count
13     INSERT INTO  tmp_tab VALUES tb_table(i);
14 END;

 

 

 1 --批量修改
 2 DECLARE
 3   TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
 4   tb_table tb_table_type;
 5 BEGIN
 6   FOR i IN 1..100 LOOP
 7     tb_table(i).id:=i;
 8     tb_table(i).name:='MY_NAME_'||i;
 9   END LOOP;
10   FORALL i IN 1..tb_table.count
11     UPDATE tmp_tab t SET row = tb_table(i) WHERE t.id =tb_table(i).id;
12 END;

 

--批量删除
 DECLARE
  TYPE tb_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
  tb_table tb_table_type;
 BEGIN
   FOR i IN 1..10 LOOP
    tb_table(i).id:=i;
    tb_table(i).name:='MY_NAME_'||i;
   END LOOP;
   FORALL i IN 1..tb_table.count
    DELETE FROM tmp_tab WHERE id =tb_table(i).id;
 END;

 

示例2,使用INDICES OF子句:

 1 DECLARE
 2   TYPE demo_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
 3   demo_table demo_table_type;
 4 BEGIN
 5   FOR i IN 1..10 LOOP
 6     demo_table(i).id:=i;
 7     demo_table(i).name:='NAME'||i;
 8   END LOOP;
 9   -- 使用集合的delete方法移除第3、6、9三个成员
10   demo_table.delete(3);
11   demo_table.delete(6);
12   demo_table.delete(9);
13   FORALL i IN INDICES OF  demo_table
14     INSERT INTO tmp_tab VALUES demo_table(i);
15 END ;

示例3,使用VALUES OF子句:

 1 DECLARE
 2 TYPE index_poniter_type IS TABLE OF pls_integer;
 3   index_poniter index_poniter_type;
 4   TYPE demo_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
 5   demo_table demo_table_type;
 6 BEGIN 
 7   index_poniter := index_poniter_type(1,3,5,7);
 8   FOR i IN 1..10 LOOP 
 9     demo_table(i).id:=i;
10     demo_table(i).name:='NAME'||i;
11   END LOOP;
12   FORALL i IN VALUES OF index_poniter
13     INSERT INTO  tmp_tab VALUES demo_table(i);
14 END;

 

3. FORALL注意事项

使用FORALL时,应该遵循如下规则:

  1. FORALL语句的执行体,必须是一个单独的DML语句,比如INSERT,UPDATE或DELETE。
  2. 不要显式定义index_row,它被PL/SQL引擎隐式定义为PLS_INTEGER类型,并且它的作用域也仅仅是FORALL。
  3. 这个DML语句必须与一个集合的元素相关,并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。
  4. lower_bound和upper_bound之间是按照步进 1 来递增的。
  5. 在sql_statement中,不能单独地引用集合中的元素,只能批量地使用集合。
  6. 在sql_statement中使用的集合,下标不能使用表达式。

4. BULK COLLECT介绍

BULK COLLECT子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。

通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。下面逐一描述BULK COLLECT在这几种情形下的用法。

5. BULK COLLECT的使用

5.1 在SELECT INTO中使用BULK COLLECT

示例:

 1 DECLARE
 2    -- 定义记录类型
 3    TYPE emp_rec_type IS RECORD            
 4    (  
 5       empno      emp.empno%TYPE,
 6       ename      emp.ename%TYPE, 
 7       hiredate   emp.hiredate%TYPE  
 8    );  
 9    -- 定义基于记录的嵌套表
10    TYPE nested_emp_type IS TABLE OF emp_rec_type;  
11    -- 声明变量
12    emp_tab   nested_emp_type;  
13 BEGIN
14    -- 使用BULK COLLECT将所得的结果集一次性绑定到记录变量emp_tab中  
15    SELECT empno, ename, hiredate  
16    BULK   COLLECT INTO emp_tab       
17    FROM   emp;  
18 
19    FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP  
20       DBMS_OUTPUT.PUT_LINE('当前记录: '
21                 ||emp_tab(i).empno||chr(9)
22                 ||emp_tab(i).ename||chr(9)
23                 ||emp_tab(i).hiredate);  
24    END LOOP;
25 END;  

说明:使用BULK COLLECT一次即可提取所有行并绑定到记录变量,这就是所谓的批量绑定。

5.2 在FETCH INTO中使用BULK COLLECT

在游标中可以使用BLUK COLLECT一次取出一个数据集合,比用游标单条取数据效率高,尤其是在网络不大好的情况下。

语法:

FETCH ... BULK COLLECT INTO ...[LIMIT row_number];

在使用BULK COLLECT子句时,对于集合类型会自动对其进行初始化以及扩展。因此如果使用BULK COLLECT子句操作集合,则无需对集合进行初始化以及扩展。由于BULK COLLECT的批量特性,如果数据量较大,而集合在此时又自动扩展,为避免过大的数据集造成性能下降,因此可以使用LIMIT子句来限制一次提取的数据量。LIMIT子句只允许出现在FETCH操作语句的批量中。

示例:

 1 DECLARE  
 2    CURSOR emp_cur IS  
 3       SELECT empno, ename, hiredate FROM emp;  
 4 
 5    TYPE emp_rec_type IS RECORD  
 6    (  
 7       empno      emp.empno%TYPE,
 8       ename      emp.ename%TYPE ,
 9       hiredate   emp.hiredate%TYPE  
10    );  
11    -- 定义基于记录的嵌套表  
12    TYPE nested_emp_type IS TABLE OF emp_rec_type;
13    -- 声明集合变量 
14    emp_tab     nested_emp_type;
15    -- 定义了一个变量来作为limit的值  
16    v_limit     PLS_INTEGER := 5;
17    -- 定义变量来记录FETCH次数
18    v_counter   PLS_INTEGER := 0;  
19 BEGIN  
20    OPEN emp_cur; 
21 
22    LOOP 
23       -- fetch时使用了BULK COLLECT子句  
24       FETCH emp_cur  
25       BULK   COLLECT INTO emp_tab         
26       LIMIT v_limit; -- 使用limit子句限制提取数据量  
27 
28       EXIT WHEN emp_tab.COUNT = 0; -- 注意此时游标退出使用了emp_tab.COUNT,而不是emp_cur%notfound  
29       v_counter   := v_counter + 1;  -- 记录使用LIMIT之后fetch的次数  
30 
31       FOR i IN emp_tab.FIRST .. emp_tab.LAST  
32       LOOP  
33          DBMS_OUTPUT.PUT_LINE( '当前记录: '
34                     ||emp_tab(i).empno||CHR(9)
35                     ||emp_tab(i).ename||CHR(9)
36                     ||emp_tab(i).hiredate);  
37       END LOOP;  
38    END LOOP;  
39 
40    CLOSE emp_cur;  
41 
42    DBMS_OUTPUT.put_line( '总共获取次数为:' || v_counter );  
43 END;  

5.3 在RETURNING INTO中使用BULK COLLECT

BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用。当与这几个DML语句结合时,需要使用RETURNING子句来实现批量绑定。

示例:

 1 DECLARE  
 2    TYPE emp_rec_type IS RECORD  
 3    (  
 4       empno      emp.empno%TYPE, 
 5       ename      emp.ename%TYPE, 
 6       hiredate   emp.hiredate%TYPE  
 7    );   
 8    TYPE nested_emp_type IS TABLE OF emp_rec_type;  
 9    emp_tab   nested_emp_type;
10 BEGIN
11    DELETE FROM emp WHERE deptno = 20  
12    RETURNING empno, ename, hiredate   -- 使用returning 返回这几个列  
13    BULK   COLLECT INTO emp_tab;       -- 将返回的列的数据批量插入到集合变量    
14 
15    DBMS_OUTPUT.put_line( '删除 ' || SQL%ROWCOUNT || ' 行记录' );  
16    COMMIT;  
17 
18    IF emp_tab.COUNT > 0 THEN   -- 当集合变量不为空时,输出所有被删除的元素  
19       FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP  
20          DBMS_OUTPUT.PUT_LINE('当前记录:'  
21                     || emp_tab( i ).empno || CHR( 9 )  
22                     || emp_tab( i ).ename || CHR( 9 )  
23                     || emp_tab( i ).hiredate  
24                     || ' 已被删除' );  
25       END LOOP;  
26    END IF;  
27 END;

6. BULK COLLECT的注意事项

  1. BULK COLLECT INTO 的目标对象必须是集合类型。
  2. 只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。
  3. 不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。
  4. 复合目标(如对象类型)不能在RETURNING INTO子句中使用。
  5. 如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO子句中使用。
  6. 如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO子句中。

    7. FORALL与BULK COLLECT综合运用

    FORALL与BULK COLLECT是实现批量SQL的两个重要方式,我们可以将其结合使用以提高性能。

    示例:

     1 -- 创建表tb_emp
     2 CREATE TABLE tb_emp AS  
     3    SELECT empno, ename, hiredate  
     4    FROM   emp  
     5    WHERE  1 = 0;  
     6 
     7 DECLARE
     8    -- 声明游标
     9    CURSOR emp_cur IS
    10       SELECT empno, ename, hiredate FROM emp;
    11    -- 基于游标的嵌套表类型
    12    TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE;
    13    -- 声明变量
    14    emp_tab   nested_emp_type; 
    15 BEGIN  
    16    SELECT empno, ename, hiredate  
    17    BULK   COLLECT INTO emp_tab
    18    FROM   emp  
    19    WHERE  sal > 1000;
    20 
    21    -- 使用FORALL语句将变量中的数据插入到表tb_emp  
    22    FORALL i IN 1 .. emp_tab.COUNT                     
    23       INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)  
    24       VALUES emp_tab( i );  
    25 
    26    COMMIT;  
    27    DBMS_OUTPUT.put_line('总共向 tb_emp 表中插入记录数: ' || emp_tab.COUNT);  
    28 END;

     

 

1. 什么是事务

在数据库中事务是工作的逻辑单元,一个事务是由一个或多个完成一组的相关行为的SQL语句组成,通过事务机制确保这一组SQL语句所作的操作要么都成功执行,完成整个工作单元操作,要么一个也不执行。

如:网上转帐就是典型的要用事务来处理,用以保证数据的一致性。

2. 事务特性

SQL92标准定义了数据库事务的四个特点:(面试时可能会问的)

  • 原子性(Atomicity):一个事务里面所有包含的SQL语句是一个执行整体,不可分割,要么都做,要么都不做。
  • 一致性(Consistency):事务开始时,数据库中的数据是一致的,事务结束时,数据库的数据也应该是一致的。
  • 隔离性(Isolation):是指数据库允许多个并发事务同时对其中的数据进行读写和修改的能力,隔离性可以防止事务的并发执行时,由于他们的操作命令交叉执行而导致的数据不一致状态。
  • 持久性 (Durability) : 是指当事务结束后,它对数据库中的影响是永久的,即便系统遇到故障的情况下,数据也不会丢失。

一组SQL语句操作要成为事务,数据库管理系统必须保证这组操作的原子性(Atomicity)、一致性(consistency)、隔离性(Isolation)和持久性(Durability),这就是ACID特性。

 

相关文章
|
3天前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版操作报错合集之采集oracle的时候报ORA-65040:不允许从可插入数据库内部执行该操作如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
20 3
|
6天前
|
SQL Oracle 安全
Oracle11g更改数据库名(详细教程)
Oracle11g更改数据库名(详细教程)
15 1
|
3天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之可以通过配置Oracle数据库的schema注册表来监测表结构的变化吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
12 1
|
6天前
|
Oracle 关系型数据库 数据库
Oracle数据库协议适配器错误解决方法
Oracle数据库协议适配器错误解决方法
12 2
|
6天前
|
存储 Oracle 关系型数据库
实验三 Oracle数据库的创建和管理
实验三 Oracle数据库的创建和管理
16 1
|
6天前
|
SQL Oracle 关系型数据库
实验一 安装和使用Oracle数据库
实验一 安装和使用Oracle数据库
16 1
|
6天前
|
Oracle Java 关系型数据库
【服务器】python通过JDBC连接到位于Linux远程服务器上的Oracle数据库
【服务器】python通过JDBC连接到位于Linux远程服务器上的Oracle数据库
17 6
|
SQL 关系型数据库 计算机视觉
Oracle bulk 示例
Oracle有2个引擎来执行PL/SQL blocks 和 subprograms。那么在执行的时候,PL/SQL 引擎把DML 语句发送给SQL 引擎,然后由SQL 引擎执行,执行完毕后,SQL 引擎把结果集在发送给PL/SQL 引擎。
802 0
|
6天前
|
DataWorks Oracle 关系型数据库
DataWorks操作报错合集之尝试从Oracle数据库同步数据到TDSQL的PG版本,并遇到了与RAW字段相关的语法错误,该怎么处理
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
35 0
|
6天前
|
SQL Oracle 关系型数据库
零基础入门 Oracle数据库:轻松上手
零基础入门 Oracle数据库:轻松上手
8 0