area 数据库模式总结

drop table EMP cascade constraints;

/*==============================================================*/
/* Table: EMP                                                   */
/*==============================================================*/
create table EMP  (
   ID                   INTEGER                         not null,
   REALNAME             varchar2(50),
   LOGINNAME            varchar2(50),
   DEPTID               integer                         not null,
   constraint PK_EMP primary key (ID)
);

comment on table EMP is
'员工';

comment on column EMP.ID is
'主键编号';

comment on column EMP.REALNAME is
'真实名';

comment on column EMP.LOGINNAME is
'登录名';

comment on column EMP.DEPTID is
'所属部门';

 

drop table DEPT cascade constraints;

/*==============================================================*/
/* Table: DEPT                                                  */
/*==============================================================*/
create table DEPT  (
   DEPTID               integer                         not null,
   DEPTNAME             varchar2(50)                    not null,
   constraint PK_DEPT primary key (DEPTID)
);

comment on table DEPT is
'部门';

comment on column DEPT.DEPTID is
'部门主键';

comment on column DEPT.DEPTNAME is
'部门名';

 

 

 

--创建视图

 

select * from emp;
select * from dept;

create or replace view emp_v
as
select e.id,e.realname,e.loginname,e.deptid,t.deptname from emp e,dept t where e.deptid=t.deptid;

create or replace view dept_v
as
select * from dept;

 

--创建序列

 

 

drop SEQUENCE emp_seq;
drop SEQUENCE dept_seq;
CREATE  SEQUENCE emp_seq 
     INCREMENT BY 1   -- 每次加几个 
     START WITH 10000     -- 从1开始计数 
     NOMAXVALUE       -- 不设置最大值 
     NOCYCLE          -- 一直累加，不循环 
     CACHE 10; 
CREATE SEQUENCE dept_seq 
     INCREMENT BY 1   -- 每次加几个 
     START WITH 10000     -- 从1开始计数 
     NOMAXVALUE       -- 不设置最大值 
     NOCYCLE          -- 一直累加，不循环 
     CACHE 10; 

select emp_seq.nextval from dual;
select dept_seq.nextval from dual;

 

--创建触发器
create or replace trigger emp_v_t
  INSTEAD OF INSERT or update or delete  ON emp_v FOR EACH ROW
begin
  if inserting then
    insert into emp(id,realname,loginname,deptid) values(emp_seq.nextval,:new.realname,:new.loginname,:new.deptid);
  end if;
  if updating then
    update emp set realname=:new.realname,loginname=:new.loginname,deptid=:new.deptid where id=:old.id;
  end if;
  if deleting then
     delete emp where id=:old.id;
  end if;
end;

create or replace trigger dept_v_t
  INSTEAD OF INSERT or update or delete  ON dept_v FOR EACH ROW
begin
  if inserting then
    insert into dept(deptid,deptname) values(dept_seq.nextval,:new.deptname);
  end if;
  if updating then
    update dept set deptname=:new.deptname where deptid=:old.deptid;
  end if;
  if deleting then
     delete dept where deptid=:old.deptid;
  end if;
end;

 

--测试数据

 

insert into dept_v(DEPTNAME) values('软件部')
update dept_v set deptname='软件部' where deptid=10001
insert into emp_v(realname,loginname,deptid) values('胡博维','hubowei',10001)

 

select * from emp;  
select * from emp_v;    

select * from dept;  
select * from dept_v;    

 

 

  

 

 

Select user_name,sql_text from V$open_cursor where user_name='weidu';