创建一个嵌套表，仅在内部表中插入数据 [英] Create a nested table and insert data only in the inner table

问题描述

我有此表:employees(id，firstname，lastname，salary)，并添加了另一列，该列将存储每个雇员的所有过去薪水.

I have this table: employees(id, firstname, lastname, salary) and I added another column that will store all the past salaries of every employee.

CREATE OR REPLACE TYPE salary_list AS TABLE OF NUMBER;
/
ALTER TABLE employees
ADD (salary_history salary_list)
NESTED TABLE salary_history STORE AS salary_history_tab;

现在，该表是员工(id，名字，姓氏，薪水， salary_history )，而薪水_history为空.当我修改salaray值时，如何将当前薪水值插入到内部表salary_history中?我尝试过:

Now, the table is employees(id, firstname, lastname, salary, salary_history) and salary_history is null. When I modify the salaray value, how do I insert the current salary value into the inner table salary_history? I have tried:

INSERT INTO 
TABLE(SELECT salary_history FROM employees WHERE id=1) 
VALUES(1500);

然后出现以下错误:

ORA-22908:引用NULL表值

ORA-22908: reference to NULL table value

是的，因为我刚刚创建了嵌套表salary_history，所以它为null.我究竟做错了什么?将值(附加到现有数据中)插入 salary_history 以ID标识员工的正确方法是什么?

Yes, the nested table salary_history is null because I just created it. What am I doing wrong? What is the correct way to insert values(append to the existing data) into salary_history identifying the employee by id?

更新: 像这样添加了nvl(，()):

UPDATE: added nvl(,()) like so:

INSERT INTO 
TABLE(SELECT nvl(salary_history,salary_list()) FROM employees WHERE id=1) 
VALUES(1000);

我现在得到的错误是:

ORA-25015:无法在此嵌套表视图列上执行DML

ORA-25015: cannot perform DML on this nested table view column

推荐答案

您可以使用COALESCE( salary_history, salary_list() ) MULTISET UNION ALL salary_list( :your_new_value )将新值附加到旧列表中(如果不存在，则创建一个新列表).

You can use COALESCE( salary_history, salary_list() ) MULTISET UNION ALL salary_list( :your_new_value ) to append the new value to the old list (or create a new list if it does not exist).

Oracle 11g R2架构设置:

CREATE OR REPLACE TYPE salary_list AS TABLE OF NUMBER;
/

CREATE TABLE employees(
  id             NUMBER,
  salary_history salary_list
) NESTED TABLE salary_history STORE AS salary_history_tab
/

INSERT INTO employees VALUES ( 1, NULL )
/

查询1 :

UPDATE employees
SET   salary_history = COALESCE( salary_history, salary_list() )
                       MULTISET UNION ALL salary_list( 500 )
WHERE id = 1

SELECT * FROM employees

结果 :

Results:

| ID | SALARY_HISTORY |
|----|----------------|
|  1 |            500 |

查询2 :

UPDATE employees
SET   salary_history = COALESCE( salary_history, salary_list() )
                       MULTISET UNION ALL salary_list( 700 )
WHERE id = 1

SELECT * FROM employees

结果 :

Results:

| ID | SALARY_HISTORY |
|----|----------------|
|  1 |        500,700 |

查询3 :

UPDATE employees
SET   salary_history = COALESCE( salary_history, salary_list() )
                       MULTISET UNION ALL salary_list( 500 )
WHERE id = 1

SELECT * FROM employees

结果 :

Results:

| ID | SALARY_HISTORY |
|----|----------------|
|  1 |    500,700,500 |

这篇关于创建一个嵌套表，仅在内部表中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！