A function is a set of PL/SQL statements you can call by name. Functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User defined functions can be used as part of a SQL expression.

The data type of the return values must be declared in the header of the function. A function has output that needs to be assigned to a variable or it can be used in a SELECT statement.

Funtion can not call when it has RETURN data type as Boolean. A Function can contain more than one return statement, each exception should have a RETURN statement.

Syntax :

CREATE [OR REPLACE] 
FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
 < function_body > 
END [function_name];

Create a Function

CREATE OR REPLACE FUNCTION totalCustomers 
RETURN number IS 
 total number(2) := 0; 
BEGIN 
 SELECT count(*) into total 
 FROM customers; 
 
 RETURN total; 
END; 
/

Function TOTALCUSTOMERS compiled

Calling a Function

DECLARE 
 t number(2); 
BEGIN 
 t := totalCustomers(); 
 dbms_output.put_line('Total no.of Customers : ' || t); 
END; 
/

Total no.of Customers : 6

PL/SQL procedure successfully completed.

Example : 2

CREATE FUNCTION get_bal(acc_no IN NUMBER) 
 RETURN NUMBER 
 IS acc_bal NUMBER(11,2);
 BEGIN 
 SELECT order_total 
 INTO acc_bal 
 FROM orders 
 WHERE customer_id = acc_no; 
 RETURN(acc_bal); 
 END;
/


Function GET_BAL compiled

Calling function with select statement

SELECT get_bal(100) FROM DUAL;

GET_BAL(100)
------------
        2500

Examples :3

CREARE OR REPLACE FUNCTION
employcount ( dept_no emp.deptno%typ)
RETURN NUMBER IS
emp_count number(4);
BEGIN
SELECT count(empno) INTO emp_count
FROM emp WHERE deptno=dept_no;
RETURN emp_count;
END;
/

SELECT unique deptno,employcount(deptno)
FROM emp where employcount(deptno) > 10;

Alter Function

Use the ALTER FUNCTION statement to recompile an invalid standalone stored function. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.

ALTER FUNCTION employcount
 COMPILE;

Drop Function

Use the DROP FUNCTION statement to remove a standalone stored function from the database.

DROP FUNCTION employcount;