实验6过程_函数和程序包

实验6 过程、函数和程序包

姓名：学号：

专业：软件工程（金融）班级：

同组人：无实验日期：2013/7/19

【实验目的与要求】

?掌握过程的创建与调用

?掌握PL/SQL函数的编写与调用

?熟悉程序包的使用

【实验内容与步骤】

6.0．实验准备工作:PL/SQL程序文件的编辑与执行

1．使用文档编辑器编辑以下文件，并保存为aa.sql：

2．以scott身份登录，在SQL Plus中执行＠aa命令运行程序：

注：测试时，文件名请用全名(即包含路径，如：@c:\aa)

给出运行结果：

6.1．存储过程

1．最简单的存储过程编写与执行

(1)创建测试表

drop 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)

is

begin

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 Exam_Table;

给出执行的最后结果：

2．参数的使用：in/out/in out参数

阅读以下程序，理解不同类型参数使用的不同，运行程序，给出运行结果。

(1) 用两个参数：in ,out 传入一个姓名，输出：某某人你好：

create or replace procedure mp(v_in varchar2,v_out out varchar2) is

begin

v_out:=v_in||'你好';

end;

declare

v_name varchar2(10);

begin

mp('scott',v_name);

dbms_output.put_line(v_name);

end;--输出：scott你好

给出运行结果：

(2)-- in out类型参数

create or replace procedure mp(name_in in varchar2,

name_out out varchar2,

name_in_out in out varchar2)

is

begin

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 procedure p_test(name out varchar2,age out number,sex out varchar2,sal out number)

is

begin

name:='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_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 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);

根据上表结构编写存储过程，实现以下功能，并给出测试结果。

1)根据指定的部门更新指定部门名（DEPARTMENT_NAME）的管理者（MANAGER_ID）id。

2)根据部门ID（DEPARTMENT_ID）删除指定部门

（2）编写给雇员增加工资的存储过程CHANGE_SALARY，通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。（操作数据库表为EMP）.

1)编写存储过程CHANGE_SALARY，给出程序代码：

2)调用存储过程：EXECUTE CHANGE_SALARY(7788,80)

6.2．函数

1.最简单的函数：

--简单函数

(1)创建函数

create function f(name in varchar2)return varchar2

is

begin

return name;

end;

(2)调用函数

declare

v_name varchar2(10);

begin

v_name:=f('scott');

dbms_output.put_line(v_name);

end;

给出运行结果：

2．稍微复杂的函数

--编写函数

create or replace function salarylevel(salary number) return varchar2 is

begin

if salary < 1000 then

return '工资太低了,要加油了';

elsif salary <3000 then

return '还可以,但是也要努力啊';

elsif salary <5000 then

return '这个还可以';

else

return '你的工资现在来说,是比较高了';

end if;

end;

--调用函数

declare

v_salarylevel varchar2(50);

begin

v_salarylevel := salarylevel(1000);

dbms_output.put_line(v_salarylevel);

end;

给出运行结果：

3．函数练习：

（1）. 定义一个函数接收三个参数，算出最大值。而后调用该函数，给出测试结果。

给出求三个数最大值函数getMax(num1,num2,num3)程序源码：

--调用测试：

declare

v_max number(10,2);

begin

v_max:= getMax(10.2,34.4,34.6);

dbms_output.put_line(v_max);

end;

给出运行结果：

（2）.根据房屋的相关数据（长，宽），编写函数find_area，根据公式求出其建筑面积和使用面积，并写入到数据库表中，完成后，使用Select 语句查询表中数据，以确认程序编写的正确性。

1)创建表：建立房屋表House(房屋名称，长，宽，建筑面积，使用面积)；

给出相应代码：

2)插入三行记录测试，插入值时忽略建筑面积和使用面积

'1号机房',20,5.5

'2号机房',25,5.5

'卧室',200,5.5

给出相应代码：

房屋名称：housename

3)利用 find_area函数，更新建筑面积和使用面积字段

--建筑面积 =长* 宽 + 2.5

--使用面积 =长* 宽

给出相应代码：

4)在select查询表中数据。

给出运行结果：

6.3．包的声明和使用

阅读以下程序，理解包的声明和使用。

-----------包的声明和使用1------------------------- DROP TABLE Exam_Emps;

CREATE TABLE Exam_Emps(

id number(5) primary key,

name varchar2(30),

commission_pct number(3,2 )

);

insert into Exam_Emps values (1,'张一',0.13);

insert into Exam_Emps values (2,'张二',0.23);

insert into Exam_Emps values (3,'张三',0.33);

insert into Exam_Emps values (4,'张四',0.43);

commit;

select * from Exam_Emps;

--创建包头

CREATE OR REPLACE PACKAGE comm_package

IS

g_comm NUMBER := 0.10;

PROCEDURE reset_comm (p_comm IN NUMBER);

END comm_package;

/

--创建包体

CREATE OR REPLACE PACKAGE BODY comm_package

IS

-------------在包体中定义的局部函数--------------

/*

如果输入的参数p_comm大于Exam_Emps表中最大的commission_pct 字段,则函数返回FALSE,否则函数返回TRUE

*/

FUNCTION validate_comm (p_comm IN NUMBER)

