SQL | Procedures in PL/SQL
Last Updated :
21 Mar, 2018
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.
Advantages:
- 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.
Disadvantages:
- 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
Example:
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
Example:
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
Example:
DROP PROCEDURE GetStudentDetails
Share your thoughts in the comments
Please Login to comment...