Oracle序列的问题
(1)
SQL> connect scott/tiger
已连接。
SQL> select * from v$version;BANNER
-----------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - ProductionSQL> select * from tab; SQL> select * from user_tables;和SQL> select * from all_tables;有什么区别和联系?(2)
SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL> create sequence sq_dept
increment by 10
start with 50
maxvalue 90
nocache
nocycle ;序列已创建。SQL> select * from user_sequences;SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
SQ_DEPT 1 90 10 N N 0 50SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 TRAINING SHANGHAISQL> select sq_dept.currval from dual; CURRVAL
----------
50SQL> select sq_dept.nextval from dual; NEXTVAL
----------
60SQL> insert into dept values(sq_dept.nextval,'SUPPORTING','SHANGHAI');已创建 1 行。SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 TRAINING SHANGHAI
70 SUPPORTING SHANGHAI已选择6行。为什么是“70 SUPPORTING SHANGHAI”,应该是“60 SUPPORTING SHANGHAI”?
(1)
SQL> connect scott/tiger
已连接。
SQL> select * from v$version;BANNER
-----------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - ProductionSQL> select * from tab; SQL> select * from user_tables;和SQL> select * from all_tables;有什么区别和联系?(2)
SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONSQL> create sequence sq_dept
increment by 10
start with 50
maxvalue 90
nocache
nocycle ;序列已创建。SQL> select * from user_sequences;SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
SQ_DEPT 1 90 10 N N 0 50SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 TRAINING SHANGHAISQL> select sq_dept.currval from dual; CURRVAL
----------
50SQL> select sq_dept.nextval from dual; NEXTVAL
----------
60SQL> insert into dept values(sq_dept.nextval,'SUPPORTING','SHANGHAI');已创建 1 行。SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 TRAINING SHANGHAI
70 SUPPORTING SHANGHAI已选择6行。为什么是“70 SUPPORTING SHANGHAI”,应该是“60 SUPPORTING SHANGHAI”?
SQL> select sq_dept.nextval from dual; NEXTVAL
----------
60SQL> insert into dept values(sq_dept.nextval,'SUPPORTING','SHANGHAI');已创建 1 行。你的60已经用过一次,
NEXTVAL
----------
60
所以再insert 就变成70了select * from tab; SQL> select * from user_tables;和SQL> select * from all_tables;有什么区别和联系?select * from tab: 查询指定表的信息
select * from user_tables: User_tables是一个View,里面包括所有用户表的表名,表空间。
select * from user_tables: 是所有表,包括用户表,系统表等等。