ORACLE 约束&&视图

什么是约束?
:: 约束强制规则在表级
:: 如果有从属关系，约束防止表的删除
:: 下面的约束类型是有效的：
–NOT NULL
–UNIQUE
–PRIMARY KEY
–FOREIGN KEY
–CHECK


约束
    Oracle服务器用约束 (constraints) 来防止无效数据输入到表中。

你可以使用约束做下面的事：
:: 在插入、更新行或者从表中删除行的时候强制表中的数据遵循规则。对于成功的操作，约束必须被满足
:: 如果表之间有依赖关系，防止表的删除


数据一致性约束

约束 说明
NOT NULL 指定列不能包含空值
UNIQUE 指定列的值或者列的组合的值对于表中所有的行必须是唯一的
PRIMARY KEY 表的每行的唯一性标识
FOREIGN KEY 在列和引用表的一个列之间建立并且强制一个外键关系
CHECK 指定一个必须为真的条件



约束原则
:: 命名一个约束，或者由Oracle 用SYS_Cn格式产生一个名字
:: 创建一个约束：
–在创建表的同时，或者
–在创建表之后
:: 在列或者表级定义一个约束
:: 在数据字典中查看约束


约束原则
所有的约束存储在数据字典中。如果给约束一个有意义的名字，约束易于引用，约束命名必须遵守标准的对象命名规则。如果你不命名你的约束，Oracle服务器将用格式 SYS_Cn产生一个名字，这里 n 是一个唯一的整数，所以约束名是唯一的。

你可以用USER_CONSTRAINTS数据字典表查看对一个表的约束的定义。
constraint_name,constraint_type列



定义约束

CREATE TABLE [schema.] table
(column datatype [DEFAULT expr]
[column_constraint],
...
[table_constraint][,...]);

column_constraint 是一个作为列定义一部分的完整性约束
table_constraint  是一个作为表定义一部分的完整性约束


CREATE TABLE employees (
employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL, //表级约束.
CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));



定义约束

:: 列级约束
   column[CONSTRAINT constraint_name] constraint_type,
:: 表级约束
   column,...
[CONSTRAINT constraint_name] constraint_type(column, ...),

   约束通常在创建表的同时被创建。在表创建后约束能够被添加，并且约束可以可以被临时禁用。


约束可以在两个级别上定义。

约束级别    说明
列    只涉及一个单个的列，对于该列用规范定义；能够定义完整性约束的任何类型
表    涉及一个或多个列，表中的列被分别定义；除了 NOT NULL，能够定义任意约束


NOT NULL约束
确保某些列不允许空值

NOT NULL约束确保列不包含空值。在默认情况下，列没有NOT NULL约束，可以包含空值。


:: 只能定义在列级上,不能在表级.

CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,    <----由系统指定约束名字
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,   <---用户指定了约束名字
...



UNIQUE约束

UNIQUE约束
UNIQUE键完整性约束，要求列或者列的组合中 (键) 的每个值是唯一的，既，在表中指定的列或列组合中不能有两行有相同的值。定义UNIQUE键约束的列 (或列组合) 被称为唯一键 (unique key)。

除非你对相同的列也定义了NOT NULL约束，UNIQUE约束允许输入空值，事实上，对于无NOT NULL约束的列，能包含空值的行可以是任意数目，因为空不等于任何事。在一个列 (或者在一个复合UNIQUE键中的所有列) 中的空值总是满足UNIQUE约束。

注：因为在多于一列上的UNIQUE约束的搜索机制原因，在一个部分为空的组合UNIQUE键约束的非空列中你不能有相同的值。

值在第一次插入后已经存在,不允许第二次输入.

(续)
既可以定义在表级也可以定义在列级,使用表级定义时一个复合唯一键被创建。

CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));

    Oracle服务器在唯一键列或组合列上隐式地创建一个唯一索引强制UNIQUE约束。



PRIMARY KEY 约束


PRIMARY KEY约束
        PRIMARY KEY约束为表创建一个主键。每个表只能创建一个主键。PRIMARY KEY约束是表中的对行唯一标识的一个列或者列组合，该约束强制列或列组合的唯一性，并且确保作为主键一部分的列不能包含空值。

表级定义; ././././.
CREATE TABLE departments(
department_id NUMBER(4),
department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL, //列级
manager_id NUMBER(6),
location_id NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id));

一个表只能有一个子PRIMARY KEY 约束,但可以有多个UNIQUE 约束.

对于一个PRIMARY KEY列, UNIQUE索引被自动创建.




FOREIGN KEY 约束

FOREIGN KEY约束
FOREIGN KEY，引用完整性约束，指明一个列或者列的组合作为一个外键，并且在相同表或者不同表的主键或者唯一键和外键之间建立一个关系。

一个外键值必须匹配一个在父表中存在的值或者空值。/./././././

外键基于数据值，并且纯粹是逻辑的，不是物理的，指针。


(续)
定义在表级.
CREATE TABLE employees (
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email)
);

定义在列级上: //FOREIGN KEY不出现.
CREATE TABLE employees
(...
department_id NUMBER(4) CONSTRAINT emp_deptid_fk
REFERENCES departments(department_id),
...
);


FOREIGN KEY约束关键字

:: FOREIGN KEY: 在表约束级别，定义在子表的列中
:: REFERENCES: 标识表和父表中列
:: ON DELETE CASCADE: 当父表中的行被删除时，删除子表中相依赖的行
:: ON DELETE SET NULL: 转换相依赖的外键为空

(续)

