PLSQL中使用TYPE实现表间数据插入报错：表或视图不存在

问题描述 #

编写PLSQL程序，意图通过自定义TYPE类型将EMP表的数据批量插入到EMP_NEW表，但执行时提示“表或视图不存在”错误。EMP与EMP_NEW表结构一致，原代码及EMP表创建语句如下：

原PLSQL代码

set serveroutput on;
DECLARE
   CURSOR c
   IS
      SELECT EMP_ID,      
                     EMP_NAME,    
                     EMAIL,       
                     PHONE_NUMBER,
                     HIRE_DATE,   
                     JOB_ID,
                     Sal from emp;
 TYPE emp_type IS TABLE OF emp%ROWTYPE
   INDEX BY BINARY_INTEGER;

   emp_rec   emp_type;
BEGIN
   OPEN c;

   FETCH c
   BULK COLLECT INTO emp_rec;
   
   If c%Found Then 
   INSERT INTO EMP_NEW
              (EMP_ID,
               EMP_NAME,
               EMAIL,            
               PHONE_NUMBER,      
               HIRE_DATE,         
               JOB_ID,            
               SAL)
SELECT EMP_ID, EMP_NAME, EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID, SAL FROM emp_rec ;

   CLOSE c;
END IF;
end;
/

EMP表创建语句

CREATE TABLE EMP
(
    EMP_ID          NUMBER (6),
    EMP_NAME        VARCHAR2 (46 BYTE),
    EMAIL           VARCHAR2 (25 BYTE) NOT NULL,
    PHONE_NUMBER    VARCHAR2 (20 BYTE),
    HIRE_DATE       DATE NOT NULL,
    JOB_ID          VARCHAR2 (10 BYTE) NOT NULL,
    SAL             NUMBER (8, 2)
);

错误原因 #

报错核心在于SELECT ... FROM emp_rec这一行：emp_rec是PLSQL级别的关联数组（带INDEX BY BINARY_INTEGER的TABLE类型），SQL引擎无法识别这种仅存在于PLSQL上下文的集合类型，因此会抛出“表或视图不存在”的错误。

解决方法 #

方法一：使用FORALL批量插入（推荐，效率更高）

FORALL是PLSQL专门用于批量DML的语句，可直接操作PLSQL集合，避免SQL与PLSQL引擎的上下文切换，同时解决集合无法被SQL识别的问题：

set serveroutput on;
DECLARE
   CURSOR c
   IS
      SELECT EMP_ID,      
             EMP_NAME,    
             EMAIL,       
             PHONE_NUMBER,
             HIRE_DATE,   
             JOB_ID,
             Sal from emp;
   TYPE emp_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
   emp_rec   emp_type;
BEGIN
   OPEN c;
   FETCH c BULK COLLECT INTO emp_rec;
   CLOSE c;

   -- 检查集合是否有数据，避免空批量操作
   IF emp_rec.COUNT > 0 THEN
      FORALL i IN emp_rec.FIRST .. emp_rec.LAST
         INSERT INTO EMP_NEW VALUES emp_rec(i);
   END IF;
END;
/

方法二：将PLSQL集合转换为SQL可识别的嵌套表

若需在SQL语句中直接查询集合，需先在SQL级别定义类型，再将数据转换为嵌套表：

1. 先创建SQL级别的类型：

CREATE OR REPLACE TYPE emp_row_type AS OBJECT (
    EMP_ID          NUMBER (6),
    EMP_NAME        VARCHAR2 (46 BYTE),
    EMAIL           VARCHAR2 (25 BYTE),
    PHONE_NUMBER    VARCHAR2 (20 BYTE),
    HIRE_DATE       DATE,
    JOB_ID          VARCHAR2 (10 BYTE),
    SAL             NUMBER (8, 2)
);
/
CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_row_type;
/

2. 修改PLSQL程序：

set serveroutput on;
DECLARE
   CURSOR c
   IS
      SELECT emp_row_type(EMP_ID, EMP_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, Sal) 
      from emp;
   emp_rec   emp_table_type;
BEGIN
   OPEN c;
   FETCH c BULK COLLECT INTO emp_rec;
   CLOSE c;

   IF emp_rec.COUNT > 0 THEN
      INSERT INTO EMP_NEW
      SELECT t.* FROM TABLE(emp_rec) t;
   END IF;
END;
/

额外注意事项 #

需确认EMP_NEW表已正确创建（结构与EMP一致），且当前执行用户拥有EMP_NEW表的INSERT权限。

内容的提问来源于stack exchange，提问作者deepika kumaran