PL SQL语言

4.1 PL/SQL简介

4.1.1 PL/SQL概述

PL/SQL是Oracle在标准SQL语言上的过程性扩展，它允许嵌入SQL语句、定义变量和常量、使用过程语言结构（条件分支语句和循环语句）、使用异常来处理Oracle错误等。在任何运行Oracler的平台上，应用开发人员都可以使用PL/SQL。通过使用PL/SQL，可以在一个PL/SQL块中包含多条SQL语句和PL/SQL语句。PL/SQL可以用于创建存储过程、触发器和程序包等，也可以用于处理业务规则、数据库事件或为SQL命令的执行添加程序逻辑。

PL/SQL是一种可移植的高性能事务处理程序，它支持SQL和面向对象编程，提供了良好的性能和高效的处理能力，具有以下6个方面的优点。

1．提高应用程序的运行性能

在编写OracoeOracle数据库应用程序时，开发人员可以直接将PL/SQL块内嵌到应用程序中，其最大的优点是可以降低网络开销、提高应用程序性能。对于其他异构数据库（例如SQL Server、Sybase、DB2等），当应用程序访问RDBMS时，每次只能发送单条SQL语句。而对于Oracle数据库而言，通过使用PL/SQL块，可以将多条SQL语句组织到同一个PL/SQL块中，从而降低网络开销，提高应用程序的性能。

2．提供模块化的程序设计功能

当开发数据库应用程序时，为了简化客户端应用程序的开发和维护工作，可以首先将企业规则或商业逻辑集成到PL/SQL子程序（过程、函数和包）中，然后在应用程序中调用子程序实现相应的程序功能。

3．允许定义标识符

当使用PL/SQL开发应用模块时，为了使应用模块与应用环境实现数据交互，需要定义变量、常量、游标等各种标识符。例如，函数get_sal中的no为输入参数，用于接收雇员编号的输入值，而salary变量则用于临时存储雇员工资。

4．具有过程语言控制结构

PL/SQL是Oracle在标准SQL上的过程性扩展，它不仅允许在PL/SQL块中嵌入SQL语句，而且允许在PL/SQL块中使用各种类型的条件分支语句和循环语句。

5．具有良好的兼容性

PL/SQL是Oracle提供的用于实现应用模块的语言，在允许运行Oracle的任何平台上都可以使用PL/SQL。例如，在Oracle数据库中使用PL/SQL开发数据库端的过程、函数和触发器，以及在Oracle提供的应用开发工具Developer中使用PL/SQL开发客户端的过程、函数和触发器。

6．处理运行错误

当设计并开发应用程序时，为了提高应用程序的健壮性，可以使用PL/SQL提供的异常处理（EXCEPTION）集中处理各种Oracle错误和PL/SQL错误，从而简化错误处理。

4.1.2 PL/SQL块

所有的PL/SQL程序都以块作为基本单位组成。块中包含过程化语句和SQL的DML语句。这些块可以按顺序出现，也可以相互嵌套（一个块在别一个块的内部）。按是否带有名称以及在数据库中的存储方式，块可以分为以下5种。

1．匿名块

匿名块是出现在应用程序中的没有名称且不存储到数据库中的块。匿名块可以出现在SQL语句出现的地方，它们可以调用其他程序，却不能被其他程序调用。

2．命名块

命名块是一种带有标签的匿名块，标签为块指定了一个名称。

3．子程序

子程序是存储在数据库中的过程（procedure）、函数（function），生成之后可以被多次执行。

4．程序包

程序包是存储在数据库中的一组子程序、变量定义。程序包中的子程序可以被其他程序包或者子程序调用。如果声明为局部子程序，则只能在定义该局部子程序的块中被调用该局部子程序。

5．触发器

触发器是一种存储在数据库中的命名块，生成之后可以被多次执行。在相应的触发器事件发生之前或之后就会被执行一次或多次（每行记录一次）。触发器事件一般是指对特定的数据库表、视图进行的操作，如INSERT、UPDATE和DELETE等（被称为DML触发器）；或者对数据库级的操作，如关闭、启动、登录、退出数据库，创建对象、修改对象、删除对象等（称为系统触发器）。

4.2 程序结构

4.2.1 组成部分和注释

