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 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));
/
desc ssn_t
SELECT object_name, object_type
FROM user_objects
ORDER BY 2;
desc user_types
col typecode format a10
SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;
-- examine type definition
col text format a60
SELECT *
FROM user_source
WHERE name = 'SSN_T';
CREATE TABLE ssn (
per_id NUMBER(10),
per_ssn ssn_t);
desc ssn
-- examine table columns
col data_type format a30
SELECT column_name, data_type
FROM user_tab_cols
WHERE table_name = 'SSN';
INSERT INTO ssn VALUES (1, '123-45-6789');
set describe depth all linenum on indent on
desc ssn
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 (per_id, per_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;
DROP TYPE ssn_t;
DROP TABLE ssn PURGE;
DROP TYPE ssn_t; |
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));
/
SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;
-- examine type definition
col text format a40
SELECT *
FROM user_source
WHERE name = 'PHONE_T';
CREATE TABLE phone (
per_id NUMBER(10),
per_phone phone_t);
desc phone
set describe depth all
desc phone
set describe depth all linenum on indent on
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_cols
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'));
COMMIT;
SELECT * FROM phone;
col per_phone format a30
SELECT per_id, PER_PHONE
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;
COMMIT;
-- selective delete
DELETE FROM phone p
WHERE p.per_id = 2;
SELECT * FROM phone;
ROLLBACK;
SELECT * FROM phone;
DELETE FROM phone p
WHERE p.per_phone.a_code = '303';
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;
/
SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;
-- derive collection type from supertype
CREATE OR REPLACE TYPE person_tab_typ AS TABLE OF
person_typ;
/
SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;
-- for more information about collections
-- see the collections page of the library
-- derive object subtype from object supertype
CREATE OR REPLACE TYPE student_typ UNDER
person_typ (
deptid NUMBER, major VARCHAR2(30))
NOT FINAL;
/
SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;
-- derive collection type from subtype
CREATE OR REPLACE TYPE student_tab_typ
AS TABLE OF student_typ;
/
SELECT type_name, typecode, attributes, methods, predefined, incomplete,
final, instantiable
FROM user_types;
-- 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;
-- for more information on nested tables
-- see the nested tables page of the library
desc test
desc per_tab
desc stu_tab |
|
Create Type Body (always as a database object) |
Create Function |
CREATE OR REPLACE FUNCTION
validate_ssn(ssn_in IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
IF TRANSLATE(ssn_in, 'A0123456789', 'BAAAAAAAAAA') = 'AAA-AA-AAAA' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF ;
END
validate_ssn;
/ |
Create Type Specification (Header) |
CREATE OR REPLACE TYPE ssn AS OBJECT (
n_ CHAR(11),
CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2) RETURN
self AS result,
MEMBER FUNCTION get_ssn RETURN CHAR);
/
desc ssn |
Create Type Body |
CREATE OR REPLACE TYPE BODY ssn IS
CONSTRUCTOR FUNCTION ssn(ssn_in IN VARCHAR2)
RETURN self AS RESULT IS
BEGIN
IF validate_ssn(ssn_in) 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;
/
SELECT object_name, object_type
FROM user_objects
ORDER BY 2;
desc ssn |
Create Object Table |
CREATE TABLE person (
per_name VARCHAR2(20),
per_ssn SSN);
desc person
set describe depth all linenum on indent on
desc person
desc user_tab_cols
col data_type format a20
SELECT column_name, data_type, data_type_mod, data_type_owner
FROM user_tab_cols
WHERE table_name = 'PERSON'; |
Test SSN Data Type |
DECLARE
myssn ssn;
BEGIN
myssn := ssn(ssn_in=>'232-22-5678');
INSERT INTO person VALUES ('Morgan', myssn);
myssn := ssn(ssn_in=>'444=55-6789');
INSERT INTO person VALUES ('Morgan', myssn);
myssn := ssn(ssn_in=>'123-45-6789');
INSERT INTO person VALUES ('Cline', myssn);
COMMIT;
END;
/
DECLARE
myssn ssn;
BEGIN
myssn := ssn(ssn_in=>'232-22-5678');
INSERT INTO person VALUES ('Morgan', myssn);
/*
myssn := ssn(ssn_in=>'444=55-6789');
INSERT INTO person VALUES ('Morgan', myssn);
*/
myssn := ssn(ssn_in=>'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
desc phone_t_tab |
|
Drop Type |
Dropping a Type |
DROP TYPE <type_name> |
DROP TABLE person;
-- fails
DROP TYPE phone_t;
DROP TYPE phone_t_tab;
DROP TYPE phone_t; |
Dropping a Type with dependencies |
DROP TYPE <type_name> FORCE; |
CREATE OR REPLACE
TYPE phone_t AS OBJECT (
a_code CHAR(3),
p_number CHAR(8)) NOT FINAL;
/
CREATE OR REPLACE TYPE phone_t_tab AS TABLE OF phone_t;
/
-- will fail;
DROP TYPE phone_t;
DROP TYPE phone_t FORCE; |
|
Type Inheritance |
Create Supertype |
CREATE OR REPLACE TYPE person_typ AS OBJECT (
ssn NUMBER(9), name VARCHAR2(30), address VARCHAR2(100))
NOT FINAL;
/
SELECT object_name, object_type
FROM user_objects;
SELECT type_name, supertype_owner, supertype_name
FROM user_types; |
Create Type From Supertype |
CREATE OR REPLACE TYPE person_tab_typ AS TABLE OF person_typ;
/
SELECT object_name, object_type
FROM user_objects;
SELECT type_name, supertype_owner, supertype_name
FROM user_types; |
Create Subtype |
CREATE OR REPLACE TYPE student_typ UNDER person_typ (
deptid NUMBER, major VARCHAR2(30))
NOT FINAL;
/
SELECT object_name, object_type
FROM user_objects;
SELECT type_name, supertype_owner, supertype_name
FROM user_types; |
Create Type From Subtype |
CREATE OR REPLACE TYPE student_tab_typ
AS TABLE OF student_typ;
/
SELECT object_name, object_type
FROM user_objects;
SELECT type_name, supertype_owner, supertype_name
FROM user_types; |
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;
set head on |
|
Type Demos |
Set operations with TypesThank you Jim Kennedy for this code |
DECLARE
TYPE l_test_type IS TABLE OF VARCHAR2(10);
l_test1 l_test_type := l_test_type();
l_test2 l_test_type := l_test_type();
l_test_result l_test_type;
BEGIN
l_test1.EXTEND(3);
l_test1(1) := 'foo';
l_test1(2) := 'moo';
l_test1(3) := 'too';
l_test2.EXTEND(2);
l_test2(1) := 'foo';
l_test2(2) := 'woo';
dbms_output.put_line('===== Union Sets Keep Duplicates =====');
l_test_result := l_test1 MULTISET UNION l_test2;
FOR i IN 1 .. l_test_result.COUNT LOOP
dbms_output.put_line(TO_CHAR(i) || ' ' || l_test_result(i));
END LOOP;
dbms_output.put_line('========================================');
dbms_output.put_line('===== Union Sets Remove Duplicates =====');
l_test_result := l_test1 MULTISET UNION DISTINCT l_test2;
FOR i IN 1 .. l_test_result.COUNT LOOP
dbms_output.put_line(TO_CHAR(i) || ' ' || l_test_result(i));
END LOOP;
dbms_output.put_line('========================================');
END TEST_PROC;
/ |
|
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;
/ |
Type and type body creation |
CREATE OR REPLACE TYPE data_typ AS OBJECT
(year NUMBER, MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER);
/
CREATE OR REPLACE TYPE BODY data_typ IS
MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
BEGIN
RETURN (year + invent);
END;
END;
/ |
Type based on a type |
CREATE OR REPLACE TYPE person_t AS OBJECT (name VARCHAR2(100), ssn NUMBER)
NOT FINAL;
/
CREATE OR REPLACE TYPE employee_t UNDER person_t
(department_id NUMBER, salary NUMBER) NOT FINAL;
/
CREATE OR REPLACE TYPE part_time_emp_t UNDER employee_t(num_hrs NUMBER);
/ |
A user defined data type used by a 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; |
|