1 - Stored Procedures as Database Programming Model

Abstract

This chapter discusses stored procedures as database programming model. Although stored procedures have been around for more than a decade now, there still is a recurrent, almost ideological, debate on this programming model. This chapter discusses the rationales for stored procedures, the obstacles to their adoption, languages used for writing stored procedures, and proprietary procedural languages such as PL/SQL versus open standards languages such as Java. The motivations to use stored procedures range from simplifying database programming, advanced data access and performance to centrally managed data logic and optimizing network traffic. Stored procedures inherently incur minimal data movement, compared with a set of individual SQL statements that ship data outside the database. By processing data within the database and returning just the results, stored procedures reduce network traffic and data movement. Finally, the pragmatic database developers use both Java and PL/SQL, because these complement each other very well to glue together the rich set of database features.