Oracle Collections (Index-By table)

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

types of collections

• Index-by table (associative arrays)
• Nested tables
• Varrays ( variable size arrays)

If you already have code or business logic that uses some other language, you can usually translate that language's array and set types directly to PL/SQL collection types.

• Arrays in other languages become VARRAYs in PL/SQL.
• Sets and bags in other languages become nested tables in PL/SQL.
• Hash tables and other kinds of unordered lookup tables in other languages become associative arrays in PL/SQL.

Index-by table

DECLARE
    i            VARCHAR (50);
    TYPE arraylike IS TABLE OF VARCHAR2 (50)
        INDEX BY VARCHAR2 (50);
    datearray    arraylike;
    cutidarray   arraylike;
BEGIN
    datearray (1) := '02-08-2015';
    datearray (2) := '02-08-2015';
    datearray (3) := '06-08-2015';
    datearray (4) := '06-08-2015';
    datearray (5) := '04-08-2015';
    datearray (6) := '04-08-2015';
    datearray (7) := '02-08-2015';
    datearray (8) := '04-08-2015';
    datearray (9) := '06-08-2015';
    datearray (10) := '02-08-2015';
    datearray (11) := '06-08-2015';
    datearray (12) := '04-08-2015';
    datearray (13) := '02-08-2015';
    datearray (14) := '06-08-2015';
    datearray (15) := '02-08-2015';
    cutidarray (1) := '11684129';
    cutidarray (2) := '11192994';
    cutidarray (3) := '11300838';
    cutidarray (4) := '11281815';
    cutidarray (5) := '11228291';
    cutidarray (6) := '11129267';
    cutidarray (7) := '11114680';
    cutidarray (8) := '11183177';
    cutidarray (9) := '11242009';
    cutidarray (10) := '11200434';
    cutidarray (11) := '11328917';
    cutidarray (12) := '11130167';
    cutidarray (13) := '11172261';
    cutidarray (14) := '11401292';
    cutidarray (15) := '11893237';

    FOR i IN 1 .. 15
    LOOP
        UPDATE m01_cma_complient_details cma
           SET cma.is_secondery_update = 1,
               cma.m01_other_next_review =
                   TO_DATE (datearray (i), 'dd-mm-yyyy')
         WHERE cma.m01_customer_id IN
                   (SELECT m01.m01_customer_id
                      FROM m01_customer m01
                     WHERE m01.m01_c1_customer_id = cutidarray (i));
    /*DBMS_OUTPUT.put_line (
        datearray (i) || '-----------' || cutidarray (i)); */
    END LOOP;
END;

Nested and Correlated Subqueries

Nested - The subquery executed first and the result is inserted to the main query.

SELECT *
  FROM t11_executed_orders t11
 WHERE t11.t11_symbol IN
           (SELECT aa.t11_symbol
              FROM t11_executed_orders aa
             WHERE aa.t11_value = (SELECT bb.t11_price * bb.t11_filled_volume
                                     FROM t11_executed_orders bb
                                    WHERE bb.t11_exec_id = '123'));

the inner most query will executed first and then the nest subquery and after that the main query.


Correlated - The main query executed first and the subquery is executed against each and every row returned by the main query.

UPDATE t11_executed_orders t11
   SET t11.t11_value =
           (SELECT a.t11_price * a.t11_filled_volume
              FROM t11_executed_orders a
             WHERE     t11.t11_exec_id = a.t11_exec_id
                   AND t11.t11_exectime_int = a.t11_exectime_int
                   AND t11.t11_side = a.t11_side
                   AND t11.t11_exchange = a.t11_exchange)
 WHERE t11.t11_value <>
           (SELECT a.t11_price * a.t11_filled_volume
              FROM t11_executed_orders a
             WHERE     t11.t11_exec_id = a.t11_exec_id
                   AND t11.t11_exectime_int = a.t11_exectime_int
                   AND t11.t11_side = a.t11_side
                   AND t11.t11_exchange = a.t11_exchange);



the same update can be written in PL-SQL as well . like this.

BEGIN
    FOR i IN (SELECT *
                FROM (SELECT t11_exec_id,
                             t11_exchange,
                             t11_side,
                             t11_datetime,
                             t11_symbol,
                             t11_routingac,
                             t11_price * t11_filled_volume
                                 AS calculated_t11_value,
                             t11_value
                        FROM mubasher_oms.t11_executed_orders)
               WHERE calculated_t11_value <> t11_value)
    LOOP
        UPDATE mubasher_oms.t11_executed_orders
           SET t11_value = i.calculated_t11_value
         WHERE     t11_exec_id = i.t11_exec_id
               AND t11_exchange = i.t11_exchange
               AND t11_side = i.t11_side;
    END LOOP;
END;

for more infor

http://sql-plsql.blogspot.com/2011/09/difference-nested-correlated-subquery.html

Analytical Functions in Oracle

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174

if i explain this with a simple example .. lets say we have a to take a data set like this..

"select  sum(order_amt) from t01_orders group by order_no "


this will certainly  return the sum of each order amount . but what if want to show other details like customer name and all.. you can not add that column to group by clause.


"select sum(order_amt) over(partition by order_no) , * from t01_orders"


simple like that ... :)