Subprograms: PL SQL Procedures And Functions With Examples

This tutorial explains PL SQL Subprograms types mainly Procedures and Functions, different parameter passing methods, and how to create/delete Procedures and Functions in PL/SQL:

In the PL SQL Operators And Control Statements Tutorial of the PL/SQL series, we learned about different PL SQL Operators and Control Statements like if-then-else, for loop, while loop, etc. with sample code examples.

In this article, we will discuss the subprograms that PL/SQL supports. Also, we shall explore the procedures and functions that are available in PL/SQL.

Moreover, we will also touch base on some of the built-in PL/SQL functions.

PL SQL Subprograms

A PL/SQL block of code that accepts parameters and can be invoked is called a subprogram.

There are two types of subprograms: Procedures and Functions. A function is used for calculating value and a procedure is used to do an action. A subprogram can be considered as a module that is integrated to build a large program. This helps to give a modular architecture.

A subprogram consists of the following sections:

#1) Declarative Section

It is not a mandatory part of the code and it is optional to mention the keyword DECLARE. It has the constants, variables, nested subprograms, exceptions, and so on. These belong to a particular subprogram and have no existence once the subprogram is no longer present.

#2) Executable Section

It is a required part of the code. It is used for assigning values, flow, and manipulation of data. Thus this part of the code always has a specific task to carry out.

#3) Exception Handling Section 

It is not a mandatory part of the code and it primarily takes care of the exceptions encountered during execution.

A subprogram can be built inside a package, within a block of PL/SQL or in a schema.

A schema level subprogram is an independent one that mainly deals with the CREATE function or procedure. It is stored in the database and we can perform delete or drop operations on them.

A subprogram defined inside a package is called a packaged subprogram. It remains in the database until the package is deleted with drop operation.

A block of PL/SQL can be called with the help of functions (returns a value) and procedures (does not return value).

Advantages Of Subprograms

The advantages of PL/SQL subprograms are listed below:

1. Subprograms give extensibility which means we can customize the code as per our needs.
2. It gives modularity to the code which means we can separate our program as per modules or clusters.
3. It gives easy maintainability and scalability to the code.
4. It gives the reusability of the code.
5. It also helps to achieve abstraction in the code.

Modes Of Parameter In PL/SQL Subprogram

Modes of Parameter are of three types IN, OUT, and INOUT. These can be described as follows:

#1) IN

This is a constant parameter inside a subprogram. It allows passing a value to a subprogram. It is not writable and is not assigned a value. An IN parameter is capable of passing an expression, initialized variable, constant and literal. It is the inbuilt parameter passing mechanism in PL/SQL. It can be assigned a default value. But by doing so, it is ignored from subprogram calls.

Here, the actual parameter is passed by reference.

#2) OUT 

This parameter return values to the calling subprogram. It is treated as a variable within a subprogram implying that it can be used as a local variable. OUT parameter value can be modified and referenced. An actual parameter must be a variable and always passed by value. It is a read-write variable within a subprogram.

#3) INOUT 

This parameter can be used for getting both input and output from the subprograms. It can be treated as an initialized variable within a subprogram. Thus it can be assigned a value and also assigned to another variable. An actual parameter is always passed by value.

Code implementation with IN and OUT parameters:

 
DECLARE 
   i number; 
   j number; 
   k number;
   PROCEDURE findAdd(num1 IN number, num2 IN number, sum OUT number) IS 
BEGIN 
   sum := num1 + num2;
END; 
BEGIN 
    i:= 5; 
    j:= 5; 
    findAdd(i, j, k); 
    dbms_output.put_line(' The sum is : ' || k); 
END; 
/

The output of the above code should be:

Let us see another example.

Code implementation with IN OUT parameter:

DECLARE 
    num number; 
PROCEDURE addNum(i IN OUT number) IS 
BEGIN 
    i := i + i; 
END; 
BEGIN 
    num := 1;
    addNum(num); 
    dbms_output.put_line('The sum is ' || num); 
END; 
/

The output of the above code should be:

