Procedures in PL/SQL

A Procedure in PL/SQL is a subprogram containing a series of declarative SQL statements that can take parameters and be called to perform a specific action.

These PL/SQL procedures are stored in the database catalog. 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 the Oracle engine all at once which increases processing speed and decreases the traffic.

Here, we will learn about Procedures in PL/SQL with examples and learn how to create, modify, and delete procedures. It is a very important concept of database and you might face several PL/SQL Procedure interview questions during the job hunt, so understand the concept carefully.

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

Syntax

CREATE PROCEDURE syntax is:

SET ANSI_NULLS ONSET QUOTED_IDENTIFIER ONGO— Comments —CREATE PROCEDURE procedure_name @Parameter1 INT, @Parameter2 VARCHAR(50) = NULL, @ReturnValue INT OUTPUT ASBEGIN— Query —ENDGO

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

Create Procedures in PL/SQL 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

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

Syntax

ALTER PROCEDURE Syntax is:

SET ANSI_NULLS ONSET QUOTED_IDENTIFIER ONGO — Comments — ALTER PROCEDURE procedure_name @Parameter1 INT,
@Parameter2 VARCHAR(50) = NULL,
@ReturnValue INT OUTPUT ASBEGIN — Query — ENDGO

PL/SQL Modify Procedure 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

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

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

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

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