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