Stored Procedures

Stored procedures provide a powerful way to code application logic that can be stored on the server. Stored procedures reduce network traffic, improve performance, and improving productivity (statements in a stored procedure only need to be written one time). Additionally, stored procedures can be used to help ensure the integrity of the database (information is entered in a consistent manner).

In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity.

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

• IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
• OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
• IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.