5.34.txt

delphi renyuanguanlixinxioxitong

CREATE OR REPLACE PACKAGE BODY emp_package IS
--创建私有函数,判断部门号是否存在，是返回TRUE，否则返回FALSE
FUNCTION pri_deptno(v_deptno VARCHAR2) RETURN BOOLEAN IS
  v_temp department.departmentno%TYPE;
BEGIN
  SELECT departmentno INTO v_temp FROM department WHERE departmentno=v_deptno;
  RETURN TRUE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  RETURN FALSE;
END pri_deptno;
--编写公共过程和函数的执行部分
--增加员工记录过程
PROCEDURE add_employee (eno VARCHAR2,name VARCHAR2,deptno VARCHAR2,
 sex VARCHAR2 DEFAULT '男',edate DATE,wdate DATE,
 natno VARCHAR2,pno VARCHAR2,esal NUMBER,ecomm NUMBER)
IS
BEGIN
  IF pri_deptno(deptno) THEN
     INSERT INTO employee VALUES(eno,name,deptno,sex,edate,wdate,
     natno,pno,esal,ecomm);
  ELSE
     raise_application_error(-20010,'部门不存在！');
  END IF; 
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
     raise_application_error(-20011,'员工号不能重复！');
END add_employee;
--删除员工记录过程
PROCEDURE fire_employee(eno VARCHAR2) IS
BEGIN
  DELETE FROM employee WHERE employeeno=eno;
  IF SQL%NOTFOUND THEN
     raise_application_error(-20012,'该雇员不存在');
  END IF;
END;
--通过员工姓名获得员工工资函数
FUNCTION get_sal(name VARCHAR2) RETURN NUMBER AS
   v_sal employee.sal%TYPE;
 BEGIN
   SELECT sal INTO v_sal FROM employee WHERE trim(employeename)=trim(name);
   RETURN(v_sal);
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     raise_application_error(-20000,'该员工不存在！');
 END get_sal;
END emp_package;