Calling a stored procedure from a client application generates minimal network traffic. Rather than the application submitting an entire PL/SQL program block from the client, all that is required is a single call to the procedure or function with an optional parameter list.
Stored procedures provide a convenient and effective security mechanism. One of the characteristics of stored PL/SQL is that it always executes with the privilege domain of the procedure owner. This enables non-privileged users to have controlled access (through the procedure code) of privileged objects. This feature usually serves to reduce the amount of grant administration that the DBA must do.
Both the compiled and textual form of stored procedures are maintained in the database. Because the compiled form of the procedure is available and readily executable, the need to parse and compile the PL/SQL at run time is alleviated.
Database triggers—Database triggers resemble stored procedures in that they are database-resident PL/SQL blocks; the difference between the two is that triggers are fired automatically by the RDBMS kernel in response to a commit time event (such as an insert, update, or delete operation). You can use triggers to enforce complex integrity checking, perform complex auditing and security functions, and implement application alerts and monitors. Like stored procedures, database triggers greatly reduce the amount of code and processing that is necessary in the client portion of an application.
Oracle's implementation of database triggers is slightly different from that of other vendors. Although most databases support statement-level triggers, Oracle also includes functionality to fire triggers at the row-level. Consider an UPDTAE statement that affects values in a set of 100 rows. The kernel would fire a statement-level trigger once—for the UPDATE statement (either before and/or after the statement executes). Row-level triggers, on the other hand, are fired by the kernel for each row that the statement affects—in this case, 100 times. Oracle enables statement-level and row-level triggers to be used in conjunction with one another.
Declarative integrity—When you define a table in Oracle, you might include integrity constraints as part of your table definition. Constraints are enforced by the server whenever records are inserted, updated, or deleted. In addition to using referential integrity constraints that enforce primary and foreign key relationships, you can also define your own constraints to control the value domains of individual columns within a table.
Server-enforced integrity reduces some of the code required for validation by the client and also increases the robustness of the business model defined within the database. With constraints, you can often improve performance and provide the flexibility to support multiple front-end interfaces.
User-defined functions—You'll also find PL/SQL blocks in user-defined functions. User-defined functions are similar to stored procedures and also reduce the amount of application code in the client portion of an application. Not only can you call these functions from PL/SQL, but you can also use them to extend the set of standard Oracle SQL functions. You can place user-defined functions in SQL statements just as you would any other Oracle SQL function.