实验5 过程函数和程序包V.docx

1、实验5 过程函数和程序包V实验5过程、函数和程序包【实验目的与要求】 掌握过程的创建与调用 掌握PL/SQL函数的编写与调用 熟悉程序包的使用【实验内容与步骤】5.0实验准备工作:PL/SQL程序文件的编辑与执行1使用文档编辑器编辑以下文件，并保存为aa.sql：2以scott身份登录，在SQL Plus中执行aa命令运行程序：注：测试时，文件名请用全名(即包含路径，如：c:aa)给出运行结果：5.1存储过程1最简单的存储过程编写与执行(1)创建测试表drop table Exam_Table;create table Exam_Table( e_id number(5), e_name va

2、rchar2(20), e_salary number(8,2);(2)创建存储过程create or replace procedure insert_salary (v_id number,v_name varchar2,v_salary number) isbegin insert into Exam_Table values (v_id,v_name,v_salary); commit; dbms_output.put_line(数据插入成功);end;/ (3) 执行(调用)存储过程exec insert_salary(6,g,2000);(4)查询执行结果select * from

3、 Exam_Table;给出执行的最后结果：2参数的使用：in/out/in out参数阅读以下程序，理解不同类型参数使用的不同，运行程序，给出运行结果。(1) 用两个参数：in ,out 传入一个姓名，输出：某某人你好：create or replace procedure mp(v_in varchar2,v_out out varchar2)isbegin v_out:=v_in|你好;end;/declare v_name varchar2(10);begin mp(scott,v_name); dbms_output.put_line(v_name);end;-输出：scott你好给

4、出运行结果：(2)- in out类型参数create or replace procedure mp(name_in in varchar2, name_out out varchar2, name_in_out in out varchar2) isbegin dbms_output.put_line(name_in); name_out := 返回的参数name_out是 | name_in; name_in_out := name_in_out是 | name_in | name_in_out;end;给出运行结果：(3)定义一个返回多个值的存储过程。create or replace

5、 procedure p_test(name out varchar2,age out number,sex out varchar2,sal out number)isbeginname:=scott;age:=26;sex:=男;sal:=8000;end;/declare v_name varchar2(20); v_age number(10); v_sex varchar2(5); v_sal number(10);begin p_test(v_name,v_age,v_sex,v_sal); dbms_output.put_line(v_name); dbms_output.put

6、_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), MANAGER_ID NUMBER(6), LOCATION_ID NUMBER(4) );insert into departments v

7、alues(1,技术部,1,1);insert into departments values(2,人事部,2,2);insert into departments values(3,市场部,3,3);insert into departments values(4,财务部,4,4);根据上表结构编写存储过程，实现以下功能，并给出测试结果。1)根据指定的部门更新指定部门名（DEPARTMENT_NAME）的管理者（MANAGER_ID）id。执行前的数据：执行后的数据：2)根据部门ID（DEPARTMENT_ID） 删除指定部门执行前的数据：执行后的数据：（2）编写给雇员增加工资的存储过程CH

8、ANGE_SALARY，通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。（操作数据库表为EMP）.1)编写存储过程CHANGE_SALARY，给出程序代码：2)调用存储过程：EXECUTECHANGE_SALARY(7788,80)5.2函数1.最简单的函数：-简单函数(1)创建函数create function f(name in varchar2)return varchar2isbegin return name;end;(2)调用函数declare v_name varchar2(10);begin v_name:=f(scott); dbms_output.put_line

9、(v_name);end;给出运行结果：2稍微复杂的函数-编写函数create or replace function salarylevel(salary number) return varchar2 isbegin if salary 1000 then return 工资太低了,要加油了; elsif salary 3000 then return 还可以,但是也要努力啊; elsif salary v_max_comm THEN RETURN FALSE ; ELSE RETURN TRUE ; END IF; END validate_comm; -在包体中定义的局部函数:结束-

10、-完成在包体中声明的过程- PROCEDURE reset_comm (p_comm IN NUMBER) IS BEGIN IF validate_comm(p_comm) THEN g_comm:=p_comm; ELSE RAISE_APPLICATION_ERROR(-20210, 不合理的表达式); END IF; END reset_comm; -完成在包体中声明的过程:结束- END comm_package;-测试包EXECUTE comm_package.reset_comm(1);EXECUTE comm_package.reset_comm(.33);给出运行结果：beg

11、in comm_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 into Exam

12、_Emp values(3,张三,5600);commit;create or replace package tax_pkg as function 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 is begin if v_value 1000 then return (v_value * 0); elsif v_value 5000 th

