Declares a block of statements for always executing a block, regardless of whether an exception occurs or not.

Syntax

---

[Label:]
BEGIN
   [<Statement>;]
   [<Statement>;]
FINALLY
   [<Statement>;]
END [Label];

Usage

---

Use these statements to declare a block of statements for execution in a procedure or function with an associated block of statements that will be executed regardless of any exceptions that are raised, or whether the block of statements was exited using the LEAVE statement.

FINALLY blocks are useful for ensuring that any resources that are allocated before the block is executed, are released after the block is executed. For example, if an external function/procedure is called that opens a file on disk, you would want to use a FINALLY block to ensure that another external function/procedure is called to close the file.

Examples

---

-- This procedure uses a FINALLY
-- block to make sure that the file opened
-- using the OpenFile() external function
-- is closed using the CloseFile() external
-- function

CREATE FUNCTION ReadTextFile(IN TextFileName VARCHAR)
RETURNS VARCHAR
BEGIN
   DECLARE FileHandle INTEGER DEFAULT 0;
   DECLARE Result VARCHAR DEFAULT '';

   SET FileHandle=OpenFile(TextFileName);
   BEGIN
      SET Result=ReadFile(FileHandle);
   FINALLY
      CloseFile(FileHandle);
   END;

END

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: