logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

BULK COLLECT INTO

功能描述

BULK COLLECT子句用于指定结果集到集合的这种聚合。该子句可与 SELECT INTO、FETCH INTO 和 EXECUTE IMMEDIATE 命令一起使用，也可与 DELETE、INSERT 和 UPDATE 命令的 RETURNING INTO 子句一起使用。

注意事项

该功能仅在数据库兼容模式为Oracle时能够使用（即创建DB时DBCOMPATIBILITY='A'），在其他数据库兼容模式下不能使用该特性。

SELECT BULK COLLECT

语法格式

SELECT select_expressions BULK COLLECT INTO collection  [, ...] FROM ...;

注意事项

  • 如果指定了单个集合，则 collection 可以是单个字段的集合，也可以是某一记录类型的集合。如果指定了多个集合，则每个 collection 必须由单个字段组成。
  • select_expressions 必须在数字、顺序和类型兼容性方面与目标集合中的所有字段相匹配。

示例

1、创建并切换至兼容模式为Oracle的数据库db_oracle。

CREATE DATABASE db_oracle dbcompatibility='A';    
\c db_oracle

2、创建测试表。

create table EMP
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(4)
);

3、插入数据。

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

4、打开serveroutput参数。（使信息从存储过程传输回应用程序，输出在屏幕上。）

set serveroutput on;

5、bulk collect 子句与select into使用，多个单一字段，多个集合。

DECLARE
    TYPE empno_tbl    IS TABLE OF emp.empno%TYPE    INDEX BY BINARY_INTEGER;
    TYPE ename_tbl    IS TABLE OF emp.ename%TYPE    INDEX BY BINARY_INTEGER;
    TYPE job_tbl      IS TABLE OF emp.job%TYPE      INDEX BY BINARY_INTEGER;
    TYPE hiredate_tbl IS TABLE OF emp.hiredate%TYPE INDEX BY BINARY_INTEGER;
    TYPE sal_tbl      IS TABLE OF emp.sal%TYPE      INDEX BY BINARY_INTEGER;
    TYPE comm_tbl     IS TABLE OF emp.comm%TYPE     INDEX BY BINARY_INTEGER;
    TYPE deptno_tbl   IS TABLE OF emp.deptno%TYPE   INDEX BY BINARY_INTEGER;
    t_empno           EMPNO_TBL;
    t_ename           ENAME_TBL;
    t_job             JOB_TBL;
    t_hiredate        HIREDATE_TBL;
    t_sal             SAL_TBL;
    t_comm            COMM_TBL;
    t_deptno          DEPTNO_TBL;
BEGIN
    SELECT empno, ename, job, hiredate, sal, comm, deptno BULK COLLECT
       INTO t_empno, t_ename, t_job, t_hiredate, t_sal, t_comm, t_deptno
       FROM emp;
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME    JOB        HIREDATE    ' ||
        'SAL        ' || 'COMM      DEPTNO');
    DBMS_OUTPUT.PUT_LINE('-----  -------  ---------  ---------   ' ||
        '--------   ' || '--------  ------');
    FOR i IN 1..t_empno.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(t_empno(i) || '   ' ||
            RPAD(t_ename(i),8) || ' ' ||
            RPAD(t_job(i),10) || ' ' ||
            TO_CHAR(t_hiredate(i),'DD-MON-YY') || ' ' ||
            TO_CHAR(t_sal(i),'99,999.99') || ' ' ||
            TO_CHAR(NVL(t_comm(i),0),'99,999.99') || '  ' ||
            t_deptno(i));
    END LOOP;
END;
/

返回结果为：

EMPNO  ENAME    JOB        HIREDATE    SAL        COMM      DEPTNO
-----  -------  ---------  ---------   --------   --------  ------
7369   SMITH    CLERK      17-DEC-80     800          20
7499   ALLEN    SALESMAN   20-FEB-81   1,600     300  30
7521   WARD     SALESMAN   22-FEB-81   1,250     500  30
7566   JONES    MANAGER    02-APR-81   2,975          20
7654   MARTIN   SALESMAN   28-SEP-81   1,250   1,400  30
7698   BLAKE    MANAGER    01-MAY-81   2,850          30
7782   CLARK    MANAGER    09-JUN-81   2,450          10
7839   KING     PRESIDENT  17-NOV-81   5,000          10
7844   TURNER   SALESMAN   08-SEP-81   1,500          30
7900   JAMES    CLERK      03-DEC-81     950          30
7902   FORD     ANALYST    03-DEC-81   3,000          20
7934   MILLER   CLERK      23-JAN-82   1,300          10
ANONYMOUS BLOCK EXECUTE

