PLSQL命名对象 oracle数据库应用教程

1、 Oracle数据库应用教程1第第11章章 PL/SQL命名对象命名对象 Oracle数据库应用教程2本章内容本章内容p11.1项目导入规划人力资源管理系统数据库功能模块p11.2存储过程p11.3 函数p11.4 包p11.5 触发器 Oracle数据库应用教程3本章要求本章要求p掌握存储过程、函数、包、触发器的应用 Oracle数据库应用教程411.1项目导入项目导入规划人力资源管理系统规划人力资源管理系统数据库功能模块数据库功能模块p存储过程n创建名为创建名为“PROC_SHOW_EMP”的存储过程，以的存储过程，以部门编号为参数，查询并返回该部门平均工资，以及部门编号为参数，查询并返回

2、该部门平均工资，以及该部门中比该部门平均工资高的员工信息。该部门中比该部门平均工资高的员工信息。n创建名为创建名为“PROC_RETURN_DEPTINFO”的存储的存储过程，以部门编号为参数返回该部门的人数和平均工过程，以部门编号为参数返回该部门的人数和平均工资。资。n创建名为创建名为“PROC_SECURE_DML”的存储过程，的存储过程，检查当前用户操作时间是否为工作时间，即非周六、检查当前用户操作时间是否为工作时间，即非周六、周日，时间为周日，时间为08：00-18：00。n创建名为创建名为“PROC_JOB_CHANGE”的存储过程，的存储过程，实现员工职位的调动。实现员工职位的调动

3、。n创建名为创建名为“PROC_DEPARTMENT_CHANGE”的的存储过程，实现员工部门的调动存储过程，实现员工部门的调动 Oracle数据库应用教程5p函数n创建名为创建名为“FUNC_DEPT_MAXSAL”的函数，以的函数，以部门编号为参数，返回部门最高工资。部门编号为参数，返回部门最高工资。n创建名为创建名为“FUNC_EMP_SALARY”的函数，以员的函数，以员工编号为参数，返回员工的工资。工编号为参数，返回员工的工资。n创建名为创建名为“FUNC_EMP_DEPT_AVGSAL”的函数，的函数，以员工编号为参数，返回该员工所在部门的平均工资。以员工编号为参数，返回该员工所在

4、部门的平均工资。 Oracle数据库应用教程6p触发器n创建名为创建名为“TRG_SECURE_EMP”的触发器，保证的触发器，保证非工作时间禁止对非工作时间禁止对EMPLOYEES表进行表进行DML操作。操作。n为为employees表创建一个触发器表创建一个触发器“TRG_EMP_DEPT_STAT”，当执行插入或删除，当执行插入或删除操作时，统计操作后各个部门员工人数；当执行更新操作时，统计操作后各个部门员工人数；当执行更新工资操作时，统计更新后各个部门员工平均工资。工资操作时，统计更新后各个部门员工平均工资。n为为EMPLOYEES表创建触发器表创建触发器“TRG_UPDATE_JOB

5、_HISTORY”，当员工职，当员工职位变动或部门变动时，相关信息写入位变动或部门变动时，相关信息写入job_history表。表。n为为EMPLOYEES表创建触发器表创建触发器“TRG_DML_EMP_SALARY”，保证插入新员工，保证插入新员工或修改员工工资时，员工的最新工资在其工作职位所或修改员工工资时，员工的最新工资在其工作职位所允许的工资范围之内。允许的工资范围之内。n为为EMPLOYEES表创建触发器，当更新员工部门或插表创建触发器，当更新员工部门或插入新员工时，保证部门人数不超过入新员工时，保证部门人数不超过20人。人。 Oracle数据库应用教程711.2 存储过程存储过程