PL/SQL程序块由3个部分组成：定义部分、执行部分和异常处理部分。其中，定义部分用于定义常量、变量、游标、异常和复杂数据类型等；执行部分用于实现应用模块功能，该部分包含需要执行的PL/SQL语句和SQL语句；异常处理部分用于处理执行部分可能出现的运行错误。PL/SQL块的基本结构如下：

DECLARE

   定义部分

BEGIN

   执行部分

EXCEPTION

   异常处理部分

END；

其中，定义部分以DECLARE开始，该部分是可选的；执行部分以BEGIN开始，该部分是必须的；异常处理部分以EXCEPTION开始，该部分是可选的；END则是PL/SQL程序块的结束标记。以下示例创建匿名程序块，用于接收用户输入的员工编号，输出该员工的姓名，以及处理用户输入的员工编号不存在的异常

注意

DECLAREE、BEGIN和EXCEPTION后面没有“；”（分号），而END后则必须要带“；”（分号）。

在PL/SQL程序中加入注释可以帮助理解程序，PL/SQL编译器在编译时会忽略注释，PL/SQL的注释分为单行注释和多行注释。

1．单行注释

单行注释可以放在一行的任何地方，由两条短横线（--）开始。

2．多行注释

多行注释“/*”开始、“*/”结束，可以跨越多行，但不允许嵌套。

在调试PL/SQL程序时，完全可以使用单行或多行注释来将暂时不需要或不正确的语句进行注释或禁用。

4.1.1 常量与变量

在声明部分中可以声明需要使用的常量、变量、函数、游标、异常处理名称等。

1．声明

声明常量、变量的语法如下：

语法：

Iidentifier_name  [CONSTANT]  data_type [NOTNULL][:=value_expression]|[DEFAULT value_expression]

其中，identifier_name指定需要声明的常量、变量的名称；data_type指定数据类型；“：=”是赋值运算符（或使用DEFAULT）；value_expression是赋值表达式。如果语句中有CONSTANT，则表明声明的是一个常量；如果有NOT NULL，则表明声明的变量是不能为空（即在声明时必须赋值）。声明常量时应该立即赋值，如果没有赋值则表示初始化为NULL。

注意:

在PL/SQL中，每一行中能声明一个常量或变量。在引用一个常量或变量之前，必须先对其进行声明。

2．使用SELECT INTO语句为变量赋值

除了可以使用常量为变量赋值之外，还可以将从数据库表中查询获得的值赋给变量。通过SELECT INTO语句可以从数据库表中查询的结果赋予变量，根据雇员编号获得雇员工资、补助和总工资，代码如图4.1.1所示。该示例代码中，从数据库中根据员工编号查询员工姓名，并把员工姓名赋给变量v_ename。

4.1.1 数据类型

编写PL/SQL程序时，若临时存储数值，则必须定义变量和常量；若在应用环境和子程序之间传递数据，则必须为子程序指定参数。而在PL/SQL程序中定义变量、常量和参数时，必须为它们指定PL/SQL数据类型。常见的数据类型如表14-1-1所示。

表14-1-1 部分数据类型及说明

数据类型

说明

char

表示固定长度字符串，长度不够时使用空格来补充，最多可以存储2000字节。

varchar2

表示可变长字符串，最多可以存储4000字节。

number

可以存储正数、负数、零、定点数和精度为38的浮点数。其格式为：number(m,n)，其中m表示精度代表数字的总位数；n表示小数点右边数字的位数。

date

用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日到公元9999年12月31日。Date类型的长度是7，7个字节分别表示世纪、年、月、日、时、分和秒。

timestamp

用户存储日期的年、月、日、小时、分和秒值。其中，秒值精确到小数点后6位数，该数据类型同时包含时区信息。

clob

用于存储可变长度的字符数据，最多可存储4GB。该数据类型用于存储varchar2类型不能存储的长文本信息。

blob

用于存储较大的二进制对象，如图形、视频剪辑和声音剪辑等，该类型最多可存储4GB数据。

PL/SQL中的数据类型除了表1-1-1所列出的以外还有以下常用的类型：

1.%TYPE

