DB2 10: Using locally defined types and procedures
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, road VARCHAR(20), zip CHAR(6), city VARCHAR(20)); BEGIN DECLARE TYPE address AS ROW(number INTEGER, road VARCHAR(20), zip CHAR(6), city VARCHAR(20)); 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); END / SELECT * FROM address; NUMBER ROAD ZIP CITY ----------- ----
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:
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)); BEGIN DECLARE process VARCHAR(20) CONSTANT 'SCRIPT'; DECLARE PROCEDURE TRACE(num INTEGER, data VARCHAR(20)) BEGIN INSERT INTO trace VALU
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 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.