6、p存储过程的创建p存储过程的调用p存储过程的管理 Oracle数据库应用教程8（1）存储过程的创建）存储过程的创建p基本语法nCREATE OR REPLACE PROCEDURE procedure_namen(parameter1_name mode datatype n DEFAULT|:=valuen, parameter2_name mode datatype n DEFAULT|:=value,)nAS|ISn /*Declarative section is here */nBEGINn /*Executable section is here*/ nEXCEPTIONn /*E

7、xception section is here*/ nENDprocedure_name; Oracle数据库应用教程9p参数说明 n参数的模式参数的模式 pIN（默认参数模式）表示当过程被调用时，实参值被传递给形参；在过程内，形参起常量作用，只能读该参数，而不能修改该参数；当子程序调用结束返回调用环境时，实参没有被改变。IN模式参数可以是常量或表达式。pOUT表示当过程被调用时，实参值被忽略；在过程内，形参起未初始化的PL/SQL变量的作用，初始值为NULL，可以进行读/写操作；当子程序调用结束后返回调用环境时，形参值被赋给实参。OUT模式参数只能是变量，不能是常量或表达式。pIN OUT

8、表示当过程被调用时，实参值被传递给形参；在过程内，形参起已初始化的PL/SQL变量的作用，可读可写；当子程序调用结束返回调用环境时，形参值被赋给实参。IN OUT模式参数只能是变量，不能是常量或表达式。 Oracle数据库应用教程10n参数的限制参数的限制p在声明形参时，不能定义形参的长度或精度、刻度，它们是作为参数传递机制的一部分被传递的，是由实参决定的。n参数传递方式参数传递方式p当子程序被调用时，实参与形参之间值的传递方式取决于参数的模式。IN参数为引用传递，即实参的指针被传递给形参；OUT，IN OUT参数为值传递，即实参的值被复制给形参。n参数默认值参数默认值p可以为参数设置默认值，

9、这样存储过程被调用时如果没有给该参数传递值，则采用默认值。需要注意，有默认值的参数应该放在参数列表的最后。 Oracle数据库应用教程11p创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。pCREATE OR REPLACE PROCEDURE proc_show_emp(p p_deptno employees.department_id%TYPE)pASp v_sal employees.salary%TYPE;pBEGINp SELECT avg(salary) INTO v_sal FROM employees p WHERE dep

10、artment_id=p_deptno;p DBMS_OUTPUT.PUT_LINE(p_deptno| |p average salary is: |v_sal);p FOR v_emp IN (SELECT * FROM employees p WHERE department_id=p_deptno AND salaryv_sal)LOOPp DBMS_OUTPUT.PUT_LINE(v_emp.employee_id| |p v_emp.first_name| |v_emp.last_name);p END LOOP;pEXCEPTIONp WHEN NO_DATA_FOUND THE

11、Np DBMS_OUTPUT.PUT_LINE(The department doesnt exists! );pEND proc_show_emp; Oracle数据库应用教程12p通常，存储过程不需要返回值，如果需要返回一个值可以通过函数调用实现。但是，如果希望返回多个值，可以使用OUT或IN OUT模式参数来实现。 Oracle数据库应用教程13p创建一个存储过程，以部门号为参数，以部门编号为参数返回该部门的人数和平均工资。p pCREATE OR REPLACE PROCEDURE proc_return_deptinfo(p p_deptno employees.department

12、_id%TYPE,p p_avgsal OUT employees.salary%TYPE,p p_count OUT NUMBER)pASpBEGINp SELECT avg(salary),count(*) INTO p_avgsal,p_count FROM employeesp WHERE department_id=p_deptno;pEXCEPTIONp WHEN NO_DATA_FOUND THENp DBMS_OUTPUT.PUT_LINE(The department dont exists! );pEND proc_return_deptinfo; Oracle数据库应用教

