PL/SQL——简介&基础

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;