如何用单条SQL INSERT语句插入子查询结果+固定教师数据？

问题背景 #

需要向PROFESORES表插入两行数据，要求：

• COD_CENTRO取自PERSONAL表中仅配备1名行政人员（FUNCION = 'ADMINISTRATIVO'）的学校，该子查询返回2个不同的学校代码
• 教师信息固定：DNI=8790055、APELLIDOS='Clara Salas'、ESPECIALIDAD='IDIOMAS'

原使用INSERT INTO...VALUES的语句触发ORA-01427: single-row subquery returns more than one row错误，因为该方式仅支持插入单行，而子查询返回多行。

单条SQL实现批量插入 #

可以使用INSERT ... SELECT语法替代INSERT ... VALUES，将子查询作为数据源，同时将固定教师信息与每个学校代码组合，一次性插入多行：

INSERT INTO PROFESORES (COD_CENTRO, DNI, APELLIDOS, ESPECIALIDAD)
SELECT COD_CENTRO, 8790055, 'Clara Salas', 'IDIOMAS'
FROM PERSONAL
WHERE FUNCION = 'ADMINISTRATIVO'
GROUP BY COD_CENTRO
HAVING COUNT(FUNCION) = 1;

该语句会将子查询返回的2个学校代码分别与固定教师信息拼接，生成两行数据插入表中，完全满足需求。

替代方案：分两次插入 #

如果需要分两次插入，可以通过限制子查询的行数来获取单个学校代码：

第一次插入第一个学校代码 #

INSERT INTO PROFESORES (COD_CENTRO, DNI, APELLIDOS, ESPECIALIDAD)
VALUES (
    (SELECT COD_CENTRO
     FROM PERSONAL
     WHERE FUNCION = 'ADMINISTRATIVO'
     GROUP BY COD_CENTRO
     HAVING COUNT(FUNCION) = 1
     FETCH FIRST 1 ROW ONLY),
    8790055, 'Clara Salas', 'IDIOMAS'
);

第二次插入第二个学校代码（排除已插入的） #

INSERT INTO PROFESORES (COD_CENTRO, DNI, APELLIDOS, ESPECIALIDAD)
VALUES (
    (SELECT COD_CENTRO
     FROM PERSONAL
     WHERE FUNCION = 'ADMINISTRATIVO'
     GROUP BY COD_CENTRO
     HAVING COUNT(FUNCION) = 1
     AND COD_CENTRO NOT IN (SELECT COD_CENTRO FROM PROFESORES WHERE DNI = 8790055)
     FETCH FIRST 1 ROW ONLY),
    8790055, 'Clara Salas', 'IDIOMAS'
);

也可以通过ROWNUM来指定获取第N个学校代码，比如：

-- 获取第1个学校代码
SELECT COD_CENTRO
FROM (
    SELECT COD_CENTRO
    FROM PERSONAL
    WHERE FUNCION = 'ADMINISTRATIVO'
    GROUP BY COD_CENTRO
    HAVING COUNT(FUNCION) = 1
)
WHERE ROWNUM = 1;

-- 获取第2个学校代码
SELECT COD_CENTRO
FROM (
    SELECT COD_CENTRO, ROWNUM AS rn
    FROM PERSONAL
    WHERE FUNCION = 'ADMINISTRATIVO'
    GROUP BY COD_CENTRO
    HAVING COUNT(FUNCION) = 1
)
WHERE rn = 2;

说明 #

此前尝试的“先插入学校代码再补充教师信息”思路不可行，因为DNI是主键，插入时不能为空，必须同时提供主键值和其他字段内容。

内容的提问来源于stack exchange，提问作者Álvaro Terrasa