oracle - 无效的 user.table.column、table.column 或列规范
问题描述
CREATE TABLE emp (
empno NUMBER (10) NOT NULL,
empname VARCHAR2 (50) NOT NULL,
mgr VARCHAR2 (10),
hiredate DATE,
sal NUMBER (10),
comm VARCHAR2 (10),
CONSTRAINT emp_pk PRIMARY KEY (empno)
) ;
DESC emp ;
INSERT ALL
INTO emp
(100, 'MARK', 'CXS', hire_date('1999-09-08', 'yyyy/mm/dd'), 8000, 'axw')
(200, 'peter', 'NULL', hire_date('1996-01-08', 'yyyy/mm/dd'), 9000)
(300, 'karl', 'NULL', hire_date('1995-05-08', 'yyyy/mm/dd'), 5000, 'AZQ')
(400, 'MAx', 'NULL', hire_date('1994-04-08', 'yyyy/mm/dd'), 10000, 'DES')
(500, 'Maggie', 'SAQ', hire_date('1998-06-08', 'yyyy/mm/dd'), 20000, 'QAS')
SELECT * FROM dual ;
解决方案
您的insert all
语法格式错误,您需要多into ... values ...
对。目前它正试图解释100
为列名,因此您会看到特定的错误。
您也有,hire_date(...)
而不是to_date(...)
,并且您缺少其中一个comm
值;假设应该为空:
INSERT ALL
INTO emp (empno, empname, mgr, hiredate, sal, comm)
VALUES (100, 'MARK', 'CXS', TO_DATE('1999-09-08', 'yyyy-mm-dd'), 8000, 'axw')
INTO emp (empno, empname, mgr, hiredate, sal, comm)
VALUES (200, 'peter', NULL, TO_DATE('1996-01-08', 'yyyy-mm-dd'), 9000, null)
INTO emp (empno, empname, mgr, hiredate, sal, comm)
VALUES (300, 'karl', NULL, TO_DATE('1995-05-08', 'yyyy-mm-dd'), 5000, 'AZQ')
INTO emp (empno, empname, mgr, hiredate, sal, comm)
VALUES (400, 'MAx', NULL, TO_DATE('1994-04-08', 'yyyy-mm-dd'), 10000, 'DES')
INTO emp (empno, empname, mgr, hiredate, sal, comm)
VALUES (500, 'Maggie', 'SAQ', TO_DATE('1998-06-08', 'yyyy-mm-dd'), 20000, 'QAS')
SELECT * FROM dual ;
5 rows inserted.
我还将搅拌文字更改'NULL'
为纯空值,因为这似乎更可靠,这就是您真正想要的。
而且我已将日期格式掩码更改为使用-
而不是/
, 以匹配值。但是,如果您使用这样的固定值,则使用日期文字会更简单,例如,而不是
INTO emp (empno, empname, mgr, hiredate, sal, comm)
VALUES (100, 'MARK', 'CXS', TO_DATE('1999-09-08', 'yyyy-mm-dd'), 8000, 'axw')
利用:
INTO emp (empno, empname, mgr, hiredate, sal, comm)
VALUES (100, 'MARK', 'CXS', DATE '1999-09-08', 8000, 'axw')
推荐阅读
- go - Go pkg/mod vs pkg/windowsamd_64 on go build
- c# - 有什么方法可以在没有空格的情况下将字符串保存在 SQL Server 数据库中?
- google-cloud-platform - GCP AI Platform custom prediction routine fails downloading nltk resources
- hex - Understanding a few iTerm2 default keycode mappings
- amazon-web-services - Unable to set variable in AWS pgsql
- java - how to store file bytes in memory for files bigger than 2^32 bytes size
- dialog - X++ 对话框字段查找覆盖错误 DialogControl.Control 无法从服务器调用
- javascript - Currently trying to use my MobileNet in a web application, nothing seems to work
- css - Vuetify v-btn 水平向右
- python - 为什么'withColumn'在pyspark中花费这么长时间?