Procedures in PL/SQL

PL/SQL procedures are reusable code blocks that perform specific actions or logic within a database environment. They consist of two main components such as the procedure header which defines the procedure name & optional parameters and the procedure body which contains the executable statements implementing the desired business logic.

The procedure contains two parts:

Procedure Header

• The procedure header includes the procedure name and optional parameter list.
• It is the first part of the procedure and specifies the name and parameters

Procedure Body

• The procedure body contains the executable statements that implement the specific business logic.
• It can include declarative statements, executable statements, and exception-handling statements

Create Procedures in PL/SQL

To create a procedure in PL/SQL, use the CREATE PROCEDURE command:

Syntax:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE procedure_name
@Parameter1 INT,
@Parameter2 VARCHAR(50) = NULL,
@ReturnValue INT OUTPUT
AS
BEGIN
END
GO

Note: Procedures in PL/SQL without parameters are written without parentheses after the procedure name

Example: In this example, we will create a procedure in PL/SQL

SET ANSI_NULLS ON
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

Explanation:The stored procedure GetStudentDetails:

• Takes an optional parameter @StudentID (default = 0).
• Returns: FirstName, LastName, BirthDate, City, Country from the Students table.
• Uses SET NOCOUNT ON to suppress row count messages.
• Executes a SELECT query where StudentID = @StudentID.

Parameters in Procedures

In PL/SQL, parameters are used to pass values into procedures. There are three types of parameters used in procedures:

IN parameters

• Used to pass values into the procedure
• Read-only inside the procedure
• Can be a variable, literal value, or expression in the calling statement.

OUT parameters

• Used to return values from the procedure to the calling program
• Read-write inside the procedure
• Must be a variable in the calling statement to hold the returned value

IN OUT parameters

• Used for both passing values into and returning values from the procedure
• Read-write inside the procedure
• Must be a variable in the calling statement

Modify Procedures in PL/SQL

To modify an existing procedures in PL/SQL use the ALTER PROCEDURE command:

Syntax

ALTER PROCEDURE Syntax is:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE procedure_name
@Parameter1 INT,
@Parameter2 VARCHAR(50) = NULL,
@ReturnValue INT OUTPUT

AS

BEGIN

-- Query

END
GO

Example: In this example, we will modify a procedure in PL/SQL:

SET ANSI_NULLS ON
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

Explanation: This code updates the GetStudentDetails procedure to:

• Take @StudentID as input (default = 0)
• Return only FirstName, LastName, and City from the Students table
• Suppress row count message using SET NOCOUNT ON

Drop Procedure in PL/SQL

To drop a procedure in PL/SQL use the DROP PROCEDURE command

Syntax:

DROP PROCEDURE procedure_name

PL/SQL DROP PROCEDURE Example

In this example, we will delete a procedure in PL/SQL

DROP PROCEDURE GetStudentDetails

Important Points About Procedures in SQL

• A procedure in PL/SQL is a subprogram that can take parameters and be called to perform a specific action.
• Procedures are executed just like SQL statements.
• Procedures have two parts the specification (spec) and the body. The spec begins with the PROCEDURE keyword and ends with the procedure name and optional parameter list. The body begins with IS (or AS) and ends with END followed by an optional procedure name
• They enhance performance by reducing network traffic between the application and database.

Advantages of Procedures

• 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 of Procedures

• 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.