存储过程及触发器.doc

PL/SQL学习笔记(存储过程与触发器)
2008-11-04 16:28
存储过程:
将命名的 PL/SQL 块，编译并存储在数据库中
存储过程的各个部分：
声明部分
可执行部分
异常处理部分(可选)
存储过程的分类：
过程 － 执行某些操作
函数 － 执行操作并返回值
子程序(存储过程)的优点：
模块化
将程序分解为逻辑模块
可重用性
可以被任意数目的程序调用
可维护性
简化维护操作
安全性
通过设置权限，使数据更安全
过程:
过程是用于完成特定任务的子程序
创建过程的语法：
CREATE PROCEDURE <procedure_name>
(<argument>　 [IN｜OUT｜INOUT]　   ［NOCOPY］ <datatype>, ……)
AS｜IS
      <local declarations>
BEGIN
      <SQL statements>
[
EXCEPTION
      <exception handlers>
]
END;
创建过程的语法含义：
procedure_name：存储过程名
argument：参数名
[IN｜OUT｜INOUT]：IN输入参数，OUT输出参数，INOUT输入输出参数
如果使用了NOCOPY，则PL / SQL编译器将按引用传递参数，而不按值传递．
datatype：数据类型
<local declarations>：声明部分
<SQL statements>：存储过程执行的PL/SQL语句
<exception handlers>：异常处理部分
CREATE OR REPLACE PROCEDURE find_emp (emp_no NUMBER)
AS
   empname   VARCHAR2(20);
BEGIN
   SELECT ename INTO empname
FROM EMP WHERE empno = emp_no;
   DBMS_OUTPUT.PUT_LINE('雇员姓名是 '|| empname);
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('雇员编号未找到');
END find_emp;
/
过程参数的三种模式：
IN
用于接受调用程序的值
默认的参数模式
OUT
用于向调用程序返回值
IN OUT
用于接受调用程序的值，并向调用程序返回更新的值
查看过程的相关信息 ：
SQL> DESC 过程名;
删除过程：
SQL> DROP PROCEDURE 过程名;
显示过程创建时的错误：
SQL> show error;
函数:
函数是可以返回值的命名的 PL/SQL 子程序
创建函数的语法：
CREATE［OR REPLACE］ FUNCTION function_name [(parameter[, parameter]...)]
RETURN datatype
IS|AS
<local declarations>
BEGIN  
   <SQL statements>
[
EXCEPTION
<exception handlers>
]
END ;
创建函数的语法含义：
parameter 替换为以下代码:
parameter_name ［NOCOPY］ datatype
如果使用了NOCOPY，则PL / SQL编译器将按引用传递参数，而不按值传递．
定义函数的限制：
函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数
形参不能是 PL/SQL 类型
函数的返回类型也必须是数据库类型
访问函数的两种方式：
使用 PL/SQL 块
使用 SQL 语句
创建函数：
CREATE OR REPLACE FUNCTION fun_hello
RETURN VARCHAR2
IS
BEGIN
RETURN '朋友，您好';
END;
/
从 SQL 语句调用函数：
SQL> SELECT fun_hello FROM DUAL;
CREATE OR REPLACE FUNCTION   item_price_range (price NUMBER)
RETURN VARCHAR2
AS
min_price NUMBER;
max_price NUMBER;
BEGIN
SELECT MAX(ITEMRATE), MIN(ITEMRATE)
INTO max_price, min_price
FROM itemfile;
IF price >= min_price AND price <= max_price
THEN
    RETURN '输入的单价介于最低价与最高价之间';
ELSE
    RETURN '超出范围';
END IF;
END;
/
DECLARE
P NUMBER := 300;
MSG VARCHAR2(200);
BEGIN
MSG := item_price_range(300);
DBMS_OUTPUT.PUT_LINE(MSG);
END;
/
过程和函数的比较:
过 程:
作为 PL/SQL 语句执行
在规格说明中不包含 RETURN 子句
不返回任何值
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值
函 数:
作为表达式的一部分调用
必须在规格说明中包含 RETURN 子句
必须返回单个值
必须包含至少一条 RETURN 语句
触发器概念
命名的 PL/SQL 块，编译并存储在数据库中
与存储过程不同的是触发器是在事件发生时隐式（自动）触发的，而存储过程由调用者显式地调用
维护在表创建阶段通过声明限制无法实现的复杂完整性限制
通过记录修改内容和修改者来审计表中的信息
在表内容发生变更时，自动通知其他程序采取相应的处理
在订阅发布环境下，发布有关各种事件的信息
触发器创建语法 :
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} - - 触发器类型
triggering_event
[referencing_clause]
[WHEN trigger_condition] - -触发条件
[FOR EACH ROW]
trigger_body;
--其中trigger_body是触发器的主体部分，主体部分的结构如下
DECLARE
/* 声明块*/
BEGIN
/* 执行块*/
EXCEPTION
/* 异常块*/
END;
触发器的分类:
DML触发器
DML触发器由DML语句激发
可以定义DML触发器基于INSERT，UPDATE，DELETE操作触发
触发器可以在INSERT，UPDATE，DELETE操作之前或之后激发（ BEFORE | AFTER ）
触发器也可以在行或语句操作上激发（行级触发器）
inserd of 触发器
顾名思义这种触发器不是执行本来的SQL，而是用别的语句替换掉了
instead of 触发器是定义在视图上而不是表上的
系统触发器
系统触发器在发生如数据库启动或关闭等系统事件时激发，而不是在执行D M L语句时激发
系统触发器也可以在D D L操作时，如表的创建中激发
触发器的其他操作

禁用触发器 ALTER TRIGGER <trigger_name> DISABLE;
启用触发器 ALTER TRIGGER <trigger_name> ENABLE;
删除触发器 DROP TRIGGER <trigger_name>；
相关数据字典 SELECT * FROM USER_TRIGGERS;