How to make Function in Oracle?

What is Oracle Functions?

Functions are a combination of SQL and PL/SQL. The difference between in procedure and function is that function should return the value but in procedure return is optional. The sample code is given below.
——————————————————————————–

SQL> CREATE TABLE  EMP
2  (
3  EMP_CODE        NUMBER,
4  EMP_NAME        VARCHAR2(100),
5  EMP_SALARY      NUMBER,
6  USER_NAME       VARCHAR2(100),
7  DATE_TIME       DATE
8  );
Table created.

SQL> INSERT INTO EMP
2  VALUES (100,’ROBERT’,50000,’ALENA’,SYSDATE);
1 row created.

SQL> CREATE OR REPLACE FUNCTION FUN_GET_EMP_NAME(V_EMP_CODE NUMBER) RETURN VARCHAR2 IS
2  V_NAME VARCHAR2(100);
3  BEGIN
4  SELECT EMP_NAME INTO V_NAME FROM EMP WHERE EMP_CODE = V_EMP_CODE;
5  RETURN V_NAME;
6  EXCEPTION WHEN OTHERS THEN RETURN NULL;
7  END;
8  /
Function created.

SQL>
SQL> SELECT FUN_GET_EMP_NAME(100) FROM DUAL;
FUN_GET_EMP_NAME(100)
——————————————————————————–
ROBERT
——————————————————————————-