Si possono fare due tipi di UDT, come oggetto o come lista. Non mi è mai capitato di usare i tipi oggetto, perchè di solito il domain model è sempre definito nell'applicazione, ma sono comunque molto potenti, sebbene abbiano dei vincoli e delle restrizioni.
Vediamo invece come usare i tipi lista per passare liste di dati in una stored procedure, probabilmente il caso più comune che può capitare ad uno sviluppatore dotnet che si trovi a lavorare con Oracle.
Gli step da seguire sono pochi e abbastanza semplici:
1. Creiamo uno User Defined Type (UDT) in Oracle con una istruzione sql:
CREATE OR REPLACE TYPE MYINTTABLE AS
TABLE OF NUMBER;
In questo modo creiamo un UDT chiamato MYINTTABLE, di tipo TABLE, che può contenere dei NUMBER.
2. Creiamo ora una stored procedure di esempio:
CREATE OR REPLACE PROCEDURE TEST_UDT (PARAM MYINTTABLE,POUT SYS_REFCURSOR)
AS
BEGIN
OPEN POUT FOR
SELECT * FROM TABLE1 WHERE ID IN (SELECT * FROM TABLE(CAST(PARAM AS MYINTTABLE)));
END;
Come vedete la procedura accetta un parametro in input di tipo MYINTTABLE (il nostro UDT) e un parametro in output di tipo REFCURSOR.
Il codice PL/SQL non fa altro che riempire il cursore con una select star su una tabella di esempio, in questo caso TABLE1, con un campo ID sul quale vado a filtrare le righe prendendo i valori dalla variabile UDT passata in ingresso.
Immaginando di voler prendere le righe di TABLE1 con ID 1, 10, e 20 scrivendo un'istruzione sql "pura", dovrei scrivere
SELECT * FROM TABLE1 WHERE ID IN (1,10,20)
Se però voglio avere la possibilità di variare la lista di ID che filtrerà i miei risultati, posso sostituire la lista statica di numeri con
SELECT * FROM TABLE(CAST(PARAM AS MYINTTABLE))
che non è altro che una subquery che ripropone l'insieme di numeri sotto forma di tabella, proprio come una normale subquery.
3. Vediamo ora lato codice cosa ci serve. Intanto abbiamo bisogno di una classe .NET che mappi il mio UDT oracle, quindi creo una nuova classe c# ( o vb.net, in base alle preferenze), come segue:
using Oracle.DataAccess.Types;[OracleCustomTypeMappingAttribute("MYINTTABLE")]
public class UDTMappingOracle_MYINTTABLE_Type:IOracleArrayTypeFactory { public System.Array CreateArray(int numElems) { return new int[numElems - 1]; } public System.Array CreateStatusArray(int numElems) { return null; } }
L'attributo OracleCustomTypeMappingAttribute indica al provider ODP.NET qual'è la classe da utilizzare per instanziare i giusti oggetti da inviare al client oracle, mentre l'implementazione dell'interfaccia IOracleArrayTypeFactory mette a disposizione i metodi per creare appunto la nostra lista di id da passare alla procedura.
4. E' giunto il momento di richiamare la nostra procedura, con un qualsiasi codice in grado di aprire una connessione ed eseguire un comando SQL passando dei parametri, ad esempio:
using Oracle.DataAccess.Client; namespace OracleUDT { class Program { static void Main(string[] args) { try { //creo la nostra lista di id var listaId = new System.Collections.Generic.List<int>() { 1, 10, 20 }; //creo un adapter per praticità using (OracleDataAdapter adapter = new OracleDataAdapter("TEST_UDT", "User Id=TEST;Password=TEST;Data Source=TESTDB;")) { //indico al provider che voglio eseguire una stored procedure invece di una istruzione plain sql adapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure; //creo il parametro di input OracleParameter inParam = new OracleParameter("PARAM", OracleDbType.Array, System.Data.ParameterDirection.Input); //imposto il valore convertendo in array la nostra lista di id inParam.Value = listaId.ToArray(); //SPECIFICO IL NOME DELL'UDT CHE INTENDO USARE inParam.UdtTypeName = "MYINTTABLE"; //creo il parametro di output, di tipo cursore OracleParameter outParam = new OracleParameter("POUT", OracleDbType.RefCursor, System.Data.ParameterDirection.Output); //aggiungo i due parametri al command adapter.SelectCommand.Parameters.Add(inParam); adapter.SelectCommand.Parameters.Add(outParam); //riempio un dataset con i risultati prodotti dalla procedura var results = new System.Data.DataSet(); adapter.Fill(results); System.Diagnostics.Debug.WriteLine("numero di record trovati: " + results.Tables[0].Rows.Count); } } catch (System.Exception ex) { System.Diagnostics.Debug.WriteLine(ex.Message); } } } }
Come vedete, quando andiamo ad istanziare il parametro di input, indichiamo che è di tipo Array, che il suo valore è uguale alla nostra lista convertita ad array, e che l'UdtTypeName che vogliamo utilizzare è MYINTTABLE.
Se eseguiamo il codice otterremo un dataset di record i cui ID saranno uguali a 1, 10 o 20.
Uno dei vantaggi nell'usare un UDT di tipo tabella, come visto nella procedura, è quello di poterlo trattare come una tabella, con tutti i vantaggi che ne conseguono, seppur in maniera limitata, ovvero la possibilità di fare union, intersect, minus, e quant'altro, permettendoci di integrare le nostre query con liste di dati, invece che con singoli valori.