外键被定义在子表中，包含引用列的表是父表。外键(FOREIGN KEY)用下面关键字的组合定义：
:: FOREIGN KEY 被用于在表约束级定义子表中的列。
:: REFERENCES 确定父表中的表和列。
:: ON DELETE CASCADE 指出当父表中的行被删除时，子表中相依赖的行也将被级联删除。
:: ON DELETE SET NULL 当父表的值被删除时，转换外键值为空。

    默认行为被称为约束规则，该规则不允许引用数据的更新或删除。

   无ON DELETE CASCADE或ON DELETE SET NULL选项，如果父表中的行在子表中引用，
   则它不能被删除。



CHECK约束
:: 定义每行必须满足的条件
:: 下面的表达式不被允许使用：
–涉及到CURRVAL, NEXTVAL, LEVEL和ROWNUM伪列
–调用SYSDATE, UID, USER和USERENV函数
–涉及其它行中其它值的查询

..., salary NUMBER(2)
CONSTRAINT emp_salary_min CHECK (salary > 0),...


CHECK 约束

CHECK约束定义一个每行都必须满足的条件，该条件可以用和查询条件一样的结构，下面的情况例外：
:: 引用CURRVAL, NEXTVAL, LEVEL和ROWNUM伪列
:: 调用SYSDATE, UID, USER和USERENV函数
:: 查询涉及其它行中的其它值

  一个单个列在它的定义中可以有多个CHECK约束，在一个列上能够定义的CHECK 约束的数目无限制。

CHECK约束能够被定义在列级或表级。

//列级定义;
CREATE TABLE employees
(...
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),
...

      伪列不是表中实际的列，但它们的行为象列一样，例如，你能从伪列中选择够选择值，可是，你不能插入、更新或从伪列中删除。伪列能被用于SQL语句中。



添加约束语法 ././././././

用ALTER TABLE语句：
:: 添加或删除约束，但不修改它的结构
:: 启用或禁用约束
:: 用MODIFY子句添加一个NOT NULL约束

ALTER TABLE table ADD [CONSTRAINT constraint] type (column);

//
alter table table add (id varchar(10)); //添加列


添加约束
    你可以用带ADD子句的ALTER TABLE语句为已经存在的表添加一个约束。

在语法中：
table 是表的名字
constraint 是约束的名字
type 是约束的类型
column 是受约束影响的列的名字

   尽管建议命名约束，但约束名在语法中是个选项。如果你不命名约束，系统将产生约束名。

原则 ././././.
  :: 你可以添加、删除或禁用一个约束，但你不能修改它的结构。
  :: 你可以用ALTER TABLE语句的MODIFY子句添加一个NOT NULL约束到一个已经存在的列。

././././.
注：只有在表是空的或者每个行的该列都有非空值的情况下，你才可以定义一个NOT NULL列。


     你可以延迟检查约束的有效，直到事务结束。

     如果仅在提交(COMMIt)的时候进行系统检查，约束将被延迟 (deferred)。如果一个延迟的约束被违反，则该提交导致事务回退。(事务回退)

     如果在每条语句结束时进行检查，约束是立即的 (immediate)。如果该约束被违反，则该语句被立即回退。(语句回退)



添加约束

  添加一个FOREIGN KEY约束到EMPLOYEES表，注意:经理必须已经是EMPLOYEES表中的.

ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
Table altered.


  用ALTER TABLE MODIFY语法添加一个NOT NULL约束：

ALTER TABLE employees
MODIFY (salary CONSTRAINT emp_salary_nn NOT NULL);




删除约束

:: 从EMPLOYEES表中删除经理约束.

ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;
Table altered.


/./././././.删除主键的同时,把相关联的外表的约束也删除
::删除DEPARTMENTS表上的PRIMARY KEY约束，并且删除相关联的在EMPLOYEES.DEPARTMENT_ID列上的FOREIGN KEY约束

alter table departments
drop primary key cascade;
Table altered.

删除约束 ./.././././.
    为了删除约束，你可以先从USER_CONSTRAINTS和USER_CONS_COLUMNS数据字典视图中确定约束的名字，然后使用带DROP子句的ALTER TABLE语句。DROP子句的CASCADE选项导致任何与其相依赖的约束也被删除。

语法:
  ALTER TABLE table
  DROP PRIMARY KEY | UNIQUE (column) |
  CONSTRAINT constraint [CASCADE];

//删除列
alter table table drop (id);

在语法中：
table 是表的名字
column 是受约束影响的列的名字
constraint 是约束的名字

  当你删除一个完整性约束时，约束不再由Oracle服务器强制，并且在数据字典中不再可用。



禁用约束
:: 执行ALTER TABLE语句的DISABLE子句来禁用完整性约束
:: 应用CASCADE选项禁用相依赖的完整性约束

ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
Table altered.


禁用约束
    你可以禁用一个约束而不删除它，或者用带DISABLE子句的ALTER TABLE语句重新创建它。

语法
  ALTER TABLE table
  DISABLE CONSTRAINT constraint [CASCADE];

在语法中：
table 是表的名字
constraint 是约束的名字

原则
:: 你即可以在CREATE TABLE语句也可以在ALTER TABLE语句中使用 DISABLE子句。
:: CASCADE子句禁用相依赖的完整性约束。
:: 禁用唯一或主键约束会移除唯一性索引。


启用约束
:: 用ENABLE字句启用一个在表中定义的当前禁用的完整性约束

ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk;
Table altered.

:: 如果启用一个UNIQUE键或PRIMARY KEY约束,一个UNIQUE或PRIMARY KEY索引被自动创建


启用约束
   你可以用带ENABLE子句的ALTER TABLE语句启用一个禁用的约束，而不需要重新创建它。

语法
  ALTER TABLE table
  ENABLE CONSTRAINT constraint;

在语法中：
table 是表的名字
constraint 是约束的名字

