Oracle: Functions

Oracle: Functions

• 1.
  1Using ORACLE®Using functions(singlerow and aggregate functions)And the ‘GROUP BY ‘ clause
• 2.
  2FUNCTIONSWe have manyfunctions in Oracle that can be used to perform a myriad of tasks.There are two types of functions viz:Functions take input ,process it and provide the output.Single row functions operate only on one row (1 input and 1 output)Aggregate functions can operate on a range of rows ( multiple inputs and one output)
• 3.
  3FUNCTIONSSingle row functionscan be divided into different parts as:CharacterSingle row functionsNumberGeneralConversionDate
• 4.
  4CHARACTER FUNCTIONSCase manipulationfunctionsCharacter manipulation functions
• 5.
  5CHARACTER FUNCTIONSSELECTprod_ID, INITCAP (prod_name) , CONCAT ('Rs ',prod_cost) AS COST FROM product_masterWHERE prod_name LIKE( LOWER ('TEAK%'));SELECTprod_ID, UPPER (prod_name) , CONCAT ('Rs ',prod_cost) AS COST FROM product_masterWHERE prod_name LIKE ( CONCAT ( '%', SUBSTR ( 'teak_sofa', 6, 4 )));Equivalent to LIKE ( ‘%sofa’);
• 6.
  6NUMBER FUNCTIONSSELECT ROUND (3.14126,4), TRUNC (3.14126,4) , MOD ( 22, 7 ) FROM DUAL ;(DUAL is a dummy table in Oracle used to perform calculations.)SELECTprod_ID , prod_name , ROUND( MOD (prod_stock,prod_order),0) AS REORDER_LVLFROM product_masterORDER BY REORDER_LVL;(Here we use the alias name to order the output)
• 7.
  7DATE FUNCTIONSWe canuse either MONTH or YEAR as parameters in the ROUND and TRUNC functions.
• 8.
  8DATE MANIPULATIONSApart fromthe date functions we can also perform arithmetic operations on dates as:.Adding or subtracting a number to or from a date to get a resultant date.Subtracting two dates to find the number of days between those dates.Adding hours to a date by dividing the number of hours by 24.SELECT SYSDATEASTODAY, (SYSDATE+1) AS NEXT_DAY, (SYSDATE-1) AS PREV_DAYFROM DUAL;
• 9.
  9CONVERSION FUNCTIONSHere inthe TO_NUMBER function we are converting the string ‘2000’ to a number 2000.In TO_DATE we have to specify the format of the output date and In TO_CHAR we have to specify the format of input date to be converted to a string
• 10.
  10GENERAL FUNCTIONSSELECT prod_ID,prod_name, NVL (prod_order,0), NVL2 (prod_sales,'YES','NO'), NULLIF (prod_stock,(prod_order+prod_sales)) AS NULLIFFROM product_master;
• 11.
  11AGGREGATE FUNCTIONSAggregate functionstake multiple rows as input and give one output. The SYNTAX is:SELECT column1,column2…….aggregate_function( column)…FROMtable_nameWHERE (expression/condition);
• 12.
  12AGGREGATE FUNCTIONSSELECT COUNT (prod_ID) AS COUNT, MIN (prod_cost) AS MINIMUM, MAX (prod_sales) AS MAXIMUM, AVG (prod_cost) AS AVERAGE, SUM (prod_stock) AS SUM, ROUND( STDDEV (prod_sales),3) AS STD_DEVIAYION,ROUND( VARIANCE (prod_stock),3) AS VARIANCEFROM product_master;(We can also use ‘*’ in COUNT and ‘DISTINCT’ in AVG )
• 13.
  13GROUP BY CLAUSEWhenwe wish to divide the table into multiple groups based on some criterion we use the ‘GROUP BY’ clause.It is mandatory that while using a group by clause we include the column used in the group must not be used in a aggregate function in the SELECT statement.SYNTAX:SELECT column1,column2…….aggregate_function( column)…FROMtable_nameGROUP BY columnWHERE (expression/condition);It is mandatory to use the group by clause when displaying any column along with a aggregratefunction.We can also use multiple columns in the group by clause.Use the WHERE clause to furthur filter results.
• 14.
  THANK YOU14THANK YOUFOR VIEWING THIS PRESENTATIONFOR MORE PRESENTATIONS AND VIDEOS ON ORACLE AND DATAMINING ,please visit: www.dataminingtools.net