I provided a sample procedure written using SQL/PL earlier to check if a string contained only numbers. This is a common validation for input strings.
SQL UDFs cannot contain any commands that are outside the scope of the SQL reference. DB2 will take an SQL UDF and stick it directly into the calling SQL, so that when it's executed it's indistinguishable from the
rest of the SQL statement, as far as DB2 is concerned.
On the other hand, an SQL Procedure is a separate entity that contains it's own set of information specific to it's execution. As such, the SQL/PL language has directives that are NOT found in the SQL reference, as they are specific to the procedure processing. "DECLARE EXIT HANDLER" is one of them.
Also, stored procedures create a package. For the example MP1, the specific name is SQL080109124101300, as found in SYSCAT.PROCEDURES. The generated package name from Data Studio is P2410137. A bit of detective work was required to find how this name was created.
The package was created at 2008-01-09 12:41:01.377. So the package name was pulled from the middle of the timestamp, starting with the second digit of the hour to the hundredths of a second.
A customer asked me to develop a function to check whether a character string was in fact numeric with the further stipulation that it be done in SQL. He reported that the documentation seemed to be lacking.
The DB2 LUW Information Center used to have a Reference section, where you would find the SQL statement reference along with everything else. This has been reorganized in DB2 9.5 to have a "Database Fundamentals" section. You will find the SQL reference information in there.
I figured the easiest thing to do would to use an exception handler. Unfortunately, the documentation provides nothing in the list of topics. The best example I could find was in the IF statement in the SQL section. (IF statement you say: "Of course!") There is almost nothing available for SQL/PL.
From snippets of code there, I developed this:
-- Invalid numeric character is SQLSTATE 22018
DECLARE not_num CONDITION FOR SQLSTATE '22018';
DECLARE EXIT HANDLER FOR not_num
SET smint = -1;
SELECT INTEGER(sTmp) INTO sTmpa FROM SYSIBM.SYSDUMMY1;
The next thing was I couldn't use this in a function. I'll write about functions and stored procedures next time.
