Oracle的包实例

最新推荐文章于 2021-04-16 00:53:59 发布

更好的自己520

最新推荐文章于 2021-04-16 00:53:59 发布

阅读量528

点赞数

分类专栏： Oracle入门教程文章标签： oracle 实例

本文链接：https://blog.csdn.net/a3060858469/article/details/79477225

版权

Oracle入门教程专栏收录该内容

18 篇文章 0 订阅

订阅专栏

调用

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,-20001);
  pragma exception_init(e_no_dept,-20008);
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(-20020,'不存在此员工！');

     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(-20020,'不存在此员工！');

     end;

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

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

        end if;
    end;

end;

根据员工号查询员工信息

declare 
     emp_record emp%rowtype;
     e_no_emp exception;
     pragma exception_init(e_no_emp,-20020);
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,-20020);
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