• Loading
    • SQL Functions

      Learning Objectives
      After completing this session, you will be able to:
      • Write single row functions
      • Write aggregate functions


      Understand Function


      Functions are similar to operators in that they manipulate data items and return a result. Functions differ from operators in the format of their arguments. This format enables them to operate on zero, one, two, or more arguments:
      function(argument, argument, ...)
      Functions can:
      • Perform calculations on data
      • Modify individual data items
      • Manipulate output for groups of rows
      • Format dates and numbers for display
      • Convert column data types


      The following are the types of functions:
      • Single-row functions: Operate on single-rows only, and return one result per row
      • Multiple-row functions: Operate on groups of rows, and produce one result per group of rows


      Single – Row Functions

      Single row functions Operate on single-rows only, and return one result per row. They can be used in SELECT, WHERE and ORDER BY clause. Single row functions can manipulate data item and may change the data type. They can be nested.
      Single row functions are of the following types:
      • Character
      • Number
      • Date
      • Conversion
      • General functions



      Character Function
      Single row character function accepts character data as input and can return both character and numeric value.
      Character function can be divided into following:
      • Case manipulation function
      • Character manipulation function


      Case manipulation function
      • LOWER (column|expression): Converts alpha character values to lowercase.
      • UPPER (column|expression): Converts alpha character values to uppercase.
      • INITCAP (column|expression): Converts alpha character values to upper case for the first letter of each word and lower case for all other letters.


      Example:
      Code:
      SELECT LOWER(‘Hello’) FROM DUAL 
      Result : hello 
      SELECT UPPER(‘Hello’) FROM DUAL 
      Result : HELLO 
      SELECT INITCAP (‘hello’) FROM DUAL 
      Result : Hello

      Character manipulation function:

      Function Purpose
      CONCAT(column1|expression1, Column2|expression2) Concatenates the first character value to the second character value.
      SUBSTR(column|expression, m, [n]) Returns the specified characters from the character starting at position m and retrieving the next n characters. (If n not specified retrieves to end of field, if m negative counts backward from end.
      LENGTH(column|expression) Returns the number of characters in a value.
      INSTR(column|expression, m, [n]) Returns the numeric position of a named string
      RPAD(column|expression,n,’string’)
      LPAD(column|expression,n,’string’)
      Pads the character value right justified to a total width of n character position
      Pads the character value left justified to a total width of n character position
      TRIM(leading|trailing|both,
      Trim_character FROM trim_source)
      Enable you to trim heading or trailing (or both) from a character string



      Function
      REPLACE(text,search_string,replacement_string)
      Purpose
      Search a text expression for a character string, and if found replace it with a specified replacement string.
      CHR(number) Returns the character based on a ASCII number.
      ASCII(character) Returns the ASCII number based on a character.

      Example:
      Function Result
      Code:
      CONCAT (‘Hello’,’World’) HelloWorld 
      SUBSTR(‘HelloWorld’,1,5) Hello 
      LENGTH(‘HelloWorld’) 10 
      INSTR(‘HelloWorld’,’W’) 6 
      LPAD(salary,10,*) *****10000 
      RPAD(salary,10,*) 10000***** 
      TRIM(‘H’ FROM ‘HelloWorld’) elloWorld

      Numeric Functions


      Number function accepts numeric input and returns numeric value.
      Function Purpose
      ROUND(column|expression, n) Rounds the column, expression, or value to decimal places. If n is omitted, no decimal places. If n is negative, numbers to left of the decimal point are rounded.
      TRUNC(column|expression, n) Truncates the column, expression or value to n decimal places. If n is omitted to no decimal places. If n is negative numbers to the left of the decimal point are truncated to 0
      MOD(m, n) Returns the remainder of m/n where m and n can be columns, expressions or values.

      Example
      Code:
      SELECT ROUND(45.926,2) FROM DUAL ; 
      Result : 45.93 
      SELECT TRUNC(45.926,2) FROM DUAL ; 
      Result : 45.92 
      SELECT MOD(1600,300) FROM DUAL ; 
      Result : 100

      Date Functions


      Oracle stores dates in an internal numeric format, century, year, month, day, hours, minutes, seconds. The default date display is DD-MON-YY
      Example:
      to display the current date using the DUAL table
      Code:
      SQL> SELECT SYSDATE FROM SYS.DUAL
      Function Purpose
      NEXT_DAY(date, 'day of week') Finds the next date that is the specified day of the week. For example NEXT_DAY('15-OCT-98', 'SUNDAY') fids the first Sunday following October 15, 1998.
      LAST_DAY(date) Finds the date of the last day of the month that contains the date specified. For example LAST_DAY(SYSDATE) returns the date of the last day of the current month.
      Add_Months(date, n) Adds the specified number of calender months to the date, n can be negative (months are subtracted), but must bre an integer. For example ADD_MONTHS('15_-OCT-98', 2) returns 15_DEC-98.
      MONTHS_BETWEEN(date1, date2) Finds number of months between 2 dates by subtracting date 2 form date 1. Result can be negative (If date later than date 1) and includes fractional portions of months. For example MONTHS_BETWEEN('30-NOV-98', '15-NOV-98') returns .5.
      ROUND(date, 'fmt') Rounds the date to the unit listed in the format model. For example ROUND('20-OCT-98', 'MON') returns 01-NOV-98. If no format is specified, rouning is to the nearest day. ROUND(SYSDATE) returns the current date till noon and the next day after noon.
      TRUNC(date, 'fmt‘) Like round except truncates to the format unit specified. TRUNC('20-OCT-98) returns 01-OCT-98. TRUNC(SYSDATE) returns a value of midnight on the current date, the fractional time element is truncated.


      Example :
      Code:
      SELECT MONTHS_BETWEEN(’01-SEP-07’,’11-JAN-07’) 
      FROM DUAL; 
      Result : 19.6774194 
      
      SELECT ADD_MONTHS(’11-JAN-07’,6) 
      FROM DUAL; 
      Result : 11-JUL-07 
      
      SELECT NEXT_DAY(’01-SEP-07’, ‘MONDAY’) 
      FROM DUAL; 
      Result : 08-SEP-07 
      
      SELECT LAST_DAY(’01-FEB-07’) 
      FROM DUAL 
      Result :28-FEB-07
      Code:
      Assume sysdate = ’17-FEB-08’
      SELECT ROUND(SYSDATE,’MONTH’) FROM DUAL ;
      Result : 01-FEB-08
      
      SELECT ROUND(SYSDATE,’YEAR’) FROM DUAL ;
      Result : 01-JAN-08
      
      SELECT TRUNC(SYSDATE,’MONTH’) FROM DUAL ;
      Result : 01-FEB-08
      
      SELECT TRUNC(SYSDATE,’YEAR’) FROM DUAL ;
      Result : 01-JAN-08

      Conversion functions:
      Conversion can either be implicitly or explicitly. Oracle can implicitly convert from varchar2 or char to number, varchar2 or char to Date, Number to varchar2, and date to varchar2. Explicit conversion is required primarily for conversion and display of date/time data to character form and vice-versa.

      Function Purpose
      TO_CHAR(number|date,'fmt') Converts a number or date value (could be a column or expression) to a VARCHAR2 string with the format model fmt.
      TO_DATE(char,['fmt']) Converts a character string representing a data to a date value according to the fmt specified. If fmt omitted, format is DD-MON-YY.
      TO_NUMBER(char) Converts a character string containing digits to a number.


      The following is an example of the TO_CHAR () function:
      Code:
      SQL> SELECT TO_CHAR (SYSDATE, 'dd-mon-yyyy hh:mi:ss'), 
      2 TO_CHAR (SYSDATE, 'day-monthdd, yyyy'), 
      3 TO_CHAR (SYSDATE, 'dd/mm/yy') from sys.dual; 
      TO_CHAR(SYSDATE,'DD-MON-YYYYHH:MI:SS') 
      -------------------------------------- 
      TO_CHAR(SYSDATE,'DAY-MONTHDD,YYYY') 
      -------------------------------------- 
      TO_CHAR(SYSDATE,'DD/MM/YY') 
      -------------------------------------- 
      04-jun-1998 06:54:52 
      thursday -june 04, 1998 
      04/06/98


      General Function
      These functions work with any data type and pertain to the use of NULL values in the expression list.

      NVL(expr1, expr2):
      Converts a null value to an actual value

      NVL2(expr1, expr2, expr3):
      If expr1 is not null ,NVL2 returns expr2 . If expr1 is null ,NVL2 returns expr3.The argument expr1 can have any data type

      NULLIF(expr1, expr2):
      Compares two expressions and returns null if they are equal , or the first expression if they are not equal.

      COALESCE(expr1, expr2,…expr n):
      Returns the first not null expressions in the expression list.

      Example:

      Code:
      SELECT NVL(intrest_rate,1) 
      FROM daily_pipeline_loan_fact 
      
      SELECT NVL2(total_amount,’loan_amount*intrest_rate’, 
      loan_amount) 
      FROM daily_pipeline_loan_fact 
      
      SELECT laonno, 
      COALESCE(loan_amount,intrest_rate,10) amt 
      FROM daily_pipeline_loan_fact 
      
      SELECT laonno, 
      NULLIF(intrest_rate,10) result 
      FROM daily_pipeline_loan_fact

      DECODE function

      The DECODE function applies logic similar to CASE or IF-THEN-ELSE statements in programming languages.
      Syntax:
      Code:
      Code:
      DECODE (col/expression, search1, result1, 
      [, search2, result2, . . .] 
      [, default]) 

      Example of DECODE function:

      Suppose, the ABC wants to give a interest rate of 15 percent on all personal loans ‘P’, a interest rate of 12 percent on home loan ‘H”, and a interest rate of 16 percent on all other loans. The code to retrieve the data is:

      Code:
      SELECT loan_no,loan_amount, DECODE(loan_type,‘P', 15, ‘H', 12, 16) as interestrate 
      FROM daily_pipeline_loan_fact; 
      The above DECODE statement is equivalent to the following 
      IF-THEN-ELSE statement: 
      IF loan_type = ’P’ THEN 
      Result :=15; 
      ELSEIF loan_type = ’H’ THEN 
      Result := 12; 
      ELSE Result := 16;

      Nesting of functions:
      Single-row functions can be nested to any level.
      Example:
      Code:
      SELECT ENAME (NVL(TO_CHAR(MGR), ‘NO MANAGER’) FROM EMP;


      Aggregate Functions


      Aggregate function operates on set of rows to give one result per group.
      The following are the aggregate functions:
      • SUM
      • AVG
      • MAX
      • MIN
      • COUNT(*)
      • COUNT (column name)


      All group functions except COUNT(*) ignore NULL. MAX and MIN functions can be used for any datatype.

      Group By clause:

      Query results can be summarized using the GROUP BY clause. Multiple columns can be used in GROUP BY clause.
      Following can be used with GROUP BY clause:
      • Constant
      • Function without parameters (SYSDATE, USER)
      • Group functions


      Example:
      Code:
      SELECT loan_status, SUM(loan_amount) 
      FROM daily_pipeline_loan_fact 
      GROUP BY loan_status;

      The
      HAVING clause: Used for restricting groups

      Sequence

      • Rows are first grouped
      • The group function is applied
      • Groups matching the HAVING clause is then displayed


      Example:
      Code:
      SELECT loan_status, SUM(loan_amount) 
      FROM daily_pipeline_loan_fact 
      GROUP BY loan_status; 
      HAVING SUM(loan_amount)> 10000;

      Summary

      • Functions are used to perform calculations on data, modify individual data items, manipulate output for groups of rows, format dates and numbers for display, convert column data types.
      • The types of functions are single-row functions and multiple-row functions.
      • The types single row functions are character, number, date, conversion, list, and general functions and can be used with SELECT, WHERE, and ORDER BY.
      • Oracle can implicitly convert from varchar2 or char to number, varchar2 or char to Date, Number to varchar2, and date to varchar2.
      • Explicit conversion is required primarily for conversion and display of date/time data to character form and vice-versa.


      Test Your Understanding

      • What are the different types of functions?
      • What are the different types of single row functions?
      • What are the different types of date function?
      • What are the different types of character function?
      • What are the different types of number function?
      • What is the use of COALESCE?
      • What is the use of NULLIF?
      • What is the use of DECODE?
      • What are the different aggregated functions?
      • What is the difference between WHERE clause and HAVING clause?
    • Enter your email address:

      Delivered by FeedBurner

    • Categories




    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.