A Full Guide on Procedure and Function in PL SQL

Here, we have shown PL SQL procedures and functions with examples in detail.

What is PL SQL?

PL SQL is a short form to Procedural Language extensions to SQL.
PL SQL is a block-structured language for programmers that help them to combine the power of SQL with procedural statements.
It is Oracle Corporation’s procedural extension.
This procedural language is available in the Oracle database, Times Ten in-memory database, and IBM Db2.
At run-time, both PL/SQL and SQL bring optimal efficiency by running within the same server process.
PL SQL ensures effective and undisturbed processing of SQL statements by enhancing the portability, security, and robustness of the database.
PL SQL simply means instructing the compiler on what to do through SQL and about how to do it through its procedural way.
PL SQL gives more control to programmers with the use of loops, conditions, and object-oriented concepts.

What is Procedure and Function in PL SQL?

PL SQL has two subprograms, known as procedures and functions.
Usually, a procedure is used to perform an action and a function to compute a value.
Subprograms in PL SQL have a declarative part, an executable part, and an optional exception-handling part.
These subprograms can be created and saved in the database as database objects.
Procedure and function subprograms in PL SQL return a single value which is mainly used to compute and return a value.
These subprograms are simply a collection of PL/SQL and SQL statements that can execute a specific task.

How to Create a Stored Procedure and Function in PL SQL?

When a block of code for PL SQL stored procedure or function is written, they are compiled by the Oracle engine. Once compiled, it is stored as a database object. The stored procedure or function block of code is made up of three parts:

Declarative Part: In this part, variables, constants, cursor, or exceptions that are to be used by procedure or function are declared.
Executable Part: In this part, the definition of the procedure or function that is created is written. This part also consists of PL/SQL or SQL statements assigning values, manipulating data, and controlling execution.
Exception Handling Part: This last part is optional and in it expected exceptions are written which may come up during the execution of code written in the executable part.

Advantages of Stored Procedure and Function in PL SQL

Before we uncover PL SQL procedure example, you must first be acquainted with the advantages of procedure and function subprograms.

Enhance database performance: Oracle engine helps in the automatic compilation. Also, whenever a PL SQL call procedure or function is done, the Oracle engine loads the compiled code in SGA, System Global Area, which helps in execution faster.
Reusability and no Redundancy: The number of lines of code cannot be written repeatedly because the same block of code for a procedure or function can be called any number of times on multiple data.
Security: The security of database is also maintained with the use of stored procedures or functions as it helps to control the usage and their access by granting permission to users. Although, permission to edit or manipulate the database is not granted to users.
Integrity: Another advantage of the use of PL SQL stored procedure or function is that it ensures integrity as they are stored as database objects by Oracle engine.
Save Memory: One of many benefits of stored procedures or functions is that they share memory which helps in saving memory as a single copy of either a procedure or a function that can be loaded by a number of users who have permission to do the same.

Also Read: How to Fix javascript:void(0) Error

Syntax for Creating Stored Procedure

You can look at the syntax below that is used to create a stored procedure in Oracle:

CREATE OR REPLACE PROCEDURE <procedure_name>
(<variable_name>IN/OUT/IN OUT <datatype>,
<variable_name>IN/OUT/IN OUT <datatype>,...) IS/AS
variable/constant declaration;
BEGIN
-- PL/SQL subprogram body;
EXCEPTION
-- Exception Handling block ;
END <procedure_name>;

syntax to create a stored procedure in oracle

In the above code:

Procedure_name represents the name of the procedure.
Variable_name represents the name of the variable used in the stored procedure.
Create or Replace procedure represents a keyword used for specifying the name of the procedure to be created.
Begin, Exception, and End represents keywords used to indicate different sections of the procedure is created.
IN/OUT/IN OUT represents parameter modes wherein, IN refers to READ ONLY mode which is used for a variable via which it will accept the value from the user. This is the default parameter mode.
OUT refers to WRITE ONLY mode which is used for a variable that returns the value to the user.
IN OUT refers to READ and WRITE mode which is used for a variable that will either accept a value or return a value to the user.
<procedure_name> represents an end to the procedure definition. You can also simply use END in its place.

