Saturday, September 9, 2017

Oracle PL/SQL Collections

1. Definition
This is what the documentation says about collections:
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.

PL/SQL offers these collection types:
         Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
         Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
         Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
Although collections can have only one dimension, you can model multi-dimensional arrays by creating collections whose elements are also collections.

To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters, to move data between client-side applications and stored subprograms.

To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.

2. Persistent and non-persistent collections
Index-by tables cannot be stored in database tables, so they are non-persistent.
You cannot use them in a SQL statement and are available only in PL/SQL blocks.
Nested tables and Varrays are persistent. You can use the CREATE TYPE statement to create them in the database, you can read and write them from/to a database column.
Nested tables and Varrays must have been initialized before you can use them.

3. Declarations

3.1 Nested tables

TYPE type_name IS TABLE OF element_type [NOT NULL];
With nested tables declared within PL/SQL, element_type can be any PL/SQL datatype except : REF CURSOR
Nested tables declared in SQL (CREATE TYPE) have additional restrictions. They cannot use the following element types:
       BINARY_INTEGER, PLS_INTEGER
       BOOLEAN
       LONG, LONG RAW
       NATURAL, NATURALN
       POSITIVE, POSITIVEN
       REF CURSOR
       SIGNTYPE
       STRING
  

3.2 Varrays

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
   OF element_type [NOT NULL];

size_limit is a positive integer literal representing the maximum number of elements in the array.
PL/SQL
Declare
   TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;

SQL
    CREATE [OR REPLACE] TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;
  

3.3 Index-by tables

TYPE type_name IS TABLE OF element_type [NOT NULL]
   INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;

The key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER(9i).
It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG. You must specify the length of a VARCHAR2-based key, except for LONG which is equivalent to declaring a key type of VARCHAR2(32760).
The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for an associative array.
 Declare
   TYPE TYP_TAB_VAR IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER ;

4. Initalization

Only Nested tables and varrays need initialization.
To initialize a collection, you use the “constructor” of the collection which name is the same as the collection.

4.1 Nested tables

Declare
   TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
    Nt_tab TYP_NT_NUM ;
Begin
   Nt_tab := TYP_NT_NUM( 5, 10, 15, 20 ) ;
End ;

4.2 Varrays

Declare
   TYPE TYP_V_DAY IS VARRAY(7) OF VARCHAR2(15) ;
   v_tab TYP_V_DAY ;
Begin
   v_tab := TYP_NT_NUM(Sunday,Monday,Tuesday,Wedneday,Thursday,Friday,Saturday) ;
End ;

It is not required to initialize all the elements of a collection. You can either initialize no element. In this case, use an empty constructor.
v_tab := TYP_NT_NUM() ;

This collection is empty, which is different than a NULL collection (not initialized).
  

4.3 Index-by tables

Declare
   TYPE TYP_TAB IS TABLE OF NUMBER INDEX BY PLS_INTEGER ;
   my_tab  TYP_TAB ;
Begin
   my_tab(1) := 5 ;
   my_tab(2) := 10 ;
   my_tab(3) := 15 ;
End ;

5. Handle the collection

While the collection is not initialized (Nested tables and Varrays), it is not possible to manipulate it.
You can test if a collection is initialized: 
The 10g release allows to do some comparaisons between collections:
You can compare collections of same type to verify if they ar equals or not equals

6. Methods

We can use the following methods on a collection:
         EXISTS
         COUNT
         LIMIT
         FIRST and LAST
         PRIOR and NEXT
         EXTEND
         TRIM
         DELETE

A collection method is a built-in function or procedure that operates on collections and is called using dot notation.
collection_name.method_name[(parameters)]

Collection methods cannot be called from SQL statements.

Only the EXISTS method can be used on a NULL collection.
all other methods applied on a null collection raise the COLLECTION_IS_NULL error.

For example. 
EXISTS(index)
Returns TRUE if the index element exists in the collection, else it returns FALSE.
Use this method to be sure you are doing a valid operation on the collection.
This method does not raise the SUBSCRIPT_OUTSIDE_LIMIT exception if used on an element that does not exists in the collection.
If my_collection.EXISTS(10) Then
   My_collection.DELETE(10) ;
End if ;
  
Caution:
For Varrays, you can suppress only the last element.
If the element does not exists, no exception is raised. 

7. Multi-level Collections 

A collection is a one-dimension table.
You can have multi-dimension tables by creating collection of collection. 
Declare
  TYPE TYP_TAB is table of NUMBER index by PLS_INTEGER ;
  TYPE TYP_TAB_TAB is table of TYP_TAB index by PLS_INTEGER ;
  tab1 TYP_TAB_TAB ;
