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