TXT

oracle_init_table

By Shane Kennedy,2014-05-27 15:10
12 views 0
oracle_init_table

     本文由xxoo689贡献

     prompt PL/SQL Developer import file

     prompt Created on 20101230 by wangdr

     set feedback off

     set define off

     prompt Dropping BONUS„„

     drop table BONUS cascade constraints;

     prompt Dropping DEPT„„

     drop table DEPT cascade constraints;

     prompt Dropping EMP„„

     drop table EMP cascade constraints;

     prompt Dropping SALGRADE„„

     drop table SALGRADE cascade constraints;

     prompt Creating BONUS„„

     create table BONUS

     (

     ENAME VARCHAR2(10),

     JOB VARCHAR2(9),

     SAL NUMBER,

     COMM NUMBER

     )

     ;

     comment on table BONUS

     is '奖金表';

     comment on column BONUS.ENAME

     is '雇员姓名';

     comment on column BONUS.JOB

     is '工作类别';

     comment on column BONUS.SAL

     is '薪水';

     comment on column BONUS.COMM

     is '佣金';

     prompt Creating DEPT„„

     create table DEPT

     (

     DEPTNO NUMBER(2) not null,

     DNAME VARCHAR2(14),

     LOC VARCHAR2(13)

     )

     ;

     comment on table DEPT

     is '部门表';

     comment on column DEPT.DEPTNO

     is '部门编号';

     comment on column DEPT.DNAME

     is '部门名';

     comment on column DEPT.LOC

     is '地址';

     alter table DEPT

     add constraint PK_DEPT primary key (DEPTNO);

     prompt Creating EMP„„

     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)

     )

     ;

     comment on table EMP

     is '雇员信息表';

     comment on column EMP.EMPNO

     is '雇员编号';

     comment on column EMP.ENAME

     is '雇员姓名';

     comment on column EMP.JOB

     is '工作类别';

     comment on column EMP.MGR

     is '雇主编号';

     comment on column EMP.HIREDATE

     is '雇员日期';

     comment on column EMP.SAL

     is '薪水';

     comment on column EMP.COMM

     is '佣金';

     comment on column EMP.DEPTNO

     is '部门编号';

     alter table EMP

     add constraint PK_EMP primary key (EMPNO);

     alter table EMP

     add constraint FK_DEPTNO foreign key (DEPTNO)

     references DEPT (DEPTNO);

     prompt Creating SALGRADE„„

     create table SALGRADE

     (

     GRADE NUMBER,

     LOSAL NUMBER,

     HISAL NUMBER

     )

     ;

     comment on table SALGRADE

     is '薪水级别表';

     comment on column SALGRADE.GRADE

     is '级别';

     comment on column SALGRADE.LOSAL

     is '最低薪水';

     comment on column SALGRADE.HISAL

     is '最高薪水';

     prompt Disabling triggers for BONUS„„

     alter table BONUS disable all triggers;

     prompt Disabling triggers for DEPT„„

     alter table DEPT disable all triggers;

     prompt Disabling triggers for EMP„„

     alter table EMP disable all triggers;

     prompt Disabling triggers for SALGRADE„„

     alter table SALGRADE disable all triggers;

     prompt Disabling foreign key constraints for EMP„„

     alter table EMP disable constraint FK_DEPTNO;

     prompt Loading BONUS„„

     prompt Table is empty

     prompt Loading DEPT„„

     insert into DEPT (DEPTNO, DNAME, LOC)

     values (10, 'ACCOUNTING', 'NEW YORK');

     insert into DEPT (DEPTNO, DNAME, LOC)

     values (20, 'RESEARCH', 'DALLAS');

     insert into DEPT (DEPTNO, DNAME, LOC)

     values (30, 'SALES', 'CHICAGO');

     insert into DEPT (DEPTNO, DNAME, LOC)

     values (40, 'OPERATIONS', 'BOSTON');

     commit;

     prompt 4 records loaded

     prompt Loading EMP„„

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800,

    null, 20);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'),

    1600, 300, 30);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);

     insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

     values (8888, 'RAYMOND', 'MANAGER', 7839, to_date('01-07-2003 08:30:00', 'dd-mm-yyyy hh24:mi:ss'), 4000, null, null);

     commit;

     prompt 15 records loaded

     prompt Loading SALGRADE„„

     insert into SALGRADE (GRADE, LOSAL, HISAL)

     values (1, 700, 1200);

     insert into SALGRADE (GRADE, LOSAL, HISAL)

     values (2, 1201, 1400);

     insert into SALGRADE (GRADE, LOSAL, HISAL)

     values (3, 1401, 2000);

     insert into SALGRADE (GRADE, LOSAL, HISAL)

     values (4, 2001, 3000);

     insert into SALGRADE (GRADE, LOSAL, HISAL)

     values (5, 3001, 9999);

     commit;

     prompt 5 records loaded

     prompt Enabling foreign key constraints for EMP„„

     alter table EMP enable constraint FK_DEPTNO;

     prompt Enabling triggers for BONUS„„

     alter table BONUS enable all triggers;

     prompt Enabling triggers for DEPT„„

     alter table DEPT enable all triggers;

     prompt Enabling triggers for EMP„„

     alter table EMP enable all triggers;

     prompt Enabling triggers for SALGRADE„„

     alter table SALGRADE enable all triggers;

     set feedback on

     set define on

     prompt Done.

    TXT由“文库宝”下载:http://www.mozhua.net/wenkubao

Report this document

For any questions or suggestions please email
cust-service@docsford.com