PL/SQL包3（使用包构造过程）

在包中定义了全局变量之后，有些情况下，会话中可能还需要初始化全局变量，此时可以使用包的构造过程，这种过程类似于高级语言中的构造函数和构造方法。
1、建立包规范

CREATE OR REPLACE PACKAGE emp_package
IS
  minsal NUMBER(6,2);
  maxsal NUMBER(6,2);
  PROCEDURE add_employee(eno NUMBER,NAME VARCHAR2,salary NUMBER,dno NUMBER);
  PROCEDURE upd_sal(eno NUMBER,salary NUMBER);
  PROCEDURE upd_sal(NAME VARCHAR2,salary NUMBER);
END emp_package;

2、建立包体

CREATE OR REPLACE PACKAGE BODY emp_package
IS
  PROCEDURE add_employee(eno NUMBER,NAME VARCHAR2,
    salary NUMBER,dno NUMBER)
  IS
  BEGIN
    IF salary BETWEEN minsal AND maxsal THEN
      INSERT INTO emp(empno,ename,sal,deptno)
      VALUES (eno,NAME,salary,dno);
    ELSE
      RAISE_APPLICATION_ERROR(-20000,'工资不在范围内');
    END IF;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        RAISE_APPLICATION_ERROR(-20001,'雇员已存在');
  END;
  
  PROCEDURE upd_sal(eno NUMBER,salary NUMBER)
  IS
  BEGIN
    IF salary BETWEEN minsal AND maxsal THEN
      UPDATE emp SET sal = salary WHERE empno = eno;
      IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20002,'不存在该雇员号');
      END IF;
    ELSE
      RAISE_APPLICATION_ERROR(-20000,'工资不在范围内');
    END IF;
  END;
  
  PROCEDURE upd_sal(NAME VARCHAR2,salary NUMBER)
  IS
  BEGIN
    IF salary BETWEEN minsal AND maxsal THEN
      UPDATE emp SET sal = salary WHERE UPPER(ename) = UPPER(NAME);
      IF SQL%NOTFOUND THEN
        RAISE_APPLICATION_ERROR(-20002,'不存在该雇员号');
      END IF;
    ELSE
      RAISE_APPLICATION_ERROR(-20000,'工资不在范围内');
    END IF;
  END;
  --包构造过程
  BEGIN
    SELECT MIN(sal),MAX(sal) INTO minsal,maxsal FROM emp;
END emp_package;

3、调用包公用组件

SQL> EXEC emp_package.add_employee(1114,'MARY',3000,20);
SQL> EXEC emp_package.upd_sal('MARY',2000);