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 and optional parameters and the procedure body which contains the executable statements implementing the desired business logic.

In this article, We will learn about PL/SQL Procedures in detail by understanding various examples and so on.

Procedures in PL/SQL

A PL/SQL procedure is a reusable block of code that contains a specific set of actions or logic.

The procedure contains two parts:

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

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

CREATE PROCEDURE syntax is:

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

Parameters in Procedures

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

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

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

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

Drop Procedure in PL/SQL

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

Syntax

DROP PROCEDURE syntax is:

DROP PROCEDURE procedure_name

PL/SQL DROP PROCEDURE Example

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

DROP PROCEDURE GetStudentDetails

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.

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.

Conclusion

PL/SQL procedures are essential for efficient database management, providing a way to encapsulate and execute business logic. They help in enhancing performance by minimizing network traffic and improving execution efficiency. Despite their advantages, procedures can increase memory usage and lack debugging functionality in some databases.