 |
|
EnterpriseDB: Composite Type
Oracle Tips by Burleson Consulting
|
A
composite type is a User Defined Type (UDT). There are times when
you want to deal with several scalar types in a structured group.
Corresponding data types in other languages are a struct in C or a
record type in PL/SQL. In SQL, a composite type can serve as the
basis for a table or for a column in a table.
A
composite type is a mixture of any scalar types that you need.
We
created the numeric_table in the numbers section above. We can
modify that CREATE TABLE syntax and create a composite type:
CREATE TYPE number_type AS (
small_num NUMBER(5),
big_num NUMBER(25),
really_big_num NUMBER,
number_float NUMBER(15,5),
int_field INTEGER,
real_float REAL,
double_float
DOUBLE PRECISION,
oid_field OID );
Notice
I changed the object's name. We already have a table named
number_table so I had to name my type something else.
Once
you've created this type, you can use it as a column data type or
even as a parameter to a procedure or function. Types can
definitely help with simplifying your coding tasks.
If I
continue with the format of my examples, I can create a table:
CREATE TABLE big_numbers (
number_field number_type );
INSERT INTO big_numbers (number_field.small_num)
VALUES (123);
Notice
I chose to only insert one data element of my number_type column. I
could have inserted the entire row by using the ROW()
function.
INSERT INTO big_numbers (number_field)
VALUES
( ROW(12345, 1234567890, 12345678901234567890, 123456.1234, 1234,
109283.2364, 1283723266252533.8372326362636262632, NULL));
Now I
will select some data back out:
SELECT (number_field).small_num, (number_field).double_float
FROM big_numbers;
Here
are the results from the above commands:
edb=# CREATE TYPE number_type AS (
edb(# small_num NUMBER(5),
edb(# big_num NUMBER(25),
edb(# really_big_num NUMBER,
edb(# number_float NUMBER(15,5),
edb(# int_field INTEGER,
edb(# real_float REAL,
edb(# double_float DOUBLE PRECISION,
edb(# oid_field OID );
CREATE TYPE
edb=# CREATE TABLE big_numbers (
edb(# number_field number_type );
CREATE TABLE
edb=#
edb=# INSERT INTO big_numbers (number_field.small_num)
edb-# VALUES (123);
INSERT 0 1
edb=# INSERT INTO big_numbers (number_field)
edb-# VALUES
edb-# ( ROW(12345, 1234567890, 12345678901234567890, 123456.1234,
1234, 109283.2364,
1283723266252533.8372326362636262632, NULL));
INSERT 0 1
edb=# SELECT (number_field).small_num, (number_field).double_float
edb-# FROM big_numbers;
small_num | double_float
-----------+-----------------------
123 |
12345 | 1.28372326625253e+015
(2 rows)
edb=#
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
