在Pl/Sql中尝试像异常处理一样捕获 [英] Try Catch Like Exception Handling In Pl/Sql

问题描述

在一个过程中，我想执行逻辑单元1，不管它是否失败，都执行逻辑单元2

In a procedure, I want to do logic unit 1, doesn't matter if it fails, execute logic unit 2

这似乎是典型的try-catch方案.但是如何在pl/sql中执行呢?

this seems like a typical try-catch scenario. but how can I do it in pl/sql?

create or replace
PACKAGE BUILD_PKG
AS
PROCEDURE reset_seq_and_truncate_tbl(
    p_seq_name   IN VARCHAR2,
    p_table_name IN VARCHAR2 );
END BUILD_PKG;
/
create or replace
PACKAGE BODY  BUILD_PKG
AS
BEGIN
PROCEDURE reset_seq_and_truncate_tbl(
    p_seq_name   IN VARCHAR2,
    p_table_name IN VARCHAR2 )
 IS
   l_val NUMBER;
 BEGIN
   BEGIN
     EXECUTE immediate 'truncate table ' || p_table_name;
   EXCEPTION 
        WHEN OTHERS 
        THEN dbms_output.put_line(SQLCODE);
   END;

   BEGIN
     EXECUTE immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
   EXCEPTION 
        WHEN OTHERS 
        THEN dbms_output.put_line(SQLCODE);
   END; 
 END reset_seq_and_truncate_tbl;

END BUILD_PKG;

如您所见，第二个Exception块导致了该问题.

as you can see the second Exception block cause the problem.

推荐答案

在BEGIN..END块中包含第二个EXCEPTION.尝试这种方式

Enclose the second EXCEPTION in BEGIN..END block. Try this way

PROCEDURE reset_seq_and_truncate_tbl(
    p_seq_name   IN VARCHAR2,
    p_table_name IN VARCHAR2 )
 IS
   l_val NUMBER;
 BEGIN
   EXECUTE immediate 'truncate table ' || p_table_name;
 EXCEPTION 
      WHEN OTHERS 
      THEN
      BEGIN
        dbms_output.put_line(SQLCODE);
        EXECUTE immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
       --this would cause compilation error
       EXCEPTION 
            WHEN OTHERS 
            THEN dbms_output.put_line(SQLCODE);
     END; 
 END reset_seq_and_truncate_tbl;

根据评论进行更新

PROCEDURE reset_seq_and_truncate_tbl(
    p_seq_name   IN VARCHAR2,
    p_table_name IN VARCHAR2 )
 IS
   l_val NUMBER;
 BEGIN
   BEGIN
     EXECUTE immediate 'truncate table ' || p_table_name;
   EXCEPTION 
        WHEN OTHERS 
        THEN dbms_output.put_line(SQLCODE);
   END;

   BEGIN
     EXECUTE immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
   EXCEPTION 
        WHEN OTHERS 
        THEN dbms_output.put_line(SQLCODE);
   END; 
 END reset_seq_and_truncate_tbl;

这篇关于在Pl/Sql中尝试像异常处理一样捕获的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！