Begin
  For i IN 1 .. 3 Loop
     For j IN 1 .. 2 Loop
       tab1(i)(j) := i + j ;
       dbms_output.put_line( 'tab1('   || ltrim(to_char(i))
                             || ')('   || ltrim(to_char(j))
                             || ') = ' || tab1(i)(j) ) ;         
     End loop ;
  End loop ;  
End;
/
tab1(1)(1) = 2
tab1(1)(2) = 3
tab1(2)(1) = 3
tab1(2)(2) = 4
tab1(3)(1) = 4
tab1(3)(2) = 5

PL/SQL procedure successfully completed.

Collections of records

Declare
  TYPE TYP_TAB is table of DEPT%ROWTYPE index by PLS_INTEGER ;
  tb_dept TYP_TAB ;
  rec     DEPT%ROWTYPE ;
  Cursor  CDEPT IS Select * From DEPT ;
Begin
  Open CDEPT ;
  Loop
     Fetch CDEPT Into rec ;
     Exit  When CDEPT%NOTFOUND ;
     tb_dept(CDEPT%ROWCOUNT) := rec ;
  End loop ;
  For i IN tb_dept.FIRST .. tb_dept.LAST Loop
    dbms_output.put_line( tb_dept(i).DNAME || ' - ' ||tb_dept(i).LOC ) ;
  End loop ; 
End;
/
ACCOUNTING - NEW YORK
RESEARCH - DALLAS
SALES - CHICAGO
OPERATIONS - BOSTON

PL/SQL procedure successfully completed.

8. Collections and database tables 

Nested tables and Varrays can be stored in a database column of relational or object table.
To manipulate collection from SQL, you have to create the types in the database with the CREATE TYPE statement.
One or several collections can be stored in a database column.
Let’s see an example with a relational table.
You want to make a table that store the invoices and the currents invoice lines of the company.
You need to define the invoice line type as following:

-- type of invoice line --
CREATE TYPE TYP_LIG_ENV AS OBJECT (
  lig_num    Integer,
  lig_code   Varchar2(20),
  lig_Pht    Number(6,2),
  lig_Tva    Number(3,1),
  ligQty     Integer
);

-- nested table of invoice lines --
CREATE TYPE TYP_TAB_LIG_ENV AS TABLE OF TYP_LIG_ENV ;

Then create the invoice table as following: 
CREATE TABLE INVOICE (
  inv_num     Number(9),
  inv_numcli  Number(6),
  inv_date    Date,
  inv_line    TYP_TAB_LIG_ENV ) -- lines collection
  NESTED TABLE inv_line STORE AS inv_line_table ;

You can query the USER_TYPES view to get information on the types created in the database.

-- show all types --
select type_name, typecode, attributes from user_types
TYPE_NAME                      TYPECODE                       ATTRIBUTES
------------------------------ ------------------------------ ----------
TYP_LIG_ENV                    OBJECT                                  5
TYP_TAB_LIG_ENV                COLLECTION                              0
  
You can query the USER_COLL_TYPES view to get information on the collections created in the database.

-- show collections --
select type_name, coll_type, elem_type_owner, elem_type_name from user_coll_types
TYPE_NAME                 COLL_TYPE              ELEM_TYPE_OWNER           ELEM_TYPE_NAME
------------------------- ---------------------- ------------------------- -------
TYP_TAB_LIG_ENV           TABLE                  TEST                      TYP_LIG_ENV 

8.1 Insertion 

Use the INSERT statement with all the constructors needed for the collection 
INSERT INTO INVOICE
VALUES
(
   1
  ,1000
  ,SYSDATE
  , TYP_TAB_LIG_ENV  -- Table of objects constructor
    (
       TYP_LIG_ENV( 1 ,'COD_01', 1000, 5.0, 1 ) -object constructor
    )
)
/

Use the INSERT INTO TABLE statement
INSERT INTO TABLE
  ( SELECT the_collection FROM the_table WHERE ... )

The sub query must return a single collection row.
INSERT INTO TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1)
VALUES( TYP_LIG_ENV( 2 ,'COD_02', 50, 5.0, 10 ) )
1 row created.

You can add more than one element in a collection by using the SELECT statement instead of the VALUES keyword.
INSERT INTO TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1)
SELECT nt.* FROM TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt

8.2 Update

8.2.1 Nested table
Use the UPDATE TABLE statement
 UPDATE TABLE
  ( SELECT the_collection FROM the_table WHERE ... ) alias
