Oracle DataTypes - Parte 2

Datamaze
13.01.25 03:42 PM Comment(s)
Nel nostro precedente articolo “Back to Basics: i tipi di dati in Oracle”, abbiamo discusso e descritto gli Oracle Built-in Data Types, ossia quelli definiti nativi nel database, raggruppati in queste categorie: Character Data Type, Numeric Data Type, LONG Data Type, Datatetime e Interval Data Type, RAW e LONG RAW Data Type, Large Object (LOB) Data Type ed Extended Data Type.              
In questo articolo completeremo la panoramica sulle restanti categorie di dati: 
  • ANSI, DB2 and SQL/DS Data Types 
  • User-Defined Types 
  • Oracle-Supplied Types 

ANSI, DB2 e SQL/DS Data Types

Le istruzioni SQL che creano tabelle e cluster possono anche utilizzare data types ANSI e data types  provenienti da prodotti IBM SQL/DS e DB2. Oracle riconosce il nome del tipo di dati ANSI o IBM che differisce dal nome del tipo di dati di Oracle Database, converte il tipo di dati nel tipo di dati Oracle equivalente, registra il tipo di dati Oracle come nome del tipo di dati della colonna e memorizza i dati della colonna nel tipo di dati Oracle in base alle conversioni mostrate nelle tabelle che seguono.


DB2 Data Types
ANSI Data Types convertiti in Oracle Data Types
Note: 
  1. I tipi NUMERIC e DECIMAL possono specificare solo numeri a virgola fissa. Per tali tipi di dati, la scala (s) è impostata di default su 0.
  2. Il tipo di dati FLOAT è un numero a virgola mobile con una precisione binaria b. La precisione predefinita per questo tipo di dati è 126 binari o 38 decimali.
  3. Il tipo di dati DOUBLE PRECISION è un numero a virgola mobile con una precisione binaria di 126.
  4. Il tipo di dati REAL è un numero a virgola mobile con una precisione binaria di 63 o 18 decimali. 

Non è possibile definire colonne con i seguenti tipi di dati SQL/DS e DB2, perché non hanno un tipo di dati Oracle corrispondente: GRAPHIC, LONG VARGRAPHIC, VARGRAPHIC e TIME. I dati di tipo TIME possono comunque essere espressi come dati datetime Oracle.

ANSI Data Types
SQL/DS e DB2 Data Types convertiti in Oracle Data Types
Note: 
  1. Il tipo di dati DECIMAL può specificare solo numeri a virgola fissa. Per questo tipo di dati, se è impostato di default su 0.
  2. Il tipo di dati FLOAT è un numero a virgola mobile con una precisione binaria b. La precisione di default per questo tipo di dati è 126 binari oppure 38 decimali.

User-Defined Types

I tipi di dati definiti dall'utente utilizzano gli Oracle built-in data types e altri tipi di dati definiti dall'utente come blocchi di costruzione di tipi di oggetti che modellano la struttura e il comportamento dei dati nelle applicazioni. Le sezioni che seguono descrivono le varie categorie di tipi definiti dall'utente. 

Object Types 

I tipi di oggetti sono astrazioni delle entità del mondo reale, come gli ordini di acquisto, che i programmi applicativi gestiscono. Un tipo di oggetto è un oggetto schema con tre tipi di componenti:  

  • Un nome, che identifica il tipo di oggetto in modo univoco all'interno di quello schema.  

  • Attributi, che sono tipi incorporati o altri tipi definiti dall'utente. Gli attributi modellano la struttura dell'entità del mondo reale.  

  • Metodi, che sono funzioni o procedure scritte in PL/SQL e memorizzate nel database, o scritte in un linguaggio come C o Java e memorizzate esternamente. I metodi implementano le operazioni che l'applicazione può eseguire sull'entità del mondo reale. 

REF Data Types 

Un object identifier (rappresentato dalla parola chiave OID) identifica in modo univoco un oggetto e consente di fare riferimento all'oggetto da altri oggetti o da tabelle relazionali. Una categoria di tipi di dati denominata REF rappresenta tali riferimenti. Un tipo di dati REF è un contenitore per un object identifier. I valori REF sono puntatori a oggetti. Quando un valore REF punta a un oggetto inesistente, il REF è detto "in sospeso". Un REF sospeso è diverso da un REF nullo. Per determinare se un REF è sospeso o meno, utilizzare la condizione IS [NOT] DANGLING.  

Ad esempio, data la vista oggetto oc_orders nello schema di esempio oe, la colonna customer_ref è di tipo REF per il tipo customer_typ, che ha un attributo cust_email: 

SELECT o.customer_ref.cust_email 

  FROM oc_orders o  

  WHERE o.customer_ref IS NOT DANGLING; 

 

Varray  

