编写一个函数以检查所指定雇员的薪水(SAL)是否有效范围内。不同职位(JOB)的薪水范围为:
Designation 1500-2500
Raise 1500-2500
Clerk 1500-2500
Salesman 2501-3500
Analyst 3501-4500
Othersc 4501
如果薪水在此范围内,则显示消息"Salary is OK"
Designation 1500-2500
Raise 1500-2500
Clerk 1500-2500
Salesman 2501-3500
Analyst 3501-4500
Othersc 4501
如果薪水在此范围内,则显示消息"Salary is OK"
return varchar2 is
ls_ret varchar2(20);
begin
if job = 'Designation' and (sal > 1500 and sal <2500) then
ls_ret := 'Salary is OK';
end if; ... return ls_ret ;
end;
/
Create Or Replace Function c_check_sal(p_empno emp.empno%Type)
Return Boolean
Is
v_sal emp.sal%Type;
-- v_sal_2 emp.sal%Type;
v_flag Boolean:=False;
v_job emp.job%Type;
Begin
Select sal Into v_sal From emp Where empno=p_empno;
If(v_job='DESIGNATION'And v_sal Between 1500 And 2500) Then
v_flag:=True;
Elsif(v_job='RAISE'And v_sal Between 1500 And 2500) Then
v_flag:=True;
Elsif(v_job='CLERK'And v_sal Between 1500 And 2500) Then
v_flag:=True;
Elsif(v_job='SALEMAN'And v_sal Between 1500 And 2500) Then
v_flag:=True;
Elsif(v_job='ANALYST'And v_sal Between 1500 And 2500) Then
v_flag:=True;
Elsif(v_job='other' and v_sal=4501) then
v_flag:=true;
else
End If;
If (v_flag=True) Then
dbms_output.put_line('The salary is ok!');
Else
dbms_output.put_line('The salary is not ok!');
End If;
Return True;
End c_check_sal;
Select sal Into v_sal From emp Where empno=p_empno;如果没有p_empno对于的雇员的话肯定报错了.可以改为Select max(sal) Into v_sal From emp Where empno=p_empno;