过程中的 PL/SQL 异常处理

发布于 2024-12-04 06:43:57 字数 839 浏览 0 评论 0原文

这是一个关于最佳实践的问题。我有一个与此类似的 PL/SQL 块。

DECLARE

    --work variables

    PROCEDURE p1(in_parameter1, out_parameter1, out_parameter2...) IS
    BEGIN    
        --do stuff
        --select ( ... ) into ( ... ) from t1 where ( ... )
    END;

    PROCEDURE p2(in_parameter1, out_parameter1, out_parameter2...) IS
    BEGIN 
        --do stuff
        --insert/update tables
        --do more stuff
    END;    

BEGIN -- MAIN PROCESS STARTS HERE

    open c1;
    fetch c1 into c1RowData;
    EXIT WHEN c1%NOTFOUND    
     --call procedure1
     --do stuff
     --call procedure2
     --do stuff
     --do stuff
     --call procedure1
     --call procedure2
END;
/
EXIT;

过程 p1 和 p2 中的语句可能会引发异常（NO_DATA_FOUND、DUP_VAL_ON_INDEX，...）。

您认为处理这种异常的最佳方法是什么？它们应该在程序内部处理还是您认为我应该用 TRY-CATCH 块包围主体中程序的每个调用？

原文

This is kind of a question about best practices. I have a PL/SQL block similar to this

DECLARE

    --work variables

    PROCEDURE p1(in_parameter1, out_parameter1, out_parameter2...) IS
    BEGIN    
        --do stuff
        --select ( ... ) into ( ... ) from t1 where ( ... )
    END;

    PROCEDURE p2(in_parameter1, out_parameter1, out_parameter2...) IS
    BEGIN 
        --do stuff
        --insert/update tables
        --do more stuff
    END;    

BEGIN -- MAIN PROCESS STARTS HERE

    open c1;
    fetch c1 into c1RowData;
    EXIT WHEN c1%NOTFOUND    
     --call procedure1
     --do stuff
     --call procedure2
     --do stuff
     --do stuff
     --call procedure1
     --call procedure2
END;
/
EXIT;

The statements in procedures p1 and p2 could possibly raise exceptions ( NO_DATA_FOUND, DUP_VAL_ON_INDEX, ... ).

What do you think is the best way to handle this exceptions? Should they be handled inside the procedures or do you think that I should surround every call the procedures in the main body with a TRY-CATCH block?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岁月打碎记忆 2024-12-11 06:43:57

就我个人而言，我会在他们被抛出的过程中抓住他们。这意味着您对外部处理方式有更多的控制权。例如，您可以将它们作为用户定义的异常再次抛出，您可以用有关到底出了什么问题的更多信息来修饰它们。

'Failed to find a matching row in table a for value b'

在过程之外比过程更具描述性

'no data found'

但这实际上取决于：

调用应用程序的错误报告要求
在过程中“执行操作”时实现的实际功能

例如，假设您想要运行过程 2，即使选择在过程 1 中未找到任何行。您需要捕获过程 1 中的异常并忽略它。如果没有，那么它会被抛出到过程 2 中的异常处理程序。

或者，假设您希望过程 1 在 select 没有找到任何内容的情况下插入一行，在这种情况下您需要捕获异常并在异常处理程序中执行插入。

现在，在有人攻击我之前，我不建议您使用异常处理程序来控制代码中的执行流程，这些示例是理论上的，但希望您能理解。

Personally I'd catch them inside the procedures they're thrown from. This means that you have much more control with regards to how they're handled externally. For example, you can throw them again as user defined exceptions which you can embellish with more information about exactly what went wrong.

'Failed to find a matching row in table a for value b'

is much more descriptive outside the procedure than

'no data found'

But this is realy dependent on:

The error reporting requirements of the calling application
The actual functionality implemented where you 'do stuff' within the procedures

For example, say you wanted to run procedure 2 even though the select in procedure 1 found no rows.. You'd need to catch the exception in procedure 1 and ignore it. If you didn't, then it'd be thrown to the exception handler in procedure 2.

Or, say you wanted procedure 1 to insert a row in the case where the select didn't find anything, in this case you'd need to catch the exception and perform the insert in the exception handler.

Now, before anyone jumps on me, I'm not recommending you use exception handlers for contrlling the flow of execution within your code, these examples are the theoretical, but hopefully you get the idea..

回复收藏 0 原文