第一章 PL/SQL 一览
一、理解 PL/SQL 的主要特性
了解 PL/SQL 最好的方法就是从简单的实例入手。下面的程序是用于处理一个网球拍订单的。首先声明一个 NUMBER 类型
的变量来存放现有的球拍数量。然后从数据表 inventory 中把球拍的数量检索出来。如果数量大于零，程序就会更新
inventory 表，并向 purchase_record 表插入一条购买记录，如果数量不大于零，程序会向 purchase_record 表
插入一条脱销(out-of-stock)记录。
DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity
INTO qty_on_hand
FROM inventory
WHERE product = 'TENNIS RACKET'
FOR UPDATE OF quantity;
IF qty_on_hand > 0 THEN -- check quantity
UPDATE inventory
SET quantity = quantity - 1
WHERE product = 'TENNIS RACKET';
INSERT INTO purchase_record
VALUES ('Tennis racket purchased', SYSDATE);
ELSE
INSERT INTO purchase_record
VALUES ('Out of tennis rackets', SYSDATE);
END IF;
COMMIT;
END;
在 PL/SQL 中，可以使用 SQL 语句来操作 Oracle 中的数据，并使用流程控制语句来处理数据。我们还可以声明常量和
变量，定义函数和过程并捕获运行时错误。因此，PL/SQL 是一种把 SQL 对数据操作的优势和过程化语言数据处理优势结
合起来的语言。
1、块结构
PL/SQL 是一种块结构的语言，它的基本组成单元是一些逻辑块，而这些块又能嵌套任意数量子块。通常，每一个逻辑块
都承担一部分工作任务， PL/SQL 这种将问题分而治之(divide-and-conquer)的方法称为逐步求精(stepwise
refinement)。块能够让我们把逻辑相关的声明和语句组织起来，声明的内容对于块来说是本地的，在块结构退出时它
们会自动销毁。
如下图所示，一个块分为三个部分：声明，处理，异常控制。其中，只有处理部分是必需的。首先程序处理声明部分，然
后被声明的内容就可以在执行部分使用，当异常发生时，就可以在异常控制部分中对抛出的异常进行捕捉、处理。
我们还可以在处理部分和异常控制部分嵌套子块，但声明部分中不可以嵌套子块。不过我们仍可以在声明部分定义本地的
子程序，但这样的子程序只能由定义它们的块来调用。
2、变量与常量
PL/SQL 允许我们声明常量和变量，但是常量和变量必须是在声明后才可以使用，向前引用(forward reference)是不
允许的。
• 变量声明
变量可以是任何 SQL 类 型，如 CHAR，DATE 或 NUMBER 等，也可以是 PL/SQL 类型，BOOLEAN 或 BINARY_INTEGER 等。
声明方法如下：
part_no NUMBER(4);
in_stock BOOLEAN;
我们还可以用 TABLE、VARRAY 和 RECORD 这些复合类型来声明嵌套表、变长数组(缩写为 varray)和记录。
• 变量赋值
我们可以用三种方式为变量赋值，第一种，直接使用赋值操作符":="：
tax := price * tax_rate;
valid_id := FALSE;
bonus := current_salary * 0.10;
wages := gross_pay(emp_id,
st_hrs,
ot_hrs
) - deductions;
第二种，利用数据库中查询的结果为变量赋值：
SELECT sal * 0.10
INTO bonus
FROM emp
WHERE empno = emp_id;
第三种，把变量作为一个 OUT 或 IN OUT 模式的参数传递给子程序，然后由子程序为其赋值。如下例所示，IN OUT 参数
可以为被调用的子程序传递初始值然后子程序将更新后的新值返回给调用程序：
DECLARE
my_sal REAL(7,2);
PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ...
BEGIN
SELECT AVG(sal)
INTO my_sal
FROM emp;
adjust_salary(7788, my_sal); -- assigns a new value to my_sal
• 声明常量
声明常量跟声明变量类似，但是要加一个 CONSTANT 关键字，并在声明时为其赋上初始值。下例中，我们声明一个名为
credit_limit 的常量：
credit_limit CONSTANT REAL := 5000.00;
3、游标
Oracle 使用工作区(work area)来执行 SQL 语句，并保存处理信息。PL/SQL 可以让我们使用游标来为工作区命名，
并访问存储的信息。游标的类型有两种：隐式和显式。 PL/SQL 会为所有的 SQL 数据操作声明一个隐式的游标，包括只
返回一条记录的查询操作。对于返回多条记录的查询，我们可以显式地声明一个游标来处理每一条记录。如下例：
DECLARE
CURSOR c1 IS
SELECT empno, ename, job
FROM emp
WHERE deptno = 20;
由多行查询返回的行集合称为结果集(result set)。它的大小就是满足我们查询条件的行的个数。如下图所示，显式游
标"指向"当前行的记录，这可以让我们的程序每次处理一条记录。
多行查询处理有些像文件处理。例如，一个 COBOL 程序打开一个文件，处理记录，然后关闭文件。同样，一个 PL/SQL
程序打开一个游标，处理查询出来的行，然后关闭游标。就像文件指针能标记打开文件中的当前位置一样，游标能标记出
结构集的当前位置。
我们可以使用 OPEN，FETCH 和 CLOSE 语句来控制游标，OPEN 用于打开游标并使游标指向结果集的第一行，FETCH 会检
索当前行的信息并把游标指移向下一行，当最后一行也被处理完后，CLOSE 就会关闭游标。
4、游标 FOR 循环
在大多需要使用显式游标的情况下，我们都可以用一个简单的游标 FOR 循环来代替 OPEN，FETCH 和 CLOSE 语句。首先，
游标 FOR 循环会隐式地声明一个代表当前行的循环索引(loop index)变量。下一步，它会打开游标，反复从结果集中
取得数据并放到循环索引的各个域(field)中。当所有行都被处理过以后，它就会关闭游标。下面的例子中，游标 FOR
循环隐式地声明了一个 emp_rec 记录：
DECLARE
CURSOR c1 IS
SELECT ename, sal, hiredate, deptno
FROM emp;
...
BEGIN
FOR emp_rec IN c1 LOOP
...
salary_total := salary_total + emp_rec.sal;
END LOOP;
END;
为了使用每一个查询到的行中的每一个字段，我们可以使用点标志(dot notation)，它的作用就像一个域选择器。
5、游标变量
游标变量的使用方式和游标类似，但更加灵活，因为它不绑定于某个特定的查询，所以可用于打开任何返回类型相兼容的
查询语句。游标变量是真正的 PL/SQL 变量，我们可以为它赋值，把它传递给子程序。如下例，我们把游标变量作为存储
过程 open_cv 的一个参数传进去，程序执行时，可以根据 choice 值的不同，灵活地打开不同的查询内容。
PROCEDURE open_cv(generic_cv IN OUT genericcurtyp, choice NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR
SELECT *
FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR
SELECT *
评论0