FETCH BULK COLLECT

语法格式

FETCH name BULK COLLECT INTO collection [, ...] [ LIMIT n ];

注意事项

  • FETCH BULK COLLECT 不是一次从结果集返回一行，而是将结果集中的所有行一次性返回到指定的集合中，除非由 LIMIT 子句进行了限制。
  • 如果指定了单个集合，则 collection 可以是单个字段的集合，也可以是某一记录类型的集合。如果指定了多个集合，则每个 collection 必须由单个字段组成。
  • 游标（由 name 标识）的 SELECT 列表中的表达式必须在数字、顺序和类型兼容性方面与目标集合中的所有字段相匹配。
  • 如果指定了 LIMIT n，则每个 FETCH 返回到集合中的行数将不会超过 n。

示例：bulk collect 子句与fetch into使用，用limit限制输出5条数据。

前三个步骤与SELECT BULK COLLECT示例相同，同样采用前面示例创建的测试表EMP。

DECLARE
    TYPE emp_tbl IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
    t_emp           EMP_TBL;
    CURSOR emp_cur IS SELECT * FROM emp;
BEGIN
    OPEN emp_cur;
    FETCH emp_cur BULK COLLECT INTO t_emp limit 5;
    CLOSE emp_cur;
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME    JOB        HIREDATE    ' ||
        'SAL        ' || 'COMM      DEPTNO');
    DBMS_OUTPUT.PUT_LINE('-----  -------  ---------  ---------   ' ||
        '--------   ' || '--------  ------');
    FOR i IN 1..t_emp.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(t_emp(i).empno || '   ' ||
            RPAD(t_emp(i).ename,8) || ' ' ||
            RPAD(t_emp(i).job,10) || ' ' ||
            TO_CHAR(t_emp(i).hiredate,'DD-MON-YY') || ' ' ||
            TO_CHAR(t_emp(i).sal,'99,999.99') || ' ' ||
            TO_CHAR(NVL(t_emp(i).comm,0),'99,999.99') || '  ' ||
            t_emp(i).deptno);
    END LOOP;
END;
/

返回结果为：

EMPNO  ENAME    JOB        HIREDATE    SAL        COMM      DEPTNO
-----  -------  ---------  ---------   --------   --------  ------
7369   SMITH    CLERK      17-DEC-80     800          20
7499   ALLEN    SALESMAN   20-FEB-81   1,600     300  30
7521   WARD     SALESMAN   22-FEB-81   1,250     500  30
7566   JONES    MANAGER    02-APR-81   2,975          20
7654   MARTIN   SALESMAN   28-SEP-81   1,250   1,400  30
ANONYMOUS BLOCK EXECUTE

EXECUTE IMMEDIATE BULK COLLECT

语法格式

EXECUTE IMMEDIATE 'sql_expression;'
BULK COLLECT INTO collection [,...]
[USING {[bind_type] bind_argument} [, ...]}];

参数说明

  • collection 指定集合的名称。

  • bind_type 指定 bind_argument 的参数模式:

    • bind_type 为 IN 时指定 bind_argument 包含传递给 sql_expression 的值。
    • bind_type 为 OUT 时指定 bind_argument 从 sql_expression 接收值。
    • bind_type 为 IN OUT 时指定将 bind_argument 传递给 sql_expression，然后存储 sql_expression 返回的值。

注意事项

  • bind_argument 指定一个参数，该参数包含一个值，该值或传递给 sql_expression（bind_type 为 IN 时指定），或从 sql_expression 接收值（bind_type 为 OUT 时指定），或者执行这两项操作（bind_type 为 INOUT 时指定）。
  • 如果指定了单个集合，则 collection 可以是单个字段的集合，也可以是某一记录类型的集合；如果指定了多个集合，则每个 collection 必须由单个字段组成。

示例：bulk collect 子句与execute immediate使用。

前三个步骤与SELECT BULK COLLECT示例相同，同样采用前面示例创建的测试表EMP。

DECLARE
    TYPE emp_tbl IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
    t_emp           EMP_TBL;
