Procedural Extension Uses and Benefits

Stored procedures, user-defined functions, and triggers can be used in many different ways and for many different reasons. The main categories include performance improvement, network traffic reduction, database security, and code reusability.

Sequential and Event-Driven Programming

In sequential programming, the application controls the order of execution; in other words, the code is executed in a predetermined sequence and requires minimal user interference. Sequential, or linear, programming is typical for batch programs that perform large sequential tasks. The most popular languages for linear programming are COBOL, FORTRAN, and C.

In event-driven programming, the code is executed according to a user action or system event; the user actions and system events control the sequence. Application starts and waits for an event (mouse click, keystroke, or a system event such as time expiring) to occur. Based on the specific event the program performs an action and waits for the next event. Most modern visual applications are event-driven. Order entry, financial, time tracking, banking, and many other applications typically open the main menu screen on startup and then wait for users to perform certain actions. The most typical languages for writing event-driven applications are Visual C++, Visual Basic, and Java. (Of course, you can create an event-driven program using COBOL or a procedural program using C++; we just emphasize the typical or most common scenarios.)

In RDBMS, events are usually more database-related than the ones described here. For example, inserting or deleting table rows, updating certain columns, users logging in and out can trigger other events in a database.

Performance and network traffic

Stored routines can be used to improve application performance. Since they simply appear to be compiled code stored inside the RDBMS, they generally execute faster uncompiled SQL statements (that normally have to be compiled each time a user or program calls them).

Network traffic can also be significantly reduced because there is no need to send SQL statements as they are already compiled and stored on the server. Each individual statement is probably not large enough to improve the overall network performance, but, in a large system with thousands of users and tons of SQL statements, it can make a difference.

Database security

Stored procedures, functions, and triggers can be used for database security purposes.

A stored procedure (or function) is a separate database object with its own database privileges. That means you can design a stored procedure in such way that it would, for example, update only certain columns; or insert rows with NULL values for columns that a user who executes the procedure has no permission to update — s/he would only need the privilege to execute that particular stored procedure.

Triggers are even more useful for security implementation. For example, they can be designed in such way that certain actions performed by users on certain objects are written to special database tables or OS files. These records can be reviewed later.

Cross-References 

Read more about using stored procedures and triggers to enforce database security in Chapter 12.

Code reusability

Another important thing about stored routines is code reusability — once compiled, a stored procedure or user-defined function can be used over and over again by multiple users (or applications), saving time on retyping large SQL statements and reducing the probability of human errors. Also, when a persistent module needs to be changed, the change won't affect the client programs that access it, as long as all the calling parameters remain the same.