Solved

# difference between single row function and multi row function

Posted on 2004-03-31
11,772 Views
Can anybody explain me what are single row and multi row function with example?
Differences between single row function and multi row function.
0
Question by:nag0452
• 2

LVL 23

Accepted Solution

seazodiac earned 63 total points
single-row function: "functions return a single result row for every row of a queried table or view. "
Multiple row functions:  "functions return a single result row based on groups of rows, rather than on single rows."

an example using scott schema emp table:

select empno, ename, to_char(sal, '9,999.99') from emp; --here to_char is a single row function

select deptno, sum(sal) from emp group by deptno; --here sum() is a multiple row function.

0

LVL 8

Expert Comment

0

LVL 8

Assisted Solution

Danielzt earned 62 total points

one more:

SQL-FUNCTIONS

A SQL function is similar to an operator in that it manipulates data
items and returns a result.  Functions differ from operators in the
format in which they appear with their arguments.  This format
allows them to operate on zero, one, two, or more arguments:

function(argument, argument, ...)

If you call a function with an argument of a datatype other than the
datatype expected by the function, Oracle implicitly converts the
argument to the expected datatype before performing the function.

If you call a function with a null argument, the function
automatically returns null.  The only functions that do not follow
this rule are CONCAT, REPLACE, DUMP. and NVL.

Do not design your applications to rely on Oracle either to evaluate
or to not evaluate all arguments to a function.

Note:  You can call a Stored PL/SQL function anywhere that you
can call a SQL function.  Refer to your PL/SQL documentation for
information on how to construct a Stored PL/SQL function for use
in a SQL statement.

Functions are of these general types:

* single row (or scalar) functions
* group functions (or aggregate) functions

These functions differ in the number of rows upon which they act.  A
single row function returns a single result row for every row of a
queried table or view, while a group function returns a single
result row for a group of queried rows.

Single row functions can appear in select lists (provided the SELECT
statement does not contain a GROUP BY clause), WHERE clauses, START
WITH clauses, and CONNECT BY clauses.

Group functions can appear in select lists and HAVING clauses.  If
you use the GROUP BY clause in a SELECT statement, Oracle divides
the rows of a queried table or view into groups.  In a query
containing a GROUP BY clause, all elements of the select list must
be either expressions from the GROUP BY clause, expressions
containing group functions, or constants.  Oracle applies the group
functions in the select list to each group of rows and returns a
single result row for each group.  If you omit the GROUP BY clause,
Oracle applies group functions in the select list to all the rows in
the queried table or view.  You can also use group functions in a
HAVING clause in the statement to restrict the result rows returned.

0

## Featured Post

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…