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;