oracle（4）

第四章: 使用PL/SQL语言
课程回顾：
1、Oracle数据库中常用的函数有哪些？
2、Oracle数据库中常用的数据类型有哪些？
课程目标：
1、PL/SQL简介。
2、程序结构。
3、流程控制。
4、异常处理。
5、什么是事务。
第一节：PL/SQL简介
PL/SQL概述：
PL/SQL是Oracle在标准SQL语言上的过程性扩展，它允许嵌入SQL语句、定义常量和常量、使用过程语言结构（条件分支语句和循环语句）、使用异常来处理Oracle错误等。在任何运行Oracle的平台上，应用开发人员都可以使用PL/SQL。通过使用PL/SQL，可以在一个PL/SQL块中包含多条SQL语句和PL/SQL语句。PL/SQL可以用于创建存储过程、触发器和程序包等，也可以用于处理业务规则、数据库事件或为SQL命令的执行添加程序逻辑。PL/SQL是一种可移植的高性能事务处理程序，它支持SQL和面向对象编程，提供了良好的性能和高效的处理能力，具有以下6个方面的特点：
提高应用程序的运行能力。
提供模块化的程序设计功能。
允许定义标识符。
具有过程语言控制结构。
具有良好的兼容性。
处理运行错误。
PL/SQL块：
匿名块：匿名块是出现在应用程序中的没有名称且不存储到数据库中的块。匿名块可以出现在SQL语句出现的地方，它们可以调用其他程序，却不能被其他程序调用。
命名块：命名快是一种带有标签的匿名块，标签为块指定一个名称，其中命名块包含以下3种程序。
1、子程序：子程序是存储在数据库中的过程（procedure）、函数（function）、生成之后可以被多次执行。
2、程序包：程序包是存储在数据库中的一组子程序、变量定义。程序包中的子程序可以被其他程序包或者子程序调用。如果声明为局部子程序，则只能在定义该局部子程序的块中被调用该局部子程序。
3、触发器：触发器是一种存储在数据库中的命名块，生成之后可以被多次执行。在相应的触发器事件发生之前或之后就会被执行一次或多次（每行记录一次）。触发器事件一般是指对特定的数据库表、视图进行的操作，如INSERT、UPDATE和DELETE（被称为DML触发器）；或者对数据库级的操作，如关闭、启动、登录、退出数据库，创建对象、修改对象、删除对象等称为系统触发器。
第二节：程序结构
PL/SQL程序块由3个部分组成：定义部分、执行部分和异常处理部分。其中，定义部分用于定义常量、变量、游标、异常和复杂数据类型等；执行部分用于实现应用模块功能，该部分包含需要执行的PL/SQL语句和SQL语句；异常处理部分用于处理执行部分可能出现的运行错误。PL/SQL程序块的基本语法如下：

示例：用匿名块来表达一下接收用户输入的员工编号，输出该员工的姓名，以及处理用户输入的员工编号不存在的异常，如下图：

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

定义块常量匿名块示例：

定义块变量的匿名块示例：
接收用户输入的员工编号，查询并输出该员工的姓名和雇佣日期，处理用户输入的员工编号不存在的异常（按上面所讲语法写出示例，注释每行作用，讲解注释，赋值）

第三节：流程控制
PL/SQL数据类型：
1、PL/SQL数据类型等同于Oracle数据类型。

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

3、%ROWTYPE数据类型
如果一张表中包含较多的列，则可以使用%ROWTYPE来定义一个表示表中一行记录的变量，记录的各个列的数据类型相对应、一致。

4、record数据类型
类似高级语言中的结构
首先需要定义记录类型和记录变量
当引用记录成员时，必须将记录变量作为前缀

5、TABLE数据类型
TABLE(索引表)相当于一个键值集合，键是唯一的，用于查找对应的值。键可以是整数或字符串

条件控制：
1、条件控制—IF

示例：
员工奖金发放：
输入员工编号，如果该员工
原来没有奖金，则按照工资的10%发放
原来有奖金但不超过1000的，补到1000；
其余的按照原来奖金基础再加上10%发放；

2、条件控制——case

示例：根据员工编号输出员工工资级别（1000以下C，1-2000 B，2000以上A）

