Aggregate vs Analytic Functions

WHY USE ANALYTIC FUNCTIONS?

• Ability to see one row from another row in the results

• Avoid self-join queries

• Summary data in detail rows

• Slice and dice within the results

What’s the difference?

Aggregate (traditional)

Query often includes the keywords GROUP BY

Output is a single row (or one row per group with GROUP BY)

Analytic

Query often includes the keywords OVER ( some other stuff)

Does not change number of rows

What makes a function  analytic?

• Keyword OVER, followed by set of parentheses

SELECT deptno, ename, sal , COUNT  ( * )  OVER () ,  SUM   ( sal ) OVER ()
FROM scott.emp;

Returns one result for each record in the dataset. No grouping

TWO NEW FUNCTIONS

• LAG
•  LEAD

                Usage: LAG ( field_name, num_recs ) OVER ( )

These function are analytic only

SELECT  deptno, ename, sal , LAG ( ename ) OVER ( ORDER BY ename ) f1,
 LAG ( ename , 2) OVER( ORDER BY ename ) f2, 
 LEAD  ( ename ) OVER( ORDER BY ename DESC) f3,
LAG ( sal ) OVER( ORDER BY ename ) f4
FROM scott.emp
ORDER BY deptno, ename;

THREE NEW FUNCTIONS

• RANK
• DENSE_RANK 
• ROW_NUMBER

Usage: RANK ( ) OVER ( ORDER BY field_name )

WINDOWING :

Below 2 queries returns similar info. Because, first query represents DEFAULT WINDOWING.

select SUM(SAL) OVER ( ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )  FROM EMP;

select SUM(SAL) over ()
FROM EMP;

ROWS WINDOWING :  Restricts window by records based on ORDER BY

RANGE WINDOWING : Restricts window by a period of time or a value . References field used in ORDER BY

Eg:
select SAL,SUM(SAL) over ( order by SAL rows between 1 preceding and 1 following ) from EMP;select

SAL,SUM(SAL) over ( order by SAL range between 200 preceding and 200 following ) from EMP;

RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.

Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).

RAISE_APPLICATION_ERROR raises an exception but does not handle it.

RAISE_APPLICATION_ERROR is used for the following reasons, 
a) to create a unique id for an user-defined exception. 
b) to make the user-defined exception look like an Oracle error.

The General Syntax to use this procedure is:

RAISE_APPLICATION_ERROR (error_number, error_message);

• The Error number must be between -20000 and -20999 
• The Error_message is the message you want to display when the error occurs.

Steps to be folowed to use RAISE_APPLICATION_ERROR procedure: 
1. Declare a user-defined exception in the declaration section. 
2. Raise the user-defined exception based on a specific business rule in the execution section. 
3. Finally, catch the exception and link the exception to a user-defined error number in RAISE_APPLICATION_ERROR.

Using the above example we can display a error message using RAISE_APPLICATION_ERROR.

DECLARE

  huge_sal EXCEPTION;

BEGIN

  FOR i IN

  (SELECT sal FROM emp )

  LOOP

    if I.SAL > 3000 then

      DBMS_OUTPUT.PUT_LINE(I.SAL);

    ELSE

      RAISE HUGE_SAL;

      end if;

    END LOOP;

  EXCEPTION

  WHEN HUGE_SAL THEN

    raise_application_error(-20133,'Huge Salaray exception raised');

  end;

  /

Cursors

A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or data manipulation language (DML) statement (INSERT, UPDATE, DELETE, or MERGE). Cursor management of DML statements is handled by Oracle Database, but PL/SQL offers several ways to define and manipulate cursors to execute SELECT statements. This article focuses on the most-common ways programmers execute SELECT statements in PL/SQL, namely 

·         Using the SELECT-INTO statement

·         Fetching from an explicit cursor

·         Using a cursor FOR loop (a cursor FOR loop, the body of the loop is executed for each row returned by the query)

·         Using EXECUTE IMMEDIATE INTO for dynamic queries

·         Using cursor variables 

Cursors are 2 types:  Implicit and Explicit

Implicit Cursors :

Whenever a SQL statement is executed, Oracle automatically allocates a memory area (known as context area) in Oracle database PGA i.e. Process Global Area. This allocated memory space is the query work area which holds the query related information.

 All the DML statements (INSERT, UPDATE or DELETE) and SELECT query with INTO or BULK COLLECT clauses are candidates for implicit cursors.

For implicit cursor, the complete execution cycle is internally handled and maintained by the oracle server. For developers, implicit cursor appears to be an abstract concept. Only thing which is physically available and visible to them is the cursor status flags and information. Cursor attributes reveal the cursor related information and status. Following are the cursor attributes available

·         SQL%ROWCOUNT – Number of rows returned/changed in the last executed query. Applicable for SELECT as well as DML statement

·         SQL%ISOPEN – Boolean TRUE if the cursor is still open, else FALSE. For implicit cursor it is FALSE only

·         SQL%FOUND – Boolean TRUE, if the cursor fetch points to a record, else FALSE

·         SQL%NOTFOUND – Inverse of SQL%FOUND. The flag is set as FALSE when the cursor pointer does not point to a record in the result set.

These attributes are set at the different stages of execution cycle and retained in the context area.

Explicit Cursors :

These cursors are explicitly declared in the DECLARE section of the block. They possess a specific name and a static SELECT statement attached to them. Explicit cursors are manually executed by the developers and follow complete execution cycle.

Explicit cursor information is also captured in cursor attributes, which are set during the cursor processing and reveal essential information about the cursors. These attributes, as listed below, are same as that in implicit cursors but specific to the cursors.

·         CURSOR%ROWCOUNT

·         CURSOR%ISOPEN

·         CURSOR%FOUND

·         CURSOR%NOTFOUND

Cursor Execution Cycle

The key steps in the cursor execution cycle are OPEN, FETCH and CLOSE. But it would be worth expanding the complete execution cycle for better understanding about the processing of a cursor. A cursor execution cycle refers to the stages which a cursor follows to process and execute the query. The phases of cursor execution are listed below.

Cursor For Loops

Dynamic Cursor For Loops

Parameterized Cursors

Using FOR UPDATE clause in cursor

Ref-Cursor

Exceptions with Cursors:

ORA-01001: invalid cursor

ORA-06511: PL/SQL: cursor already open

Source:http://www.dbanotes.com/database-development/introduction-to-oracle-11g-cursors/