当定义PL/SQL变量存放值时，必须确保变量使用合适的数据类型和长度，否则可能在运行过程中出现PL/SQL运行错误。此时，可以使用%TYPE属性来定义变量。当使用%TYPE属性定义变量时，Oracle会自动地按照数据库列或其他变量来确定新变量的类型和长度。图4.1.3演示了使用%TYPE定义变量的过程。

2．%ROWTYPE

如果一张表中包含较多的列，则可以使用%ROWTYPE来定义一个表示表中一行记录的变量，这样比分别使用%TYPE来定义表示表中各个列的变量理更加简洁。为了使一个变量的数据类型与一张表中记录的各个列的数据类型对应一致，Oracle提供了%ROWTYPE定义定议方式。

在图4.1.3中，根据输入的员工编号查询该员工姓名、部门编号，并将查询结果存储在变量v_ename、v_deptno中。然后根据根据部门编号查询一条部门信息并将该信息存入变量v_dept_row中。

3．RECORD

PL/SQL记录类似于高级语言中的结构，每个PL/SQL记录一般都包含多个成员。当使用PL/SQL记录时，首先需要在定义部分定义记录类型和记录变量，然后在执行部分引用该记录变量。当引用记录成员时，必须将记录变量作为前缀，格式为“记录变量.记录成员”。

语法:

Type  record_name is record

(

V1  data_type1  [not null] [:=default_value],

V2  data_type2  [not null] [:=default_value],

Vndata_typen  [not null] [:=default_value]

);

4．TABLE

索引表相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串。

语法:

Type  table_name  is  table  of   element_type [not null]

index  by [binary_integer |  pls_integer  |varchar2]  

关键字INDEX BY表示创建一个主键索引，以便引用记录表变量中的特定行。

4.1 流程控制

流程控制是PL/SQL对SQL最重要的扩展，流程控制结构包括条件控制、循环控制和顺序控制。

4.1.1 条件控制

在许多情况中，需要按照某种条件来选择执行某些语句段。条件控制先测试一个条件，然后根据测试的结果选择、运行不同的语句段。条件结构中允许嵌套。

1．IF…THEN…END IF结构

最简单的条件控制结构。

语法:

If IF  条件表达式  THEN

语句段

END  IF;

2．IF…THEN…ELSE…END IF结构

二重条件分支结构。如果满足条件则执行一组操作，不满足条件则执行另外一级组操作。

语法:

IF  条件表达式  THEN

语句段1

ELSE

语句段2

END  IF;

3．IF…THEN…ELSIF…END IF结构

多重条件分支结构。如果满足第一个条件，则执行第一种操作；如果不满足第一个条件，则检查是否满足第二个条件，如果满足则执行第二种操作；如果不满足第二个条件，则检查是否满足第三个条件……依此类推。

语法:

IF  表达式1   THEN

语句段1

ELSIF 条件表达式2  THEN

语句段2

……

ELSIF  条件表达式n   THEN

语句段n

ELSE

语句段

END  IF ;

CASE结构

CASE结构是在Oracle 9i中引的另一种逻辑判断结构，它提供了另一种逻辑判断的实现方法。

语法:

CASE 表达式

WHEN 条件表达式结果1   THEN

语句段1;

WHEN 条件表达式结果2   THEN

语句段2;

…...

ELSE

语句段n;

   END  CASE;

CASE结构的另一种语法格式被称为CASE搜索结构，语法如下：

语法：

CASE

WHEN  条件表达式结果1   THEN

语句段1;

WHEN  条件表达式结果2    THEN

语句段2;

……

ELSE

语句段n;

END  CASE;

其中，CASE结构非常适用于从多个值中选择一个，以决定需要执行的语句段。在实际应用中，CASE结构的可读性及有效性更高，尽量将很长的IF…THEN…ELSIF…END IF语句改写成CASE结构。

4.1.1 循环控制

为了在编写的PL/SQL块中重复执行一条语句或者一组语句，可以使用循环控制结构。编写循环控制结构时，用户可以使用基本循环、WHILE循环和FOR循环等类型的循环语句。

1．基本循环

在PL/SQL中，格式最简单的循环语句是基本循环语句，它以LOOP开始，以END LOOP结束。

语法：

LOOP

语句段;

EXIT  [WHEN 条件表达式]

END  LOOP;

