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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×