原则 ,.,.,.,.,.,../././././
:: 如果启用一个约束，约束将应用于表中所有的数据，(所有在表中的数据都必须适合该约束).
:: 如果你启用一个UNIQUE键或者PRIMARY KEY约束，一个UNIQUE或PRIMARY KEY索引将被自动地创建
:: 你即可以CREATE TABLE语句也可以在ALTER TABLE语句中使用ENABLE子句。
:: 启用一个带CASCADE选项的被禁用的主键约束不会起用任何依赖于该主键的外键。



级联约束 /./././

:: CASCADE CONSTRAINTS子句连同DROP COLUMN子句一起被使用
:: CASCADE CONSTRAINTS子句删除所有定义在被删除列上的涉及主键和唯一键的引用完整性约束:: CASCADE CONSTRAINTS子句也删除所有定义在被删除列上的多列约束

级联约束

  该语句举例说明CASCADE CONSTRAINTS子句的用法。假设表TEST1被如下创建：

CREATE TABLE test1 (
pk NUMBER PRIMARY KEY,
fk NUMBER,
col1 NUMBER,
col2 NUMBER,
CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1,  //也对,(感觉要回上(pk))
CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
CONSTRAINT ck2 CHECK (col2 > 0));

对于下面的语句返回一个错误：
ALTER TABLE test1 DROP (pk); -- pk是父键
ERROR 位于第 1 行:
ORA-12992: 无法删除父项关键字列

ALTER TABLE test1 DROP (col1); -- col1被多列约束ck1引用
ERROR 位于第 1 行:
ORA-12991: 引用的列处于多列约束条件




级联约束

例子: //././././././
ALTER TABLE test1 DROP (pk) CASCADE CONSTRAINTS;
Table altered.
提交这条语句删除列PK，主键约束，fk_constraint外键约束和检查约束 CK1：


ALTER TABLE test1 DROP (pk, fk, col1) CASCADE CONSTRAINTS;
Table altered.

    如果所有由定义在已删除列上的约束引用的列也被删除，那么就不需要CASCADE CONSTRAINTS，例如，假设没有其它表引用PK，提交下面的不带CASCADE CONSTRAINTS子句的语句是恰当的：
ALTER TABLE test1 DROP (pk, fk, col1);

     如果任何约束被来自其它表中的列或在目的表中的保留列所引用(既约束的列被引用)，那么必须指定CASCADE CONSTRAINTS，否则，该语句异常中断，并且返回错误提示
ORA-12991：在多列约束中的列被引用。




查看约束
  查询USER_CONSTRAINTS表来查看所有约束定义和命名

SELECT constraint_name,
constraint_type,search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

SQL> set linesize 120
SQL> SELECT     constraint_name,
  2     constraint_type,search_condition
  3  FROM       user_constraints
  4  WHERE      table_name = 'EMPLOYEES';

CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - ------------------------------
EMP_LAST_NAME_NN               C "LAST_NAME" IS NOT NULL
EMP_EMAIL_NN                   C "EMAIL" IS NOT NULL
EMP_HIRE_DATE_NN               C "HIRE_DATE" IS NOT NULL
EMP_JOB_NN                     C "JOB_ID" IS NOT NULL
EMP_SALARY_MIN                 C salary > 0
EMP_EMAIL_UK                   U
EMP_EMP_ID_PK                  P
EMP_DEPT_FK                    R
EMP_JOB_FK                     R
EMP_MANAGER_FK                 R

已选择10行。

查看约束
    在创建表之后，你可以用DESCRIBE命令来确认它的存在,你唯一能够校验的约束是NOT NULL约束。为了查看表上所有的约束，查询USER_CONSTRAINTS表。

././././././.
注：那些没有被表的所有者命名的约束将收到系统指定的约束名。在约束类型中，C代表CHECK，P代表PRIMARY KEY，R代表引用完整性(referential integrity)， U代表UNIQUE键。注意NOT NULL约束实际上是一个CHECK约束。即NOT NULL约束在constraint_type域输入C(表示CHECK)



查看约束关联的列
观察在USER_CONS_COLUMNS视图中与约束名关联的列
SELECT  constraint_name, column_name
FROM    user_cons_columns
WHERE   table_name = 'EMPLOYEES';

查看约束 (续)

    你可以用USER_CONS_COLUMNS数据字典视图查看与约束相关的列名。该视图对于那些由系统指定名字的约束特别有用。././././.





*******************11 创建视图********************

数据库对象

对象 说明
Table(表) 基本存储单元;由行和列组成
View(视图) 数据来自一个或多个表的数据子集的逻辑表示
Sequence(序列) 产生主键的值
Index(索引) 改善某引起查询的性能
Synonym(同义词) 一个对象的替换名字.




  什么是视图? /././././././
你可以通过创建表的视图来表现数据的逻辑子集或数据的组合。视图是基于表或另一个视图的逻辑表，一个视图并不包含它自己的数据，它象一个窗口，通过该窗口可以查看或改变表中的数据。视图基于其上的表称为基表。
 
   视图在数据字典中作为一个SELECT语句存储。



为什么用视图?
:: 限制数据访问
:: 使得复杂的查询容易
:: 提供数据的独立性
:: 表现相同数据的不同观察

视图的优越性
:: 视图限制数据的访问，因为视图能够选择性的显示表中的列。
:: 视图可以用来构成简单的查询以取回复杂查询的结果。例如，视图能用于从多表中查询信息，而   用户不必知道怎样写连接语句。

:: 视图对特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回数据。
:: 视图提供用户组按照他们的特殊标准访问数据。



简单视图和复杂视图

特性 简单视图 复杂视图

表的数目 一个 一个或多个
包含函数 无 有
包含数据分组 无 有
通过视图进行DML 操作 是 不允许

