Chapter 9. Storing Logic in the Database

Most relational databases allow you to compartmentalize reusable nuggets of logic in what are called stored procedures and user-defined functions (UDFs). In addition, many relational databases allow you to react to changes in data or even structural changes to the database via the use of triggers. The SQL standard defines a syntax for expressing these. In this chapter we’ll focus on the SQL standard commands for working with casts, cursors, functions, methods, procedures, triggers, and types and to what extent these commands are supported in the databases covered in this book (including any deviations from or extensions to the standard). One particular feature not covered by the latest SQL standard is syntax for creating aggregate functions. All the databases we cover support this, but they deviate widely in their syntax. In addition, the SQL standard does not cover triggers that take action when objects in a database (such as tables, views, and even functions) are added, altered, or dropped. Again, most of the platforms provide such triggers, but with varying syntax, as you will see.

How to Use This Chapter

When researching a command in this chapter:

  1. Read “SQL Platform Support”.

  2. Check Table 9-1.

  3. Look up the specific SQL statement, check the syntax, and read the “Keywords,” “Rules at a glance,” and “Programming tips and gotchas” sections. Do this even if you are looking for a specific platform implementation.

  4. Finally, read the platform-specific ...

Get SQL in a Nutshell, 4th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.