ORA-00933:同时删除两个表时，SQL命令未正确结束 [英] ORA-00933: SQL command not properly ended when deleting two tables at the same time

问题描述

我正在尝试从两个相关的表中删除数据-表users中的主键和表login中的外键-但出现错误PL/SQL: ORA-00933: SQL command not properly ended.

I am trying to delete data from two related tables - primary key in the table users and foreign key in the table login - but I'm getting error PL/SQL: ORA-00933: SQL command not properly ended.

创建表users和主键:

/* table user*/
create table users (id_user number(10) not null, 
                    name_user varchar(30) not null);
/* primary key */
alter table users add constraint user_pk primary key (id_user);

创建表login以及主键和外键:

Create table login and primary key and foreign key:

/* table login*/
create table login (id_login number(10) not null, 
                    id_user_login number(10) not null, 
                    email_login varchar(20) not null,
                    password_login varchar(20) not null);
/* primary key */
alter table login add constraint login_pk primary key (id_login);

/* foreign key reference to user*/
alter table login add constraint login_fk_user foreign key (id_user_login)
references users(id_user) on delete cascade;

使用表users/login创建会话的过程，该过程有效:

Procedure to create session with table users/login, which works:

PROCEDURE create_user_session( p_name   IN VARCHAR2,
                               p_email  IN VARCHAR2,
                               p_pass   IN VARCHAR2,
                               p_error  OUT NUMBER,
                               p_msg_error OUT VARCHAR2)
  IS
  BEGIN
    p_error := 0;
    INSERT ALL

    INTO users (id_user, name_user) VALUES(seq_user.NEXTVAL,p_name)

    INTO login(id_login, id_user_login, email_login, pass_login)
    VALUES(seq_login.NEXTVAL, seq_user.CURRVAL, p_email, p_pass)

    SELECT * FROM DUAL COMMIT;
  EXCEPTION
  WHEN OTHERS THEN
    p_error     := 1;
    p_msg_error := 'Error!'||SQLERRM;
  END create_user_session;

现在，我想删除此会话，但是此过程出现错误PL/SQL: ORA-00933: SQL command not properly ended:

Now I want to delete this session, but I have error PL/SQL: ORA-00933: SQL command not properly ended from this procedure:

PROCEDURE delete_user_session(
    p_id_user IN NUMBER,
    p_error OUT NUMBER,
    p_msg_error OUT VARCHAR2)
IS
BEGIN
  p_error := 0;
  DELETE FROM users, login USING users
  INNER JOIN login WHERE users.id_user = p_id_user
  AND login.id_user_login  = p_id_user;
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  p_error     := 1;
  p_msg_error := 'Error!'||SQLERRM;
END delete_user_session;

我从SQL开发人员那里得到这张图片来显示错误(如果看不到该图片，请在DELETE FROM users, login ...中的s,上的红色花括号下划线):

I have this image from SQL developer to show the error (red squiggle underline on the s, in DELETE FROM users, login ... if you can't see the image):

我在做什么错了?

推荐答案

您不能在一条语句中从两个表中删除-没有等同于insert all的删除. (除非您具有限制级联删除操作或手动执行删除操作的触发器). 文档显示您的语法无效，因为没有路径可以访问指定多个表.

You can't delete from two tables in one statement - there is no delete equivalent of insert all. (Unless you have constraints that cascade the delete, or a trigger that does that manually). The documentation shows that your syntax is not valid, as there is no path to specify more than one table.

您将需要两个delete语句，首先从子表中删除记录:

You will need to have two delete statements, removing the records from the child table first:

  DELETE FROM login 
  WHERE login.id_user_login = p_id_user;
  DELETE FROM users
  WHERE users.id_user = p_id_user;

您可以将您的外键约束更改为delete cascade:

You could change your foreign key constraint to delete cascade:

alter table login add constraint login_fk_user foreign key (id_user_login)
references users(id_user) on delete cascade;

...这意味着您只需显式地从users表中删除；但这实际上并不是您想要的，因为它删除了一个级别的验证-您可能要防止如果有孩子的父键被意外删除.在这里发出两次删除并没有真正的伤害.

... which would mean you would only have to explicitly delete from the users table; but that may not actually be what you want, as it removes one level of validation - you may want to prevent a parent key being accidentally removed if it has children. Issuing two deletes doesn't really hurt here.

顺便说一句，您的第一个过程没有提交，这可能是您期望的.在这一行:

Incidentally, your first procedure is not committing, which you might be expecting. In this line:

    ...
    SELECT * FROM DUAL COMMIT;

... COMMIT被解释为DUAL表的别名，而不是单独的命令. DUAL之后需要分号，并且COMMIT;最好换行.但是通常认为，在过程中提交 not 更好，让顶级调用者决定是提交还是回滚以保持数据完整性.

... the COMMIT is interpreted as an alias for the DUAL table, not a separate command. You would need a semicolon after DUAL, and preferably a new line for the COMMIT;. But it's generally considered better not to commit in a procedure, and let the top-level caller decide whether to commit or roll back to preserve data integrity.

这篇关于ORA-00933:同时删除两个表时，SQL命令未正确结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！