Database Design
Varrays
/ 0
- Details
- Parent Category: Articles
- Created on Thursday, 19 August 2010 21:17
- Last Updated on Thursday, 12 February 2015 15:34
- Published on Thursday, 19 August 2010 21:17
- Written by Guy Lambregts
- Hits: 3317
Working with Varrays
The data types we are familiar with are
1. number
2. varchar2
3. date
4. timestamp
5. clobs
6. blobs
7.bfiles
... and some others
We can in rare cases use an ARRAY as a datatype in the Oracle Database. Associative Arrays are used in the PL/SQL language.You may want to read the PL/SQL guide. However 2 kind of ARRAYS we can use as a datatype in the table definition and we can use SQL to query the information.
1. NESTED TABELS
2. Variable arrays or VARRAYS
You may want to read the application developpers guide for a complete description and difference bewteen the above two ARRAYS
Here some gym with a 1 dimension VARRAY
STEP 1 : We create a TYPE : JOB_TYPE
SQL> create type JOB_TYPE as VARRAY(7) of NUMBER(1);
2 /
Type created.
STEP 2 : We create a table which uses that TYPE
SQL> create table T_JOBS (job_id number,job_name varchar2(30),job_days JOB_TYPE);
Table created.
STEP 3 : We insert some records in the table and afterwards we use "ordinary" SQL to query the data.
SQL> insert into T_JOBS values (1,'BACKUP_HOST1_DB1',job_type(1,2,3));
1 row created.
SQL> insert into T_JOBS values (2,'BACKUP_HOST1_DB2',job_type(0));
1 row created.
SQL> commit;
SQL> select * from T_JOBS;
JOB_ID JOB_NAME
---------- ------------------------------
JOB_DAYS
--------------------------------------------------------------------------------
1 BACKUP_HOST1_DB1
JOB_TYPE(1, 2, 3)
2 BACKUP_HOST1_DB2
JOB_TYPE(0)
STEP 4 : We adjust our SQL statement to unnest the array
SQL> select a.job_id,a.job_name,
2 case b.column_value
3 when 1 then 'Monday'
4 when 2 then 'Tuesday'
5 when 3 then 'Wednesday'
6 when 4 then 'Thursday'
7 when 5 then 'Friday'
8 when 6 then 'Saturday'
9 when 0 then 'Sunday'
10 end AS DAY
11 from
12 t_jobs a, TABLE (a.job_days) b;
JOB_ID JOB_NAME DAY
---------- ------------------------------
1 BACKUP_HOST1_DB1 Monday
1 BACKUP_HOST1_DB1 Tuesday
1 BACKUP_HOST1_DB1 Wednesday
2 BACKUP_HOST1_DB2 Sunday