这两个视图区别涉及DML(INSERT,UPDATE,DELETE)操作.


:: 下面是简单视图：
- 数据仅来自一个表
- 不包含函数或数据分组
- 能通过视图执行DML操作
:: 下面是复杂视图：
- 数据来自多个表
-包含函数或数据分组
- 不总是允许通过视图进行DML操作


创建视图

::在CREATE VIEW 语句中嵌入一个子查询

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

::子查询可以包含复杂的SELECT语法.


创建查询
   你可以在CREATE VIEW语句中嵌入一个子查询来创建一个视图。

在语法中：
   OR REPLACE 如果视图已经存在重新创建它
   FORCE 创建视图，而不管基表是否存在
   NOFORCE 只在基表存在的情况下创建视图(这是默认值)
   view 视图的名字
   alias 为由视图查询选择的表达式指定名字
(别名的个数必须与由视图选择的表达式的个数匹配)
   subquery 是一个完整的SELECT语句(对于在SELECT列表中的字段你可以用别名)
   WITH CHECK OPTION 指定只有可访问的行在视图中才能被插入或修改
   constraint 为CHECK OPTION约束指定的名字
   WITH READ ONLY 确保在该视图中没有DML操作被执行


(续)
:: 创建一个视图，EMPVU80，其中包含了在部门80 中雇员的详细信息

CREATE VIEW empvu80
AS  SELECT employee_id, last_name, salary
     FROM employees
     WHERE department_id = 80;
View created.View created.

:: 用iSQL*Plus DESCRIBE命令查看视图的结构
DESCRIBE empvu80

././././././.
创建视图的原则：

:: 定义一个视图的子查询可以包含复杂的SELECT语法，包括连分组和多个子查询。
:: 定义视图的子查询不能包含ORDER BY子句，当你从视图取回数据时可以指定ORDER BY子句。././
:: 如果你没有为用WITH CHECK OPTION选项创建的视图指定一个约束名字，系统将以SYS_Cn格式指定   一个默认的名字。
:: 你可以用OR REPLACE选项改变视图的定义而无须删除和重新创建它，或重新授予以前已经授予它的对象权限。

(续)
:: 用子查询中的列别名创建视图

CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
   salary*12 ANN_SALARY //必须要有别名
    FROM employees
    WHERE department_id = 50;
View created.

:: 从该视图中选择列，视图中的列使用别名命名
可以在子查询中包括列别名来控制列名。

/./././././在视图名(里面填别名)
   作为一个选择，你可以在CREATE语句后面在SELECT子查询前面用一个别名，被列出的别名的个数必须与在子查询中被选择的表达式相匹配。

CREATE VIEW salvu50 (ID_NUMBER, NAME, ANN_SALARY)
AS SELECT employee_id, last_name, salary*12
   FROM employees
   WHERE department_id = 50;
视图创建。


从视图中取回数据
从视图中取回数据
    你可以像从任何表中取回数据一样从视图取回数据，你既可以显示整个视图的内容，也可以仅显示指定的行和列。
select * from salvu50;


数据字典中的视图
   一旦视图被创建，你就可以查询数据字典视图USER_VIEWS来看视图的名字和视图定义。构成视图的SELECT语句的文本被存储在一个LONG列中。


用视图存取数据

  当你用视图存取数据时，Oracle 服务器执行下面的操作：
1. 从数据字典表USER_VIEWS中取回视图定义。
2. 检查对视图的基表的数据存取权限。
3. 转换视图查询为一个在基表或表上的等价操作，换句话说，数据从基表得到，或更新基表

USER_VIEWS
   --empvu80
select *     ----->  select employee_id, last_name,salary from employees
from empvu80;     where department_id=80;
employee_id last_name salary |
149   Zlotkey 100 |
174   Abel 1100   <--- employees



修改视图


:: 用CREATE OR REPLACE VIEW子句，为每个列添加一个别名

CREATE OR REPLACE VIEW empvu80
  (id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
salary, department_id
   FROM employees
   WHERE department_id = 80;
View created.

:: 在CREATE VIEW子句中的字段别名列表，按照与子查询中的字段相同的顺序排列


修改视图
   用OR REPLACE选项，允许创建视图时同名的视图已经存在，这样旧版本的视图会被替换，这意味着视图可以在不被删除、重新创建和再次授予对象权限的情况下修改。

注：当在CREATE VIEW子句中指定列别名时，不要望了别名的列表顺序与子查询的列顺序一样。




创建复杂视图

创建包含组函数的复杂视图，以从两个表中显示值

CREATE VIEW dept_sum_vu(name, minsal, maxsal, avgsal)
AS SELECT  d.department_name, MIN(e.salary),
   MAX(e.salary),AVG(e.salary)
   FROM employees e, departments d
   WHERE e.department_id = d.department_id
   GROUP BY d.department_name;
View created.


:::如果视图中的任何列源自函数或表达式，别名是必需的。


视图中DML 操作的执行规则

:: 只能在简单视图上执行DML 操作

在视图中执行 DML 操作
    你可以通过视图对数据进行执行DML操作，但那些操作必须符合下面的规则。

如果视图中不包含下面的部分，你就可以从视图中删除数据：
:: 组函数
:: GROUP BY子句
:: DISTINCT关键字
:: 伪列ROWNUM关键字


视图中DML 操作的执行规则

如果视图中包含下面的部分就不能修改数据：
-组函数
-GROUP BY子句
-DISTINCT关键字
-伪列ROWNUM关键字
-用表达式定义的列

     如果视图中不包含上面提到的情况，或者不包含由表达式定义的列，例如，SALARY * 12，你就可以通过视图修改数据。



视图中DML 操作的执行规则

如果视图中包含下面的部分就不能通过视图添加数据：
-组函数
-GROUP BY子句
-DISTINCT关键字
-伪列ROWNUM关键字
-用表达式定义的列
-基表中的NOT NULL列不在视图中



WITH CHECK OPTION 子句

:: 你可以确保DML操作在视图上被执行，用WITH CHECK OPTION子句检查视图中的域

CREATE OR REPLACE VIEW empvu20
AS SELECT *
   FROM employees
   WHERE department_id = 20
   WITH CHECK OPTION CONSTRAINT empvu20_ck ;
View created.

:: 任何改变视图的任意行中部门号的企图都会失败，因为它违反了WITH CHECK OPTION约束

使用WITH CHECK OPTION子句
  可以通过视图执行引用完整性检查，你也可以在数据库级别强制约束。视图能用于保护数据的完整性，但用途非常有限。

  WITH CHECK OPTION子句指出通过视图执行的INSERTs和UPDATEs不能创建视图不能选择的行，因此，该子句在数据开始插入或更新时允许完整性约束和数据验证检查。

  如果在没有选择的行上有一个执行DML操作的尝试，将会显示错误，如果指定了约束名，会带在错误提示中。

UPDATE empvu20
SET department_id = 10
WHERE employee_id = 201;
UPDATE empvu20
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

注：没有行被更新，因为如果部门号变成10，该视图将不再能够看到雇员，因此，带 WITH CHECK OPTION子句，视图只能看到部门20中的雇员，并且不允许通过视图改变那些看不到的部门中的雇员。



拒绝DML 操作
:: 添加WITH READ ONLY选项到视图定义中，能够确保无DML 操作发生

:: 任何在视图的任意行上执行的DML 的企图都导致一个Oracle 服务器错误

   如果用户没有提供约束名，系统将以SYS_Cn格式指定一个名字，这里n是一个整数，在系统中约束名是唯一的。

CREATE OR REPLACE VIEW empvu10(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
   FROM employees
   WHERE department_id = 10
   WITH READ ONLY;


拒绝DML操作
从带只读约束的视图中做任何删除行的尝试只会得到错误的结果。

DELETE FROM empvu10
WHERE employee_number = 200;
DELETE FROM empvu10
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-
preserved table

  任何对带只读约束的视图进行的插入或修改行的尝试，在Oracle服务器中都显示下面的错误结果：
01733: virtual column not allowed here.



删除视图


   删除视图不会丢失数据，因为视图是基于数据库中的基本表的

DROP VIEW view;

DROP VIEW empvu80;
View dropped.

   你可以用DROP VIEW语句来删除视图。该语句从数据字典中删除视图定义。删除视图不影响用于建立视图的基表。基于已删除视图上的其它视图或应用程序将无效。只有创建者或具有DROP ANY VIEW权限的用户才能删除视图。


内建视图

:: 内建视图是一个带有别名(或相关名) 的可以在SQL 语句中使用的子查询
:: 一个主查询的在FROM 子句中指定的子查询就是一个内建视图的离子
:: 内建子查询不是方案对象

SELECT a.last_name,a.salary,a.department_id,b.maxsal
FROM employees a,(SELECT department_id,max(salary) maxsal
  FROM employees
  GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;

     内建视图由位于FROM子句中命名了别名的字查询创建。该字查询定义一个可以在主查询中引用数据源。

在上面的例子中，内建视图b从EMPLOYEES表中返回每一个部门的部门号和最高薪水。主查询显示雇员的名字、薪水、部门号和该部门的最高薪水，WHERE a.department_id = b.department_id AND a.salary < b.maxsal 子句取回所有收入少于该部门最高薪水的雇员。

什么是序列?
序列：
:: 是自动产生的唯一的数
:: 是可共享的对象
:: 典型的用途是创建一个主键值
:: 可以代替应用程序编号
::当使用高速缓存存储器时，访问序列值的效率提高



什么是序列?
序列是用户创建的数据库对象，序列可以被多个用户共享以产生唯一的整数。
序列的一个典型的用途是创建一个主键的值，它对于每一行必须是唯一的。序列由一个Oracle内部程序产生并增加或减少。
序列是一个节省时间的对象，因为它可以减少应用程序中产生序列程序的代码量。
序列号独立于表被存储和产生，因此，相同的序列可以被多个表使用。



CREATESEQUENCE语句

   语法定义一个序列来自动产生有顺序的数：

CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n| NOCACHE}];

创建序列
   用CREATE SEQUENCE语句自动产生序列数。

在语法中：

sequence 是序列发生器的名字
INCREMENT BY n 指定序列号之间的间隔，在这儿n是一个整数 (如果该子句
被省略，序列增量为1)
START WITH n 指定要产生的第一个序列数 (如果该子句被省略，序列从1开始)

MAXVALUE n 指定序列能产生的最大值
NOMAXVALUE 对于升序序列指定10^27为最大值，对于降序序列指定-1
为最大值 (这是默认选项)

MINVALUE n 指定最小序列值

NOMINVALUE 对于升序序列指定1为最小值，对于降序序列指定-(10^26)
为最小值 (这是默认选项)

CYCLE|NOCYCLE 指定序列在达到它的最大或最小值之后，是否继续产生(NOCYCLE是默认选 项)

CACHE n|NOCACHE 指定Oracle服务器预先分配多少值，并且保持在内存中(默认
情况下，Oracle服务器缓冲20个值)

创建序列

:: 创建一个序列，命名为DEPT_DEPTID_SEQ，用于DEPARTMENTS表的主键
:: 使用非CYCLE选项

CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
Sequence created.

    此例子创建一个序列，并被命名为DEPT_DEPTID_SEQ，该序列用于DEPARTMENTS表的DEPARTMENT_ID列，该序列从120开始，不允许高速缓冲的，不循环。

    如果序列用于产生主键值，不使用CYCLE选项，除非你有一个可靠的机制比序列循环更快地清除旧的行。


    注：序列不依赖于一个表，通常，你应该命名序列；可是序列可以被用在任何地方，而不管它的名字。

    如果INCREMENT BY值是负数，序列是降序。另外，ORDER | NOORDER选项可用，ORDER选项保证序列值按顺序产生，如果你将序列用于产生主键值它是不重要的，该选项仅与Parallel Server(并行服务)选项有关。

   如果序列值被高速缓冲，如果系统故障它们将被丢失。



确认序列

:: 校验在USER_SEQUENCES数据字典表中的序列值
SELECT  sequence_name, min_value, max_value, increment_by, last_number
FROM    user_sequences;

:: 如果NOCACHE被指定，LAST_NUMBER列显示下一个可用序列数


确认序列
   一旦创建了序列，它就被文本化在数据字典中。因为序列是一个数据库对象，你可以在USER_OBJECTS数据字典表中识别它。



NEXTVAL和CURRVAL伪列

:: NEXTVAL返回下一个可用的序列值，它每次返回一个唯一的被引用值，即使对于不同的用户也是如    此

::CURRVAL获得当前的序列值
::在CURRVAL获得一个值以前，NEXTVAL对该序列必须发布


使用序列
  在创建序列后，它产生连续的数给你在表中使用。用NEXTVAL和CURRVAL伪列引用序列值。


NEXTVAL和CURRVAL伪列 /././././.
   NEXTVAL伪列用于从指定的序列中取回连续的序列数的下一个值。你必须用序列名限定NEXTVAL，当你引用sequence.NEXTVAL时，一个新的序列数被产生并且当前的序列数被放入CURRVAL。

   CURRVAL伪列被用于查阅当前当前用户刚才产生的序列数，NEXTVAL必须被在 CURRVAL可以被引用之前用于在当前用户的会话中产生一个序列数，你必须用序列名限定CURRVAL，当sequence.CURRVAL被引用时，最后返回给用户程序的值被显示。


使用NEXTVAL和CURRVAL的规则

你可以在下面的上下文中使用NEXTVAL和CURRVAL：
:: 一个不是子查询的一部分的SELECT语句的SELECT列表
:: 在一个INSERT语句中子查询的SELECT列表
:: 一个INSERT语句中的VALUES子句
:: 一个UPDATE语句的SET子句

你不能在下面的上下文中使用NEXTVAL和CURRVAL：
:: 一个视图的SELECT列表
:: 一个带DISTINCT关键字的SELECT语句
:: 一个带GROUP BY、HAVING或ORDER BY子句的SELECT语句
:: 一个在SELECT、DELETE或UPDATE语句中的子句
:: 在CREATE TABLE或ALTER TABLE语句中的DEFAULT表达式




使用序列

:: 在location ID 2500 中插入一个新部门名称“Support”


INSERT INTO departments(department_id, department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);
1 row created.


:: 查看当前的DEPT_DEPTID_SEQ序列值

select dept_deptid_seq.currval
from dual;


     现在设想你想要雇用雇员充当新部门的职员，对所有新雇员被执行的INSERT语句可以包含下面的代码：
INSERT INTO employees (employee_id, department_id, ...)
VALUES (employees_seq.NEXTVAL, dept_deptid_seq .CURRVAL, ...);



使用序列
:: 可以更快地访问缓存在存储器中的序列值
:: 序列值可能产生间隙，由于：
–一个回退发生
–系统崩溃
–一个序列被用于另一个表
:: 如果带NOCACHE创建序列，查询USER_SEQUENCES 表，可以查看下一个可用值


缓存序列值
     在内存中缓冲序列可以对序列值更快地存取，在你第一次使用到序列时被移入缓存。对于下一个序列值的每次请求从缓存的序列中找回。在最后一个序列值被使用后，对于序列的下一个请求拉出另一个在内存中的序列缓存。

序列中的间隙 /././././././
     尽管序列发生器发布无间隙的序列数，但因为这种行为不依赖于提交和回退，如果你回退包含一个序列的语句，该序列数将丢失。

     另一个能在序列中导致间隙产生的事件是系统崩溃，如果序列在内存中缓冲值，那么，如果系统崩溃那些值将丢失。

     因为序列不直接依赖于表，所以，相同的序列可以被用于多个表，如果你这样做了，每个表都将包含序列数的间隙。


查看下一个可用的序列值，而不增加它
     如果序列用带 NOCACHE 的选项创建的，就可以在序列值不增加的情况下用查询 USER_SEQUENCES表的方法，查看下一个可用的序列值。
     经常使用的序列用带缓存创建将增进效率，对于缓存(CACHE)的序列，没有办法找出下一个可用的序列将是什么，该值不实际获得和使用，因此，建议用户不要查找下一个序列值，而相信每次一个序列被用于一个 INSERT 语句时系统会提供一个唯一值。

SQL>  select locations_seq.nextval from dual;
      3400

SQL>  select locations_seq.nextval from dual;
      3500

SQL> select sequence_name,last_number,cache_size from user_sequences;

SEQUENCE_NAME                  LAST_NUMBER CACHE_SIZE
------------------------------ ----------- ----------
LOCATIONS_SEQ                         3600          0




修改序列

创建增量值，最大值，最小值，循环选项和缓存选项

ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
Sequence altered.

修改序列
   如果序列达到MAXVALUE限制，将再无来自序列的新值产生，并且你将收到一个序列已经超过MAXVALUE的错误指示。为了继续使用序列，你可以用ALTER SEQUENCE语句修改该序列。

语法
ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
在语法中： //不能修改start  with.
sequence 是序列发生器的名字

产生序列语法:
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n| NOCACHE}];