PL SQL Procedure Example

For the procedure code below, here is a simple example that will clearly demonstrate the use of the stored procedure for adding two numbers:

set serveroutput on;
CREATE OR REPACE PROCEDURE Sum ( where a IN number, b IN number) IS c number;
BEGIN
c := a+b;
dbms_output.put_line ('Sum of two nos= '|| c);
END Sum;

In case of calling the procedure, the following code will be executed:

set serveroutput on;
DECLARE
x number;
y number;
BEGIN
x := &x;
y := &y;
Sum(x,y);
END;
Where:
Enter value for x: 10
Enter value for y: 20
Sum of two nos: 30

PL SQL procedure successfully created.

Syntax for Creating Function in PL SQL

Now that you are aware of how to create syntax for stored procedure and its example, it is time to shed some light on creating functions in PL/SQL:

CREATE OR REPLACE FUNCTION <function_name>
(<variable_name> IN <datatype>,
<variable_name> IN <datatype>,...)
RETURN <datatype> IS/AS
variable/constant declaration;
BEGIN
-- PL/SQL subprogram body;
EXCEPTION
-- Exception Handling block ;
END <function_name>;

syntax to create functions

In the above code:

Function_name represents the function’s name.
Variable_name represents the variable name for the variable used in the function.
Create or Replace function represents a keyword used for the name of the function to be created.
IN represents READ ONLY mode which is used for a variable by which it will accept the value from the user. This is the default parameter.
Return represents a keyword that is followed by a datatype specifying datatype of a value that the function is going to return.

Also Read: Fix Command Failed With Error Code 1 Python Egg Info

PL SQL Function Example

Below given is an example of the PL SQL function which demonstrates the use of the function for adding two numbers:

set serveroutput on;
CREATE OR REPLACE FUNCTION Sum(a IN number, b IN number) RETURN Number IS
c number;
BEGIN
c := a+b;
RETURN c;
END;

function to add two numbers syntax

In case of calling the function sum, the following code will be executed:

set serveroutput on;
DECLARE
no1 number;
no2 number;
result number;
BEGIN
no1 := &no1;
no2 := &no2;
result := Sum(no1,no2);
dbms_output.put_line(‘Sum of two nos=’||result);
END;
Where:
Enter value for no1: 5
Enter value for no2: 5
Sum of two nos: 10

PL SQL procedure successfully created.

syntax to call function sum

Read the next section to know about PL SQL exit procedure.

What is PL SQL Exit Procedure?

Exit helps when the statement exits the current iteration of the loop once the condition in its WHEN clause is satisfied, and transfers the control to the end of the loop currently going on. Exit statements cannot be used outside the loops and are meant for exiting a loop either unconditionally using Exit and Continue statements or conditionally using Exit When and Continue When statements.

Difference Between Stored and Function Procedure

Now that you are aware of procedure and function in PL SQL, it is finally time to know the basic difference between the two as well:

Stored procedure may or may not return a value to the calling part of the program whereas, function returns a value to the calling part of the program.
Stored procedure returns a value using OUT parameter whereas, function returns the value using RETURN.
Stored procedure uses IN, OUT, IN OUT parameter whereas, function uses only IN parameter.
Stored procedure does not specify the datatype the value if it is going to return after a calling made to it whereas, function specifies the datatype of the value which is going to return after a calling made to it.
Stored procedure cannot be called from the function block of code whereas, function can be called from the procedure block of code.

Recommended:

We hope that our doc on procedure and function in PL SQL was helpful and successful in answering all your doubts on PL SQL and their subprograms, procedure & function, PL SQL exit procedure and more. If we helped you out in any way or there are more queries from your side, please leave your comments below to let us know.

A Full Guide on Procedure and Function in PL SQL