A set of SQL statements accompanying an assigned name and stored in the database data dictionary is known as a stored procedure. Alternatively, a stored procedure can be understood as a subroutine available for applications that access an RDBMS.
Typically, complex or extensive processing requiring execution of several SQL statements can be saved into stored procedures for saving time and memory. It is also possible to use nested stored procedures by executing one stored procedure from the other.
Although stored procedures are able to access as well as modify data present in a database, they are not limited to a specific database or object.
Uses of Stored Procedures
Primary
- Consolidating and centralizing logic originally implemented in applications connected to the RDBMS
- In access control mechanisms
- In data validation mechanisms
Secondary
- Allow database admins to track errors in the system in greater detail by writing stored procedures as condition handlers
- Run inside a transaction so that it is transparent to the stored procedures
- To control transaction management in some systems
The Implementation
In terms of implementation, stored procedures are much similar to UDFs (user-defined functions). While UDFs can be used just like any other expression within SQL statements, stored procedures are invoked using the CALL or EXECUTE statement.
The precise implementation process of a stored procedure depends on the RDBMS system for which it is meant for. Hence, the implementation of a stored procedure, for example for MariaDB will be different from the one implemented for the Microsoft SQL Server.
Based on the database system, it is possible to implement a stored procedure in a range of programming languages. However, stored procedures are written in non SQL languages, such as C++ or Java, may or may not execute SQL statements on their own.
Some of the databases and supported implementation languages are listed down as follows:
- DB2 – SQL PL or Java
- Firebird – PSQL
- Microsoft SQL Server – Transact-SQL and several .NET framework languages
- MySQL – Closely adheres to the SQL/PSM standard
- Oracle Database – Java or PL/SQL
- PostgreSQL – PL/Perl or PL/PHP or PL/pgSQL
Stored Procedure in Some Popular RDBMS
Microsoft SQL Server
Typically, stored procedures belonging to the Microsoft SQL Server are developed using Transact-SQL (T-SQL). They are capable of accepting input parameters and then returning multiple values of output parameters.
In Microsoft SQL Server, stored procedures are responsible for programming statements to perform operations in the database and then return a status value to the calling procedure or batch.
User-defined procedures are created in a user-defined database or all system databases, excluding for a read-only database. There are two types of temporary procedures, global and local, and are stored in TempDB.
While local procedures are only visible to the current user connection in Microsoft SQL Server, global procedures are visible to all users right after the moment they are created.
Systems procedures come prepackaged with the Microsoft SQL Server and are physically stored in an internal and hidden-resource database. This type of stored procedures appears in the SYS schema of each system and in a user-defined database.
Oracle Database
Oracle Database’s proprietary language, PL/SQL is made from stored procedures. Professionals working with Oracle Database use stored programs in the RDBMS for writing and testing code. Once compiled, these programs become stored procedures.
Any stored procedure in Oracle Database follows the basic PL/SQL block structure that consists of individual declarative, executable, and exception-handling parts.
Advantages
- A stored procedure enhances productivity as statements in a stored procedure must be written only once
- As the commands in a stored procedure are executed as a single batch of code, using stored procedures can significantly reduce network traffic among the clients and the servers. In other words, instead of sending every single line of code over a network only the call to be executed the procedure is sent over a network
- Because information is entered in a consistent way, data procedures preserve data integrity
- Coding a stored procedure is easier than building a query via a graphical user interface
- Due to their modular nature, it is easier to troubleshoot a stored procedure in case of an application issue
- Offer advantages over embedding queries in a GUI
- Provide support for security via data access controls. End users can alter or enter data but aren’t able to write procedures. Hence, they provide an important security layer between the database and the user interface
- Stored procedures are tunable. Hence, it eliminates the requirement to modify GUI source code for improving the performance
Disadvantages
- As stored procedure languages are typically vendor-specific, changing database providers usually necessitates rewriting the existing stored procedures
- Compared to other code, changes made to stored procedures are difficult to track using a version control system. Also, it is difficult to merge and track differences made in stored procedures
- Different stored procedure languages offered by different vendors have different degrees of sophistication
- Tools for debugging and writing stored procedures are not often as good as compared to the ones available to non-database programming languages
MySQL, SQL and Stored Procedures from Beginner to Advanced
Stored Procedures vs. UDFs (User-Defined Functions)
While a stored procedure is a subroutine, a user-defined function is a subprogram meant to perform certain computations. Different types of functions return different values. For example, a scalar function returns only one value while a table function returns a relational table.
Whereas it is not possible to include stored procedures in SELECT statements, functions can be used in the SELECT statement. However, there is a condition that they must perform no data manipulation.
Returning value is mandatory for functions. However, no such condition is imposed on stored procedures. They can also use RETURN keyword as functions do but with no value being passed. Unlike a function, a stored procedure is a database and material object.
Compared to a function, a stored procedure cuts on query compiling time. Also, a stored procedure can return several values by means of the OUT parameter.
Both stored procedures and functions are appropriate for accomplishing the same task. Although both can be custom-defined, functions are specially designed to send their output to a query or a T-SQL statement.
While stored procedures are designed for returning outputs to the application, user-defined functions return table variables. Moreover, functions can’t change the operating system environment or the server environment.
Stored Procedures vs. Prepared Statements
A prepared statement takes a query or ordinary statement and parameterizes the same in order to allow different literal values to be used sometime later. Compared to stored procedures, prepared statements are more declarative and simpler.
Just like stored procedures, prepared statements are stored on the server for enhancing efficiency as well as protection against SQL Injection attacks. However, prepared statements can’t operate on variables and often don’t use procedural logic.
Nonetheless, prepared statements offer better reusability among different database systems due to their client-side implementations and comparatively simpler interfaces.
Conclusion
A stored procedure is simply a prepared SQL code that you can save to be reused time and again. With this, the stored procedures article is now complete. Hope it will help you better understand and work with stored procedures.
People are also reading:
