Social BI ..continous..

Business intelligence solutions english from Stratebi

Social bi ..Sounds good..

Social Intelligence 2013 from Stratebi

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.)

The Oracle Merge Command

Say you would like to take transformed data and use it to update our dimension table. In the past, we'd have accomplished this by a combination of INSERT and UPDATE statements, but starting with Oracle 9i we've now got the Oracle MERGE command which does this in one step. What's more, we can wrap up the whole transformation process into this one Oracle MERGE command, referencing the external table and the table function in the one command as the source for the MERGED Oracle data.

        alter session enable parallel dml;
    merge /*+ parallel(contract_dim,10) append */
        into contract_dim d
        using TABLE(trx.go(
            CURSOR(select /*+ parallel(contracts_

file,10) full (contracts_file) */ *
                from contracts_file ))) f
        on  d.contract_id = f.contract_id
        when matched then
            update set   
                                   desc  = f.desc,
                       init_val_loc_curr = f.init_val_loc_curr,
                       init_val_adj_amt  = f.init_val_adj_amt
        when not matched then
            insert values ( f.contract_id,
                            f.desc,
                            f.init_val_loc_curr,
                            f.init_val_adj_amt); 
           

So there we have it - our complex ETL function all contained within a single Oracle MERGE statement. No separate SQL*Loader phase, no staging tables, and all piped through and loaded in parallel.

Enhanced Merge Functionality

In Oracle Database 10g, the MERGE statement has been extended to cover a larger variety of complex and conditional data transformations, allowing faster loading of large volumes of data.

You should use the MERGE statement to select rows from one or more sources for insert or update of one or more tables. The decision to perform an insert or update is based on conditions specified by you.

The MERGE statement is designed to combine multiple operations to reduce the complexity of mixed insert and update operations. MERGE allows you to avoid multiple INSERT, UPDATE, and DELETE DML statements by combining the operations into a single statement. MERGE is what is known as a deterministic statement. That is, you can only update the same row of the target table once in the same MERGE statement.

Prerequisites

Since MERGE combines INSERT and UPDATE operations, you must have the INSERT and UPDATE object privileges on the target table, and of course, the SELECT object privilege on the source table. If you need to specify the DELETE clause of the merge_update_clause, then you must also have the DELETE object privilege on the target table.

Merge Syntax

The syntax of the MERGE statement is:

MERGE [hint] INTO [schema .]table [t_alias]
USING [[schema .]table | view | subquery] t_alias
ON ( condition ) [merge_update_clause | merge_insert_clause]

merge_update_clause:

WHEN MATCHED THEN UPDATE SET [ = [|DEFAULT][,]]
DELETE

merge_insert_clause:

WHEN NOT MATCHED THEN INSERT ( [,])
VALUES (|DEFAULT[,])

where_clause:

The clauses in the MERGE statement have the following definitions.

INTO Clause - The INTO clause is used to specify the target table into which you are inserting or updating.

USING Clause - The USING clause specifies the source of the data to be updated or inserted. The source for a MERGE statement can be a table, view, or the result of a subquery.

ON Clause - The ON clause specifies the condition that the MERGE operation uses to determine whether it updates or inserts. When the search condition evaluates to true, Oracle updates the row in the target table with corresponding data from the MERGE source. If no rows satisfy the condition, then Oracle inserts the row into the target table based on the corresponding MERGE source row.

merge_update_clause - The merge_update_clause is used to specify the update column values of the target table. Oracle performs the specified update if the condition of the ON clause is true. As with any normal update, when the update clause is executed, all update triggers defined on the target table are fired.

where_clause - You must specify the where_clause if you want Oracle to execute the update operation only if the specified condition is true. The WHERE condition can apply to either the data source or the target table. If the condition is false, the update operation is skipped when merging the row into the target table.

You can specify the DELETE where_clause to clean up data in a table while the MERGE statement is populating or updating it. The only rows affected by the delete clause of the MERGE statement are those rows in the target table that are updated by the merge operation.

This means the DELETE WHERE condition evaluates the updated value, not the original value of the row. Even if a row of the target table satisfies the DELETE condition but is not included in the data set from the join defined by the MERGE's ON clause, then it is not deleted. If the MERGE statement deletes a row, any delete triggers defined on the target table will be activated for each row deletion.

merge_insert_clause – The WHERE clause can be specified by itself or in concert with the merge_insert_clause. If both are specified, then the order of the clauses is not important.

View Update Restrictions

• You are not allowed to specify DEFAULT when updating a view.
• Any column referenced in the ON condition cannot be updated.

merge_insert_clause

To specify the values used for insert operations the merge_insert_clause is used. The MERGE statement uses the merge_insert_clause when the condition of the ON clause is false. As with any normal insert, if the insert clause is executed, all insert triggers defined on the target table are fired. If the column list after the INSERT keyword is left out, the number of columns in the target table must match the number of values in the VALUES clause.

If you wish to insert all of the MERGE source rows into the table, you should use what is known as a "constant filter predicate" in the ON clause. An example of a constant filter predicate would be:

ON (1=2)

A predicate such as the one shown is recognized by Oracle as a special case, and Oracle makes an unconditional insert of all source rows into the table. The benefit of this approach over just omitting the merge_update_clause, Is that Oracle still must perform a join if the merge_update_clause is left out, while with a constant filter predicate, no join is performed.

You would specify the where_clause when you want Oracle to execute the insert operation only if the specified condition is true. The condition can refer only to the MERGE data source. Oracle will skip the insert operation for all rows where the condition evaluates to false.

You can specify the where_clause by itself or with the merge_update_clause. If both are specified, then they can be in either order.

n Example Merge

The following example is taken from the Oracle documentation for Oracle Database 10g. The example creates a bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses(employee_id)
SELECT e.employee_id FROM employees e, orders o

EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100

MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1)
 

EMPLOYEE_ID      BONUS

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

153        180
154        175
155        170
159        180
160        175
161        170
179        620
173        610
165        680
166        640
164        720
172        730
167        620
171        740

sql 11g

Oracle11g SQL Lab Guide

OBIEE11g Building Logical Dimension Hierarchy

OBIEE11g Building Logical Dimension Hierarchy