| 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 | | ALTER ANY TYPE | DROP ANY TYPE | | CREATE ANY TYPE | EXECUTE ANY TYPE | | CREATE TYPE | UNDER 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 TYPEssn_tAS 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_ssnssn_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 TYPEphone_tAS 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_phonephone_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 TYPEperson_typAS 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 TYPEperson_tab_typAS TABLE OFperson_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 TYPEstudent_typUNDERperson_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 TYPEstudent_tab_typAS TABLE OFstudent_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 TABLEperson_nested_tabSTORE ASper_tab
NESTED TABLEstudent_nested_tabSTORE ASstu_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 FUNCTIONvalidate_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 ;
ENDvalidate_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
IFvalidate_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 TYPEphone_tADD 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; | | | | 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_typUNDERperson_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 TABLEperson_nested_tabSTORE ASper_tab
NESTED TABLEstudent_nested_tabSTORE ASstu_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 | | | | 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
TYPECourseListIS TABLE OF VARCHAR2(16);
my_coursesCourseList;
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
TYPECourseListIS TABLE OF VARCHAR2(16);
my_coursesCourseList;
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 TYPECourseListAS TABLE OFVARCHAR2(64);
/ |
Defining Types in a package header | CREATE OR REPLACE PACKAGE xyz IS
TYPECourseListIS TABLE OFVARCHAR2(64);
TYPEPartNumIS TABLE OFparent.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 TYPEphone_tAS 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; |
|
