Oracle PL/SQL 学习笔记（三）

丽5057

　　3.PL/SQL基础
　　3.1PL/SQL块简介
　　块(BLOCK)是PL/SQL的基本程序单元，编写PL/SQL程序就是编写PL/SQL块。
　　3.1.1PL/SQL块结构
　　PL/SQL由3个部分组成：定义部分、执行部分、例外处理部分。
　　定义部分：定义常量、变量、游标、例外、复杂数据类型等。
　　执行部分：实现应用模块功能，该部分包含要执行的PL/SQL语句和SQL语句。
　　例外处理部分：处理执行部分可能出现的运行错误。
　　基本结构：
　　DECLARE
　　/* 定义部分（可选） */
　　BEGIN
　　/* 执行部分（必须） */
　　EXCEPTION
　　/* 例外处理部分（可选） */
　　END; /*块结束标记*/
　　注意：DECLARE,BEGIN,EXCEPTION后面没有分号，END后必须有分号。
　　
　　示例：
　　DECLARE
　　name VARCHAR2(100);
　　BEGIN
　　SELECT offer_name INTO name FROM bbnn
　　WHERE offer_id = &id;
　　dbms_output.put_line('套餐名：'||name);
　　EXCEPTION
　　WHEN NO_DATE_FOUND THEN
　　dbms_output.put_line('请输入正确的套餐号！');
　　END;
　　输入不正确套餐号，会提示输入正确套餐号。其中NO_DATE_FOUND是PL/SQL的预定义例外。
　　
　　3.1.2 PL/SQL块分类
　　(1)匿名块：没有名称的PL/SQL块，既可以内嵌到应用程序中，也可以在交互式环境中使用。
　　(2)命名块：具有特定标识的PL/SQL块，使用<<>>标记。
　　<<outer>>
　　DECLARE
　　v_deptno NUMBER(2);
　　v_dname VARCHAR2(10);
　　BEGIN
　　<<inter>>
　　BEGIN
　　SELECT deptno INTO v_deptno FROM emp
　　Where lower(ename)=lover(‘&name’);
　　END;--- <<inner>>
　　SELECT dname INTO v_dname FROM dept
　　WHERE deptno = v_deptno;
　　Dbms_output.put_line(‘部门名：’||v_dname);
　　END;--- <<outer>>
　　(3)子程序：包括过程、函数和包。
　　过程
　　用于执行特定操作。当建立过程时，既可以指定输入参数(IN)，也可以指定输出参数(OUT)。通过在过程中使用输入参数，可以将应用环境的数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。
　　CREATE PROCEDURE update_sal(name VARCHAR2, newsal NUMBER)
　　IS
　　BEGIN
　　UPDATE emp SET sal = newsal
　　WHERE lower(ename) = lower(name);
　　END;
　　过程update_sal用于更新雇员工资。
　　示例一：exec update_sal(‘scott’,2000)
　　示例二：call update_sal(‘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;
　　函数annual_income返回雇员的全年收入（包括工资和奖金）。
　　SQL>VAR income NUMBER
　　SQL>CALL annual_income(‘scott’) INTO : income
　　SQL>PRINT income
　　
　　包
　　包用于逻辑组合相关的过程和函数，由包规范和包体两部分组成。包规范用于定义公用的常量、变量、过程和函数。
　　CREATE PACKAGE emp_pkg IS
　　PROCEDURE update_sal(name VARCHAR2, newsal NUMBER);
　　FUNCTION annual_income(name VARCHAR2) 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 VARCHAR2) RETURN NUMBER
　　IS
　　Aunnual_salary NUMBER(7,2);
　　BEGIN
　　SELECT sal*12+nvl(comm,0) INTO annual_salary
　　FORM 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’) INFO:income;
　　SQL> PRINT income
　　
　　
　　(4)触发器：
　　触发器是指隐含执行的存储过程。当定义触发器时，必须要制定出发事件以及处罚操作，常用的触发事件包括INSERT,UPDATE和DELETE语句。触发操作实际是一个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_cascade用于实现级联更新，如果不建立该触发器，那么当更新dept表的deptno列数据时就会出错，”ORA-02292：违反完整约束条件(SCOTT.FK_DEPTNO)”。而建立触发器后，当更新deptno列时，就会级联更新emp表的deptno列的相关数据。