Read an Excerpt
PREFACE: Working with Informix Stored Procedure Language (SPL) requires more than understanding the language syntax, which, in itself, is not difficult to learn. What is challenging for many programmers and database administrators is gaining an appreciation of the role this unique code plays in modern databases and application models. Developers must be able not only to understand how to create a procedure, but also when to create one. This handbook focuses on the essential syntax and structure of Stored Procedures (SPs) and provides an overall evaluation process for strategically applying this tool for maximum results.
As with any technical material, some prerequisites are required before you can gain the most value from the information presented. This handbook assumes the reader has a thorough understanding of SQL syntax and usage INFORMIX-4GL, INFORMIX-ESQL for C, or other similar language INFORMIX-OnLine or INFORMIX-SE, version 5.1 or later Informix DB-Access Informix Triggers.
This book focuses on providing a solid foundation of Stored Procedures. Topics range from discussion of database objects to techniques that help identify the application of SPs.
After Stored Procedures are set in context in Chapter 1, "Introduction," they are covered under four major categories:
I. SPL Primer: Understanding how to use objects; Chapter 2, "Defining Stored Procedures" General overview; Chapter 3, "Dissecting Stored Procedures" Detailed discussion of SPL structure; Chapter 4, "Controlling Stored Procedures" Examination of logic flow and control.
II. Performance Optimization: Targeting major areas for performance gains; Chapter 5,"Speeding Up with SPs" Strategies and examples; Chapter 6, "Fortifying Security" Techniques and examples; Chapter 7, "Protecting Data Integrity" Approaches to the problem.
III. Implementation of Stored Procedures: Applying SPs; Chapter 8, "Determining Right Size, Right Scope" Models, tests for SP effectivity; Chapter 9, "Applying Stored Procedures" Tasks that benefit from SPs. IV. Appendices: Supplemental information; Appendix A, "SPL Reference Manual Pages" Exhaustive list of SPL syntax; Appendix B, "Stored Procedure Errors" Informix errors; Appendix C, "Orders-R-Us Database" Database for most book examples; Appendix D, "SPL Version Differences" SPL feature differences between versions; Appendix E, "SP Object Pack Review" Review of Stored Procedures in the SP Object Pack.
Finally, this handbook includes the SP Object Pack, a collection of Stored Procedures that you can use for your own implementations. The pack includes meaningful objects you can incorporate immediately.
Here are format elements you can expect to find in this book: Each chapter begins with the chapter name and a list of topics covered. Examples are based, when possible, on a database called "Orders-R-Us," which is presented in Appendix C.
Each chapter ends with two frames: One summarizes what you should have learned in the chapter.
The second, called "Object Insight," elaborates on important points. References to reserved words or syntax of the Stored Procedure Language or SQL are capitalized. However, capitalized words in quotation marks should be considered variable constants. For example, CREATE PROCEDURE my_name(variable_1 CHAR(3) DEFAULT "AMG"). In this example, only CREATE PROCEDURE, CHAR, and DEFAULT are reserved words.