Oracle之包-2(创建包体)

--包体创建

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;