Le collezioni di Oracle semplificano sicuramente la programmazione in PL/SQL e sono in grado di velocizzare notevolmente alcuni processi.
Mostrerò come utilizzarle e includerò qualche esempio di impieghi particolari.
Innanzitutto creiamo il tipo
Create or replace type t_varchar2_table as table of varchar2(4000);
Operazioni base :
Create or replace procedure pr_base_test AS
--inizializzo la lista con zero elementi
x_Varchar2_Table t_varchar2_table := t_varchar2_table();
n_Step NUMBER;
BEGIN
--mettiamo 10 elementi nella lista
FOR n_Step IN 1..10 LOOP
x_Varchar2_Table.EXTEND;
x_Varchar2_Table(x_Varchar2_Table.LAST) := 'Row '||n_Step;
END LOOP;
--visualizzo quanti elementi ho nella lista
Dbms_Output.put_Line('Count = '||x_Varchar2_Table.COUNT);
--visualizzo ogni elemento in lista
FOR n_Step IN x_Varchar2_Table.FIRST..x_Varchar2_Table.LAST LOOP
Dbms_Output.put_Line(x_Varchar2_Table(n_Step);
END LOOP;
--controllo se un elemento è presente nella lista
IF 'Row 7' MEMBER OF x_Varchar2_Table THEN
Dbms_output.put_Line('OK');
END IF;
--Controllo se la lista è univoca, ovvero non ho duplicati
IF x_Varchar2_Table IS A SET THEN
Dbms_output.put_Line('IS A SET);
END IF;
--svuoto la lista
x_Varchar2_Table.DELETE;
END;
/
A partire da Oracle 10g sono presenti una serie di operatori multiset che permettono operazioni sulle liste. Eccone una lista con accanto l’equivalente operatore SQL :
| MULTISET UNION DISTINCT | UNION |
| MULTISET UNION | UNION ALL |
| MULTISET UNION ALL | UNION ALL |
| MULTISET INTERSECT | INTERSECT |
| MULTISET INTERSECT DISTINCT | N/A |
| MULTISET EXCEPT | MINUS |
| MULTISET EXCEPT DISTINCT | N/A |
Per riempire una lista con dei valori provenienti da una query è semplicissimo :
declare
x_table_list t_varchar2_table := t_varchar2_table();
begin
select table_name BULK COLLECT INTO x_table_list
from user_tables
where table_name like 'A%';
--la lista ora contiene l'elenco delle tabelle che iniziano con A
end;
Naturalmente non bisogna abusare dell’uso delle liste in quanto consumano parecchia memoria. Gli utilizzi principali che ho riscontrato sono :
- creo una lista di dati che poi andrò ad utilizzare parecchie volte : è inutile fare la join con la medesima tabella (magari di grandi dimensioni) molte volte quando è possibile caricare in memoria la lista di ID di cui necessito e unitilizzarla n volte.
- sono molto utili per trasferire dati da una procedura all’altra : ad esempio, se ho un package che mi gestisce gli utenti e all’interno di questo una procedure che mi restituisce la lista degli utenti è inutile implementare tale logica in un altro package. Quindi il package che avrà bisogno dell’elenco degli utenti invocherà tale procedura e ne utilizzerà la lista ottenuta.
- Ottimizzazione di insert/update/delete di tante righe grazie all’operatore FORALL
Ecco alcuni esempi :
Esempio 1
declare
x_table_list t_varchar2_table := t_varchar2_table();
n_count Number;
begin
select table_name bulk collect into x_table_list
from user_tables
where table_name like 'A%';
--use the list as a table
select count(*) into n_count
from user_tab_columns a, TABLE(x_table_list) b
where a.table_name = b.column_value;
dbms_output.put_line(n_count);
end;
Esempio 2
create table temp
as select * from user_tables
where 1 = 2;
declare
type t_user_tables is table of user_tables%ROWTYPE;
x_user_tables t_user_tables := t_user_tables();
n Number;
begin
select * bulk collect into x_user_tables
from user_tables;
FORALL n IN x_user_tables.FIRST..x_user_tables.LAST
Insert into temp values x_user_tables(n);
COMMIT;
End;
E se invece avessi bisogno di utilizzare una collezione all’interno di una query dinamica ?? In questo caso è obbligatorio utilizzare collezioni basate su tipi creati all’interno dello schema tramite il comando Create Type. Nell’esempio utlizzerò il tipo t_varchar2_table creato all’inizio del post.
create or replace PACKAGE pk_Test AS
PROCEDURE pr_Load;
PROCEDURE pr_ExecuteQuery;
FUNCTION fn_List RETURN T_Varchar2_Table;
END pk_Test;
create or replace PACKAGE BODY pk_Test AS
kx_List T_Varchar2_Table := T_Varchar2_Table();
PROCEDURE pr_Load IS
BEGIN
SELECT Table_Name BULK COLLECT INTO kx_List
FROM User_Tables
WHERE Table_Name LIKE ‘A%’;
END pr_Test;
PROCEDURE pr_ExecuteQuery IS
v_Sql VARCHAR2(1000);
n_Count NUMBER;
BEGIN
v_Sql := ‘SELECT COUNT(*)
FROM User_Tables
WHERE Table_Name IN (
SELECT Column_Value FROM
TABLE ( pk_Test.fn_List )
)’;
EXECUTE IMMEDIATE v_Sql INTO n_Count;
Dbms_Output.put_line(n_Count);
END pr_ExecuteQuery;
FUNCTION fn_List RETURN T_Varchar2_Table IS
BEGIN
Return kx_List;
END fn_List;
END pk_Test;
Nella procedura pr_Load non faccio altro che caricare in una collezione tutte le tabelle che iniziano con A.
Nella procedura pr_ExecuteQuery eseguo una query dinamica tramite il comando Execute Immediate utilizzando la lista appena caricata che mi viene restituita dalla funzione fn_List.
