ORACLE学习笔记：SQL入门2

表：

　　数据库表是保存数据的二维记录结构。经常会需要对表授权读写能力。有时也会对表中的列赋予受限的访问权限。

　　数据库包含表，表包含数据，数据由数据类型来组织。数据类型这一模型中最小容器。他定义了哪种类型的数据库值可以存储在容器中，数据值如 数字，字符串，日期 分别在列中定义为 numeric，variable-length string 和date 数据类型。

　　表很少会定义为质保函一列。他们经常包含列集。这个列的集合将表定义为数据结构。

约束

　　表是通过DDL的 CREATE TABLE 命令定义的。改命令提供列名，数据类型，默认值和约束。列，数据类型和默认值必须在同一行定义，但是约束可以在两个地方定义。在列的同一行定义的约束是内链约束，这是经典的约束模式。如非空约或者单列为PRIMARY KEY 主键。 也可以列的定义之后在定义约束。这种约束的外联叫外联约束、

数据库的约束：

　　oracle 数据库中有5中约束，分别为：检查约束、外键约束、非空约束、主键约束、和唯一性约束。 约束对DML命令的限制如下：

　   1.检查约束在向列中插入或者更新数据之前，检查列值是否满足标准例如列值是否在两个值之间、值是否大于两个数值。或者是否满足一个符合逻辑组成的规则

　　2.外键约束检查列值是否出现在相同的表或不同的表中指定为主键列的列值表中，外键约束的表通常应该在应用程序中管理，而不是作为数据库约束

　　3.非空约束检验列值是否包含null以外的值

　　4.主键的约束来用于将表的列标识为主键，同时要求列值为非空和唯一，外键引用有效的主键列

　　5.唯一性的约束检查列值在表中的所有行是否唯一

 

表的约束可以应用于单一列，也可以用于多列。这是因为表约束由它在CREATE TABLE 语句语法中的定义位置。表的约束在最后一个列定义之后定义，这也使得它成为外联约束。表约束可以应用于多列或者在行间增强唯一约束。

　　由于不常使用外联约束，因此可以编写单列主键。不常使用的原因是单列主键约束 等价于 非空约束和唯一性约束，只有这两项约束完全可以用内联主键约束替代。使用外键的主键约束有两种选择：

　　一种使用系统生成的约束名，另一种使用用户定义的约束名。

下面实例展示了使用系统生成的约束名的外键主键约束：

　　,PRIMARY KEY (system_user_id) 

　　如下使用用户定义约束名：

　　,CONSTRAINT pk_system_user PRIMARY KEY (system_user_id)

　　多列主键约束的定义如下：

　　，CONSTRAINT pk_contact PRIMARY KEY (first_name,last_name)

　　检查约束经常是外键约束，因为涉及到的列可能包含其他内联约束，如非空约束。使用系统生成约束名的检查约束定义如下：

　　，CHECK(salary >0 AND salary < 5000 )

　　添加自定义约束名：

　　，CONSTRAINT ck_employee_01 CHECK (salary > 0 AND salary < 50000 )

　　基于多列的表约束定义如下：

　　,CONTSTRAINT ck_employee_02 CHECK

