About Stored Procedures

To reduce network traffic when performing complex operations in a federated environment, you can use stored procedures. A procedure or function is a schema object that is run to solve a specific problem or perform a set of related tasks. Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not. Generally, you use a procedure to perform an action, and you use a function to compute a value. In this guide, the general term stored procedure includes both procedures and functions.

Oracle databases support stored procedures that are written in PL/SQL or Java, but this guide discusses only PL/SQL stored procedures. PL/SQL stored procedures consist of a set of SQL statements and other PL/SQL constructs that are grouped together and stored in the database. Stored procedures let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language.

As with SQL statements, to run a stored procedure, you do not need to be aware of its physical location. Similarly, by using the appropriate Oracle Database Gateway, you can even call a stored procedure that is in a non-Oracle database. In this case, the gateway maps the PL/SQL calls to the non-Oracle database stored procedures.