PL/SQL Functions

PL/SQL Functions

Function is one of the two available Named Blocks in Pl/SQL, the other being Procedure. In PL/SQL, a function takes one or more parameter and returns one value. 

The main difference between a PL/SQL function and a PL/SQL procedure is that a function returns the value while a procedure does not.

The syntax for a function in PL/SQL is as follows:

Syntax

• A PL/SQL Function must always contain a return type specified. It helps to specify the Return Datatype that is supposed to be passed from the function.
• IN represents that argument value will be passed from outside the Function. It is a read-only parameter. Parameters are passed by reference. Inside the procedure or a sub-program, an IN Parameter acts as a constant. It cannot be assigned a value. It is the default mode of parameter passing.
• An OUT parameter returns a value to the calling program. OUT represents that this parameter will be used to return a value outside of the Function. The actual parameter must be variable and it is passed by value. Inside the subprogram, an OUT parameter acts like a variable.
• An IN OUT parameter passes an initial value to the sub-program and returns an updated value to the caller. We can read and write values using this parameter.

Delete or Drop a PL/SQL Function

The DROP keyword is used in PL/SQL to delete a function from a previously created database.


Syntax

Example

This will create your function. You can verify it with the below screenshot. In order to execute this function, we need to execute using a PL/SQL block. This is as follows:

Example

The above PL/SQL block will execute the function i.e., the function is called in here and the return value created in the function before is stored in the variable total mentioned here.

PL/SQL Aggregate Functions

PL/SQL database also provides us with built-in functions for a particular purpose just like they’re available in other languages. Aggregate functions are pre-defined by the compiler and are kept in the library to be used by the programmer as and when the need arises. 

SQL aggregate functions return a single value, calculated from values in a column. Some of the aggregate functions used in PL/SQL are as follows:

• FIRST() – Returns the first value
• LAST() – Returns the last value
• AVG() – Returns the average value
• SUM() – Returns the sum of the values
• MAX() – Returns the largest value
• MIN() – Returns the smallest value
• COUNT() – Returns the number of rows