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;
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;