20-1:
	CREATE OR REPLACE PACKAGE emp_package IS
	  g_deptno emp.deptno%TYPE:=30;
	  PROCEDURE add_employee(
	    empno emp.empno%TYPE,ename emp.ename%TYPE,
	    job emp.job%TYPE DEFAULT 'CLERK',mgr emp.mgr%TYPE,
	    hiredate emp.hiredate%TYPE DEFAULT SYSDATE,
	    sal emp.sal%TYPE DEFAULT 1000,comm emp.comm%TYPE,
	    deptno emp.deptno%TYPE DEFAULT g_deptno);
	  PROCEDURE fire_employee(eno NUMBER);
	  FUNCTION get_sal(eno NUMBER) RETURN NUMBER;
	END emp_package;
	/
20-2:
	CREATE OR REPLACE PACKAGE BODY emp_package IS
  	FUNCTION validate_deptno(dno emp.deptno%TYPE)
	RETURN BOOLEAN
	IS
	  temp INT;
	BEGIN
	  SELECT 1 INTO temp FROM dept WHERE deptno=dno;
	  RETURN TRUE;
	EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	    RETURN FALSE;
	END;
	PROCEDURE add_employee(
	  empno emp.empno%TYPE,ename emp.ename%TYPE,
	  job emp.job%TYPE DEFAULT 'CLERK',mgr emp.mgr%TYPE,
	  hiredate emp.hiredate%TYPE DEFAULT SYSDATE,
	  sal emp.sal%TYPE DEFAULT 1000,comm emp.comm%TYPE,
	  deptno emp.deptno%TYPE DEFAULT g_deptno)
	IS
	BEGIN
	  IF validate_deptno(deptno) THEN
	    INSERT INTO emp VALUES(empno,ename,job,mgr,
	    hiredate,sal,comm,deptno);
	  END IF;
	END;
	PROCEDURE fire_employee(eno NUMBER) IS
	BEGIN
	  DELETE FROM emp WHERE empno=eno;
	END;
	FUNCTION get_sal(eno NUMBER) RETURN NUMBER
	IS
	  v_sal emp.sal%TYPE;
	BEGIN
	  SELECT sal INTO v_sal FROM emp WHERE empno=eno;
	  RETURN v_sal;
	END;
	END emp_package;
	/
20-3:
	exec emp_package.g_deptno:=20
20-4:
	exec emp_package.g_deptno:=20
	exec emp_package.add_employee(1566,'MARY',mgr=>7788,comm=>0)
	SELECT ename,deptno FROM emp WHERE empno=1566;
20-5:
	VAR salary NUMBER
	exec :salary:=emp_package.get_sal(1566)
	PRINT salary
20-6:
	CREATE OR REPLACE PACKAGE emp_pkg AS
	  PROCEDURE add_emp(emp_record emp%ROWTYPE);
	  FUNCTION get_info(eno emp.empno%TYPE) RETURN emp%ROWTYPE;
	END;
	/
	CREATE OR REPLACE PACKAGE BODY emp_pkg IS
	PROCEDURE add_emp(emp_record emp%ROWTYPE) IS
	  e_2291 EXCEPTION;
	  PRAGMA EXCEPTION_INIT(e_2291,-2291);
	BEGIN
	  INSERT INTO emp VALUES emp_record;
	EXCEPTION
	  WHEN DUP_VAL_ON_INDEX THEN
	    RAISE_APPLICATION_ERROR(-20010,'ԱŲظ');
	  WHEN e_2291 THEN
	    RAISE_APPLICATION_ERROR(-20011,'Ų');
	END;
	FUNCTION get_info(eno emp.empno%TYPE) RETURN emp%ROWTYPE
	IS
	  emp_record emp%ROWTYPE;
	BEGIN
	  SELECT * INTO emp_record FROM emp WHERE empno=eno;
	  RETURN emp_record;
	EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	    RAISE_APPLICATION_ERROR(-20012,'Ա');
	END;
	END emp_pkg;
	/
	DECLARE
	  emp_record emp%ROWTYPE;
	BEGIN
	  emp_record.empno:=&eno; emp_record.ename:='&name';
	  emp_record.job:='&job'; emp_record.mgr:=&mgr;
	  emp_record.hiredate:=SYSDATE; emp_record.sal:=&sal;
	  emp_record.comm:=&comm; emp_record.deptno:=&deptno;
	  emp_pkg.add_emp(emp_record);
	END;
	/
	DECLARE
	  emp_record emp%ROWTYPE;
	BEGIN
	  emp_record:=emp_pkg.get_info(&eno);
	  dbms_output.put_line(':'||emp_record.ename
	    ||',:'||emp_record.sal);
	END;
	/
