Oracle DB管理数据并发处理（1）

描述锁定机制以及Oracle 如何管理数据并发处理

• 使用SQL 管理数据

• 识别和管理PL/SQL  对象

• 描述触发器和触发事件

• 监视和解决锁定冲突

• 通过SQL 处理数据

在数据库中使用基本数据操纵语言(DML) 语句来处理数据。

• INSERT 命令

• 一次创建一行。

• 插入另一个表中的多行。

使用基本的INSERT 语句一次可创建一行。如果使用所谓的子选择，则可以使用INSERT命令将一个表中的若干行复制到另一个表。这种方法又称为INSERT SELECT 语句。

如以下INSERT 命令：

insert into dept_80 (select * from employees  wheredepartment_id = 80); 

这种情况下，dept_80表拥有与employees 表完全相同的结构。如果不是这种情况，则可命名每个表中的列。SELECT 语句中选择的各个值与要插入的表中的各个列相关联。列值按INSERT 和SELECT 语句中指定的顺序进行匹配。只要数据类型完全匹配即可。

例如：

insert into just_names (first, last) (selectfirst_name, last_name from employees); 

其中just_names表中仅有的两列的数据类型与employees 表中first_name和last_name 列的数据类型相同。

使用INSERT SELECT 方法可将一个或多个表中的大量数据装入到另一个表中。

• UPDATE 命令

使用UPDATE 命令可更改表的零行或多行。

UPDATE 命令用于修改表中的现有行。UPDATE 命令所修改的行数取决于WHERE条件。

如果省略了WHERE子句，则会更改所有行。如果所有行都不满足WHERE条件，则不修改任何行。

• DELETE 命令

使用DELETE 命令可从表中删除零行或多行。

DELETE 命令用于从表中删除现有行。DELETE 命令所修改的行数取决于WHERE条件。

如果省略了WHERE子句，则删除所有行。如果所有行都不满足WHERE条件，则不删除任何行。注意，未删除任何行，这并不表示出现了错误；返回的消息仅表明从表中删除了零行。

• MERGE 命令

使用MERGE命令可通过一个命令同时执行INSERT 和UPDATE 操作。

MERGE INTO jobs j

USING (SELECT * FROM jobs_acquisition) a

ON (j.job_id=a.job_id)

WHEN MATCHED THEN UPDATE SET j.job_title=a.job_title

WHEN NOT MATCHED THEN INSERT

(j.job_id,j.job_title,j.min_salary,j.max_salary)

使用MERGE命令可在一个命令中执行UPDATE 和INSERT 。可将一个源中的数据合并到另一个源，因而可选择插入新行和更新特定列（如果行已经存在）。

请考虑以下示例。JOBS 表中的某些数据如下所示：

JOB_ID     JOB_TITLE     MIN_SALARY MAX_SALARY 

---------- -------------- --------------------

AD_PRES    President     20000      40000 

FI_ACCOUNT Accountant     4200       9000 

ST_CLERK   Stock Clerk    2000       5000 

IT_PROG    Programmer     4000      10000 

以下是JOBS_ACQUISITION 表的内容：

JOB_ID     JOB_TITLE     MIN_SALARY MAX_SALARY 

---------- -------------- --------------------

AD_PRES     VP 20000     40000 

DBA        DB Admin       4200       9000 

SA         Sys Admin      2000       5000

使用MERGE命令可将具有新JOB_ID 的所有行插入到JOBS 表中，如果JOB_ID 已存在，则使用JOB_TITLE 更新现有JOBS 行。结果“President ”职位更改为“VP”，并且添加了新职务“SA”和“DBA”。

• COMMIT 和ROLLBACK 命令

下面的命令用于结束事务处理：

• COMMIT ：使更改成为永久性更改

• ROLLBACK ：撤消更改

默认情况下，不会提交输入的每个DML 命令。几个工具（包括i SQL*Plus）提供了一些选项，使用这些选项可以基于每个命令进行提交，或基于一组命令进行提交。

在发出COMMIT 或ROLLBACK 之前，更改处于暂挂状态。仅执行更改的用户可以查看更改后的数据。其他用户可选择相同数据，但只能看到更改之前的数据。其他用户不能对别的用户已更改的数据发出DML。