BEGIN
    EXECUTE IMMEDIATE 'SELECT * FROM emp where deptno=:1' BULK COLLECT INTO t_emp USING IN 20;
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME    JOB        HIREDATE    ' ||
        'SAL        ' || 'COMM      DEPTNO');
    DBMS_OUTPUT.PUT_LINE('-----  -------  ---------  ---------   ' ||
        '--------   ' || '--------  ------');
    FOR i IN 1..t_emp.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(t_emp(i).empno || '   ' ||
            RPAD(t_emp(i).ename,8) || ' ' ||
            RPAD(t_emp(i).job,10) || ' ' ||
            TO_CHAR(t_emp(i).hiredate,'DD-MON-YY') || ' ' ||
            TO_CHAR(t_emp(i).sal,'99,999.99') || ' ' ||
            TO_CHAR(NVL(t_emp(i).comm,0),'99,999.99') || '  ' ||
            t_emp(i).deptno);
    END LOOP;
END;
/

返回结果为：

EMPNO  ENAME    JOB        HIREDATE    SAL        COMM      DEPTNO
-----  -------  ---------  ---------   --------   --------  ------
7369   SMITH    CLERK      17-DEC-80     800          20
7566   JONES    MANAGER    02-APR-81   2,975          20
7902   FORD     ANALYST    03-DEC-81   3,000          20
ANONYMOUS BLOCK EXECUTE

RETURNING BULK COLLECT

语法格式

{ insert | update | delete }
RETURNING { * | expr_1 [, expr_2 ] ...}
BULK COLLECT INTO collection [, ...];

insert、update 和 delete 分别是 INSERT、UPDATE 和 DELETE 命令。

注意事项

  • 如果指定了单个集合，则 collection 可以是单个字段的集合，也可以是某一记录类型的集合。如果指定了多个集合，则每个 collection 必须由单个字段组成。

  • RETURNING 关键字后面的表达式必须在数字、顺序和类型兼容性方面与目标集合中的所有字段相匹配。

示例

前置步骤与SELECT BULK COLLECT示例相同，同样采用前面示例创建的测试表EMP。

1、bulk collect 子句与update returning into使用。

DECLARE
  TYPE empno_tbl IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
  TYPE ename_tbl IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
  TYPE sal_tbl  IS TABLE OF emp.sal%TYPE  INDEX BY BINARY_INTEGER;
  t_empno     EMPNO_TBL;
  t_ename     ENAME_TBL;
  t_sal      SAL_TBL;
BEGIN
  UPDATE emp SET sal = sal * 1.5 RETURNING empno, ename, sal
  BULK COLLECT INTO t_empno, t_ename, t_sal;
  DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME    SAL     ');
  DBMS_OUTPUT.PUT_LINE('-----  -------   --------  ');
  FOR i IN 1..t_empno.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE(t_empno(i) || '  ' || RPAD(t_ename(i),8) ||
  ' ' || TO_CHAR(t_sal(i),'99,999.99'));
  END LOOP;
END;
/

返回结果如下：

EMPNO  ENAME      SAL
-----  -------    --------
7369   SMITH      1,200
7499   ALLEN      2,400
7521   WARD       1,875
7566   JONES      4,462.5
7654   MARTIN     1,875 
7698   BLAKE      4,275
7782   CLARK      3,675
7839   KING       7,500
7844   TURNER     2,250
7900   JAMES      1,425
7902   FORD       4,500
7934   MILLER     1,950
ANONYMOUS BLOCK EXECUTE

2、bulk collect 子句与insert into returning into使用。

DECLARE
    TYPE empno_tbl IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
    TYPE ename_tbl IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
    TYPE sal_tbl   IS TABLE OF emp.sal%TYPE   INDEX BY BINARY_INTEGER;
    t_empno         EMPNO_TBL;
    t_ename         ENAME_TBL;
    t_sal           SAL_TBL;
BEGIN
 INSERT INTO EMP VALUES (5623,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20) RETURNING empno, ename, sal
        BULK COLLECT INTO t_empno, t_ename, t_sal;
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME      SAL        ');
	DBMS_OUTPUT.PUT_LINE('-----  -------    --------   ');
    FOR i IN 1..t_empno.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(t_empno(i) || '   ' || RPAD(t_ename(i),8) ||
            ' ' || TO_CHAR(t_sal(i),'99,999.99'));
    END LOOP;
END;
/

返回结果为：

EMPNO  ENAME      SAL
-----  -------    --------
5623   SMITH        800