PL/SQL Table and Nested Table

                                                    PL/SQL Table and Nested Table

 Introduction:

As you know, a datatype is used to classify the data to be stored as number, varchar, boolean etc. But a varible declared using a dataype can hold only a single data value at a time, i.e a single memory location.

What if we need to store multiple rows of data in a single variable? There comes the use of collections in Oracle. A variable declared as a collection could store anarray of data of the same TYPE or ROWTYPE. This is beneficial when we need to pass multiple rows of data between Procedures or need to return multiple records from functions especially to other languages like Java, C etc.

 These are the main classifications:

1.      PL/SQL TABLE (index-by tables) or NESTED TABLE

2.      VARRAY

3.      RECORD

4.      OBJECT

Here I am discussing PL/SQL TABLE (index-by tables) and NESTED TABLE in detail

 1. PL/SQL TABLE (index-by tables) and NESTED TABLE

  These are single-column tables and could be considered as an Array of data but which is unbounded; i.e we cannot set a limit on number of rows like an array and that s sometimes an added advantage.

 Both PL/SQL TABLE and NESTED TABLE has got the same structure and all, but the main difference is, nested tables could be stored in a database  column whereas PL/SQL tables could not be. To explain further,  Nested table Dataypes can be created standalone they could be used for columns in normal database Tables.

 Declaration:

  a. NESTED TABLE

a.1 Delcare and use in PL/SQL code:

TYPE NTab_List1 is TABLE OF TYPE INTEGER;

Eg: CREATE OR REPLACE PROCEDURE PRC_NT

 IS

TYPE N Tab_List1 is TABLE OF TYPE INTEGER;       --create a Collection typeTYPENTab_List1

TAB NTab_List1;                                                                    --create a variable of NTab_List1 Type

 Begin

--

End;

Just know the Difference between Row Number, Rank and Dense rank

              Difference between Row Number, Rank and Dense rank

Which one to use? 

This is very common question in the minds of SQL newbie's. Lets take 1 simple example to understand the difference between 3. 

First lets create some sample data :

- create table CREATE TABLE Salaries ( Names VARCHAR(1), SalarY INT ) -- insert data INSERT INTO Salaries SELECT 'A',5000 UNION ALL SELECT 'B',5000 UNION ALL SELECT 'C',3000 UNION ALL SELECT 'D',4000 UNION ALL SELECT 'E',6000 UNION ALL SELECT 'F',10000 -- Test the data Select Names, Salary From Salaries

Now lets query the table to get the salaries of all employees with their salary in descending order.
For that I'll write a query like this :

Select  names , salary                ,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER               ,rank () OVER (ORDER BY salary DESC) as RANK              ,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK From  salaries

---------------Output

NAMES	SALARY	ROW_NUMBER	RANK	DENSE_RANK
F	10000	1	1	1
E	6000	2	2	2
A	5000	3	3	3
B	5000	4	3	3
D	4000	5	5	4
C	3000	6	6	5

Interesting Names in the result are employee A, B and D. Row_number assign different number to them.
Rank and Dense_rank both assign same rank to A and B.
But interesting thing is what RANK and DENSE_RANK assign to next row?
Rank assign 5 to the next row, while dense_rank assign 4.
The numbers returned by the DENSE_RANK function do not have gaps and always have consecutive  ranks.  The RANK function does not always return consecutive integers.  The ORDER BY clause  determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified  partition.
So question is which one to use?
Its all depends on your requirement and business rule you are following.
1. Row_number to be used only when you just want to have serial number on result set. It is not as intelligent     as RANK and DENSE_RANK.
2. Choice between RANK and DENSE_RANK depends on business rule you are following. Rank leaves       the gaps between number when it sees common values in 2 or more rows. DENSE_RANK don't leave        any gaps between ranks.
So while assigning the next rank to the row RANK will consider the total count of rows before that row and    DESNE_RANK will just give next rank according to the value.
So If you are selecting employee’s rank according to their salaries you should be using DENSE_RANK and   if you are ranking students according to there marks you should be using RANK
  (Though it is not mandatory, depends on your requirement.)