其它数据库对象的相关介绍
2023-09-14 09:13:29 时间
什么是序列？
- 自动生成的唯一序列号
- 是可分享的对象
- 通常用来创建主键值
- 替代应用程序代码
- 当缓冲在内存中，加速访问序列的效率
SQL> create sequence seq_empno start with 7935 increment by 1 cache 50;
Sequence created.
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
SEQ_EMPNO 1 1.0000E+28 1 N N 50 7935 N N N N N
SQL>
SQL>
SQL> select SEQ_EMPNO.nextval from dual;
NEXTVAL
----------
7935
SQL> select SEQ_EMPNO.currval from dual;
CURRVAL
----------
7935
SQL>
SQL> insert into emp (empno) values (SEQ_EMPNO.nextval);
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7935
7936
7937
7938
7939
7940
7941
7942
7943
7944
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 02-APR-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1 tom 700
25 rows selected.
SQL> insert into emp (empno) values (SEQ_EMPNO.nextval);
1 row created.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7935
7936
7937
7938
7939
7940
7941
7942
7943
7944
7945
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 02-APR-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1 tom 700
26 rows selected.
SQL>
SQL>
SQL> select * from user_sequence;
select * from user_sequence
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
SEQ_EMPNO 1 1.0000E+28 1 N N 50 7985 N N N N N
SQL> 定义一个序列，自动生成连续的数字
CREATE SEQUENCE sequence
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
SEQ_EMPNO 1 1.0000E+28 1 N N 50 7985 N N N N N
SQL> alter sequence SEQ_EMPNO minvalue 7946;
alter sequence SEQ_EMPNO minvalue 7946
*
ERROR at line 1:
ORA-04007: MINVALUE cannot be made to exceed the current value
SQL> alter sequence SEQ_EMPNO minvalue 7950;
alter sequence SEQ_EMPNO minvalue 7950
*
ERROR at line 1:
ORA-04007: MINVALUE cannot be made to exceed the current value
SQL> alter sequence SEQ_EMPNO minvalue 7945;
Sequence altered.
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
SEQ_EMPNO 7945 1.0000E+28 1 N N 50 7946 N N N N N
SQL> alter sequence SEQ_EMPNO maxvalue 9999;
Sequence altered.
SQL> alter sequence SEQ_EMPNO INCREMENT BY 5;
Sequence altered.
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
SEQ_EMPNO 7945 9999 5 N N 50 7950 N N N N N
SQL> alter sequence SEQ_EMPNO cycle;
Sequence altered.
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
SEQ_EMPNO 7945 9999 5 Y N 50 7950 N N N N N
SQL> alter sequence SEQ_EMPNO cache 100;
Sequence altered.
SQL> select * from user_sequences;\
2
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ------------ - - ---------- ----------- - - - - -
SEQ_EMPNO 7945 9999 5 Y N 100 7950 N N N N N
SQL> alter sequence SEQ_EMPNO start with 7947;
alter sequence SEQ_EMPNO start with 7947
*
ERROR at line 1:
ORA-02283: cannot alter starting sequence number
SQL> 索引是什么？
- 是一个schema对象
- 被Oracle服务器使用通过具有指示的行来加速检索
- 通过快速路径访问访问定位数据可以减少磁盘I/O
- 被Oracle服务器自动使用和维护。
相关文章
- 【应用 业务】做数据库技术支持的一点感悟
- 数据库基础的知识点
- 状态对象：数据库的替代者
- MySQL数据库设置远程访问权限方法小结
- JDBC:数据库操作：处理大对象CLOB数据
- 单实例数据库安装脚本
- 创建基于文件组的数据库SQL救命语句
- 如何优化数据库对象
- MySQL数据库备份
- paip.提升性能----数据库连接池以及线程池以及对象池
- 关于对象数据库 DB4O 的一些BUG以及如何应对的方法
- vs2013 ADO联系SQL server2012数据库
- PostgreSQL的学习心得和知识总结（五十八）|深入理解PostgreSQL数据库 数据库对象权限 赋予及回收机制
- ORMBase对象/关系型数据库映射在MVC中的应用
- MySQL数据库(三)-表行的语句使用