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.
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 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
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 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
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 withIS
(orAS
) and ends withEND
followed by an optional procedure name- They enhance performance by reducing network traffic between the application and database.
Contact Us