A Stored Procedure is a type of code in SQL that can be stored for later use and can be used many times. So, whenever you need to execute the query, instead of calling it you can just call the stored procedure. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter values that is passed.
CREATE PROCEDURE procedure_name AS sql_statement GO;
To execute a stored procedure –
CREATE PROCEDURE SelectAllGeeks AS SELECT * FROM Geeks GO;
The main advantages of stored procedure are given below:
- Better Performance –
The procedure calls are quick and efficient as stored procedures are compiled once and stored in executable form.Hence the response is quick. The executable code is automatically cached, hence lowers the memory requirements.
- Higher Productivity –
Since the same piece of code is used again and again so, it results in higher productivity.
- Ease of Use –
To create a stored procedure, one can use any Java Integrated Development Environment (IDE). Then, they can be deployed on any tier of network architecture.
- Scalability –
Stored procedures increase scalability by isolating application processing on the server.
- Maintainability –
Maintaining a procedure on a server is much easier then maintaining copies on various client machines, this is because scripts are in one location.
- Security –
Access to the Oracle data can be restricted by allowing users to manipulate the data only through stored procedures that execute with their definer’s privileges.
The main disadvantages of stored procedures are given below:
- Testing –
Testing of a logic which is encapsulated inside a stored procedure is very difficult. Any data errors in handling stored procedures are not generated until runtime.
- Debugging –
Depending on the database technology, debugging stored procedures will either be very difficult or not possible at all. Some relational databases such as SQL Server have some debugging capabilities.
- Versioning –
Version control is not supported by the stored procedure.
- Cost –
An extra developer in the form of DBA is required to access the SQL and write a better stored procedure. This will automatically incur added cost.
- Portability –
Complex stored procedures will not always port to upgraded versions of the same database. This is specially true in case of moving from one database type(Oracle) to another database type(MS SQL Server).
|It is faster.||It is difficult to debug.|
|It is pre-compiled.||Need expert developer, since difficult to write code.|
|It reduces network traffic.||It is database dependent.|
|It is reusable.||It is non-portable.|
|It’s security is high .||It is expensive.|
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.
- What is Stored Procedures in SQL ?
- SQL | Difference between functions and stored procedures in PL/SQL
- Advantages and Disadvantages of SQL
- Advantages and Disadvantages of various CPU scheduling algorithms
- Advantages and Disadvantages of working in a Startup
- Advantages and Disadvantages of various Page Replacement algorithms
- Advantages and Disadvantages of various Disk scheduling algorithms
- SQL | Procedures in PL/SQL
- Different types of Procedures in MySQL
- MySQL | Creating stored function
- Difference between Stored and Derived Attribute
- Where is an object stored if it is created inside a block in C++?
- Disadvantages of DBMS
- Disadvantages of Distributed DBMS
- Advantages of Distributed database
- Advantages of Database Management System
- Advantages of DBMS over File system
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.