默认情况下，当一个用户尝试更改另一个用户正更改的行时，此用户必须等待，直到执行更改的用户提交或回退更改为止。这由Oracle  数据库的锁定机制自动进行控制。由于锁定机制已经内置到行中，所以数据库绝不会用完锁。

• PL/SQL

Oracle 对SQL 的过程语言扩展(PL/SQL)  是第四代编程语言(4GL) 。它提供了以下功能：

• 对SQL 的过程扩展

• 平台和产品间的可移植性

• 更高级别的安全性和数据完整性保护

• 支持面向对象的编程

PL/SQL  是Oracle  专有的第四代编程语言，它提供了对SQL 的过程扩展。PL/SQL  为Oracle  数据库和应用程序提供了一种公共编程环境，适用于所有操作系统或硬件平台。借助PL/SQL，可以使用SQL 语句处理数据，并且还可以使用过程结构（如IF-THEN、CASE 和LOOP）控制编程流。另外，还可以声明常量和变量，定义过程和函数，使用集合和对象类型，以及使用陷阱来捕获运行时错误。在PL/SQL  程序中还可调用使用其它语言（如C、C++ 和Java ）编写的程序。

PL/SQL  还提供了数据保护功能。调用方不必知道要读取或处理的数据结构便可进行调用。

另外，调用方不必具有访问这些对象的权限，只需要具有执行PL/SQL  程序的权限就足够了。可以选择使用另一种模式的权限来调用PL/SQL，这种情况下，调用方必须有权执行调用程序运行期间执行的每个语句。

因为PL/SQL  代码在数据库内部运行，所以这种代码在执行数据量巨大的操作时非常有效，并且可最大程度地降低应用程序的网络通信量。

• 管理PL/SQL  对象

数据库管理员应可以：

• 找出存在问题的PL/SQL  对象

• 建议适当的PL/SQL  用法

• 将PL/SQL  对象装入到数据库中

• 协助PL/SQL  开发人员诊断故障

作为DBA，通常不负责将PL/SQL  代码装入到数据库中，也不负责协助开发人员诊断故障。另外，通常不要求DBA 使用PL/SQL  来编写应用程序，但作为DBA 应对不

同的PL/SQL  对象有足够的了解，才能为应用开发人员提供建议，也才能找出存在问题的对象。

在Database Control 中，单击“Schema（方案）”中的“Administration（管理）”标签可访问PL/SQL  对象。单击对象类型时，可查看、修改和创建选定PL/SQL  对象的类型。

• PL/SQL  对象

PL/SQL  数据库对象有多种类型：

 • 程序包：程序包是由逻辑上相关的一些过程和函数组成的集合。程序包的这一部分又称为说明，用于描述应用程序的接口；它声明了可供使用的类型、变量、常量、异常错误、游标和子程序。

• 程序包体：程序包体完整地定义了游标和子程序，因此实施了说明。程序包体包含实施明细和专用声明，这些内容不显示给调用方。

• 类型主体：类型主体是由与用户定义的数据类型相关联的一些方法（过程和函数）组成的集合。

• 过程：过程是用于执行特定操作的 PL/SQL  块。

• 函数：函数是使用 RETURN PL/SQL 命令返回单个值的PL/SQL  块。它是具有返回值的过程。

• 触发器：触发器是当数据库中发生特定事件时执行的 PL/SQL  块。这些事件可以基于表，如在表中插入行时。也可以是数据库事件，如在用户登录数据库时。

• 函数

PL/SQL  函数通常用于计算值。有许多内置函数，如SYSDATE、SUM 、AVG 和TO_DATE。开发人员还可在编写应用程序时创建自己的函数。PL/SQL  函数的代码中必须包含RETURN 语句。

如使用以下SQL 命令创建的：

CREATE OR REPLACE FUNCTION compute_tax (salaryNUMBER) 

RETURN NUMBER 

AS 

BEGIN 

IF salary<5000 THEN 

RETURN salary*.15; 

ELSE 

RETURN salary*.33; 

END IF; 

END; 

