第一章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);oracle游标的使用
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 enam e, 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 *
FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR
SELECT *
FROM salgrade;
END IF;
...
END;
6、属性
PL/SQL的变量和游标都有着让我们能够直接引用它们各自的数据类型或结构的属性。数据库字段和表也有类似的属性。"%"是一个属性的指示符。
%TYPE
%TYPE可以提供一个变量或数据库字段的数据类型，这在声明存放数据库值的变量时是非常有用的。假设我们要声明一个存放表books中的字段my_title的字段的变量时，就可以这样使用%TYPE属性：
my_title books.title%TYPE;
这样声明my_title有两个优点，第一，我们不必知道title具体类型；第二，如果我们改变了数据库中对该字段的数据类型定义的话，my_title的数据类型会在运行时做出相应的改变。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。