EXAMPLE on LAG Function.

Example:
Generate Aggregated Rolling last  six quarter results per sector per quarter.   
Output :  Quarter ,  Current Quarter Result ,  Aggregated Last Six quarter Results.


-- Create a table for test example

create table sector_quarter_results
(quarter varchar2(20),
 sector  varchar2(20),
 results number);

-- Financial sector

insert into sector_quarter_results (quarter,results,sector) values ('Q3 2010',100000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2010',90000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2010',80000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2009',70000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2009',60000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2009',50000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2009',40000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2008',30000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2008',20000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2008',10000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2008',5000,'Financial');

-- Healthcare sector

insert into sector_quarter_results (quarter,results,sector) values ('Q3 2010',1000,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2010',900,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2010',800,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2009',700,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2009',600,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2009',500,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2009',400,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2008',300,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2008',200,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2008',100,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2008',50,'Healthcare');


-- Insurance Sector

insert into sector_quarter_results (quarter,results,sector) values ('Q3 2010',10000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2010',9000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2010',8000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2009',7000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2009',6000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2009',5000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2009',4000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2008',3000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2008',2000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2008',1000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2008',500,'Insurance');

commit;

Reporting Query:

select quarter,sector,current_results, 
                                          (nvl(current_results,0)+
                                           nvl(previous_quarter_1,0)+
                                           nvl(previous_quarter_2,0)+
                                           nvl(previous_quarter_3,0)+
                                           nvl(previous_quarter_4,0)+
                                           nvl(previous_quarter_5,0)) rolling_6_quarters
from
(
     select quarter,
            sector,
            results current_results,
            lag(results,1) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_1,
            lag(results,2) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_2,
            lag(results,3) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_3,
            lag(results,4) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_4,
            lag(results,5) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_5
     from sector_quarter_results
)
order by sector asc,(substr(quarter,4)||substr(quarter,2,1)) desc;

OUTPUT:

QUARTER         SECTOR     CURRENT_RESULTS  ROLLING_6_QUARTERS
---------------- ------------------ ---------------           ------------------
Q3 2010              Financial                     100000             450000
Q2 2010              Financial                      90000             390000
Q1 2010              Financial                      80000             330000
Q4 2009              Financial                      70000             270000
Q3 2009              Financial                      60000             210000
Q2 2009              Financial                      50000             155000
Q1 2009              Financial                      40000             105000
Q4 2008              Financial                      30000              65000
Q3 2008              Financial                      20000              35000
Q2 2008              Financial                      10000              15000
Q1 2008              Financial                       5000               5000

Q3 2010              Healthcare                      1000               4500
Q2 2010              Healthcare                       900               3900
Q1 2010              Healthcare                       800               3300
Q4 2009              Healthcare                       700               2700
Q3 2009              Healthcare                       600               2100
Q2 2009              Healthcare                       500               1550
Q1 2009              Healthcare                       400               1050
Q4 2008              Healthcare                       300                650
Q3 2008              Healthcare                       200                350
Q2 2008              Healthcare                       100                150
Q1 2008              Healthcare                        50                 50

Q3 2010              Insurance                      10000              45000
Q2 2010              Insurance                       9000              39000
Q1 2010              Insurance                       8000              33000
Q4 2009              Insurance                       7000              27000
Q3 2009              Insurance                       6000              21000
Q2 2009              Insurance                       5000              15500
Q1 2009              Insurance                       4000              10500
Q4 2008              Insurance                       3000               6500
Q3 2008              Insurance                       2000               3500
Q2 2008              Insurance                       1000               1500
Q1 2008              Insurance                        500                500

RATIO_TO_REPORT

RATIO_TO_REPORT

Generate the value of the denominator for performance calculations.

Example:
Divide each salesperson's total sales (SP_SALES) by the total region sales (REGION_SALES) to determine what ratio of the total region sales can be attributed to each salesperson.


SELECT region_id, salesperson_id,
  SUM(tot_sales) sp_sales,
  SUM(tot_sales) / SUM(SUM(tot_sales)) OVER (PARTITION BY region_id) percent_of_region
  FROM orders
  WHERE year = 2001
  GROUP BY region_id, salesperson_id;


This can be done by RATIO_TO_REPORT function.


SELECT region_id, salesperson_id,
  SUM(tot_sales) sp_sales,
  RATIO_TO_REPORT(SUM(tot_sales)) OVER (PARTITION BY region_id)  sp_ratio
FROM orders
WHERE year = 2001
GROUP BY region_id, salesperson_id;

LAG / LEAD

LAG / LEAD

All rows to be referenced by their position relative to the current row.

They are useful for comparing one row of a result set with another row of the same result set.

Example:
"Compute the total sales per month for the Mid-Atlantic region, including the percent change from the previous month"

Requires data from both the current and preceding rows in order to calculate the answer.



Example

SELECT month, SUM(tot_sales) monthly_sales,

  LAG(SUM(tot_sales), 1) OVER (ORDER BY month) prev_month_sales

FROM orders

WHERE year = 2001

  AND region_id = 6

GROUP BY month

ORDER BY month;



     MONTH MONTHLY_SALES PREV_MONTH_SALES

---------- ------------- ----------------

         1        610697

         2        428676           610697

         3        637031           428676

         4        541146           637031

         5        592935           541146

         6        501485           592935

         7        606914           501485

         8        460520           606914

         9        392898           460520

        10        510117           392898

        11        532889           510117

        12        492458           532889

As we might expect, the LAG value for month 1 is NULL, since there is no preceding month. This would also be the case for the LEAD value for month 12.
Take this into account when performing calculations that utilize the results of the LAG or LEAD functions.
The next query utilizes the output from the previous query to generate the percentage difference from month to month.
Note how the prev_month_sales column is wrapped in the NVL function so that month 1 won't generate a NULL value for the percentage change:

