1、第 1 页 共 22 页实验 6 过程、函数和程序包姓名： 学号：专业： 软件工程（金融） 班级：同组人： 无 实验日期： 2013/7/19【实验目的与要求】 掌握过程的创建与调用 掌握 PL/SQL 函数的编写与调用 熟悉程序包的使用【实验内容与步骤】6.0实验准备工作:PL/SQL 程序文件的编辑与执行1使用文档编辑器编辑以下文件，并保存为 aa.sql：2以 scott 身份登录，在 SQL Plus 中执行aa 命令运行程序：注：测试时，文件名请用全名 (即包含路径，如： c:aa)给出运行结果：6.1存储过程第 2 页 共 22 页1最简单的存储过程编写与执行(1)创建测试表dro
2、p table Exam_Table;create table Exam_Table(e_id number(5),e_name varchar2(20),e_salary number(8,2);(2)创建存储过程create or replace procedure insert_salary (v_id number,v_name varchar2,v_salary number) isbegininsert into Exam_Table values (v_id,v_name,v_salary);commit;dbms_output.put_line(数据插入成功);end;/ (3
3、) 执行(调用)存储过程exec insert_salary(6,g,2000);(4)查询执行结果select * from Exam_Table;给出执行的最后结果：2参数的使用：in/out/in out 参数阅读以下程序，理解不同类型参数使用的不同，运行程序，给出运行结果。(1) 用两个参数：in ,out 传入一个姓名，输出：某某人你好：create or replace procedure mp(v_in varchar2,v_out out varchar2)isbeginv_out:=v_in|你好;end;declarev_name varchar2(10);beginmp(
4、scott,v_name);dbms_output.put_line(v_name);第 3 页 共 22 页end;-输出：scott 你好给出运行结果：(2)- in out 类型参数create or replace procedure mp(name_in in varchar2,name_out out varchar2,name_in_out in out varchar2) isbegindbms_output.put_line(name_in);name_out := 返回的参数 name_out 是 | name_in;name_in_out := name_in_out 是
5、 | name_in | name_in_out;end;给出运行结果：第 4 页 共 22 页(3)定义一个返回多个值的存储过程。create or replace procedure p_test(name out varchar2,age out number,sex out varchar2,sal out number)isbeginname:=scott;age:=26;sex:=男 ;sal:=8000;end;declarev_name varchar2(20);v_age number(10);v_sex varchar2(5);v_sal number(10);beginp
6、_test(v_name,v_age,v_sex,v_sal);dbms_output.put_line(v_name);dbms_output.put_line(v_age);dbms_output.put_line(v_sex);dbms_output.put_line(v_sal);end;给出运行结果：3.练习：根据测试表完成下列程序的编写，并给出测试结果：（1）已知有如下表和相应的数据，请根据要求完成实验。create table departments(DEPARTMENT_ID NUMBER(4) primary key,DEPARTMENT_NAME VARCHAR2(30),
7、MANAGER_ID NUMBER(6),LOCATION_ID NUMBER(4);insert into departments values(1,技术部,1,1);insert into departments values(2,人事部,2,2);insert into departments values(3,市场部,3,3);insert into departments values(4,财务部,4,4);第 5 页 共 22 页根据上表结构编写存储过程，实现以下功能，并给出测试结果。1)根据指定的部门更新指定部门名（DEPARTMENT_NAME ）的管理者（MANAGER_ID
8、）id 。2)根据部门 ID（DEPARTMENT_ID） 删除指定部门（2）编写给雇员增加工资的存储过程 CHANGE_SALARY，通过 IN 类型的参数传递要增加工资的雇员编号和增加的工资额。 （操作数据库表为 EMP）.1)编写存储过程 CHANGE_SALARY，给出程序代码：第 6 页 共 22 页2)调用存储过程：EXECUTE CHANGE_SALARY(7788,80)6.2函数1.最简单的函数：-简单函数(1)创建函数create function f(name in varchar2)return varchar2isbeginreturn name;end;(2)调用函
9、数declarev_name varchar2(10);beginv_name:=f(scott);dbms_output.put_line(v_name);end;给出运行结果：2稍微复杂的函数-编写函数create or replace function salarylevel(salary number) return varchar2 is第 7 页 共 22 页beginif salary v_max_comm THEN RETURN FALSE ;ELSE RETURN TRUE ;END IF;END validate_comm;-在包体中定义的局部函数:结束-第 12 页 共
10、22 页-完成在包体中声明的过程-PROCEDURE reset_comm (p_comm IN NUMBER)ISBEGINIF validate_comm(p_comm) THEN g_comm:=p_comm; ELSERAISE_APPLICATION_ERROR(-20210, 不合理的表达式);END IF;END reset_comm;-完成在包体中声明的过程: 结束-END comm_package;-测试包EXECUTE comm_package.reset_comm(.1);EXECUTE comm_package.reset_comm(.33);给出运行结果：第 13 页
11、 共 22 页begincomm_package.reset_comm(0.15);dbms_output.put_line(g_comm = | comm_package.g_comm );end;/给出运行结果：-包的声明和使用 2-drop table Exam_Emp;create table Exam_Emp(id number (5),name varchar2(30),salary number(8,2);insert into Exam_Emp values(1,张一,3000);insert into Exam_Emp values(2,张二,3400);insert int
12、o Exam_Emp values(3,张三,5600);commit;create or replace package tax_pkg asfunction tax(v_value in number) return number;end tax_pkg;/create or replace package body tax_pkg as-包体中的函数执行部分-function tax(v_value in number) return number第 14 页 共 22 页isbeginif v_value =1500 and v_sal=2501 and v_sal=3501 and
13、v_sal=4501 thenv_mesg:=salary is ok;elsev_sal:=4501;v_mesg:=have updated your salary to|to_char(v_sal);end if;end if;update empset sal=v_salwhere empno=no;dbms_output.put_line(v_mesg);end;请给出程序运行结果：6. 编写一个 PL/SQL 函数 count_by_Salary，接受传入参数 Salary_min 和 Salary_max，据此统计并返回薪水值在 Salary_min 和 Salary_max 之
14、间的员工人数。请给出程序源码：create or replace function count_by_Salary(Salary_min in number,Salary_max in number)return numberas num number:=0;beginselect count(*) into numfrom empwhere sal between Salary_min and Salary_max order by sal desc;return num;end;请给出程序运行结果：第 20 页 共 22 页7. 创建一个函数 getAveSal()，以员工号为参数，返回该
15、员工所在部门的平均工资。请给出程序源码：create or replace function getAveSal(p_empno emp.empno%type)return emp.sal%typeas v_sal emp.sal%type;beginselect avg(sal) into v_sal from emp where deptno =(select deptno from emp where empno=p_empno);return v_sal;end;请给出程序运行结果：8. 创建一个包 pkg_emp，包中包含一个函数和一个存储过程。函数以部门号为参数，返回该部门员工的最
16、高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。要求完成后，可以以如下方式调用包：(1)调用包中函数：(2)调用包中存储过程(1)请给出程序源码：第 21 页 共 22 页create or replace package pkg_empasfunction func_highsal(p_deptno emp.deptno%type)return emp.sal%type;procedure proc_highsal(p_deptno emp.deptno%type);end;create or replace package body pkg_empas function
17、func_highsal(p_deptno emp.deptno%type)return emp.sal%typeas v_highsal emp.sal%type;beginselect max(sal) into v_highsal from empwhere deptno = p_deptno;return v_highsal;end;procedure proc_highsal(p_deptno emp.deptno%type)asbeginfor v_emp in (select * from emp where deptno=p_deptno andsal=func_highsal(p_deptno)loopdbms_output.put_line(v_emp.empno| |v_emp.ename);end loop;end;end;(2)请给调用包中函数的执行结果：第 22 页 共 22 页(3)请给出调用包中存储过程的执行结果：
