PL/SQL 块简介

1、BLOCK块是PL/SQL的基本程序单元，编写PL/SQL程序实际就是编写PL/SQL块。
2、PL/SQL块由三部分组成：定义部分、执行部分、例外处理部分。
定义部分：定义常量、变量、游标、例外、复杂数据类型等；
执行部分：是想应用模块功能，包含要执行的PL/SQL/语句和SQL语句；
例外处理部分：处理执行部分可能出现的运行错误。
PL/SQL块的基本结构：
DECLARE
/*
* 定义常量、变量、游标、例外
*/
BEGIN
/*
* PL/SQL语句和SQL语句
*/
EXCEPTION
/*
*处理运行错误
*/
END;/*块结束标记*/
例：
set serveroutput on
BEGIN
   dbms_output.put_line('Hello,everyone!');
END;
/
hello,everyone!
执行这该PL/SQL快时，会输出消息“Hello,everyone!”.其中dbms_output是ORACLE所提供的系统包，put_line是该包所包含的过程，用于输出字符串信息。注意，当使用dbms_output包输出数据或消息时，必须要将SQL*PLUS的环境变量serveroutput设置为on.

 

例2：包含定义部分和执行部分的PL/SQL块
为了在PL/SQL块中使用变量、常量、例外和显式游标，开发人员必须要在定义部分定义变量、常量、例外和显示游标。如下：
DECLARE
  v_ename VARCHAR2(5);
BEGIN
  SELECT ename INTO v_ename FROM emp
  WHERE empno=&no; 

  dbms_output.put_line('雇员名：'||v_ename);
EXCEPTION
   WHEN NO_DATE_FOUND THEN
     dbms_output.put_line('请输入正确的雇员号！')；
END;
/
输入no的值：7788
雇员名：SCOTT

当执行该PL/SQL块时，会根据输入的雇员号显示雇员号。为了临时存放雇员名，就必须要定义变量。&no为SQL*PLUS的替代变量。替代变量的作用参加附录B.

 

PL/SQL块分类
1、匿名块（没有名称的PL/sql块，可以内嵌到应用程序(例如PRO*c/C++)中，也可以在交互式环境中（例如SQL*PLUS）直接使用。）
例：DECLARE
      v_avgsal NUMBER(6,2);
    BEGIN
      SELECT avg(sal) INTO v_avgsal FROM emp
      WHERE deptno=&no;
      dbms_output.put_line('平均工资：'||v_avgsal);
    END;
    /
输入no的值：10
平均工资：2916.67
2、命名块（具有特定名称标识的PL/SQL块，与匿名块非常类似，只不过在PL/SQL块前使用<<>>加以标记。当使用嵌套块时，为了区分多级嵌套层次关系，可以使用命名块加以区分。）
例：<<outer>>
DECLARE
v_deptno NUMBER(2);
v_dname VARCHAR(10);
BEGIN
<<inner>>
BEGIN
   SELECT deptno INTO v_deptno FROM emp
   WHERE lower(ename)=lower('&name');
END;--<<inner>>
   SELECT dname INTO v_dname FROM dept
   WHERE deptno=v_deptno;
   dbms_output.put_line('部门名：'||v_dname);
END;--<<outer>>
/
输入name的值：scott
部门名：RESEARCH

<<outer>>和<<inner>>分别是主块（外层块）和子快（内层块）的标记。
3、子程序
包括过程、函数和包。
过程：用于执行特定操作。当建立过程时，既可以指定输入参数IN，也可以指定输出参数OUT。
CREATE PROCEDUCRE update_sal(name VARCHAR2,newsal NUMBER)
IS
BEGIN
  UPDATE emp SET sal=newsal
  WHERE lower(ename)=lower(name);
END;
/
过程update_Sal用于更新雇员工资。可以使用execute命令或call命令。
如：SQL>exec update_sal('scott',2000)
SQL>call update_sal('scott',2000)

 

函数：用于返回特定数据。
CREATE FUNCTION annual_income (name VARCHAR2)
RETURN NUMBER IS
   annual_slary NUMBER(7,2);
BEGIN
   SELECT sal*12+nvl(comm,0) INTO annual_salary
  FROM emp WHERE lower(ename）=lower(name);
  RETURN annual_salary;
END;
/
nvl(comm,0),如果comm为空，程序自动使用0值来表示该值，如果不空就使用该值。
上例，函数annual_income用于返回雇员的全年收入（包括工资和奖金）。
调用函数：SQL>VAR income NUMBER
          SQL>CALL annual_income('scott') INTO income;
          SQL>PRINT income
             INCOME
         --------------------
           24000

包：用于逻辑组合相关的过程和函数，它由包规范和包体两部分组成。包规范用于定义公用的常量、变量、过程和函数。使用CREATE PACKAGE命令
CREATE PACKAGE emp_pkg IS
 PROCEDURE update_sal(name VARCHAR2,newsal NUMBER);
 FUNCTION annual_income(name VACHAR2) RETURN NUMBER;
END;
/
包规范之包含了过程和函数说明，而没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。使用CREATE PACKAGE BODY命令。
CREATE PACKAGE BODY emp_pkg IS
PROCEDURE update_sal(name VARCHAR2,newsal NUMBER);
IS
BEGIN
   UPDATE emp SET sal=newsal
   WHERE lower(ename)=lower(name);
END;
FUNCTION annual_income(name VACHAR2) RETURN NUMBER
IS
 annual_salary NUMBER(7,2);
BEGIN
 SELECT sal*12++nvl(comm,0) INTO annual_salary
  FROM emp WHERE lower(ename）=lower(name);
  RETURN annual_salary;
END;
END;
/

当调用包的过程和函数时，在过程和函数名之前必须要带有包名作为前缀（包名.子程序名），而如果要访问其他方案的包，还必须要加方案名作为前缀（方案名.包名.子程序名）。
例如：SQL> call emp_pkg.update_sal('scott',1500);
SQL>VAR income NUMBER
SQL>CALL emp_pkg.annual_income('scott') INTO:income;
SQL>PRINT income
   INCOME
 ------------
 18000

 

触发器
触发器是指隐含执行的存储过程。当定义触发器时，必须要指定触发事件以及触发操作，常用的触发事件包括INSERT,UPDATE,DELETE语句，而触发操作实际上是一个PL/SQL块。建立触发器使用CREATE TRIGGER命令来完成。
例：CREATE TRIGGER update_cascade
AFTER UPDATE OF deptno ON dept FOR EACH ROW
BEGIN
  UPDATE emp SET deptno=:new.deptno
  WHERE deptno=:old.deptno;
END;
/
如上例所示，触发器update_cascade用于实现级联更新，如果不建立该触发器，那么当更新dept表中 的deptno列数据时就会显示错误“ORA-02292:违反完整约束条件（SCOTT.FK_DEPTNO）-已找到子记录日志”，而在建立了该触发器之后，当更新deptno列时，就会级联更新emp表中的deptno列的相关数据。