Stored Procedure Language

• The work is done at the server so you don't have to collect a bunch of data and process it in client code. Saves bandwidth and improves performance drastically in some cases.
• Permissions are the permissions of the creator of the SPL, not of the user. So you can have an SPL update a table that is normally off limits to a user. This allows a 'DBA' to set up strict rules for updating/inserting rows and codify them in an SPL. Programmers would then be required to use the SPL to do any work against the table. This also ensures that you have one place to maintain table modification code and one place to debug.
• They are cached in memory - so the next user who invokes one gets it faster. This also means that you can drop an SPL and recreate it and SOMETIMES the old SPL is still out there in memory. A royal pain - it helps if you can tell which version is in use.
• They are a natural follow on for a trigger. Imagine something like: ON INSERT TO TABLE A CALL SPL(record.A); That is not the syntax - but it sounds more like english.
• They are nice to do recursion like when you want to explode a bill of materials. Something that 4gl cannot do well with cursors.
• When used in conjunction with Triggers they are a very versatile tool for handling things like a cascading (or snowball for all you COBOL folks) delete.

• Debugging can be complicated, because it's not always apparent that there is an SPL/trigger combo out there doing work.
• The language is quite primitive. Time is not handled well. Time is set for all variables that require it at SPL invocation - so if you are trying to clock an SPL you need to do it from outside the SPL and not by putting in a set start time/ end time from within the SPL. You can't do things like 'UNLOAD'. There is limited ability to do string manipulation and no capability to retrieve anything from the OS.
• Once memory resident it stays there while your session (or whoever has called it) is current. Means that you can DROP the procedure, recreate it with new code, and when you call it you are still running the old code from memory (this is with 5.0).
• DO NOT, REPEAT, DO NOT use them for every little thing in the world. You will create a nightmare to untangle. Rather do a careful plan that revolves around why you want them - not just because they are cool.
• The language is propriatory. You can't just move Informix-SPL to Oracle/Sybase/Where-ever.
• Informix's new Data-Blades seem to be The Way Of The Future, so don't hold your breath waiting for new features in SPL.

When a stored procedure is created all optimization will be attempted at that time. If the tables cannot be examined at compile time (they may not exist or may not be available), the create procedure will not fail. The SQL in this case will be optimized the first time the stored essay writing procedure is executed, and the query plan will be stored for use by other processes.

An SQL statement will also be optimized at execution time if any DDL statement (e.g. alter table, drop index, create index) has been run that might alter the query plan. Altering a table which is linked to another table via a referential constraint (in either direction) will cause re-optimization of procedures which operate on the referenced table. The dependency list is used to track which changes would cause re-optimization.

If “update statistics for table” is run for any table involved in the query, the SQL statement will be re-optimized the next time the stored procedure is executed.

Dynamic SPL