上述语法中，当使用基本循环时，无论是否满足条件，语句至少会被执行一次。当条件表达式为TRUE时，会退出循环，并执行END LOOP后的操作。

注意：

EXIT语句必须放在循环体内，且只能退出循环体，不能退出PL/SQL块。当需要退出PL/SQL块时，应该使用RETURN语句。

WHILE循环

基本循环至少需要执行一次循环体内的语句，而WHILE循环中，只有当条件为TRUE时，才会执行循环体内的语句。我们改造图4.1.9的示例，使用while循环来实现

FOR循环

当使用基本循环或WHILE循环时，需要定义循环控制变量。循环控制变量不仅可以使用NUMABENUMBER类型，还可以使用其他数据类型。当使用FOR循环时，Oracle会隐含定义循环变量。

默认情况下，当使用FOR循环时，每次循环，时控制变量会自动增加1；如果指定了REVERSE选项，则每次循环时循环控制变量会自动减少1。在循环体内部可以引用循环变量，但不能对其赋值。

4.1 异常处理

PL/SQL异常是指通过编译的PL/SQL程序在运行时产生的错误。导致异常的原因较多，如内存用尽、硬件故障、违反表的完整性约束、设计缺陷等。PL/SQL提供了异常错误处理机制，可以帮助实现对错误的捕获和处理。当异常发生时，PL/SQL会自动地捕获异常并将程序控制流程转移到异常处理部分部会的程序。

语法:

EXCEPTION

WHEN 异常错误名称1 [OR  异常错误名称2……]  THEN

语句段1;

WHEN 异常错误名称3  [OR  异常错误名称4…… ]  THEN

语句段2;

……

WHEN  OTHERS  THEN

语句段3;

 

上述语法中，异常处理部分以保留字EXCEPTION开始，可以使用WHEN子句按“异常错误名称”捕获各种异常错误，其他没有预计到的异常错误可以使用WHEN OTHERS子句进行捕获。

4.1.1 预定义异常

针对一些常见的错误，PL/SQL预定义了一些异常错误。当PL/SQL程序违反了Oracle的规定或超出了系统规定的限制时，就会隐含地引发一个预定义的错误。PL/SQL在程序包STANDARD中包含了这些预定义异常，常用的预定义异常错误见表4-1-2所示。

表1.1.14-1-2 PL/SQL预定义异常

异常

ORACLE错误

描述

access_into_null

ora-6530

试图访问一个未初始化的对象

case_not_found

ora-6592

case语句中的选项与用户输入的数据不匹配

cursor_already_open

ora-6511

试图打开一个已打开的游标

dup_val_on_index

ora-0001

试图破坏一个唯一性约束

invalid_cursor

ora-1001

试图打开一个无效的游标

invalid_number

ora-1722

试图对非数字值进行数字操作

login_denied

ora-1017

无效的用户名或者口令

no_data_found

ora-1403

查询未找到数据

not_logged_on

ora-1012

还未连接就试图数据库操作

program_error

ora-6501

内部错误

rowtype_mismatch

ora-6504

主变量的游标的类型不兼容

storage_error

ora-6500

内部错误

timeout_on_resource

ora-0051

发生超时

too_many_rows

ora-1422

select  into 命令返回多行

transaction_backed_out

ora-006

由于死锁提交被退回

value_error

ora-6502

转换或者裁剪错误

zero_divide

ora-1476

试图被除零

4.1.1 自定义异常

预定义异常都是由Oracle判断的异常错误。在实际的PL/SQL程序开发过程中，为了具体的业务规则、编程和调试的需要，程序员可以自定义一些异常。自定义异常必须进行声明，并且必须使用RAISE语句显式地触发。

在删除表中的数据时，如果没有符合条件的记录，则不会删除数据。因为这不是错误，所以不会有任何的错误提示，但可以使用自定义错误的方法来提示。例如，通过判断来获知是否存在该记录，并使用自定义异常的方法进行提示，代码如图14.1.13所示。其中sql代表隐含游标。

4.1 什么是事务（Transaction）

设想网上购物的一次交易，其付款过程至少包括以下几步数据库操作：

Ø 更新客户所购商品的库存信息

Ø 保存客户付款信息--可能包括与银行系统的交互

Ø 生成订单并且保存到数据库中

