| General Information |
| Object types and other user-defined types allow for the definition
of data types that model the structure and behavior of the data in an application. |
| Related Data Dictionary Objects |
collection$
source$
type$
| dba_coll_types |
all_coll_types |
user_coll_types |
| dba_dependencies |
all_dependencies |
user_dependencies |
| dba_source |
all_source |
user_source |
| dba_types |
all_types |
user_types |
| dba_varrays |
all_varrays |
user_varrays |
|
| System Privileges Related To Types |
CREATE TYPE
CREATE ANY TYPE
DROP ANY TYPE |
| |
| Create Type Header |
Single Column Object Declaration |
CREATE OR REPLACE TYPE <type_name>
AUTHID <CURRENT USER | DEFINER>
AS OBJECT (
<attribute> <attribute data_type>,
<inheritance clause>,
<subprogram spec>)
<FINAL | NOT FINAL> <INSTANTIABLE | NOT INSTANTIABLE>;
/ |
CREATE OR REPLACE TYPE
ssn_t AS OBJECT (
ssn_type CHAR(11));
/
-- examine type definition
SELECT *
FROM user_source
WHERE name = 'SSN_T';
CREATE TABLE ssn (
per_id NUMBER(10),
per_ssn ssn_t);
desc SSN
-- examine table columns
SELECT column_name, data_type
FROM user_tab_columns
WHERE table_name = 'SSN';
INSERT INTO ssn VALUES (1, '123-45-6789');
INSERT INTO ssn VALUES (1, ssn_t('123-45-6789'));
SELECT * FROM ssn;
UPDATE ssn
SET per_ssn = ssn_t('111-22-3333');
SELECT * FROM ssn;
INSERT INTO ssn VALUES (1, ssn_t('999-88-7777'));
SELECT * FROM ssn;
UPDATE ssn
SET per_ssn = ssn_t('456-56-0841')
WHERE per_ssn = ssn_t('111-22-3333');
SELECT * FROM ssn; |
Multicolumn Object Declaration |
CREATE OR REPLACE TYPE <type_name> AS OBJECT (
<column_name> <data_type>,
...,
<column_name> <data_type>);
/ |
CREATE OR REPLACE TYPE
phone_t AS OBJECT (
a_code CHAR(3),
p_number CHAR(8));
/
-- examine type definition
SELECT *
FROM user_source
WHERE name = 'PHONE_T';
CREATE TABLE phone (
per_id NUMBER(10),
per_phone phone_t);
set describe depth all
desc phone
-- examine table columns
col data_type format a15
col data_type_owner format a15
SELECT column_name, data_type, data_type_mod, data_type_owner
FROM user_tab_columns
WHERE table_name = 'PHONE';
INSERT INTO phone
(per_id, per_phone)
VALUES
(1, phone_t('206', '555-1212'));
INSERT INTO phone
(per_id, per_phone)
VALUES
(2, phone_t('212', '123-4567'));
SELECT * FROM phone;
SELECT per_id FROM phone;
SELECT per_id, per_phone
FROM phone;
-- selective select
SELECT *
FROM phone p
WHERE p.per_phone.a_code = '206';
SELECT p.per_phone.p_number
FROM phone p
WHERE p.per_phone.a_code = '206';
-- selective update
UPDATE phone p
SET p.per_id = 9
WHERE p.per_id = 1;
SELECT * FROM phone;
UPDATE phone p
SET p.per_phone.a_code = '303'
WHERE p.per_phone.a_code = '206';
SELECT * FROM phone;
-- selective delete
DELETE FROM phone p
WHERE p.per_id = 2;
SELECT * FROM phone;
DELETE FROM phone p
WHERE p.per_phone.a_code = '206';
SELECT * FROM phone; |
| |
| Create Subtype |
Subtype Creation |
CREATE OR REPLACE TYPE <type_name>
AUTHID <CURRENT USER | DEFINER>
UNDER <supertype_name>,
<attribute> <data_type>,
<inheritance clause> <subprogram spec>, <pragma clause>)
<FINAL | NOT FINAL> <INSTANTIABLE | NOT INSTANTIABLE>;
/ |
-- create object supertype
CREATE OR REPLACE TYPE person_typ AS OBJECT (
ssn NUMBER(9), name VARCHAR2(30), address VARCHAR2(100))
NOT FINAL;
/
-- derive collection type from supertype
CREATE OR REPLACE TYPE person_tab_typ AS TABLE OF
person_typ;
/
-- derive object subtype from object supertype
CREATE OR REPLACE TYPE student_typ UNDER
person_typ (
deptid NUMBER, major VARCHAR2(30))
NOT FINAL;
/
-- derive collection type from subtype
CREATE OR REPLACE TYPE student_tab_typ
AS TABLE OF student_typ;
/
-- create nested table from the two collection
CREATE TABLE test (
regular_field DATE,
person_nested_tab person_tab_typ,
student_nested_tab student_tab_typ)
NESTED TABLE person_nested_tab STORE AS per_tab
NESTED TABLE student_nested_tab STORE AS stu_tab;
desc test
desc per_tab
desc stu_tab |
| |
| Create Type Body |
Create A Collection From An Object |
|
CREATE OR REPLACE TYPE people_typ AS OBJECT (
last_name VARCHAR2(25),
department_id NUMBER(4),
salary NUMBER(8,2));
/
CREATE OR REPLACE TYPE people_tab_typ AS TABLE OF
people_typ;
/
CREATE TABLE hr_info (
department_id NUMBER(4),
location_id NUMBER(4),
manager_id NUMBER(6),
people people_tab_typ)
NESTED TABLE people STORE AS people_stor_tab;
DESC hr_info
INSERT INTO hr_info
VALUES
(100, 1800, 999, people_tab_typ());
INSERT INTO hr_info
VALUES
(200, 2000, 881, people_tab_typ());
SELECT * FROM hr_info; |
| Create Type As Variable |
|
| |
| Create Type As Variable In Package Header |
|
| |
| |
| Row Data Type Declaration |
|
| |
| Array Data Type Declaration |
|
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY; |
| |
| Create Type Header As Database Object |
Type Collection Of A User Defined Object Data Type |
CREATE OR REPLACE TYPE <type_name> AS TABLE OF <user_defined_data_type>;
/ |
CREATE OR REPLACE TYPE
phones_tab_t AS TABLE OF
phone_t;
/
SELECT object_name, object_type
FROM user_objects;
SELECT type_name, typecode
FROM user_types;
SELECT type_name, coll_type
FROM user_coll_types; |
Heap Table Of A User Defined Data Object Type |
CREATE TABLE <table_name>
column_name <user_defined_data_type); |
CREATE TABLE test (
person_id NUMBER(10),
per_phones phone_t);
desc test
COL data_type FORMAT a40
SELECT column_name, data_type
FROM user_tab_columns
WHERE table_name = 'TEST'; |
Heap Table Of With A User Defined Data Object Type As A Nested Table |
CREATE TABLE <table_name> (
column_name <user_defined_data_type>)
NESTED TABLE <column_name> STORE AS <name_of_nested_table>; |
CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(25),
middle_inits VARCHAR2(4),
last_name VARCHAR2(25),
name_suffix VARCHAR2(5),
title_1 VARCHAR2(5),
title_2 VARCHAR2(5),
dob DATE,
ssn VARCHAR2(11),
address_id NUMBER(10),
person_phones phones_tab_t,
email_address VARCHAR2(30),
ok_to_mail VARCHAR2(1),
active_flag VARCHAR2(1))
NESTED TABLE person_phones STORE AS phones_tab
TABLESPACE uwdata;
desc person
COL table_name FORMAT a30
COL table_type_name FORMAT a20
COL parent_table_column FORMAT a25
SELECT table_name, table_type_name, parent_table_column
FROM user_nested_tables; |
Insert Into Parent And Nested Table |
INSERT INTO <table_name>
(<column_name_list>)
VALUES
(<list_of_column_values>,
<table_type_constructor>(<type_constructor(<type_values>))); |
INSERT INTO person
(person_id, last_name, person_phones)
VALUES
(1, 'Morgan', phones_tab_t(phone_t('C', '206-555-1212')));
INSERT INTO person
(person_id, last_name, person_phones)
VALUES
(2, 'Cline', phones_tab_t(phone_t('C', '425-555-1212'))); |
Insert Into Nested Table |
INSERT INTO TABLE (
SELECT <nested_table_column_name>
FROM <table_name>
WHERE <column_name> <condition <value>)
VALUES
(<column_value>, <column_value>); |
INSERT INTO TABLE(
SELECT person_phones
FROM person
WHERE person_id = 1)
VALUES
('H','206-987-6543');
SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2; |
| Select From Nested Table |
SELECT <column_name_list> FROM <table_name>; |
| SELECT * FROM person; |
| Select From Nested Table With TABLE Function |
SELECT <column_name_list, <tested_table_column_list>
FROM <table> <alias>, TABLE (<alias.nested_table_name> <alias>; |
SELECT person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2; |
Update All Nested Table Rows |
UPDATE person
SET person_phones =
<table_constructor>(<type_constructor(<column_values>)); |
UPDATE person
SET person_phones = phones_tab_t(phone_t('H', '360-555-1212'))
WHERE person_id = 2; |
Update Selected Nested Table Rows |
UPDATE TABLE (
<nested_select_statment>) <alias>
SET <nested_column> = <value>
WHERE <alias.nested_column_name> <condition> <value>; |
UPDATE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
SET p.phone_type = 'W'
WHERE p.phone_number = '206-987-6543';
SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2; |
Delete From Nested Table |
DELETE TABLE (
<nested_select_statment>) <alias>
WHERE <alias.nested_column_name> <condition> <value>; |
DELETE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
WHERE p.phone_number = '206-987-6543'; |
SELECT a_code FROM TABLE (
SELECT per_phone
FROM phone h) p
WHERE 1=1; |
| Create Type Header As Database Varray |
User Defined Object Data Type |
-- same as above
CREATE OR REPLACE TYPE <type_name> AS OBJECT (
<column_name> <data_type>);
/ |
CREATE OR REPLACE TYPE title_t AS OBJECT (
title_name VARCHAR2(5));
/
SELECT type_name, typecode
FROM user_types; |
Define VARRAY Of Object Type |
CREATE TYPE TitleList AS VARRAY(<integer>)
OF <data_type>;
/
SELECT type_name, typecode
FROM user_types; |
CREATE TYPE TitleList AS VARRAY(3) OF title_t;
/
SELECT type_name, typecode
FROM user_types; |
Heap Table With Nested Varray |
CREATE TABLE <table_name> (
column_name varray_name); |
DROP TABLE person;
CREATE TABLE person (
person_id NUMBER(10),
first_name VARCHAR2(25),
middle_inits VARCHAR2(4),
last_name VARCHAR2(25),
name_suffix VARCHAR2(5),
person_titles title_t,
dob DATE,
ssn VARCHAR2(11),
address_id NUMBER(10),
person_phones phones_tab_t,
email_address VARCHAR2(30),
ok_to_mail VARCHAR2(1),
active_flag VARCHAR2(1))
NESTED TABLE person_phones STORE AS phones_tab
TABLESPACE uwdata;
desc person
COL parent_table_column FORMAT a30
SELECT parent_table_name, parent_table_column, type_name
FROM user_varrays;
-- note there is no record |
Insert Into Parent, Varray, And Nested Table |
INSERT INTO <table_name>
(<column_name_list>)
VALUES
(<list_of_column_values>,
<varray_constructor>(<varray_values>)
<table_type_constructor>(<type_constructor(<type_values>))); |
INSERT INTO person
(person_id, last_name, person_titles, person_phones)
VALUES
(1, 'Morgan', title_t('PhD'),
phones_tab_t(phone_t('C', '206-555-1212')));
INSERT INTO person
(person_id, last_name, person_titles, person_phones)
VALUES
(2, 'Cline', title_t('MD'),
phones_tab_t(phone_t('C', '425-555-1212'))); |
Insert Into Nested Varray |
|
COL person_titles FORMAT a20
SELECT t1.person_id, last_name, person_titles, t2.*
FROM person t1, TABLE(t1.person_phones) t2; |
| Select From Nested Table |
SELECT <column_name_list> FROM <table_name>; |
SELECT person_id, last_name, person_titles, person_phones
FROM person; |
| Select From Nested Table With TABLE Function |
SELECT <column_name_list, <tested_table_column_list>
FROM <table> <alias>, TABLE (<alias.nested_table_name> <alias> |
SELECT person_id, last_name, t2.*
FROM person t1,
TABLE(t1.person_phones) t2; |
Update All Nested Table Rows |
UPDATE person
SET person_phones =
<table_constructor>(<type_constructor(<column_values>)); |
UPDATE person
SET person_phones = phones_tab_t(phone_t('H', '360-555-1212'))
WHERE person_id = 2; |
Update Selected Nested Table Rows |
UPDATE TABLE (
<nested_select_statment>) <alias>
SET <nested_column> = <value>
WHERE <nested_column_name> <condition> <value> |
UPDATE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
SET p.phone_type = 'W'
WHERE p.phone_number = '206-987-6543';
SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2; |
Delete From Nested Table |
DELETE TABLE (
<nested_select_statment>) <alias>
|
DELETE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
WHERE p.phone_number = '206-987-6543'; |
| |
| Create Type Body (always as a database object) |
| Create Type |
CREATE OR REPLACE TYPE ssn AS OBJECT (
ssn_type CHAR(11));
/ |
| Create Type Specification (Header) |
CREATE OR REPLACE TYPE ssn AS OBJECT (
n_ CHAR(11),
CONSTRUCTOR FUNCTION ssn(n IN VARCHAR2)
RETURN self AS result,
MEMBER FUNCTION get_ssn RETURN CHAR);
/ |
Create Type Body |
CREATE OR REPLACE TYPE BODY ssn IS
CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2)
RETURN self AS RESULT IS
BEGIN
IF TRANSLATE(ssn_in, 'A0123456789', 'BAAAAAAAAAA') =
'AAA-AA-AAAA' THEN
n_ := ssn_in;
RETURN;
ELSE
RAISE_APPLICATION_ERROR(-20001, 'INVALID SSN');
END IF;
END;
MEMBER FUNCTION get_ssn RETURN CHAR IS
BEGIN
RETURN n_;
END;
END;
/ |
| Create Object Table |
CREATE TABLE person (
per_name VARCHAR2(20),
per_ssn SSN); |
Test SSN Data Type |
DECLARE
myssn ssn;
BEGIN
myssn := ssn(n=>'232-22-5678');
INSERT INTO person VALUES ('Morgan', myssn);
-- myssn := ssn(n=>'444=55-6789');
-- INSERT INTO person VALUES ('Morgan', myssn);
myssn := ssn(n=>'123-45-6789');
INSERT INTO person VALUES ('Cline', myssn);
COMMIT;
END;
/ |
| |
| Alter Type |
Alter Type Demo |
ALTER TYPE <type name> ADD
ATTRIBUTE (<atribute name> <data type>) CASCADE; |
CREATE OR REPLACE TYPE phone_t AS OBJECT (
a_code CHAR(3),
p_number CHAR(8)) NOT FINAL;
/
desc phone_t
CREATE OR REPLACE TYPE phone_t_tab AS TABLE OF phone_t;
/
desc phone_t_tab
--produces an error
CREATE OR REPLACE TYPE phone_t AS OBJECT (
country_code CHAR(3),
area_code CHAR(3),
phone_number CHAR(8));
/
ALTER TYPE phone_t ADD
ATTRIBUTE (country_code CHAR(3)) CASCADE;
desc phone_t |
| |
| Drop Type |
| Dropping a Type |
DROP TYPE <type_name> |
DROP TABLE person;
DROP TYPE phones_tab_t;
DROP TYPE phone_t; |
| |
| Type Inheritance |
| Create Supertype |
CREATE OR REPLACE TYPE person_typ AS OBJECT (
ssn NUMBER(9), name VARCHAR2(30), address VARCHAR2(100))
NOT FINAL;
/ |
| Create Type From Subtype |
CREATE OR REPLACE TYPE person_tab_typ AS TABLE OF person_typ;
/ |
| Create Subtype |
CREATE OR REPLACE TYPE student_typ UNDER person_typ (
deptid NUMBER, major VARCHAR2(30))
NOT FINAL;
/ |
| Create Type From Subtype |
CREATE OR REPLACE TYPE student_tab_typ
AS TABLE OF student_typ;
/ |
Create Table With Two
Nested Tables |
CREATE TABLE test (
regular_field DATE,
person_nested_tab person_tab_typ,
student_nested_tab student_tab_typ)
NESTED TABLE person_nested_tab STORE AS per_tab
NESTED TABLE student_nested_tab STORE AS stu_tab;
desc test
desc per_tab
desc stu_tab |
Insert A Row |
INSERT INTO test
VALUES
(SYSDATE, person_tab_typ(), student_tab_typ(student_typ(987654321, 'Cline', 'PO Box 0', 101, 'Computer Science')));
INSERT INTO test
VALUES
(SYSDATE, person_tab_typ(), student_tab_typ(student_typ(987654321, 'Cline', 'PO Box 0', 101, 'Computer Science'),
student_typ(12345, 'Starr', '123 Main St', 102, 'Agricultureal Science')));
INSERT INTO test
VALUES
(TRUNC(SYSDATE), person_tab_typ(), student_tab_typ());
SELECT * FROM test;
INSERT INTO TABLE (
SELECT person_nested_tab
FROM test
WHERE regular_field = TRUNC(SYSDATE))
VALUES (111223456, 'Morgan', '123 Main Street');
SELECT * FROM test;
INSERT INTO TABLE (
SELECT student_nested_tab
FROM test
WHERE regular_field = TRUNC(SYSDATE))
VALUES (987654321, 'Cline', 'PO Box 123', 101, 'Frontal Lobotomy Can Be Fun' );
INSERT INTO TABLE (
SELECT student_nested_tab
FROM test
WHERE regular_field = TRUNC(SYSDATE))
VALUES (987654321, 'Cline', 'PO Box 123', 299, 'Advanced Basket Weaving');
SELECT * FROM test;
SET HEAD OFF
SELECT t1.*, t2.*, t3.*
FROM test t1,
TABLE(person_nested_tab) t2,
TABLE(student_nested_tab) t3; |
| |
| Hold |
Constructor for a Nested Table |
In the following example, you pass multiple elements
to the constructor CourseList(), which returns a nested table containing those elements:
DECLARE
TYPE CourseList IS TABLE OF VARCHAR2(16);
my_courses CourseList;
BEGIN
my_courses := CourseList('Econ 2010','Acct 3401','Mgmt 3100');
END;
/
Because a PL/SQL table does not have a declared maximum size, you can put as many elements in the constructor as necessary.
DECLARE
TYPE CourseList IS TABLE OF VARCHAR2(16);
my_courses CourseList;
BEGIN
my_courses := CourseList('Econ 2010','Acct 3401','Mgmt 3100');
my_courses := CourseList('Math 2022','Acct 3431','Mgmt 3100');
my_courses := CourseList('Phys 2299','Chem 9876');
my_courses := CourseList('Food 9999');
my_courses := CourseList('Orcl 3456','Math 3434','Hist 1040');
END;
/ |
| Defining a Type as a database object |
CREATE OR REPLACE TYPE CourseList
AS TABLE OF VARCHAR2(64);
/ |
Defining Types in a package header |
CREATE OR REPLACE PACKAGE xyz IS
TYPE CourseList IS TABLE OF VARCHAR2(64);
TYPE PartNum IS TABLE OF parent.part_num%TYPE
INDEX BY BINARY_INTEGER;
END;
/ |
| |
set serveroutput on
DECLARE
TYPE demo IS TABLE OF VARCHAR2(32767);
x demo;
BEGIN
x := demo(RPAD('X', 32766, 'Z'));
dbms_output.put_line(x(1));
END;
/ |
| |
CREATE TYPE data_typ AS OBJECT
(year NUMBER, MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER);
CREATE TYPE BODY data_typ IS
MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
BEGIN
RETURN (year + invent);
END;
END;
/ |
| |
CREATE TYPE person_t AS OBJECT (name VARCHAR2(100), ssn NUMBER)
NOT FINAL;
CREATE TYPE employee_t UNDER person_t
(department_id NUMBER, salary NUMBER) NOT FINAL;
CREATE TYPE part_time_emp_t UNDER employee_t (num_hrs NUMBER);
|
| |
CREATE OR REPLACE TYPE emp_type AS OBJECT (
eno NUMBER, ename CHAR(31) , eaddr addr_t); |
CREATE OR REPLACE TYPE phone_t AS OBJECT (
a_code CHAR(3),
p_number CHAR(8));
/ |
| |
-- user defined data type for use by function
CREATE OR REPLACE TYPE InStrTab IS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION contains_all (useridin dms_user.id%TYPE, stringin VARCHAR2, checkint INTEGER) RETURN INTEGER IS
i BINARY_INTEGER;
my_table dbms_utility.uncl_array;
stringary InStrTab;
BEGIN
-- convert stringin of domain ids into a PL/SQL table
dbms_utility.comma_to_table(stringin, i, my_table);
-- initialize a collection
stringary := InStrTab('');
-- extend the collection to the size of the PL/SQL table
stringary.EXTEND(my_table.COUNT);
-- for each element in the PL/SQL table
FOR j IN 1 .. my_table.COUNT LOOP
-- remove the double-quotes
my_table(j) := TRANSLATE(my_table(j), 'A"', 'A');
-- assign it to an element in the array
stringary(j) := my_table(j);
END LOOP;
-- check the count of array elements found in the user_domain_map table
SELECT COUNT(*)
INTO i
FROM zuser_domain_map
WHERE user_id = useridin
AND domain_id IN (
SELECT column_value
FROM TABLE(CAST(stringary AS InStrTab)));
-- compare the number found agains the checksum
IF i >= checkint THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END contains_all;
/
-- How to test:
SELECT contains_all(121, '"200","201","207"',3)
FROM dual; |