Parameter Passing Methods In PL SQL

Parameter passing in PL/SQL can be done by Positional Notation, Named Notation, and Mixed Notation.

#1) Positional Notation: Here the first actual parameter is acting as the first formal parameter, and the second actual parameter is acting as the second formal parameter, and so on.

Syntax:
findAdd(i, j, k);

#2) Named Notation: Here the actual parameter is linked with the formal parameter with the help of arrow notation (=>).

Syntax:
findAdd(num1=>i, num2=>j, sum=>k);

#3) Mixed Notation: Here, we can have a mixture of both positional and named notation. However, the positional notation should always be before named notation.

Syntax:
findAdd(i, num2=>j, sum=>k);

PL SQL PROCEDURES

The procedure is a part of the PL/SQL subprogram which performs a particular task. All procedures possess a unique name and are an independent block of code. A procedure may contain a nested block or can be described inside other packages or blocks.

A procedure has parameters included while calling a procedure. They cannot be used directly by SELECT statements. We can build a procedure with a CREATE OR REPLACE statement.

Create Procedure 

The syntax for creating a procedure:

 
CREATE [OR REPLACE] PROCEDURE name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
   Block of code
 EXCEPTION
     Exception handling
 END name;

Here,
‘name’ is the procedure name.
‘OR REPLACE’ keyword informs the compiler about updating a procedure.
‘AS’ keyword is used if the procedure is standalone.
‘IS’ keyword is used if the procedure is a nested one.
‘Block of code’ is the actual implementation logic of the program.
‘Exception handling’ contains error checking at the runtime.

The parameter set defines the types, modes, and nature of the parameters. OUT parameter type points to the value that shall be returned outside the procedure. IN parameter type points to the value that shall be passed from outside.

Code implementation:

CREATE OR REPLACE PROCEDURE firstprocedure 
  AS 
BEGIN 
        dbms_output.put_line('Software Testing Help!'); 
END; 
/

The output of the above code should be:

This is a standalone procedure which can be executed by the ways listed below:

#1) Using a PL/SQL block and then calling the name of the procedure.

Coding implementation:

BEGIN 
    firstprocedure; 
END; 
/

The output of the above code should be:

#2) With the help of the keyword EXECUTE.

EXECUTE firstprocedure;

Delete Procedure 

We can delete the procedure created with the help of the following statement:

DROP PROCEDURE <<procedure-name>>;

Coding implementation:

 DROP PROCEDURE firstprocedure;

The output of the above code should be:

PL SQL Functions

The functions are similar to procedures in PL/SQL except for the fact that it has the ability to return a value (specified with keyword RETURN) and performs computation tasks. It has a unique name and acts as an independent block of code.

The data type of a function is set at the time of the creation of function. A function may contain a nested block or describe inside other packages or blocks.

A function can return values with the help of the RETURN keyword and OUT parameter.

Create Function

The syntax for creating a function:

CREATE [OR REPLACE] FUNCTION name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN statement with data type
{IS | AS}
BEGIN
   Block of code
EXCEPTION
   Exception handling
END name;

Here,
‘name’ is the function name.
‘OR REPLACE’ keyword informs the compiler about updating a function.
‘AS’ keyword is used if the function is standalone.
‘IS’ keyword is used if the function is a nested one.
‘Block of code’ is the actual implementation logic of the program.
‘Exception handling’ contains error checking at the runtime.

The parameter set defines the types, modes, and nature of the parameters. OUT parameter type points to value to be returned outside the function. IN parameter type points to the value that shall be passed from outside.

Let us consider a table named TURORIAL.

We have created a table with the SQL statement given below:

CREATE TABLE TURORIAL(
CODE INT NOT NULL,
SUBJECT VARCHAR(15) NOT NULL,
PRICE INT NOT NULL,
REVIEWS VARCHAR (10) NOT NULL,
PRIMARY KEY (CODE)
);

Then we have inserted values to this table with SQL statements given below:

