Chapter 15 序列号(Sequence)和同义词(Synonym)

Chapter 15 序列号(Sequence)和同义词(Synonym)

SQL> desc supplier;
Name    Type         Nullable Default Comments
------- ------------ -------- ------- --------
S_CODE  NUMBER(6)    Y                         
SNAME   VARCHAR2(25) Y                         
CONTACT VARCHAR2(15) Y                         
PHONE   VARCHAR2(15) Y                         
FAX     VARCHAR2(15) Y                         

SQL> select * from supplier;

 S_CODE SNAME                     CONTACT         PHONE           FAX
------- ------------------------- --------------- --------------- ---------------

create sequence supplier_s_code
start with 2000
increment by 10
maxvalue 100000
nocache
nocycle;

select sequence_name, min_value, max_value,
       increment_by, last_number
from user_sequences
where sequence_name like upper('supplier_s_code');

select object_name, object_type, created, last_ddl_time, status
from user_objects
where object_type != 'TABLE'
and object_type != 'INDEX';

select supplier_s_code.currval from dual;

ORACLE 规定在引用CURRVAL之前，必须在当前的会话中使用NEXTVAL产生一个序列号

insert into supplier(s_code,sname,contact,phone,fax)
values
(supplier_s_code.nextval,'仙来客栈','真正',4444944,44444);

commit;

select supplier_s_code.currval from dual

select sequence_name, min_value, max_value,
       increment_by, last_number, cache_size
from user_sequences;

alter sequence supplier_s_code
increment by 11
maxvalue 999999
cache 50;

drop sequence supplier_s_code;


//同义词
create synonym s
for supplier;

select * from s;

select object_name, object_type, created, status
from user_objects
where object_type like 'SYN%';

select synonym_name, table_owner, table_name
from user_synonyms
where synonym_name like 'S';

create public synonym ss
for supplier;

select * from ss;

drop synonym s;