14(上)-过程、函数、包和触发器全解.ppt

* 下面的例子显示了包EmployeePackage的包体： CREATE OR REPLACE PACKAGE BODY EmployeePackage AS --游标定义 CURSOR order_sal RETURN EmpRecType IS SELECT empno,sal FROM emp ORDER BY sal; --招聘雇员子程序代码 PROCEDURE HireEmployee(p_EmpNo emp.empno%TYPE, p_EName emp.ename%TYPE, p_Job emp.job%TYPE, p_Mgr emp.mgr%TYPE, p_HireDate emp.hiredate%TYPE, p_Sal emp.sal%TYPE, p_Comm m%TYPE, p_DeptNo emp.deptno%TYPE) IS BEGIN INSERT INTO emp(empno,ename,job,mgr, Hiredate,sal,comm,deptno) VALUES (p_EmpNo,p_EName,p_Job,p_Mgr, p_HireDate,p_Sal,p_Comm,p_DeptNo); END HireEmployee; --解雇雇员子程序代码 PROCEDURE FireEmployee(p_EmpNo emp.empno%TYPE) IS BEGIN DELETE FROM emp WHERE empno=p_EmpNo; -- Check to see if the DELETE operation was successful. If -- it didnt match any rows, raise an error. IF SQL%NOTFOUND THEN RAISE e_EmployeeNotHired; END IF; END FireEmployee; --统计某部门的员工人数子程序代码 FUNCTION count_emp(p_DeptNo emp.deptno%TYPE) RETURN INTEGER IS Num INTEGER; BEGIN SELECT COUNT(*) INTO num FROM emp WHERE deptno=p_DeptNo; RETURN num; END count_emp; --返回一个所有部门的PL/SQL表子程序代码 PROCEDURE DepartmentList(p_Depts OUT t_DepartmentNoTable, p_NumDepartments IN OUT BINARY_INTEGER) IS v_DepartmentNo dept.deptno%TYPE; CURSOR c_Departments IS SELECT deptno FROM dept; BEGIN /*p_NumDepartments will be the table index. It will start at 0, and be incremented each time through the fetch loop. At the end of the loop, it will have the number of rows fetched, and therefore the number of rows returned in p_Depts.*/ p_NumDepartments:=0; OPEN c_Departments; LOOP FETCH c_Departments INTO v_DepartmentNo; EXIT WHEN c_Departments%NOTFOUND; p_NumDepartments:=p_NumDepartments+1; p_Depts(p_NumDepartments):=v_DepartmentNo; END LOOP; END DepartmentList; END EmployeePackage; / * * 这意味着把包从硬盘中读入到存储器中，并运行被调用子程序的编译代码。 这时，系统为该包中定义的所有变量分配存储器单元。每个会话都有其封装变量的备份，以确保执行同一包内子程序的两个对话使用不同的存储单元 。 一般在包体中所有对象之后加入一个初始化段，其语法如下： CREATE OR REPLACE PACKAGE BODY package_name{IS | AS} ... BEGIN initialization_code; END [ package_name]; * * * 该语句块中声明的变量（v_EmpNo,v_EName,v_Job