假设我们有通常的Oracle员工和部门表。
我还有一些Oracle对象类型:
表Employees.
的对象表
现在,我想在一个查询中批量收集多个部门及其员工。
我知道如何在没有员工的情况下大量收集部门:
DECLARE
departmentList TDepartmentList;
BEGIN
SELECT TDepartment(dept.id, dept.name)
BULK COLLECT INTO departmentList
FROM departments dept
WHERE <some condition>;
END;但是,如何在同一查询中收集选定部门的员工?
因此,就像
DECLARE
departmentList TDepartmentList;
BEGIN
SELECT TDepartment(dept.id, dept.name,
SELECT TEmployee(emp.id, emp.name)
FROM Employees
WHERE emp.deptId = dept.id
)
BULK COLLECT INTO departmentList
FROM departments dept
WHERE <some condition>;
END;发布于 2019-11-06 13:38:21
好吧,我找到了:
DECLARE
departmentList TDepartmentList;
BEGIN
SELECT TDepartment(dept.id, dept.name,
CAST(MULTISET(
SELECT TEmployee(emp.id, emp.name)
FROM Employees
WHERE emp.deptId = dept.id
) AS TEmployeeList))
)
BULK COLLECT INTO departmentList
FROM departments dept
WHERE <some condition>;
END;因此,CAST(MULTISET( query ) AS TEmployeeList)是我添加的。
https://stackoverflow.com/questions/58730915
复制相似问题