回滚@Oracle 上的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3490300/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rollback @ Stored Procedure on Oracle
提问by alex
I don't know if thats right but for some reason my stored procedure is not rolling back after an exception occurs. So my insert statement is commited even when i get an exception
我不知道这是否正确，但由于某种原因，我的存储过程在发生异常后没有回滚。所以即使我遇到异常，我的插入语句也会被提交
Did i forgot something?
我忘记了什么吗？
PROCEDURE SP_USUARIO_INSERT
(
pUSU_IDUSUARIO IN OUT ENG.USU_USUARIO.USU_IDUSUARIO%TYPE,
pUSU_CDUSUARIO IN ENG.USU_USUARIO.USU_CDUSUARIO%TYPE,
pPES_IDPESSOA IN ENG.USU_USUARIO.PES_IDPESSOA%TYPE,
pUSU_DLSENHA IN ENG.USU_USUARIO.USU_CDUSUARIO%TYPE,
pUSU_DLOBSERVACAO IN ENG.USU_USUARIO.USU_DLOBSERVACAO%TYPE,
pUSU_NUIP IN ENG.USU_USUARIO.USU_NUIP%TYPE,
pUSU_DTCADASTRO IN ENG.USU_USUARIO.USU_DTCADASTRO%TYPE,
pUSU_DTDESATIVACAO IN ENG.USU_USUARIO.USU_DTDESATIVACAO%TYPE,
pUSU_DTULTIMOACESSO IN ENG.USU_USUARIO.USU_DTULTIMOACESSO%TYPE,
pUSU_DLMAQUINA IN ENG.USU_USUARIO.USU_DLMAQUINA%TYPE,
pUSU_STNOVO IN ENG.USU_USUARIO.USU_STNOVO%TYPE,
pUSU_STATIVO IN ENG.USU_USUARIO.USU_STATIVO%TYPE
)
IS
sCreateUser Varchar(200);
bUsuarioExiste Number;
eUsuarioExiste Exception;
BEGIN
SELECT
COUNT(usu_cdusuario)
INTO bUsuarioExiste
FROM ENG.USU_USUARIO
WHERE USU_CDUSUARIO = pUSU_CDUSUARIO;
IF(bUsuarioExiste > 0) THEN
RAISE eUsuarioExiste;
END IF;
SELECT usu_seq.nextval INTO pUSU_IDUSUARIO FROM DUAL;
INSERT INTO ENG.USU_USUARIO
(
USU_IDUSUARIO,
USU_CDUSUARIO,
PES_IDPESSOA,
USU_DLOBSERVACAO,
USU_NUIP,
USU_DTCADASTRO,
USU_DTDESATIVACAO,
USU_DTULTIMOACESSO,
USU_DLMAQUINA,
USU_STNOVO,
USU_STATIVO
)
VALUES
(
pUSU_IDUSUARIO,
pUSU_CDUSUARIO,
pPES_IDPESSOA,
pUSU_DLOBSERVACAO,
pUSU_NUIP,
sysdate,
pUSU_DTDESATIVACAO,
pUSU_DTULTIMOACESSO,
pUSU_DLMAQUINA,
pUSU_STNOVO,
pUSU_STATIVO
) ;
sCreateUser := 'CREATE USER ' || pUSU_CDUSUARIO || ' IDENTIFIED BY ' || pUSU_DLSENHA;
EXECUTE IMMEDIATE sCreateUser;
EXECUTE IMMEDIATE 'GRANT ENG_GERAL TO ' || pUSU_CDUSUARIO;
COMMIT;
EXCEPTION
WHEN eUsuarioExiste THEN
RAISE_APPLICATION_ERROR (-20001, 'Usuário já existe ou possui nome inválido.');
ROLLBACK;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001, SQLCODE || ': ' || SQLERRM);
ROLLBACK;
END SP_USUARIO_INSERT;
采纳答案by Gary Myers
I suspect you are 'handling' (or more accurately, ignoring) your exception.
我怀疑您正在“处理”（或更准确地说，忽略）您的异常。
create table temp (id number);
DECLARE
v_str VARCHAR2(2);
BEGIN
INSERT INTO temp VALUES (1);
v_str := '123';
EXCEPTION
WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Whoops');
END;
/
select * from temp;
Will show the row because, as far as the SQL layer is concerned, the procedure completed successfully (as the exception was caught and ignored).
将显示该行，因为就 SQL 层而言，该过程成功完成（因为异常被捕获并被忽略）。
There can be other reasons such as
可能还有其他原因，例如
- The insert happens before the procedure execution and so isn't rolled back when the statement fails (and you don't explicitly rollback the transaction)
- The insert is committed by a explicit commit before the exception is raised.
- 插入发生在过程执行之前，因此在语句失败时不会回滚（并且您没有显式回滚事务）
- 在引发异常之前通过显式提交提交插入。
回答by dpbradley
The "EXECUTE IMMEDIATE sCreateUser;" is implicitly committing your insert.
“立即执行 sCreateUser;” 正在隐式提交您的插入。
回答by Adam Musch
I think if you restructure your code, you can get the behavior you want.
我认为如果你重组你的代码，你可以获得你想要的行为。
PROCEDURE SP_USUARIO_INSERT
(
pUSU_IDUSUARIO IN OUT ENG.USU_USUARIO.USU_IDUSUARIO%TYPE,
pUSU_CDUSUARIO IN ENG.USU_USUARIO.USU_CDUSUARIO%TYPE,
pPES_IDPESSOA IN ENG.USU_USUARIO.PES_IDPESSOA%TYPE,
pUSU_DLSENHA IN ENG.USU_USUARIO.USU_CDUSUARIO%TYPE,
pUSU_DLOBSERVACAO IN ENG.USU_USUARIO.USU_DLOBSERVACAO%TYPE,
pUSU_NUIP IN ENG.USU_USUARIO.USU_NUIP%TYPE,
pUSU_DTCADASTRO IN ENG.USU_USUARIO.USU_DTCADASTRO%TYPE,
pUSU_DTDESATIVACAO IN ENG.USU_USUARIO.USU_DTDESATIVACAO%TYPE,
pUSU_DTULTIMOACESSO IN ENG.USU_USUARIO.USU_DTULTIMOACESSO%TYPE,
pUSU_DLMAQUINA IN ENG.USU_USUARIO.USU_DLMAQUINA%TYPE,
pUSU_STNOVO IN ENG.USU_USUARIO.USU_STNOVO%TYPE,
pUSU_STATIVO IN ENG.USU_USUARIO.USU_STATIVO%TYPE
)
IS
sCreateUser Varchar(200);
bUsuarioExiste Number;
eUsuarioExiste Exception;
l_sqlcode number;
BEGIN
SELECT
COUNT(usu_cdusuario)
INTO bUsuarioExiste
FROM ENG.USU_USUARIO
WHERE USU_CDUSUARIO = pUSU_CDUSUARIO;
IF(bUsuarioExiste > 0) THEN
RAISE eUsuarioExiste;
END IF;
sCreateUser := 'CREATE USER ' || pUSU_CDUSUARIO || ' IDENTIFIED BY ' ||
pUSU_DLSENHA;
EXECUTE IMMEDIATE sCreateUser;
begin
EXECUTE IMMEDIATE 'GRANT ENG_GERAL TO ' || pUSU_CDUSUARIO;
-- moved this into the insert
--SELECT usu_seq.nextval INTO pUSU_IDUSUARIO FROM DUAL;
INSERT INTO ENG.USU_USUARIO
(
USU_IDUSUARIO,
USU_CDUSUARIO,
PES_IDPESSOA,
USU_DLOBSERVACAO,
USU_NUIP,
USU_DTCADASTRO,
USU_DTDESATIVACAO,
USU_DTULTIMOACESSO,
USU_DLMAQUINA,
USU_STNOVO,
USU_STATIVO
)
VALUES
(
usu_seq.nextval, --pUSU_IDUSUARIO,
pUSU_CDUSUARIO,
pPES_IDPESSOA,
pUSU_DLOBSERVACAO,
pUSU_NUIP,
sysdate,
pUSU_DTDESATIVACAO,
pUSU_DTULTIMOACESSO,
pUSU_DLMAQUINA,
pUSU_STNOVO,
pUSU_STATIVO
)
returning ;
exception
when others then
-- save off the sqlcode, you will need it to reraise
l_sqlcode := SQLCODE;
-- rollback any insert that may have run,
-- depending on where the exception was raised
rollback;
-- drop the created user
execute immediate 'drop user ' || pUSU_CDUSUARIO ;
-- reraise the error
RAISE_APPLICATION_ERROR (-20001, l_sqlcode || ': ' ||
SQLERRM(-l_sqlcode));
end;
COMMIT;
EXCEPTION
WHEN eUsuarioExiste THEN
RAISE_APPLICATION_ERROR (-20001,
'Usuário já existe ou possui nome inválido.');
ROLLBACK;
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001,
SQLCODE || ': ' || SQLERRM);
ROLLBACK;
END SP_USUARIO_INSERT;
回答by Alex Poole
Doesn't the RAISE_APPLICATION_ERRORmean that the ROLLBACKis unreachable? Unless the caller is also issuing a ROLLBACK, I think those need to be the other way around. You may be getting in implicit commit from DDL as other have said, depending on what's actually erroring, so it might be irrelevant, but it doesn't look quite right.
这不RAISE_APPLICATION_ERROR意味着ROLLBACK无法访问吗？除非调用者也发出ROLLBACK，否则我认为这些需要相反。正如其他人所说，您可能会从 DDL 获得隐式提交，具体取决于实际出错的内容，因此它可能无关紧要，但看起来不太正确。