13、程14（2）存储过程的调用）存储过程的调用p在SQL*PLUS中调用nEXEC procedure_name(parameter_list)nEXECUTE proc_show_emp(10)p在PL/SQL块中调用nBEGIN procedure_name(parameter_list);nEND；p注意n在在PL/SQL程序中，存储过程可以作为一个独立的程序中，存储过程可以作为一个独立的表达式被调用。表达式被调用。 Oracle数据库应用教程15pDECLAREp v_avgsal emp.sal%TYPE;p v_count NUMBER;pBEGINp proc_show_emp(20

14、);p proc_return_deptinfo(10,v_avgsal,v_count);p DBMS_OUTPUT.PUT_LINE(v_avgsal| | p v_count);pEND; Oracle数据库应用教程16（3）存储过程的管理）存储过程的管理p修改存储过程 nCREATE OR REPLACE PROCEDURE procedure_namep查看存储过程及其源代码 n查询数据字典视图查询数据字典视图USER_SOURCEnSELECT name,text FROM user_source WHERE type=PROCEDURE; p重新编译存储过程 nALTER PRO

15、CEDURECOMPILE nALTER PROCEDURE proc_show_emp COMPILE; p删除存储过程 nDROP PROCEDUREnDROP PROCEDURE proc_show_emp; Oracle数据库应用教程1711.2.5 人力资源管理系统存储过程的创人力资源管理系统存储过程的创建建 p创建名为“PROC_SECURE_DML”的存储过程，检查当前用户操作时间是否为工作时间，即非周六、周日，时间为08：00-18：00。pCREATE OR REPLACE PROCEDURE proc_secure_dmlpISpBEGINp IF TO_CHAR (SYS

16、DATE, HH24:MI) NOT BETWEEN 08:00 p AND 18:00 OR TO_CHAR (SYSDATE, DY, p NLS_DATE_LANGUAGE=AMERICAN) IN (SAT, SUN) p THENp RAISE_APPLICATION_ERROR (-20205,只能在正常的工作时p 间内进行改变。);p END IF;pEND proc_secure_dml; Oracle数据库应用教程18p创建名为“PROC_JOB_CHANGE”的存储过程，实现员工职位的调动。pCREATE OR REPLACE PROCEDURE proc_job_chan

17、ge(p p_employee_id employees.employee_id%type,p p_new_job_title jobs.job_title%type)pASp v_old_job_id jobs.job_id%type;p v_old_job_title jobs.job_title%type;p v_new_job_id jobs.job_id%type;pBEGINp SELECT job_id INTO v_old_job_id FROM employees WHERE employee_id=p_employee_id;p SELECT job_title INTO

18、v_old_job_title FROM jobs WHERE job_id=v_old_job_id;p IF v_old_job_title=p_new_job_title THENp RAISE_APPLICATION_ERROR(-20001,the new job title is as same as before!);p END IF;p SELECT job_id INTO v_new_job_id FROM jobs WHERE job_title=p_new_job_title;p UPDATE employees SET job_id=v_new_job_id WHERE

19、 employee_id=p_employee_id;p COMMIT;pEXCEPTIONp WHEN NO_DATA_FOUND THENp RAISE_APPLICATION_ERROR(-20002,The job title does not exists!);pEND proc_job_change; Oracle数据库应用教程19p创建名为“PROC_DEPARTMENT_CHANGE”的存储过程，实现员工部门的调动。pCREATE OR REPLACE PROCEDURE proc_department_change(p p_employee_id employees.empl

20、oyee_id%type,p p_new_department_name departments.department_name%type)p ASp v_old_department_id departments.department_id%type;p v_old_department_name departments.department_name%type;p v_new_department_id departments.department_id%type;p BEGINp SELECT department_id INTO v_old_department_id FROM emp

21、loyees p WHERE employee_id=p_employee_id;p SELECT department_name INTO v_old_department_name FROM departments p WHERE department_id=v_old_department_name;p IF v_old_department_name=p_new_department_name THENp RAISE_APPLICATION_ERROR(-20001,the new department name is as same as before!);p END IF;p SE

