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
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.
Contact Us