当我尝试执行我的代码时，我收到以下错误.SQL 错误: ORA-00917: 缺少逗号

Question

我在甲骨文中的查询一直说我有一个缺少逗号错误。我没有看到任何缺少逗号。请帮忙

INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID) VALUES 
(100,"Jessica, Janet, Robert, Jonathon","Davies, Smith, Clarkson, Campbell",2i,1),
(101,"Jessica, Janet, Robert, Jonathon","Davies, Smith, Clarkson, Campbell",6i,2),
(102,"Jessica, Janet, Robert, Jonathon","Davies, Smith, Clarkson, Campbell",10i,3),
(103,"Jessica, Janet, Robert, Jonathon","Davies, Smith, Clarkson, Campbell",14i,4),

Answer 1

Oracle

不允许使用值插入多行。 因此，请使用多个插入。

此外，Oracle 不支持字符串的双引号。 像 2i 这样的东西没有意义，除非它是一个字符串。 因此，以下内容似乎可以插入所需的行：

INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID) 
    VALUES (100, 'Jessica, Janet, Robert, Jonathon', 'Davies, Smith, Clarkson, Campbell', '2i', 1);
INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID) 
    VALUES (101, 'Jessica, Janet, Robert, Jonathon', 'Davies, Smith, Clarkson, Campbell', '6i',2);
INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID) 
    VALUES (102, 'Jessica, Janet, Robert, Jonathon', 'Davies, Smith, Clarkson, Campbell', '10i', 3);
INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID) 
    VALUES (103, 'Jessica, Janet, Robert, Jonathon', 'Davies, Smith, Clarkson, Campbell', '14i', 4);

我觉得你把列表放在一个字符串中并插入它们很奇怪，但那是另一回事。

Answer 2

您可以转换为带有联合所有子句的 select 语句，并将双引号替换为单引号，并将其他非引号的字母数字值转换为单引号：

INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID)
SELECT 100,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','2i' ,1  FROM dual UNION ALL
SELECT 101,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','6i' ,2  FROM dual UNION ALL
SELECT 102,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','10i',3  FROM dual UNION ALL
SELECT 103,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','14i',4  FROM dual;

或者使用INSERT ALL子句：

INSERT ALL 
  INTO TBLTEACHER VALUES(100,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','2i' ,1)
  INTO TBLTEACHER VALUES(101,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','6i' ,2)
  INTO TBLTEACHER VALUES(102,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','10i',3)
  INTO TBLTEACHER VALUES(103,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','14i',4)
SELECT 1 FROM dual;

附言如果 VALUES 子句中逗号分隔的组件数等于表的列数，则无需显式写入它们。

演示