修改序列的原则

:: 你必须是该序列的所有者，或者有ALTER该序列的权限
:: 只有未来的序列数受影响
:: 为了以不同的数字重新开始一个序列，该序列必须被删除并且被重新创建 ./././.
:: 一些确认被执行


修改序列的原则 /././././
  你必须是被修改序列的所有者，或者有ALTER权限。

  用ALTER SEQUENCE语句，只有以后的序列数会受影响。

  用ALTER SEQUENCE语句，START WITH选项不能被改变。为了以不同的数重新开始一个序列，该序列必须被删除和重新创建。
 
   一些验证被执行，例如，一个新MAXVALUE如果小于当前的序列值就不能用。

ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 90
NOCACHE
NOCYCLE;

ALTER SEQUENCE dept_deptid_seq
*
ERROR at line 1:
ORA-04009: MAXVALUE cannot be made to be less than the current value




删除序列

:: 用DROP SEQUENCE语句从数据字典中删除序列
:: 序列一旦不再被引用可以被删除

DROP SEQUENCE dept_deptid_seq;
Sequence dropped.


删除序列
      使用DROP SEQUENCE语句从数据字典中删除一个序列。你必须是被删除序列的所有者或者有DROP ANY SEQUENCE权限来删除它。

  语法:
DROP SEQUENCE sequence;




