Sql functions, PL-SQL Programming

SQL Functions

The PL/SQL uses all the SQL functions involving the following aggregate functions that summarize the whole columns of the Oracle data: GROUPING, AVG, COUNT, STDDEV, MAX, MIN, SUM, & VARIANCE. Except for the COUNT (*), all the aggregate functions ignore nulls.

You can use the aggregate functions in the SQL statements, but not in the procedural statements. The Aggregate functions operate on whole columns unless you use the SELECT GROUP BY statement to sort the returned rows into subgroups. If you omit the GROUP BY clause, the aggregate functions treat all returned rows as a single group.

You call an aggregate the function using the syntax as shown below:

function_name([ALL | DISTINCT] expression)

Where the expression refers to one or more database columns. When you specify ALL (the default), the aggregate function consider all column values including the duplicates. When you specify DISTINCT, the aggregate function considers only the distinct values. For illustration, the statement below returns the number of various job titles in the database table emp:

SELECT COUNT(DISTINCT job) INTO job_count FROM emp;

The function COUNT specify the asterisk (*) choice, that returns the number of rows in a table. For illustration, the following statement returns the number of rows in a table emp:

SELECT COUNT (*) INTO emp_count FROM emp;

Posted Date: 10/4/2012 3:39:30 AM | Location : United States







Related Discussions:- Sql functions, Assignment Help, Ask Question on Sql functions, Get Answer, Expert's Help, Sql functions Discussions

Write discussion on Sql functions
Your posts are moderated
Related Questions
Using Aliases The Select-list items fetched from a cursor related with the %ROWTYPE should have simple names or, if they are expressions, should have aliases. In the example bel

Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th

What is Cursors how to use it in Real time application ?

Avoiding Collection Exceptions   In many cases, if you reference a nonexistent collection element, then PL/SQL raises a predefined exception. Consider the illustration shown b

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

An analyst in the quality assurance office reviews the time lapse between receiving an order and shipping an order. Any orders that have not been shipped within a day of the order

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

Some Varray Examples In SQL Plus, assume that you define an object type Project, as described below: SQL> CREATE TYPE Project AS OBJECT ( 2 project_no NUMBER(2), 3 title VARCHA

Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh

In Packages The Forward declarations also group logically related subprograms in the package. The subprogram specifications go in the package specification, & the subprogram b