Oracle的包執行個體

調用

declare
  v_empno emp.empno%type:=&empno;
  v_ename emp.ename%type:=&name;
  v_sal emp.sal%type:=&salary;
  v_deptno emp.deptno%type:=&deptno;
  e_dup_val EXCEPTION;
  e_no_dept EXCEPTION;

  pragma exception_init(e_dup_val,-);
  pragma exception_init(e_no_dept,-);
begin
   emp_package.add_emp_proc(v_empno,v_ename,v_sal,v_deptno);
   Commit;
EXCEPTION
   when e_dup_val then
       DBMS_OUTPUT.put_line(SQLERRM);
   when e_no_dept then
       DBMS_OUTPUT_put_line(SQLERRM);
   Rollback;
end;
           

子程式重載及相關操作

所謂的重載是指兩個或多個子程式有相同的名稱，但擁有不同的參數變量、參數順序或參數資料類型

包規範的建立

create or replace package overload_pkh
as 
   function get_info(eno number) return emp%ROWTYPE;
   Function get_info(name varchar2) return emp%ROWTYE;

   procedure del_emp(eno number);
   procedure del_emp(name varchar);
end;
           

建立包體

create or replace package body overload_pkg
as
   function get_info(eno number) return emp%rowtype;
   as
       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(-,'不存在此員工！');

     end;

   function get_info(name varchar) return emp%rowtype;
   as
       emp_record emp%rowtype;
   begin
       select * into emp_record from emp where ename=name;
       return  emp_record;
   exception
       when no_data_found then
          raise_application_error(-,'不存在此員工！');

     end;

    procedure del_emp(eno number)
    is
    begin
        delete from emp where empno=eno;
        if  sql%notfound then
              raise_application_error(-,'不存在此員工！');

        end if;
    end;
     procedure del_emp(ename varchar)
    is
    begin
        delete from emp where ename=name;
        if  sql%notfound then
              raise_application_error(-,'不存在此員工！');

        end if;
    end;

end;
           

根據員工号查詢員工資訊

declare 
     emp_record emp%rowtype;
     e_no_emp exception;
     pragma exception_init(e_no_emp,-);
begin
     emp_record:=overload.pkg.get_info(&no);
     DBMS_OUTPUT.put_line(emp_record.empno);
Exception
     when e_no_emp then
         DBMS_OUTPUT.put_line(SQLERRM);
end;
           

調用員姓名查詢員工資訊

declare 
     emp_record emp%rowtype;
     e_no_emp exception;
     pragma exception_init(e_no_emp,-);
begin
     emp_record:=overload.pkg.get_info(&name);
     DBMS_OUTPUT.put_line(emp_record.empno);
Exception
     when e_no_emp then
         DBMS_OUTPUT.put_line(SQLERRM);
end;
           

參考：http://www.51zxw.net/show.aspx?id=61838&cid=616