Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Multi-Row SQL Functions
Oracle Tips by Burleson
 

Multi-row SQL functions (also called group or aggregate functions) work with groups of rows. These functions ignore NULL values, except where noted in this section. The most commonly used multi-row SQL functions fall into the numeric group. All functions listed in this section are numeric 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;

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 NULL value 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';

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;

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;

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;

returns the total number of vacation days taken by employees this year.

PL/SQL Functions

PL/SQL provides two important error reporting functions that are not provided by SQL: SQLCODE() and SQLERRM().

SQLCODE()

The SQLCODE() function provides the number of the latest Oracle error that has occurred. Following is an example of calling the SQLCODE() function:

vErrorCode := SQLCODE;

SQLERRM()

The SQLERRM() function provides the complete text of the most recent Oracle error that has occurred, including the error number provide by the SQLCODE() function. Following is an example of calling the SQLERRM() function:

vErrorText := SQLERRM;

               
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter