文档章节

ORACLE SQL总结四:视图、序列、索引和同义词

yeyelei
 yeyelei
发布于 2012/07/17 16:38
字数 1266
阅读 217
收藏 1

1、序列

1.1 序列的定义和作用

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value

1.2 在哪里使用序列和哪里不能使用 Where to Use Sequence Values

You can use CURRVAL and NEXTVAL in the following locations:

  • The select list of a SELECT statement that is not contained in a subquery, materialized view, or view

  • The select list of a subquery in an INSERT statement

  • The VALUES clause of an INSERT statement

  • The SET clause of an UPDATE statement

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the following constructs:

  • A subquery in a DELETE, SELECT, or UPDATE statement

  • A query of a view or of a materialized view

  • A SELECT statement with the DISTINCT operator

  • A SELECT statement with a GROUP BY clause or ORDER BY clause

  • A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator

  • The WHERE clause of a SELECT statement

  • The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

  • The condition of a CHECK constraint

Within a single SQL statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.

1.3 什么时候序列增长 Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once:

  • For each row returned by the outer query block of a SELECT statement. Such a query block can appear in the following places:

    • A top-level SELECT statement

    • An INSERT ... SELECT statement (either single-table or multitable). For a multitable insert, the reference to NEXTVAL must appear in the VALUES clause, and the sequence is updated once for each row returned by the subquery, even though NEXTVAL may be referenced in multiple branches of the multitable insert.

    • A CREATE TABLE ... AS SELECT statement

    • A CREATE MATERIALIZED VIEW ... AS SELECT statement

  • For each row updated in an UPDATE statement

  • For each INSERT statement containing a VALUES clause

  • For each INSERT ... [ALL | FIRST] statement (multitable insert). A multitable insert is considered a single SQL statement. Therefore, a reference to the NEXTVAL of a sequence will increase the sequence only once for each input record coming from the SELECT portion of the statement. If NEXTVAL is specified more than once in any part of the INSERT ... [ALL |FIRST ] statement, then the value will be the same for all insert branches, regardless of how often a given record might be inserted.

  • For each row merged by a MERGE statement. The reference to NEXTVAL can appear in the merge_insert_clause or the merge_update_clause or both. The NEXTVALUE value is incremented for each row updated and for each row inserted, even if the sequence number is not actually used in the update or insert operation. If NEXTVAL is specified more than once in any of these locations, then the sequence is incremented once for each row and returns the same value for all occurrences of NEXTVAL for that row.

  • For each input row in a multitable INSERT ALL statement. NEXTVAL is incremented once for each row returned by the subquery, regardless of how many occurrences of theinsert_into_clause map to each row.

1.4 序列的一些特殊情况

If any of these locations contains more than one reference to NEXTVAL, then Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL.

If any of these locations contains references to both CURRVAL and NEXTVAL, then Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL.

2、视图 view

2.1 视图的一些特殊情况

Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.

3、约束状态 Constraint State

3.1 DEFERRABLE

DEFERRABLE Clause The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

  • Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The checking of a NOT DEFERRABLE constraint can never be deferred to the end of the transaction.

    If you declare a new constraint NOT DEFERRABLE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

  • Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until a COMMIT statement is submitted. If the constraint check fails, then the database returns an error and the transaction is not committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

You cannot alter the deferrability of a constraint. Whether you specify either of these parameters, or make the constraint NOT DEFERRABLE implicitly by specifying neither of them, you cannot specify this clause in an ALTER TABLE statement. You must drop the constraint and re-create it.

指定为DEFERRABLE才能使用INITIALLY clause,它指定CREATE 或ALTER 语句是否执行检查

INITIALLY Clause The INITIALLY clause establishes the default checking behavior for constraints that are DEFERRABLE. The INITIALLY setting can be overridden by a SET CONSTRAINT(S) statement in a subsequent transaction.

  • Specify INITIALLY IMMEDIATE to indicate that Oracle should check this constraint at the end of each subsequent SQL statement. If you do not specify INITIALLY at all, then the default is INITIALLY IMMEDIATE.

    If you declare a new constraint INITIALLY IMMEDIATE, then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

  • Specify INITIALLY DEFERRED to indicate that Oracle should check this constraint at the end of subsequent transactions.

This clause is not valid if you have declared the constraint to be NOT DEFERRABLE, because a NOT DEFERRABLE constraint is automatically INITIALLY IMMEDIATE and cannot ever be INITIALLYDEFERRED.

© 著作权归作者所有

yeyelei

yeyelei

粉丝 18
博文 68
码字总数 97956
作品 0
武汉
私信 提问
oracle系列(四)oracle事务和常用数据库对象

博主QQ:819594300 博客地址:http://zpf666.blog.51cto.com/ 有什么疑问的朋友可以联系博主,博主会帮你们解答,谢谢支持! 前言:本次内容的相关知识点我们在学习sqlserver2008R2的时候介绍...

Mr大表哥
2017/05/27
0
0
Oralce专题10之Oracle的其他数据库对象

1、Oracle的数据库对象:序列 a、什么是序列? 序列(sequence):是一种用于自动生成唯一数字的数据库对象。主要用于提供主键值。 b、怎样创建序列? 创建序列的语法格式: 示例: c、怎样使...

极客微信条
2017/12/08
0
0
Oracle的对象——表,同义词,序列,视图,索引和簇

一:表 a.创建表语法格式 create table table_name ( 字段1 类型1, 字段2 类型2, 字段3 类型3(也可以给字段设置默认值) ... ); Sql代码 create table student ( stuno int, stuname varcha...

mrliuze
2015/11/26
58
0
Oracle笔记 目录索引

Oracle笔记 一、oracle的安装、sqlplus的使用 Oracle笔记 二、常用dba命令行 Oracle笔记 三、function 、selectOracle笔记 四、增删改、事务 Oracle笔记 五、创建表、约束、视图、索引、序列...

ibm_hoojo
2011/05/03
0
0
Oracle事务和常用数据库对象

1、事务:有一组命令组成的逻辑单元,保证所有操作要么全部成功要么全部失败。 以下情况事务结束:1)显式提交(commit) 2)显式回滚(rollback) 3)ddl语句隐身提交 4)正常结束程序提交 ...

我要学学学
2018/04/19
0
0

没有更多内容

加载失败,请刷新页面

加载更多

MongoDB系列-在复制集(replication)以及分片(Shard)中创建索引

关注我,可以获取最新知识、经典面试题以及微服务技术分享   在使用MongoDB时,在创建索引会涉及到在复制集(replication)以及分片(Shard)中创建,为了最大限度地减少构建索引的影响,在副本...

ccww_
22分钟前
17
0
SAP HANA数据库multi container模式JDBC链接connection refused

报错如下信息 com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: Cannot connect to jdbc:sap://xxx.xxx.xxx.xxx:30015 [Cannot connect to host xxx.xxx.xxx.xxx:30015 [C......

flash胜龙
47分钟前
33
0
c++ 虚基类

c++ 虚基类 p556

天王盖地虎626
53分钟前
86
0
k8s删除Terminating状态的命名空间

背景: 我们都知道在k8s中namespace有两种常见的状态,即Active和Terminating状态,其中后者一般会比较少见,只有当对应的命名空间下还存在运行的资源,但是该命名空间被删除时才会出现所谓的...

Andy-xu
今天
81
0
seata源码阅读笔记

seata源码阅读笔记 本文没有seata的使用方法,怎么使用seata可以参考官方示例,详细的很。 本文基于v0.8.0版本,本文没贴代码。 seata中的三个重要部分: TC:事务协调器,维护全局事务和分支...

东都大狼狗
今天
44
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部