12. TYPE OF FUNCTIONS And NUMBER FUNCTIONS

FUNCTIONS

They are built in programs which are used to modify the existing date or for calculations so as to full fill the business requirements followingare function types.

• SINGLE ROW FUNCTIONS
• MULTIPLE ROW FUNCTIONS (OR) GROUP FUNCTIONS 
• AGGREGATE FUNCTIONS      (OR) SUMMARY FUNCTIONS
• WINDOW FUNCTIONS
•  ANALYTICAL (RANK FUNCTIONS)

      SINGLE ROW FUNCTIONS OR SCALAR FUNCTIONS:

•    They get execute for each of the row and return a value
•        Based on data we can classify the functions in following ways

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

•  Number (or) numerical functions or arthimatic functions(old versions)
• String functions or character functions or text functions
• Date functions
• Conversion functions
• Null functions
•  Search functions
• Conditional functions
• Hierarchical functions
• General functions

  NUMBER FUNCTIONS:

•      POWER
•       SQRT 
•       MOD
•       REMINDER
•           SIN, COS,
•       SIGN
•       ABS
•       SINH, COSH
•            EXP
•            LOG
•            LN
•            CEIL
•             FLOOR
•            TRUNC

POWER: To find out the power values

            

             SYN:  POWER (M, N) (M TO THE POWER OF N)

       

      SQRT: To find out the square root value

                    

            SYN: SQRT (VALUE)

            

      MOD: To find out the remainder

            SYN: MOD (M, N)

           REMAINDER: To find out the remainder.

            SYN: REMAINDER (M,N);

  NOTE: MOD AND REMAINDER FUNCTIONS work with different mechanisms.

           SIN,COS,…: Tringometric functions

           

      SIGN: RETURNS   -1 FOR ALL OF THE –VE VALUES +1 FOR ALL OF THE +VE VALUES 0 FOR ZERO VALUE

             SYN: SIGN (V);

             ABS: Returns absolute value irrespective of sign

            SYN: ABS (V);

            SINH, COSH,…: HEPERBOLIC VALUES OF SINE,COS,…

            EXP: EXPONENTIAL VALUES

             LOG: LOG VALUES

            LN  : NATURAL VALUES

            CEIL : Returns same value or next highest value

                        

                        SYN: CEIL (V);

             FLOOR: RETURNS SAME VALUE OR NEXT LOWEST VALUE

                       SYN: FLOOR (V);

           ROUND: ROUND ROUNDS THE GIVEN VALUE TO GIVEN POSITION

                      

                       SYN: ROUND (V);

           TRUNC: TRUNCS THE VALUE

                      SYN: TRUNC (V);

EG: SELECT POWER(5,2),POWER(0,0),POWER(1,0),POWER(0,1) FROM DUAL;

      SELECT MOD(2,5) ,MOD(1,2),MOD(4,2)  FROM DUAL;

      SELECT REMAINDER(5,2) FROM DUAL;

      SELECT MOD(3,-2),REMAINDER(3,-2) FROM DUAL;

      SELECT SIN(45),SINH(50) FROM DUAL;

      SELECT SIGN(-30),SIGN(0),SIGN(+30),ABS(-30),ABS(0),ABS(+30)FROM   DUAL;

       SELECT CEIL(-12.45),FLOOR(-14.56),CEIL(0.56),FLOOR(-0.567) FROM DUAL;

 ROUND:  

         Round rounds the value to given position and it also checks the position i.e. if the last eliminating value is greater than are equal to 5 or >5 then it simply add one value to the left adjacent value.

EG: SELECT ROUND (12.567, 2) FROM DUAL;

      SELECT ROUND (12.563999, 2) FROM DUAL;

      SELECT ROUND (12.56, 3) FROM DUAL;

      SELECT ROUND (13.56, 1) FROM DUAL;

  

      SELECT ROUND (13.56) FROM DUAL;

      SELECT ROUND (15.56,-1) FROM DUAL;

      SELECT ROUND (-16.99,-1) FROM DUAL;

      SELECT ROUND ( -56.99,-2) FROM DUAL;

      SELECT ROUND ( -56.99,-3) FROM DUAL;

      SELECT ROUND (12.56, 1 ) ,TRUNC(12.56,1) FROM DUAL;

      SELECT ROUND ((TRUNC (98.56),-2) FROM DUAL;