我应该何时嵌套PL/SQL BEGIN…

I've been somewhat haphazardly grouping subsections of code in BEGIN...END blocks when it seems right. Mostly when I'm working on a longer stored procedure and there's a need for a temporary variable in one spot I'll declare it just for that portion of the code. I also do this when I want to identify and handle exceptions thrown for a specific piece of code.

我在BEGIN中对代码的子部分进行了一些随意的分组……当它看起来是正确的时候，结束的时候。大多数情况下，当我在处理一个更长的存储过程时，需要在某个地方使用一个临时变量时，我将只对代码的这一部分进行声明。当我想要识别并处理抛出特定代码的异常时，我也会这样做。

Any other reasons why one should nest blocks within a procedure, function or another larger block of PL/SQL?

为什么要在程序、函数或另一个更大的PL/SQL块中嵌套?

3 个解决方案

#1

When you want to handle exceptions locally like this:

当您想要在本地处理异常时，如下所示:

begin
   for emp_rec in (select * from emp) loop
      begin
         my_proc (emp_rec);
      exception
         when some_exception then
            log_error('Failed to process employee '||emp_rec.empno);
      end;
   end loop;
end;

In this example, the exception is handled and then we carry on and process the next employee.

在本例中，异常被处理，然后我们继续并处理下一个员工。

Another use is to declare local variables that have limited scope like this:

另一种用法是声明具有有限范围的局部变量，如:

declare
    l_var1 integer;
    -- lots of variables
begin
   -- lots of lines of code
   ...
   for emp_rec in (select * from emp) loop
      declare
         l_localvar integer := 0;
      begin
         -- Use l_localvar
         ...
      end
   end loop;

end;

Mind you, wanting to do this is often a sign that your program is too big and should be broken up:

请注意，想要这样做通常是一个信号，表明你的程序太大了，应该被拆分:

declare
   l_var1 integer;
   -- lots of variables
   ...
   procedure local_proc (emp_rec emp%rowtype):
      l_localvar integer := 0;
   begin
      -- Use l_localvar
      ...
   end
begin
   -- lots of lines of code
   ...
   for emp_rec in (select * from emp) loop
      local_proc (emp_rec);
   end loop;

end;

#2

I tend to nest blocks when I want to create procedures that are specific to data that only exists within the block. Here is a contrived example:

当我想要创建特定于只存在于块中的数据的过程时，我倾向于嵌套块。这里有一个人为的例子:

BEGIN
  FOR customer IN customers LOOP
    DECLARE

      PROCEDURE create_invoice(description VARCHAR2, amount NUMBER) IS
      BEGIN
        some_complicated_customer_package.create_invoice(
            customer_id => customer.customer_id,
            description => description,
            amount => amount
          );
      END;

    BEGIN

      /* All three calls are being applied to the current customer,
         even if we're not explicitly passing customer_id.
       */
      create_invoice('Telephone bill',  150.00);
      create_invoice('Internet bill',   550.75);
      create_invoice('Television bill', 560.45);

    END;
  END LOOP;
END;

Granted, it's not usually necessary, but it has come in really handy when a procedure can be called from many locations.

当然，它通常不是必需的，但是当一个过程可以从许多地方调用时，它就变得非常有用了。

#3

One reason to have nested BEGIN/END blocks is to be able to handle exceptions for a specific local section of the code and potentially continue processing if the exception is processed.

嵌套的开始/结束块的一个原因是能够处理代码的特定局部区域的异常，并可能在处理异常时继续处理。

#1