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';

?

?