INSERT INTO TURORIAL (CODE,SUBJECT,PRICE,REVIEWS)
VALUES (1, 'Automation', 150, 'five stars');
INSERT INTO TURORIAL (CODE,SUBJECT,PRICE,REVIEWS)
VALUES (2,'Manual',100, 'four stars');
INSERT INTO TURORIAL (CODE,SUBJECT,PRICE,REVIEWS)
VALUES (3, 'PLSQL', 200, 'five stars');

Code implementation:

CREATE OR REPLACE FUNCTION sumPrice
RETURN number IS 
   addition number(4) := 0; 
BEGIN 
   SELECT sum(PRICE) into addition
   FROM TURORIAL; 
   RETURN addition; 
END; 
/

The output of the above code should be:

Next, we have to call the function created in order to perform its assigned task. The format of calling a function is to specify the name of the function with its parameters. The called function is able to do its job if it has a return statement or if the end of the logic is reached. Then the actual program execution resumes again.

Code implementation to show function calling of sumPrice():

BEGIN 
dbms_output.put_line('Addition of price :' || sumPrice()); 
END; 
/

The output of the above code should be:

Delete Function

We can delete the procedure created with the help of the following statement:

DROP FUNCTION <<function-name>>;

Coding implementation:

DROP FUNCTION sumPrice;

The output of the above code should be

A recursive function is a function which calls itself to perform a specific task.

PL/SQL Functions Vs Procedures

Default Functions In PL/SQL

#1) PL SQL String Functions

• LENGTH(string): To get the length of the string.
• LOWER(string): To get the lower case of the string.
• UPPER(string): To get the upper case of the string.
• LTRIM (string): To get the string without leading white spaces.
• RTRIM (string): To get the string without trailing white spaces.
• SUBSTR (string, begin, length): To get the string from the beginning point to the length of the substring.
• INSTR (string, search, begin, occurrence): To get the position of the search string in the given string.
• TRIM (string): To get the string without leading or trailing white spaces.

#2) Conversion Functions

• TO_DATE (string, format): Conversion to the specified date format.
• TO_CHAR (): Conversion to the character data type.
• TO_NUMBER(string, format): Conversion from string to the specified number format.

#3) PL SQL Date Functions

• SYSDATE: To get the present date and time of the server.
• TRUNC: To get the date rounded to the lower value.
• ROUND: To get the date to the closest range (high or low).
• ADD_MONTHS (date, months): To get a date by adding months to the date.

Recommended reading =>> Substring function in MySQL

Frequently Asked Questions And Answers

Q #1) What is the subprogram in PL/SQL?

Answer: A PL/SQL block with a unique name can be invoked multiple times. This is called a subprogram. There are two types of subprograms – procedures and functions.

Q #2) What is procedure and function in PL/SQL?

Answer: A procedure or function is a collection of PL/SQL and SQL statements that can execute a specific task. A procedure can do an action and not compulsorily return a value. But a function will return a value every time.

Q #3) How functions and procedures are called in a PL/SQL block?

Answer: For calling functions or procedures in a PL/SQL block, we need to mention the name, the parameters, BEGIN- END code, and exceptions if any, of the procedures or the function.

Q #4) Why do we use the function in PL/SQL block?

Answer: A function can be utilized as a part of an SQL statement. This means a function can be present in the update, select, or merge operations. A function always returns a value and at the same time does a computational task.

Q #5) What is the difference between function and procedure?

Answer: A function only has input parameters while a procedure contains both output and input parameters. A function compulsorily returns a value whereas a procedure optionally may return multiple or zero values. A function can be called inside a procedure but the reverse is not true.

Q #6) Can we use commit in function in Oracle?

Answer: Yes, we can use commit in function provided the function is an autonomous transaction.

Q #7) Can DML be used in functions?

Answer: No, we cannot use DML statements in functions. The functions are only capable of read-only operations in the database.

Conclusion

We have covered topics like PL SQL subprograms – procedures and functions in detail, PL/SQL default functions like String function and Date function, differences between procedures and functions, and some of the built-in PL/SQL functions.

<<PREV Tutorial | NEXT Tutorial>>