SQL语言的分类
SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。
1 数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>
2 数据操纵语言 DML
数据操纵语言DML主要有三种形式：
1) 插入：INSERT
2) 更新：UPDATE
3) 删除：DELETE
3 数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYNONYM/CLUSTER
| | | | |
表 视图 索引 同义词 簇
4 数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
1) GRANT：授权。
2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚---ROLLBACK
回滚命令使数据库状态回到上次最后提交的状态。其格式为：
SQL>ROLLBACK;
3) COMMIT [WORK]：提交。
在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。
(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；
(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；
一 并发处理
数据库的特点就是数据的集中管理和共享。在通常情况下总是有若干个事务并发地运行，这些并行的事务可能并发地存取相同的数据。因此，数据库管理系统的一个重要任务就是要有一种机制去保证这种并发的存取和修改不破坏数据的完整性，确保这些事务能正确地运行并取得正确的结果。
我们知道，事务并发执行时若不加控制的话将导致不正确的结果和数据库的不一致状态。为保证数据库数据正确地反映所有事务的更新以及在一事务修改数据时其它事务不同时修改这个数据，数据库系统用锁来控制对数据的并发存取。
二 ORACLE的并发处理机制
无需任何说明，ORACLE自动提供行级锁，它允许用户在没有冲突的情况下更新表中不同的行。行级锁对联机事务处理非常有用。
1 ORACLE锁的类型
在正常情况下，ORACLE会自动锁住需要加锁的资源以保护数据，这种锁是隐含的，叫隐含锁。然而，在一些条件下，这些自动的锁在实际应用时并不能满足需要，必须人工加一些锁。这些人工加的锁叫显示锁。
下面指明了会产生隐含锁的SQL语句：
INSERT；
UPDATE；
DELETE；
DDL/DCL语句。
下面指明了会产生显示锁的SQL语句：
SELECT FOR UPDATE；
LOCK TABLE IN XXX MODE。
解决读的不可重复性可以用下面的方法。在ORACLE中，用SELECT FOR UPDATE对预期要修改的记录加行排它锁（X），对表加行共享锁（RS）。它常用于要锁住一行，但不去真的修改这一行。
锁之间是有相互作用的。例如，更新时会对表加RX锁，对行加X锁，而只有RS锁和RX锁允许再加RX锁。因此，当存在RS和RX锁时，表允许更新。再比如，当执行DDL和DCL语句时，会对表加排它锁X，而在存在X、RS、SRX、RX和S锁的前提下，都不能再加X锁。因此，当存在X，RS，SRX，RS或S锁时，不能对表做DCL和DDL操作。这样，数据库会自动防止一个用户更新表中的数据，而其他用户在同时修改表的结构。
2 ORACLE只读事务
ORACLE支持只读事务。只读事务有以下特点：
* 在事务中只允许查询
* 其它事务可修改和查询数据
* 在事务中，其它用户的任何修改都看不见
只读事务的写法为：
SET TRANSACTION READ ONLY
SQL 语句
COMMIT，ROLLBACK，DDL结束只读事务
3 事务一致性的级别
事务是定义和维护一致性的单位，封锁就是要保证这种一致性。如果对封锁的要求高会增加开销，降低并发性和效率；有的事务并不严格要求结果的质量（如用于统计的事务），如果加上严格的封锁则是不必要和不经济的。因此有必要进行进一步的分析，考察不同级别的一致性对数据库数据的质量及并行能力的影响。
一致性级别定义为如下的几个条件：
(1) 事务不修改其它任何事务的脏数据。脏数据是被其它事务修改过，但尚未提交的数据。
(2) 在事务结束前不对被修改的资源解锁。
(3) 事务不读其它任何事务的脏数据。
(4) 在读前对数据加共享锁（RS）和行排它锁，直至事务结束。
* 满足条件1的事务叫第0级事务。
* 满足条件1和2的事务叫第1级一致性事务。
* 满足条件1、2和3的事务为2级一致性事务。ORACLE的读一致性保证了事务不读其它事务的脏数据。
* 满足条件1、2、3和4的事务叫第3级一致性事务。
由ORACLE的三个性质：自动加隐式锁、在事务结束时释放锁和读一致性，使ORACLE成为自动满足以上的0、1和2级一致性事务。因此，ORACLE自动防止了脏读（写-读依赖）。但是，ORACLE不能自动防止丢失修改（写-写依赖），读的不可重复性（读-写依赖），彻底解决并发性中的问题还需满足第4个条件（3级一致性事务），这需要程序员根据实际情况编程。
方法如下：
* 如果想在一段时间内使一些数据不被其它事务改变，且在本事务内仅仅查询数据，
则可用SET TRANSACTION READ ONLY 语句达到这一目的。
* 如果想在一事务内修改一数据，且避免丢失修改，则应在读这一数据前用SELECT
FOR UPDATE对该数据加锁。
* 如果想在一事务内读一数据，且想基于这一数据对其它数据修改，则应在读数据前
对此数据用SELECT FOR UPDATE加锁。对此种类型的应用，用这条SQL语句加锁最恰当。
* 如果想避免不可重复读现象，可在读前用SELECT FOR UPDATE对数据加锁，或用SET TRANSACTION READ ONLY设置只读事务。
一 触发器介绍
触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。数据库触发器有以下的作用：
* 安全性。可以基于数据库的值使用户具有操作数据库的某种权利。
# 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。
# 可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一 次超过10%。
* 审计。可以跟踪用户对数据库的操作。
# 审计用户操作数据库的语句。
# 把用户对数据库的更新写入审计表。
* 实现复杂的数据完整性规则。
# 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
# 提供可变的缺省值。
* 实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。
# 在修改或删除时级联修改或删除其它表中的与之匹配的行。
# 在修改或删除时把其它表中的与之匹配的行设成NULL值。
# 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。
# 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code列上生成一个插入触发器，如果新值与auths.author_code列 中的某值不匹配时，插入被回退。
* 同步实时地复制表中的数据。
* 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。
二 ORACLE 触发器
ORACLE产生数据库触发器的语法为：
create [or replace] trigger 触发器名 触发时间 触发事件
on 表名
[for each row]
pl/sql 语句
其中：
触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before---表示在数据库动作之前触发器执行；
after---表示在数据库动作之后出发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器；
update：数据库修改会触发此触发器；
delete：数据库删除会触发此触发器。
表 名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个
表执行一次。
举例：下面的触发器在更新表auths之前触发，目的是不允许在
周末修改表：
create trigger auth_secure
before insert or update or delete //对整表更新前触发
on auths
begin
if(to_char(sysdate,'DY')='SUN'
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');
end if;
end
存储过程介绍
存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。在ORACLE中，若干个有联系的过程可以组合在一起构成程序包。
使用存储过程有以下的优点：
* 存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
* 可保证数据的安全性和完整性。
# 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。
# 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
* 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。
* 可以降低网络的通信量。
* 使体现企业规则的运算程序放入数据库服务器中，以便：
# 集中控制。
# 当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。如果把体现企业规则的运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化。
二 ORACLE 的存储过程
ORACLE 创建存储过程的语法为：
create [or replace] procedure 过程名
参数1 [in|out|in out] 数据类型
[,参数2 [in|out|in out] 数据类型]...
{is|as} pl/sql 语句
下面举例说明ORACLE数据库存储过程的写法和用法。
可以建立一个存储过程，每当用户修改数据库的重要数据时，即把用户的用户名;
create procedure update_log is
begin
insert into update_log_tab(use_name,update_date,operation)
values(user,sysdate,'update' )
end;
可以在恰当的位置调用这个存储过程来记录用户对表的修改。例如下面在表sal_comm上建立一个修改触发器，每当用户修改此表后，用户的名称、修改时间和操作即被记录在了表update_log_tab中：
create trigger audit_update
after update on sal_comm
for each row
begin
update_log
end
一 事务处理介绍
事务是这样一种机制，它确保多个SQL语句被当作单个工作单元来处理。事务具有以下的作用：
* 一致性：同时进行的查询和更新彼此不会发生冲突，其他用户不会看到发生了变化但尚未提交的数据。
* 可恢复性：一旦系统故障，数据库会自动地完全恢复未完成的事务。
二 事务与一致性
事务是完整性的单位，一个事务的执行是把数据库从一个一致的状态转换成另一个一致的状态。因此，如果事务孤立执行时是正确的，但如果多个事务并发交错地执行，就可能相互干扰，造成数据库状态的不一致。在多用户环境中，数据库必须避免同时进行的查询和更新发生冲突。这一点是很重要的，如果正在被处理的数据能够在该处理正在运行时被另一用户的修改所改变，那么该处理结果是不明确的。
不加控制的并发存取会产生以下几种错误：
1 丢失修改（lost updates）
当多个事务并发修改一个数据时，不加控制会得出错误的结果，一个修改会覆盖掉另一个修改。
2 读的不可重复性
当多个事务按某种时间顺序存取若干数据时，如果对并发存取不加控制，也会产生错误。
3 脏读（DIRDY DATA），读的不一致性
4 光标带来的当前值的混乱
事务在执行过程中它在某个表上的当前查找位置是由光标表示的。光标指向当前正处理的记录。当处理完该条记录后，则指向下一条记录。在多个事务并发执行时，某一事务的修改可能产生负作用，使与这些光标有关的事务出错。
5 未释放修改造成连锁退出
一个事务在进行修改操作的过程中可能会发生故障，这时需要将已做的修改回退（Rollback）。如果在已进行过或已发现错误尚未复原之前允许其它事务读已做过修改（脏读），则会导致连锁退出。
6 一事务在对一表更新时，另外的事务却修改或删除此表的定义。
数据库会为每个事务自动地设置适当级别的锁定。对于前面讲述的问题：脏读、未释放修改造成的连锁退出、一事务在对一表更新时另外的事务却修改或删除此表的定义，数据库都会自动解决。而另外的三个问题则需要在编程过程中人为地定义事务或加锁来解决。
三 事务和恢复
数据库本身肩负着管理事务的责任。事务是最小的逻辑工作单元，在这个工作单元中，对数据库的所有更新工作，要么必须全部成功，要么必须全部失败（回退）。只要应用程序指定了某段程序为一个事务并做了相应的处理（提交或回退），数据库系统会自动维护事务本身的特性。
四 ORACLE数据库的事务定义
ORACLE事务从COMMIT、ROLLBACK、连接到数据库或开始第一条可执行的SQL语句时开始，到一条COMMIT、ROLLBACK语句或退出数据库时结束。如果在一个事务中包含DDL语句，则在DDL语句的前后都会隐含地执行COMMIT语句，从而开始或结束一个事务。
如果一个事务由于某些故障或者由于用户改变主意而必须在提交前取消它，则数据库被恢复到这些语句和过程执行之前的状态。
利用ROLLBACK语句可以在COMMIT命令前随时撤消或回退一个事务。可以回退整个事务，也可以会退部分事务，但是不能回退一个已经被提交的事务。回退部分事务的ROLLBACK命令为：
ROLLBACK to savepoint 存储点名
存储点是用户放入事务中的标记，用来表示一个可被回退的位置。存储点通过在事务中放入一个SAVEPOINT命令而被插入。该命令的语法是：
SAVEPOINT 存储点名
如果在ROLLBACK语句中没有给出存储点名，则整个事务被回退。
一 视图的概念
视图是原始数据库数据的一种变换，是查看表中数据的另外一种方式。可以将视图看成是一个移动的窗口，通过它可以看到感兴趣的数据。
视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。
视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些视图可以修改对应的基表，有些则不能（仅仅能查询）。
二 视图的作用
* 简单性。看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
* 安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上：
使用权限可被限制在基表的行的子集上。
使用权限可被限制在基表的列的子集上。
使用权限可被限制在基表的行和列的子集上。
使用权限可被限制在多个基表的连接所限定的行上。
使用权限可被限制在基表中的数据的统计汇总上。
使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。
* 逻辑数据独立性。视图可帮助用户屏蔽真实表结构变化带来的影响。
三 视图的安全性
视图的安全性可以防止未授权用户查看特定的行或列，是用户只能看到表中特定行的方法如下：
1 在表中增加一个标志用户名的列；
2 建立视图，是用户只能看到标有自己用户名的行；
3 把视图授权给其他用户。
四 逻辑数据独立性
视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。视图可以在以下几个方面使程序与数据独立：
1 如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而应用程序可以不动。
2 如果应用建立在数据库表上，当应用发生变化时，可以在表上建立视图，通过视图屏蔽应用的变化，从而使数据库表不动。
3 如果应用建立在视图上，当数据库表发生变化时，可以在表上修改视图，通过视图屏蔽表的变化，从而应用程序可以不动。
4 如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而数据库可以不动。
一 序号生成器
有很多应用系统要用到序号，如商场的销货号，货物入库的流水号等等。由于序号是连续生成的，在大型系统中，会有多个用户同时申请下一个序号，序号生成便成了应用系统的瓶颈问题大型数据库都增加了序号对象。通过序号对象可以自动生成序号，多个用户可以并发读取，无需互相等待。
二 ORACLE的序号生成器
ORACLE序号生成的语法为 create 序号名 increment by 每次增长数 start with 起始序号
例如，生成序号seq1，初始值为1，每次增长为1： create seq1 increment by 1 start with 1
可以用下面的语句得到当前的序号： select seq1.currval from dual
也可以用下面的语句得到下一个序号： insert into dept values(seq1.nextval,...)
在一个事务中，不管由于什么原因程序终止，已经申请的序号就不能再使用了。多个用户同时使用序号时，它们之间互不等待。因此，使用序号发生器会有跳号现象出现。
闽公网安备 35060202000074号