WITH Clause Queries

SELECT E.ENAME,E.DEPTNO , DC.EMP_COUNT

FROM EMP  E ,

( SELECT DEPTNO,COUNT(*) AS EMP_COUNT FROM EMP GROUP BY DEPTNO) DC

WHERE E.DEPTNO = DC.DEPTNO

/

WITH EMP_COUNT AS (

SELECT DEPTNO,COUNT(*) AS EMP_COUNT FROM EMP GROUP BY DEPTNO

)

SELECT E.ENAME , EC.EMP_COUNT

FROM EMP E, EMP_COUNT EC

WHERE E.DEPTNO = EC.DEPTNO

COLLECTIONS AND RECORDS

A collection is an ordered group of elements, all of the same type

PL/SQL offers these collection types:

• Index by tables, also known as Associative arrays
• Nested Tables
• Varrays

Associative Arrays:

Available only in PL/SQL, not in SQL

Can be indexed by BINARY_INTEGER, PLS_INTEGER, VARCHAR2

Nested Tables :

Available both in PL/SQL and SQL

Nested table is single-dimensional, meaning that each row has a single column of data like a one-dimension array.

VArrays:

Available both in PL/SQL and SQL

Choosing between Associative Arrays, VArrays and Nested Tables :

Associative Arrays:

• Relatively small Look up tables
• Passing Collection from or to Database Server

Associative Arrays Use Case : 

Eg: 

DECLARE 

    TYPE INDIAN_STATE IS RECORD (

        STATE VARCHAR2(20),

        CAPITAL VARCHAR2(20)

        );

        

    TYPE V_STATE IS TABLE OF INDIAN_STATE 

    INDEX BY BINARY_INTEGER ; 

    

    T_STATE V_STATE; 

BEGIN 

    T_STATE(1).STATE := 'ANDHRA';

    T_STATE(1).CAPITAL := 'AMARAVATHI'; 

    

    T_STATE(2).STATE := 'TELANGANA';

    T_STATE(2).CAPITAL := 'HYDERABAD'; 

    

    FOR X IN  1 .. 2  

    LOOP 

    DBMS_OUTPUT.PUT_LINE ( 'CAPITAL OF '||  T_STATE(X).STATE || ' IS '  || T_STATE(X).CAPITAL  ); 

    END LOOP; 

END ;

/

VArrays :

• No. of elements is known in advance
• Elements are usually accessed sequentially

Nested Tables:

• Index values are consecutive
• There are no set number of index values
• You must delete or update some elements, but not all at same time

Use case of Nested Tables: 

DECLARE 

    TYPE EMPLOYEES IS TABLE OF EMP.ENAME%TYPE; 

    V_EMPLOYEES EMPLOYEES := EMPLOYEES(); 

    I NUMBER ; 

BEGIN 

  I := 0 ; 

   FOR C IN (SELECT ENAME FROM EMP ) 

    LOOP

        I := I+1; 

        V_EMPLOYEES.EXTEND;

        V_EMPLOYEES(I) :=  C.ENAME; 

    END LOOP; 

    

    DBMS_OUTPUT.PUT_LINE ('EMPLOYEE COUNT IS '|| V_EMPLOYEES.COUNT);

    

    FOR X IN 1 ..V_EMPLOYEES.count   

    LOOP 

        DBMS_OUTPUT.PUT_LINE (V_EMPLOYEES(X)); 

    END LOOP; 

END;

/ 

Collection Exceptions :

• COLLECTION_IS_NULL
• NO_DATA_FOUND
• VALUE_ERROR
• SUBSCRIPT_BEYOND_COUNT
• SUBSCRIPT_OUTSIDE_LIMIT

Collection Methods:

• COUNT
• LIMIT
• FIRST
• LAST
• EXTEND
• EXTEND(n)
• TRIM
• TRIM(n)
• DELETE
• DELETE(n)
• DELETE(m,n)
• PRIOR(n)
• NEXT(n)