数据库对象的基本知识

一、概述：

以数据库对象的基本知识为主线逐步展开说明，阐述数据库对象在实际项目中的应用场景及其应用方式，同时说明部分对象的使用注意事项，从而达到高效操作数据库的目的。

二、常用的数据库基本对象：

1、索引(Index)；

2、视图(View)；

3、存储过程(Procedure)；

4、函数(Function)；

5、触发器(Trigger)；

6、游标(Cursor)；

7、同义词（synonym）；

8、定时任务（Job）；

三、数据库对象的详细说明：

1、索引：

1)定义：

是一种用于提高查询效率的数据库对象，使用索引可以快速定位数据，减少磁盘IO操作次数。索引是由数据库自动维护，删除或破坏索引不会对数据表造成影响，只会影响查询速度。

2)分类：

a)按照索引字段是否允许出现重复划分：

唯一性索引、非唯一性索引；

b)按照索引基于字段的数目划分：

单字段索引、联合索引；

c)按照索引基于的字段是普通字段还是复合表达式划分：

普通索引、函数索引；

d)按照索引的数据结构划分：

B树索引、位图索引；

3)创建原则：

a)主键、外键要创建索引；

b)数据量大于300行的数据表建议创建索引；

c)若经常多表联合查询，则连接字段应该创建索引；

d)经常出现在where字句中的字段建议使用索引；

e)索引应该创建在小字段上，对于大的文本字段甚至超长字段，不要创建索引；

4)实际处理方式：

a)外键：

只创建普通索引；

b)时间字段：

同一个表的多个时间字段一般创建联合索引，其他场景只创建普通索引；

c)需要模糊查询的字段：

只在使用单模糊且只使用后模糊的情况下创建索引（非此情况查询时不走索引）；

d)定期重建索引：

一般针对频繁进行插入或删除数据的表进行此操作（目的是解决索引碎片问题）；

5)图解：

索引

2、视图：

1)定义：

视图是一种虚表，在已有的数据表或其他视图的基础上创建的，可以理解为存储起来的查询语句，视图本身不存储数据，因此对虚表的操作最终都会转换为对基表的操作。

2)优缺点：

a)优点：

i.可以存储复杂的查询语句，简化查询；

ii.安全性角度，从视图中查询到的数据只是基表数据中的一部分，这样可以屏蔽数据表结构，限制对数据的访问；

iii.逻辑数据独立性，视图的存在可以使得应用程序和数据表一定程度上相互独立，应用程序可以建立在视图之上，没有视图的话，应用程序必定是建立在数据表上的；

iv.数据库中实际上保存的视图编译后的查询指令，使用视图查询可以避免编译过程，提高运行效率；

b)缺点：

i.对视图的操作最终都会转换为对基表的操作，因此如果是对复杂视图的修改操作可能会失败；

ii.视图可以看作一个临时的结果集，因此没有键和索引的概念，应尽量避免与其他数据表进行联合查询；

3)特殊视图使用：

a)物化视图的定义：

物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新（通过oracle的内部机制可以定期更新）。物化视图是oracle提供的一种优化功能，类似于MSSQLServer中的snapshot，静态快照。

b)物化视图的分类：

i.包含聚集的物化视图；

ii.只包含连接的物化视图；

iii.嵌套物化视图；

c)特点：

三种物化视图快速刷新的限制条件有很大区别，而对于其他方面则区别不大。

4)图解：

a)普通视图：

视图

b)物化视图：

物化视图

3、存储过程和函数：

1)定义：

a)存储过程：

存储过程用于执行特定的操作，将经常需要执行的特定操作创建为存储过程，可以简化客户端应用程序的开发和维护，由于这些操作创建为存储过程之后，在数据库服务器端存储的是其编译后的指令，因此调用存储过程是不需要重新编译，可以提高运行效率。

b)函数：

函数用于返回特定的数据，可以将应用程序中经常使用SQL语句返回特定数据的操作创建为函数，通过函数可以简化客户端应用程序的开发和维护，提高应用程序的运行性能。

2)区别：

a)函数只能使用IN模式参数接收外界传入的数据，而存储过程可以用IN、OUT、INOUT三种；

b)函数必须有且只有一个返回值，用于将计算结果返回给调用环境；存储过程不允许有返回值，但可以使用OUT、或INOUT模式参数返回多个值给调用环境；

c)函数的返回值必须是数据库支持的类型，不允许使用SQL特有的数据类型；

d)函数不允许向存储过程一样被独立调用，函数只能作为表达式的一部分来使用；

