实验七 过程与函数(教师).doc

实验七过程与函数一、 实验目的1. 掌握PL/SQL的存储过程2. 熟悉PL/SQL的函数的编写二、实验环境一台PC机，安装widows xp操作系统，oracle 10g或11g数据库软件。三、实验内容 1 写存储过程，显示所指定雇员名所在的部门名和位置。CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,pdname OUT dept.dname%TYPE,ploc OUT dept.loc%TYPE) ASBEGINSELECT dname,loc INTO pdname,ploc FROM emp,deptWHERE emp.deptno=dept.deptno AND emp.ename=pename;END;/VARIABLE vdname VARCHAR2(14);VARIABLE vloc VARCHAR2(13);EXECUTE DeptMesg(SMITH,:vdname，:vloc);PRINT vdname vloc;2 定义一个为修改职工表（emp）中某职工工资的存储过程子程序，职工名作为形参,若该职工名在职工表中查找不到，就在屏幕上提示“查无此人”然后结束子程序的执行；否则若工种为MANAGER的，则工资加1000；工种为SALESMAN，工资加500；工种为ANALYST，工资加200，否则工资加100。create or replace procedure xggz(name varchar2) isk_job emp.job%type; addsal emp.sal%type;beginselect job into k_job from emp where ename=name;if k_job=MANAGER then addsal:=1000;elsif k_job=SALESMAN then addsal:=500;elsif k_job=ANALYST then addsal:=200;elseaddsal:=100;end if;update emp set sal=sal+addsal where ename=name;exceptionwhen no_data_found thendbms_output.put_line(查无此人”);end;3 定义一个函数子程序，根据输入的职工号计算该职工的年收入总额。程序如下：FUNCTION compsumal(v_empno number)RETURN real IS /*函数返回值是实型值*/sum_sal real;BEGIN select 12*(sal+nvl(comm,0) INTO sum_sal from emp/* nvl(参数1,参数2) 为空值处理函数参见课本P88* /where emono= v_empno;RETURN(sum_sal);EXCEPTIONWHEN NO_DATA_FOUND THEN dbms_output.putline(没有找到职工!);return(-1);WHEN Others THEN dbms_output.put_line(发现其他错误！); return(-2);END compsumal; /*在END 后的函数名compsumal 是可选的，可以省略*/可用如下PL/SQL赋值语句调用上述函数：y_sum:= compsumal(7788);在主调函数中可以对y_sum的值进行判断。如果是-1，说明未找到该职工，不能计算；如果是-2，说明有其他错误，否则就是要计算该职工的年收入总额。4 编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：Designation RaiseClerk 1500-2500Salesman 2501-3500Analyst 3501-4500Others 4501 and above.如果薪水在此范围内，则显示消息Salary is OK，否则，更新薪水为该范围内的最低值。请给出程序源码：CREATE OR REPLACE FUNCTION Sal_Level(no emp.empno%TYPE) RETURN CHAR ASvjob emp.job%TYPE;vsal emp.sal%TYPE;vmesg CHAR(50);BEGINSELECT job,sal INTO vjob,vsal FROM emp WHERE empno=no;IF vjob=CLERK THENIF vsal=1500 AND vsal=2501 AND vsal=3501 AND vsal=4501 THEN vmesg:=Salary is OK.;ELSE vsal:=4501; vmesg:=Have updated your salary to |TO_CHAR(vsal);END IF;END IF;UPDATE emp SET sal=vsal WHERE empno=no;RETURN vmesg;END;/DECLAREvmesg CHAR(50);vempno emp.empno%TY