　　((salary BETWEEN 0  AND 49999.99 AND employee_class ='NON-EXEMPT'）

　　OR (salary BETWEEN  5000 AND 2499999.99 AND employee_class ='EXEMPT')

 　　OR (salary BETWEEN 250000 AND 9999.99 AND employee_class='EXECUTIVE') )

　　两种情况下可以将唯一约束定义为外联约束。第一种情况是已经应用了非空内联约束并想创建单列的唯一约束时。第二种情况是创建多列的唯一约束。

　　如下展示了单列唯一约束的语法：

　　,UNIQUE(common_lookup_id)

　　基于多列的唯一约束与单列的情况区别不大，唯一约束的区别是使用以逗号为分隔符的列名列表替换了单一列。下面的实例保安用户定义的约束名：

　　，CONSTARAINT un_lookup 

　　UNIQUE (common_lookup_table,common_lookup_column,common_lookup_type)

最常用的表级别约束是FOREIGN KEY（外键）约束。与以REFERENCES 关键字开头的内联版本不同，必须在外联约束中提供FOREIGN KEY 语句。下面的实例介绍了未使用用户定义约束名的FOREIGN KEY 约束：

　　，FOREIGN KEY (system_id ) REFERENCES common_lookup (common_lookup_id)

　　system_id 列名标识了当前表中的外键列。REFERENCE子句标识了表以以及括号内的列，括号中的K约束用于能找到外键值列表

　　如果为外键约束命名，则需要添加CONSTRAINT关键字和用户定义的约束。如下：

　　,CONSTRAINT fk_system_01 FOREIGN KEY (system_id)

　　REFERENCES common_lookup(cmmon_lookup_id)

　　与唯一约束相似，可以提供单列引用或者是以逗号分的列表，通常单列外键约束引用 指序列正生成值的代理建。多列外键值与表的多列自然建（复合自然键）相关。符合主键有主键约束，而且隐士地创建唯一约束。

删除约束：

　　ALTER TABLE table_name DROP CONSTRAINT constraint_name;

 

索引：

　　索引是单独的数据结构，他提供找到数据的领一种方法。索引通常可以加速查询处理和其他DML命令，如INSERT ,UPDATE ,MERGE ,和DELETE 语句，索引也可以被称为快速访问的途径

　　创建索引后，12C 数据库会维护索引的完整性。使用索引的好处就是可以改进SQL语句的性能。缺点是会增加花销，每当有INSERT UPDATE MERGE ,DELETE语句执行时，数据库会对索引进行相关的插入更新或删除的操作

　　索引有两种关键的属性，可用性和可见性。默认情况下索引是可用的，也是可见的。这意味着索引对于12C中基于成本优化器是可见的，对于支持索引的表是可用的

　　可以将索引设置为不可见，这种情况下的查询跟DML 语句不会使用索引。然而 基于成本的优化器仍然可以看见索引，并且可以使用DML语句维护它 这说明不可见索引和不可用索引或临时删除索引是不同的，不可见索引成为一定的开销。

　　索引按键原则工作，。键通常是列或者表达式的集。在键之上建立索引。当然键也可以为单列。基于多列的索引是复合的或者串级的索引

　　索引可以是唯一的 也可以是不唯一的 当通过主键约束或唯一约束限制列的时候。通常也会创建唯一索引，但他们是间接索引。针对两个不唯一的列，可以使用如下语法在单列上创建直接唯一索引

　　CREATE INDEX common_lookup_nuidx ON common_lookup(common_lookup_table);

　　如下实例将索引装变为不唯一爽列索引

　　CREATE INDEX common_lookup_nuidx 

　　ON common_lookup(common_lookup_table,common_lookup_column);

　　将索引标识为唯一索引更为直观，只需要在CREATE INDEX 语句添加UNIQUE关键字，如下，

　　CREATE UNIQUE INDEX common_lookup_nuidx

　　ON common_lokup (common_lookup_table,common_lookup_column,common_lookup_type);

 

多数索引都是使用B-tree (平衡树) B-tree 由三种块组成：根分三块，用于查找下一层块; 分支快，用于查找其他的分支快; 叶子快，用于存储指向值的指针。B-tree平衡树 因为所有叶子块都在同一层，这意味着对数中所有的元素查找长度是一样的。分支快存储最小的建的前缀，用于遍历B-tree时决定路径

　索引组织表  索引组织表以变异的B-tree索引结构存储 索引组织表的行存储由表主键定义的索引，Btree 中的每一个索引项也存储非建列的值。使用非书索引组织表可以通过主键快速的访问表中的数据，以及通过额外的数据块I/O 之间访问非键列。

反向索引 　　反向键索引是B-tree索引的一种，它颠倒索引键的物理字节顺序。但是保持了列的顺序不变

升序降序索引 Oracle 默认的索引是升序索引，字符以二进制形式存储字符数据，数值由小到大存储，且由近到远存储，降序索引的顺序相反

 　　在索引创建的语句中，通过附加DESC关键字可以创建不唯一的降序复合索引，例如：

　　CREATE INDEX common_look_upnuidx 

　　ON common_look_up (common_lookup_table,commn_look_up_column) DESC;

　　当查询中涉及升序和降序索引对列排序时。降序索引是最有用的。Oracle数据库查找首先是确定键值，然后使用相关的ROWUD值

B-tree 群集索引 B-tree群集索引是使用群集键查找数据的群集表。必须在创建表之前创建群集；

　　CREATE CLUSTER sales_records (cost_center_id NUMBER(4) SIZE 512);

　　下面是创建索引的语法：

　　CREATE INDEX sales_uidx ON CLUSTER sales_records;

　　最后创建如下表：

　　CREATE TABLE eastern_reqion (<column_list>)

　　CLUSTER sales_records (cost_center_id);

　　CREATE TABLE western_region(<column_list>)

　　CLUSTER sales_records (cost_center_id);

　　　这种配置确保了来自两个表中的行被写进了一个文件块。之后数据库以堆的形式存储数据，并使用索引查找数据

基于函数的索引：

　　在计算语句中，即WHERE子句中包含函数时，基于函数的索引效率很高。

　　使用如下语法创建基于函数的索引：

　　CREATE INDEX employee_uidx 

　　ON employee (salary + commission_percent ,salary ,connission_percent);

　　当查询中包含如下算数表达式时会触发函数：

　　SELECT emolooyee_id

　　,first_name 

　　,last_name

　　,(salary + commission_percent ) AS annual_salary

　　FROM employee e 

　　WHERE (salary + commission_percent) > 10000

　　ORDER B annual_salary DESC;

　　对WHERE 子句包含表达式的查询，优化器对于基于函数的索引进行索引范围扫描。当WHERE 字句中的选择性不高时　　，使用范围扫描访问路径的收益明显

　　应用程序域索引 应用程序索引域是自定义索引，用于支持应用

　　位图索引  位图索引是为每一个索引键存储比较数组，位图索引中最适合使用临时查询的仓库系统，用于基数低的表。对于更新频繁的读写表，位图索引是昂贵的且不适用的，位图表用于只读数据或者没有明显变化的数据。

 

视图 

　　oracle只支持读或者写的普通视图。如下是创建Oracle创建视图的基本原型：

　　CREATE [OR REPLACE]  [  [NO] FPRCE ]  VIEW view_name 

　　[ { column_name [inline_contstraint]  [,...] 

　　[,CONSTRAINT constraint_name

　　UNIQUE (column_name) RELY DISABLE NONVALIDATE ]

　　[,CONSTRAINT  constraint_name 

　　PRIMARY KEY (column_list) RELY DISABLE NONVOLIDATE ]

　　AS select_statement

　　[WITH {READ ONLY  | CHECK OPTION]  } ]

 　　OR  REPLACE 子句非常好用，因为他可以避免使用新定义重新创建视图时需要删除之前的视图。虽然默认使用 NO FORCE ，但是FORCE 告诉DBMS 及时查询引用的内容不再数据库中，也要创建视图

　　查询属具目録以及找到无效的视图。如下查询返回用户模式中的所有无效的视图列表：

　　SELECT object_name 

　　, object_type

　　,FRO user_objects

　　WHERE status='INVALID';

　　读写视图：　　临时绑定视图是最简单的读写视图，他返回的是表中存储的指定行区间内所有列，对于所有行集合来说指定行区间就像整个派中的一片一样

　　读写视图如下：

　　CREATE OR REPLACE VIEW item_view AS 

　　select * from item I

　　SWHERE SYSDATE 

　　BETWEN TRUNC(i.start_date) AND NVL(i.end_date,TRUNC(SYSDATE)+1)；

　　要注意BETWEN 操作符的查找值和经验和起始，终止边界值不能为null

　　只读视图：

　　只读视图发生在查询中，该查询SELECT 列表中包含子查询的集合表达式，选择性操作符（DECODE 函数或者是CASE操作符） 或DISTINCT操作符; 或者是查询使用了链接（join），set操作符，聚合函数或者GROUP BY ,ORDER BY ,MODEL,CONNECT BY ,START WITH 子句时。最后两种子句时Oracle混合子句，用户支持递归查询。视图执行上述操作时，不能写入基础行，但是可以通过在Oracke中部署INSTEAD OF 触发器展开查询的逻辑，并将数据写入基础行。

如下实例为 可读视图：

　　CREATE OR REPLACE VIEW employee_view 

　　(employee_id

,　　employee_name

,　　employee_status

.　　CONSTRAINT  pk_employee 

　　PRIMARY KEY (employee_id) RELY DISABLE NOVALIDATE)

　　AS

　　SELECT c.contact_id AS employee_id

　　　　　　c.last_name || ',' || c.first_name ||  

　　　　CASE 

　　　　WHEN c.middle_name IS NULL

　　　　THEN  '　　'

　　　　ELSE  '　　'  || c.middle_name 

　　　　END AS employee_name 

　　　　CASE 

　　　　WHEN c.contact_type = common_lookup_id

　　　　THEN 'Active'

　　　　ELSE 'Inactive'

　　　　END AS employee_status 

　　FROM contact c INNER JOIN common_lookup cl 

　　ON common_lookup_table ='CONTACT'

　　AND common_lookup_column ='CONTACT_TYPE'

　　AND common_lookup_type='EMPLOYEE';

　　 读写触发器可以包含派生的列或者表达式，但是不能为这些列写入数据。可以使用transaction_view 并使用INSTEAD OF 触发器将数据写入底层的事务

　　下面视图包含两个派生列，并且列不可更新

　　CREATE OR REPLACE VIEW transaction_view AS 

　　SELECT t.transaction_id AS id

　　,　　　　t.transaction_account AS account 

　　,　　　　t.transaction_type AS pirchase_type

　　,　　　　ttransaction_date AS purchase_date

　　,　　　　t.transaction_amount AS retail_amount 

　　,　　　　transaction_amount  * .0875 AS sales_tax

　　.　　　　t.transaction_amount * 1.0875 AS total

　　,　　　　t.rental_id AS rental_id

　　,　　　　t.payment_method_type AS payment_type 

　　,　　　　t.payment_account_number AS account_number

　　,　　　　t.created_by 

　　,　　　　t.creation_date

　　,　　　　t.last_updated_by 

　　,　　　　t.last_update_date

　　FROM 　　transaction t;

　　

　　函数。过程和包

　　函数，过程和包都是存储程序，是本书的主要内容。Oracle存储程序使用PL/SQL编程语言或带有PL/SQL 封装的其他语言编写。可以使用C/C++ 或JAVA 编写语言创建类库。

　　使用CREATE OR REPLACE 语法创建函数，过程和包，包包含函数，过程和用户定义类型。然而 不能使用CREATE OR REPLACE 语法将函数转变为过程，反义依然。、

　　函数的通用原型如下：

　　CREATE OR REPLACE FUNCTION function_name;

　　过程的通用原型如下：

　　CREATE OR REPLACE PROCEDURE procedure_name;

　　包规范的通用原型如下：

　　CREATE OR REPLACE PACKAGE package_name;

　　包主体的通用原型如下：

　　CREATE OR REPLACE PACKAGE BODY package_name;

　　要注意在包规范中定义的函数和过程列表，必须在包主体中严格的实现。这一点很重要

　　存储程序不能重名，但是可以从数据库中删除再重新建立

　　CREATE OR PLACE PROCEDURE hello_word IS 

　　BEGIN

　　　　dnms_output.put_line('Hwllo World!');

　　END;

　　/　　　　

　　下面在数据目录中使用同名的函数替换上面的过程:

　　CREATE OR REPLACE FUNCTION hello_word RETURNS VARCHAR2 IS 

　　BEGIN

　　　　RETURN 'Hello World!';

　　END;

　　/

　　产生如下的错误：

　　CREATE OR REPLACE FUNCTION hello_world RETURNS VARCHAR2 IS *

　　ORA-00955 :name is already used by an existing object

　　错误要求创建hello_world函数之前要删除已有的hello_word过程，这种问题是PL/SAL 编写新手经常遇到的问题

　　

 

  

 对象类型　　

　　由于对象类型的类型主要体现在是通过PL/SQL 实现的，因此PL/SQL 大篇幅的对其进行了介绍，但是对象类型也可以定义为SQL 级别的对象，而不需要实现，SQL级对象是UDT，这些UDT可以是：

　　● 分组存储在对象类型中的位置相关的一组属性集合

　　　　●不包含用户定义的构造函数和成员函数，过程

　　　　●包含用户定义的狗仔函数和成员函数，过程

　　●标量数据类型的列或数组，即ADT

　　●UDT对象集合

　　不包含用户定义构造函数和任何成员函数，过程的非集合UDP对象类型是自由浮点数据行，它等价于计算机科学中的记录结构。记录结构中的字段通常称为属性。因为他面向对象分析设计（OOAD中）就这样称呼他们。单独属性的标量数据类型集合称为ADT。

　　ADT和UDT可以是参数，返回类型，函数的局部变量，过程和对象。Oracle Database 12 C 在原有受限的PL/SQL 使用范围基础推广了PL/SQL 对象类型的使用。尽管如此，PL/SQL 没有取代SQL对象类型。他也可以是表中列的数据类型，

　　Oracle Database 12C 对象类型要么是持久的，要么是临时的。任何函数或者过程的参数，返回的数据类型。命名块或非命名块的局部变量都是临时的对象。临时对象是短暂存在的，因为他们的生命周期取决于使用他们的程序单元的持续时间，持久的对象是长久存在的，他们可以列的数据类型一致存在表中，一般不会改变

　　使用对象类型的第一步是在在SQL 或PL/SQL 中创建结构，因为对象表函数需要一个对象类型。

　　示例item_strucure 包含代理建ID 列，文本描述性字段lookup ，用于存储模式名的user_name .也可以在前面“”对象表函数视图“”中了解对象表函数的PL/SQL实现

　　CREATE OR REPLACE

　　TYPE item_structure IS  OBJECT 

　　(id, NUMBER 

　　,lookup VARCHAR2(30)

　　,USER_NAME varchar2(30));

　　/

　　注意到上述语句通过切断语法创建了对象类型。以阐述SQL 和PL/SQL 元素。第一行CREATE OR REPLACE语句和正斜线（/）都是SQL元素、第二行至第四行的定义是PL/SQL元素、唯一区分是PL/SQL还是SQL对象的地方就是第二行IS 后面的OBJECT 关键字

　　PL/SQL 记录结构使用IS RECORD关键字。该设计会将当前USER值插入user_name 列中

　　正如前面“”对象类型“中所介绍，可以在SQL*PLus ”命令行中描述对象类型。

　　DESCRIBE item_strucrure

　　Name                                         NUll　　　　　　　　　　　　　　　　TYPE

　　id　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　NUMBER

　　LOOKUP　　　　　　　　　　　　　　　　　　　　　　　　　　　　VARCHAR2(30)

　　USER_NAME 　　　　　　　　　　　　　　　　　　　　　　　　　VARCHAR2(30)

 

　　与前面的例子相似，这个创建结构集合的语法包含SQL和PL/SQL 元素。第一行和第三行是SQL元素。第二行是PL/SQL对象类型结构。这样没有主题的结构有一个默认的构造函数，它遵循为INSERT 语句准备的表的模式，插入元素时，使用创建结构时定义的位置顺序，有一种例外的情况：PL/SQL 支持命名符号，能够为参数添加标签，因此可以混合顺序。

　　同样，可以描述　　item_lookup集合UDP:

　　DESCRIBE item_lookup

　　item_lookup TABLE OF ITEM_STRUCTURE

　　NAME 　　　　　　NULL?　　TYPE 

　　ID　　　　　　　　　　　　　　NUMBER

　　LOOKUP 　　　　　　　　　　VARCHAR2(30)

 　 USER_NAME　　　　　　　　　VARCHAR2(30)

　　应该注意，在描述UDP集合是，需要包含一行来标识改UDP是另一个UDP类型的TABLE OF 、SQL*Plus DESCRIBE 语句显示的是item_structure udp 类型

 

　　外部表

　　Oracle 支持自定义外部组织表。外部组织表看起来与数据库的普通表是一样，但是其结构是来自操作系统 的只读或者读写文件，只读文件可以是以逗号分隔（CSV文件），以tab分隔或特定位置的文件，读写文件是Oracle 数据抽专有的格式

　　无论是处理外部的只读文件还是处理读写文件，都需要两个关键的准备步骤。这些部署将创建虚拟目录，并且授予数据库响应的读写权限。后续的第一部分包含了对准备步骤的介绍。接下来的两部分介绍了如何处理只读和读写文件

　　虚拟目录：虚拟目录是Oracle数据库的结构，他们存储在数据库目录中，他们将续集目录名映射到操作系统目录。虚拟目录会做一些假设，这些假设可能会成为严重的失误点。为了数据库授权并成功运行，物理目录必须可以按照操作系统的用户访问。这意味着操作系统用户应该有相对应目录的读写权限

　　CREATE DIRECTORY upload AS 'c:\dATA\Upload'

　　创建虚拟目录后，必须获取改路径的读写权限不管是部署只读文件还是读写文件，都应该获取目录的读写权限，因为通常这些文件会在同一目录上读写错误。

　　GRANT READ ,WRITE ON DIRECTORY upload TO importer;

　　创建虚拟目录后，可以在DBA_DIRECTORIES 视图中找到虚拟目录的映射。只有sys或system 超级用户可以访问这个概念视图。与其其他管理视图不通，此处没有USER_DIRECTORIES视图

　　Oracle SQL*Loader 文件 在准备阶段完成后，可以定义使用只读文件的外部管理的表，第六行设置TYPE 值为Oracle SQL*Loader 第七行设置DEFAULTDIRECORY 为前面创建虚拟目录名:

　　CREATE TABLE CHARACTER

　　(character_id NUMBER

　　,　　first_name VARCHAR2(20)

　　, 　　last_name VARCHAR2(20))

　　OGGANIZATION EXTERNAL

　　(TYPE oracle_loader

　　DEFAULT DIRECTORY upload

　　ACCESS PARAMETERS

　　(RECORDS  DELIMITED BY NEWLINE CHARACTERSET US7ASCII

　　BADFILE ' UPLOAD ': 'character.bad'

　　DISCARDFILE 'UPLOAD':character.dis'

　　LOGFILE 'UPLOAD':'character.log'

　　FOLELDS TERMINATED BY ','

　　optionally enclosed by ","

　　MISSING FILED VALUES ARE NULL )

　　LOCATION ('character.csv'))

REJECT LIMIT UNLIMITED;