PL/SQL Function

PL/SQL Function is same as PL/SQL Procedure, The only difference is that function must return a value and a procedure may or may not return a value



The syntax for creating function starts with CREATE OR REPLACE FUNCTION

See the simple syntax for creating a PL/SQL function




CREATE [OR REPLACE] FUNCTION function_name 
(parameter1 [IN | OUT | IN OUT] datatype, parameter2 [IN | OUT | IN OUT] datatype)
RETURN datatype

IS
 <<Declaration Section>>

BEGIN 

  <<Execution Section>> 

EXCEPTION

<<Exception Handling>>

END;

See this example of creating PL/SQL function that has IN and OUT parameters both, This function takes EmployeeID as input parameter and returns Email as default return value and FirstName and LastName in output parameters

-- Header Section with IN and OUT Parameters
CREATE OR REPLACE FUNCTION FN_GET_EMPNAME
(EMP_NO IN NUMBER, F_NAME OUT VARCHAR2,L_NAME OUT VARCHAR2) 
-- RETURN Variable datatype
RETURN VARCHAR2 IS
   EMAIL_V VARCHAR2(50) := 'N';
BEGIN
--Statements that returns First Name , Last Name and Email of an Employees
    SELECT FIRST_NAME INTO F_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_NO;
    SELECT LAST_NAME INTO L_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_NO;
    SELECT EMAIL INTO EMAIL_V FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_NO;
    RETURN EMAIL_V;
END;

and to execute this PL/SQL function

DECLARE 
email varchar2(100);
fname varchar2(100);
lname varchar2(100);
BEGIN
email:=FN_GET_EMPNAME(101,fname,lname);
dbms_output.put_line('First Name is- '||fname||' and Last Name is- '||lname);
END;

Output is

Cheers :) Happy Learning