循环控制：
1、Loop end loop用法

示例：利用上面所学rowtype和table用法插入部门数据

2、While loop end loop用法

示例：（改装上述例子）

3、For in loop end loop用法;

第四节：异常处理
异常是指通过了编译的PL/SQL程序在运行时产生的错误
PL/SQL提供了一种异常错误处理机制，可以帮助实现对错误的捕获和处理
预定义异常：
针对一些常见的错误，PL/SQL预定义了一些异常错误
当PL/SQL程序违反了Oracle的规定或超出了系统规定的限制时，就会隐含地引发一个预定义的错误

异常处理的语法

示例：写个破坏唯一约束的限制

自定义异常：
在实际的PL/SQL程序开发过程中，为了具体的业务规则、编程和调试的需要，程序员可以自定义一些异常。
自定义异常必须要声明，并且必须使用RAISE语句显式地引发。
在更新表中的数据时，如果没有符合条件的记录，则不会更新数据。因为这不是错误，所以不会有任何的错误提示，但可以使用自定义错误异常的方法来提示。

第五节：什么是事务
设想网上购物的一次交易，其付款过程至少包括以下几步数据库操作：更新客户所购商品的库存信息、保存客户付款信息—可能包括与银行系统的交互、生成订单并且保存到数据库中、更新用户相关信息等，正常情况下，这些操作将顺利进行，最终交易成功，与交易相关的所有数据库信息也成功的更新。但是，如果在这一系列过程中任何一个环节出了差错，例如在更新商品库存信息时发生异常、该顾客银行卡存款不足等，都讲导致交易失败。一旦交易失败，数据库中所有信息都应该必须保持交易前的状态不变，比如最后一步更新用户信息时失败而导致交易失败，那么必须保证这笔失败的交易不影响数据库的状态—库存信息没有被更新、用户也没有付款，订单也没有生成。否则，数据库的信息将会一片混乱而不可预测。
数据库事务正是用来保证这种情况下交易的平稳性和可预测性的技术。
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml（数据操作语言insert delete update）语句要么全部成功，要么全部失败。
事务特性：
原子性(Atomicity) 事务是数据库的逻辑工作单位，事务中包括的诸多操作要么全做，要么全不做。
一致性(Consistency) 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性(Isolation) 一个事务的执行不能被其他事务干扰。
持久性(durability) 一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。
加锁是实现数据库并发控制的一个非常重要的技术:
排它锁（Exclusive Locks，即X锁）当数据对象被加上排它锁时，其他的事务不能对它读取和修改。
共享锁（Share Locks，即S锁）加了共享锁的数据对象可以被其他事务读取，但不能修改。
事务控制语句
Commit 语句用于提交事务
Rollback 语句可以将事务回滚到事务的起点或某个存储点开始，作用：回滚事务，取消对数据库所作的任何操作。
Savepoint <保存点>在事务中建立一个存储的点。当事务处理发生异常而回滚事务时，可以将数据回滚到保存点的位置，从而实现事务的部分回滚功能。
 Rollback to <回滚点>可以通过 Rollback to 回滚到这个你定义的保存点上
在一个大的事务中通过Savepoint 和Rollback to可以把一个大的事务分割为几个小事务，实现回滚小事务，rollback后整个大事务直接回滚
示例： 举例说明事务用法

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

当A窗口更新数据1时（没提交没回滚） 同时更新B窗口的数据4
当B窗口更新数据4时（没提交没回滚） 同时更新A窗口的数据1
就会造成事务死锁
总结：
PL/SQL基本语法
常量、变量命名:变量名称或者常量名称 【constant】 data_type ：=
程序结构：declare begin exception end；
条件判断、循环:if then elsif…then end if;case when then … end case;loop exit[when 表达式] end loop；while … loop end loop;for [循环变量] in 初值表达式…终值表达式 loop [语句段] end loop;
PL/SQL异常处理
预定义异常、自定义异常
Oracle事务
事务的特性ACID
commit、savepoint、rollback to
事务的死锁
大型数据库应用系统存在并发机制，也就是说可能同时有多个会话访问同一个数据，这时可能不可避免出现死锁。