Un array è un set ordinato di elementi di dati. Tutti gli elementi di un array sono dello stesso tipo di dati. Ogni elemento ha un indice, che è un numero corrispondente alla posizione dell'elemento nell'array. Il numero di elementi in un array è la dimensione dell'array. Gli array Oracle sono di dimensione variabile, motivo per cui sono chiamati varray. Devi specificare una dimensione massima quando dichiari il varray.  

Quando dichiari un varray, non alloca spazio. Definisce un tipo, che puoi usare come:  

  • Il tipo di dati di una colonna di una tabella relazionale  

  • Un attributo di tipo oggetto  

  • Una variabile PL/SQL, un parametro o una funzione di tipo di codice di ritorno  

Oracle normalmente memorizza un oggetto array in line (come parte dei dati di riga) o out of line (in un LOB), a seconda della sua dimensione. Tuttavia, se specifichi caratteristiche di archiviazione separate per un varray, Oracle lo memorizza fuori linea, indipendentemente dalla sua dimensione. 

Esempio di Varray. 

Creazione: 

-- To Create a VARRAY type here 
TYPE SampleVARRAY IS VARRAY (3) of VARCHAR (10); 
 
-- To Declare a variable which is of VARRAY type  
DECLARE  
  myVarray SampleVARRAY := SampleVARRAY('Mario', 'Paolo', 'Enzo'
); 

 

Accesso a un singolo elemento: 

DECLARE  
  myVarray SampleVARRAY := SampleVARRAY('Mario', 'Paolo', 'Enzo'); 
 
Begin 
  -- Accessing elements by index 
  dbms_output.PUT_LINE('This is First element: ' || myVarray(2)); 

 

che genera l’output: ‘Paolo’ 

 

Nested Tables 

Un tipo di tabella nidificata modella un set non ordinato di elementi. Gli elementi possono essere tipi incorporati o tipi definiti dall'utente. È possibile visualizzare una tabella nidificata come una tabella a colonna singola o, se la tabella nidificata è un tipo di oggetto, come una tabella a più colonne, con una colonna per ogni attributo del tipo di oggetto.  

Una definizione di nested table non alloca spazio. Definisce un tipo, che è possibile utilizzare per dichiarare:  

  • Il tipo di dati di una colonna di una tabella relazionale  

  • Un attributo di tipo oggetto  

  • Una variabile PL/SQL, un parametro o una funzione di tipo di codice di ritorno  

Quando una tabella nidificata appare come il tipo di una colonna in una tabella relazionale o come un attributo del tipo di oggetto sottostante di una tabella di oggetti, Oracle memorizza tutti i dati della tabella nidificata in una singola tabella, che associa alla tabella relazionale o di oggetti di chiusura. 

Ad esempio, qui vediamo la creazione di una nested table multi-column dove il customers può avere più indirizzi. 

CREATE TYPE address_t AS OBJECT ( 

  street  VARCHAR2(30), 

  city  VARCHAR2(20), 

  state  CHAR(2), 

  zip  CHAR(5) ); 

CREATE TYPE address_tab IS TABLE OF address_t; 

CREATE TABLE customers ( 

  custid  NUMBER, 

  address address_tab ) 

NESTED TABLE address STORE AS customer_addresses; 

 

INSERT INTO customers VALUES (1, 

  address_tab( 

  address_t('101 First', 'Redwood Shores', 'CA', '94065'), 

  address_t('123 Maple', 'Mill Valley',  'CA', '90952') 

  )  ); 

Unnesting the subtable 

SQL> select c.custid, u.* 

    from customers c, table (c.address) u; 

 

  CUSTID STREET  CITY  ST ZIP 

---------- ------------------------------ -------------------- -- ----- 

  1 101 First  Redwood Shores  CA 94065 

  1 123 Maple  Mill Valley  CA 90952 


Oracle-Supplied Types

Oracle fornisce interfacce basate su SQL per definire nuovi types quando quelli integrati o supportati da ANSI non sono sufficienti. Il comportamento per questi types può essere implementato in C/C++, Java o PL/SQL. Oracle Database fornisce automaticamente i servizi infrastrutturali di basso livello necessari per input-output, accesso eterogeneo lato client per nuovi data types e ottimizzazioni per i trasferimenti di dati tra l'applicazione e il database.               

Queste interfacce possono essere utilizzate per creare types definiti dall'utente (o oggetto) e sono anche utilizzate da Oracle per creare alcuni data types di uso comune. Diversi di questi tipi di dati sono forniti con il server e servono sia ampie aree applicative orizzontali (ad esempio, i types Any) sia specifiche verticali (ad esempio, i types Spatial). I tipi forniti da Oracle sono descritti nelle seguenti sezioni: 
  • Types Any 
  • Types XML 
  • Types Spatial 

Type ANY

I tipi Any forniscono una modellazione altamente flessibile di parametri di procedura e colonne di tabella in cui il tipo effettivo non è noto. Questi tipi di dati consentono di incapsulare e accedere dinamicamente a descrizioni di tipo, istanze di dati e set di istanze di dati di qualsiasi altro tipo SQL. Questi tipi hanno interfacce OCI e PL/SQL per la costruzione e l'accesso.               