SELECT months.month month, months.monthly_sales monthly_sales,

  ROUND((months.monthly_sales -- NVL(months.prev_month_sales,

    months.monthly_sales)) /

    NVL(months.prev_month_sales, months.monthly_sales),

    3) * 100 percent_change

FROM

 (SELECT month, SUM(tot_sales) monthly_sales,

    LAG(SUM(tot_sales), 1) OVER (ORDER BY month) prev_month_sales

  FROM orders

  WHERE year = 2001

    AND region_id = 6

  GROUP BY month) months

ORDER BY month;



     MONTH MONTHLY_SALES PERCENT_CHANGE

---------- ------------- --------------

         1        610697              0

         2        428676          -29.8

         3        637031           48.6

         4        541146          -15.1

         5        592935            9.6

         6        501485          -15.4

         7        606914             21

         8        460520          -24.1

         9        392898          -14.7

        10        510117           29.8

        11        532889            4.5

        12        492458           -7.6

Windowing Functions

Windowing Functions:

In cases such as
1. From the current row to the end of the partition
2. For the 30 days preceeding the transaction date
3. All rows having a transaction amount within 5% of the current row's transaction amount.

-- COMPLETE PARTITION WINDOW

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- ROLLING CALCULATIONS
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- WINDOW OF (CURRENT ROW - X ROWS) , (CURRENT ROW) , (CURRENT ROW + Y ROWS)
ROWS BETWEEN X PRECEDING AND Y FOLLOWING



FIRST_VALUE  AND LAST_VALUE :

These can be used with the windowing functions to identify the values of the first and last values
in the window.

FIRST_VALUE (sum(total_sales)) over (order by month
                                     rows between 1 preceding and 1 following) first_value_in_window,
LAST_VALUE  (sum(total_sales)) over (order by month
                                     rows between 1 preceding and 1 following) last_value_in_window,
Avg(sum(total_sales)) over (order by month
                            rows between 1 preceding and 1 following) rolling_3_month_avg

Ranking functions

Ranking functions:

1.RANK
2.DENSE_RANK
3.ROW_NUMBER
4.NTILE
5.WIDTH_BUCKET
6.CUME_DIST
7.PERCENT_RANK
8.FIRST / LAST (KEEP)


1.RANK: Starts with 1, if duplicate values, same rank is assigned. Gaps appear in the ranks.

2.DENSE_RANK: Starts with 1, if duplicate values,same rank is assigned. No gap in ranks.

3.ROW_NUMBER: Starts with 1 & unique, if duplicate values,numbers are arbitrarily assigned.

The PARTITION BY clause used in ranking functions is used to divide a result set into pieces so that rankings can be applied within each subset.

Handling NULLS:

All ranking functions allow to specify where in the ranking order NULL values should appear.

Append NULLS FIRST / NULLS LAST after the ORDER BY clause of the function.

USAGE: Top/Bottom-N queries



FIRST / LAST: (KEEP)

"Find the regions with the best and worst total sales last year."

Example:

MIN(region_id)
    KEEP (DENSE_RANK FIRST ORDER BY SUM(tot_sales) DESC) best_region,
MIN(region_id)
    KEEP (DENSE_RANK LAST ORDER BY SUM(tot_sales) DESC) worst_region

NTILE:

Group the records into buckets ( equiheight buckets ).

Example:

 NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) will divide it into four buckets.


WIDTH_BUCKET:(equiwidth buckets)

WIDTH_BUCKET function groups rows of the result set into buckets.

WIDTH_BUCKET function attempts to create equiwidth buckets, meaning that the range of values is evenly distributed across the buckets.

WIDTH_BUCKET can operate on numeric or date types.

WIDTH_BUCKET uses the values of the second, third, and fourth parameters to generate N buckets.

If the expression yields values that fall outside the range specified by the second and third parameters, the WIDTH_BUCKET function will generate two additional buckets, numbered 0 and N+1, into which the outliers are placed.

Example:
 WIDTH_BUCKET(SUM(tot_sales), 1, 3000000, 3) sales_buckets

Based on these parameters, the WIDTH_BUCKET function generates three buckets;
the first bucket starts at 1, and the third bucket has an upper range of 3,000,000.
Since there are three buckets, the ranges for each bucket will be
1 to 1,000,000,
1,000,0001 to 2,000,000, and
2,000,0001 to 3,000,000.
Any values less than 1 or greater than 3,000,000 will be placed to 0th and 4th buckets.

NOTE:

Keep in mind that the WIDTH_BUCKET function does not remove rows from the result set that do not lie within the specified range; rather, they are placed into special buckets that your query can either utilize or ignore as needed.


CUME_DIST and PERCENT_RANK :

CUME_DIST( )  : (Cumulative Distribution)
   Ratio of the number of rows that have a lesser or equal ranking to the total number of rows in the partition.

PERCENT_RANK( ) :
   Ratio of a row's ranking to the number of rows in the partition.

   PERCENT_RANK=((Rank of the row -1) / (Total rows -1))

Example:

