The named block in PL/SQL is said to be a function. The idea of procedures is similar to that of functions. The key difference between these two is that a procedure is used to perform an action and may or may not return a value, but the function should return a value. PLSQL Function helps in extending the sql statements as well as modularize and abstract the complex business logic. Like a procedure, a function is a put away program i.e, it is pre-compiled and saved in the database with a name. A function has all advantages of a stored program like re-usability and execution optimization. A PLSQL Function always returns a value and is used in expressions to assign a variable or to directly fetch values from the sql statements by performing insert, update or delete operations on the DML statements.

The below example illustrates how to create or replace a function. [c]SQL> create or replace function calcsum(a number,b number)return number is 2 s number(10); 3 begin 4 s:=a+b; 5 return s; 6 end; 7 / Function created. SQL> select calcsum(10,20)from dual; CALCSUM(10,20) -------------- 30[/c] In the above example, the function is created by assigning values 10 and 20 and it calculates the sum and gives the output as 30.

Oracle database provides a rich set of build_in functions that are available as a part of the PL/SQL environment and contains a lot of commonly used tasks. Some of these functions can be directly called in SQL statement and subprograms in PL/SQL. There are a rich set of functions such as These functions takes a number parameter and returns a number.

Round Functions

Round functions round the number to the closest integer.

CEIL Functions

CEIL function gives back the values greater than or equal to the given number input. For instance, if the input is 30.9, then it will return 31.

ABSOLUTE or ABS Functions

Absolute function takes the input values and returns positive values. Suppose, if the value is -123, then it will return +123.

LTRIM Functions

LTRIM is a function used to trim spaces on the left of a character.

LPAD Functions

LPAD is a function used to pad the character arguments passed to the left to make it a certain length.

SYSDATE Functions

SYSDATE will return the current system date.

SYSTIMESTAMP Functions

SYSTIMESTAMP function returns the current time stamp.

TO_DATE Functions

TO_DATE function can be called to convert a character literal 10-jan-2016 to a date using the TO_DATE function.

PLSQL Function provides three types of parameters, they are: The IN parameter can be referenced by functions or procedures. The function and parameters cannot overwrite the parameter values.

OUT Parameter

This parameter cannot be referenced by the function or procedure. The function and parameters can overwrite the parameter values.

IN OUT Parameter

The parameter can be referenced by the function or procedure. The function and parameters can overwrite the parameter values.

The  below example is a sample program of PL/SQL. [c]SQL> create or replace function FindCourse(name_in IN varchar2) 2 RETURN NUMBER 3 IS 4 cnumber number; 5 cursor c1 is 6 SELECT course_number 7 FROM course_tb1 8 WHERE course_name=name_in; 9 BEGIN 10 open c1; 11 fetch c1 into cnumber; 12 if c1%notfound then 13 cnumber:=999; 14 end if; 15 close c1; 16 RETURN cnumber; 17 EXCEPTION 18 WHEN OTHERS THEN 19 raise_application_error(-20001,'An error was encountered-'||SQLCODE||'-ERRO R-'||SQLERRM): 20 END; 21 / [/c] The above example 'Find course' consists of name_in parameter and it doesn't return any number, otherwise, it returns 9999 error. 

For dropping a PLSQL Function, system privileges are required. Dropping a function can be done using the command DROP followed by the function_name like drop get_emp_count.

The below example describes the dropping of a function. [c]SQL> create or replace function fun1(no in number) 2 return varchar2 3 is name varchar2(20); 4 begin 5 select ename into name from emp1 where eno=no; 6 return name; 7 end; 8 / SQL@fun1 Function created. PL/SQL program successfully executed. SQL> drop function fun1; Function dropped. PL/SQL program successfully executed.[/c] In the above example, PLSQL Function name fun1 has been created successfully and for deleting that function use the command drop as follows: drop fun1., which will drop the entire function name fun1.

• PLSQL Function - Is a collection of statements in the PL/SQL and can be called by name.
• Oracle provided function - Oracle database gives a great arrangement of build_in functions and are a part of PL/SQL environment.
• Types of parameters - Defines the type of parameters like: IN, OUT, and IN OUT PARAMETER.
• Function drop - This is used for dropping a function from the database.