Recently I discussed the motivation behind using anonymous blocks.
The reasoning was centered around the need to execute complex scripts on the server without being able or willing to define a routine in the database's catalog.
Inside of an anonymous block you can then define all the logic flow you need.
You can also declare local variables to hold temporary data.
DB2 10 expands beyond that by allowing you to declare local types and local procedures.
When ARRAY types were introduced in DB2 9.5 we discussed whether there should be predefined types for arrays of any base types.
In the end we decided against this, but the point in favor was that such arrays may be commonly used and it would be annoying fro an application developer to request permission from the DBA to get these types created.
Or it may be that different developers create all sorts of arrays of integers in numerous schemata with different or the same names.
A point against predefining arrays of base types was that the next step would have been to define arrays for any row types that are created as well.
In the end locally declared types make away with the need to predefine any types.
Their definition is pretty self explanatory I think - this will be short post.
CREATE TABLE address(number INTEGER,
DECLARE TYPE address AS ROW(number INTEGER,
DECLARE TYPE addresses AS address ARRAY;
DECLARE vaddress address;
DECLARE vaddresses addresses;
SET vaddress = (5, 'Sugarcane Street', 'L0G4P9', 'Springfield');
SET vaddresses = vaddress;
INSERT INTO address SELECT * FROM TABLE(vaddresses);
SELECT * FROM address;
NUMBER ROAD ZIP CITY
----------- -------------------- ------ --------------------
5 Sugarcane Street L0G4P9 Springfield
As you can see the only difference between a create type statement and a declared type is the keyword DECLARE.
Here are the rules which apply:
- Local types must be declared at the very beginning of a compound block, prior to any variables.
- You can declare row types and array types
- You can declare arrays of rows
- You cannot, yet, declare nested complex types as allowed since Db2 9.7.5 for created types.
- The scope of a local type is that of the compound in which it is defined and any compounds defined within it.
When the compound is left the type ceases to exist.
- Local types can be used in any SQL PL or PL/SQL compound including:
- Compiled triggers
- Compiled functions
- Anonymous blocks
Adding support to locally declare procedures is just another natural extension to supporting local variables and types.
Common usage is the implementation of logic patterns that are repetitive within a larger piece of logic.
But the pattern isn't of importance beyond the scope of the block, so it's not useful to harden it into the catalog.
In the example below the procedure "TRACE" encapsulates an insert statement.
The only purpose here is to improve readability:
CREATE TABLE trace(stamp TIMESTAMP, process VARCHAR(10),
num INTEGER , data VARCHAR(20));
DECLARE process VARCHAR(20) CONSTANT 'SCRIPT';
DECLARE PROCEDURE TRACE(num INTEGER, data VARCHAR(20))
INSERT INTO trace
VALUES(TIMESTAMP(GENERATE_UNIQUE()), process, num, data);
CALL TRACE(10, 'Entry');
/* do stuff */
CALL TRACE(20, 'Done Stuff');
/* do more stuff */
CALL TRACE(30, 'Exit');
SELECT * FROM trace;
STAMP PROCESS NUM DATA
-------------------------- ---------- ----------- --------------------
2012-05-14 12:41:21.653200 SCRIPT 10 Entry
2012-05-14 12:41:21.653512 SCRIPT 20 Done Stuff
2012-05-14 12:41:21.653801 SCRIPT 30 Exit
This example, small as it may be already displays an important property of locally declared procedures:
The procedure can use local variables and even local types that are defined in an outer compound block.
Here are the overall rules:
- Local procedures are declared at the beginning just before condition handlers, but after any types, variables or cursor.
- You can do the same overloading of procedures by number as parameter as you can do with created procedures.
- Local procedures can in-turn contain local procedures, but in that case name overloading is not allowed anymore.
I have a very hard time imagining anyone exploiting nested local procedures
- When calling procedures, just like with variables and types resolution goes from the inside to the outside.
That is an unqualified local procedure will be preferred over an unqualified global (or "not so local" procedure").
- You cannot define any properties such as DETERMINISTIC for local procedures.
Local functions are unfortunately not yet supported in DB2 10.1.
Time and resources are limited.
But I'm sure we'll get to them in due time.