Types of Functions

• Single row

  • Strings

    SELECT UPPER(name) FROM author;
    
    UPPER(NAME)
    --------------------------------
    ORSON SCOTT CARD
    JAMES BLISH
    ISAAC AZIMOV
    LARRY NIVEN
    JERRY POURNELLE
    WILLIAM SHAKESPEARE
    
    6 rows selected.
    

  • Numbers

    SELECT POWER(2,3) FROM DUAL;
    
    POWER(2,3)
    ----------
             8
    

  • Dates

    SELECT ADD_MONTHS('18-JAN-03',3) FROM DUAL;
    
    ADD_MONTH
    ---------
    18-APR-03
    

  • Data type conversion

    SELECT TO_CHAR('1000000','999,999,999') FROM DUAL;
    
    TO_CHAR('100
    ------------
       1,000,000
    

  • Other

    SELECT NVL(NULL,'This is NULL') FROM DUAL;
    
    NVL(NULL,'TH
    ------------
    This is NULL
    

• Group

  • Aggregate

    SELECT COUNT(*) FROM subject;
    
      COUNT(*)
    ----------
            17
    

  • Analytical functions return multiple rows per group.

• Object references use DEREF() to find object values.

• User defined.

  CREATE OR REPLACE FUNCTION FToC (F IN NUMBER DEFAULT 0)
     RETURN NUMBER IS
  BEGIN
     RETURN ROUND((F - 32) * (5 / 9),0);
  END;
  /
  ALTER FUNCTION FToC COMPILE;
  /
  
  set serveroutput on;
  exec dbms_output.put_line(fToC(100));
  38
  
  PL/SQL procedure successfully completed.
  

String Functions

Detailed Syntax

• CONCAT (expr, expr)
• INITCAP (expr)
• INSTR (expr, substr [, position [, occurrence]])
• LENGTH (expr)
• LOWER (expr)
• LPAD (expr, n [, expr])
• LTRIM (expr, expr)
• REPLACE (expr, search [, replace])
• RPAD (expr, n [, expr])
• RTRIM (expr, expr)
• SUBSTR (expr, [-]position[, length])
• TRANSLATE (expr, search [, replace])
• TRIM ([[LEADING|TRAILING|BOTH] character FROM] expr)
• UPPER (expr)

Number Functions

Detailed Syntax

• ABS (n)
• CEIL (n)
• FLOOR (n)
• MOD (m, n)
• POWER (m, n)
• ROUND (n [, places])
• SIGN (n)
• SQRT (n)
• TRUNC (n [, places])

Date Functions

Detailed Syntax

Dates and Times

• CURRENT_DATE
• SYSDATE
• CURRENT_TIMESTAMP (precision)
• LOCALTIMESTAMP (precision)
• SYSTIMESTAMP

Date Manipulation

• ADD_MONTHS (date, months)
• LAST_DAY(date)
• MONTHS_BETWEEN (date, date)
• NEXT_DAY (date, weekday)

Rounding and Truncation

• ROUND (date [, format])
• TRUNC (date [, format])

This diagram gives a brief summary of possible formatting patterns for execution of ROUND and TRUNC functions on dates.

Datatype Conversion Functions

Detailed Syntax

• TO_CHAR (expr [, format])
• TO_NUMBER (expr [, format])
• TO_DATE (expr [, format])
• TO_CLOB ('string')

Other Functions

Detailed Syntax

• BFILENAME ('dir', 'file')
• COALESCE (expr)
• DECODE (expr, search, replace [, default])
• GREATEST (expr[, expr ])
• LEAST (expr[, expr ])
• NULLIF (expr, expr)
• NVL (expr, replace)
• USER