Oracle Functions - oracle tutorial - sql tutorial



What is Oracle Functions ?

  • SQL functions are built into Oracle and are available for use in various appropriate SQL statements.
  • You can also create your own function using PL/SQL.

Single-Row Functions

  • Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.
  • The Oracle SQL Functions are divided into five types, they are
    • Number Functions (Math Functions)
    • Character Functions
    • Miscellaneous Functions
    • Aggregate Functions
    • Date and Time Functions

Number Functions:

  • Number functions accept numeric input and return numeric values.
  • Most of these functions return values that are accurate to 38 decimal digits.

Character Functions.

  • A character function is a function that takes one or more-character values as parameters and returns either a character value or a number value.
  • The Oracle Server and PL/SQL provide a number of different character datatypes.
  • Including CHAR, VARCHAR, VARCHAR2, LONG, RAW, and LONG RAW.
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

Miscellaneous Functions:

  • The Miscellaneous Functions available in Oracle SQL functions are,

COALESCE

  • Coalesce function returns the first not null value in the expression list.

DECODE

  • Decode functions compares an expr with search value one by one.
  • If the expr does not match any of the search value then returns the default value. If the default value is omitted then returns null.

GREATEST:

  • Returns the greatest expr from an expr list.

LEAST:

  • It is simillar to greatest. It returns the least expr from the expression list.

NVL:

  • This function is oftenly used to check null values.
  • It returns expr2 if the expr1 is null, otherwise returns expr1.
  • NVL2
  • NVL2 returns expr2 if expr1 is not null, otherwise return expr3.
  • NULLIF
  • Nullif compares expr1 with expr2. If they are equal then returns null, otherwise return expr1.
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

UID:

  • Returns the current session ID of user logged on.
  • Returns the username of the current user logged on

SYS_CONTEXT:

  • SYS_CONTEXT returns the value of parameter associated with the context namespace.
  • You can use this function in both SQL and PL/SQL statements.

VSIZE:

  • Returns the internal representation of expr in bytes.

Multi-Row Functions:

  • Multi-Row Functions (also called group or aggregate functions) return a single value based on groups of rows, rather than single value for each row.
  • You can use Aggregate functions in select lists and in ORDER BY and HAVING clauses.
  • They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.
  • The important Aggregate functions are
    • Avg
    • Sum
    • Max
    • Min
    • Count
    • Stddev
    • Variance
Oracle sql group by aggregate functions

Oracle sql group by aggregate functions

avg()

  • The avg() function returns the average value of a numeric field from a group of rows. For example,
SELECT avg (base_salary)
FROM   EMPLOYEES;
click below button to copy the code. By - oracle tutorial - team
  • returns the average salary of all employees.

count()

  • The count() function counts the number of rows in a group of rows. This function counts all rows in the group, including those for which a NULLvalue is present. There are two ways of calling count(), as follows:
SELECT count (*)
FROM   EMPLOYEE_HISTORY
WHERE  employee_number = 90213
AND    warning         = 'Y';
 
SELECT count (married)
FROM   EMPLOYEE_HISTORY
WHERE  employee_number = 90213
AND    warning         = 'Y';
click below button to copy the code. By - oracle tutorial - team
  • The first example returns the total number of rows that match the query’s WHERE clause. The second example returns the total number of rows that have a non-NULL value in the specified column.

max()

  • The max() function returns the highest value of a specified column from a group of rows. For example,
SELECT max (base_salary)
FROM   EMPLOYEES;
click below button to copy the code. By - oracle tutorial - team
  • returns the salary of the highest paid employee.

min()

  • The min() function returns the lowest value of a specified column from a group of rows. For example,
SELECT min (base_salary)
FROM   EMPLOYEES;
click below button to copy the code. By - oracle tutorial - team
  • returns the salary of the lowest paid employee.

sum()

  • The sum() function returns the total of all values for a specified column in a group of rows. For example,
SELECT sum (vacation_days_used)
FROM   EMPLOYEES;
click below button to copy the code. By - oracle tutorial - team
  • returns the total number of vacation days taken by employees this year.
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

Date and Time Functions:

  • To see the system date and time use the following functions:

CURRENT_DATE:

  • It returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE

SYSDATE:

  • SYSDATE returns the current date and time.

SYSTIMESTAMP:

  • SYSTIMESTAMP function returns the system date, including fractional seconds and time zone of the database.
  • The return type is TIMESTAMP WITH TIME ZONE

Stored Procedure Vs Functions

Stored Procedure Vs Functions

This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning

Related Searches to Oracle Functions