存储过程及触发器.doc

PL/SQL学习笔记(存储过程与触发器)2008-11-04 16:28存储过程:将命名的 PL/SQL 块，编译并存储在数据库中存储过程的各个部分： 声明部分 可执行部分 异常处理部分(可选)存储过程的分类： 过程 执行某些操作 函数 执行操作并返回值子程序(存储过程)的优点： 模块化 将程序分解为逻辑模块 可重用性 可以被任意数目的程序调用 可维护性 简化维护操作 安全性 通过设置权限，使数据更安全过程:过程是用于完成特定任务的子程序 创建过程的语法： CREATE PROCEDURE ( INOUTINOUT NOCOPY , ) ASIS BEGIN EXCEPTION END;创建过程的语法含义：procedure_name：存储过程名argument：参数名INOUTINOUT：IN输入参数，OUT输出参数，INOUT输入输出参数如果使用了NOCOPY，则PL / SQL编译器将按引用传递参数，而不按值传递datatype：数据类型：声明部分：存储过程执行的PL/SQL语句：异常处理部分 CREATE OR REPLACE PROCEDURE find_emp (emp_no NUMBER)AS empname VARCHAR2(20);BEGIN SELECT ename INTO empnameFROM 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 子程序创建函数的语法：CREATEOR REPLACE FUNCTION function_name (parameter, parameter.) RETURN datatypeIS|ASBEGIN EXCEPTIONEND ; 创建函数的语法含义：parameter 替换为以下代码:parameter_name NOCOPY datatype如果使用了NOCOPY，则PL / SQL编译器将按引用传递参数，而不按值传递 定义函数的限制：函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数形参不能是 PL/SQL 类型函数的返回类型也必须是数据库类型访问函数的两种方式：使用 PL/SQL 块使用 SQL 语句创建函数：CREATE OR REPLACE FUNCTION fun_helloRETURN VARCHAR2ISBEGINRETURN 朋友，您好;END;/从 SQL 语句调用函数：SQL SELECT fun_hello FROM DUAL;CREATE OR REPLACE FUNCTION item_price_range (price NUMBER) RETURN VARCHAR2 ASmin_price NUMBER;max_price NUMBER;BEGINSELECT MAX(ITEMRATE), MIN(ITEMRATE)INTO max_price, min_priceFROM itemfile;IF price = min_price AND price = max_price THEN RETURN 输入的单价介于最低价与最高价之间;ELSE RETURN 超出范围;END IF;END;/DECLAREP NUMBER := 300;MSG VARCHAR2(200);BEGINMSG := item_price_range(300);DBMS_OUTPUT.PUT_LINE(MSG);END;/过程和函数的比较:过 程:作为 PL/SQL 语句执行在规格说明中不包含 RETURN 子句不返回任何值可以包含 RETURN 语句，但是与函数不同，它不能用于返回值函 数:作为表达式的一部分调用必须在规格说明中包含 RETURN 子句必须返回单个值必须包含至少一条 RETURN 语句触发器概念 命名的 PL/SQL 块，编译并存储在数据库中与存储过程不同的是触发器是在事件发生时隐式（自动）触发的，而存储过程由调用者显式地调用维护在表创建阶段通过声明限制无法实现的复杂完整性限制通过记录修改内容和修改者来审计表中的信息在表内容发生变更时，自动通知其他程序采取相应的处理在订阅发布环境下，发布有关各种事件的信息触发器创建语法 :CREATE OR REPLACE TRIGGER trigger_nameBEFORE | AFTER | INSTEAD OF - - 触发器类型triggering_eventreferencing_clauseWHEN trigger_condition - -触发条件FOR EACH ROWtrigger_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 DISABLE; 启用触发器 ALTER TRIGGER ENABLE; 删除触发器 DROP TRIGGER ； 相关数据字典 SELECT * FROM USER_TRIGGERS;