Experiment No – 9 (Procedures and Functions in SQL)
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
PL/SQL Function
The PL/SQL Function is very similar to PL/SQL Procedure. The main difference between
procedure and a function is, a function must always return a value, and on the other hand a
procedure may or may not return a value. Except this, all the other things of PL/SQL procedure
are true for PL/SQL function too.
Syntax to create a function:
1. CREATE [OR REPLACE] FUNCTION function_name [parameters]
2. [(parameter_name [IN | OUT | IN OUT] type [, ...])]
3. RETURN return_datatype
4. {IS | AS}
5. BEGIN
6. < function_body >
7. END [function_name];
Here:
o Function_name: specifies the name of the function.
o [OR REPLACE] option allows modifying an existing function.
o The optional parameter list contains name, mode and types of the parameters.
o IN represents that value will be passed from outside and OUT represents that this
parameter will be used to return a value outside of the procedure.
o The function must contain a return statement.
o RETURN clause specifies that data type you are going to return from the function.
o Function_body contains the executable part.
o The AS keyword is used instead of the IS keyword for creating a standalone function.
PL/SQL Function Example
Let's see a simple example to create a function.
1. create or replace function adder(n1 in number, n2 in number)
2. return number
3. is
4. n3 number(8);
5. begin
6. n3 :=n1+n2;
7. return n3;
8. end;
9. /
Now write another program to call the function.
1. DECLARE
2. n3 number(2);
3. BEGIN
4. n3 := adder(11,22);
5. dbms_output.put_line('Addition is: ' || n3);
6. END;
7. /
Output:
Addition is: 33
Statement processed.
0.05 seconds
Another PL/SQL Function Example
Let's take an example to demonstrate Declaring, Defining and Invoking a simple PL/SQL function
which will compute and return the maximum of two values.
1. DECLARE
2. a number;
3. b number;
4. c number;
5. FUNCTION findMax(x IN number, y IN number)
6. RETURN number
7. IS
8. z number;
9. BEGIN
10. IF x > y THEN
11. z:= x;
12. ELSE
13. Z:= y;
14. END IF;
15.
16. RETURN z;
17. END;
18. BEGIN
19. a:= 23;
20. b:= 45;
21.
22. c := findMax(a, b);
23. dbms_output.put_line(' Maximum of (23,45): ' || c);
24. END;
25. /
Output:
Maximum of (23,45): 45
Statement processed.
0.02 seconds
