按结果存储Oracle /SQL中的变量组

Question

我可以使用下面的代码在变量类型为表的变量中存储行集。

DECLARE
    TYPE TABLE_A_ROW_TYPE IS TABLE OF TABLE_A%ROWTYPE ;
    TABLE_A_ROWS_LIST TABLE_A_ROW_TYPE ;
BEGIN
    SELECT * BULK COLLECT INTO TABLE_A_ROWS_LIST FROM TABLE_A
    WHERE ORG_MEMBER_NAME is NULL;
END;

我的要求是将GROUP BY子句的结果存储在变量中。我需要使用下面的查询来代替SELECT *：

SELECT ORG_NAME, COUNT(1) BULK COLLECT INTO TABLE_A_ROWS_LIST FROM TABLE_A
WHERE ORG_MEMBER_NAME is NULL GROUP BY ORG_NAME;

如何存储上述GROUP BY子句的结果导致变量？为了使TABLE_A_ROWS_LIST变量正常工作，我应该使用什么变量类型？

Answer 1

这里有一个选择：

SQL> DECLARE
  2     TYPE ttar IS RECORD
  3     (
  4        org_name   VARCHAR2 (10),
  5        cnt        NUMBER
  6     );
  7
  8     TYPE ttav IS VARRAY (10) OF ttar;
  9
 10     ttab  ttav;
 11  BEGIN
 12       SELECT d.dname, COUNT (*)
 13         BULK COLLECT INTO ttab
 14         FROM dept d JOIN emp e ON e.deptno = d.deptno
 15     GROUP BY d.dname;
 16
 17     FOR i IN ttab.FIRST .. ttab.LAST
 18     LOOP
 19        DBMS_OUTPUT.put_line (ttab (i).org_name || ': ' || ttab (i).cnt);
 20     END LOOP;
 21  END;
 22  /
ACCOUNTING: 3
RESEARCH: 5
SALES: 6

PL/SQL procedure successfully completed.

SQL>

或者，使用SQL级别声明的类型：

SQL> CREATE OR REPLACE TYPE ttar IS OBJECT (org_name VARCHAR2 (10), cnt NUMBER);
  2  /

Type created.

SQL> CREATE OR REPLACE TYPE ttabr IS TABLE OF ttar;
  2  /

Type created.

SQL> DECLARE
  2     ttab  ttabr;
  3  BEGIN
  4       SELECT ttar (d.dname, COUNT (*))
  5         BULK COLLECT INTO ttab
  6         FROM dept d JOIN emp e ON e.deptno = d.deptno
  7     GROUP BY d.dname;
  8
  9     FOR i IN ttab.FIRST .. ttab.LAST
 10     LOOP
 11        DBMS_OUTPUT.put_line (ttab (i).org_name || ': ' || ttab (i).cnt);
 12     END LOOP;
 13  END;
 14  /
ACCOUNTING: 3
RESEARCH: 5
SALES: 6

PL/SQL procedure successfully completed.

SQL>