序列和视图

--分页语句rownum
select * from (select d.*,rownum rn from ( select * from emp order by sal desc) d ) where rn>5 and rn<=10 ;

--序列
--创建序列
create sequence seq_class start with 1 increment by 2 maxvalue 5000 cache 30;

--修改序列
alter sequence seq_class cache 20;

--删除序列
drop sequence seq_class;
--查询当前用户下的序列信息
select * from user_sequences;

select seq_class.nextval from dual;
--序列建成功后，第一次不能用currval获取当前值，必须使用一次nextval，才可以查询当前值
select seq_class.currval from dual;

 

 

--为用户授予创建视图的权限
grant create view to scott;
revoke create view from scott;

--视图
--创建视图 不带with check option 也不带where 条件 数据可以通过视图和源表添加
create or replace view emp_view as
select empno,ename,job from emp;

select empno,ename from emp_view; --操作视图

select * from emp;
delete from emp_view where empno=6666;
insert into emp_view values(1111,'lily','clerk');--通过视图添加数据，视图和源表都能添加

--创建视图 带 with check option 条件， 没带where条件 ，也可以添加
create or replace view emp_view2 as

select empno,ename,job from emp with check option;

select * from emp_view2;

insert into emp_view2 values(1111,'lily','clerk'); --也可以添加

select * from emp;
delete from emp where empno=1111;
--创建视图 带where条件 带 with check option
create or replace view emp_view3 as
select empno ,ename ,job,sal from emp where sal >1000 and sal<2000 with check option;

select * from emp_view3;

insert into emp_view3 values(1111,'lily','clerk',2500);--不满足where条件，不能添加
insert into emp_view3 values(2222,'lucy','clerk',1500); --满足where条件是可以添加的

--创建视图 带where 条件 不带with check option

create or replace view emp_view4 as
select empno,ename ,job ,sal from emp where sal>1000 and sal<2000;

insert into emp_view4 values(1111,'lily','clerk',2500);

select * from emp_view4;
select * from emp;
delete from emp where empno=1111;

--创建视图 with read only 只读操作
create or replace view emp_view5 as
select empno,ename,job from emp with read only;

select * from emp_view5;

insert into emp_view5 values(3333,'偶发清','搬砖');--不能对只读视图 做DML操作 也就是增删改

转载于:https://www.cnblogs.com/yin-dt/p/6736500.html