Oracle PL/SQL/Object Oriented Database/Object Column
Содержание
- 1 Alter a table with user-defined object to upgrade including data
- 2 Check object table column type
- 3 Format column in the object
- 4 Nested type Column
- 5 Query column with user-defined type
- 6 Reference nested data type in select statement
- 7 Reference type column
- 8 The Object Type Column Objects
- 9 This script demonstrates column objects.
- 10 use user-defined type as the column type
- 11 Use varray in a table
Alter a table with user-defined object to upgrade including data
SQL>
SQL> create or replace
2 type person as object(
3 first_name varchar2(100),
4 last_name varchar2(100) )
5 /
Type created.
SQL>
SQL>
SQL> create table person_table(
2 name person,
3 age number )
4 /
Table created.
SQL>
SQL>
SQL>
SQL> alter table person_table upgrade including data;
Table altered.
SQL>
SQL>
SQL> drop table person_table;
Table dropped.
SQL>
SQL>
SQL> drop type person;
Type dropped.
SQL>
SQL>
Check object table column type
SQL>
SQL> create or replace type address_type
2 as object
3 ( city varchar2(30),
4 street varchar2(30),
5 state varchar2(2),
6 zip number
7 )
8 /
Type created.
SQL> create or replace type person_type
2 as object
3 ( name varchar2(30),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created.
SQL> create table people1 of person_type
2 /
Table created.
SQL> desc people1
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
NAME VARCHAR2(30)
DOB DATE
HOME_ADDRESS ADDRESS_TYPE
WORK_ADDRESS ADDRESS_TYPE
SQL> select name, segcollength
2 from sys.col$
3 where obj# = ( select object_id
4 from user_objects
5 where object_name = "PEOPLE1" )
6 /
NAME SEGCOLLENGTH
-------------------- ------------
SYS_NC_OID$ 16
SYS_NC_ROWINFO$ 1
NAME 30
DOB 7
HOME_ADDRESS 1
SYS_NC00006$ 30
SYS_NC00007$ 30
SYS_NC00008$ 2
SYS_NC00009$ 22
WORK_ADDRESS 1
SYS_NC00011$ 30
SYS_NC00012$ 30
SYS_NC00013$ 2
SYS_NC00014$ 22
14 rows selected.
SQL>
SQL> drop table people1;
Table dropped.
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL> --
Format column in the object
SQL> -- Use discrete attribute names
SQL>
SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5));
6 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE emp (empno NUMBER(3),
2 name VARCHAR2(20),
3 address ADDRESS_OBJ);
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL> COLUMN name FORMAT a9
SQL> COLUMN empno FORMAT 999999
SQL> COLUMN address FORMAT a50
SQL>
SQL>
SQL> SELECT empno, name, address FROM emp;
EMPNO NAME ADDRESS(STREET, CITY, STATE, ZIP)
------- --------- --------------------------------------------------
101 Adam ADDRESS_OBJ("1 A St.", "Mobile", "AL", "36608")
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>
Nested type Column
SQL> create or replace type addressType as object
2 (
3 Street VARCHAR2(50),
4 City VARCHAR2(25),
5 State CHAR(2),
6 Zip NUMBER
7 );
8 /
SQL> create or replace type personType as object
2 (
3 Name VARCHAR2(25),
4 Address addressType
5 );
6 /
SQL> create table CUSTOMER
2 (
3 cid NUMBER,
4 Person personType
5 );
SQL>
SQL> insert into CUSTOMER values(444, personType("Name",addressType("1 RD", "Van", "MA", "10002")));
SQL>
SQL>
SQL> drop table customer;
SQL> drop type personType;
SQL> drop type addressType;
SQL>
Query column with user-defined type
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> create or replace type emp_type
2 as object
3 (empno number(4),
4 ename varchar2(10),
5 job varchar2(9),
6 mgr number(4),
7 hiredate date,
8 sal number(7, 2),
9 comm number(7, 2)
10 );
11 /
Type created.
SQL>
SQL> create or replace type emp_tab_type as table of emp_type;
2 /
Type created.
SQL> create table dept_and_emp
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13),
5 emps emp_tab_type
6 )
7 nested table emps store as emps_nt;
Table created.
SQL>
SQL>
SQL>
SQL> insert into dept_and_emp
2 select dept.*,
3 CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm from emp ) AS emp_tab_type )
4 from dept
5 /
4 rows created.
SQL> select deptno, dname, loc, d.emps AS employees
2 from dept_and_emp d
3 where deptno = 10
4 /
SQL>
SQL>
SQL> drop table emp cascade constraints;
Table dropped.
SQL> drop table dept cascade constraints;
Table dropped.
SQL> drop table dept_and_emp;
Table dropped.
SQL> drop type emp_tab_type;
Type dropped.
SQL> drop type emp_type;
Type dropped.
SQL>
SQL> --
Reference nested data type in select statement
SQL> create type addressType as object
2 (Street VARCHAR2(50),
3 City VARCHAR2(25),
4 State CHAR(2),
5 Zip NUMBER);
6 /
SQL>
SQL> create type personType as object
2 (Name VARCHAR2(25),
3 Address addressType);
4 /
SQL>
SQL>
SQL> create or replace type personType as object
2 (Name VARCHAR2(25),
3 Address addressType);
4 /
SQL>
SQL>
SQL> create table myemp
2 (cid NUMBER,
3 Person personType);
SQL>
SQL>
SQL> insert into myemp values
2 (1,personType("SomeName",
3 addressType("StreetValue","CityValue","ST",11111)));
SQL>
SQL>
SQL> select C.Person.Name,
2 C.Person.Address.City
3 from myemp C
4 where C.Person.Address.City like "C%";
PERSON.NAME PERSON.ADDRESS.CITY
------------------------- -------------------------
SomeName CityValue
SQL>
SQL>
SQL> drop table myemp;
SQL> drop type personType;
SQL> drop type addressType;
SQL>
SQL>
Reference type column
SQL> CREATE or replace TYPE BookType;
2 /
Type created.
SQL> PROMPT Now we can create ProductType successfully
Now we can create ProductType successfully
SQL>
SQL> CREATE OR REPLACE TYPE ProductType AS OBJECT (
2 item_id NUMBER(10),
3 num_in_stock NUMBER(10),
4 reorder_status VARCHAR2(20),
5 price REF BookType);
6 /
Type created.
The Object Type Column Objects
SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5));
6 /
Type created.
SQL> CREATE TABLE emp (empno NUMBER(3),
2 name VARCHAR2(20),
3 address ADDRESS_OBJ);
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam", ADDRESS_OBJ("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL> SELECT * FROM emp;
EMPNO NAME
---------- --------------------
ADDRESS(STREET, CITY, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
101 Adam
ADDRESS_OBJ("1 A St.", "Mobile", "AL", "36608")
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>
This script demonstrates column objects.
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BookType AS OBJECT (
2 rebate NUMBER (10, 4),
3 price NUMBER (10, 2),
4 MEMBER FUNCTION discount_price
5 RETURN NUMBER
6 )
7 INSTANTIABLE FINAL;
8 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY BookType
2 AS
3 MEMBER FUNCTION discount_price
4 RETURN NUMBER
5 IS
6 BEGIN
7 RETURN (SELF.price * (1 - SELF.rebate));
8 END discount_price;
9 END;
10 /
Type body created.
SQL>
SQL> CREATE TABLE bookTable (
2 item_id NUMBER(10) PRIMARY KEY,
3 num_in_stock NUMBER(10),
4 reorder_status VARCHAR2(20 CHAR),
5 price BookType
6 );
Table created.
SQL>
SQL> INSERT INTO bookTable VALUES (1, 10, "IN STOCK", BookType (.1, 75));
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> DECLARE
2 v_price BookType;
3 BEGIN
4 SELECT price INTO v_price FROM bookTable WHERE item_id = 1;
5
6 DBMS_OUTPUT.put_line ("Price BEFORE update: " || v_price.discount_price);
7 v_price.rebate := .2;
8
9 UPDATE bookTable SET price = v_price;
10
11 DBMS_OUTPUT.put_line ("Price AFTER update: " || v_price.discount_price);
12 ROLLBACK;
13 END;
14 /
Price BEFORE update: 67.5
Price AFTER update: 60
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT i.price.price, i.price.rebate FROM bookTable i;
PRICE.PRICE PRICE.REBATE
----------- ------------
75 .1
1 row selected.
SQL>
SQL> SELECT i.price.discount_price() FROM bookTable i;
I.PRICE.DISCOUNT_PRICE()
------------------------
67.5
1 row selected.
SQL>
SQL> drop table bookTable;
Table dropped.
SQL>
use user-defined type as the column type
SQL>
SQL> create table dept
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13)
5 );
Table created.
SQL>
SQL>
SQL> create table emp
2 (empno number(4) primary key,
3 ename varchar2(10),
4 job varchar2(9),
5 mgr number(4) references emp,
6 hiredate date,
7 sal number(7, 2),
8 comm number(7, 2),
9 deptno number(2) references dept
10 );
Table created.
SQL>
SQL>
SQL> create or replace type emp_type
2 as object
3 (empno number(4),
4 ename varchar2(10),
5 job varchar2(9),
6 mgr number(4),
7 hiredate date,
8 sal number(7, 2),
9 comm number(7, 2)
10 );
11 /
SQL>
SQL> create or replace type emp_tab_type as table of emp_type;
2 /
SQL> create table dept_and_emp
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13),
5 emps emp_tab_type
6 )
7 nested table emps store as emps_nt;
SQL>
SQL>
SQL> drop table dept_and_emp;
Table dropped.
SQL> drop table emp cascade constraints;
Table dropped.
SQL> drop table dept cascade constraints;
Table dropped.
SQL> drop type emp_tab_type;
Type dropped.
SQL> drop type emp_type;
Type dropped.
SQL>
SQL>
SQL> --
Use varray in a table
SQL> CREATE or replace TYPE addressType AS OBJECT (
2 street VARCHAR2(15),
3 city VARCHAR2(15),
4 state CHAR(2),
5 zip VARCHAR2(5)
6 );
7 /
Type created.
SQL>
SQL> CREATE or replace TYPE addressTypeVArray AS VARRAY(2) OF VARCHAR2(50);
2 /
SQL>
SQL> CREATE or replace TYPE addressTypeNestedTable AS TABLE OF addressType;
2 /
Type created.
SQL>
SQL> --
SQL> CREATE GLOBAL TEMPORARY TABLE empTempTable (
2 id INTEGER PRIMARY KEY,
3 fname VARCHAR2(10),
4 lname VARCHAR2(10),
5 addresses addressTypeVArray
6 );
Table created.
SQL>
SQL> CREATE TABLE empTable (
2 id INTEGER PRIMARY KEY,
3 fname VARCHAR2(10),
4 lname VARCHAR2(10),
5 addresses addressTypeNestedTable
6 )
7 NESTED TABLE
8 addresses
9 STORE AS
10 nested_addresses2 TABLESPACE users;
Table created.
SQL>
SQL>
SQL> -- equal/not equal example
SQL> CREATE OR REPLACE PROCEDURE equal_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 emp3 charTable;
6 result BOOLEAN;
7 BEGIN
8 emp1 := charTable("A", "B", "C");
9 emp2 := charTable("A", "B", "C");
10 emp3 := charTable("B", "C", "D");
11
12 result := emp1 = emp2;
13 IF result THEN
14 DBMS_OUTPUT.PUT_LINE("emp1 equal to emp2");
15 END IF;
16
17 END equal_example;
18 /
Procedure created.
SQL>
SQL> drop type addressType force;
Type dropped.
SQL> drop type addressTypeVArray force;
Type dropped.
SQL> drop TYPE addressTypeNestedTable force;
Type dropped.
SQL> drop TABLE empTable;
Table dropped.