Stored procedure language (SPL): Advantages of stored procedures

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.

Leave a Reply