PL/SQL概述

在SQL之类的非过程化语言中，指定的是要操作的数据集，而不是要执行什么操作或该操作应如何执行。在过程化语言程序中，大多数语句的执行取决于其前面或后面的语句及控制结构，如循环或条件分支，而这在 SQL 中是不可用的。

为演示过程化语言与非过程化语言之间的区别，假设如下的SQL 语句查询employees表：

SELECT employee_id, department_id, last_name, salary FROM employees;

前面的语句只是请求数据，但并不会在数据上应用逻辑。但是，假设您希望应用程序能够确定在数据集中的每个员工是否应该基于工资和部门绩效来提高待遇。加薪的必要条件是，在过去五年中，加薪没有超过三次以上的雇员。如果要加薪，应用程序必须调整工资并发送电子邮件给经理 ；否则，该应用程序必须更新一份报告。

问题是，需要条件逻辑和程序流控制的过程化数据库应用程序，如何才能使用 SQL。基本的开发方法如下所示：

 使用客户端编程，将SQL 语句嵌入到由过程化语言如 C、 C++、 或 Java等编写的应用程序中

可以将SQL 语句置入到源代码中，并在编译之前将其提交给预编译器或 Java 翻译器。或者，您可以消除预编译步骤，而使用如 Java 数据库连接 (JDBC) 或 Oracle 调用接口 （OCI） 这样的 API ，使应用程序能够与数据库进行交互。

 使用服务器端编程，开发驻留在数据库中的数据逻辑

应用程序可以显式地调用以 PL/SQL (发音为P L sequel) 或 Java 编写的存储子程序（过程和函数）。您还可以创建一个触发器，它是一个存储在数据库中的命名程序单元，在响应指定的事件时调用。

服务器端编程的主要好处，是内置于数据库的功能可以在任意位置部署。由数据库（而不是由应用程序）确定在给定的操作系统上执行任务的最佳方式。此外，通过在服务器上集中应用程序处理，子程序增加可伸缩性，使客户端能够重用代码。由于子程序调用快速而高效，仅仅一个调用就可以启动一个计算密集型的存储子程序，减少了网络流量。

在Oracle数据库中可以使用以下语言来存储数据逻辑：

 PL/SQL

PL/SQL 是 Oracle数据库针对SQL的过程化扩展。PL/SQL 与数据库集成，支持所有 Oracle SQL 语句、 函数、和数据类型。由数据库 API编写的应用程序，可以调用 PL/SQL 存储子程序，或发送PL/SQL 代码块到数据库以被执行。

 Java

Oracle 数据库还对开发、 存储、和部署 Java 应用程序提供支持。Java 存储子程序在数据库中运行，并独立于在中间层上运行的程序。Java存储子程序使用与 PL/SQL 类似的运行模型来与SQL接口。

PL/SQL概述

PL/SQL 提供了一种服务器端的，存储的过程化语言，它易于使用并与 SQL无缝集成，而且健壮、可移植、且安全。您可以使用被称为 PL/SQL 程序单元的过程化模式对象，来访问和操作数据库数据。

PL/SQL 程序单位一般分为如下几类：

 PL/SQL 子程序，它存储在数据库中，可以从应用程序按名称调用。当你创建一个子程序时，数据库解析该子程序，并将其已解析的表示形式存储在数据库中。您可以将一个子程序声明为一个过程或函数。

 PL/SQL匿名块，它出现在您的应用程序中，没有名字，也不存储在数据库中。在很多的应用程序中 ，PL/SQL 块可以出现在任何 SQL 语句可以出现的地方。

Oracle SQL Developer中嵌入了PL/SQL 编译器和解释器，给与开发人员一个在客户端和服务器上一致和有效的开发模式。此外，PL/SQL 存储过程可从多种数据库客户端调用，比如Pro*C、 JDBC、 ODBC 、或OCI，以及Oracle Reports和 Oracle Forms等。

PL/SQL子程序

PL/SQL 子程序是一个命名的 PL/SQL 块，允许调用方提供参数（可以是输入参数、 输出参数、或输入输出参数值）。子程序解决某一具体问题，或执行相关的任务，并作为模块化的、 可维护的数据库应用程序的构造块。

子程序是一个过程或函数。过程和函数基本相同，区别在于函数始终向调用者返回单个值，而过程不返回任何值。

PL/SQL 子程序的优势

服务器端编程相对于客户端编程有很多的好处。PL/SQL 子程序提供了以下优势：

 提高性能