22、LECT department_id INTO v_new_department_id FROM departments p WHERE department_name=p_new_department_name;p UPDATE employees SET department_id=v_new_department_id p WHERE employee_id=p_employee_id;p COMMIT;p EXCEPTIONp WHEN NO_DATA_FOUND THENp RAISE_APPLICATION_ERROR(-20002,The department name does

23、 not exists!);p END proc_department_change; Oracle数据库应用教程2011. 3函数函数p函数的创建p函数的调用p函数的管理 Oracle数据库应用教程21（1）函数的创建）函数的创建p基本语法为 nCREATE OR REPLACE FUNCTION function_name n(parameter1_name mode datatype n DEFAULT|:=valuen, parameter2_name mode datatype n DEFAULT|:=value,)nRETURN return_datatype nAS|ISn /*

24、Declarative section is here */nBEGINn /*Executable section is here*/ nEXCEPTIONn /*Exception section is here*/ nEND function_name; Oracle数据库应用教程22p注意n在函数定义的头部，参数列表之后，必须包含一个在函数定义的头部，参数列表之后，必须包含一个RETURN语句来指明函数返回值的类型，但不能约束语句来指明函数返回值的类型，但不能约束返回值的长度、精度、刻度等。如果使用返回值的长度、精度、刻度等。如果使用%TYPE，则可以隐含地包括长度、精度、刻度等约束信

25、息；则可以隐含地包括长度、精度、刻度等约束信息；n在函数体的定义中，必须至少包含一个在函数体的定义中，必须至少包含一个RETURN 语语句，来指明函数返回值。也可以有多个句，来指明函数返回值。也可以有多个RETURN语句，语句，但最终只有一个但最终只有一个RETURN语句被执行。语句被执行。 Oracle数据库应用教程23p创建名为“FUNC_DEPT_MAXSAL”的函数，以部门编号为参数，返回部门最高工资。p pCREATE OR REPLACE FUNCTION func_dept_maxsal(p p_deptno employees.department_id%TYPE)p RETU

26、RN employees.salary%TYPEpASp v_maxsal employees.salary%TYPE;pBEGINp SELECT max(salary) INTO v_maxsal FROM employees p WHERE department_id=p_deptno;p RETURN v_maxsal;pEXCEPTION p WHEN NO_DATA_FOUND THENp DBMS_OUTPUT.PUT_LINE(The deptno is invalid! );pEND func_dept_maxsal; Oracle数据库应用教程24p如果需要函数返回多个值，

27、可以使用OUT或IN OUT模式参数。 Oracle数据库应用教程25p创建一个名为“FUNC_DEPT_INFO”的函数，以部门号为参数，返回部门名、部门人数及部门平均工资。 pCREATE OR REPLACE FUNCTION func_dept_info(p p_deptno departments.department_id%TYPE,pp_num OUT NUMBER,pp_avg OUT NUMBER)p RETURN departments.department_name%TYPEpASp v_dname departments.department_name%TYPE;pBE

28、GINp SELECT department_name INTO v_dname FROM departments pWHERE department_id=p_deptno;p SELECT count(*),avg(salary) INTO p_num,p_avg FROM employees pWHERE department_id=p_deptno;p RETURN v_dname;pEND func_dept_info; Oracle数据库应用教程26（2）函数的调用）函数的调用p在SQL语句中调用函数p在PL/SQL中调用函数p注意n函数只能作为表达式的一部分被调用。函数只能作为表

29、达式的一部分被调用。p示例n通过通过func_dept_maxsal函数的调用，输出各个部函数的调用，输出各个部门的最高工资；门的最高工资；n通过通过func_dept_info函数调用，输出各个部门名、函数调用，输出各个部门名、部门人数及平均工资。部门人数及平均工资。 Oracle数据库应用教程27pDECLAREp v_maxsal employees.salary%TYPE;p v_avgsal employees.salary%TYPE;p v_num NUMBER;p v_dname departments.department_name%TYPE;pBEGINp FOR v_dep

30、t IN (SELECT DISTINCT department_id FROM p employees WHERE department_id IS NOT NULL) LOOPp v_maxsal:=func_dept_maxsal(v_dept.department_id); p v_dname:=func_dept_info(v_dept.department_id,p v_num, v_avgsal);p DBMS_OUTPUT.PUT_LINE(v_dname| |v_maxsal| |p v_avgsal| |v_num);p END LOOP;pEND; Oracle数据库应用

