Stored procedure language (SPL): Stored procedures extend the capabilities of SQL and turn it in to a regular programming language known as SPL. Stored procedures were introduced by Sybase. The basic purpose of a stored procure is to provide function like capabilities in SQL. Also, by use of stored procedure a group of SQL statements can be sent as one to database servers instead of sending one SQL statement at a time for processing. The following features are added to SQL to make it SPL:
1) Loop: WHILE, FOR and cursor based loops provide repetitive functionality.
2) Condition: IF THEN ELSE statement is used for checking conditions.
3) Block: A group of SQL statements can be put together as a block.
4) Named variable: Calculated and retrieved values are saved for later use as named variables.
5) Named procedure: SQL statements can be grouped as named procedures which can be used as functions.
Advantages of Stored procedures:
1) Increase in performance at run time: Stored procedures increases the performance of SQL statements at run time compared to individual SQL statements.
2) Network traffic is reduced: Since a group of statements are sent to database server, instead of individual statements, network traffic is greatly minimized.
3) Reusable: Just like a function or methods in many programming languages, a stored procedure can be reused at a later stage.
4) Encapsulation: Data in stored procedures is encapsulated.
5) Secure: A user who wants to access a stored procedure must have specific permissions. Stored procedures provide necessary security within SQL statements.
6) Easy to access: Juts like calling a method or a function a stored procedure is easier to access.
7) Business rules: Stored procedure is often used for enforcing business rules in a database.