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
At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

What Is a Package? The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a bo

Project Description: I want to rebuild it and add better content to it It will include up to 5 forms The data will be saved on SQL server and the data access layer could b

Data Types in SQL - Character CHARACTER or, synonymously, CHAR, for character strings. When this type is to be the declared type of something (e.g., a column), the permissible

Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

Fetching Across Commits The FOR UPDATE clauses acquire exclusive all row locks. All rows are locked when you open the cursor, and when you commit your transaction they are unl

Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

How Calls Are Resolved? The figure shows that how the PL/SQL compiler resolves the subprogram calls. When the compiler encounters the procedure or function call, it tries to di

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

Project Description: This is stage 1 of a larger conversion project. We are converting a traditional Server/Client application written in Access 2007 into a web interface with S