什么是索引?

索引：
:: 是一个方案对象
:: 由Oracle 服务器使用，索引用一个指针来加速行的取回
:: 用快速路径访问方法来快速定位数据，减小磁盘I/O
:: 表和它的索引是无关的
:: 被Oracle 服务器自动地使用和维护


索引 /././././././
   Oracle服务器索引是一个方案对象，索引能用指针加速行的取回，索引可以被显式创建，也可以被自动创建，如果你在列上没有索引，那么将发生全表扫描。

    索引提供对表中行的直接和快速访问，它的目的是用已索引的路径快速定位数据以减少磁盘I/O。索引由Oracle服务器自动使用和维护，一旦一个索引被创建，它就不再需要用户直接管理。

    索引逻辑地和物理地独立于他们索引的表，这意味者索引可以在任何时候被创建或删除，并且不影响基表或其它的索引。

    注：当你删除表时，相应的索引也被删除。 /././././


   创建索引的决定是是一个全局的、高级的决定，对于数据库管理员，索引的创建与维护创建是一个经常性的工作。

在谓词WHERE字句中引用有索引的列，如果没有修改带函数或表达式的列的索引。ROWID是一个十六进制的串，表示包含块定义的行地址，行的位置在块中，并且有数据库文件标识符，访问任何指定行的最快的方法是引用它的ROWID。



索引怎样被创建?
:: 自动：
     在一个表的定义中，当定义一个PRIMARY KEY或UNIQUE约束时，一个唯一索引被自动创建

:: 手动：
     用户能够在列上创建非唯一的索引来加速对行的访问


索引的类型
    可以创建两种类型的索引，一种是唯一性索引：当你在一个表中定义一个列为主键，或者定义一个唯一键约束时Oracle服务器自动创建该索引，索引的名字习惯上是约束的名字。另一种索引类型是非唯一索引，它可以由用户创建，例如，你可以创建一个 FOREIGN KEY列索引用于一个查询中的连接来改进数据取回的速度。

    注：你可以手工创建唯一索引，但建议你创建一个唯一约束，这样会隐式创建一个唯一索引。




创建索引

:: 在一个或多个列上创建索引

CREATE INDEX index
ON table (column[,column]);

:: 改善EMPLOYEES表中LAST_NAME列的查询访问速度

CREATE INDEX emp_last_name_idx
ON employees(last_name);


创建索引
      用CREATE INDEX 语句在一个或多个列上创建一个索引。


为了在你自己的方案中创建索引，你必须有CREATE TABLE权限。为了在任何方案中创建索引，你需要CREATE ANY INDEX权限或者在正在创建索引的表上的 CREATE TABLE权限。
在语法中的另一个选项是UNIQUE关键字，强调你应该在表上显式地定义唯一索引。在表中可用约束代替定义唯一性索引，Oracle服务器用自动在唯一键上定义一个唯一索引强制唯一完整性约束。



什么时候创建索引
你应该创建索引，如果： /./././
:: 一个列包含一个大范围的值
:: 一个列包含很多的空值
:: 一个或多个列经常同时在一个WHERE子句中或一个连接条件中被使用
:: 表很大，并且经常的查询期望取回少于百分之2 到4 的行


more is not always better /./././././.
       在表上建立更多的索引并不意味者更快地查询，在带索引的表上被提交的每个 DML操作意味者索引必须更新；与表联系的索引越多，对Oracle服务器的影响越大，Oracle服务器在每次DML操作之后必须更新所有的索引。


什么时候创建索引

  因此，你应该只在下面的情况下创建索引：
:: 列包含一个大范围的值
:: 列包含大量的空值
:: 一个或多个列在WHERE子句或连接条件中被频繁使用
:: 表很大并且大多数查询所期望的返回行数少于总行数的2–4%

不要忘记，如果你想要强制非唯一，你应该在表中定义一个唯一的约束,这样唯一索引被自动创建。


   复合索引 (也称为连接索引) 是在一个表中的多个列上创建的索引，在复合索引中的列可以任何顺序出现，并且不需要与在表中的列相一致。

   复合索引可以加速SELECT语句的数据取回速度，在SELECT语句中WHERE子句引用复合索引的所有列或部分主要的列。



什么时候不创建索引