o 相比发出一个个 SQL 语句，或将整个 PL/SQL 块的文本发送到数据库中，应用程序必须通过网络发送的信息的量是很小的，因为信息只发送一次，并随后在使用时才调用它。

o 已编译形式的过程在数据库中是随时可用的，所以在执行时不需要编译。

o 如果该过程已在SGA的共享池中，则数据库不需要从磁盘检索它，就可以立即开始执行。

 内存分配

因为存储过程利用了数据库的共享内存功能，由对多个用户执行的同一过程，数据库只须将该过程加载一次到内存中。在多个用户间共享代码，使应用程序对数据库内存的要求大幅减少。

 提高生产率

存储过程增加了开发生产率。通过围绕一组通用的过程来设计应用程序，您可以避免冗余编码。例如，您可以编写过程，以处理employees表中的行。任何应用程序可以调用这些过程，而无需重写 SQL 语句。如果数据管理方法改变了，则只须修改过程，而不必修改使用过程的应用程序。

存储过程也许是实现代码重用的最好方式。因为连接到数据库的任何客户端应用程序，无论它是用何种语言编写的，都可以调用存储过程，存储过程提供了在所有环境中最大的代码重用。

 完整性

存储过程能改进您的应用程序完整性和一致性。通过围绕一组公用的过程开发应用程序，您可以减少编码错误的可能性。

例如，您可以测试一个子程序，来保证它将返回一个准确的结果，验证后，可以在任意数量的应用程序中重用它，而无需重新测试。如果该过程所引用的数据结构改变了，则只须重新编译该过程。调用该过程的应用程序不需要修改。

 定义者权限程序的安全性

存储过程有助于强制执行数据安全 。定义者权限过程按其所有者的权限来执行，而不是按当前用户的权限。因此，您可以通过让用户只通过按定义者权限运行的过程和函数来访问数据，以限制用户执行的数据库操作。

例如，您可以只授予用户访问更新表的过程的权限，而不授予其访问表本身的权限。当用户调用该过程时，它按其所有者的权限运行。只有权运行该过程（但对其基础表不具有查询、 更新、或删除权限）的用户可以调用该过程，但不能以除此之外的任何其他方式操作表数据。

 继承权限与调用者权限过程的模式上下文

调用者权限过程以当前用户的权限在当前用户的模式中执行。换句话说，调用者权限过程不依赖于特定用户或模式。调用者权限过程调用使应用程序开发人员更易于集中处理应用程序逻辑，但是其基础数据可能会分散到多个用户模式。

例如，一个hr_manager 用户可以运行更新过程来更新在hr.employees 表中的薪水，而一个 hr_clerk用户运行相同的过程却只能更新地址数据。

示例：

create [or replace] procedure procedure_name

authid definer | current_user

authid definer：这是默认选项，以定义者的权限运行子程序。authid current_user：以执行者权限运行

如果是以执行者权限运行的话，可能会因为对子程序内的对象权限不足而不能执行子程序。但这样会因不同用户来执行该子程序而不同。

PL/SQL 子程序的创建

在模式级别使用CREATE PROCEDURE 或 CREATE FUNCTION语句创建的一个子程序叫做独立存储子程序。在一个包中定义的子程序称为包子程序，并被认为是包的一部分。数据库将子程序作为模式对象存储在数据字典中。

每个子程序有一个规范，包括一些参数说明和一个程序体。例如，示例8-1 显示了创建独立 PL/SQL 过程 hire_employees 的语句的一部分。该过程向employees表中插入一行。

CREATE PROCEDURE hire_employees 
(p_last_name VARCHAR2, p_job_id VARCHAR2, p_manager_id NUMBER, p_hire_date DATE,
p_salary NUMBER, p_commission_pct NUMBER, p_department_id NUMBER)
IS
BEGIN
.
.
.
INSERT INTO employees (employee_id, last_name, job_id, manager_id, hire_date,
salary, commission_pct, department_id)
VALUES (emp_sequence.NEXTVAL, p_last_name, p_job_id, p_manager_id, p_hire_date,
p_salary, p_commission_pct, p_department_id);
.
.
.
END;

PL/SQL 子程序的执行

用户可以通过以下方式交互地执行一个子程序：

 使用 Oracle 工具，如 SQL * Plus 或 SQL Developer

 在一个数据库应用程序（如 Oracle Forms或预编译器应用程序）代码中显式调用它

 在另一个过程或触发器的代码中显式调用它

图 8-1 显示了调用 hire_employees的不同数据库应用程序。

另外，有权限的用户可以使用 Oracle 企业管理器或 SQL*Plus使用如下的语句来运行 hire_employees 过程：