31、教程28p函数可以在SQL语句的以下部分调用：nSELECT语句的目标列；语句的目标列；nWHERE和和HAVING子句；子句；nCONNECT BY，START WITH，ORDER BY，GROUP BY子句；子句；nINSERT语句的语句的VALUES子句中；子句中；nUPDATE语句的语句的SET子句中。子句中。 Oracle数据库应用教程29p如果要在SQL中调用函数，那么函数必须符合下列限制和要求：n在在SELECT语句中的函数不能修改（语句中的函数不能修改（INSERT，UPDATE，DELETE）调用函数的）调用函数的SQL语句中使用的语句中使用的表；表；n函数在一个远程或并行

32、操作中使用时，不能读函数在一个远程或并行操作中使用时，不能读/写封装写封装变量；变量；n函数必须是一个存储数据库对象（或存储在包中）；函数必须是一个存储数据库对象（或存储在包中）；n函数的参数只能使用函数的参数只能使用IN模式；模式；n形式参数类型必须使用数据库数据类型；形式参数类型必须使用数据库数据类型；n返回的数据类型必须是数据库数据类型；返回的数据类型必须是数据库数据类型； Oracle数据库应用教程30（3）函数的管理）函数的管理p函数的修改nCREATE OR REPLACE FUNCTION function_namep查看函数及其源代码n查询数据字典视图查询数据字典视图USER_

33、SOURCEnSELECT name,text FROM user_source WHERE type=FUNCTION;p函数重编译nALTER FUNCTIONCOMPILEnALTER FUNCTION func_dept_info COMPILE; p删除函数nDROP FUNCTIONnDROP FUNCTION func_dept_info; Oracle数据库应用教程3111.3.5 人力资源管理系统函数的创建人力资源管理系统函数的创建p创建名为“FUNC_EMP_SALARY”的函数，以员工编号为参数，返回员工的工资。pCREATE OR REPLACE FUNCTION fu

34、nc_emp_salary(p p_empno employees.employee_id%type)pRETURN employees.salary%typepASp v_sal employees.salary%type;pBEGINp SELECT salary INTO v_sal FROM employees p WHERE employee_id=p_empno;p RETURN v_sal;pEXCEPTIONp WHEN NO_DATA_FOUND THENp RAISE_APPLICATION_ERROR(-20000,There is not p such an emplo

35、yee!);pEND func_emp_salary; Oracle数据库应用教程32p创建名为“FUNC_EMP_DEPT_AVGSAL”的函数，以员工编号为参数，返回该员工所在部门的平均工资。pCREATE OR REPLACE FUNCTION func_emp_dept_avgsal(pp_empno employees.employee_id%type)pRETURN employees.salary%typepASp v_deptno employees.department_id%type;p v_avgsal employees.salary%type;pBEGINp SELE

36、CT department_id INTO v_deptno FROM employees p WHERE employee_id=p_empno;p SELECT avg(salary) INTO v_avgsal FROM employees p WHERE department_id=v_deptno;p RETURN v_avgsal;pEXCEPTIONp WHEN NO_DATA_FOUND THENp RAISE_APPLICATION_ERROR(-20000,There is not such p an employee!);pEND func_emp_dept_avgsal

37、; Oracle数据库应用教程3311.4 包包p包概述p包的创建p包的调用p包重载p包的初始化p包的持续性 p包的串行化 p包的管理 Oracle数据库应用教程34包概述包概述 p包是包含一个或多个子程序单元（过程、函数等）的容器。p包是一种全局结构 。p包类型n数据库内置包数据库内置包 n用户创建的包用户创建的包p包构成n包规范包规范n包体两包体两 Oracle数据库应用教程35p包规范声明了软件包中所有内容，如过程、函数、游标、类型、异常和变量等，其中过程和函数只包括原型信息，不包含任何子程序代码。 p包体中包含了在包头中的过程和函数的实现代码。包体中还可以包括在规范中没有声明的变量、游