ANYTYPE - Questo tipo può contenere una descrizione di tipo di qualsiasi tipo SQL denominato o tipo transitorio senza nome. 

ANYDATA - Questo tipo contiene un'istanza di un dato tipo, con dati, più una descrizione del tipo. ANYDATA può essere utilizzato come tipo di dati di colonna di tabella e consente di archiviare valori eterogenei in una singola colonna. I valori possono essere di tipi SQL incorporati e di tipi definiti dall'utente. 

ANYDATASET - Questo tipo contiene una descrizione di un dato tipo più un set di istanze di dati di quel tipo. ANYDATASET può essere utilizzato come tipo di dati di parametro di procedura in cui è necessaria tale flessibilità. I valori delle istanze di dati possono essere di tipi SQL incorporati o di tipi definiti dall'utente.

XML Types

Extensible Markup Language (XML) è un formato standard sviluppato dal World Wide Web Consortium (W3C) per rappresentare dati strutturati e non strutturati sul World Wide Web. Gli Universal resource identifiers (URI) identificano risorse come pagine Web ovunque sul Web. Oracle fornisce tipi per gestire dati XML e URI, nonché una classe di URI denominati tipi DBURIRef per accedere ai dati archiviati all'interno del database stesso. Fornisce inoltre un set di types per archiviare e accedere agli URI sia esterni che interni dall'interno del database.               

XMLType - Questo tipo fornito da Oracle può essere utilizzato per archiviare e interrogare dati XML nel database. XMLType ha funzioni membro che puoi utilizzare per accedere, estrarre e interrogare i dati XML utilizzando espressioni XPath. XPath è un altro standard sviluppato dal comitato W3C per attraversare documenti XML. Le funzioni XMLType di Oracle supportano molte espressioni XPath W3C. Oracle fornisce anche un set di funzioni SQL e pacchetti PL/SQL per creare valori XMLType da dati relazionali o relazionali a oggetti esistenti. 

Le query e DML sulle colonne XMLType funzionano allo stesso modo indipendentemente dal meccanismo di archiviazione. 

URI Data Types - Oracle fornisce una famiglia di types URI ossia URIType, DBURIType, XDBURIType e HTTPURIType  che sono correlati da una gerarchia di ereditarietà. URIType è un tipo di oggetto e gli altri sono sottotipi di URIType. Poiché URIType è il supertipo, puoi creare colonne di questo tipo e archiviare istanze di tipo DBURIType o HTTPURIType in questa colonna.

XML Data Types

Types Spatial

Oracle Spatial and Graph è progettato per rendere la gestione dei dati spaziali più semplice e naturale per gli utenti di applicazioni location-enable, applicazioni di sistemi informativi geografici (GIS) e applicazioni di geoimaging. Dopo che i dati spaziali sono archiviati in un database Oracle, puoi facilmente manipolarli, recuperarli e correlarli a tutti gli altri dati archiviati nel database. I seguenti tipi di dati sono disponibili solo se hai installato Oracle Spatial and Graph.


Types Spatial
Esempio. 

Qui creaimo tre tabelle specifiche per città, regione, autostrada nel quale viene incluso il tipo di oggetto SDO_GEOMETRY. Con Spatial e Graph, la descrizione geometrica di un oggetto spaziale è memorizzata in una singola riga, in una singola colonna di tipo oggetto SDO_GEOMETRY in una tabella definita dall'utente. Ogni tabella che ha una colonna di tipo SDO_GEOMETRY deve avere un'altra colonna, o un set di colonne, che definisce una chiave primaria univoca per quella tabella. Le tabelle di questo tipo sono talvolta denominate tabelle spaziali o tabelle di geometria spaziale. 

CREATE TABLE GEOD_CITIES( 
  LOCATION     SDO_GEOMETRY, 
  CITY         VARCHAR2(42), 
  STATE_ABRV   VARCHAR2(2), 
  POP90        NUMBER, 
  RANK90       NUMBER); 

CREATE TABLE GEOD_COUNTIES( 
  COUNTY_NAME   VARCHAR2(40), 
  STATE_ABRV    VARCHAR2(2), 
  GEOM          SDO_GEOMETRY); 

CREATE TABLE GEOD_INTERSTATES( 
  HIGHWAY   VARCHAR2(35), 
  GEOM      SDO_GEOMETRY); 

Con questa select cerchiamo tutte le città distanti non oltre 15 miglia dall’interstate I170. 

SELECT /*+ ORDERED */ c.city 
FROM geod_interstates i, geod_cities c 
WHERE i.highway = 'I170' 
   AND sdo_within_distance ( 
        c.location, i.geom, 
       'distance=15 unit="mile')" = 'TRUE'; 

di Anna Bruno, pubblicato il 13 gennaio 2025