如何从Oracle Table TYPE对象提取唯一记录并插入至另一表

没问题，我来帮你捋清楚怎么实现这个需求！

首先先提个小细节：你给出的typ_employee类型定义最后多了个逗号，执行时会报错，我先帮你修正一下（另外注意EMP_NAME定义成DATE类型应该是笔误吧？员工名字一般是字符串，我先改成VARCHAR2，如果确实是日期类型，你改回去就行）：

CREATE OR REPLACE TYPE typ_employee AS OBJECT (
    EMP_NAME VARCHAR2(100),
    EMP_DEPT NUMBER,
    EMP_SALARY NUMBER
);
/

接下来分两种常见场景给你对应的插入语句：

场景1：从嵌套表类型的变量/字段中筛选唯一记录插入 #

通常我们会用嵌套表来存储多个typ_employee对象，比如先定义嵌套表类型：

CREATE OR REPLACE TYPE typ_employee_tab AS TABLE OF typ_employee;
/

假设你已经有一个嵌套表变量v_emp_tab typ_employee_tab;（或者某张源表EMP_SOURCE里有个嵌套表字段EMP_LIST typ_employee_tab），目标表EMPLOYEE_TARGET的结构和typ_employee一致，那么可以用TABLE()函数把集合转成关系型数据集，再用DISTINCT去重插入：

从嵌套表变量取数据 #

INSERT INTO EMPLOYEE_TARGET (EMP_NAME, EMP_DEPT, EMP_SALARY)
SELECT DISTINCT emp.EMP_NAME, emp.EMP_DEPT, emp.EMP_SALARY
FROM TABLE(v_emp_tab) emp;

从表中的嵌套表字段取数据 #

INSERT INTO EMPLOYEE_TARGET (EMP_NAME, EMP_DEPT, EMP_SALARY)
SELECT DISTINCT emp.EMP_NAME, emp.EMP_DEPT, emp.EMP_SALARY
FROM EMP_SOURCE src, TABLE(src.EMP_LIST) emp;

场景2：从构造的typ_employee对象集合中去重插入 #

如果是通过查询直接构造typ_employee对象，再去重插入目标表，可以这么写：

INSERT INTO EMPLOYEE_TARGET (EMP_NAME, EMP_DEPT, EMP_SALARY)
SELECT DISTINCT obj.EMP_NAME, obj.EMP_DEPT, obj.EMP_SALARY
FROM (
    -- 这里替换成你生成typ_employee对象的查询逻辑
    SELECT typ_employee(emp_name, emp_dept, emp_salary) obj
    FROM SOME_SOURCE_TABLE
) t;

进阶：按指定字段去重（保留特定规则的记录） #

如果不是简单的全字段去重，而是要按EMP_NAME+EMP_DEPT去重，保留最高工资的记录，可以用分析函数实现：

INSERT INTO EMPLOYEE_TARGET (EMP_NAME, EMP_DEPT, EMP_SALARY)
SELECT EMP_NAME, EMP_DEPT, EMP_SALARY
FROM (
    SELECT 
        emp.EMP_NAME, emp.EMP_DEPT, emp.EMP_SALARY,
        -- 按姓名+部门分组，按工资倒序排，取每组第一条
        ROW_NUMBER() OVER (PARTITION BY emp.EMP_NAME, emp.EMP_DEPT ORDER BY emp.EMP_SALARY DESC) rn
    FROM TABLE(v_emp_tab) emp
)
WHERE rn = 1;

内容的提问来源于stack exchange，提问作者A Saraf