SOA, Multi-Tier Architectures and Logic in the Database

<<Prev 1 2 3 Next>>

Logic in the Database

The reason for rules and logic in the database is uniformity across applications. Consider a scenario where a company builds an order-entry system to support a toll-free order line and later decides to add a web site with a shopping-cart application. The company's business practices and rules for order processing should be consistent for all classes of orders, whether they come from a toll-free number or the web site. One rule might be to not extend credit terms to customers whose accounts are past due more than 120 days. If the original order-processing system encapsulated that logic in a client-side Visual Basic or C# program, it wouldn't be accessible to the HTML pages that implement the shopping cart. If the logic resides in a database used by both the shopping-cart pages and the order-entry system, the rule would be consistently applied.

Because data integrity is a primary concern of database developers, DBMS vendors provide several techniques for controlling how a DBMS manages data. These include constraints, procedures, triggers, functions and packages. Some SQL dialects also provide rules and a CREATE RULE statement. 

Triggers and Constraints

Constraints permit you to specify rules for maintaining integrity. For example, a CHECK constraint on a column will ensure that stored values fall within acceptable values. You could use a CHECK constraint to specify that the prefix for persons in a Congressional mailing list be "Representative" or "Senator." You can also define other constraints to guarantee that values in a column are unique, that columns not be null, and that a column be the unique primary key which identifies a row.

Triggers specify conditional execution of logic. A trigger executes when a certain condition is met, such as when an order causes a customer's account to exceed its credit limit. Because triggers can fire before or after the execution of SQL statements, you can set up logic that is conditional on the insertion or deletion of records, or attempted record insertions and deletions.

Functions, Procedures, Packages

Stored procedures include SQL statements in a module of procedural logic stored as part of a database. SQL servers from Sybase and Oracle provide a proprietary procedural language. IBM DB2 supports procedures written in programming languages such as C and COBOL.  A user running an SQL script or interactive statement can call stored procedures to receive output parameters, query result sets, or both. The SQL standards committee developed a standard for persistent stored modules (SQL/PSM) because of the proliferation of proprietary stored procedure languages, such as Oracle PL/SQL, Sybase Transact-SQL and Informix SPL. In the 1990s, Oracle, IBM, Informix (now IBM Informix) and Sybase updated their DBMS platforms to support Java as a stored procedure language. Microsoft committed to support language-independent stored procedures after the release of Microsoft SQL Server 7.0. It also announced Microsoft SQL Server 2005 will use the Common Language Runtime (CLR) for that purpose. IBM DB2, starting with version 8.1, provides CLR support for creating database plug-ins and Oracle is doing the same starting with Oracle 10g. 

A package is a unit of logic bound to a database, such as a collection of procedures bound together as a unit. Packages encapsulate SQL objects such as variables, exceptions, cursors, functions, and stored procedures. The package concept is not implemented identically by DBMSs such as Oracle and DB2, but one generic advantage is security. SQL databases manage access to packages based on ownership, rights, and privileges. Oracle uses CREATE PACKAGE to specify packages that include private and public objects, declarations of the names and types of arguments, and calls to subprograms. DB2 developers can create packages for applications written in compiled host languages. DB2 developers can use static SQL, preprocess and compile the code, and bind the package to a database.

SQL functions are similar to functions in several programming languages. You invoke a function, pass it zero or more arguments, and it returns a value. SQL supports an extensive array of numeric, string, and other scalar functions. The numeric functions include operations, such as calculating a tangent or square root, and aggregation, such as calculating a sum or average. String functions support operations such as extracting substrings or converting text to upper case. Some SQL dialects provide functions that return tables instead of scalar values. Extensible servers also permit you to extend an SQL dialect by writing and installing user-defined functions. You declare a function by using an SQL CREATE FUNCTION statement.

System-defined functions, triggers, and stored procedures have been available for years with SQL servers. However, the latest generation of database servers opened up the server architecture to user-defined functions, types, and object-relational capabilities.

<<Prev 1 2 3 Next>>

Database Server Watch  SQLSummit Home Page   Articles  

© 1998-2005, Ken North Computing, LLC. All rights reserved.