RETURN BOOLEAN

IS

v_max_comm NUMBER;

BEGIN

SELECT MAX(commission_pct)

INTO v_max_comm

FROM Exam_Emps;

IF p_comm > v_max_comm THEN

RETURN FALSE ;

ELSE

RETURN TRUE ;

END IF;

END validate_comm;

---------在包体中定义的局部函数:结束------------

------------完成在包体中声明的过程--------------

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);

给出运行结果：

begin

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_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 then

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;

给出运行结果：

6.4 作业与思考练习题

以为实验中用到的表除作特殊说明外，均为scott模式下的表，如Emp表，请在实验时，连接到scott模式。

1.创建一个存储过程show_emp()，以员工号为参数，输出该员工的工资。

执行测试结果应如下：

(1)请给出程序源码：

create or replace procedure show_emp(s_empno emp.empno%type)

as

v_sal emp.sal%type;

v_empno emp.empno%type;

begin

select sal,empno into v_sal,v_empno

from emp

where empno = s_empno;

dbms_output.put_line(v_sal|| '该员工的员工号:'||v_empno);

end;

(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%type;

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_sal+v_increment);

end;

(2) 完成后，请执行存储过程，并查询表中数据确认是否完成规定要求。

执行前：

执行后：

3.创建一个存储过程maxSal( )，以一个整数为参数，输出工资最高的前几个（以参数值

为标准）员工的信息。

执行测试结果应如下：

(1)请给出程序源码：

create or replace procedure maxsal(num in number)

is

cursor v_sal

is

select * from emp

order by sal desc;

v_emp v_sal%rowtype;

begin

open v_sal;

loop

fetch v_sal into v_emp;

dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.sal||' '

||v_emp.deptno);

exit when v_sal%rowcount=num;

end loop;

end;

(2)请给出测试结果：

4.创建一个存储过程，以两个整数为参数，输出工资排序在两个参数之间的员工信息。

执行测试结果应如下：

(1)请给出程序源码：

create or replace procedure select_emp(num1 in number,num2 in number)

is

cursor v_sal

is

select * from emp

where sal between num1 and num2 order by sal desc;

v_emp v_sal%rowtype;

begin

open v_sal;

loop

fetch v_sal into v_emp;

dbms_output.put_line(v_emp.empno||' '||v_emp.ename||' '||v_emp.sal||' '

||v_emp.deptno);

exit when v_sal%notfound;

end loop;

end;

(2)请给出测试结果：

5.编写一个存储过程checkSal( )，用以检查所指定雇员，传入员工工号作为参数，检查

该员工的薪水是否在有效范围内。不同职位的薪水范围为：

Designation Raise

Clerk 1500-2500

Salesman 2501-3500

Analyst 3501-4500

Others 4501 and above.

如果薪水在此范围内，则显示消息"Salary is OK"，否则，更新薪水为该范围内的最低值。

请给出程序源码：

create or replace procedure checkSal(no emp.empno%type)

as

v_job emp.job%type;

v_sal emp.sal%type;

v_mesg varchar2(50);

begin

select job,sal into v_job,v_sal

from emp where empno = no;

if v_job = 'CLERK' then

if v_sal>=1500 and v_sal<=2500 then

v_mesg:='salary is ok';

else

v_sal:=1500;

v_mesg:='have updated your salary to'||to_char(v_sal);

end if;

elsif v_job='SALESMAN' then

if v_sal>=2501 and v_sal<=3500 then

v_mesg:='salary is ok';

else

v_sal:=2501;

v_mesg:='have updated your salary to'||to_char(v_sal);

end if;

elsif v_job='ANALYST' then

if v_sal>=3501 and v_sal<=4500 then

v_mesg:='salary is ok';

else

v_sal:=3501;

v_mesg:='have updated your salary to'||to_char(v_sal);

end if;

else

if v_sal>=4501 then

v_mesg:='salary is ok';

else

v_sal:=4501;

v_mesg:='have updated your salary to'||to_char(v_sal);

end if;

end if;

update emp

set sal=v_sal

where empno=no;

dbms_output.put_line(v_mesg);

end;

请给出程序运行结果：

6.编写一个PL/SQL函数count_by_Salary，接受传入参数Salary_min和Salary_max，

据此统计并返回薪水值在Salary_min和Salary_max之间的员工人数。

请给出程序源码：

create or replace function count_by_Salary(Salary_min in number,Salary_max in number)

return number

as

num number:=0;

begin

select count(*) into num

from emp

where sal between Salary_min and Salary_max order by sal desc;

return num;

end;

请给出程序运行结果：

7.创建一个函数getAveSal()，以员工号为参数，返回该员工所在部门的平均工资。

请给出程序源码：

create or replace function getAveSal(p_empno emp.empno%type)

return emp.sal%type

as v_sal emp.sal%type;

begin

select avg(sal) into v_sal from emp where deptno =

(select deptno from emp where empno=p_empno);

return v_sal;

end;

请给出程序运行结果：

8.创建一个包pkg_emp，包中包含一个函数和一个存储过程。函数以部门号为参数，返回

该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。

要求完成后，可以以如下方式调用包：

(1)调用包中函数：

(2)调用包中存储过程

(1)请给出程序源码：