SQl Functions

The General Syntax to create a function is:

CREATE [OR REPLACE] FUNCTION function_name [parameters] 

RETURN return_datatype;  

IS  

Declaration_section  

BEGIN  

Execution_section 

Return return_variable;  

EXCEPTION  

exception section  

Return return_variable;  

END; 

1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.

Example :

CREATE OR REPLACE FUNCTION employer_details_func

RETURN VARCHAR(20);

IS 

emp_name VARCHAR(20); 

BEGIN 

SELECT first_name INTO emp_name

FROM emp_tbl WHERE empID = '100';

RETURN emp_name;

END;