Oracle PL/SQL Stored Procedure & Functions with Examples

What are PL/SQL Subprograms?

In this tutorial, you see a detailed description of how to create and execute the named blocks, procedures and functions.

Procedures and functions are subprograms that can be created and saved in the database as database objects. They can be called or referred to inside other blocks too.

We also cover the major differences between these two subprograms and discuss the Oracle built-in functions.

Terminologies in PL/SQL Subprograms

Before learning about PL/SQL subprograms, we discuss the various terminologies that are part of these subprograms.

Parameter

A parameter is a variable or placeholder of any valid PL/SQL data type through which the PL/SQL subprogram exchanges values with the main code. This parameter allows input to the subprograms and extraction of values from them.

• These parameters should be defined along with the subprograms at the time of creation.
• They are included in the calling statement to interact with the subprograms.
• The data type of the parameter in the subprogram and the calling statement should be the same.
• The size of the data type should not be mentioned at the time of parameter declaration, as the size is dynamic.

Based on their purpose, parameters are classified as:

1. IN Parameter
2. OUT Parameter
3. IN OUT Parameter

IN Parameter

• Used for giving input to the subprograms.
• It is a read-only variable inside the subprograms; its value cannot be changed inside the subprogram.
• In the calling statement, it can be a variable, a literal value, or an expression, such as ‘5*8’ or ‘a/b’.
• By default, parameters are of IN type.

OUT Parameter

• Used for getting output from the subprograms.
• It is a read-write variable inside the subprograms; its value can be changed inside them.
• In the calling statement, it should always be a variable to hold the value from the subprogram.

IN OUT Parameter

• Used for both giving input and getting output from the subprograms.
• It is a read-write variable inside the subprograms; its value can be changed inside them.
• In the calling statement, it should always be a variable to hold the value from the subprogram.

The parameter type should be mentioned at the time of creating the subprograms.

RETURN

RETURN is the keyword that instructs the compiler to switch control from the subprogram to the calling statement. In a subprogram, RETURN simply means that control needs to exit the subprogram; once the controller finds RETURN, the code after it is skipped.

Normally, the parent or main block calls the subprograms, and control shifts from the parent block to the called subprogram. RETURN in the subprogram returns control back to the parent block. In the case of functions, the RETURN statement also returns a value, whose data type is mentioned at the time of function declaration.

What is a Procedure in PL/SQL?

A Procedure in PL/SQL is a subprogram unit that consists of a group of PL/SQL statements that can be called by name. Each procedure has its own unique name and is stored in the Oracle database as a database object.

Note: A subprogram is nothing but a procedure, and it needs to be created manually as per the requirement. Once created, it is stored as a database object.

The characteristics of a procedure subprogram unit in PL/SQL are:

• Procedures are standalone blocks that can be stored in the database.
• They can be called by their name to execute the PL/SQL statements.
• They are mainly used to execute a process.
• They can have nested blocks, or be nested inside other blocks or packages.
• They contain a declaration part (optional), an execution part, and an exception-handling part (optional).
• Values can be passed into or fetched from a procedure through parameters.
• These parameters should be included in the calling statement.
• A procedure can have a RETURN statement to return control to the calling block, but it cannot return any value through RETURN.
• Procedures cannot be called directly from SELECT statements; they can be called from another block or through the EXEC keyword.

Syntax

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE PROCEDURE instructs the compiler to create a new procedure. The keyword ‘OR REPLACE’ instructs it to replace the existing procedure (if any) with the current one.
• The procedure name should be unique.
• The keyword ‘IS’ is used when the stored procedure is nested inside some other block. If the procedure is standalone, ‘AS’ is used. Apart from this coding standard, both have the same meaning.

Example 1: Creating a Procedure and calling it using EXEC. In this example, we create an Oracle procedure that takes a name as input and prints a welcome message as output, using the EXEC command to call it.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Code Explanation:

• Code line 1: Creating the procedure with name ‘welcome_msg’ and one parameter ‘p_name’ of ‘IN’ type.
• Code line 4: Printing the welcome message by concatenating the input name.
• The procedure is compiled successfully.
• Code line 7: Calling the procedure using EXEC with the parameter ‘Guru99’. The procedure executes and prints “Welcome Guru99”.

What is a Function?

A function is a standalone PL/SQL subprogram. Like a procedure, a function has a unique name and is stored as a PL/SQL database object. Its characteristics are:

• Functions are standalone blocks used mainly for calculation.
• A function uses the RETURN keyword to return a value, whose data type is defined at the time of creation.
• A function should either return a value or raise an exception; return is mandatory in functions.
• A function with no DML statements can be called directly in a SELECT query, whereas a function with DML can only be called from other PL/SQL blocks.
• It can have nested blocks, or be nested inside other blocks or packages.
• It contains a declaration part (optional), an execution part, and an exception-handling part (optional).
• Values can be passed into or fetched from the function through parameters.
• These parameters should be included in the calling statement.
• A function can also return a value through OUT parameters in addition to using RETURN.
• Since it always returns a value, the calling statement always uses an assignment operator to populate a variable.

Syntax

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE FUNCTION instructs the compiler to create a new function. ‘OR REPLACE’ instructs it to replace the existing function (if any) with the current one.
• The function name should be unique.
• The RETURN data type should be mentioned.
• The keyword ‘IS’ is used when the function is nested inside some other block. If the function is standalone, ‘AS’ is used.

Example 1: Creating a Function and calling it using an anonymous block. In this program, we create a function that takes a name as input and returns a welcome message, using an anonymous block and a SELECT statement to call it.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN ('Welcome '|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func ('Guru99');
dbms_output.put_line(lv_msg);
END;
/
SELECT welcome_msg_func('Guru99') FROM DUAL;

Code Explanation:

• Code line 1: Creating the function with name ‘welcome_msg_func’ and one parameter ‘p_name’ of ‘IN’ type.
• Code line 2: Declaring the return type as VARCHAR2.
• Code line 5: Returning the concatenated value ‘Welcome’ and the parameter value.
• Code line 8: Anonymous block to call the above function.
• Code line 9: Declaring the variable with the same data type as the return type of the function.
• Code line 11: Calling the function and populating the return value into the variable ‘lv_msg’.
• Code line 12: Printing the variable value. The output is “Welcome Guru99”.
• Code line 14: Calling the same function through a SELECT statement. The return value is directed to standard output.

Similarities Between a Procedure and a Function

• Both can be called from other PL/SQL blocks.
• If an exception raised in the subprogram is not handled in its exception-handling section, it propagates to the calling block.
• Both can have as many parameters as required.
• Both are treated as database objects in PL/SQL.

Procedure vs Function: Key Differences

Built-in Functions in PL/SQL

PL/SQL contains various built-in functions to work with string and date data types. Here we see the commonly used functions and their usage.

Conversion Functions

These built-in functions convert one data type to another.

String Functions

These functions are used on the character data type.

Date Functions

These functions are used for manipulating dates.

FAQs