oracle自定义类型使用方法

来源：互联网发布：php 无刷新post 编辑：程序博客网时间：2024/05/22 02:24

<1>CREATE TYPE T_DEPT AS OBJECT(DEPTNO NUMBER,DNAME VARCHAR2(20),LOC VARCHAR2(20));

<2>

-- Create table
create table EMP
(
empno    NUMBER(4) not null,
ename    VARCHAR2(10),
job      VARCHAR2(9),
mgr      NUMBER(4),
hiredate DATE,
sal      NUMBER(7,2),
comm     NUMBER(7,2),
deptno   NUMBER(2),
dept     T_DEPT
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table EMP
add constraint PK_EMP primary key (EMPNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
<3>

DECLARE
T SCOTT.DEPT%ROWTYPE;
BEGIN
FOR REC IN (SELECT EMPNO, DEPTNO FROM EMP) LOOP

SELECT * INTO T FROM SCOTT.DEPT WHERE DEPTNO = REC.DEPTNO;
UPDATE EMP A SET A.DEPT = T_DEPT(T.DEPTNO, T.DNAME, T.LOC);
END LOOP;
END;

SELECT * FROM EMP;

<4>

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPT.DEPTNO DEPT.DNAME DEPT.LOC
1 7369 SMITH CLERK 7902 12/17/1980 800.00  20 10 ACCOUNTING NEW YORK
2 7499 ALLEN SALESMAN 7698 2/20/1981 1600.00 300.00 30 10 ACCOUNTING NEW YORK
3 7521 WARD SALESMAN 7698 2/22/1981 1250.00 500.00 30 10 ACCOUNTING NEW YORK
4 7566 JONES MANAGER 7839 4/2/1981 2975.00  20 10 ACCOUNTING NEW YORK
5 7654 MARTIN SALESMAN 7698 9/28/1981 1250.00 1400.00 30 10 ACCOUNTING NEW YORK
6 7698 BLAKE MANAGER 7839 5/1/1981 2850.00  30 10 ACCOUNTING NEW YORK
7 7782 CLARK MANAGER 7839 6/9/1981 2450.00  10 10 ACCOUNTING NEW YORK
8 7788 SCOTT ANALYST 7566 4/19/1987 3000.00  20 10 ACCOUNTING NEW YORK
9 7839 KING PRESIDENT  11/17/1981 5000.00  10 10 ACCOUNTING NEW YORK
10 7844 TURNER SALESMAN 7698 9/8/1981 1500.00 1.00 30 10 ACCOUNTING NEW YORK
11 7876 ADAMS CLERK 7788 5/23/1987 1100.00  20 10 ACCOUNTING NEW YORK
12 7900 JAMES CLERK 7698 12/3/1981 950.00  30 10 ACCOUNTING NEW YORK
13 7902 FORD ANALYST 7566 12/3/1981 3000.00  20 10 ACCOUNTING NEW YORK
14 7934 MILLER CLERK 7782 1/23/1982 1300.00  10 10 ACCOUNTING NEW YORK