oracle 第20章序列号和同义词

2015-10-24

1.创建序列号 CREATE SEQUENCE

2.修改序列号 ALTER SEQUENCE

3.删除序列号 DROP SEQUENCE

4.创建同义词 CREATE SYNONYM

5.修改同义词 ALTER SYNONYM

6.删除同义词 DROP SYNONYM

1.创建序列号 CREATE SEQUENCE

【语法】 CREATE SEQUENCE 官方文档

create_sequence::=

CREATE SEQUENCE [ schema. ] sequence
  [ { INCREMENT BY | START WITH } integer
  | { MAXVALUE integer | NOMAXVALUE }
  | { MINVALUE integer | NOMINVALUE }
  | { CYCLE | NOCYCLE }
  | { CACHE integer | NOCACHE }
  | { ORDER | NOORDER }
  | { KEEP | NOKEEP }
  | { SESSION | GLOBAL }
  ]...
;

View Code

2.修改序列号 ALTER SEQUENCE

【语法】 ALTER SEQUENCE 官方文档

alter_sequence::=

ALTER SEQUENCE [ schema. ] sequence
  { INCREMENT BY integer
  | { MAXVALUE integer | NOMAXVALUE }
  | { MINVALUE integer | NOMINVALUE }
  | { CYCLE | NOCYCLE }
  | { CACHE integer | NOCACHE }
  | { ORDER | NOORDER }
  | { KEEP | NOKEEP }
  | { SESSION | GLOBAL }
  } ...
;

View Code

3.删除序列号 DROP SEQUENCE

【语法】 DROP SEQUENCE 官方文档

drop_sequence::=

DROP SEQUENCE [ schema. ] sequence_name ;

View Code

4.创建同义词 CREATE SYNONYM

【语法】 CREATE SYNONYM 官方文档

create_synonym::=

CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
   [ PUBLIC ] SYNONYM
   [ schema. ] synonym
   FOR [ schema. ] object [ @ dblink ] ;

View Code

5.修改同义词 ALTER SYNONYM

【语法】ALTER SYSNONYM 官方文档

alter_synonym::=

ALTER [ PUBLIC ] SYNONYM [ schema. ] synonym
  { EDITIONABLE | NONEDITIONABLE | COMPILE } ;

View Code

6.删除同义词 DROP SYNONYM

【语法】 DROP SYNONYM 官方文档

drop_synonym::=

DROP [PUBLIC] SYNONYM [ schema. ] synonym [FORCE] ;

View Code

【例子】

#创建序列号
SQL> create sequence emp_seq start with 1000 increment by 1 nomaxvalue nocycle;
#使用数据字典user_sequences查看序列号信息
SQL> col cycle_flag for a10;
SQL> select sequence_name,min_value,increment_by,cycle_flag from user_sequences where 

sequence_name like 'EMP%';
#创建表employees
SQL> create table employees(employee_id number(6) not null,emp_name varchar2(20),email varchar2(25) not null,phone_number varchar2(20),hiredate date not null);
#向表employees插入两行数据
SQL> insert into employees values (emp_seq.nextval,'tom','susu@yahoo.com',13988383756,sysdate);
SQL> insert into employees values (emp_seq.nextval,'larry','larry@yahoo.com',139832182756,sysdate);
#查看员工信息
SQL> select * from employees;
#使用currval伪列查询当前序列号的值
SQL> select eum_seq.currval,emp_seq.nextval from dual;

#查看数据字典user_sequences
SQL> desc user_sequences;
#查询当前序列号emp_seq
SQL> select cache_size,increment_by,cycle_flag from user_sequences where sequence_name like 'EMP%';
#修改序列号emp_seq
SQL> alter sequence emp_seq increment by 2 cache 30;
#查看数据字典user_sequences
SQL> select cache_size,increment_by,cycle_flag from user_sequences where sequence_name like 'EMP%';
#删除序列号
SQL> drop sequence emp_seq;

#创建公有同义词
SQL> create public synonym department for scott.dept;
#使用公有同义词查询数据
SQL> select * from department;

#创建私有同义词
SQL> create synonym d for scott.dept;
SQL> select * from d;

#删除公有同义词department
SQL> drop public synonym department;
#删除私有同义词d
SQL> drop synonym d;

#切换用户模式
SQL> conn system/oracle
SQL> alter session set current_schema = scott;