Ø 更新用户相关信息，例如购物数量等等

正常的情况下，这些操作将顺利进行，最终交易成功，与交易相关的所有数据库信息也成功地更新。但是，如果在这一系列过程中任何一个环节出了差错，例如在更新商品库存信息时发生异常、该顾客银行帐户存款不足等，都将导致交易失败。一旦交易失败，数据库中所有信息都必须保持交易前的状态不变，比如最后一步更新用户信息时失败而导致交易失败，那么必须保证这笔失败的交易不影响数据库的状态--库存信息没有被更新、用户也没有付款，订单也没有生成。否则，数据库的信息将会一片混乱而不可预测。

数据库事务正是用来保证这种情况下交易的平稳性和可预测性的技术。

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml(数据操作语言insert delete update)语句要么全部成功，要么全部失败。

4.1.1 事务的ACID特性  

  对一组SQL语句操作构成事务,数据库操作系统必须确保这些操作的原子性,一致性,隔离性,持久性.  

1、  原子性(Atomicity)  

    事务的原子性是指事务中包含的所有操作要么全做，要么不做,也就是说所有的活动在数据库中要么全部反映,要么全部不反映,以保证数据库的一致性。  

2、  一致性(Consistency)  

    事务的一致性是指数据库在事务操作前和事务处理后,其中数据必须满足业务的规则约束。  和我们的规则预期一致。 不会出现一个加 一个没减

3、  隔离性(Isolation)  

   隔离性是指数据库允许多个并发的事务同时对其中的数据进行读写或修改的能力,隔离性可以防止多个事务的并发执行时,由于它们的操作命令交叉执行而导致数据的不一致性。   Oracle支持 read commited 和序列化

4、  持久性(durability)  

    事务的持久性是指在事务处理结束后,它对数据的修改应该是永久的。即便是系统在遇到故障的情况下也不会丢失,这是数据的重要性决定的。

4.1.2 事务的控制语句  

   在Oracle数据库中,没有提供开始事务处理语句,所有的事务都是隐式开始的,也就是说在oracle中,用户不可以显示使用命令来开始一个事务.oracle任务第一条修改数据库的语句,或者一些要求事务处理的场合都是事务的隐式开始。但是当用户想要终止一个事务处理时,必须显示使用commit和rollback语句结束。  

   Savepoint 在事务中建立一个存储的点.当事务处理发生异常而回滚事务时,可指定事务回滚到某存储点.然后从该存储点重新执行。  

   Rollback 回滚事务 取消对数据库所作的任何操作  

   Commit 提交事务 对数据库的操作做持久的保存。

 

实例4.14：设置自动提交

SET AUTOCOMMIT {ON | OFF | IMMEDIATE | n}

IMMEDIATE选项的功能与ON相同

n表示当Oracle数据库成功执行了n条insert,update,delete或PL/SQL程序块时自动提交事务

SQL> set autocommit on;

SQL>show autocommit;

Commit语句用于提交事务

使用ROLLBACK语句可以将事务回滚到事务的起点或某个保存点开始

定义保存点

保存点定义了事务中的一个位置，使用Rollback语句可以将数据会滚到保存点的位置，从而实现事务的部分回滚功能。

savepoint <回滚点>

rollback to <回滚点>

查看事务信息

查看事务信息使用视图V$TRANSACTION,这个视图必须使用sys或system访问,因为当前事务是匿名事务，所以name字段值为空

前面的实例都是匿名事务，其实可以给事务命名

SET TRANSACTION NAME '事务名称'

通过plsql块 演示事务declarebegin insert into dept values(33,'xx','ss');insert into dept values(10,'xx','ss');commit;exception when others thenrollback;end;

4.1.1 事务和锁

当多个用户同时访问Oracle数据库，执行事务操作（dml语句）时,同时可能有多个用户访问同一个数据库资源，Oracle会自动在被作用的表上加锁，防止其他用户的事务改表的数据，这里对我们用户来说是非常重要的，它会避免使用的数据产生逻辑错误。

例如转账 可能出现负值。

如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。由于并发操作带来的数据不一致性包括：丢失数据修改、读”脏”数据（脏读）、不可重复读、产生幽灵数据。 　　

