PL/SQL is a block-structured language that enables developers to combine the power of SQL with procedural statements.
A stored procedure in PL/SQL is nothing but a series of declarative SQL statements which can be stored in the database catalogue. A procedure can be thought of as a function or a method. They can be invoked through triggers, other procedures, or applications on Java, PHP etc.
All the statements of a block are passed to Oracle engine all at once which increases processing speed and decreases the traffic.
- They result in performance improvement of the application. If a procedure is being called frequently in an application in a single connection, then the compiled version of the procedure is delivered.
- They reduce the traffic between the database and the application, since the lengthy statements are already fed into the database and need not be sent again and again via the application.
- They add to code reusability, similar to how functions and methods work in other languages such as C/C++ and Java.
- Stored procedures can cause a lot of memory usage. The database administrator should decide an upper bound as to how many stored procedures are feasible for a particular application.
- MySQL does not provide the functionality of debugging the stored procedures.
Syntax to create a stored procedure
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Comments -- CREATE PROCEDURE procedure_name = , = , = AS BEGIN -- Query -- END GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE GetStudentDetails @StudentID int = 0 AS BEGIN SET NOCOUNT ON; SELECT FirstName, LastName, BirthDate, City, Country FROM Students WHERE StudentID=@StudentID END GO
Syntax to modify an existing stored procedure
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Comments -- ALTER PROCEDURE procedure_name = , = , = AS BEGIN -- Query -- END GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE GetStudentDetails @StudentID int = 0 AS BEGIN SET NOCOUNT ON; SELECT FirstName, LastName, City FROM Students WHERE StudentID=@StudentID END GO
Syntax to drop a Procedure:
DROP PROCEDURE procedure_name
DROP PROCEDURE GetStudentDetails
This article is contributed by Anannya Uberoi. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
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.