38、标、类型、异常、过程和函数，但是它们是私有元素，只能由同一包体中其他过程和函数使用。 Oracle数据库应用教程3611.4.1 包的创建包的创建p创建包规范 p创建包体 Oracle数据库应用教程37（1）创建包规范）创建包规范p语法nCREATE OR REPLACE PACKAGE package_name nIS|ASnPRAGMA SERIALLY_RESUABLEn type_definition|variable_declaration|n exception_declaration|cursor_declaration| n procedure_ declaration|fun

39、ction_ declarationnEND package_name; Oracle数据库应用教程38p注意：n元素声明的顺序可以是任意的，但必须先声明元素声明的顺序可以是任意的，但必须先声明后使用；后使用；n所有元素是可选的；所有元素是可选的；n过程和函数的声明只包括原型，不包括具体实过程和函数的声明只包括原型，不包括具体实现。现。 Oracle数据库应用教程39p创建一个软件包，包括2个变量、2个过程和1个异常。nCREATE OR REPLACE PACKAGE pkg_empnASn minsal NUMBER;n maxsal NUMBER;n e_beyondbound EXCE

40、PTION;n PROCEDURE update_sal(n p_empno NUMBER, p_sal NUMBER);n PROCEDURE add_employee(n p_empno NUMBER,p_sal NUMBER);nEND pkg_emp; Oracle数据库应用教程40p语法nCREATE OR REPLACE PACKAGE BODY package_name nIS|ASnPRAGMA SERIALLY_RESUABLEn type_definition|variable_declaration|n exception_declaration|n cursor_dec

41、laration| n procedure_definition |n function_definitionnEND package_name; （2）创建包体）创建包体 Oracle数据库应用教程41p注意：n包体中函数和过程的原型必须与包规范中的声包体中函数和过程的原型必须与包规范中的声明完全一致；明完全一致；n只有在包规范已经创建的条件下，才可以创建只有在包规范已经创建的条件下，才可以创建包体；包体；n如果包规范中不包含任何函数或过程，则可以如果包规范中不包含任何函数或过程，则可以不创建包体。不创建包体。 Oracle数据库应用教程42pCREATE OR REPLACE PACKAG

42、E BODY pkg_emppASp PROCEDURE update_sal(p_empno NUMBER, p_sal NUMBER)p ASp BEGINp SELECT min(salary), max(salary) INTO minsal,maxsal p FROM employees;p IF p_sal BETWEEN minsal AND maxsal THENp UPDATE employees SET salary=p_sal p WHERE employee_id=p_empno;p IF SQL%NOTFOUND THENp RAISE_APPLICATION_ERR

43、OR(-20000,p The employee doesnt exist);p END IF;p ELSEp RAISE e_beyondbound;p END IF;p EXCEPTIONp WHEN e_beyondbound THENp DBMS_OUTPUT.PUT_LINE(The salary is beyond bound! );p END update_sal; Oracle数据库应用教程43pPROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)p ASp BEGINp SELECT min(salary), max(sala

44、ry) INTO minsal,maxsal p FROM employees;p IF p_sal BETWEEN minsal AND maxsal THENp INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,salary)pVALUES(p_empno,Smith,sysdate, ST_MAN,p_sal);p ELSEp RAISE e_beyondbound;p END IF;p EXCEPTIONp WHEN e_beyondbound THENp DBMS_OUTPUT.PUT_LINE(T

45、he salary is beyond bound! );p END add_employee; pEND pkg_emp; Oracle数据库应用教程4411.4.2 包的调用包的调用p在包规范声明的任何元素是公有的，在包外都是可见的n包外：通过包外：通过package.element形式调用；形式调用；n包内：直接通过元素名进行调用。包内：直接通过元素名进行调用。 p在包体中定义而没有在包头中声明的元素是私有的，只能在包体中引用 Oracle数据库应用教程45p调用包pkg_emp中的过程update_sal，修改150号员工工资为8000。p调用add_employee添加一个员工号为2