EXECUTE hire_employees ('SMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20);

前面的语句在employees表中为TSMITH插入一条新记录。

存储过程依赖于在其主体中引用的对象。数据库自动跟踪和管理这些依赖。例如，hire_employees过程引用了employees表，如果您更改了此表的定义且影响到该过程，则该过程必须重新编译以验证它仍然工作正常。通常，数据库自动管理这种依赖关系。

PL/SQL包

PL/SQL 包是一组相关子程序，与其使用的游标和变量一起，作为一个单元存储在数据库中。打包的子程序可以由应用程序或用户显式调用。

Oracle 数据库包括许多原厂商提供的软件包，扩展了数据库的功能，并提供对 SQL 功能的 PL/SQL 访问。例如，UTL_HTTP 包使你能够从 PL/SQL 和 SQL 访问发出HTTP调用，以访问在因特网上的数据，或调用 Oracle Web Server模块。你可以在创建应用程序时使用这些原厂商提供的软件包，或当创建您自己的存储过程时将其作为思维来源。

PL/SQL 包的优势

PL/SQL 软件包提供了以下优点：

 封装

包使您能够封装或分组存储过程、 变量、数据类型等等，并存储为命名单元。封装为开发过程提供了更好地组织形式，也提供了更大的灵活性。您可以创建规范，只引用公共过程而无需实际创建包体。封装简化了权限管理。对包进行授权，使其结构元素可以由被授权者访问。

 数据安全

定义包的方法使您能够指定哪些变量、游标过程是公共的或私有的。公有的意味着包的用户可以直接访问它。私有的意味着它对于包的用户是隐藏的。

例如，一个包可能包含 10 个过程。您可以定义包，使其只有三个过程是公共的，且可由包的用户执行。其余的过程是私有的，且只可由包中的过程访问。不要将公有或私有的包变量与对PUBLIC的授权相混淆。

 更好的性能

当包中的某个过程第一次被调用时，整个包被加载到内存中。这种加载在一个操作中完成，不同于加载多个独立过程需要分别加载。当调用到包中的相关过程时，只需直接运行内存中的已编译代码，而无需额外磁盘I/O。

包体可以被替换并重新编译，而不会影响其规范。这样，（总是通过该规范）引用包的结构元素的模式对象不需要被重新编译，除非包规范也被替换了。通过使用包，可以将不必要的重新编译减小到最少，结果对数据库的总体性能影响就比较小。

PL/SQL 包的创建

分两个部分创建包： 包规范和包体。包规范声明包的所有公有结构，而包体定义包的所有（公有的和私有的）结构。

示例8-1 显示了用于创建employees_management包规范的语句的一部分，封装了用于管理雇员的几个子程序。包的每个部分用不同的语句创建。

示例 8-2 PL/SQL 包

CREATE PACKAGE employees_management AS
FUNCTION hire_employees (last_name VARCHAR2, job_id VARCHAR2, manager_id NUMBER,
salary NUMBER, commission_pct NUMBER, department_id NUMBER) RETURN NUMBER;
PROCEDURE fire_employees(employee_id NUMBER);
PROCEDURE salary_raise(employee_id NUMBER, salary_incr NUMBER);
.
.
.
no_sal EXCEPTION;
END employees_management;

此规范声明了函数 hire_employees、 过程 fire_employees 和 salary_raise、以及异常 no_sal。所有这些公有程序对象，对有权访问该包的用户都是可用的。

CREATE PACKAGE BODY命令定义在规范中声明的对象。包体必须在和包相同的模式中创建。在创建包之后，您就可以开发应用程序，并在其中调用这些公共过程或函数，或抛出包中的任何公共例外。

PL/SQL 包子程序的执行

您可以从数据库触发器、 存储子程序、 3GL 应用程序、和 Oracle 工具引用包内容。图 8-2 显示了数据库应用程序调用employees_management 包中的过程和函数。

图 8-2 在PL/SQL 包中调用子程序

数据库应用程序在必要时显式调用包内的过程。在取得对employees_management 包的授权后，用户可以显式运行其中的任何过程。例如， SQL * Plus 可以发出以下语句来运行hire_employees 包过程：

EXECUTE employees_management.hire_employees ('TSMITH', 'CLERK', 1037, SYSDATE, 500, NULL, 20);

PL/SQL 匿名块

匿名块是一个未命名的、 非持久化的 PL/SQL 单元。匿名块的典型用途包括：

 启动调用子程序和包结构元素

 隔离例外处理

 通过在其他 PL/SQL 块中嵌套代码来管理控制

匿名块没有存储子程序的代码重用优势。表 8-1 总结了这两类程序单元的之间的差异。

匿名块包括一个可选的声明性部分、一个可执行部分、和一个或多个可选的异常处理程序。下面的示例匿名块将一个雇员的姓氏选出并存入到一个变量，并输出该姓氏：

DECLARE
v_lname VARCHAR2(25);
BEGIN
SELECT last_name INTO v_lname
FROM employees
WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee last name is '||v_lname);
END;

Oracle 数据库编译 PL/SQL 块，并将其放在SGA的共享池中，但它不会在数据库中存储其源代码或已编译版本，以便在当前运行实例之外能重复使用。与触发器不同的是，一个匿名块在它每次加载到内存中都要被重新编译。共享 SQL 允许匿名 PL/SQL 块可以在共享池中被共享和重用，直到它们被从共享池中冲掉。

PL/SQL 语言结构

PL/SQL 块可以包括各种不同的 PL/SQL 语言结构。这些结构包括：

 变量和常量

您可以在过程、 函数、或包内声明这些结构。在你需要在 SQL 或 PL/SQL 语句中捕获或提供一个值时，您可以使用变量或常量。

 游标

您可以在过程、 函数、或包中显式声明一个游标，以便对Oracle数据库数据进行面向记录的处理。PL/SQL 引擎也可以隐式声明游标。

 例外

PL/SQL 允许您显式处理例外，即在PL/SQL代码的处理过程中抛出的内部的或用户定义的错误。

PL/SQL 可以运行动态 SQL语句，其完整的文本直到运行时才知道。动态 SQL 语句被存储为字符串，可以是手工输入的或在运行时由其程序生成。这种技术使您能够创建通用过程。例如，您可以创建一个过程，用于操作一个直到运行时才知道其名称的表。

PL/SQL 集合和记录

许多编程技术都使用集合类型，如数组、 袋、 列表、 嵌套表、 集、和树等。为在数据库应用程序中支持这些技术，PL/SQL 提供了TABLE和 VARRAY数据类型，使您能够声明关联数组、 嵌套表、和变长数组。

集合

集合是一组有序的、具有相同类型的元素。每个元素都有一个唯一的下标，以确定其在该集合中的位置。若要创建一个集合，你得首先定义一个集合类型，然后声明一个该类型的变量。

集合的工作原理与第三代编程语言中的数组类似。此外，集合可以作为参数传递。所以，你可以使用他们在数据库中的表、客户端应用程序、和存储子程序等之间，移入或移出数据列。

记录

记录是一种复合变量，可以存储不同类型的的数据值，类似于 C、 C++、或 Java 中的结构类型。记录可用于容纳来自多个表行，或多个表行中的某些列的数据。

假设有某个雇员的相关数据，如名字、工资、和雇用日期。这些项的类型不同，但逻辑上相关。记录为每个项都包含了一个字段，让你可以视其为一个逻辑单元。

您可以使用 %ROWTYPE 属性来声明一条记录，以表示一个表行或从游标读取的行。对用户定义的记录，您可以声明自己的域。

PL/SQL 如何运行

PL/SQL 支持本地执行和解释执行。对解释执行，PL/SQL 源代码被编译为所谓的字节表示形式，由实现为Oracle数据库一部分的一个可移植虚拟机来运行。而本机执行为计算密集型程序单元提供了最佳性能，PL/SQL 程序源代码被直接编译为给定平台的对象代码。该对象代码被链接到Oracle数据库中。

PL/SQL 引擎是用于定义、 编译、和运行 PL/SQL 程序单元的工具。该引擎是包括数据库的许多 Oracle 产品中的一个特殊的组件。虽然许多 Oracle 产品都有PL/SQL 组件，但本节专门介绍可以存储在数据库中，并使用Oracle 数据库 PL/SQL 引擎处理的程序单元。每种 Oracle 工具的 PL/SQL 功能在相应工具的文档中描述。

图 8-3 说明了包含在Oracle数据库中的 PL/SQL 引擎。

程序单元存储在数据库中。当应用程序调用存储过程时，数据库会将已编译的程序单元加载到系统全局区（SGA）的共享池中。PL/SQL执行器和 SQL 语句执行器共同处理过程中的语句。

您可以从另一个PL/SQL 块调用存储过程，这可以是一个匿名块或另一个存储过程。例如，您可以从Oracle Forms调用存储过程。

在Oracle数据库上执行的PL/SQL 过程，可以调用一个由C 编程语言编写的并存储在一个共享库中的外部过程或函数。C例程在一个不同于数据库的单独的地址空间中运行。