CUME_DIST(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_cume_dist
PERCENT_RANK(  ) OVER (ORDER BY SUM(tot_sales) DESC) sales_percent_rank

SELECT FOR UPDATE / WHERE CURRENT OF

SELECT FOR UPDATE
When a cursor is opened, the sql statement gets executed and builds the dataset.
Once the dataset(resultset) is built, the changes made to the data at the database level will not be reflected to the dataset built.

Example:

Session X  (session sleeps for 10 minutes)

Declare
  cursor emp_rec is select * from scott.emp;
  emp_record emp_rec%rowtype;
Begin
  open emp_rec;
-- sleep for 10 mins
  dbms_lock.sleep(600);
  loop
    fetch emp_rec into emp_record;
   dbms_output.put_line('emp no:' || emp_record.empno || ' salary:' || emp_record.sal);
   exit when emp_rec%notfound;
  end loop;
End;
/

Session Y (while the Session X is in sleep mode)

update emp set sal=sal*1.1;
commit;

The increased salaries will not be reflected to the cursor dataset.

If we want to lock the rows captured by the cursor, we use the FOR UPATE OF clause.

When we issue a SELECT...FOR UPDATE statement , Oracle automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement.

No one else will be able to change any of these records until we perform a ROLLBACK or a COMMIT
but other sessions can still read the data.

we can use the FOR UPDATE clause in a select with multiple tables.All the tables with the columns in the OF list will be locked.

Example:

Cursor emp_cur is
  select
    emp.empno,
    emp.sal,
    dept.dept_name
  from emp,dept
  where emp.deptno=dept.deptno
  FOR UPDATE OF emp.SAL;

In the above Cursor only emp table rows will be locked for update not the dept table.

Once COmmit or Rollback is encountered after the cursor is open,locks will be released & we can no longer fetch data from the cursor.

Note:
we cannot execute another FETCH against a FOR UPDATE cursor after we COMMIT or ROLLBACK.

WHERE CURRENT OF :

"Delete the row I just fetched."
"Update the columns in the row I just fetched."

PL/SQL provides the WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor.

To update columns in the most recently fetched row, specify:
    UPDATE table_name
       SET set_clause
     WHERE CURRENT OF cursor_name;

To delete the row from the database for the most recently fetched record, specify:
    DELETE
      table_name
     WHERE CURRENT OF cursor_name;

EXAMPLE:

Declare
  cursor emp_rec is select * from scott.emp for update;
  emp_record emp_rec%rowtype;
  my_sal scott.emp.sal%type;
BEGIN
  open emp_rec;
  fetch emp_rec into emp_record;
  dbms_output.put_line(emp_record.sal);
  update scott.emp set sal=sal*2
    where current of emp_rec;
  select sal into my_sal from scott.emp where empno=emp_record.empno;
  dbms_output.put_line(my_sal);
  close emp_rec;
commit;
End;
/

BULK COLLECT / LIMIT CLAUSE

BULK COLLECT:

Bulk collect can retrieve multiple rows of data through either an implicit or an explicit query with a single roundtrip to and from the database.

... BULK COLLECT INTO collection_name[, collection_name] ...

We can use BULK COLLECT keywords in any of the following clauses:
1.SELECT INTO
2.FETCH INTO
3.RETURNING INTO

SELECT...BULK COLLECT will not raise NO_DATA_FOUND if no rows are found.
Instead, we must check the contents of the collection to see if there is any data inside it.

The BULK COLLECT operation empties the collection referenced in the INTO clause before executing the query. If the query returns no rows, this collection's COUNT method will return 0.

Examples:

Declare  Type my_test_t is table of source_daily%rowtype;
  my_test_collection my_test_t :=my_test_t();
Begin
  select * bulk collect into my_test_collection 
  from source_daily;
  dbms_output.put_line(my_test_collection.count);
End;/

Declare  Type my_test_t is table of source_daily%rowtype;
  my_test_collection my_test_t :=my_test_t();
  Cursor test_cur is
    select * 
    from source_daily;
Begin  Open test_cur;
  Fetch test_cur
              bulk collect into my_test_collection;
  Dbms_output.put_line(my_test_collection.count);
End;/


LIMIT:

LIMIT clause for BULK COLLECT allows us to limit the number of rows fetched from the database.

FETCH cursor_name BULK COLLECT INTO ... [LIMIT rows];

where rows can be any literal, variable, or expression that evaluates to an integer.

LIMIT is very useful with BULK COLLECT, because it helps us to manage how much memory our program will be used to process the data.

[rows] this many number of rows that will be fetched into collections per single trip.

For every iteration the collections will be refreshed with the new fetched rows.
Once all the rows are done, the count on the collection will be zero and we can exit the fetch loop.

Example:

create table limit_test (rno number);

-- Populate test data
Begin
  For i in 1..10004
   loop
     insert into limit_test values (i);
   end loop;
  commit;
End;
/

Declare
  Type limit_test_t is table of limit_test%rowtype;
  limit_collection limit_test_t;
  Cursor c1 is select * from limit_test;
Begin 
  Open c1;
-- Bulk collect without any limit
  Fetch c1 bulk collect into limit_collection;
  Close c1;
  dbms_output.put_line(limit_collection.count);

-- Reopen the cursor & use limit
  open c1;
  loop
    fetch c1 bulk collect into limit_collection limit 30;
    exit when limit_collection.count=0;
    dbms_output.put_line(limit_collection.count);
  end loop;
End;/

RETURNING Clause : (With Bulk collect)

We can use the RETURNING clause within the DML (UPDATE/DELETE) to grab the modified info
by the statement into collections.

1.Execute the DML Statement and Grab the modified values into Collections Via Bulk Collect on the fly.
2. Use the variables in the code.

Note:

If the Update/Delete affects only one row, we can use the RETURNING Clause and grab the modified data into the Scalar variables.

If the Update/Delete affects multiple rows, we need to use BULK COLLECT and COLLECTION to grab the modified data using the RETURNING Clause.

CURSORS : Explict Cursors

EXPLICIT CURSORS:

Syntax:
CURSOR cursor_name [ ( [ parameter [, parameter ...] ) ]
       [ RETURN return_specification ]
       IS SELECT_statement
           [FOR UPDATE [OF [column list]];

USAGE FLOW:
Declare the cursor in the declaration block.
Open the cursor in execution block.
fetch the data from the opened cursor.
Close the cursor.


1. OPEN :

OPEN cursor_name [ ( arguments,... ) ];

arguments are the values to be passed, if the cursor was declared with a parameter list.

When we open the cursor,PL/SQL executes the query for that cursor.
Regardless of the when we perform the fetch, it reflects the data as it existed when cursor was opened.
If Select statement in the cursor uses a FOR UPDATE,all rows will be locked untill cursor is closed.

We can't open an already opened cursor untill closed.

2. FETCH :

FETCH cursor_name INTO record_or_variable_list;

We should always fetch into a record that was defined with %ROWTYPE against the cursor.

Once a cursor is open, we can fetch from it untill no more rows left but we can continue to fetch past the last record.we don't get any exception & fetch doesn't do anything( it wont' even make the variables to null).

Example:

Declare
-- returns zero records
cursor my_test_cursor is select sysdate from dual where 1=2;
my_date date:=sysdate+10;
begin
open my_test_cursor;
fetch my_test_cursor into my_date;
-- The variable is not set to NULL and it has the default value.
dbms_output.put_line(my_date);
end;



Note: All the calculated or virtual columns need to be aliased in the Explicit cursors.

3. CLOSE :

CLOSE cursor_name;

If we leave too many cursors open, we may exceed the value set by the database initialization parameter, OPEN_CURSORS.
If this happens, we will encounter error message:
    ORA-01000: maximum open cursors exceeded
If we get this message, check the usage of package-based cursors to make sure they are closed
when no longer needed.
BEGIN
       OPEN my_package.my_cursor;
       ... Do stuff with the cursor
       CLOSE my_package.my_cursor;
    EXCEPTION
       WHEN OTHERS
       THEN
          CLOSE my_package.my_cursor;
    END;

Explicit Cursor Attributes :

1. cursor_NAME%FOUND    : TRUE if a record was fetched good.
2. cursor_NAME%NOTFOUND : FALSE if a record was fetched good.
3. cursor_NAME%ROWCOUNT : Number of records fetched at that point in time.
4. cursor_NAME%ISOPEN   : TRUE if cursor is open.

EXCEPTIONS:

INVALID_CURSOR
If we try to use %FOUND, %NOTFOUND, or %ROWCOUNT before the cursor is opened
or after it is closed, Oracle will raise an INVALID_CURSOR error.

Cursor Parameters:
We can parameterize the Cursors and make it generic.
we can specify as many cursor parameters as we need.
When we OPEN the cursor, we need to include an argument in the parameter list for each parameter, except for trailing parameters that have default values.


The scope of the cursor parameter is confined to that cursor.

Example:

CURSOR emp_cur (emp_id_in NUMBER := 0)
    IS
       SELECT employee_id, emp_name
         FROM employee
        WHERE employee_id = emp_id_in;

Cursors : Implicit Cursors

Cursors:


When we execute a SQL statement from PL/SQL, the Oracle assigns a private work area for that statement and also manages the data specified by the SQL statement in the SGA.
The private work area contains information about the SQL statement and the set of data returned or affected by that statement.


Implicit Cursors:

PL/SQL declares and manages an implicit cursor every time we execute an DML (SELECT INTO,INSERT,UPDATE,DELETE).

SYNTAX:

SELECT column_list
      [BULK COLLECT] INTO PL/SQL variable list
      ...SQL SELECT statement...


A common use of implicit cursors is to perform a lookup based on a primary key.(returns only one row)

If we need to fetch multiple rows

1. Go for Explicit cursors.
2. BULK COLLECT INTO 


Implicit cursors can't be controlled.


Common Exceptions:

1. NO_DATA_FOUND
2. TO_MANY_ROWS


When ever these exceptions occur,execution of the current block terminates and control is passed to the execution section.

Best practice:

Include the implicit cursor as a separate PL/SQL BLOCK.



DECLARE
-------
-------
Begin
-------
-------
     Begin
        Select xxxx INTO xxxx from table where <conditions>; --<Implicit Cursor>
     Exception
        When NO_DATA_FOUND then
         xxxxxx;
        When TO_MANY_ROWS then
        xxxxxxx;
     END;
--------
--------
Exception
---------
---------
END;
/



EXAMPLE:

Create new email account in email_account TABLE

LOGIC FLOW: check if the entered email exists,
                          if exists return 'NOT AVAILABLE'
                          else 'AVAILABLE'


CREATE Function Check_email_availability
(entered_email in varchar2)
return varchar2

begin

   select email_address into return_value from email_account
   where email=entered_email;

   Return return_value;

Exception
 When NO_DATA_FOUND 
 then
      Return NULL;
 When TO_MANY_ROWS
 then
      DBMS_OUTPUT.PUT_LINE('DATA INTEGRITY');
      RAISE;
END;

-- CALLING PL/SQL BLOCK --

Declare
-------
-------
-------
Begin

  IF (check_email_availability) IS NULL

      'Email Available'
  else
  
      'Email is not Available'
  END IF;

-------
-------
-------
EXCEPTION
-------
-------
END;


IF      select email_address into return_value from email_account
        where email=entered_email;
doesn't return any row means the entered email is not used by anyone and can be used, but the implicit cursor raises NO_DATA_FOUND exception and we handle it and return NULL.


IF       select email_address into return_value from email_account
         where email=entered_email;
returns more than one, it means something wrong with the table design.



CURSOR ATTRIBUTES:

1. SQL%FOUND    : Returns TRUE if one row was fetched.
2. SQL%NOTFOUND : Returns FALSE if one row was fetched.
3. SQL%ROWCOUNT : Returns the number of rows fetched from the cursor.(SELECT INTO : 1)
4. SQL%ISOPEN   : Alway FALSE

All the implicit cursor attributes return NULL if no implicit cursors have yet been executed in the session.
Otherwise, the values of the attributes always refer to the most recently executed SQL statement, regardless
of the block or program from which the SQL statement was executed.

DML : FORALL ( BULK: INSERT / UPDATE / DELETE )

FORALL:


The FORALL statement allows us to pass multiple SQL statements all together (in bulk) to the SQL engine.


Syntax:


 FORALL index_row IN
       [ lower_bound ... upper_bound |
         INDICES OF indexing_collection |
         VALUES OF indexing_collection
       ]
       [ SAVE EXCEPTIONS ]
       sql_statement;




Cursor Attributes :




SQL%FOUND : TRUE : If last execution of the sql statement modifies atleast one.


SQL%ROWCOUNT : Returns the total number of rows processed by all executions  of the SQL statement,not just the last statement.


SQL%BULK_ROWCOUNT: Returns a collection that tells us the number of rows processed by each SQL statement via FORALL.




Difference between SQL%ROWCOUNT & SQL%BULK_ROWCOUNT: 


SQL%ROWCOUNT : Grand total


SQL%BULK_ROWCOUNT: Individual Count for every sql statement in the FORALL.




SQL%BULK_ROWCOUNT pseudo-collection use the same subscripts derived from FORALL Statement.




If any update statement through FORALL, updates more than one row (in a specific iteration) then the SQL%BULK_ROWCOUNT value at that subscript  will be equal to the number of rows updated.




EXAMPLE:
CREATE TABLE FORALL_TABLE_TEST(ITERATION_NUM NUMBER);


DECLARE  


   TYPE NUM_T IS TABLE OF NUMBER;
   COLLECTION_NUM_T   NUM_T := NUM_T ();
  
   START_TIME         NUMBER;
   END_TIME           NUMBER;
   EXECUTION_TIME     NUMBER;


BEGIN
  
  FOR INDX IN 1 .. 10000
  LOOP
      COLLECTION_NUM_T.EXTEND;
      COLLECTION_NUM_T (INDX) := INDX * 10;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE ('COLLECTION COUNT:' ||
                         COLLECTION_NUM_T.COUNT
                       );

  -- TABLE INSERT VIA REGULAR LOOPING


  START_TIME := DBMS_UTILITY.GET_TIME ();

  FOR I IN COLLECTION_NUM_T.FIRST .. COLLECTION_NUM_T.LAST
  LOOP
    INSERT INTO FORALL_TABLE_TEST (ITERATION_NUM)
    VALUES   (COLLECTION_NUM_T (I));
  END LOOP;

  END_TIME := DBMS_UTILITY.GET_TIME ();

  DBMS_OUTPUT.PUT_LINE ('INSERT:SQL%ROWCOUNT:' ||
                         SQL%ROWCOUNT
                       );
  COMMIT;

  EXECUTION_TIME := (END_TIME - START_TIME);
  DBMS_OUTPUT.PUT_LINE ('Time taken:LOOP INSERT:' ||
                         EXECUTION_TIME
                       );




  DELETE   FORALL_TABLE_TEST;

  DBMS_OUTPUT.PUT_LINE ('DELETE:SQL%ROWCOUNT:' ||
                         SQL%ROWCOUNT
                       );
  COMMIT;
  
  -- TABLE INSERT VIA FORALL

  START_TIME := DBMS_UTILITY.GET_TIME ();
  
  FORALL INDX IN COLLECTION_NUM_T.FIRST .. COLLECTION_NUM_T.LAST
    INSERT INTO FORALL_TABLE_TEST
    VALUES   (COLLECTION_NUM_T (INDX));

  END_TIME := DBMS_UTILITY.GET_TIME ();

  EXECUTION_TIME := (END_TIME - START_TIME);
  
  DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' ||
                         SQL%ROWCOUNT
                       );

  DBMS_OUTPUT.PUT_LINE ('SQL%BULK_ROWCOUNT:' ||
                         SQL%BULK_ROWCOUNT (10000)
                       );
  
  DBMS_OUTPUT.PUT_LINE ('Time taken:FORALL:' ||
                         EXECUTION_TIME
                       );
  
  DELETE   FORALL_TABLE_TEST;

  DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' ||
                         SQL%ROWCOUNT
                       );
  COMMIT;
  
END; 



What happens when one of the DML statement fails in FORALL:
1. FORALL stops executing.
2. Any Previous DML operations in that FORALL that already executed without an error are not rolledback.
EXAMPLE:


CREATE TABLE FORALL_TABLE_TEST (ITERATION_NUM NUMBER UNIQUE);

DECLARE  
   TYPE NUM_T IS TABLE OF NUMBER;
   COLLECTION_NUM_T   NUM_T := NUM_T ();

   START_TIME         NUMBER;
   END_TIME           NUMBER;
   EXECUTION_TIME     NUMBER;

BEGIN
-- Collection values will be 0 to 19 and it will have 5 sets of them.

   FOR INDX IN 1 .. 100
   LOOP
      COLLECTION_NUM_T.EXTEND;
      COLLECTION_NUM_T (INDX) := mod(INDX,20);
   END LOOP;


-- TABLE INSERT VIA FORALL
  
   START_TIME := DBMS_UTILITY.GET_TIME ();

   FORALL INDX IN COLLECTION_NUM_T.FIRST .. COLLECTION_NUM_T.LAST
      INSERT INTO FORALL_TABLE_TEST
        VALUES   (COLLECTION_NUM_T (INDX));

   END_TIME := DBMS_UTILITY.GET_TIME ();
  
   EXECUTION_TIME := (END_TIME - START_TIME);
   DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' || SQL%ROWCOUNT);
   DBMS_OUTPUT.PUT_LINE ('Time taken:FORALL:' || EXECUTION_TIME);

 Exception
    When others then
     NULL;
END;


Select * from FORALL_TABLE_TEST;

We see 0 to 19 got inserted and then it raised an exception (unique constraint violation) while executing the FORALL statement but all the successful insert statements didn't get rolled back.


Can we instruct oracle to pass the errors and  process till the end Via FORALL ? YES

How? Using SAVE EXCEPTIONS Clause.

SAVE EXCEPTIONS:


When we use SAVE EXCEPTIONS with in the FORALL statement, it saves the exceptions and once completion on FORALL, it raises ORA-24381 exception.

All the exceptions will be saved to pseudo-collection, SQL%BULK_EXCEPTIONS.


SQL%BULK_EXCEPTIONS :

This pseudo-collection has two fields

1. ERROR_INDEX: This is same as the index at which the sql statement raised an error.
2. ERROR_CODE : The error code is saved as positive number.(multiply by -1)


EXAMPLE:


DECLARE

   TYPE NUM_T IS TABLE OF NUMBER;
   COLLECTION_NUM_T   NUM_T := NUM_T ();

   START_TIME         NUMBER;
   END_TIME           NUMBER;
   EXECUTION_TIME     NUMBER;

BEGIN
-- Collection values will be 0 to 19 and it will have 5 sets of them.
   FOR INDX IN 1 .. 100
   LOOP
      COLLECTION_NUM_T.EXTEND;
      COLLECTION_NUM_T (INDX) := mod(INDX,20);
   END LOOP;

-- TABLE INSERT VIA FORALL
   START_TIME := DBMS_UTILITY.GET_TIME ();

   FORALL INDX IN COLLECTION_NUM_T.FIRST .. COLLECTION_NUM_T.LAST
   SAVE EXCEPTIONS
      INSERT INTO FORALL_TABLE_TEST
        VALUES   (COLLECTION_NUM_T (INDX));

   END_TIME := DBMS_UTILITY.GET_TIME ();
   EXECUTION_TIME := (END_TIME - START_TIME);

   DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' || SQL%ROWCOUNT);
   DBMS_OUTPUT.PUT_LINE ('Time taken:FORALL:' || EXECUTION_TIME);

Exception
    When others then
     FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT
     LOOP
       dbms_output.put_line('error_index:'||sql%bulk_exceptions(i).error_index);
       dbms_output.put_line('error_code:'||sql%bulk_exceptions(i).error_code);
       dbms_output.put_line('error message:'||sqlerrm(sql%bulk_exceptions(i).error_code*-1));
     END LOOP;
END;

FORALL with NON-SEQUENTIAL COLLECTIONS:

Prior to Oracle 10g, the binding collections must be densely packed with no gaps in it.

we get the error:
ORA-22160: element at index [X] does not exist.

EXAMPLE:


DECLARE
 
   TYPE NUM_T IS TABLE OF NUMBER;
   COLLECTION_NUM_T   NUM_T := NUM_T ();
  
  START_TIME         NUMBER;
   END_TIME           NUMBER;
   EXECUTION_TIME     NUMBER;

BEGIN


   EXECUTE IMMEDIATE 'TRUNCATE TABLE FORALL_TABLE_TEST';
  
   FOR INDX IN 1 .. 100
     LOOP
        COLLECTION_NUM_T.EXTEND;
        COLLECTION_NUM_T (INDX) := INDX;
     END LOOP;
    
     COLLECTION_NUM_T.DELETE(25);
     COLLECTION_NUM_T.DELETE(50);
     COLLECTION_NUM_T.DELETE(75);
     COLLECTION_NUM_T.DELETE(100);

-- TABLE INSERT VIA FORALL

   START_TIME := DBMS_UTILITY.GET_TIME ();

   FORALL INDX IN COLLECTION_NUM_T.FIRST .. COLLECTION_NUM_T.LAST
      INSERT INTO FORALL_TABLE_TEST
        VALUES   (COLLECTION_NUM_T (INDX));
   END_TIME := DBMS_UTILITY.GET_TIME ();

   EXECUTION_TIME := (END_TIME - START_TIME);
   DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' || SQL%ROWCOUNT);
   DBMS_OUTPUT.PUT_LINE ('Time taken:FORALL:' || EXECUTION_TIME);

EXCEPTION

    When others then
       dbms_output.put_line(SQLERRM);
END;


ERROR: ORA-22160: element at index [25] does not exist


But starting ORACLE 10g, we can use

1.INDICES OF
2.VALUES OF

This is how it works:

1.INDICES OF
We build a driving collection, which will be populated with the index numbers same as index numbers of binding collection.

2.VALUES OF
We build a driving collection, which will be populate with the  index numbers of the binding collection.


EXAMPLES:

DECLARE

  

   TYPE NUM_T IS TABLE OF NUMBER;

   COLLECTION_NUM_T   NUM_T := NUM_T ();

   

  START_TIME         NUMBER;

   END_TIME           NUMBER;

   EXECUTION_TIME     NUMBER;

BEGIN

   EXECUTE IMMEDIATE 'TRUNCATE TABLE FORALL_TABLE_TEST';

   

   FOR INDX IN 1 .. 100

     LOOP

        COLLECTION_NUM_T.EXTEND;

        COLLECTION_NUM_T (INDX) := INDX;

     END LOOP;

     

     COLLECTION_NUM_T.DELETE(25);

     COLLECTION_NUM_T.DELETE(50);

     COLLECTION_NUM_T.DELETE(75);

     COLLECTION_NUM_T.DELETE(100);

-- TABLE INSERT VIA FORALL

   START_TIME := DBMS_UTILITY.GET_TIME ();

   FORALL INDX IN INDICES OF COLLECTION_NUM_T

      INSERT INTO FORALL_TABLE_TEST

        VALUES   (COLLECTION_NUM_T (INDX));

   END_TIME := DBMS_UTILITY.GET_TIME ();

   EXECUTION_TIME := (END_TIME - START_TIME);

   DBMS_OUTPUT.PUT_LINE ('FORALL:SQL%ROWCOUNT:' || SQL%ROWCOUNT);

   DBMS_OUTPUT.PUT_LINE ('Time taken:FORALL:' || EXECUTION_TIME);

EXCEPTION

    When others then

       dbms_output.put_line(SQLERRM);

END;

BULK BINDING IN ORACLE

Oracle has two engines

1. PL/SQL engine
2. SQL engine

When ever an SQL statement appears in the PL/SQL block, it will be sent to SQL engine, known as
Context-switching.This involves overhead and processing.

For example, if we have a for loop executing insert statement 1000 times
(Inserting 1000 records)

for i in 1..1000
loop
insert into context_switch_tab (iteration_number) values (i);
end loop;

for every iteration, insert statement will be built and sent to the sql engine, meaning 1000 times
context switching occurs.

Here comes the concept of BULK BINDING.

Assume that you build the 1000 insert statements at a stretch and
pack them into a single bundle (TAR all the sql statements  or  zip all the sql statements)
and then send it to the sql engine to execute the 1000 insert statements.

In the above process, only one context-switch is needed for 1000 insert statements
(But make a note that memory is needed to  store the 1000 built sql insert statements till the context switch occurs)

DML : RECORDS

We can use records in Insert and Update statements.

Record based Inserts:

We can use records that are
1.Based on %ROWTYPE declarations against the table to which the insert is to be made.
2.Explicit record type that is compatible with the structure of the table.


Example:

Create table student
(Rno number,
 name varchar2(100),
 branch varchar2(10));
insert into student values (1,'Kiran','ECE');
insert into student values (2,'Gowtham','ECE');
insert into student values (3,'Lokesh','CSE');
commit;

-- Based on %ROWTYPE declarations against the table to which the insert is to be made.

Declare
  student_rec  student%rowtype;
Begin
  student_rec.rno:=4;
  student_rec.name:='Srikanth';
  student_rec.branch:='ECE';
  Insert into student values student_rec;
  commit;
End;
/

-- Explicit record type that is compatible with the structure of the table.

Declare
  Type student_t is record  (  rno  student.rno%type,
                                            name student.name%type,
                                           branch student.branch%type);
  student_rec student_t;
Begin
  student_rec.rno:=5;
  student_rec.name:='Vijay krishna';
  student_rec.branch:='CSE';
  Insert into student values student_rec;
  commit;
End;
/

Note:
          Don't include parentheses around the record specifier.

Record based updates:(keyword: ROW)

We must update an entire row with the ROW syntax.

Declare
Type student_t is record  (  rno  student.rno%type,
                             name student.name%type,
                             branch student.branch%type);
student_rec student_t;
Begin
student_rec.rno:=5;
student_rec.name:='Vijay krishna P';
student_rec.branch:='CSE';
update student set row=student_rec
where rno=student_rec.rno;
commit;
End;
/

DML : RETURNING CLAUSE

We can use the RETURNING clause within the DML (UPDATE/DELETE) to grab the modified info
by the statement into variables(collections).

The same can be done in 3 steps

1. Execute the DML statement
2. Query(select) and get the new values into variable.
3. Use the variables in the code.

This can be avoided by using the RETURNING Clause.

1.Execute the DML Statement and Grab the modified values into Variables on the fly.
2. Use the variables in the code.

Note: 

If the Update/Delete affects only one row, we can use the RETURNING Clause and grab the modified data into the Scalar variables.

If the Update/Delete affects multiple rows, we need to use BULK COLLECT and COLLECTION to grab the modified data using the RETURNING Clause.

Example:

Declare

type emp_rec is record (empno scott.emp.empno%type,sal scott.emp.sal%type);

type emp_rec_t is table of emp_rec;

old_sal_rec_t emp_rec_t := emp_rec_t();

new_sal_rec_t emp_rec_t := emp_rec_t();

Begin

select empno,sal bulk collect into old_sal_rec_t from scott.emp where deptno=10;

update scott.emp set sal=sal*1.1 where deptno=10 returning empno,sal bulk collect into new_sal_rec_t;

dbms_output.put_line('Number of employees got the pay hike:'||SQL%ROWCOUNT);

for i in old_sal_rec_t.first..old_sal_rec_t.last

loop

dbms_output.put_line(old_sal_rec_t(i).empno || ':' || old_sal_rec_t(i).sal);

dbms_output.put_line(new_sal_rec_t(i).empno || ':' || new_sal_rec_t(i).sal);

end loop;

End;

/

DML : Exception Handling

When an exception occurs in a PL/SQL block, Oracle doesn't rollback any of the changes made by the DML statements in that block. We need to decide on the rollback explicitly.


Example:

-- Before DML

select * from scott.emp where deptno=10;

-- DML with in PL/SQL Block

Begin

update scott.emp set sal=sal*1.1 where deptno=10;

dbms_output.put_line('Number of employees got the pay hike:'||SQL%ROWCOUNT);

-- raise an exception

raise no_data_found;

Exception

when no_data_found then
dbms_output.put_line('Check the values for the update statement: Values got updated');

End;
/


-- After DML in PL/SQL Block 

select * from scott.emp where deptno=10;


Points to remember:


1. when an exception is raised,we must perform a rollback or commit (usually a rollback)

2. If an exception propagates past the outermost block (goes unhandled),
    then in most host execution environments for PL/SQL like SQL*Plus,
    an unqualified rollback is automatically executed.

3. If an exception is handled and we don't rollback the changes in the exception block, all the dml's get    committed. (Use Rollback in the Exception Block to revert the affects of DML statements in the block)

DML : CURSOR ATTRIBUTES

We can execute any DML statements from within PL/SQL, however,
we cannot execute DDL statements in PL/sQL unless we run them as dynamic SQL.

DML Operations:
INSERT / UPDATE / DELETE / SELECT

Cursor Attributes for DML Operations:

Implicit Cursor attributes return information about the execution of the most recent DML,regardless
of the block in which the implicit cursor is executed.

Start of the Session: All implicit cursor attributes yield NULL

1. SQL%FOUND ( TRUE, if one or more rows were modified )

2. SQL%NOTFOUND ( TRUE, if none of the rows were modified )

3. SQL%ROWCOUNT ( Returns Number of rows modified )

4. SQL%ISOPEN (FALSE)


Out of the above 4, we mostly use SQL%FOUND & SQL%ROWCOUNT.

SQL%FOUND : Determine if the DML statement affected any rows.

SQL%ROWCOUNT : Determine exactly how many rows were affected.


Example:

Begin

Update Scott.Emp Set Sal=(Sal*1.1) where Deptno=10;

dbms_output.put_line('Did any of the employees got the pay hike (Yes/NO):');

IF ( SQL%FOUND ) THEN
dbms_output.put_line('YES');
ELSE
dbms_output.put_line('NO');
END IF;

dbms_output.put_line('Number of employees got the pay hike:');
dbms_output.put_line(SQL%ROWCOUNT);

commit;
End;
/

INSTR SQL Function

INSTR FUNCTION returns the position of the search string.

We generally use INSTR function within the SUBSTR function to calculate the position parameters in it.

SELECT INSTR(<value>, <value_to_match>, <direction>, <instance>) FROM Table_Name;

<direction> = 1 forward

<direction> = -1 reverse search

 <instance> = Time of occurrence

Examples: 

Get the position of the second occurrence of 'k' in the given string.

SELECT  INSTR('kiran kodavati','k',1,2) FROM DUAL;

Result : 7

Get the position of the third occurrence of 'a' in the given string from back.

SELECT  INSTR('kiran kodavati','a',-1,3) FROM DUAL;

Result: 4

Get the string starting the second occurrence of 'K' in the given string.

SELECT  SUBSTR('KIRAN KODAVATI',INSTR('KIRAN KODAVATI','K',1,2)) FROM DUAL;

Result : KODAVATI.

Collections in Oracle - Part 4

Collections continued .....


Working with Collections in SQL:

Oracle introduced the below pseudo-functions to manipulate collections that live in the database.

CAST

Maps a collection of one type to a collection of another type. This can encompass mapping a VARRAY to a nested table.

MULTISET

Maps a database table to a collection. With MULTISET and CAST, you can actually retrieve rows from a database table as a collection-typed column.

TABLE

Maps a collection to a database table. This is the inverse of MULTISET.

Note:
The collection pseudo-functions are not available in PL/SQL, only in SQL.
We can, however, employ these operators in SQL statements that appear in your PL/ SQL code.

CAST:

The CAST operator can be used in a SQL statement to convert from one built-in datatype to another built-in datatype.


CREATE OR REPLACE TYPE NAMES_NT is TABLE of VARCHAR2(200);
CREATE OR REPLACE TYPE NAMES_VA  IS VARRAY(10) OF VARCHAR2(200);

Declare
NAMES_LIST_NT NAMES_NT := NAMES_NT('KIRAN','VISHALI','KODAVATI','SENIVARAPU');
NAMES_LIST_VA NAMES_VA := NAMES_VA();
BEGIN
SELECT CAST(NAMES_LIST_NT AS NAMES_VA) INTO NAMES_LIST_VA FROM DUAL;
FOR I IN NAMES_LIST_VA.FIRST..NAMES_LIST_VA.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(NAMES_LIST_VA(I));
END LOOP;
END;
/

MULTISET:

The MULTISET function exists only for use within CAST.

MULTISET allows you to retrieve a set of data and convert it on the fly to a collection type.

SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type)
       FROM DUAL;


EXAMPLE:

CREATE OR REPLACE TYPE NAMES_VA  IS VARRAY(10) OF VARCHAR2(200);


Declare

NAMES_LIST_VA NAMES_VA := NAMES_VA();
MY_LIST_LIMIT NUMBER;

BEGIN

MY_LIST_LIMIT:=NAMES_LIST_VA.LIMIT;

SELECT CAST(MULTISET(
                      SELECT ENAME FROM SCOTT.EMP
                      WHERE ROWNUM < MY_LIST_LIMIT+1
                    )
                       AS NAMES_VA
           ) INTO NAMES_LIST_VA
FROM DUAL;

FOR I IN NAMES_LIST_VA.FIRST..NAMES_LIST_VA.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(NAMES_LIST_VA(I));
END LOOP;

END;
/

NOTE: MULTISET cannot serve as the target of an INSERT, UPDATE, or DELETE statement.


TABLE: ( key word : COLUMN_VALUE )

The TABLE operator converts a collection-valued column into something you can SELECT from.

Example:
CREATE OR REPLACE TYPE NAMES_T AS TABLE OF VARCHAR2 (100);

DECLARE
       MY_favorites   NAMES_T
          := NAMES_T ('KIRAN', 'KODAVATI');
    BEGIN
       FOR rec IN  (SELECT column_value favs
                      FROM TABLE (MY_favorites)
                    WHERE COLUMN_VALUE LIKE 'KO%'
                    )
       LOOP
          DBMS_OUTPUT.put_line (rec.favs);
       END LOOP;
    END;
/


DATA DICTIONARIES:

SELECT type_name FROM user_types WHERE typecode ='COLLECTION';

SELECT text FROM user_source WHERE type = 'TYPE';

SELECT table_name,column_name FROM user_tab_columns WHERE data_type = 'XXXX';

SELECT name, type FROM user_dependencies WHERE referenced_name='XXXX';


Privileges:


Only one Oracle privilege  EXECUTE applies to collection types.

Note: Even if a user has select privilege on a table and if the table has a column of collection type, the user can select the table only if he has execute privilege on the type of the collection column.