【Oracle】IS TABLE OF和BULK COLLECT INTO+FORALL

- 日理万妓 2023-06-21 13:27 1阅读 0赞

*  IS TABLE OF ：指定是一个集合的表的数组类型，简单的来说就是一个可以存储一列多行的数据类型。
 *  INDEX BY BINARY\_INTEGER：指索引组织类型。
 *  BULK COLLECT ：指是一个成批聚合类型，简单的来说 , 它可以存储一个多行多列存储类型，采用BULK COLLECT可以将查询结果一次性地加载到集合中。

【type】在SCOTT模式下，使用IS TABLE OF获取所有员工的姓名，职务，工资信息。

declare
      type type_ename is table of emp.ename%type;
      type type_job is table of emp.job%type;
      type type_sal is table of emp.sal%type;
      
      var_ename type_ename:=type_ename();
      var_job type_job:=type_job();
      var_sal type_sal:=type_sal();
    begin
      select ename,job,sal
      bulk collect into var_ename,var_job,var_sal
      from emp;
      
      /*输出雇员信息*/
      for v_index in var_ename.first .. var_ename.last loop
          dbms_output.put_line('雇员名称：'||var_ename(v_index)||' 职务：'||var_job(v_index)||' 工资：'||var_sal(v_index));
      end loop;
    end;

【rowtype】在SCOTT模式下，使用IS TABLE OF获取所有员工的所有信息。

declare
       type emp_table_type is table of emp%rowtype index by binary_integer;
       var_emp_table emp_table_type;
    begin
      select *
      bulk collect into var_emp_table
      from emp;
      
      /*输出雇员信息*/
      for i in 1..var_emp_table.COUNT loop
          dbms_output.put_line('雇员名称：'||var_emp_table(i).ename||' 职务：'||var_emp_table(i).job||' 工资：'||var_emp_table(i).sal);
      end loop;
    end;

--------------------

在Oracle开发中，有时我们会使用BULK COLLECT INTO批量操作大量的数据，如果没有LIMIT限制，则Oracle会读取查询的所有记录行到内存中，这样很容易造成PGA溢出而导致出现错误。所以使用LIMIT关键字可以避免PGA溢出。

SQL> DECLARE
      2    TYPE typ_emp IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
      3    v_emp typ_emp;
      4
      5    v_emp_id   tab_emp_id;
      6    v_total_id tab_emp_id := tab_emp_id();
      7
      8    v_limit NUMBER := 3;
      9
     10    v_test emp%ROWTYPE;
     11
     12    CURSOR cur_emp IS
     13      SELECT * FROM emp;
     14  BEGIN
     15    OPEN cur_emp;
     16
     17    LOOP
     18      FETCH cur_emp BULK COLLECT
     19        INTO v_emp LIMIT v_limit;
     20
     21      FORALL j IN v_emp.first .. v_emp.last
     22        INSERT INTO t_emp
     23        VALUES
     24          (seq_emp.nextval,
     25           v_emp(j).ename)
     26        RETURNING id BULK COLLECT INTO v_emp_id;
     27
     28      v_total_id := v_total_id MULTISET UNION ALL v_emp_id;
     29      EXIT WHEN v_emp.count < v_limit;
     30
     31    END LOOP;
     32
     33    CLOSE cur_emp;
     34
     35    FOR rec IN (SELECT *
     36                  FROM t_emp
     37                 WHERE id IN (SELECT column_value FROM TABLE(v_total_id)))
     38    LOOP
     39      dbms_output.put_line(rec.id || '---------->' || rec.name);
     40    END LOOP;
     41
     42    COMMIT;
     43
     44  END;
     45  /
    
    86---------->SMITH_甯
    87---------->ALLEN
    88---------->WARD
    89---------->JONES
    90---------->MARTIN
    91---------->BLAKE
    92---------->CLARK
    93---------->SCOTT
    94---------->KING
    95---------->TURNER
    96---------->ADAMS
    97---------->JAMES
    98---------->FORD
    99---------->MILLER
    
    PL/SQL procedure successfully completed.

参考：

> 《[Oracle中IS TABLE OF的使用][Oracle_IS TABLE OF]》  
> 《[Oracle BULK COLLECT INTO批量操作应用][Oracle BULK COLLECT INTO]》

[Oracle_IS TABLE OF]: https://www.cnblogs.com/xwb583312435/p/9056029.html
[Oracle BULK COLLECT INTO]: https://blog.csdn.net/Alen_Liu_SZ/article/details/75364861