20-7:
	CREATE OR REPLACE PACKAGE dept_pkg IS
	  TYPE dept_table_type IS TABLE OF dept%ROWTYPE;
	  PROCEDURE add_dept(dept_table dept_table_type);
	  FUNCTION get_all RETURN dept_table_type;
	END;
	/
	CREATE OR REPLACE PACKAGE BODY dept_pkg IS
	PROCEDURE add_dept(dept_table dept_table_type)
	IS
	BEGIN
	  FORALL i IN 1..dept_table.COUNT
	    INSERT INTO dept VALUES dept_table(i);
	END;
	FUNCTION get_all RETURN dept_table_type IS
	  dept_table dept_table_type;
	BEGIN
	  SELECT * BULK COLLECT INTO dept_table FROM dept;
	  RETURN dept_table;
	END;
	END dept_pkg;
	/
	DECLARE
	  dept_table dept_pkg.dept_table_type
	    :=dept_pkg.dept_table_type(null,null,null);
	BEGIN
	   FOR i IN 1..dept_table.COUNT LOOP
	     dept_table(i).deptno:=50+i;
	     dept_table(i).dname:='SALES_'||i;
	     dept_table(i).loc:='LOC_'||i;
	  END LOOP;
	  dept_pkg.add_dept(dept_table);
	END;
	/
	DECLARE
	  dept_table dept_pkg.dept_table_type;
	BEGIN
	  dept_table:=dept_pkg.get_all;
	  FOR i IN 1..dept_table.COUNT LOOP
	    dbms_output.put_line(':'||dept_table(i).deptno
	      ||',:'||dept_table(i).dname);
	  END LOOP;
	END;
	/
20-8:
	CREATE OR REPLACE PACKAGE emp_pkg IS
	  FUNCTION get_sal(eno NUMBER) RETURN NUMBER;
	  FUNCTION get_sal(name VARCHAR2) RETURN NUMBER;
	  PROCEDURE fire_employee(eno NUMBER);
	  PROCEDURE fire_employee(name VARCHAR2);
	END;
	/
	CREATE OR REPLACE PACKAGE BODY emp_pkg IS
	FUNCTION get_sal(eno NUMBER) RETURN NUMBER
	IS
	  v_sal emp.sal%TYPE;
	BEGIN
	  SELECT sal INTO v_sal FROM emp WHERE empno=eno;
	  RETURN v_sal;
	EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	    raise_application_error(-20020,'ùԱ');
	END;
	FUNCTION get_sal(name VARCHAR2) RETURN NUMBER
	IS
	  v_sal emp.sal%TYPE;
	BEGIN
	  SELECT sal INTO v_sal FROM emp
	    WHERE upper(ename)=upper(name);
	  RETURN v_sal;
	EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	    raise_application_error(-20020,'ùԱ');
	END;
	PROCEDURE fire_employee(eno NUMBER) IS
	BEGIN
	  DELETE FROM emp WHERE empno=eno;
	  IF SQL%NOTFOUND THEN
	    raise_application_error(-20020,'ùԱ');
	  END IF;
	END;
	PROCEDURE fire_employee(name VARCHAR2) IS
	BEGIN
	  DELETE FROM emp WHERE upper(ename)=upper(name);
	  IF SQL%NOTFOUND THEN
	   raise_application_error(-20020,'ùԱ');
	   END IF;
	END;
	END;
	/
20-9:
	CREATE OR REPLACE PACKAGE emp_package IS
	  minsal NUMBER(6,2);
	  maxsal NUMBER(6,2);
	  PROCEDURE upd_sal(eno NUMBER,salary NUMBER);
	  PROCEDURE upd_sal(name VARCHAR2,salary NUMBER);
	END;
	/
	CREATE OR REPLACE PACKAGE BODY emp_package IS
	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(-20003,'ڸùԱ');
	      END IF;
	  ELSE
	     raise_application_error(-20001,'ʲڷΧ');
	  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(-20004,'ڸùԱ');
	     END IF;
	  ELSE
	     raise_application_error(-20001,'ʲڷΧ');
	  END IF;
	END;
	BEGIN   
	  SELECT min(sal),max(sal) INTO minsal,maxsal FROM emp;
	END;
	/
20-10:
	CREATE OR REPLACE PACKAGE purity IS
	  minsal NUMBER(6,2);
	  maxsal NUMBER(6,2);
	  FUNCTION max_sal RETURN NUMBER;
	  FUNCTION min_sal RETURN NUMBER;
	  PRAGMA RESTRICT_REFERENCES(max_sal,WNPS);
	  PRAGMA RESTRICT_REFERENCES(min_sal,WNPS);
	END;
	/
	CREATE OR REPLACE PACKAGE BODY purity IS
	FUNCTION max_sal RETURN NUMBER
	IS
	BEGIN
	   RETURN maxsal;
	END;
	FUNCTION min_sal RETURN NUMBER
	IS
	BEGIN
	  RETURN minsal;
	END;
	BEGIN
	  SELECT min(sal),max(sal) INTO minsal,maxsal FROM emp;
	END;
	/
20-11:
	DROP PACKAGE purity;
20-12:
	SELECT object_name,object_type FROM user_objects WHERE object_type='PACKAGE BODY' AND status='INVALID';
20-13:
	ALTER PACKAGE purity COMPILE;
20-14:
	SELECT text FROM user_source WHERE name='PURITY' AND type='PACKAGE BODY';


