08存储过程和自定义函数.pptVIP

L o g o 存储过程与自定义函数 * 本章目标 存储程序的应用 函数的应用 RETURN 语句 过程和函数的参数模式 查看存储子程序的错误 存储子程序的优点 存储过程 执行特定操作的子程序 存储于数据库中并可由任意匿名块调用 能够接受参数 在参数声明中的数据类型区分符应为无限制的 存储过程（续） 具有两个部分 说明 以关键字 PROCEDURE 开始，以过程名或参数列表结束 主体 以关键字 IS 开始，以关键字 END 结束，后面可以跟可选过程名称 存储过程（续） CREATE [OR REPLACE] PROCEDURE 过程名 [(参数1, … 参数N) ] IS|AS [局部声明] BEGIN 可执行语句； EXCEPTION [例外处理程序]； END [过程名]; 参数代表 变量名 [IN|OUT|IN OUT] 数据类型 [{:= | DEFAULT} 值] 存储过程（续） 示例 CREATE PROCEDURE branch_sum(p_brnch branch.branch_code%TYPE) IS declare variables; BEGIN 可执行语句； EXCEPTION WHEN NO_DATA_FOUND THEN 语句 ; END branch_sum; 存储过程（续） 创建过程时， Oracle 自动执行下列步骤 编译过程 存储所编译的代码 在数据库中存储过程 PL/SQL 编译程序用于编译代码 如果发生错误，也可创建过程，但是无效 存储过程（续） 可以使用 SHOW ERRORS 命令或下列命令查看编译错误 SELECT * FROM USER_ERRORS; Oracle 将编译过程装入 SGA 中 其他用户也可以执行存储在 SGA 中的相同过程 存储过程（续） Oracle 分三个步骤执行过程 验证用户访问 如果为非法用户，则拒绝访问 验证过程有效性 如果为非法过程，则不执行 执行过程 可以通过下列方式检查过程的有效性 SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = ‘PROCEDURE’; 存储过程的优点 增强了数据安全性 可以授权用户访问能执行表的存储过程，但不授权他们访问表自身 提高了数据库性能 通过网络发送的信息有所减少 编译不需要执行代码 过程存在于共享池中，所以不需要进行磁盘检索 存储过程的优点（续） 节省内存 只需要在内存中装入过程的一个拷贝便可供多个用户执行 提高了开发的工作效率 通过编写单一的过程可以避免冗余程序代码，并且提高了生产效率 完整性 只需要测试一次，以保证它能够返回正确的结果 调用存储过程 可以作为 PL/SQL 语句调用过程 示例 branch_sum(‘NYK’); 可以从 SQL*Plus 中调用单独的过程 示例 SQL EXECUTE branch_sum(‘NYK’); 函数 返回值的子程序 具有一个 RETURN 子句 存储于数据库中并可由任意匿名程序块调用 通常可以接受参数 在参数声明中的数据类型区分符应为无限制的 函数（续） 具有两个部分 说明 以关键字 FUNCTION 开始，以 RETURN 子句结束 主体 以关键字 IS 开始，以关键字 END 结束，后面可以跟可选函数名称 函数（续） CREATE [OR REPLACE] FUNCTION 函数名 [(参数1, … 参数N) ] RETURN datatype IS|AS [局部声明] BEGIN 可执行语句 ; [EXCEPTION 例外处理程序] END [函数名]; 参数代表的是 变量名 [IN|OUT|IN OUT] 数据类型 [{:= | DEFAULT} 值 函数（续） 示例 CREATE FUNCTION day_fn(mday NUMBER) RETURN CHAR IS disp_day CHAR(15); BEGIN 可执行语句 ; RETURN disp_day; EXCEPTION 语句 ; END day_fn; 调用函数 作为 PL/SQL 语句调用函数 示例 chardays := day_fn(3); 作为表达式的一部分调用函数 示例 IF day_fn(3) = ‘TUESDAY’ THEN 语句 ; END IF; 过程和函数的参数模式 用于定义正式参数的行为 可以与其他子程序一起使用 三个参数模式 IN (默认) OUT IN OUT 避免在函数中使用 OUT 和 IN OUT 模型 过程和函数的参数模式