今天我在做一个项目的时候,需要向数据库(数据库是Oracle 8.0.5)一次同时插入多条数据,但是程序运行却出错:ORA-00911: 无效字符。SQL语句如下:


DELETE FROM SYSROLEOPENR WHERE ROLEID = 2;
  INSERT INTO SYSROLEOPENR
    (PKID, ROLEID, OPERID)
    SELECT SYSROLEOPENR_PKID_SEQ.NEXTVAL, '2' ROLEID, OPERID
      FROM SYSMODULEOPER
     WHERE OPERCODE = '300090010';
  INSERT INTO SYSROLEOPENR
    (PKID, ROLEID, OPERID)
    SELECT SYSROLEOPENR_PKID_SEQ.NEXTVAL, '2' ROLEID, OPERID
      FROM SYSMODULEOPER
     WHERE OPERCODE = '300090020';
  INSERT INTO SYSROLEOPENR
    (PKID, ROLEID, OPERID)
    SELECT SYSROLEOPENR_PKID_SEQ.NEXTVAL, '2' ROLEID, OPERID
      FROM SYSMODULEOPER
     WHERE OPERCODE = '300090050';


我去掉所有的;号,也报错。困惑了许久。如果数据库是SQL SERVER 2000,程序去掉分号后是可以正常运行的。后来发现在Oracle中需要加上begin
end
才正确。改成以下的SQL就正确了:


BEGIN
  DELETE FROM SYSROLEOPENR WHERE ROLEID = 2;
  INSERT INTO SYSROLEOPENR
    (PKID, ROLEID, OPERID)
    SELECT SYSROLEOPENR_PKID_SEQ.NEXTVAL, '2' ROLEID, OPERID
      FROM SYSMODULEOPER
     WHERE OPERCODE = '300090010';
  INSERT INTO SYSROLEOPENR
    (PKID, ROLEID, OPERID)
    SELECT SYSROLEOPENR_PKID_SEQ.NEXTVAL, '2' ROLEID, OPERID
      FROM SYSMODULEOPER
     WHERE OPERCODE = '300090020';
  INSERT INTO SYSROLEOPENR
    (PKID, ROLEID, OPERID)
    SELECT SYSROLEOPENR_PKID_SEQ.NEXTVAL, '2' ROLEID, OPERID
      FROM SYSMODULEOPER
     WHERE OPERCODE = '300090050';

END;