Stored Procedures and Functions

Stored procedures are groupings of related SQL statements—commonly referred to as functions and subprograms—that allow ease and flexibility for a programmer. This ease and flexibility is derived from the fact that a stored procedure is often easier to execute than a number of individual SQL statements. Stored procedures can be nested within other stored procedures. That is, a stored procedure can call another stored procedure, which can call another stored procedure, and so on.

Stored procedures allow for procedural programming. The basic SQL DDL, DML, and DQL statements (CREATE TABLE, INSERT, UPDATE, SELECT, and so on) allow you the opportunity to tell the database what needs to be done, but not how to do it. By coding stored procedures, you tell the database engine how to go about processing the data.

A stored procedure is a group of one or more SQL statements or functions that are stored in the database and compiled and are ready to be executed by a database user. A stored function is the same as a stored procedure, but a function is used to return a value.

Functions are called by procedures. When a function is called by a procedure, parameters can be passed into a function like a procedure, a value is computed, and then the value is passed back to the calling procedure for further processing.

When a stored procedure is created, the various subprograms and functions (that use SQL) that compose the stored procedure are actually stored in the database. These stored procedures are pre-parsed, and are immediately ready to execute when invoked by the user.

The Microsoft SQL Server syntax for creating a stored procedure is as follows:

CREATE PROCEDURE PROCEDURE_NAME
[ [(] @PARAMETER_NAME
DATATYPE [(LENGTH) | (PRECISION] [, SCALE ])
[ = DEFAULT ][ OUTPUT ]]
[, @PARAMETER_NAME
DATATYPE [(LENGTH) | (PRECISION [, SCALE ])
[ = DEFAULT ][ OUTPUT ]] [)]]
[ WITH RECOMPILE ]
AS SQL_STATEMENTS

The syntax for Oracle is as follows:

CREATE [ OR REPLACE ] PROCEDURE PROCEDURE_NAME
[ (ARGUMENT [{IN | OUT | IN OUT} ] TYPE,
ARGUMENT [{IN | OUT | IN OUT} ] TYPE) ] {IS | AS}
PROCEDURE_BODY

An example of a very simple stored procedure is as follows:

						CREATE PROCEDURE NEW_PRODUCT
						(PROD_ID IN VARCHAR2, PROD_DESC IN VARCHAR2, COST IN NUMBER)
						AS
						BEGIN
						INSERT INTO PRODUCTS_TBL
						VALUES (PROD_ID, PROD_DESC, COST);
						COMMIT;
						END;
					

Procedure created.

This procedure is used to insert new rows into the PRODUCTS_TBL table.

The syntax for executing a stored procedure in Microsoft SQL Server is as follows:

EXECUTE [ @RETURN_STATUS = ]
PROCEDURE_NAME
[[@PARAMETER_NAME = ] VALUE |
[@PARAMETER_NAME = ] @VARIABLE [ OUTPUT ]]
[WITH RECOMPLIE]

The syntax for Oracle is as follows:

EXECUTE [ @RETURN STATUS =] PROCEDURE NAME
[[ @PARAMETER NAME = ] VALUE | [ @PARAMETER NAME = ] @VARIABLE [ OUTPUT ]]]
[ WITH RECOMPLIE ]

Now execute the procedure you have created:

						EXECUTE NEW_PRODUCT ('9999','INDIAN CORN',1.99);
					

PL/SQL procedure successfully completed.

Advantages of Stored Procedures and Functions

Stored procedures pose several distinct advantages over individual SQL statements executed in the database. Some of these advantages include the following:

• The statements are already stored in the database.

• The statements are already parsed and in an executable format.

• Stored procedures support modular programming.

• Stored procedures can call other procedures and functions.

• Stored procedures can be called by other types of programs.

• Overall response time is typically better with stored procedures.

• Overall ease of use.