存储过程以输出参数返回布尔值的该写法是否合法有效？

Absolutely, this is a valid approach to use an output parameter to return a success boolean status, while throwing exceptions to signal execution failures. Let’s break down why this works and some key points to keep in mind:

How This Implementation Meets Your Requirements #

Your procedure follows the intended logic perfectly:

• Success case: When both INSERT statements run without errors and the COMMIT completes, the RESULT output parameter is explicitly set to TRUE. This guarantees the caller only gets a TRUE value when all operations fully succeed.
• Error case: If any exception pops up during execution, the procedure triggers the EXCEPTION block:
  1. It rolls back all uncommitted changes with ROLLBACK to keep data consistent.
  2. After running your custom error handling (//Do stuff;), it re-raises the exception with RAISE, passing the error up to the caller. This means the caller will never get a misleading RESULT value (like an uninitialized NULL) when something goes wrong.

Best Practices & Small Improvements #

While the core logic is solid, here are a few tweaks to make it more robust:

• Avoid over-reliance on WHEN OTHERS: Catching every exception with WHEN OTHERS can hide unexpected issues. Whenever possible, add specific handlers for known error types (e.g., DUP_VAL_ON_INDEX for unique constraint violations) before the WHEN OTHERS block. This lets you handle different errors appropriately while still catching unforeseen issues as a fallback.
• Initialize the output parameter (optional): You could set RESULT := FALSE at the start of the procedure. This makes it clearer that a FALSE value (instead of NULL) would indicate an incomplete execution—though since you re-raise exceptions, the caller will likely never encounter this uninitialized state.
• Make sure the caller handles exceptions: Any code invoking this procedure needs an EXCEPTION block to catch the raised errors. Otherwise, the exception will bubble up to the application layer and might cause unhandled crashes.

Your Formatted Procedure Code #

PROCEDURE STUFF (VAL1 IN NUMBER, VAL2 IN NUMBER, RESULT OUT BOOLEAN) IS
BEGIN
    INSERT INTO TABLE_1 (A_COLUMN) VALUES (VAL1);
    INSERT INTO TABLE_2 (B_COLUMN) VALUES (VAL2);
    COMMIT;
    RESULT := TRUE;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        -- Do stuff; (replace comment with actual error handling logic)
        RAISE;
END;

内容的提问来源于stack exchange，提问作者Popplar