下面的情况通常不值得创建索引，如果： /././././
-表很小
-不经常在查询中作为条件被使用的列
-大多数查询期望取回多于表中百分之2 到4 的行
-表经常被更新
-被索引的列作为表达式的的一部分被引用


插一下:
     空值不能包含在索引中。

     为了优化连接，你可以在FOREIGN KEY列上创建一个索引，它将加速搜索匹配的行到 PRIMARY KEY 列。
     如果WHERE子句包含IS NULL表达式，优化不使用索引。



确认索引
:: USER_INDEXES数据字典视图包含索引和它唯一的名字
:: USER_IND_COLUMNS视图包含索引名、表名和列名


SQL> column index_name format a15
SQL> column column_name format a40
SQL> SELECT ic.index_name,ic.column_name,ic.column_position col_pos,ix.uniqueness
  2  FROM user_indexes ix, user_ind_columns ic
  3  WHERE ic.index_name = ix.index_name
  4  AND ic.table_name = 'EMPLOYEES';

INDEX_NAME      COLUMN_NAME                                 COL_POS UNIQUENES
--------------- ---------------------------------------- ---------- ---------
EMP_EMAIL_UK    EMAIL                                             1 UNIQUE
EMP_EMP_ID_PK   EMPLOYEE_ID                                       1 UNIQUE
EMP_DEPARTMENT_ DEPARTMENT_ID                                     1 NONUNIQUE
IX

EMP_JOB_IX      JOB_ID                                            1 NONUNIQUE
EMP_MANAGER_IX  MANAGER_ID                                        1 NONUNIQUE
EMP_NAME_IX     LAST_NAME                                         1 NONUNIQUE
EMP_NAME_IX     FIRST_NAME                                        2 NONUNIQUE

确认索引
       从USER_INDEXES数据字典视图可以确认索引的存在。你也可以查询 USER_IND_COLUMNS视图，检查与索引有关的列。




基于函数的索引

:: 一个基于函数的索引就是一个基于表达式的索引
:: 索引表达式用表中的列、常数SQL 函数和自定义函数来构建

CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));
Index created.

SELECT *
FROM departments
WHERE UPPER(department_name) = 'SALES';


基于函数的索引

    基于函数的索引用UPPER (列名)或LOWER (列名)关键字定义，它允许大小写敏感的查询，

例如，下面的索引：
CREATE INDEX upper_last_name_idx ON employees (UPPER(last_name));
使得处理查询容易，

例如：
SELECT * FROM employees WHERE UPPER(last_name) = 'KING';

   确保Oracle服务器使用索引而不是执行一个全表扫描，注意，在子查询中的函数值不能为空，例如，下面的语句保证使用索引，但如果没有WHERE子句，Oracle服务器还可能执行一个全表扫描：
SELECT *
FROM employees
WHERE UPPER (last_name) IS NOT NULL
ORDER BY UPPER (last_name);

/./././.
   Oracle服务器将带有DESC标记的列作为基于函数的索引处理，带DESC标记的列被以降序排序。

//???  没有create index这个权限可能是create table /././././
    你必须有CREATE INDEX和QUERY REWRITE系统权限。为了在另一个方案或另一个方案的表上创建索引，你必须有CREATE ANY INDEX和GLOBAL QUERY REWRITE权限。在基于函数的索引中，表的所有者也必须有关于函数的使用EXECUTE对象的权限。




删除索引

:: 用DROP INDEX命令从数据字典中删除索引
DROP INDEX index;

:: 从数据字典中删除UPPER_LAST_NAME_IDX索引
drop index upper_last_name_idx;

:: 为了删除索引，你必须是索引的所有者，或者有DROP ANY INDEX权限
/./././././.
注：如果你删除一个表，相关的索引和约束将被自动删除，但视图和序列将保留。


删除索引
       你不能修改索引，为了改变索引，你必须先删除它，然后重新创建它。用DROP INDEX 语句从数据字典中删除索引，为了删除索引，你必须是索引的所有者，或者有 DROP ANY INDEX权限。



同义词

创建同义词可以简化对象访问(对象的另一个名字)。同义词能够：
::另一个用户易于查阅表的所有者
:: 使对象名字变短

CREATE [PUBLIC] SYNONYM synonym
FOR object;

为一个对象创建同义词
    为了查阅另一个用户所拥有的表，你需要将创建该表的用户名加句点作为前缀加在表名前面。创建一个同义词可以除去对象名必须带的方案限制，并提供给你一个可替换表名、视图名、序列名和过程名或其它对象名。该方法对具有特别长的对象的名字很有用。

在语法中：
PUBLIC 创建一个可以被所有用户访问的同义词
synonym 是要被创建的同义词的名字
object 指出要创建同义词的对象

   原则:
对象不能包含包。././././.
一个私有同义词名字对于同一个用户必须与所有其它的对象不同。



创建和删除同义词

:: 为DEPT_SUM_VU视图创建一个短名字
CREATE SYNONYM d_sum
FOR dept_sum_vu;
Synonym Created.

:: 删除同义词
DROP SYNONYM d_sum;
Synonym dropped.



  为一个对象创建同义词 (续)

上面例子创建了一个DEPT_SUM_VU视图的用于快速引用的同义词。

    数据库管理员可以创建一个可为所有用户访问的公共同义词。下面的例子为Alice的 DEPARTMENTS表创建一个公共同义词：

CREATE PUBLIC SYNONYM dept
FOR alice.departments;
Synonym created.

  删除同义词
/./././././
用DROP SYNONYM语句删除一个同义词，只有数据库管理员可以删除一个公共同义词。
DROP PUBLIC SYNONYM dept;
Synonym dropped.

  Oracle服务器中，DBA可以明确授予CREATE PUBLIC SYNONYM权限给任何用户，这样普通用户就可以创建公共同义词。