HOW MANY TYPES OF FUNCTIONS ARE IN ORACLE ?

ORACLE PRE-DEFINED FUNCTION: Oracle has its own functions that's we called as PRE-DEFINED functions. This function is of two types

A. SINGLE ROW FUNCTION (RETURN ONE RESULT PER ROW)
    1. CHARACTER FUNCTION:- CHARACTER FUNCTION ARE OF TWO TYPES

     >> CASE MANIPULATION FUNCTION
           UPPER,
           LOWER,
           INITCAP,                      
                           
     >> CHARACTER MANIPULATION FUNCTION
           INSTR ,
           SUBSTR,
           LENGTH,
           CONCAT,
           REPLACE,
           TRIM/LTRIM/RTRIM,
           LPAD/RPAD



    2. NUMBER FUNCTION
        ABS
        CEIL
        FLOOR 
        ROUND (number)
        TRUNC (number)




    3. DATE FUNCTION
        CURRENT_DATE
        SYSDATE
        SYSTIMESTAMP
        TO_DATE
        TO_CHAR
        ADD_MONTHS
        MONTHS_BETWEEN
        NEXT_DAY
        LAST_DAY
        ROUND
        TRUNC 
        TO_TIMESTAMP  
        TO_TIMESTAMP_TZ 
        LOCALTIMESTAMP 
        DBTIMEZONE 
        SESSIONTIMEZONE 
        TO_YMINTERVAL

   

    4. CONVERSION FUNCTION

     >> IMPLICIT DATA TYPE CONVERSION
           VARCHAR2 OR CHAR TO NUMBER
           VARCHAR2 OR CHAR TO DATE
           NUMBER TO VARCHAR2
           DATE TO VARCHAR2

    >> EXPLICIT DATA TYPE CONVERSION
          VARCHAR2 OR CHAR      TO NUMBER
          VARCHAR2 TO CHAR      TO DATE




    5. GENERAL FUNCTION (ADVANCED FUNCTIONS)
        DECODE
        CASE
        NVL,
        NVL2,
        NULLIF, 
        COALESCE,
        LNNVL,
        NANVL,
        UID,
        USER,
        USERNV, 
        SYS_CONTEXT,
        BFILENAME,
        GROUP_ID,
        CARDINALITY,
        LAG,
        LEAD,
        EMPTY_BLOB,
        EMPTY_CLOB,




       
B. MULTIPLE ROW FUNCTION / GROUP FUNCTIONS (RETURN ONE RESULT PER SET OF ROWS)
    1. AGGREGATE FUNCTION
        MIN,
        MAX,
        SUM,
        AVG,
        COUNT,
        VARIANCE,
        STDDEV


    2. ANALYTICAL FUNCTIONS
        LEAD, 
        LAG, 
        RANK, 
        DENSE_RANK, 
        ROW_NUMBER, 
        FIRST, 
        FIRST VALUE, 
        LAST, 
        LAST VALUE





ORACLE USER-DEFINED / STORED FUNCTION: If we will create a function on our own then it's called Oracle USER-DEFINED functions & if we stored it in our database then it's known as USER-DEFINED STORED functions




ORACLE PIPE-LINED FUNCTION: This  is a collection function that must have defined a type.






NOTE: IF YOU HAVE DOUBTS, KINDLY CONTACT ME VIA THIS BLOG