chap13OraclePLSQL命名对象.pptVIP

2007-9 东北大学东软信息学院计算机系 2007-9 东北大学东软信息学院计算机系 第13章 PL/SQL命名对象 本章内容 13.1项目导入——规划人力资源管理系统数据库功能模块 13.2存储过程 13.3 函数 13.4 包 13.5 触发器 本章要求 掌握存储过程、函数、包、触发器的应用 13.1项目导入——规划人力资源管理系统数据库功能模块 存储过程 创建名为“PROC_SHOW_EMP”的存储过程，以部门编号为参数，查询并返回该部门平均工资，以及该部门中比该部门平均工资高的员工信息。 创建名为“PROC_RETURN_DEPTINFO”的存储过程，以部门编号为参数返回该部门的人数和平均工资。 创建名为“PROC_SECURE_DML”的存储过程，检查当前用户操作时间是否为工作时间，即非周六、周日，时间为08：00-18：00。 创建名为“PROC_JOB_CHANGE”的存储过程，实现员工职位的调动。 创建名为“PROC_DEPARTMENT_CHANGE”的存储过程，实现员工部门的调动 函数 创建名为“FUNC_DEPT_MAXSAL”的函数，以部门编号为参数，返回部门最高工资。 创建名为“FUNC_EMP_SALARY”的函数，以员工编号为参数，返回员工的工资。 创建名为“FUNC_EMP_DEPT_AVGSAL”的函数，以员工编号为参数，返回该员工所在部门的平均工资。 触发器 创建名为“TRG_SECURE_EMP”的触发器，保证非工作时间禁止对EMPLOYEES表进行DML操作。 为employees表创建一个触发器“TRG_EMP_DEPT_STAT”，当执行插入或删除操作时，统计操作后各个部门员工人数；当执行更新工资操作时，统计更新后各个部门员工平均工资。 为EMPLOYEES表创建触发器“TRG_UPDATE_JOB_HISTORY”，当员工职位变动或部门变动时，相关信息写入job_history表。 为EMPLOYEES表创建触发器“TRG_DML_EMP_SALARY”，保证插入新员工或修改员工工资时，员工的最新工资在其工作职位所允许的工资范围之内。 为EMPLOYEES表创建触发器，当更新员工部门或插入新员工时，保证部门人数不超过20人。 13.2 存储过程 存储过程的创建 存储过程的调用 存储过程的管理 （1）存储过程的创建 基本语法 CREATE [OR REPLACE] PROCEDURE procedure_name (parameter1_name [mode] datatype [DEFAULT|:=value] [, parameter2_name [mode] datatype [DEFAULT|:=value],…]) AS|IS /*Declarative section is here */ BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END[procedure_name]; 参数说明 参数的模式 IN（默认参数模式）表示当过程被调用时，实参值被传递给形参；在过程内，形参起常量作用，只能读该参数，而不能修改该参数；当子程序调用结束返回调用环境时，实参没有被改变。IN模式参数可以是常量或表达式。 OUT表示当过程被调用时，实参值被忽略；在过程内，形参起未初始化的PL/SQL变量的作用，初始值为NULL，可以进行读/写操作；当子程序调用结束后返回调用环境时，形参值被赋给实参。OUT模式参数只能是变量，不能是常量或表达式。 IN OUT表示当过程被调用时，实参值被传递给形参；在过程内，形参起已初始化的PL/SQL变量的作用，可读可写；当子程序调用结束返回调用环境时，形参值被赋给实参。IN OUT模式参数只能是变量，不能是常量或表达式。 参数的限制 在声明形参时，不能定义形参的长度或精度、刻度，它们是作为参数传递机制的一部分被传递的，是由实参决定的。 参数传递方式 当子程序被调用时，实参与形参之间值的传递方式取决于参数的模式。IN参数为引用传递，即实参的指针被传递给形参；OUT，IN OUT参数为值传递，即实参的值被复制给形参。 参数默认值 可以为参数设置默认值，这样存储过程被调用时如果没有给该参数传递值，则采用默认值。需要注意，有默认值的参数应该放在参数列表的最后。 创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。 CREATE OR REPLACE PROCEDURE proc_show_emp( p_deptno employees.depart