3)图解：

存储过程和函数

4、触发器：

1)定义：

触发器是指被隐含执行的存储过程，可以使用PLSQL、java或C语言进行开发。当发生特定的事件(比如修改表、创建对象、登录到数据库等操作)时，系统会自动执行触发器中的代码，类似于高级编程语言中的事件监听器，主要用于数据的维护。

2)组成：

触发事件、触发条件、触发操作；

3)分类：

a)DML触发器：

i.由DML语句（insert、update、delete）触发；

ii.按触发时间可以分：before触发器和after触发器；

iii.按触发级别可以分：语句级触发器和行级触发器；

iv.语句级触发器无法使用:new和:old标识符获取新旧数据；

b)insteadof触发器：

i.又称替代触发器，用于执行一个替代操作来代替触发事件的操作，触发事件本身最终不会被执行；

ii.如果是DML触发器，则无论是before触发器还是after触发器，触发事件最终都会被执行；

中的insteadof触发器不能针对表，只能针对视图；

iv.若对列进行了数学或函数计算，则不能对该列进行DML操作，可以用insteadof触发器（例如：向一个有函数计算的视图中添加记录，若直接执行insert语句，则报错。若创建有对应的insteadofinsert触发器，则可以正常执行insert语句）；

c)事件触发器：

i.系统事件触发器和用户事件触发器；

ii.在发生如数据库启动或关闭等系统事件时触发；

iii.支持的系统事件有：logoff、logon、servererror、shutdown、startup；

iv.对于logoff和shutdown事件只能创建before触发器，对于logon、servererror、和startup事件只能创建after触发器；

v.创建系统事件触发器需要使用ondatabase字句；

vi.创建系统事件触发器需要用户具有DBA权限；

d)DDL触发器：

i.由DDL语句（create、alter、drop、grant、comment、revoke、truncate等）触发，也可分为before、after触发器；

ii.创建DDL触发器需要用户具有DBA权限；

4)应用：

安全性保护、对数据修改值的审计、提供完整性校验规则、提供数据库表的同步复制、事件日志记录等。

5)注意事项：

慎用触发器：

触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现；同时规则、约束、也是保证的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。

6)图解：

触发器

5、游标：

1)作用：

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

2)分类：

a)显式游标和隐式游标；

b)在前述程序中用到的SELECTINTO查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。

c)但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

3)图解：

游标

6、同义词：

1)概念：

同义词是数据库方案对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。在使用同义词时，数据库将它翻译成对应方案对象的名字。

2)分类：

a)公用Oracle同义词：

由一个特殊的用户组Public所拥有。数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用。

b)私有Oracle同义词：

它是跟公用同义词所对应，他是由创建他的用户所有。当然，这个同义词的创建者，可以通过授权控制其他用户是否有权使用属于自己的私有同义词。

3)作用：

a)多用户协同开发中，可以屏蔽对象的名字及其持有者。如果没有同义词，当操作其他用户的表时，必须通过user名.object名的形式，采用了同义词之后就可以隐蔽掉user名，当然这里要注意的是：public同义词只是为数据库对象定义了一个公共的别名，其他用户能否通过这个别名访问这个数据库对象，还要看是否已经为这个用户授权。

b)为用户简化sql语句。上面的一条其实就是一种简化sql的体现，同时如果自己建的表的名字很长，可以为这个表创建一个同义词来简化sql开发。

c)为分布式数据库的远程对象提供位置透明性。

4)图解：

同义词

7、定时任务：

1)说明：

job有定时执行的功能，可以在指定的时间点或每天的某个时间点自行执行任务。而且数据库服务重新启动后，job会继续运行，不用重新启动。

2)作用：

数据的提炼、备份、清除；包括数据库的性能优化；

3)图解：

定时任务

1、在外部程序调用时，应尽量避免直接调用函数，应通过存储过程进行调用，这样相对较为安全也更加合理，因为对于外部程序来说数据库层面的函数大部分都应该是私有的。

2、应尽量避免大量的使用自定义触发器或代替触发器，该类操作应尽量交给外部程序进行处理，这样可以避免事务处理复杂化，同时也可以避免由于事务嵌套或冲突造成的表或数据死锁。

3、游标应尽量嵌套在存储过程中使用，同时还要注意提取数据块的大小和占用时长，针对这些方面的考虑对于数据库的性能和资源消耗是非常必要的，因为毕竟是在直接操作数据库的内存，所以务必谨慎小心。