SET
  Alias.col_name = ...
WHERE ...
The sub query must return a single collection row.

Update a single row of the collection
UPDATE TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
SET    nt.ligqty = 10
WHERE  nt.lig_num = 1
1 row updated.

Update all the rows of the collection
UPDATE TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
SET    nt.lig_pht = nt.lig_pht * .1
2 rows updated.

It is not possible to update one element of a VARRAY collection with SQL.
You cannot use the TABLE keyword for this purpose (because Varrays are not stored in particular table like Nested tables).
So, a single VARRAY element of a collection must be updated within a PL/SQL block:

-- varray of invoice lines --
CREATE TYPE TYP_VAR_LIG_ENV AS VARRAY(5) OF TYP_LIG_ENV ;

-- table of invoices with varray --
CREATE TABLE INVOICE_V (
  inv_num     Number(9),
  inv_numcli  Number(6),
  inv_date    Date,
  inv_line    TYP_VAR_LIG_ENV ) ;

-- insert a row --
Insert into INVOICE_V
Values
(
  1, 1000, SYSDATE,
  TYP_VAR_LIG_ENV
  (
     TYP_LIG_ENV( 1, 'COD_01', 1000, 5, 1 ),
     TYP_LIG_ENV( 2, 'COD_02',  500, 5, 10 ),
     TYP_LIG_ENV( 3, 'COD_03',   10, 5, 100 )        
  )
) ;

Query the varray collection
Declare
   v_table   TYP_VAR_LIG_ENV ;
   LC$Head   Varchar2(200) ;
   LC$Lig    Varchar2(200) ;
Begin
   LC$Head := 'Num Code       Pht        Tva        Qty' ;
   Select inv_line Into v_table From INVOICE_V Where inv_num = 1 For Update of inv_line ;
   dbms_output.put_line ( LC$Head ) ;
   For i IN v_table.FIRST .. v_table.LAST Loop
     LC$Lig := Rpad(To_char( v_table(i).lig_num ),3) || ' '
         || Rpad(v_table(i).lig_code, 10) || ' '
         || Rpad(v_table(i).lig_pht,10) || ' '
         || Rpad(v_table(i).lig_tva,10) || ' '
         || v_table(i).ligqty ;        
     dbms_output.put_line( LC$Lig ) ;       
   End loop ;
End ;
/
Num Code       Pht        Tva        Qty
1   COD_01     1000       5          1
2   COD_02     500        5          10
3   COD_03     10         5          100
PL/SQL procedure successfully completed..

Update the second line of the varray to change the quantity
Declare
   v_table   TYP_VAR_LIG_ENV ;
Begin
   Select inv_line
   Into   v_table
   From   INVOICE_V
   Where  inv_num = 1
   For Update of inv_line ;
   v_table(2).ligqty := 2 ; -- update the second element
   Update INVOICE_V Set inv_line = v_table Where inv_num = 1 ;
End ;
/
PL/SQL procedure successfully completed.

Display the new varray: 
Query the varray collection --
Declare
   v_table   TYP_VAR_LIG_ENV ;
   LC$Head   Varchar2(200) ;
   LC$Lig    Varchar2(200) ;
Begin
   LC$Head := 'Num Code       Pht        Tva        Qty' ;
   Select inv_line Into v_table From INVOICE_V Where inv_num = 1 For Update of inv_line ;
   dbms_output.put_line ( LC$Head ) ;
   For i IN v_table.FIRST .. v_table.LAST Loop
     LC$Lig := Rpad(To_char( v_table(i).lig_num ),3) || ' '
         || Rpad(v_table(i).lig_code, 10) || ' '
         || Rpad(v_table(i).lig_pht,10) || ' '
         || Rpad(v_table(i).lig_tva,10) || ' '
         || v_table(i).ligqty ;        
     dbms_output.put_line( LC$Lig ) ;       
   End loop ;
End ;
/
Num Code       Pht        Tva        Qty
1   COD_01     1000       5          1
2   COD_02     500        5          2
3   COD_03     10         5          100
PL/SQL procedure successfully completed.

8.3 Delete

8.3.1 Nested table
Use the DELETE FROM TABLE statement
Delete a single collection row
DELETE FROM TABLE
  ( SELECT the_collection FROM the_table WHERE ... ) alias
WHERE alias.col_name = ...

DELETE FROM TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
WHERE nt.lig_num = 2
1 row deleted.

Delete all the collection rows
DELETE FROM TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt 
1 row deleted.