46、011，工资为9000的员工 nBEGINn pkg_emp.update_sal(150,8000);n pkg_emp.add_employee(2011,9000);nEND; Oracle数据库应用教程4611.4.3 包重载包重载p重载子程序必须同名不同参，即名称相同，参数不同。参数不同体现为参数的个数、顺序、类型等不同。p如果两个子程序参数只是名称和模式不同，则不能重载。nPROCEDURE overloadme(parameter1 IN NUMBER);nPROCEDURE overloadme(parameter2 OUT NUMBER); p不能根据两个函数返回类型不同而对

47、它们进行重载。nFUNCTION overloadme RETURN DATE;nFUNCTION overloadme RETURN NUMBER;p重载子程序参数必须在类型系列方面有所不同。nPROCEDURE overloadchar(parameter IN CHAR);nPROCEDURE overloadchar(parameter IN VARCHAR2); Oracle数据库应用教程47p在一个包中重载两个过程，分别以部门号和部门名称为参数，查询相应部门员工名、员工号信息。 Oracle数据库应用教程48pCREATE OR REPLACE PACKAGE pkg_overlo

48、adpASp PROCEDURE show_emp(p_deptno NUMBER); p PROCEDURE show_emp(p_dname VARCHAR2);pEND pkg_overload; Oracle数据库应用教程49pCREATE OR REPLACE PACKAGE BODY pkg_overloadpASp PROCEDURE show_emp(p_deptno NUMBER)p ASp BEGINp FOR v_emp IN (SELECT * FROM employees p WHERE department_id=p_deptno) LOOPp DBMS_OUTPU

49、T.PUT_LINE(v_emp.employee_id| |p v_emp.first_name| |v_emp.last_name);p END LOOP;p END show_emp; Oracle数据库应用教程50p PROCEDURE show_emp(p_dname VARCHAR2)p ASp v_deptno NUMBER;p BEGINp SELECT department_id INTO v_deptno FROM departments p WHERE department_name=p_dname;p FOR v_emp IN (SELECT * FROM employ

50、ees p WHERE department_id=v_deptno) LOOPp DBMS_OUTPUT.PUT_LINE(v_emp.employee_id| |p v_emp.first_name| |v_emp.last_name);p END LOOP;p END show_emp;pEND pkg_overload; Oracle数据库应用教程5111.4.4包的初始化包的初始化p包在第一次被调用时从磁盘读取到共享池，并在整个会话的持续期间保持。在此过程中，可以自动执行一个初始化过程，对软件包进行实例化。p包的初始化过程只在包第一次被调用时执行，因此也称为一次性过程，它是一个匿名的

51、PL/SQL块，在包体结构的最后，以BEGIN开始。p示例n在在pkg_emp包中，在包初始化时给包中，在包初始化时给minsal和和maxsal两个变量赋值，在子程序中直接引两个变量赋值，在子程序中直接引用这两个变量。用这两个变量。 Oracle数据库应用教程52pCREATE OR REPLACE PACKAGE pkg_emppASp minsal NUMBER;p maxsal NUMBER;p e_beyondbound EXCEPTION;p PROCEDURE update_sal(p p_empno NUMBER, p_sal NUMBER);p PROCEDURE add_e

52、mployee(p p_empno NUMBER,p_sal NUMBER);pEND pkg_emp; Oracle数据库应用教程53pCREATE OR REPLACE PACKAGE BODY pkg_emppASp PROCEDURE update_sal(p_empno NUMBER, p_sal NUMBER)p ASp BEGINp IF p_sal BETWEEN minsal AND maxsal THENp UPDATE employees SET salary=p_sal p WHERE employee_id=p_empno;p IF SQL%NOTFOUND THEN

