Open In App

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:

Disadvantages:

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
Article Tags :
SQL