序列、视图

-- 十一、序列
/* 格式：
create sequence 序列名 start with 起点 increment by 步长;
例如：
create sequence seq1 start with 2017001 increment by 1;

currval:当前值
nextval:下一个值
select seq1.nextval from dual;
select seq1.currval from dual;

drop sequence seq1;

alter sequence seq1 start with 1001 increment by 1;
(错误，不能修改start with 中的值)

注意：触发的时间只能用before,不能用after */
--创建触发器，将序列与订单中的编号关联
create or replace trigger tri_order
before insert
on t_order
for each row
begin
  select seq.nextval  into :new.order_id from dual;
end;

insert into t_order values(1,'冰箱','TOM');
insert into t_order values(5555,'电视','JACK');
insert into t_order values(1,'洗衣机','TOM');

select * from t_order;
delete from t_order;


create table job
(
   jobid number primary key,
   jobname varchar2(20)
)

insert into job values(seq1.nextval,'sales');

insert into job values(seq1.nextval,'clerk');

select * from job;

-- 十二、视图
 /* 视图是对一张表或多张表所做的查询操作， 视图内存放的不是表数据，而是查询语句，因此， 视图叫“虚拟表”，也称之为“存储的查询”
 
 创建视图格式：
 create [or replace] view 视图名[(字段列表)]
 as
 select 查询
 [with check option]--保护一些规则
 [with read only]--只读 */
 
 -- 1、将每个部门的人数做在视图中
 create or replace view v_1(deptno,emp_num)
 as
 select deptno,count(empno)  from emp
 group by deptno;
 
create or replace view v_1
 as
 select deptno,count(empno) emp_num  from emp
 group by deptno;
 
 -- 2、将每个部门的编号、名称和平均工资做成视图
 create or replace view v_2
 as
 select emp.deptno,dname,avg(sal) avgSal
from emp,dept
where emp.deptno=dept.deptno

select * from v_2;

-- 3、创建一个视图，里面只包含部门20的员工信息
create or replace view v_3
as
select * from emp
where deptno=20
with check option;
-- 1)查询操作
 select * from v_3;
-- 2)增加操作
insert into v_3(empno,ename,sal,deptno) values(1004,'TOM',3000,20);
 
insert into v_3(empno,ename,sal,deptno) values(1003,'李四',3000,10);
 
 rollback;
-- 3)修改
 update v_3 set sal=sal+200
 where deptno=10