A stored procedure is a subroutine that is available to all the applications that are accessing a relational database system. These procedures are actually stored in the database data dictionary. The typical uses for these procedures will include data validation which is integrated into the database or access control mechanisms. These procedures can also be used to consolidate as well as centralize logic that was originally implemented in applications. Extensive or complex processing that will require the execution of several SQL statements will be moved into stored procedures where all the applications call the procedures.
These procedures are like User Defined Functions (UDF's) with the major difference being that UDF's can be used like any other expression within SQL statements while the procedures must be invoked using the Call statement. These procedures may also return result sets for example the results of a Select statement which can be processed using cursors, by associating a result set locator, by other stored procedures or by applications. The procedures may also contain declared variables used for processing data and cursors that will allow it to loop through multiple rows in a table.
These procedures languages will typically include, If, While, Repeat, Loop, Case statements among others. These procedures can also receive variables, return variables or modify them and return them depending on how and where the variable is declared. The exact and correct implementation of stored procedure will vary from one database system to another. A majority of the database vendors will support them in some form. Depending on the database system, these procedures can be implemented in a variety of programming languages that will include Java, SQL, C or C++.
There are however procedures that have not been written in SQL programming language but can still execute SQL statements. There is an increase in the adoption of these procedures and this is what has led to the various procedural elements to the SQL language in the part SQL/PSM. Most database systems nowadays offer proprietary and vendor specific extensions that will exceed SQL/PSM. These procedures can also be used to control transaction management in some systems and in other systems they run inside a transaction such that transactions are effectively transparent to them.
These procedures can also be invoked from a database trigger or a condition handler for example a procedure may be triggered by an insert on a specific table or update of a specific field in a table and the code inside the procedure would be executed. The writing of these procedures as condition handlers also allows database administrators to be able to track errors that are in the system with greater detail by using the procedures to catch the errors as well as errors and record some audit information in the database or an external resource like a file. These procedures are stored directly in the database and this may remove all or part of the compilation overhead that is typically required in situations where the software applications send in line SQL queries to a database.
Author Resource:
This article touched the basics of the topic. I have 2 more resources related to the above. They are stored procedure and SQL . They are worth a read.
![[Valid RSS feed]](http://www.look4articles.com/images/icon_Rss.png "XML Feed For RSS"){kind=icon}
Category Rss Feed
Print This Article
Add To Favorites
![[Valid RSS feed]](http://www.look4articles.com/images/rss.jpg "XML Feed For RSS")