--包体创建
create or replace package body emp_mgmt as
cursor C_emp return emp_tax_record is select empno from emp order by sal asc;
function getEmpCount return number is
xx number(4);
begin
select count(*) into xx from emp;
return xx;
end getEmpCount;
procedure show_emp_tax as
xx emp.empno%type;
begin
if NOT C_emp%ISOPEN then open C_emp;
end if;
loop
fetch C_emp into xx;
exit when C_emp%NOTFOUND;
dbms_output.put_line(xx||getEname(xx)||'的税为:'||to_char(tax_emp(xx)));
end loop;
close C_emp;
end show_emp_tax;
function getEname(e_no in emp.empno%type) return varchar2 as
xx varchar2(100);
begin
select ename into xx from emp where empno=e_no;
return xx;
end getEname;
procedure init(v_tax float:=0.08) as
begin
p_tax:=v_tax;
Emp_count:=getEmpCount();
dbms_output.put_line('当前税率为:'||p_tax*100||'%');
dbms_output.put_line('当前员工数为:'||Emp_count);
end init;
procedure hire_emp(e_no in EMP.empno%type,e_name in emp.ename%type,e_job in emp.job%type,e_sal in emp.sal%type) as
begin
if(Exist_emp(e_no)) then raise_application_error(-20000,e_no||'该编号的员工已经存在!');
end if;
insert into emp(empno,ename,job,sal) values(e_no,e_name,e_job,e_sal);
emp_count:=emp_count+1;
dbms_output.put_line('当前员工总数为:'||emp_count);
end hire_emp;
procedure fire_emp(e_no in EMP.empno%type) as
begin
if(not Exist_emp(e_no)) then raise_application_error(-20000,e_no||'该编号的员工不存在!');
end if;
delete from emp where empno=e_no;
emp_count:=emp_count-1;
dbms_output.put_line('当前员工总数为:'||emp_count);
end fire_emp;
function tax_emp(e_no in EMP.empno%type) return float is
v_tax number(7,2):=0;
begin
select sal into v_tax from emp where empno=e_no;if v_tax=0 then raise sal_null;
end if;
v_tax:=v_tax*p_tax;
return v_tax;
exception
when no_data_found then dbms_output.put_line('员工不存在!');
when sal_null then dbms_output.put_line('有工资为空!');
end tax_emp;
function Exist_emp (e_no in emp.empno%type) return boolean is
vv number(2):=0;
begin
select count(*) into vv from emp where empno=e_no;if vv=0 then return false;elsif vv>0 then return true;end if;
end Exist_emp;
procedure getOneEmpTax(e_no in emp.empno%type) as
begin
dbms_output.put_line(e_no||'的税收为'||tax_emp(e_no));
end getOneEmpTax;
end emp_mgmt;