（1）丢失数据修改 　　

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。如上例。 　　再例如，两个编辑人员制作了同一文档的电子复本。每个编辑人员独立地更改其复本，然后保存更改后的复本，这样就覆盖了原始文档。最后保存其更改复本的编辑人员覆盖了第一个编辑人员所做的更改。如果在第一个编辑人员完成之后第二个编辑人员才能进行更改，则可以避免该问题。

（2）读“脏”数据（脏读） 　　

读“脏”数据是指事务T1修改某一数据，并将其写回磁盘，事务T2读取同一数据后，T1由于某种原因被除撤消，而此时T1把已修改过的数据又恢复原值，T2读到的数据与数据库的数据不一致，则T2读到的数据就为“脏”数据，即不正确的数据。 　　例如：一个编辑人员正在更改电子文档。在更改过程中，另一个编辑人员复制了该文档（该复本包含到目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。如果在第一个编辑人员确定最终更改前任何人都不能读取更改的文档，则可以避免该问题。 　　

（ 3）不可重复读 　　

指事务T1读取数据后，事务T2执行更新操作，使T1无法读取前一次结果。不可重复读包括三种情况： 　　事务T1读取某一数据后，T2对其做了修改，当T1再次读该数据后，得到与前一不同的值。 　　

扣款。

（4）产生幽灵数据 　　

按一定条件从数据库中读取了某些记录后，T2删除了其中部分记录，当T1再次按相同条件读取数据时，发现某些记录消失 　　T1按一定条件从数据库中读取某些数据记录后，T2插入了一些记录，当T1再次按相同条件读取数据时，发现多了一些记录。

 

 

 

 

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。 加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

使用锁解决不可重复读 和丢失数据修改！！

 

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

Oracle数据库的锁类型

根据保护的对象不同，Oracle数据库锁可以分为以下几大类：DML锁（data locks，数据锁），用于保护数据的完整性；DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；内部锁和闩（internal locks and latches），保护数据库的内部结构。

DML锁的目的在于保证并发情况下的数据完整性，本文主要讨论DML锁。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。 粒度

当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁 用来预防其他事务使用ddl语句来修改表结构 删除表等。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。

在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。

 

 

示例4.20：死锁实例

因为大型数据库应用系统存在并发机制，也就是说可能同时有多个会话访问同一个数据，这时可能不可避免出现死锁。例如有两个事务A和B,如果事务A已经锁定了数据库对象A(数据库对象A可能是一张表或表中的几条记录)。如果事务B也已经锁定了数据库对象B(数据库对象B可能是一张表或表中的几条记录)。此时事务A再去申请锁定数据库对象B，而同时时事务B再去申请锁定数据库对象A。两者互不相让，就产生了死锁，严重影响性能。

