Database Processing and Stored Procedural SQL

The long-term trend in the database market is for databases to take on a progressively larger role in the overall data processing architecture. The pre-relational database systems basically handled only data storage and retrieval; application programs were responsible for navigating their way through the database, sorting and selecting data, and handling all processing of the data. With the advent of relational databases and SQL, the DBMS took on expanded responsibilities. Database searching and sorting were embodied in SQL language clauses and provided by the DBMS, along with the capability to summarize data. Explicit navigation through the database became unnecessary. Subsequent SQL enhancements such as primary key, foreign key (referential), and check constraints continued the trend, taking over data validation and data integrity functions that had remained the sole responsibility of application programs with earlier SQL implementations. At each step, having the DBMS take on more responsibility provided more centralized control and reduced the possibility of data corruption due to application programming errors.

In many information technology (IT) departments within large companies and organizations, this DBMS trend paralleled an organizational trend. The corporate database and the data it contains came to be viewed as a major corporate asset, and in many IT departments, a dedicated database administration (DBA) group emerged, with responsibility for maintaining the database, defining (and in some cases updating) the data it contained, and providing structured access to it. Other groups within the IT department, or elsewhere within the company, could develop application programs, reports, queries, or other logic that accessed the database. In most organizations, application programs, and the businesspeople using them, have had primary responsibility for updating the data within the database. However, the DBA group sometimes has had responsibility for updating reference (lookup) table data and for assisting with scripts and utilities to perform tasks such as the bulk loading of newly acquired data. But the security of the database, the permitted forms of access, and in general, everything within the realm of the database, became the province of the DBA.

Three important features of modern enterprise-scale relational databases—stored procedures, functions, and triggers—have been a part of this trend. Stored procedures can perform database-related application processing within the database itself. For example, a stored procedure might implement the application’s logic to accept a customer order or to transfer money from one bank account to another. Functions are stored SQL programs that return only a single value for each row of data. Unlike stored procedures, functions are invoked by referencing them in SQL statements in almost any clause where a column name can be used. This makes them ideal for performing calculations and data transformations on data to be displayed in query results or used in search conditions. Nearly all relational DBMS products come with a set of vendor-supplied functions for general use, and therefore functions added by local database users are often called user-defined functions. Triggers are used to automatically invoke the processing capability of a stored procedure based on conditions that arise within the database. For example, a trigger might automatically transfer funds from a savings account to a checking account if the checking account becomes overdrawn. The stored procedural SQL capabilities of the popular DBMS products have been significantly expanded in their major revisions during the late 1990s and 2000s.

Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition


Subscribe to Developer Techno ?
Enter your email address:

Delivered by FeedBurner