Relational databases now have built-in procedural extensions – including stored procedures, triggers, and rules. These extensions are very u...
Relational databases now have built-in procedural extensions – including stored procedures, triggers, and rules. These extensions are very useful but extremely non-standard.
What Is a Stored Procedure?
Many database vendors are now offering an RPC –like mechanism for database. This mechanism is sometimes referred to as “TP lite” or “stored procedures.”
A stored procedure is a named collection of SQL statements and procedural logic that is compiled, verified, and stored in the server database. A stored procedure is typically treated like any other database object and registered in the SQL catalog. Access to the stored procedure is controlled through their server’s security mechanisms.
Stored Procedures accept input parameters so that multiple clients using different input data can use a single procedure over the network. The client invokes a remote procedure and passes it the parameters required to do a job. A single remote message triggers the execution of a collection of stored SQL statements. The result is a reduction of network traffic and better performance.
Applications
The stored procedures are used to enforce business rules and data integrity; to perform system maintenance and administration functions; and to extend the database functions. However the primary use of stored procedures is to create the server side of an application’s logic. The encapsulation features of stored procedures are well suited for creating performance-critical applications known as Online Transaction Processing, or OLTP.
In other words, a stored procedure is a database-centric, RPC-like SQL entity that is persistent, shared, and has a name. It reduces network traffic, improves response times. And provides an object-oriented flavor of database service that is well suited for OLTP applications. Stored Procedures also provide better site autonomy because the remote modification of tables can only occur through locally executing programs. If the tables change, you don’t need to recompile all your remote applications. In general, stored procedures provide better distribution of intelligence than static or dynamic remote SQL.
Static SQL statements are defined in your code and converted into an access plan at program preparation time. The SQL statement is known before your program is run. The database objects need to exist when precompiling static SQL statements. Static SQL is a performance enhancement feature.
Dynamic SQL statements are created and issued at run time. They offer maximum flexibility at the expense of execution speed. The database objects need not exist when precompiling dynamic SQL statements. The compilation of dynamic SQL statements is done at run time and must be repeated every time the same statement gets executed again.
Disadvantages
One draw back of stored procedures is that they provide less ad hoc flexibility than remote dynamic SQL. In addition, stored procedures may perform very poor if their plans are not refreshed to take advantage of the optimizer statistics –dynamic SQL creates a fresh plan with every execution. Another draw back is that there is no transactional synchronization- that is, two-phase commit- between stored procedures; each stored procedure is a separate transaction.
Main draw back is that they are totally non-standard. The results in a number of problems. No two-vendor implementations are alike.
Triggers And Rules
Triggers are special user-defined actions-usually in the form of stored procedures- that is automatically invoked by the server based on data-related events. Triggers can perform complex actions and can use the full power of a procedural language.
A rule is a special type of triggers that is used to perform simple checks on data. Both the triggers and rules are attached to specific operations on specific tables. In other words, an event tells you something happened to the database; a trigger or rule is an event handler to write to take the proper action in response to that event. Triggers and rules are typically used to perform tasks related to changes in tables, such as auditing, looking for value thresholds, or setting column defaults. Enabled triggers or rules are executed whenever an SQL DELETE, INSERT, or UPDATE command updates a table. A separate trigger or rule can be defined for each of these commands, or a single trigger may be defined for any updates to a table. Triggers are called implicitly by database-generated events, while stored procedures are explicitly by client applications.
What Is a Stored Procedure?
Many database vendors are now offering an RPC –like mechanism for database. This mechanism is sometimes referred to as “TP lite” or “stored procedures.”
A stored procedure is a named collection of SQL statements and procedural logic that is compiled, verified, and stored in the server database. A stored procedure is typically treated like any other database object and registered in the SQL catalog. Access to the stored procedure is controlled through their server’s security mechanisms.
Stored Procedures accept input parameters so that multiple clients using different input data can use a single procedure over the network. The client invokes a remote procedure and passes it the parameters required to do a job. A single remote message triggers the execution of a collection of stored SQL statements. The result is a reduction of network traffic and better performance.
Applications
The stored procedures are used to enforce business rules and data integrity; to perform system maintenance and administration functions; and to extend the database functions. However the primary use of stored procedures is to create the server side of an application’s logic. The encapsulation features of stored procedures are well suited for creating performance-critical applications known as Online Transaction Processing, or OLTP.
In other words, a stored procedure is a database-centric, RPC-like SQL entity that is persistent, shared, and has a name. It reduces network traffic, improves response times. And provides an object-oriented flavor of database service that is well suited for OLTP applications. Stored Procedures also provide better site autonomy because the remote modification of tables can only occur through locally executing programs. If the tables change, you don’t need to recompile all your remote applications. In general, stored procedures provide better distribution of intelligence than static or dynamic remote SQL.
Static SQL statements are defined in your code and converted into an access plan at program preparation time. The SQL statement is known before your program is run. The database objects need to exist when precompiling static SQL statements. Static SQL is a performance enhancement feature.
Dynamic SQL statements are created and issued at run time. They offer maximum flexibility at the expense of execution speed. The database objects need not exist when precompiling dynamic SQL statements. The compilation of dynamic SQL statements is done at run time and must be repeated every time the same statement gets executed again.
Disadvantages
One draw back of stored procedures is that they provide less ad hoc flexibility than remote dynamic SQL. In addition, stored procedures may perform very poor if their plans are not refreshed to take advantage of the optimizer statistics –dynamic SQL creates a fresh plan with every execution. Another draw back is that there is no transactional synchronization- that is, two-phase commit- between stored procedures; each stored procedure is a separate transaction.
Main draw back is that they are totally non-standard. The results in a number of problems. No two-vendor implementations are alike.
Triggers And Rules
Triggers are special user-defined actions-usually in the form of stored procedures- that is automatically invoked by the server based on data-related events. Triggers can perform complex actions and can use the full power of a procedural language.
A rule is a special type of triggers that is used to perform simple checks on data. Both the triggers and rules are attached to specific operations on specific tables. In other words, an event tells you something happened to the database; a trigger or rule is an event handler to write to take the proper action in response to that event. Triggers and rules are typically used to perform tasks related to changes in tables, such as auditing, looking for value thresholds, or setting column defaults. Enabled triggers or rules are executed whenever an SQL DELETE, INSERT, or UPDATE command updates a table. A separate trigger or rule can be defined for each of these commands, or a single trigger may be defined for any updates to a table. Triggers are called implicitly by database-generated events, while stored procedures are explicitly by client applications.
