Stored procedures offer several advantages, both for database users and database administrators, including:
- Runtime performance. Many DBMS brands compile stored procedures (either automatically or at the user’s request) into an internal representation that can be executed very efficiently by the DBMS at runtime. Executing a precompiled stored procedure can be much faster than running the equivalent SQL statements through the PREPARE/EXECUTE process.
- Reusability. Once a stored procedure has been defined for a specific function, that procedure may be called from many different application programs that need to perform the function, permitting very easy reuse of application logic and reducing the risk of application programmer error.
- Reduced network traffic. In a client/server configuration, sending a stored procedure call across the network and receiving the results in a reply message generates much less network traffic than using a network round trip for each individual SQL statement. This can improve overall system performance considerably in a network with heavy traffic or one that has lower-speed connections.
- Security. In most DBMS brands, the stored procedure is treated as a trusted entity within the database and executes with its own privileges. The user executing the stored procedure needs to have only permission to execute it, not permission on the underlying tables that the stored procedure may access or modify. Thus, the stored procedure allows the database administrator to maintain tighter security on the underlying data, while still giving individual users the specific data update or data access capabilities they require.
- Encapsulation. Stored procedures are a way to achieve one of the core objectives of object-oriented programming—the encapsulation of data values, structures, and access within a set of very limited, well-defined external interfaces. In object terminology, stored procedures can be the methods through which the objects in the underlying RDBMS are exclusively manipulated. To fully attain the object-oriented approach, all direct access to the underlying data via SQL must be disallowed through the RDBMS security system, leaving only the stored procedures for database access. In practice, few if any production relational databases operate in this restricted way.
- Simplicity of access. In a large enterprise database, a collection of stored procedures may be the main way in which application programs access the database. The stored procedures form a well-defined set of transactions and queries that applications can perform on the database. For most application programmers, a call to a simple, predefined function that checks an account balance, given a customer number, or one that adds an order, given a customer number, quantity, and product-id, is easier to understand than the corresponding SQL statements.
- Business rules enforcement. The conditional processing capabilities of stored procedures are often used to place business rules into the database. For example, a stored procedure used to add an order to the database might contain logic to check the credit of the customer placing the order and check whether there is enough inventory on hand to fill the order, and reject the order if these conditions cannot be met. A large company could quite easily have several different ways in which orders are taken and entered into the corporate database—one program for use by direct salespeople, one for people in the telesales department, another that accepts orders placed via the World Wide Web, and so on. Each of these would typically have its own order-acceptance program, usually written by different programmers at different times. But if all of the programs are forced to use the same stored procedure to add an order, the company can be assured that the business rules in that procedure are being uniformly enforced, no matter where the order originated.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.