Use of a PL/SQL record to handle the whole structure 
Declare
  TYPE TYP_REC IS RECORD
  (
    inv_num     INVOICE.inv_num%Type,
    inv_numcli  INVOICE.inv_numcli%Type,
    inv_date    INVOICE.inv_date%Type,
    inv_line    INVOICE.inv_line%Type   –- collection line
  );
  rec_inv  TYP_REC ;
  Cursor C_INV IS Select * From INVOICE ;
Begin
   Open C_INV ;
   Loop
     Fetch C_INV into rec_inv ;
     Exit when C_INV%NOTFOUND ;
     For i IN 1 .. rec_inv.inv_line.LAST Loop   –- loop through the collection lines
       dbms_output.put_line( 'Numcli/Date ' || rec_inv.inv_numcli || '/' || rec_inv.inv_date
         || ' Line ' || rec_inv.inv_line(i).lig_num
         || ' code ' || rec_inv.inv_line(i).lig_code || ' Qty '
         || To_char(rec_inv.inv_line(i).ligqty) ) ;
     End loop ;
   End loop ; 
End ;
/
Numcli/Date 1000/11/11/05 Line 1 code COD_01 Qty 1
Numcli/Date 1000/11/11/05 Line 2 code COD_02 Qty 10
PL/SQL procedure successfully completed.

8.3.2 Varray
Varrays are more complicated to handle.
It is not possible to delete a single element in a Varray collection.
To do the job, you need a PL/SQL block and a temporary Varray that keep only the lines that are not deleted.
Declare
   v_table   TYP_VAR_LIG_ENV ;
   v_tmp     v_table%Type := TYP_VAR_LIG_ENV() ;
   ind       pls_integer  := 1 ;
Begin
   -- select the collection --
   Select inv_line
   Into   v_table
   From   INVOICE_V
   Where  inv_num = 1
   For Update of inv_line ;
   -- Extend the temporary varray --
   v_tmp.EXTEND(v_table.LIMIT) ;  
   For i IN v_table.FIRST .. v_table.LAST Loop
      If v_table(i).lig_num <> 2 Then
         v_tmp(ind) := v_table(i) ; ind := ind + 1 ;
      End if ;
   End loop ;
  
   Update INVOICE_V Set inv_line = v_tmp Where inv_num = 1 ;
End ;
/
PL/SQL procedure successfully completed.

Display the new collection:
Declare
   v_table   TYP_VAR_LIG_ENV ;
   LC$Head   Varchar2(200) ;
   LC$Lig    Varchar2(200) ;
Begin
   LC$Head := 'Num Code       Pht        Tva        Qty' ;
   Select inv_line Into v_table From INVOICE_V Where inv_num = 1 For Update of inv_line ;
   dbms_output.put_line ( LC$Head ) ;
   For i IN v_table.FIRST .. v_table.LAST Loop
     LC$Lig := Rpad(To_char( v_table(i).lig_num ),3) || ' '
         || Rpad(v_table(i).lig_code, 10) || ' '
         || Rpad(v_table(i).lig_pht,10) || ' '
         || Rpad(v_table(i).lig_tva,10) || ' '
         || v_table(i).ligqty ;        
     dbms_output.put_line( LC$Lig ) ;       
   End loop ;
End ;
/
Num Code       Pht        Tva        Qty
1   COD_01     1000       5          1
3   COD_03     10         5          100
PL/SQL procedure successfully completed.

The second line of the Varray has been deleted.

Here is a Procedure that do the job with any Varray collection
CREATE OR REPLACE PROCEDURE DEL_ELEM_VARRAY
(
  PC$Table in Varchar2, -- Main table name
  PC$Pk    in Varchar2, -- PK to identify the main table row
  PC$Type  in Varchar2, -- Varray TYPE
  PC$Coll  in Varchar2, -- Varray column name
  PC$Index in Varchar2, -- value of PK
  PC$Col   in Varchar2, -- Varray column
  PC$Value in Varchar2  -- Varray column value to delete
)
IS
  LC$Req Varchar2(2000);