13、en return (v_value * 0.10); elsif v_value 10000 then return (v_value * 0.15); else return (v_value * 0.20); end if; end tax; -包体中的函数:结束-end tax_pkg;/- 测试包中定义的函数select salary,tax_pkg.tax(salary) from Exam_Emp;给出运行结果：5.4 作业与思考练习题以为实验中用到的表除作特殊说明外，均为scott模式下的表，如Emp表，请在实验时，连接到scott模式。1. 创建一个存储过程show_emp(

14、)，以员工号为参数，输出该员工的工资。执行测试结果应如下：(1)请给出程序源码：(2)请给出测试结果：2. 创建一个存储过程，以员工号为参数，修改该员工的工资：若该员工属于10号部门，则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；若属于其他部门，则工资增加300.(1)请给出程序源码：create or replace procedure p_changesal(p_empno emp.empno%type) as v_deptno emp.deptno%type; v_increment emp.sal%type; v_sal emp.sal%ty

15、pe; begin select deptno into v_deptno from emp where empno=p_empno; case when v_deptno=10 then v_increment:=150; when v_deptno=20 then v_increment:=200; when v_deptno=30 then v_increment:=250; else v_increment:=300; end case; select sal into v_sal from emp where empno=p_empno; dbms_output.put_line(v

16、_sal+v_increment); end; (2) 完成后，请执行存储过程，并查询表中数据确认是否完成规定要求。3. 创建一个存储过程maxSal( )，以一个整数为参数，输出工资最高的前几个（以参数值为标准）员工的信息。执行测试结果应如下：(1)请给出程序源码：create or replace procedure maxsal(v_num in number)is cursor csr_sal is select * from emp order by sal desc; v_emp csr_sal%rowtype;begin open csr_sal; if(csr_sal%notf

17、ound) then dbms_output.put_line(Sorry,there is no data); else loop fetch csr_sal into v_emp; dbms_output.put_line(v_emp.empno| |v_emp.ename| |v_emp.sal); exit when csr_sal%rowcount=v_num; end loop; end if; close csr_sal; end maxsal;/(2)请给出测试结果：4. 创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。执行测试结果应如下：(1)请给出

18、程序源码：create or replace procedure select_emp(v_num in number,m_num in number)is cursor csr_sal is select * from emp where sal between v_num and m_num; v_emp csr_sal%rowtype;begin open csr_sal; if(csr_sal%notfound) then dbms_output.put_line(Sorry,there is no data); else loop fetch csr_sal into v_emp;

19、dbms_output.put_line(v_emp.empno| |v_emp.ename| |v_emp.sal); exit when csr_sal%rowcount=v_num; end loop; end if; close csr_sal; end select_emp;/(2)请给出测试结果：5. 编写一个存储过程checkSal( )，用以检查所指定雇员，传入员工工号作为参数，检查该员工的薪水是否在有效范围内。不同职位的薪水范围为：Designation RaiseClerk 1500-2500Salesman 2501-3500Analyst 3501-4500Others

20、 4501 and above.如果薪水在此范围内，则显示消息Salary is OK，否则，更新薪水为该范围内的最低值。请给出程序源码：请给出程序运行结果：6. 编写一个PL/SQL函数count_by_Salary，接受传入参数Salary_min和Salary_max，据此统计并返回薪水值在Salary_min和Salary_max之间的员工人数。请给出程序源码：create or replace procedure count_by_salary(salary_min in number,salary_max in number) as num number:=0;begin sele

21、ct count(*) into num from emp where sal between salary_min and salary_max order by sal desc; dbms_output.put_line(num);end;请给出程序运行结果：7. 创建一个函数getAveSal()，以员工号为参数，返回该员工所在部门的平均工资。请给出程序源码：create or replace procedure getAveSal(v_empno emp.empno%type)return emp.sal%type as v_sal emp.sal%typebegin select

22、avg(sal) into v_sal from emp where deptno=(select deptno from emp where empno=v_empno); return v_sal;end;请给出程序运行结果：8. 创建一个包pkg_emp，包中包含一个函数和一个存储过程。函数以部门号为参数，返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。要求完成后，可以以如下方式调用包：(1)调用包中函数：(2)调用包中存储过程(1)请给出程序源码：create or replace package pkg_empis function return_

23、maxsal(v_deptno dept.deptno%type) return number; procedure p_maxsal(p_v_deptno dept.deptno%type);end pkg_emp;/create or replace package body pkg_empas function return_maxsal(v_deptno dept.deptno%type) return number as out_sal number;begin select max(sal) into out_sal from emp where deptno=v_deptno; return(out_sal); end return_maxsal; procedure p_maxsal(p_v_deptno dept.deptno%type) is max_sal number; cursor csr_sal is select * from emp where deptno=p_v_deptno order by sal desc; v_emp csr_sal%rowtype; begin select max(sal) into max_sal from em