/ 

• 过程

• 用于执行特定操作

• 使用参数列表传入和传出值

• 可以使用以下命令进行调用：

– CALL 命令（属于SQL 语句）

– EXECUTE命令（属于SQL*Plus  命令）

PL/SQL  过程用于执行特定操作。与函数一样，过程可接受输入值，执行IF-THEN、CASE 和LOOP 等条件语句。

• 程序包

程序包是由函数和过程组成的集合。每个程序包应由两个对象组成：

• 程序包说明

• 程序包体

程序包是函数与过程的组合。将一些函数和过程组成一个程序包，性能和可维护性会有所提高。每个程序包应由两个独立编译的数据库对象组成：

• 程序包说明：这个对象（有时称为程序包头）的对象类型为 PACKAGE，其中只包含程序包中的过程、函数和变量的定义。

• 程序包体：这个对象的对象类型为PACKAGE BODY，包含程序包说明中定义的子程序的实际代码。

使用点符号可调用程序包中的过程和函数：

package_name.procedure or function name

在图显示的程序包中，可按如下方式调用子程序：

SQL> SELECT money.compute_tax(salary)FROM hr.employees WHERE employee_id=107;

SQL> EXECUTEmoney.give_raise_to_all; 

• 程序包说明和程序包体

程序包体：

• 与程序包说明是分开的。因此，可以更改并重新编译程序包体代码，此时不会将与程序包说明相关的其它对象标记为无效。

• 包含程序包说明中定义的子程序的代码。这是负责完成工作的部分。程序包说明表明了如何调用程序包中的子程序；程序包体是代码段。

• 只有在编译了程序包说明之后才能编译程序包体。可以在没有程序包体的情况下创建程序包说明，但不能在没有程序包说明的情况下创建程序包体。

• 通过包装可隐藏代码明细。包装是一个可打乱PL/SQL  源代码的独立程序，所以可不暴露源代码的情况下传送PL/SQL  应用程序。

• 内置程序包

• Oracle 数据库带有350  多个内置PL/SQL  程序包，

这些程序包可用于：

– 管理和维护实用程序

– 扩展功能

• 可使用DESCRIBE 命令查看子程序。

随Oracle  数据库一起提供的内置PL/SQL  程序包可用于访问扩展的数据库功能，例如高级队列、加密和文件输入/ 输出(I/O)，其中还包含许多管理和维护实用程序。

管理员可使用哪些程序包取决于数据库为之提供服务的应用程序的类型。以下是一些比较常用的管理和维护程序包：

• DBMS_STATS：用于收集、查看和修改优化程序统计信息

• DBMS_OUTPUT：通过PL/SQL  生成输出

• DBMS_SESSION ：通过PL/SQL  访问ALTER SESSION 和SET ROLE 语句

• DBMS_RANDOM：生成随机数字

• DBMS_UTILITY ：获取时间、CPU 时间和版本信息；计算散列值，以及执行许多其它功能

• DBMS_SCHEDULER ：调度可从PL/SQL  调用的函数和过程

• DBMS_CRYPTO：对数据库数据进行加密和解密

• UTL_FILE：通过PL/SQL  读写操作系统文件

• 触发器

触发器是存储在数据库中的PL/SQL  代码对象，它们会在某些事件发生时自动运行或“触发”。Oracle  数据库允许许多操作充当触发事件，包括插入到表中、用户登录数据库以及尝试删除表或更改审计设置等操作。

触发器可以调用其它过程或函数。触发器的代码最好简短一些，需要较长代码的内容尽量放置到单独的程序包中。

DBA 可使用触发器来协助执行基于值的审计、强制设置复杂约束条件，以及自动处理很多任务。

• 触发事件

有许多事件可用来触发触发器，这些事件分为三类。

•DML 事件触发器在通过语句修改数据时触发。

• DDL 事件触发器在通过语句创建或以某种方式修改对象时触发。

• 数据库事件触发器在数据库中发生特定事件时触发。

大多数触发器可指定为在事件发生前或事件发生后触发。对于DML 事件，可将触发器设计为在执行某一语句时触发一次，或者在修改每行时触发一次。