Oracle对象类型与对象表的增删改查操作方法咨询

一、普通表 employee_info 的增删改查操作 #

1. 插入（INSERT） #

支持单条或批量插入数据：

-- 单条插入
INSERT INTO employee_info (empid, emp_name, department, designation, salary)
VALUES (1, '张三', '技术部', '软件工程师', 8000);

-- 批量插入多条数据
INSERT INTO employee_info (empid, emp_name, department, designation, salary)
SELECT 2, '李四', '市场部', '营销专员', 6500 FROM DUAL
UNION ALL
SELECT 3, '王五', '人事部', 'HR主管', 7500 FROM DUAL;

2. 查询（SELECT） #

基础查询、条件筛选，还可结合对象类型构造结果：

-- 查询所有员工信息
SELECT * FROM employee_info;

-- 筛选技术部员工的核心信息
SELECT empid, emp_name, salary
FROM employee_info
WHERE department = '技术部';

-- 将查询结果封装为 employee_info_obj 对象
SELECT employee_info_obj(empid, department, designation, salary) AS emp_object
FROM employee_info;

3. 更新（UPDATE） #

支持单条或批量更新数据：

-- 更新单个员工的薪资
UPDATE employee_info
SET salary = 8500
WHERE empid = 1;

-- 给市场部全体员工涨薪5%
UPDATE employee_info
SET salary = salary * 1.05
WHERE department = '市场部';

4. 删除（DELETE） #

可删除单条或符合条件的批量数据：

-- 删除指定ID的员工
DELETE FROM employee_info
WHERE empid = 3;

-- 删除薪资低于7000的员工
DELETE FROM employee_info
WHERE salary < 7000;

二、对象类型 employee_info_obj 及嵌套表类型 employee_info_obj_t 的操作 #

1. 对象构造与查询 #

-- 直接构造单个对象并查询
SELECT employee_info_obj(1, '技术部', '软件工程师', 8000) AS single_employee FROM DUAL;

-- 将普通表数据组装为嵌套表集合
SELECT CAST(COLLECT(employee_info_obj(empid, department, designation, salary)) AS employee_info_obj_t) AS employee_list
FROM employee_info;

2. 嵌套表的插入（需先创建含嵌套表的表） #

原普通表未包含嵌套表字段，需先创建对应存储表：

-- 创建带嵌套表字段的部门员工表
CREATE TABLE dept_employees (
  dept_name VARCHAR2(20),
  emp_list employee_info_obj_t
) NESTED TABLE emp_list STORE AS dept_emp_nested;

-- 插入嵌套表数据
INSERT INTO dept_employees (dept_name, emp_list)
VALUES (
  '技术部',
  employee_info_obj_t(
    employee_info_obj(1, '技术部', '软件工程师', 8000),
    employee_info_obj(4, '技术部', '测试工程师', 7200)
  )
);

3. 查询嵌套表中的数据 #

SELECT e.*
FROM dept_employees d, TABLE(d.emp_list) e
WHERE d.dept_name = '技术部';

4. 更新嵌套表中的数据 #

-- 更新嵌套表中指定员工的薪资
UPDATE TABLE(SELECT emp_list FROM dept_employees WHERE dept_name = '技术部')
SET salary = 8200
WHERE empid = 4;

5. 删除嵌套表中的数据 #

-- 删除嵌套表中指定员工
DELETE FROM TABLE(SELECT emp_list FROM dept_employees WHERE dept_name = '技术部')
WHERE empid = 4;

内容的提问来源于stack exchange，提问作者Muhammad Shoaib