我什么时候应该嵌套 PL/SQL BEGIN...END 块?

2021-12-24 00:00:00 sql oracle plsql

我在 BEGIN...END 块中随意地将代码的子部分分组,这似乎是正确的.大多数情况下,当我处理一个更长的存储过程并且需要在一个地方使用一个临时变量时,我将只为那部分代码声明它.当我想识别和处理为特定代码段引发的异常时,我也会这样做.

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.

为什么应该在一个过程、函数或另一个更大的 PL/SQL 块中嵌套块的任何其他原因?

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

推荐答案

当你想像这样在本地处理异常时:

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; 

相关文章