第一章 PL/SQL一览

第一章 PL/SQL一览

游标

游标的类型有两种：隐式和显式。PL/SQL会为所有的SQL数据操作声明一个隐式的游标，包括只返回一条记录的查询操作。对于返回多条记录的查询，我们可以显式地声明一个游标来处理每一条记录。如下例：

DECLARE
  CURSOR c1 IS
    SELECT empno, ename, job
      FROM emp
     WHERE deptno = 20;

 

游标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)，它的作用就像一个域选择器。

 

属性

PL/SQL的变量和游标都有着让我们能够直接引用它们各自的数据类型或结构的属性。数据库字段和表也有类似的属性。"%"是一个属性的指示符。

 

条件控制

IF-THEN-ELSE语句能让我们按照条件来执行一系列语句。IF用于检查条件；THEN决定在条件值为true的情况下执行；ELSE在条件值为false或null的情况才执行。看一下下面的例子，这个程序用于处理银行事务。在我们从账户3取走$500元之前，它会先确认是否有足够的资金供我们支配。如果余额足够，程序就会更新账户(accounts)表的信息，否则的话，程序会向审核(audit)表插入一条余额不足的提示信息。

 

DECLARE

  acct_balance         NUMBER(11, 2);

  acct        CONSTANT NUMBER(4)     := 3;

  debit_amt   CONSTANT NUMBER(5, 2)  := 500.00;

BEGIN

  SELECT        bal

           INTO acct_balance

           FROM accounts

          WHERE account_id = acct

  FOR UPDATE OF bal;

  IF acct_balance >= debit_amt THEN

    UPDATE accounts

       SET bal = bal - debit_amt

     WHERE account_id = acct;

  ELSE

    INSERT INTO temp

         VALUES (acct, acct_balance, 'Insufficient funds');

    -- insert account, current balance, and message

  END IF;

  COMMIT;

END; 

要从多个条件值中选出对应的操作时，我们可以使用CASE结构。CASE表达式会计算条件值，然后相应的操作(这个操作有可能是一个完整的PL/SQL块)。

 

CASE

  WHEN shape = 'square' THEN

    area    := side * side;

  WHEN shape = 'circle' THEN

    BEGIN

      area    := pi *(radius * radius);

      DBMS_OUTPUT.put_line('Value is not exact because pi is irrational.');

    END;

  WHEN shape = 'rectangle' THEN

    area    := LENGTH * width;

  ELSE

    BEGIN

      DBMS_OUTPUT.put_line('No formula to calculate area of a' || shape);

      RAISE PROGRAM_ERROR;

    END;

END CASE; 

WHILE-LOOP语句会按照某个条件值执行。每次循环条件值都会被计算一次，如果条件值为true，循环内的语句就会被执行，然后再次回到循环顶部，重新计算条件值。如果条件值为false或是null的话，循环就会停止，控制权被移交给下一语句。

下面的例子中，我们要找出第一个工资高于$2500的且行政级别高于雇员编号7499雇员的员工：

DECLARE

  salary           emp.sal%TYPE     := 0;

  mgr_num          emp.mgr%TYPE;

  last_name        emp.ename%TYPE;

  starting_empno   emp.empno%TYPE   := 7499;

BEGIN

  SELECT mgr  INTO mgr_num  FROM emp  WHERE empno = starting_empno;

  WHILE salary <= 2500 LOOP

    SELECT sal, mgr, ename

      INTO salary, mgr_num, last_name

      FROM emp

     WHERE empno = mgr_num;

  END LOOP;

  INSERT INTO temp VALUES (NULL, salary, last_name);

  COMMIT;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    INSERT INTO temp

         VALUES (NULL, NULL, 'Not found');

 

    COMMIT;

END; 

 

EXIT-WHEN语句可以在需要继续执行循环的情况下退出循环。当遇到EXIT语句时，WHEN子句中的条件值就会被计算。如果条件为true，循环就会结束，控制权交给下一条语句。下面的例子中，在total的值超过2500时，循环就会结束：

LOOP

  ...

  total    := total + salary;

  EXIT WHEN total > 25000;   -- exit loop if condition is true

END LOOP;

-- control resumes here 

GOTO语句能让我们无条件地跳转到一个标签。标签就是一个用双尖括号夹起来的未声明标示符，它必须在一个可执行语句或是PL/SQL块之前。执行时，GOTO语句将控制权交给用标签作了标记的语句或块，如下例所示：

IF rating > 90 THEN

  GOTO calc_raise;   -- branch to label

END IF;

 

<<calc_raise>>

IF job_title = 'SALESMAN' THEN   -- control resumes here

  amount    := commission * 0.25;

ELSE

  amount    := salary * 0.10;

END IF; 

 

子程序

子程序有两种，分别是函数和过程。子程序很像一段小型的程序，由可选的声明部分，执行控制部分和可选的异常控制部分组成。

PROCEDURE award_bonus(emp_id NUMBER) IS

  bonus          REAL;

  comm_missing   EXCEPTION;

BEGIN   -- executable part starts here

  SELECT comm * 0.15

    INTO bonus

    FROM emp

   WHERE empno = emp_id;

 

  IF bonus IS NULL THEN

    RAISE comm_missing;

  ELSE

    UPDATE payroll

       SET pay = pay + bonus

     WHERE empno = emp_id;

  END IF;

EXCEPTION   -- exception-handling part starts here

  WHEN comm_missing THEN

    ...

END award_bonus; 

调用时，这个过程接受一个雇员编号。它使用编号来查询雇员的commission，同时计算出15%的奖金。然后，它检查bonus总值。如果奖金为空，就会抛出异常，否则雇员的工资记录就会被更新。

 

包

PL/SQL可以让我们把逻辑相关的类型、变量、游标和子程序放在一个包内，这样更加清楚易理解。包通常有两部分组成：包说明部分和包体部分。包说明部分是应用程序的接口，它"声明"了类型、常量、变量、异常、游标和可以使用的子程序。包体用于实现包说明部分声明的子程序和游标。

 

下面的例子是把两个雇用相关的过程进行打包：

CREATE PACKAGE emp_actions AS   -- package specification

  PROCEDURE hire_employee(empno NUMBER, ename CHAR, ...);

  PROCEDURE fire_employee(emp_id NUMBER);

END emp_actions;

 

CREATE PACKAGE BODY emp_actions AS   -- package body

  PROCEDURE hire_employee(empno NUMBER, ename CHAR, ...); IS

  BEGIN

    INSERT INTO emp

         VALUES (empno, ename, ...);

  END hire_employee;

  PROCEDURE fire_employee(emp_id NUMBER) IS

  BEGIN

    DELETE FROM emp

          WHERE empno = emp_id;

  END fire_employee;

END emp_actions; 

 

只有在包说明中声明的内容对应用程序是可见的，而包体中的内容是不可见，无法访问的。包被编译后会存放到数据库中，当我们首次调用包中的子程序时，整个包就会被加载到内存当中，所以，后续的调用不再涉及到磁盘的读取问题。因此，包可以提高效率改善性能。

PL/SQL块和子程序能够在编译成可执行的形式存放起来，所以调用存储过程是快速和高效的。而且，存储过程是在服务器端执行的，减少网络流量改善响应时间。可执行的代码会被自动地放到缓存然后在多个用户间共享。