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