PL/SQL基础
一、PL/SQL快结构
PL/SQL块由三个部分组成:定义部分、执行部分、异常处理部分。定义部分用于定义常量、变量、游标、异常、复杂数据类型等;
执行部分用于实现应用模块功能,该部分包含了要执行的PL/SQL语句和SQL语句;异常处理部分用于处理执行部分可能出现的运行错误。
PL/SQL块的基本结构如下所示

DECLARE
         /*
         * 定义部分————定义常量、变量、复杂数据类型、游标
         */
     BEGIN
         /*
         * 执行部分————PL/SQL语句和SQL语句
         */
     EXCEPTION
         /*
         * 异常处理部分————处理运行错误
         */
     END;  /*快结束标记*/
 如上所示,DECLARE关键字用于开始定义部分(可选),BEGIN关键字用于开始执行部分(必需),EXCEPTION关键字用于开始异常处理部分(可选),
 END为PL/SQL块的结束标记。注意,DECLARE、BEGIN、EXCEPTION后面没有分号(;),而END后则必须带有分号(;)。
 1.示例一,只包含执行部分的PL/SQL块
 当编写PL/SQL块时,执行部分是必需的,而其他俩个部分是可选的。执行部分用于编写要执行的PL/SQL语句和SQL语句。
 例:
     set serveroutput on
     BEGIN
         dbms_output.put_line('Hello,everyone!');
     END;
     /
 如上所示,dbms_ouput是用于输出信息的PL/SQL系统包,put_line是该包所包含的过程。
 注意,当使用dbms_output包输出信息时,必需将SQL*PLUS环境变量serveroutput设置为on。
 2.示例二,包含定义部分和执行部分的PL/SQL块
 为了在PL/SQL块中使用变量、常量、异常和显示游标,应用开发人员就必须在定义部分定义变量、常量、异常和显示游标。
 例:
     set verify off
     DECLARE
         v_ename VARCHAR2(5);
     BEGIN
         SELECT ename INTO v_ename FROM emp
         WHERE empno=&no;
         dbms_output.put_line('雇员名:'||v_ename);
     END;
     /
 3.示例三,包含定义部分、执行部分和异常处理部分的PL/SQL块
 为了避免PL/SQL的运行错误,提高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_DATA_FOUND THEN
             dbms_output.put_line('请输入正确的雇员号!');
     END;
     /
 二、PL/SQL块分类
 当使用PL/SQL开发应用模块时,根据需要实现的应用模块功能,可以将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;
 /
 2.命令块
 命令块是指具有特定名称标识的PL/SQL块,命名块与匿名块非常类似,只不过在PL/SQL块前使用<<>>加以标记。
 当使用嵌套快时,为了区分多级嵌套层次关系,可以使用命名块加以区分。
 例:
     <<outer>>
     DECLARE
         v_deptno NUMBER(2);
         v_dname VARCHAR2(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>>
     /
 3.子程序
 子程序是指存储在数据库或者客户端的PL/SQL过程、函数和包。
 通过将商业逻辑和企业规则集成到PL/SQL子程序,不仅可以简化客户端程序的开发和维护,而且可以提高应用程序的性能
 ①过程。过程用于执行特定操作。下面以建立用于更新雇员工资的过程update_sal,并使用该过程修改SCOTT工资为例,
 说明在SQL*Plus中建立和使用过程的方法。
 例:
     CREATE PROCEDURE update_sal1(name VARCHAR2,newsal NUMBER)
     IS
     BEGIN
         UPDATE emp SET sal=newsal WHERE lower(ename)=lower(name);
     END;
     /
     exec update_sal1('scott',2000)
 ②函数。函数用于返回特定数据。当建立函数时,函数头部必须包含RETURN子句,并且函数体必须包含RETURN语句。
 例:
     CREATE FUNCTION annual_income(name VARCHAR2) 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;
     /
     select annual_income('SCOTT') 年收入 FROM dual;
     ③包。包用于逻辑组合相关的过程和函数,它由包规范和包体两部分组成。包规范用于定义公用的常量、变量、过程和函数。
     而包体则用于实现包规范中的过程和函数。
     CREATE PACKAGE emp_pkg IS
         PROCEDURE update_sal(name VARCHAR2,newsal NUMBER);
         FUNCTION annual_income(name VARCHAR2) RETURN NUMBER;
     END;
     /
     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 VARCHAR2) 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;
     /
     exec emp_pkg.update_sal('scott',1500)
     SELECT emp_pkg.annual_income('scott') 年收入 FROM dual;
 4.触发器
 触发器是指被隐含执行的PL/SQL块。当触发了与触发器相关的事件之后,Oracle会隐含执行触发器的PL/SQL块。
 例:
     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 dept SET deptno=60 WHERE dname='ACCOUNTING';
     SELECT ename FROM emp WHERE deptno=60;