sql - 使用 SELECT 将数据插入按表 PL/SQL 的索引

Question

好吧，我有一个INDEX BY TABLE看起来像这样的：

TYPE dept_table_type IS TABLE OF departments.department_name%TYPE INDEX BY PLS_INTEGER;
my_dept_table dept_table_type;

有了这个循环，我想填充它：

FOR l IN 1..10
  LOOP
  deptno := deptno + 10;
  SELECT department_id, department_name INTO my_dept_table FROM departments WHERE department_id=deptno;
  END LOOP;

有没有可能的方法来做到这一点，因为我的解决方案不起作用......

谢谢指教

标签： sqloracleplsql

Answer 1

您可以将每个部门读入集合的适当元素：

DECLARE
  TYPE dept_table_type IS TABLE OF departments.department_name%TYPE
    INDEX BY PLS_INTEGER;

  my_dept_table dept_table_type;
  DEPTNO        NUMBER;
BEGIN
  FOR l IN 1..10
  LOOP
    deptno := l + 10;

    SELECT department_name
      INTO my_dept_table(l)
      FROM departments
      WHERE department_id = deptno;
  END LOOP;
  
  FOR I IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('MY_DEPT_TABLE(' || I || ') = ''' || MY_DEPT_TABLE(I) || '''');
  END LOOP;
END;

工作正常。

但更好的是，您可以BULK COLLECT将数据放入您的集合中，这样可以节省大量代码：

DECLARE
  TYPE dept_table_type IS TABLE OF departments.department_name%TYPE
    INDEX BY PLS_INTEGER;

  my_dept_table dept_table_type;
  DEPTNO        NUMBER;
BEGIN
  SELECT DEPARTMENT_NAME
    BULK COLLECT INTO MY_DEPT_TABLE
    FROM DEPARTMENTS;

  FOR I IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('MY_DEPT_TABLE(' || I || ') = ''' || MY_DEPT_TABLE(I) || '''');
  END LOOP;
END;

db<>在这里摆弄

编辑

OP 表示他希望将部门编号用作集合的索引。在这种情况下，我会使用游标并分别添加每个元素：

DECLARE
  TYPE dept_table_type IS TABLE OF departments.department_name%TYPE
    INDEX BY PLS_INTEGER;

  my_dept_table dept_table_type;
BEGIN
  FOR aRow IN (SELECT DEPARTMENT_ID, DEPARTMENT_NAME
                 FROM DEPARTMENTS
                 ORDER BY DEPARTMENT_ID)
  LOOP
    MY_DEPT_TABLE(aRow.DEPARTMENT_ID) := aRow.DEPARTMENT_NAME;
  END LOOP;
  
  FOR I IN MY_DEPT_TABLE.FIRST..MY_DEPT_TABLE.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('MY_DEPT_TABLE(' || I || ') = ''' || MY_DEPT_TABLE(I) || '''');
  END LOOP;
END;

db<>在这里摆弄