oracle序列同义词

创建序列books_seq 初始值为10 递步增长1最大值为100000
CREATE SEQUENCE books_seq
START WITH 10
INCREMENT BY 1
MAXVALUE 100000
CACHE 10
NOCYCLE;


创建序列books_seq2 初始值为100000 递步增长-5
CREATE SEQUENCE books_seq2
START WITH 100000
INCREMENT BY -5
MINVALUE 1
MAXVALUE 100000
NOCYCLE
CACHE 5;


使用序列
SELECT books_seq.nextval FROM dual;
SELECT books_seq.currval FROM dual;


create table books(bookid number(6) primary key,bookname VARCHAR(50));


insert into books values(books_seq.currval,'数据库原理');


insert into books values(books_seq.nextval,'c语言');
insert into books values(books_seq.nextval,'e语言');


select * from books;


修改序列
ALTER SEQUENCE books_seql INCREMENT BY 10;
ALTER SEQUENCE books_seql START WITH 100 INCREMENT BY 10; //报错  起始值不可以修改
删除序列
DROP SEQUENCE book_seql;


创建序列XL1 初始值为10 
CREATE SEQUENCE XL1
START WITH 10


select xl1.nextcal from dual; //查询序列下一个值
select xl1.currcal from dual;//查询序列当前值


create table t1(no number(6) primary key);//创建一个表 表中有一个字段
insert into t1 values(xl1.currval);
insert into t1 values(xl1.currval);//执行两次会报错 no 为主键值
insert into t1 values(xl1.nextval);


ALTER SEQUENCE XL1 INCREMENT BY 5;  //修改序列值步长为5   后插入一条数据查询验证
select * from t1;
DROP SEQUENCE XL1;


同义词


CREATE PUBLIC SYNONYM pubbooks FOR books_pub.books;  //创建同义词
select * from pubbooks; //scott用户未授权时下查询失败
//
alter user scott identified by 123 account unlock;
//
GRANT SELECT ON BOOKS TO scott;
connect scott/123
select * from pubbooks; //查询成功
DROP PUBLIC SYNONYM pb;




alter user scott identified by 123 account unlock;
connect scott/123
select * from books;




练习 
connect sys/12345678 as sysdba;
set sqlprompt "_USER > " //设置默认命令提示符为当前用户
CREATE SEQUENCE books_seq START WITH 1;
create table books(bookid number(6) primary key,bookname VARCHAR(50));
insert into books values(books_seq.currval,'数据库原理');
insert into books values(books_seq.nextval,'c语言');
select * from books;
CREATE PUBLIC SYNONYM sys_b FOR books_pub.books;  //创建同义词
select * from sys_b;
alter user scott identified by 123 account unlock;
GRANT SELECT ON BOOKS TO scott;
connect scott/123
select * from books;//失败
select * from sys_b;//成功