Solved

difference between single row function and multi row function

Posted on 2004-03-31
5
14,010 Views
Last Modified: 2012-06-21
Can anybody explain me what are single row and multi row function with example?
Differences between single row function and multi row function.
0
Comment
Question by:nag0452
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 63 total points
ID: 10723556
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

by:Danielzt
ID: 10723663
0
 
LVL 8

Assisted Solution

by:Danielzt
Danielzt earned 62 total points
ID: 10723687

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question