53、p RAISE_APPLICATION_ERROR(-20000,p The employee doesnt exist);p END IF;p ELSEp RAISE e_beyondbound;p END IF;p EXCEPTIONp WHEN e_beyondbound THENp DBMS_OUTPUT.PUT_LINE(The salary is beyond bound! );p END update_sal; Oracle数据库应用教程54pPROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)p ASp BEGINp IF p_

54、sal BETWEEN minsal AND maxsal THENp INSERT INTO employeesp(employee_id,last_name,email,hire_date,job_id,salary)pVALUES(p_empno,Smith,sysdate,pST_MAN,p_sal);p ELSEp RAISE e_beyondbound;p END IF;p EXCEPTIONp WHEN e_beyondbound THENp DBMS_OUTPUT.PUT_LINE(The salary is beyond bound! );p END add_employee

55、;pBEGIN -package initialp SELECT min(salary), max(salary) INTO minsal,maxsal pFROM employees;pEND pkg_emp; Oracle数据库应用教程5511.4.5 包的持续性包的持续性p变量持续性p游标持续性 Oracle数据库应用教程56（1）变量持续性）变量持续性 p包中变量的持续性是指，当用户调用包时，会创建自己的变量副本，在用户的整个会话过程中持续存在，对用户而言是私有的。 Oracle数据库应用教程57p为了测试变量的持续性，先创建一个包含一个变量的包和对包中变量进行读、写的两个过程。pCR

56、EATE OR REPLACE PACKAGE variable_pkgpISp pkg_test NUMBER(6,2) :=0;pEND variable_pkg;pCREATE OR REPLACE PROCEDURE get_pkg_testp (p_test OUT NUMBER)pISpBEGINp p_test := variable_pkg.pkg_test; pEND;p pCREATE OR REPLACE PROCEDURE set_pkg_testp (p_test IN NUMBER)pISpBEGINp variable_pkg.pkg_test := p_test

57、;pEND; Oracle数据库应用教程58p以不同的用户（scott，system）分别启动两个SQL*Plus，进行下列操作，可以看出，包中的变量variable_pkg.pkg_test在scott用户和system用户之间是持续的、独立的。 Oracle数据库应用教程59p-user scottpSQLvariable g_test NUMBER;pSQLEXECUTE get_pkg_test(:g_test);pSQLPRINT g_testpG_TESTp-p 0pSQLEXECUTE set_pkg_test(10);pSQLEXECUTE get_pkg_test(:g_te

58、st);pSQLPRINT g_testpG_TESTp-p 10 Oracle数据库应用教程60p-user systempSQLvariable g_test NUMBERpSQLEXECUTE scott.get_pkg_test(:g_test);pSQLPRINT g_testpG_TESTp-p 0pSQLEXECUTE scott.set_pkg_test(20);pSQLEXECUTE scott.get_pkg_test(:g_test);pSQLPRINT g_testpG_TESTp-p 20 Oracle数据库应用教程61p-user scottpSQLEXECUTE

59、get_pkg_test(:g_test);pSQLPRINT g_testpG_TESTp-p 10 Oracle数据库应用教程62（2）游标持续性）游标持续性p游标持续性是指用户调用包，打开包中的游标后，可以对游标中的结果集采用多次、连续的检索，而不是每次都从第一条记录开始检索。 Oracle数据库应用教程63p为了测试游标的持续性，首先创建一个包含游标的包。 nCREATE OR REPLACE PACKAGE pkg_persistcursornASn CURSOR c_emp IS SELECT * FROM employees;n PROCEDURE DISPLAYEMP;nEND; Oracle数据库应用教程64pCREATE OR REPLACE PACKAGE BODY pkg_persistcursorpASp PROCEDURE DISPLAYEMPp ASp v_emp employees%ROWTYPE;p BEGINp IF NOT c_emp%ISOPEN THENp OPEN C_EMP;p END IF;p FETCH c_emp INTO v_emp;p DBMS_OUTPUT.PUT_LINE(v_emp.employee_id| |p v_emp.first_name);p FETCH c_emp INTO v_emp;p DBMS_OUT