Begin
LC$Req := 'Declare'
||  ' v_table ' || PC$Type || ';'
||  ' v_tmp v_table%Type := ' || PC$Type || '() ;'
||  ' ind  pls_integer := 1 ;'
||  'Begin'
||  ' Select ' || PC$Coll
||  ' Into  v_table'
||  ' From  ' || PC$Table
||  ' Where ' || PC$Pk || '=''' || PC$Index || ''''
||  ' For Update of ' || PC$Coll || ';'
||  ' v_tmp.EXTEND(v_table.LIMIT) ;'
||  ' For i IN v_table.FIRST .. v_table.LAST Loop'
||    ' If v_table(i).' || PC$Col|| '<>''' || PC$Value || ''' Then'
||    '   v_tmp(ind) := v_table(i) ; ind := ind + 1 ;'
||    ' End if ;'
||  ' End loop ;'
||  ' Update ' || PC$Table || ' Set ' || PC$Coll || ' = v_tmp Where ' || PC$Pk || '=''' || PC$Index || ''';'
||  ' End;' ;

  Execute immediate LC$Req ;

End ;
/
  
Let’s delete the third element of the Varray:
Begin
 DEL_ELEM_VARRAY
 (
   'INVOICE_V',
   'inv_num',
   'TYP_VAR_LIG_ENV',
   'inv_line',
   '1',
   'lig_num',
   '3'
 );
End ;
/
  

9. Collection and Bulk Collect

9.1 Bulk Collect
This keyword ask the SQL engine to return all the rows in one or several collections before returning to the PL/SQL engine.

So, there is one single roundtrip for all the rows between SQL and PL/SQL engine.

BULK COLLECT cannot be use on the client-side
(Select)(Fetch)(execute immediate)BULK COLLECT Into collection_name [,collection_name, …] [LIMIT max_lines] ;

LIMIT is used to limit the number of rows returned
set serveroutput on
Declare
  TYPE    TYP_TAB_EMP IS TABLE OF EMP.EMPNO%Type ;
  Temp_no TYP_TAB_EMP ; -- collection of EMP.EMPNO%Type
  Cursor  C_EMP is Select empno From EMP ;
  Pass    Pls_integer := 1 ;
Begin
  Open C_EMP ;
  Loop
    -- Fetch the table 3 by 3 --
 Fetch C_EMP BULK COLLECT into Temp_no LIMIT 3 ;
    Exit When C_EMP%NOTFOUND ;
    For i In Temp_no.first..Temp_no.last Loop
      dbms_output.put_line( 'Pass ' || to_char(Pass) || ' Empno= ' || Temp_no(i) ) ;
    End loop ;
    Pass := Pass + 1 ;
  End Loop ;
End ;
/
Pass 1 Empno= 9999
Pass 1 Empno= 7369
Pass 1 Empno= 7499
Pass 2 Empno= 7521
Pass 2 Empno= 7566
Pass 2 Empno= 7654
Pass 3 Empno= 7698
Pass 3 Empno= 7782
Pass 3 Empno= 7788
Pass 4 Empno= 7839
Pass 4 Empno= 7844
Pass 4 Empno= 7876
Pass 5 Empno= 7900
Pass 5 Empno= 7902
Pass 5 Empno= 7934
PL/SQL procedure successfully completed.

You can use the LIMIT keyword to preserve your rollback segment: 
Declare
  TYPE    TYP_TAB_EMP IS TABLE OF EMP.EMPNO%Type ;
  Temp_no TYP_TAB_EMP ;
  Cursor  C_EMP is Select empno From EMP ;
  max_lig Pls_Integer := 3 ;

Begin
  Open C_EMP ;
  Loop
    Fetch C_EMP BULK COLLECT into Temp_no LIMIT max_lig ;
    Forall i In Temp_no.first..Temp_no.last
        Update EMP set SAL = Round(SAL * 1.1) Where empno = Temp_no(i) ;
    Commit ; -- Commit every 3 rows
    Temp_no.DELETE ;
    Exit When C_EMP%NOTFOUND ;
  End Loop ;
End ;

BULK COLLECT can also be used to retrieve the result of a DML statement that uses the RETURNING INTO clause:
Declare
   TYPE    TYP_TAB_EMPNO IS TABLE OF EMP.EMPNO%Type ;
   TYPE    TYP_TAB_NOM   IS TABLE OF EMP.ENAME%Type ; 
   Temp_no TYP_TAB_EMPNO ;
   Tnoms   TYP_TAB_NOM ; 
Begin
   -- Delete rows and return the result into the collection --
   Delete From EMP where sal > 3000
   RETURNING empno, ename BULK COLLECT INTO Temp_no, Tnoms ;
   For i in Temp_no.first..Temp_no.last Loop
      dbms_output.put_line( 'Fired employee : ' || To_char( Temp_no(i) ) || ' ' || Tnoms(i) ) ;
   End  loop ;
End ;
/
Fired employee : 7839 KING
PL/SQL procedure successfully completed.



No comments:

Post a Comment

Best Blogger TipsGet Flower Effect