SQL | Stored Procedures

SQL | Procedures in PL/SQL

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