示例4.21：jdbc中的死锁public static void main(String[] args) {Connection ct = null;try {// 1、加在驱动Class.forName("oracle.jdbc.driver.OracleDriver");// 得到连接ct = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");// 从下面开始和sql一样// 加入事务处理ct.setAutoCommit(false);Statement sm = ct.createStatement();sm.executeUpdate("update dept set loc='shanghai' where deptno=10");Thread.sleep(4000);sm.executeUpdate("update dept set loc='beijing' where deptno=20");} catch (Exception e) {e.printStackTrace();}finally{System.out.println("事务1结束！！！！");try {ct.commit();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

创建第二个main函数，将Statement类对象sm执行的SQL语句调换一下，就可以模拟死锁。

也可以设置隔离级别来达到控制并发的目的 其实隔离级别的原理就是使用不同的锁策略来达到目的：

Read commited ： 这种隔离级别指的是，事务只能读取已经提交的数据，（但是支持可重复读与幻读）是oracle数据库默认的隔离模式。

 SEAIALIZABLE：不允许脏读，重复读与幻想读，最高的隔离级别。这种隔离级别标明事务A在操作数据库的时候好像就只有事务A在操作，没有其他事务在操作数据库一样。

     Oracle 中是这样实现 SERIALIZABLE 事务的：原本通常在语句级得到的读一致性现在可以扩展到事务级。也就是在事务执行的那一刻，将这个事务将要操作的数据拍了一张照片。

级别越高 并发性越低 完整性一致性越高。

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

l         SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 

 

 

 

Ø 本章总结

Ø PL/SQL是Oracle在标准SQL语言上的过程性扩展，它允许嵌入SQL语句、定义变量和常量、使用过程语言结构（条件分支语句和循环语句）、使用异常来处理Oracle错误等

Ø PL/SQL块可以分为匿名块、命名块、子程序、程序包，触发器5种

Ø 针对一些常见的错误，PL/SQL预定义了一些异常错误。当PL/SQL程序违反了Oracle的规定或超出了系统规定的限制时，就会隐含地引发一个预定义的错误

Ø 为了具体的业务规则、编程和调试的需要，程序员可以自定义一些异常。自定义异常必须进行声明，并且必须使用RAISE语句显式地触发。

Ø 事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml(数据操作语言insert delete update)语句要么全部成功，要么全部失败。

Ø 事务控制语句包括commit,rollback,savepoint

任务实训部分

1：PL/SQL块中数据类型的使用

训练技能点

Ø PL/SQL块程序结构中的数据类型

需求说明

在使用记录变量插入数据和更新数据时，需注意列的顺序、个数、类型必须与记录成员

的顺序、个数、类型完全匹配。现要求实现：使用记录变量的方式来插入部门信息。

实现思路

(1) 定义一个关于dept部门表的%ROWTYPE变量。

(2) 为其中的deptno部门编号、部门名称及地址赋值。

(3) 然后通过INSERT语句插入数据。

关键代码

PL/SQL块中流程控制语句的使用

训练技能点

Ø FOR循环的使用

需求说明

在PL/SQL中，可以使用FOR循环直接访问SELECT查询获得的结果。要求查询出各个部

门工资最高的雇员信息。

实现思路

(1) 通过FOR循环访问各个部门，输出dept表中所有部门的名称

(2) 根据各个部门提供的部门编号（deptno）查询emp表中该部门工资最高的雇员。

(3) 输出该雇员信息。

通过For循环查询获得各个部门工资最高的雇员信息

异常处理

训练技能点

Ø 异常处理

需求说明

使用非预定义异常，更新特定雇员的部门号，并处理ORA-02291错误。当定义Oracle错误和异常之间的关联关系时，需要使用伪过程EXCEPTION_INIT。

实现思路

(1) 首先的定义部分定义异常。

(2) 使用progma exception_init(exception_name,exception_number)在异常和oracle错误之间建立关联，这时要求用户知道可能出现的错误号。

(3) 最终在异常处理部分捕捉并处理异常。

巩固练习

一．选择题

1. 数据库中有两种基本的锁类型是（     ）

    A. 排它锁

    B. 共享锁

    C. 只读锁

    D. 更新锁 

2. 下列选项中，不能直接嵌入PL/SQL块中的语句有（    ）。

A．SELECT

B．INSERT

C．CREATE TABLE

D．GRANT

E．COMMIT

3. 当SELECT INTO语句没有返回行时，触发的异常是时（    ）。

A．TOO_MANY_ROWS

B．VALUE_ERROR

C．NO_DATA_FOUND

    D．不会触发任何异常

4. 以下PL/SQL块的输出结果是（  ）

DECLARE

v_1 NUMBER(2);

         v_2 NUMBER(3);

BEGIN

v_2:=CASE WHEN v_1 IS NULL THEN 10 ELSE 20 end;

DBMS_OUTPUT.PUT_LINE(‘v_2:’||v_2);

END;

A．10

B．0

C．20

    D．语法错误

5. 以下PL/SQL块的输出结果是（    ）

BEGIN

    FOR I IN 1..5 LOOP

   DBMS_OUTPUT.PUT_LINE(‘*’);

    END LOOP;

END;

A．**

B．***

C．*

    *

    *

D．不会触发任何异常

 

二．操作题上机练习

由用户输入一个雇员编号，要求在emp表中查找是否存在与输入的雇员编号对应的雇员。

1.如果存在，则有以下操作：

（1）输出“查到此雇员”，并判断其部门是否为“SALES”部门。

（2）如果是“SALES”部门，则输出“此雇员属于SALES部门”，并删除对应的记录；否则输出“此雇员不属于SALES部门，不能删除！”。

2.如查不存在，则输出“找不到此雇员！”。