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
Database Values You can use the SELECT statement to have the Oracle assign values to a variable. For Each and every item in the select list, there must be a matching, type-compa

Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access

Closing a Cursor Variable The CLOSE statement disables the cursor variable. After that, the related result set is undefined. The syntax for the same is as shown below: CLOS

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

Cursor Variables As Parameters You can declare the cursor variables as the formal parameters of the functions and procedures. In the illustration below, you define the REF CUR

Parameter and Keyword Description: record_type_name: This identifies the user-defined type specifier that is used in the subsequent declarations of the records. NOT N

What Are Subprograms? The Subprograms are named PL/SQL blocks which can take parameters and be invoked. The PL/SQL has 2 types of subprograms known as the procedure s and func

Brewbean's is implementing a new discount for return shoppers - every fifth completed order receives a 10% discount. The count of orders for a shopper is placed in a packaged varia

EXIT The EXIT statement forces a loop to done unconditionally. Whenever an EXIT statement is encountered, the loop is done immediately and controls the passes to the next statem

LONG and LONG RAW You use the LONG datatype to store the variable-length character strings. The LONG datatype is such as the VARCHAR2 datatype, except that the maximum length o