在Oracle数据库中插入嵌套表
问题描述:
我是PL/SQL数据库的新学习者,这是一种给apex.oracle.com上的数据库应用指定序列的练习。然后我创建了表格,但是当填充与插入的代码如下所示,应用赋予错误,表你不介意,我需要你的帮助在Oracle数据库中插入嵌套表
由于事先有如下代码中定义
CREATE TYPE TEMPORAL_VARCHAR AS OBJECT (
VALID_TIME_LOWER_BOUND DATE,
VALID_TIME_UPPER_BOUND DATE,
VALUE_PART VARCHAR2(50)
);
CREATE TYPE TEMPORAL_NUMBER AS OBJECT (
VALID_TIME_LOWER_BOUND DATE,
VALID_TIME_UPPER_BOUND DATE,
VALUE_PART NUMBER);
与时间相关的attributeshave;
CREATE TYPE NAME_TYPE AS TABLE OF TEMPORAL_VARCHAR;
CREATE TYPE ADDRESS_TYPE AS TABLE OF TEMPORAL_VARCHAR;
CREATE TYPE DEPARTMENT_TYPE AS TABLE OF TEMPORAL_VARCHAR;
CREATE TYPE MANAGER_TYPE AS TABLE OF TEMPORAL_VARCHAR;
CREATE TYPE SALARY_TYPE AS TABLE OF TEMPORAL_NUMBER;
CREATE TABLE EMPLOYEE (
SSN NUMBER primary key,
NAME NAME_TYPE,
ADDRESS ADDRESS_TYPE ,
BIRTH_DATE DATE,
MANAGER MANAGER_TYPE ,
DEPARTMENT DEPARTMENT_TYPE,
SALARY SALARY_TYPE
)
NESTED TABLE NAME STORE AS NAME_TABLE,
NESTED TABLE ADDRESS STORE AS ADDRESS_TABLE,
NESTED TABLE MANAGER STORE AS MANAGER_TABLE,
NESTED TABLE DEPARTMENT STORE AS DEPARTMENT_TABLE,
NESTED TABLE SALARY STORE AS SALARY_TABLE
;
而且,我inteded插入做
INSERT INTO EMPLOYEE VALUES
(101,
NAME(TEMPORAL_VARCHAR('23.11.2005','12.31.9999','James Brown')),
ADDRESS(TEMPORAL_VARCHAR('23.11.2005','12.31.9999','BUCA, IZMIR')),
'23.10.1986',
MANAGER(TEMPORAL_VARCHAR('23.11.2005','12.31.9999','Mike White')),
DEPARTMENT(TEMPORAL_VARCHAR('23.11.2005','12.31.9999','DEPT_ID05')),
SALARY(TEMPORAL_NUMBER('23.11.2005',’12.31.9999’, 250000))
);
我收到错误消息是:
ORA-00904: “工资”:无效的标识符
答
_
此处有空格
CREATE TYPE MANAGER _TYPE AS TABLE OF TEMPORAL_VARCHAR;
CREATE TYPE SALARY _TYPE AS TABLE OF TEMPORAL_NUMBER;
DEPARTMENT DEPARTMENT _TYPE
一个逗号DEPARTMENT _Type
+0
什么插入代码? – user3708524
+0
是的,我已经创建了表格,但是当涉及到插入时,我遇到了错误 – user3708524
答
后失踪尝试此解决方案:
'alter session set NLS_DATE_FORMAT='DD.MM.YYYY';'
----------------------------------------------------
INSERT INTO EMPLOYEE VALUES
(101,
NAME_TYPE(TEMPORAL_VARCHAR('23.10.1986','09.09.9999','James Brown')),
ADDRESS_TYPE(TEMPORAL_VARCHAR('15.12.2009','09.09.9999','BUCA')),
'23.10.1986',
MANAGER_TYPE(TEMPORAL_VARCHAR('24.05.2008','09.09.9999','Mike White')),
DEPARTMENT_TYPE(TEMPORAL_VARCHAR('03.01.2012','09.09.9999','DEPT_ID05')),
SALARY_TYPE(TEMPORAL_NUMBER('01.01.2003','09.09.9999', 3200))
);
请包括确切的错误信息 –